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
Today's word is "[SCOPE]" (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...
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
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 '.