Using table-variables in dynamic sql

Question

Using table-variables in dynamic sql

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Execute statements run under a different context, and table variables are not shared. You could try using a temporary table.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 2

Also see http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=149&messageid=393025

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

John Rowan SSC Guru Points: 56440 More actions · Answer 3

Why does this need to be dynamic SQL? I don't see anything in your code that would require dynamic SQL to accomplish.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Edmund Ward SSChasing Mays Points: 620 More actions · Answer 4

John, thanks for your reply. I've simplified the query significantly to focus on the part that's causing the error. Both the linked server and the main table (the vs alias) are dynamically added to the query in my full version. I've printed out the SQL statement just before execution and using it statically (i.e. not dynamically) it works fine.

Edmund Ward SSChasing Mays Points: 620 More actions · Answer 5

Thanks, Andras. Using temporary table seems to work OK.