September 6, 2009 at 1:31 pm
I have a question about how to conect to a variable (server.database) from another within a stored proc ..e.g.,
For example assume I am connected to server1.dbase1 database and want to query or update server2.dbase2 ....
I know I can specify a server/database in the proc in dbase1 ..e.g.,
select * from server2.dbase2.some_table ....
However, in my environment the server2_name and dbase2_name values might vary from site to site....
Can anybody tell me how I might either store the 2nd server name and db name in a table or pass them as a parameter so that the stored proc would not need to be modified to connect to a different server/db ....e.g., this is sort of what I have in mind...
The values for @server2_name, @dbase2_name would be either be passed via the SP call or looked up from a table in the main database ... such as
[font="Courier New"]
create procedure some_stored_proc
@server2_name varchar(100),
@dbase2_name varchar(100)
as
begin
select * from @server2_name.@dbase2_name.some_table (etc etc.)
end[/font]
or
[font="Courier New"]
create procedure some_stored_proc ( @the_serverkey as integer)
as
declare @server2_name varchar(100);
declare @dbase2_name varchar(100);
select @server2_name = server2_name,
@dbase2_name = dbase2_name
from db_Table
where server_key = @the_serverkey;
--- then something like ..
select * from @server2_name.@dbase2_name.some_table
where ....(etc etc.)
[/font]
--- where the @server2_name and @dbase2_name would be variable ....
I've tried these (and many variants)...but SQL Server won't let me (as best as I can tell)
specify these values as variables......
September 6, 2009 at 1:50 pm
1. Should the parameters (@db + @server) act as constants/config values, where setup once / use many is applicable per each server or that per each query you need to deduct the values from a table?
2. Is it a closed list or that new values might come up from time to time?
There are basically 2 available solutions:
1. Concatenation of strings and then executing them as OPENQUERY or OPENROWSET
2. Using synonyms, but that requires some more details from you
September 6, 2009 at 3:36 pm
From what you have stated so far - I believe synonyms (or views) will work for you. I prefer synonyms because they are a little easier to manage and change as needed.
If you need a dynamic solution that can change during any execution of code - you are going to have to rely on dynamic SQL. If the solution is static, that is - doesn't change once set up - or doesn't change often (for example, updating hardware), then using synonyms (or views) will work perfectly.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 6, 2009 at 5:30 pm
I know nothing about synonyms before so this is a good point to learn.
My environment is that I am deploying a stored proc at multiple locations (different customer sites). I can't rely on the server name, (and to a lesser degree the database name) being the same, or I might want to connect to a test vs. production server, which may or may not have the same server or database names.
In the past I'd just create an ODBC connection to the alternate server/database ... but I'd like to try and have the communication between the two databases be handled by the stored procedure in this case without a separate ODBC connection for the second database, and I don't want to have to distribute customized versions of the stored procedure if possible.
If I understand how synonyms might work in my situation it sounds like I still might need a combination of dynamic sql and synonyms.....
First, I would code the stored proc using the synonym ... such as myRemoteServer and then have a procedure in my actual client code that would create the synonym 'on the fly' from a table entry in my main database ... in other words .. something like:
I could draw the values for 'sever2' and 'database2' from a table in my main database, then assemble a text string that contained these values ... as in :
server2 = "someserver"
database2 = "somedatabase"
sql = "CREATE SYNONYM MyRemodeServer FOR " + server2 + "." + database2
xyzconnection.execute SQL <--to create the synonym....
and finally execute that SQL statement to create the synonym in my database ... and then call my stored proc which references that synonym ...
in my stored proc I'd simply refer to MyRemoteServer whereever I would have had to refer to server2.database2 ....
Thanks for the suggestion regarding synonyms.
September 6, 2009 at 6:01 pm
Well. it was a good idea ... but evidently the synonym has to refer to a 'final' object (table or view or stored proc)... and the [servername].[databasename] portion is not enough ....
I created a synonym in my main database using
create synonym xyz for [server2].[database2]
Which it accepted without complaint...
Then I tried (from my main database)
select * from xyz.sometablename
and it tells me 'invalid object name 'xyz.sometablename'
When I create the synonym using .
create synonym xyz for [server2].[database2].[sometablename]
then
select * from xyz
it works just fine....
It seems like you need one synonym for each table/view in the other database and can't just have a synonym refer to the higher level object (server.database) only.
September 6, 2009 at 7:24 pm
In your situation, you need a combination of linked server and synonyms. You have to have the linked server for the connection to the other system/database unless the other database is hosted locally which is also a possibility.
Since you cannot rely on the name of the other server or database, you would create the synonym the same, but then you can alter the synonym for each site. For example, if you deploy to site A and their remote server/database is called ServerA/siteA, and you deploy to Site B (serverB/siteB) and you deploy to site c (localserver/sitec), you will do the following:
At Site A - create the linked server to ServerA. Then you create the synonyms for the remote objects as:
CREATE SYNONYM remote.ObjectA FOR ServerA.SiteA.schema.objecta;
CREATE SYNONYM remote.ObjectB FOR ServerA.SiteA.schema.objectb;
...
At Site B - create the linked server to ServerB. Then create the synonyms as:
CREATE SYNONYM remote.ObjectA FOR ServerB.SiteB.schema.objecta;
CREATE SYNONYM remote.ObjectB FOR ServerB.SiteB.schema.objectb;
...
And for Site C - you don't have a linked server, so you create the synonyms as:
CREATE SYNONYM remote.ObjectA FOR SiteC.schema.objecta;
CREATE SYNONYM remote.ObjectB FOR SiteC.schema.objectb;
...
And, now - in your stored procedure you reference the above objects as:
SELECT ...
FROM remote.ObjectA
JOIN remote.ObjectB
...
The same code is deployed to each site - and the code stays the same. The only thing that changes is the synonyms for each site that will reference different servers/databases as defined at those sites. When your customers update the other server, you create a new linked server to the new server and drop/recreate the synonyms with the new server name and the code doesn't have to change.
No matter what, you have to have the linked server - but it won't matter what the names are and you can be compliant with your customers naming conventions without having to modify your code at all. Just modify the synonyms - your code references the synonyms.
I hope this clears it up for you, if not - post back and let me know if you need further clarification.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 6, 2009 at 7:25 pm
You are absolutely correct.
You must use 3 part naming convention (Server.DB.Object) or even 4 part (Server.DB.Schema.Object) in order to get away with it.
If your solution refers to 10 objects than it's convenient. If it has 150 - you're in a setup-script-must-have situation...
September 6, 2009 at 8:15 pm
benyos (9/6/2009)
You are absolutely correct.You must use 3 part naming convention (Server.DB.Object) or even 4 part (Server.DB.Schema.Object) in order to get away with it.
If your solution refers to 10 objects than it's convenient. If it has 150 - you're in a setup-script-must-have situation...
Not necessarily - you could always script the creation of the 150 synonyms. Either reading a setup table or reading the catalog on the linked server - or a whole lot of other options.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 9, 2009 at 6:03 am
try to use OPENDATASOURCE
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply