Post reply

Parameter with \ (backslash) not parsing properly

  • January 21, 2011 at 12:03 pm

    #388686

    I have a T-SQL script on a SQL Server 2000 box which I pass a server name to as a parameter (@REMSServer). The server is a SQL server instance, so there is a backslash in the server name: [NYSQL004\NYSQL004]. Here are the two lines of code:

    SET @SQLString = N'SELECT @CountOUT = COUNT(*) FROM [' + @REMSServer + '].rems.dbo.[Order] WHERE OrderID = ' + CAST(@OrderID AS varchar) + ' AND UserID = ' + CAST(@UserID AS varchar)

    EXECUTE sp_executesql @SQLString, @Parms, @CountOUT = @NumberofRows OUTPUT;

    This works fine in our development environment, where the SQL Server is NOT an instance, but in production, I get this run-time error:

    Could not find server '[NYSQL004\\' in sys.servers

    Something is keeping the server name from being parsed correctly. Can anyone help?

    Thanks!

  • January 21, 2011 at 12:09 pm

    #1276726

    Looks like the server name in @REMSServer has TWO slashes in it, the way C languages tend to escape the slashes.

    can you PRINT @REMSServer and confirm the value is NYSQL004\NYSQL004 and not NYSQL004\\NYSQL004?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 21, 2011 at 1:26 pm

    #1276769

    No, that's not the solution. The solution was embarrassingly obvious - I just kept missing it. My parameter length was only 10. Changing it to 30 made the problem go away. Sorry for wasting your time. :Whistling:

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply