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?
casper114Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
aaronakinConnect With a Mentor Commented:
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
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.

All Courses

From novice to tech pro — start learning today.