SQL Server Performance

CHAR(13) Carriage Return - Unexpected Results in 2005

Discussion in 'SQL Server 2005 General DBA Questions' started by DBADave, Oct 24, 2007.

  1. DBADave New Member

    select 'sp_dropdevice ' + name + char(13) + 'GO'
    from sysdevices
    Where status = status & 16
    In SQL 2000 the above script can be used to generate the syntax needed to drop all backup devices. The CHAR(13) simply provides a carriage return to place the GO command on a separate line. Copy and paste the results to a query window and you can successfully execute the code.
    In SQL 2005 the above script generates the syntax, however after copying and pasting the results to a query window and executing the script you receive the following error message.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'GO'
    This behavior only pertains to system stored procedures. I suspect the carriage return is treated differently in 2005, but have not been able to find any documentation to confirm this theory. Any idea why this functions differently in 2005? Is their an option that needs to be set in 2005 Query Analyzer?
    Thanks, Dave
  2. DBADave New Member

    I just answered my own question. In 2005 you need to also supply CHAR(10) for Line Feed.
    select 'sp_dropdevice ' + name + char(13) + char(10) + 'GO'
    from sysdeviceshe
    where name like '%backup'
    Hope this helps someone.
    Dave
  3. Adriaan New Member

    Funny, I would never think of not adding CHAR(10) after CHAR(13).
  4. Madhivanan Moderator

    Also you should set result mode of QA to text mode to see GO in seperate line
  5. DBADave New Member

    I never needed CHAR(10) in 2000 so my code excluded it. I agree you always think of using carriage return and line feed together, but for whatever reason both were not required in 2000.
    In regards to Query Analyzer this particular issue pertains to a script I have that automatically creates database backup jobs and corresponding alerts. CHAR(13) is used to make the job step more readable. I'll add CHAR(10) to my scripts, but I just thought it was odd that the feature worked one way in 2000 and a different way in 2005 and I have yet to find an explanation for the change.
    Dave
  6. FrankKalis Moderator

    [quote user="DBADave"]
    I never needed CHAR(10) in 2000 so my code excluded it. I agree you always think of using carriage return and line feed together, but for whatever reason both were not required in 2000.
    In regards to Query Analyzer this particular issue pertains to a script I have that automatically creates database backup jobs and corresponding alerts. CHAR(13) is used to make the job step more readable. I'll add CHAR(10) to my scripts, but I just thought it was odd that the feature worked one way in 2000 and a different way in 2005 and I have yet to find an explanation for the change.
    Dave
    [/quote]
    I wouldn't take too much time thinking about a possible explanation. It's a "feature". [:)]

Share This Page