TSQL dynamic insert - not a valid identifier

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.

Thanks!



PMH4514Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
please double check that you really use exec(@sql) and not exec @sql  
the () are important, otherwise exec will try to run a stored procedure named @sql (aka the value of @sql9
0
 
udaya kumar laligondlaTechnical LeadCommented:
replace the [192.168.1.6] with the servername and test
0
 
PMH4514Author Commented:
Love it when they're simple! That did the trick. thanks
0
 
PMH4514Author Commented:
>>replace the [192.168.1.6] with the servername and test

My VPN  admin says nameserver resolution is not supported and asked that I specify the remote servers by IP address.  (firewalls and ports or some such reasoning..)

Anyway, the parens around @cmd solved the problem.
0
All Courses

From novice to tech pro — start learning today.