question

Candy avatar image
Candy asked

Variable declare but still get "must delcare the scalar variable"

This statement gives me an error of "Must declare the scalar variable" even though I have declared it already. TD_QUERY column in the table dbo.tbl_table has the query that can be run in EDW environment. DECLARE @TableName VARCHAR(25) DECLARE @TD_QUERY NVARCHAR(MAX) DECLARE CUR_QRY CURSOR FOR SELECT TABLENAME, TD_QUERY FROM dbo.tbl_table OPEN CUR_QRY FETCH NEXT FROM CUR_QRY INTO @TableName, @TD_QUERY WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_executesql N' EXEC (@TD_QUERY) AT [linkedservername];' FETCH NEXT FROM CUR_QRY INTO @TableName, @TD_QUERY END CLOSE CUR_QRY DEALLOCATE CUR_QRY
sql-server-2005t-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
Today's word is "[SCOPE][1]" (link to a google search that returns some suitable reading on the subject) By which we mean the sections of code in which your variable is available. The problem here is that whatever you put into the "EXEC sp_executesql"'s query parameter is executed as a separate statement in a separate context, and therefore does not have visibility of your variables in the rest of your statement. What you might want to do in this situation is something like: ... DECLARE @sql nvarchar(max) WHILE @@FETCH_STATUS = 0 BEGIN SELECT @SQL = N'EXEC (' + @TD_QUERY + N') AT [linkedservername];' EXEC sp_executesql @SQL FETCH NEXT FROM CUR_QRY INTO @TableName, @TD_QUERY END ... Might need a bit of a tweak to get it working, but should show the idea of building up the full query string before passing that as a parameter to `sp_executesql` NB: You should also do something to prepare / protect yourself against SQL Injection attacks. After all, we don't want another Bobby Tables incident... http://xkcd.com/327 [1]: https://www.google.com/search?q=t-sql+scope&oq=t-sql+scope+&aqs=chrome..69i57j0l5.4479j0j4&sourceid=chrome&es_sm=93&ie=UTF-8#newwindow=1&safe=off&q=t-sql+scope+-scope_identity
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

After tweaking around the quotes, it works the way you instructed me. Thank you so much Thomas!
1 Like 1 ·
I have changed the query to SELECT @SQL = N'N''EXEC (''' + @@TD_QUERY + N''') AT [linkedserver];''' and I get the statement that I want from PRINT @SQL. When I execute the statement itself, it works fine but when I run EXEC sp_executesql @SQL, I get an error. Here is the statement and it runs: EXEC sp_executesql N'EXEC (''CREATE TABLE dbname.tablename ,NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL AS ( SELECT columns ~~~~ ;'') AT [linkedserver];' Here is the error: Incorrect syntax near 'EXEC ('CREATE TABLE dbname.tablename ,NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL AS ( '.
0 Likes 0 ·
Looks like we're missing half the error... or the full statement might help.
0 Likes 0 ·
Candy avatar image
Candy answered
That's all the error I got. It looks likes it seems missing something though. Could it be the problem of the pipe? N'EXEC (''CREATE TABLE dbname.table1,NO FALLBACK, NO BEFORE JOURNAL,NO AFTER JOURNAL AS (SELECT DISTINCT NPA||NXX||LINE AS BTN,CASE WHEN CID IN (''''ABC'''',''''DEF'''') THEN CID ELSE BTN END FROM table1 AS tbl1 INNER JOIN dbname.table2 AS tbl2 ON tbl1.ID = tbl2.ID WITH DATA INDEX (ID);'') AT [linkedserver];' Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'EXEC ('CREATE SET TABLE dbname.table,NO FALLBACK, NO BEFORE JOURNAL,NO AFTER JOURNAL AS (SELECT DISTINCT '.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

what do we want to do with this snippet ---npa||nxx||line. is it something 1.) related to named pipe concept (if it is than please through some light of what do you want to do with the code because I have studied about it) or 2. )oracle equivalent of concatenation
0 Likes 0 ·
what is the meaning of this snippet ---- NPA||NXX||LINE is it something related to 1. named piped concept, if yes could explain what is it and why it is used, because it would be a learning curve for many like me. or 2. oracle quivalent of concatenation
0 Likes 0 ·
Thank you erlokeshsharma for trying to find the solution. The link is very helpful even thought I can't use it this time for this task (my linked server is on EDW), but it will sure a good reference to use in the future.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.