Solved

TSQL dynamic insert - not a valid identifier

Posted on 2009-04-10
4
1,284 Views
Last Modified: 2012-05-06
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
Comment
Question by:PMH4514
  • 2
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 24116672
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
 
LVL 12

Expert Comment

by:udayakumarlm
ID: 24116774
replace the [192.168.1.6] with the servername and test
0
 

Author Comment

by:PMH4514
ID: 24116783
Love it when they're simple! That did the trick. thanks
0
 

Author Comment

by:PMH4514
ID: 24116802
>>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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question