Accessing Multiple DBs with SP's

[robertfah]

Our company currently has 35+ DB's in SQL 2000 and table definitions and relationships are identical. We are required to keep customer data seperate, which is why we have so many DB's with the same setup. We currently have a website that all customers access. As they log in, the website dictates what DB to read based on some information.

So we have 1 "master" DB (SCMaster) which holds all non-customer specific data, and all the SP's for the website to use. At the time, the only way we knew how to access multiple DB's (dynamically) through a SP was to create a string and execute the string. So basically all our SP's have a parameter of "DBName" and and other that may be important and then we create a string that reads the customer specific DB, like so:

CREATE     PROCEDURE [dbo].[ASC_GetIdentifier]
	@DBName varchar(50),
	@SiteID int

 AS

	DECLARE 	@SQLString varchar(8000),
			@TableName varchar(50)
	
	SET @TableName = 'SC_' + @DBName + '.dbo.'
	
	SELECT @SQLString = '
	SELECT UniqueIdentifier
	FROM ' + @TableName + 'Sites
	WHERE SiteID = ' + convert(varchar, @SiteID)

EXEC(@SQLString)

GO

That basically gets a field called "Unique Identifier" in the DB that's being passed.

All this is great but there are many, many issues with doing it this way and was looking for another way to accomplish this. Besides having every SP reside in each DB (there are currently 346 SPs...times that by 35, ugh), is there a way that we can access multiple DB's without having to write a string and execute it? I don't know much about SQL Enterprise and it's capabilities, which is why I am asking but what about Linked Servers, etc?

Thoughts, ideas, comments?

 

[markros]

If the DB's are on the server, there is slightly different way:

declare @ExecSQL varchar(400)

set @ExecuteSQL = quotename('SC_' +@DbName) + '.sp_ExecuteSQL'

execute @ExecuteSQL 'Select UniqueIdentifier from Sites
where SiteID = @SiteID', N'@SiteID int', @SiteID

That's the idea I learned from Erland Sommarskog.

PluralSight Learning Library

 

[SQLBill]

Do the customers have access to more than one database?
How does the website pass the connection string to the database - is it the customer login or is it an application login?

If the website passes an application login AND it is different per database, you could have a list of logins and their authorized database in the SCMaster database. Then the stored procedure could pull the database name from there based on the login being passed. If the website passes the user's login, that would be harder to do as you could have hundreds or thousands of logins to keep track of.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875