SQL Query - linked server

I created a sql script that copy items from one database to another.  Both databases locate in different box.  

If I'm using the script for databases that's in the same box, I have no issue.  However, if the databases are from different boxes, I'm getting following error.

Could not locate entry in sysdatabase for database DB1.  No entry found with that name.  Make sure that name is entered correctly.

Here's a sample of the script.  I think it has to do with "Use".

USE [SVRA].DB1

IF EXISTS(SELECT ID FROM USER_APP)
BEGIN
INSERT #APP
     SELECT ID, NAME
     FROM USER_APP
     WHERE ID = @ID
END

USE DB2
IF EXISTS(SELECT ID FROM #APP)
BEGIN
INSERT USER_APP
    SELECT ID, NAME
    FROM #APP
    WHERE ID = @ID
END

Open in new window


So DB1 is on another server box.  I want to take what's there and copy to DB2.  If I don't use link server and both databases is located in the same box it works fine.  It's when I'm trying to get data from one database into another in different box.
holemaniaAsked:
Who is Participating?
 
devlab2012Commented:
check following:

1. Have you created a linked server for [SVRA].

2. Don't use the "USE" statement for DB1. Try the following code:

USE DB2
GO


IF EXISTS(SELECT ID FROM [SVRA]..USER_APP)
BEGIN
INSERT #APP
     SELECT ID, NAME
     FROM [SVRA]..USER_APP
     WHERE ID = @ID    --how will you pass value of @ID????
END
GO

IF EXISTS(SELECT ID FROM #APP)
BEGIN
INSERT USER_APP
    SELECT ID, NAME
    FROM #APP
    WHERE ID = @ID             --how will you pass value of @ID????
END
0
 
holemaniaAuthor Commented:
Thanks that works.  I'm declaring the @ID parameter and didn't include it in the query.  However, your suggestion fixed my issue.
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.