Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    2

    Angry Unanswered: Environment discrepencies concerning stored procedures & data types

    Im trying to move a database from a NT4 SQL2K server over to a WIN2K SQL2K box.

    When i copy the stored procedures over via a generated script, 1 procedure fails to create itself, and the error i get is:

    The text, ntext, and image data types are invalid in this subquery or aggregate
    the error is supposedly the top line here:

    Code:
    SELECT *, 'cms_Document_Contents_ID'='', 'cms_Language_ID'='', 'Title'='', 'XML'='', 'search_text'='', 
    			'File_XML'=(SELECT TOP 1 File_XML FROM cms_Document_Contents d 
    					INNER JOIN cms_Document_Sections e ON d.cms_Document_Id=e.cms_Document_Id
    					WHERE d.cms_Language_ID=9 AND e.cms_Document_Section_ID=@DocumentSectionId) , 
    			c.active, c.sequence, c.cms_Document_Section_ID, c.cms_Section_ID, c.cms_Document_Type_ID
    			FROM cms_Documents a
    				INNER JOIN cms_Document_Sections c ON a.cms_Document_ID=c.cms_Document_ID
    				WHERE c.cms_Document_Section_ID=@DocumentSectionId
    	END
    
    
    GO
    wtf is going on? this works fine on the old server. i tried running it back there and it just complains that the stored procedure already exists.
    i am not familiar with SQL, this is someone elses code, i dont understand the error and NEED all the help i can get.
    Last edited by 930r93; 11-20-03 at 18:16.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Somehow you got single quotes stuck around your column names.

    Select 'cms_Document_Contents_ID'=''
    ...makes no sense. I assume the procedure is createing an empty column that will be populated later. Try this and see if you get the same error:

    SELECT *,
    cms_Document_Contents_ID='',
    cms_Language_ID='',
    Title='',
    XML='',
    search_text='',
    File_XML= (SELECT TOP 1 File_XML FROM cms_Document_Contents d
    INNER JOIN cms_Document_Sections e ON d.cms_Document_Id = e.cms_Document_Id WHERE d.cms_Language_ID=9 AND e.cms_Document_Section_ID=@DocumentSectionId),
    c.active,
    c.sequence,
    c.cms_Document_Section_ID,
    c.cms_Section_ID,
    c.cms_Document_Type_ID
    FROM cms_Documents a
    INNER JOIN cms_Document_Sections c ON a.cms_Document_ID=c.cms_Document_ID
    WHERE c.cms_Document_Section_ID=@DocumentSectionId

    blindman

  3. #3
    Join Date
    Nov 2003
    Posts
    2

    Thumbs up resolved

    Stop the SQL Server, exit the Service Manager, copy the data and log files (C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDB_Data.mdf, & C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDB_Log.ldf on my machine respectivley) to your destination server, on that machine you can import (attatch) the data and log files into SQL Server with the following command in the query analyzer (substituing MyDB and the 2 paths with your equivalents):

    sp_attatch_db @dbname = 'MyDB',
    @filename1 = 'c:\path\to\datafilename.mdf',
    @filename2 = 'c:\path\to\datafilename.mdf'

    if that is successful id say your home free (refresh your database list), if not, post on this forum, they can help...

    .^sUbMSg-\/.

    see: http://msdn.microsoft.com/library/de...ae-az_52oy.asp for info on this stored procedure.

    ps: f*ck that DTS export b*llocks im sticking with the raw data files.

    The text, ntext, and image data types are invalid in this subquery or aggregate expression.

    export copy move transfer transport duplicate mirror import mdf sql database db attach stored procedure retain keep error 2000 7 6.5 8 backup restore detatch

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •