STORED PROCEDURE SYNTAX ERROR
Please help, I would appreciate it. Below I have a simple stored procedure to return the names of my databases.
I am getting a syntax error:
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string 'PASBA-DEV '.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'SERVER1-DEV
Some of my server names contain hyphens, thus the reason for using [ ]'s in the procedure.
Code for procedure begins here:
ALTER PROCEDURE
[dbo].[usp_getdatabasenames]
@ServerName char(50)
AS
Declare @string2sql char(50)
Set @string2sql =
'Select name from ['+@ServerName+'].[master].dbo.sysdatabases order by name';
EXECUTE (@string2sql)
Software/Hardware used:
Software/Hardware used:
ASKED:
September 29, 2006 9:18 AM
UPDATED:
September 29, 2006 10:21 AM
Answer Wiki:
At a quick glance your @server is Char(50) you @string is Char(50) your string is larger than 50 since you are using char instaead of varchar
Change both to varchar and also increase your @string to allow for all characters.
HTH,
Joe
To see all answers submitted to the Answer Wiki: View Answer History.
Thanks to jaylou, my problems are solved. Whoyaa!