SQL Server Performance Forum – Threads Archive

Access multiple database from SQL server

I have a system which has one centralised database for all the locations and one database for each service in each location and luckily database structures are same. my company has 2 services (home health and hospice) in 3 different locations. I am working on a reporting system where I have to get the data from different databases . 1. First , I will connect to centralised database and get all the patients. 2. based on Patient service and location , I will connect to that respective database and fetch th erequired data.
I have created a database for my reporting system ( also has some basic User management tables) and created stored proc to fetch data. I am passing DBName as parameter to stored proc and createing SQL string of the query and executing it . This is working fine for queries with few joins and it is sometimes time consuming. I looked at command "USE <DBANAME>" to change the database context in stored proc ..but that is not working in stored proc . Is there a way I can shift a database contect based on parameter and execute queries against the db. Please suggest me a better solution. Thanks
Venu Yankarla

Hi,
have you tried with full name e.g. [database].[owner].[Object] .
is all the database in same sql server or it is from different server , it is not on same serever you will have to link them using spUnderScoreaddlinkedsrv,spUnderScoreaddlinkedsrvlogin. Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami

It seems that your databases resieds at diffrent locations and diffrent servers. Then Link server will be the solution for you —————————————-
http://dineshasanka.blogspot.com/

The use of USE is not possible inside stored procedure. Instead use DBname.ObjectOwner.Object. As you are passing dbname as parameter, you need to use dynamic sql. Use any of these 1 EXEC(‘Select cols from ‘[email protected]+’..tableName’)
2 EXEC(‘USE ‘[email protected]+’ Select cols from tableName’) To know more about dynamic SQL, read this
http://www.sommarskog.se/dynamic_sql.html Madhivanan Failing to plan is Planning to fail

BTW have you ever thought of managing all the data at one central location using replication? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thank you all for suggestions. I think "EXEC(‘USE ‘[email protected]+’ Select cols from tableName’)" will work for my case. I will try this today. Coming to centralising all the data at one central location …. Actually ..We have purchased a product to manage Home health and hospice operational process from a vendor and their database design is bit different. They have created multiple databases for each location and service . But all the databases are in same server and also databases has same structures. Satys ..can you suggest me a solution to centralise the databases. Thanks for help . I have one more question ….. How to retrieve XML file stored as object in my SQL server table colmun and display that as HTML. Thanks again Venu yankarla

]]>