September 17, 2007 at 11:17 am
Need help getting a SQL statement to run.
This works fine:
SELECT rtrim(Name) FROM Dataload.dbo.sysobjects
WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1 ORDER BY Name
This fails:
DECLARE @sql VARCHAR(1000)
SELECT @sql='SELECT rtrim(Name) FROM ' + 'dbo.sysobjects'
+ ' WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1 ORDER BY Name'
EXEC(@sql)
thx in advance..
September 17, 2007 at 11:34 am
The error is subtle to spot but it will cause you problems every time. The best way to trouble shoot these types of problems is to print the statement rather than execute it. By doing so you will be able to see exactly what is causing the problem. Notice that I placed a line "print @SQL" before the execute and commented out the EXEC statement. The problem was the single qoutes around the N'IsUserTable' variable. SQL treated it as a literal statement. By adding an additional qoute N''IsUserTable'' the statement is correct.
DECLARE @sql VARCHAR(1000)
SELECT @sql='SELECT rtrim(Name) FROM ' + 'dbo.sysobjects'
+ ' WHERE OBJECTPROPERTY(id, N''IsUserTable'') = 1 ORDER BY Name'
print @SQL
--EXEC(@sql)
John
September 17, 2007 at 11:41 am
John - thx a million.. That worked fine!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply