question

suganthi avatar image
suganthi asked

Db link in sqlserver 2005

I have three databases in sql server 2005. I want to retrieve values of the another database table into one database. Say For an example three databases are there DB1,DB2 and DB3..In Db1 I want to acces the DB2 table..Please explain me what is the syntax for Db link and how to use it in Stored procedure. Thanks, Sugan.
sql-server-2005cross-database
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
Assuming that you are accessing a local DB (Not remote database) For accessing table of DB2 From DB1 use the following query
 
-- If your DB is located on the same server then try the following code
SELECT  FROM ..
eg : SELECT ID FROM DB2.dbo.Employee

If you are trying to access a remote database then you should either create a linked server or try using OPERNROWSET function
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo avatar image
Leo answered
Here is how you can retrieve the data from DB2.
Select FieldName From DB2.dbo.DB2TableName

Get the data from DB2 and insert into DB1.
Insert Into DB1.dbo.DB1TableName (FieldName)
Select FieldName From DB2.dbo.DB2TableName
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
A good way to do this is by using a synonym object. This allows you to decouple your logic from the database name. So, for example, to access db2.dbo.table - You could: CREATE SYNONYM dbo.tableInDB2 FOR db2.dbo.table Then SELECT * FROM dbo.tableInDB2 This way, your stored procedure only depends on the locally created synonym, and if the name of DB2 changes, or it moves to another server, then you only need to redefine the synonym - and not re-write all your procedures that use it.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 creative solution.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.