Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

How do I use Identity_Insert on a table from a dbase located on an entirely different server?

I am working on developing DNN modules for my company intranet to replace the need for Access to perform certain functions. My intranet is located on 1 server which is seperate from my main SQL server. I developed all the stored procedures with a copy of the live database which I placed on the same server I was developing the modules. I got everything working perfectly, but then once I pointed the path for all procedures to the live database I get an error on the 2 insert procedures which require an Identity_Insert statement. To get the path to work on SQL I had to place brackets around the server and database name to treat it as a single argument since it seems Identity insert will only allow 3 arguments ->[UCLISERVER1.Engineering01].dbo.Activity. However, now that everything is hitting the live database I get the error {Cannot find the object "UCLISERVER1.Engineering01.dbo.Activity" because it does not exist or you do not have permissions.} when I try to run the code that calls the insert procedure. If I rem out the Identity_insert lines I just get the generic error telling me I need them. Does anybody know how to get around this?
0
casper114
Asked:
casper114
  • 2
1 Solution
 
aaronakinCommented:
1. Then you put brackets around UCLISERVER1.Engineering01, you are essentially telling SQL Server that "UCLISERVER1.Engineering01" is your database name.  To specify it as a server & database, you'd have to use "[UCLISERVER1].[Engineering01]".

2. You can use IDENTITY_INSERT for a remote server.  This is why it only allows a 3-part name.  You'll probably have to call an SP on the live server to do the insert in order to make this work.
0
 
casper114Author Commented:
Thanks Aaron. Created insert SP's under the live database and once I changed the call to those procedures everything worked great. I'll have to remember that when using the Identity_Insert in the future.
0
 
casper114Author Commented:
Thanks for your help. Once I was able to sit down and implement it, things went beautifully.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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