how to dynamically set a database name

[raindogs]

I have an application with multiple databases using the same schema on the same instance of SQL Server that are storing different information for different groups of users. The users can have access to more than one database. There is a central database that keeps track of which databases the users have access to and a front-end built in ASP.NET. Right now I am using the application to allow the users to select which database they are currently using and then re-writing the connection strings to point them to the right place. What I would like to do is keep track of which DB they are using with an “active_user_database” table in the central DB and pass that on through a view. The syntax would be something like

select * from <database name>.occupants

where <database name> is a dynamic value that I would pull out of the “active_user_database” table.

The goal is to set it up so that the connection from the ASP.NET application only hooks into the central database and allows the user to query the tables in the other databases without having to re-write the connection string. Can anyone suggest a way through this?

Thanks,
Alex

 

[hwkranger]

Sure, use Dynamic SQL and use a Procedure instead of a view.

Not that this is the best solution -- if I were you I would avoid this and use your FE to handle the logging of which DB they are using.



Randall Vollen
Merrill Lynch

 

[hwkranger]

Correction:

I would normalize into a central schema. Fixed. (My guess this isn't an option though)

Randall Vollen
Merrill Lynch

 

[bborissov]

Alex,
I am not familiar with ASP,but in my application I do the following:
The connection string pointed to Common Database,
I know where every table is placed, so I have a base class that have a property that shows from where I must get the result. So before I post something to the SQL server I parse the string and add Database infront of the table name. Par example:

the cursor class has following query string:

SELECT * from MyTable

I know that MyTable is from the UserDatabase currently used and set its property WhatDataBaseIMustUse to
MyClass.WhatDataBaseIMustUse = 'SomeDataBase'

In my method where I do the query I parse the string and the actual code executed is:

SELECT * from SomeDataBase.dbo.MyTable

but this is your FrontEnd job, not SQL Server one. Other way is to pass everything to SP with DB name and the query string and to parse the string in that SP (in your Common base).

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP