Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1336
  • Last Modified:

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!



0
PMH4514
Asked:
PMH4514
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now