Solved

TSQL dynamic insert - not a valid identifier

Posted on 2009-04-10
4
1,306 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

738 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