-3

I'm trying to execute a EXEC connecting a Linked Server, but it does not working using a table variable.

Is there any fix for that?

My code:

DECLARE @Table TABLE(
    ID VARCHAR(8),
    DATA DATE,
    DIA VARCHAR(2),
    MES VARCHAR(2),
    ANO VARCHAR(4)
)

INSERT INTO @Table SELECT * FROM LOCALTABLE

EXEC ('INSERT INTO SERVER2TABLE SELECT * FROM ' + @Table) AT SERVER2;

The error:

Must declare the scalar variable "@Table".

My Servers:

SQL SERVER 2008 R2 LINKED SERVER using MySQL ODBC 5.1 Driver MySQL 5.6.8

saulob
  • 615
  • 1
  • 10
  • 25
  • 1
    See the [answer from Martin Smith](http://stackoverflow.com/questions/4626292/how-to-use-table-variable-in-a-dynamic-sql-statement), if you want to interacte with your table variable both inside and outside the EXEC statement. – David Rushton Feb 24 '16 at 14:25
  • 1
    please update your question clearly,your question and comments speak differently – TheGameiswar Feb 24 '16 at 14:44
  • fixed the question. thanks – saulob Feb 24 '16 at 15:04

2 Answers2

0

try this once:i am not sure how you are populating data into table variable,but as per your op,this will work

EXEC ('
DECLARE @Table TABLE(
    ID VARCHAR(8),
    DATA DATE,
    DIA VARCHAR(2),
    MES VARCHAR(2),
    ANO VARCHAR(4)
)

INSERT INTO SERVER2TABLE SELECT * FROM  @Table') AT SERVER2;
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • An FYI for those reading this... the `@Table` in here is completely separate from any @Table declared in the rest of the code. Anything within the string is in a completely different scope, so any data in `@Table` in the rest of the code will not be available. – Tom H Feb 24 '16 at 14:25
  • Yeah, that's the problem. I'm populating the too. – saulob Feb 24 '16 at 14:34
  • EXEC is a batch seperator and that is expected and answer was given specific to op – TheGameiswar Feb 24 '16 at 14:41
0

You can't use a table variable like that in dynamic SQL, even if it were not a Linked Server. @Table is a table, not a string, so you can't just append it to a string. Even if you used it as a name, it still wouldn't work because the variable is no longer in scope within the EXEC.

Have you tried inserting directly to the linked server? I believe that the syntax is this:

INSERT INTO SERVER2...SERVER2TABLE (id, data, dia, mes, ano)
SELECT id, data, dia, mes, ano
FROM @Table

That's assuming that the column names are the same in your destination table (SERVER2TABLE).

Tom H
  • 46,766
  • 14
  • 87
  • 128
  • I'm inserting directly today. But the problem it's that using that command the 'Linked server' does a `"select * server2table"` before any query. The solution is using a `exec` command. That's why I'm trying to use it. – saulob Feb 24 '16 at 14:27
  • I'm assuming that it's doing something like `SELECT * FROM table WHERE 1=0` so that it can get the structure of the table. It's not actually trying to pull over the complete set of data. – Tom H Feb 24 '16 at 14:28
  • Thought about that. But looking at the MySQL logs the query brings all the rows (more than 29 millions) and take more than 30 minutes. – saulob Feb 24 '16 at 14:55