May 13, 2003 at 3:13 pm
Running this dynamic sql construct gives me an error because somehow it does not accept my variable @table or it is recognised differently. If run directly no problem but apparently the single quotes are a problem.
Print @Table (db and table name: opms..transactions)
Select @sql = 'Select * From Payments where not exists (Select * from Hist Where TableName = ' + @Table + ' and sYear = '+ @Year + ' and sMonth = ' + @Month + ')'
Print @sql
EXEC (@sql)
opms..Transactions
Select * From Payments where not exists (Select * from Hist Where TableName = opms..Transactions and sYear = 2003 and sMonth = 12)
Server: Msg 1004, Level 15, State 1, Line 1
Invalid column prefix 'opms.': No table name specified
Any idea?
mipo
May 13, 2003 at 3:21 pm
Take that SQL query you're printing directly and you'll see the same error.
If TableName is a column in the Hist table, and it contains character data, then you probably need to wrap opms.Transactions in quotes (use doubled single quotes ('') to signify to SQL Server that you're intending a single quote (') to be part of the string and not a string delimeter).
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
May 13, 2003 at 4:02 pm
This worked by putting the quotes like:
Where TableName = ''' + @TableToBeCleaned + ''' and sYear etc.
Thanks
mipo
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply