There are two databases in SQL Server 2005: One called "A" and another one called "A_2".
"A" is a variable name to be entered by the user, the "_2" prefix for the second database is always known. (So databases could be "MyDB" and "MyDB_2", etc)
How to access the other database from within a stored procedure without knowing the actual name and without using the 'exec' statement?
views:
1995answers:
3
A:
I don't think that it is possible.
The name is a variable and you cannot use variables as database names.
So the only way is to put the whole command to a string and exec it, which you would like to avoid.
What is the purpose of the whole thing? What happens if you name your databases on your logic, but somewhere store the link between your logic and the name entered by the user?
Biri
2008-11-04 10:23:03
Yes that would be the fallback solution:Writing a piece of software which would replace the database names with their final values in the database scripts prior to installation.I thought that maybe somebody knows another solution than using DBNAME() and EXEC...
Marc
2008-11-04 10:37:30
Unfortunately you cannot use variable as database name or field name without using exec. It's easy to do a search and replace in the database script, so I would go that way.
Biri
2008-11-04 10:42:21
+4
A:
You can try to use a new SQL Server 2005 feature, called synonyms.
You would need to CREATE SYNONYM, then compile and save the stored procedure using the synonym. This would leave you with the possibility to change the synonym "on the fly" later on. Obviously, it would still be necessary to run the CREATE SYNONYM code as dynamic SQL.
To run CREATE SYNONYM, the user must be granted the respective permission, or have ALTER SCHEMA permission. Be sure to read more about synonyms before you go that route, there are a few strings attached.
Tomalak
2008-11-04 10:48:44
Thank you very much for this hint.I have yet to test this, but it sounds very interesting!
Marc
2008-11-04 13:49:19
So I've tested this and it perfectly solves our problem.We'll define synonyms for each table in the other database which will be defined once during installation. This way we should be safe as end-users won't have too much permissions.
Marc
2008-11-10 09:01:45
A:
What I have done is create a view for each table I wanted to access (presumably the schemas are the same), and then my subsequent code just referenced the view(s). For example
if object_id('view_Table1') is not null drop view view_Table1
dim @cmd nvarchar(max)
set @cmd = 'create view view_Table1 as select * from ' + @DbName + '.dbo.Table1'
exec sp_executesql @cmd
select WhateverColumn from view_Table1