Solved

TSQL dynamic insert - not a valid identifier

Posted on 2009-04-10
4
1,317 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 143

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:udaya kumar laligondla
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
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…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

617 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