Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
yellowman
Starting Member
25 Posts |
Posted - 2012-11-20 : 10:22:25
|
I have a small piece of code that is kicking off the error:Msg 170, Level 15, State 1, Line 29Line 29: Incorrect syntax near ')'.Maybe the Red Bull just hasn't kicked in this morning, but where did I go wrong in the code below? I know the error is on the sp_executesql line somewhere.EXEC ('INSERT INTO ' +@DB_Name+ '.CQ_DBO.Schema_Version (Version_Number) EXEC sp_executesql ' +@command+) |
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-11-20 : 11:08:24
|
the last +-sign... Too old to Rock'n'Roll too young to die. |
|
|
yellowman
Starting Member
25 Posts |
Posted - 2012-11-20 : 14:16:29
|
Now I get an error that says: Procedure 'sp_executesql' expects parameter '@statement', which was not suppliedI think I have to throw an N in my statement somewhere. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-20 : 14:24:20
|
Yes, you do. Like shown below. BTW, if possible avoid dynamic SQL - because of security considerations.EXEC ('INSERT INTO ' +@DB_Name+ '.CQ_DBO.Schema_Version (Version_Number) EXEC sp_executesql N''' +@command+'''') |
|
|
yellowman
Starting Member
25 Posts |
Posted - 2012-11-21 : 11:12:32
|
Looks like I am closer to getting it working. I made the changes above and now it gives me an error that says:Msg 170, Level 15, State 1, Line 3Line 3: Incorrect syntax near '%'.I have to laugh because there is no % on line 3.....or anywhere near line 3. I have a percent being used as a wildcard in my select statement (@command), but if I PRINT that command and run the output directly it works just fine. Here is the entire script.DECLARE @DB_Name nvarchar(200)DECLARE @command nvarchar(500) DECLARE database_cursor CURSOR FORSELECT nameFROM master.dbo.sysdatabasesWHERE UPPER(name) LIKE 'CQ_DataComm_PROD'OPEN database_cursorFETCH NEXT FROM database_cursor INTO @DB_Name WHILE @@FETCH_STATUS = 0BEGIN -- Create table if it doesn't already existsEXEC ('USE ' +@DB_Name+ ' IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''Schema_Version'')CREATE TABLE ' +@DB_Name+ '.dbo.schema_version( version_number [nchar](20) NULL) ON [PRIMARY]')--Setup SELECT statement to get the version numberSELECT @command = 'SELECT SUBSTRING(program,PATINDEX(''%Jumpstart Version%'',program)+18,PATINDEX(''%[^0-9.-]%'', SUBSTRING(program,PATINDEX(''%Jumpstart Version%'',program)+18,8000))-1) AS schema_version FROM ' +@DB_Name + '.dbo.scriptdef WHERE (program LIKE ''%sub JAT_Version%'')' -- INSERT the schema version into the Schema_Version tableEXEC ('INSERT INTO ' +@DB_Name+ '.dbo.Schema_Version (Version_Number) EXEC sp_executesql N''' +@command+'''') --Get the next database in the cursor FETCH NEXT FROM database_cursor INTO @DB_NameEND--CleanupCLOSE database_cursorDEALLOCATE database_cursor |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-21 : 11:34:39
|
Substitute each pair of single quote in your @command statement with four single quotes. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-11-21 : 11:35:32
|
The fun of doing dynamic sql. If you change your EXEC statements to PRINT statements you wil begin to see the issue. Here is the output for reference:USE DWTest IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Schema_Version')CREATE TABLE DWTest.dbo.schema_version( version_number [nchar](20) NULL) ON [PRIMARY]INSERT INTO DWTest.dbo.Schema_Version (Version_Number) EXEC sp_executesql N'SELECT SUBSTRING(program,PATINDEX('%Jumpstart Version%',program)+18,PATINDEX('%[^0-9.-]%', SUBSTRING(program,PATINDEX('%Jumpstart Version%',program)+18,8000))-1) AS schema_version FROM DWTest.dbo.scriptdef WHERE (program LIKE '%sub JAT_Version%')' |
|
|
yellowman
Starting Member
25 Posts |
Posted - 2012-11-21 : 13:38:33
|
That's it! Everything works now. I didn't pick up the quote problem when I initially printed @command to look at it. Thanks everyone. |
|
|
|
|
|
|
|