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
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
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 ( '.
Looks like we're missing half the error... or the full statement might help.
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 '.
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
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
I got this link, thought might be helpful :-
http://stackoverflow.com/questions/15975626/how-to-create-a-table-on-a-linked-server-with-a-variable-server-name
18 People are following this question.
