Copy-paste issue in SQL Server Management Studio

问题:

I have a table in SQL Server 2008 and one of the column is called Query with nvarchar(MAX) data type.

This contains a query built in the code which calls some function, something like this:

    SELECT *
    FROM dbo.SearchPropertiesWithAddress(1132,  -- @LoggedInPersonID
                              NULL,  -- @PropertyID
                              '',  -- @PCode
                              '32,16',  -- @PropertyStatusesIDS
                              '',  -- @PropertyTypesIDS
                              NULL,  -- @ResearchStatuses
                              '',  -- @ZipCodeIDS
                              NULL, -- @NumBedroomsFrom
                              NULL, -- @NumBedroomsTo
                              3, -- @LivingSizeFrom
                              NULL, -- @LivingSizeTo
                              NULL, -- @LotSizeFrom
                              NULL, -- @LotSizeTo
                              NULL, -- @IsOnTheMLS
                             '') -- @strAddress
    ORDER BY CAST(PCode As int)

I have a very strange copy paste problem: what you see above is taken with copy paste from EDIT mode of one record. If I paste here (or in other notepad, word etc file) the PASTE works nice but if I try in EDIT mode, to paste any Query field content, from one RECORD to another, it puts nothing.

If I copy paste from VIEW mode (select mode in SQL) it copies like this:

      SELECT *          FROM dbo.SearchPropertiesWithAddress(1132,  -- @LoggedInPersonID                                    NULL,  -- @PropertyID                                    '',  -- @PCode                                    '32,16',  -- @PropertyStatusesIDS                                    '',  -- @PropertyTypesIDS                                    NULL,  -- @ResearchStatuses                                    '',  -- @ZipCodeIDS                                    NULL, -- @NumBedroomsFrom                                    NULL, -- @NumBedroomsTo                                    3, -- @LivingSizeFrom                                    NULL, -- @LivingSizeTo                                    NULL, -- @LotSizeFrom                                    NULL, -- @LotSizeTo                                    NULL, -- @IsOnTheMLS                                   '') -- @strAddress          ORDER BY CAST(PCode As int)  

Now, I can PASTE this in EDIT mode of any record but the Query will no longer works when it will be called by the system... (It throws an error while executing: Incorrect syntax near ',')

I am very sure it's something about COPY-PASTE carriage returns or spaces issue, but I've spent 2 hours and I cannot figure out what can be the problem.

Why I need this? Some of my queries needs to be modified and I cannot modify them directly in EDIT mode of the table so I have to copy it to notepad+6, do the modifications and paste them back...but after paste (even if I do not modify anything) the query no longer works...

Can you guys see any ugly issue overhere?

回答1:

Seems to work fine if I see the new lines chars in notepad++:

View -> Show Symbol -> Show End Of Line will display the end of line characters.

and also to activate UNIX format (Edit - EOL - Unix format) to copy paste propertly the query after editing it.