Passing data between two databases in SQL Server

SQL Server Ask the Experts

2585 pts.

Tags:

SQL Server database

SQL Server stored procedures

Is it possible to have a stored procedure in a SQL Server database look at tables in another SQL Server database by sending the second database name as a parameter to the stored procedure?

Asked: April 2, 2009 4:48 PM Last updated: June 13, 2013 4:23 PM

Answer Wiki

Last updated: June 13, 2013 4:23 PM GMT

Michael Tidmarsh62,820 pts.
History
Contributors
- Ordered by most recent
- Michael Tidmarsh62,820 pts.
- carlosdl84,495 pts.
- Denny Cherry68,410 pts.

Thanks. We'll let you know when a new response is added.

Yes, if you have a linked server from the database server that you are connecting to, to the other database server and the login which you use to access the remote server via the linked server has access then you can access the remote database.

However using a variable for the database or server name would require using dynamic SQL.

Discuss This Question: 3 Replies

carlosdl Apr 2, 2009 6:28 PM GMT

It seems that I was typing an answer at the same time than Mrdenny, and one of the answers was overwritten. ---------------- You would need to configure a linked server in order to be able to query information from the other database, and the stored procedure would need to use dynamic sql. When you execute a distributed query against a linked server, you need to include a fully qualified, four-part table name for each data source to query. This four-part name should be in the form linked_server_name.catalog.schema.object_name. ----------------

84,495 pointsBadges:

report
2747031103112747 Nov 11, 2009 6:03 PM GMT

I am having an issue similar as this one. I was asked to transfer "20%" data from our current databases to a new development server. I can not simply use Import/Export functions or Data Transfer Functions from SSIS because of more than 15 criteria for the "20%" data, the worst case we are not allowed to use the linked server neither. Are there any other alternative ways ?

15 pointsBadges:

report
Denny Cherry Nov 12, 2009 7:16 PM GMT

You should start a new thread as your question don't have anything to do with the original question and will be a very different answer.

68,410 pointsBadges:

report