Q
Problem solve Get help with specific problems with your technologies, process and projects.

Executing distributed queries

I have a situation where in we are using 32 SQL Server 2000 Databases. Each database will be linked to all the other 31 databases. I have some transactions which require the use of distributed queries (Distributed Transactions), but my problem is that I won't know which database to connect to and execute the query until runtime. So is it possible to dynamically pick up the server name at runtime and execute distributed queries (can include cursors)?

Yes, it is possible. However, it sounds like the aggregation of the data in all 32 databases is the entire picture of your data. If so, you would be much better off using distributed partitioned views. Why? Because to get this to work the way you want to, you can going to need to write the equivalent of what DPV provide in functionality only you are going to have to write and maintain the code sitting on top of SQL Server. Microsoft has already built this functionality, tested it, optimized it, and placed it into the product.

 

For More Information

This was last published in April 2002

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close