Home > IT Answers > SQL Server > Passing data between two SQL Server databases
SQL Server Ask the Experts
2535 pts.
Q:
Passing data between two SQL Server databases
SQL Server stored procedures, SQL Server databases
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: Apr 2 2009  4:48 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
Carlosdl
34805 pts.
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • Bookmark and Share
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.
Last Answered: Apr 2 2009  6:24 PM GMT by Carlosdl   34805 pts.
Latest Contributors: Mrdenny   50690 pts.
  •   
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Carlosdl   34805 pts.  |   Apr 2 2009  6:28PM 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.
—————-

 

2747031103112747   15 pts.  |   Nov 11 2009  6:03PM 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 ?

 

Mrdenny   50690 pts.  |   Nov 12 2009  7:16PM 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.