I have a stored procedure that dynamically assembles an insert statement to a remote linked server. When I try to run it, I end up with this error:
The name 'insert into [192.168.1.6].[broker].[dbo].[incoming_sg] select * from [dbo].[outgoing_sql] where ready=1' is not a valid identifier.
The thing is, if I copy the contents of that insert statement out of the error string shown in the query window, and try to run it manually from query editor, just like this:
insert into [192.168.1.6].[broker].[dbo].[incoming_sg] select * from [dbo].[outgoing_sql] where ready=1
it works just fine. (incoming_sql and outgoing_sql share the same column names)
So why does it return that error when I try to run it dynamically using exec(@cmd) where @cmd holds the above string?
There are no variables or temp table names or anything like that in the 'removed for clarity' part that are not in scope inside exec(..) - it's all literals.