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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Variable Problem

Author  Topic 

yellowman
Starting Member

25 Posts

Posted - 2012-11-12 : 15:52:09

I keep trying to run the piece of code below on one of my databases and I am getting and error that says the following:

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '8000'.

Any ideas? Its probably something simple that I am overlooking (like a missing tick somewhere).


DECLARE @Database varchar(100)
DECLARE @Command nvarchar(200)
SELECT @Database = 'Bank_PROD'
SELECT @Command = 'SELECT ' +@Database+ ' AS dbname,Left(SUBSTRING(dbo.program, PATINDEX(''%[0-9.-]%'', dbo.program), 8000), PATINDEX(''%[^0-9.-]%'', SUBSTRING(dbo.program, PATINDEX(''%[0-9.-]%'', dbo.program), 8000) + ''X'')-1) AS Schema_Version
FROM ' +@Database + '.dbo.scriptdef
WHERE (dbo.program LIKE ''%sub schema_Version%'')'
EXEC(@Command)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-12 : 16:01:22
Your query does not have a FROM clause even though the SELECT clause refers to column names. You will see what I mean if you print out the dynamic SQL.
...
WHERE (dbo.program LIKE ''%sub schema_Version%'')'
PRINT @Command
EXEC(@Command)
Go to Top of Page

yellowman
Starting Member

25 Posts

Posted - 2012-11-12 : 16:13:32
I forgot about the PRINT command for debugging......thanks.

Looks like this piece got truncated:

+ ''X'')-1) AS Schema_Version
FROM ' +@Database + '.dbo.scriptdef
WHERE (dbo.program LIKE ''%sub schema_Version%'')'

Does the + sign get some sort of special treatment when its a string literal?
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-12 : 16:47:31
You need to make your variable for the Command larger, if you select the LEN of the variable you will see that it is full (200):
DECLARE @Database varchar(100)
DECLARE @Command nvarchar(200)
SELECT @Database = 'Bank_PROD'
SELECT @Command = 'SELECT ' +@Database+ ' AS dbname,Left(SUBSTRING(dbo.program, PATINDEX(''%[0-9.-]%'', dbo.program), 8000), PATINDEX(''%[^0-9.-]%'', SUBSTRING(dbo.program, PATINDEX(''%[0-9.-]%'', dbo.program), 8000) + ''X'')-1) AS Schema_Version
FROM ' +@Database + '.dbo.scriptdef
WHERE (dbo.program LIKE ''%sub schema_Version%'')'

SELECT LEN(@Command)
PRINT(@Command)
Go to Top of Page

yellowman
Starting Member

25 Posts

Posted - 2012-11-12 : 16:57:31
I can't believe I overlooked the most obvious. I didn't even think to look at that. Thanks so much for the help.
Go to Top of Page
   

- Advertisement -