August 19, 2004 at 11:57 am
I need to provide 2-way access between 2 databases, which may or may not be on the same server, depending on the customer's installation.
Is there a way to generically code my stored procedures so that my code will not have to be changed, regardless of:
1) whether or not they are on the same server
2) the server names change
I know that I have to set up linked servers in the case of multiple servers, but I would rather not be modifying SP source code at installation time.
Thanks.
August 19, 2004 at 2:28 pm
It could be done using dynamic SQL such as:
declare @RemoteLoc varchar(100)
set @RemoteLoc = ( SELECT Location FROM MyEnvirontmentTable WHERE Key = 'Purpose' )
-- Think of this as creating a temporary stored procedure that calls the desired stored procedure. It is done this way so that we may utilize output parameters.
set @strParams = N'@MyFirstParm int, @MySecondParm varchar(75)'
set @strQuery = N'exec ' + @RemoteLoc + '.dbo.RemoteProcedure @MyFirstParm = @MyFirstParm, @MySecondParm = @MySecondParm OUTPUT'
EXEC sp_executesql @strQuery, @strParams, @MyFirstParm = @MyFirstParm, @MySecondParm = @MySecondParm OUTPUT
if @@ERROR <> 0
Begin
RAISERROR ( 'Error occured while calling RemoteProcedure within MyProcedure.', 16, 1)
Return
End
August 19, 2004 at 2:33 pm
Thanks, Aaron.
That would work. I was thinking of something WITHOUT dynamic SQL (should have mentioned that in the original post), but if no options present themselves, I'll try that.
August 19, 2004 at 3:08 pm
I can only think of 2 other options but they may not fly.
1) if there are a limited number of possibilities being bandied about you could code each one within an if structure. I haven't tried this extensively but I think the SP will compile OK despite most of the servers/databases not existing.
SELECT value from environment table
if value = 1 exec OtherServer.OtherDatabase.dbo.RemoteSP
else if value = 2 exec YetAnotherServer.OtherDatabase.dbo.RemoteSP
else if value = 3 exec OtherLocalDatabase.dbo.RemoteSP
and so on and so forth.
2) Thunk them. If you have a lot of procedures calling a few remote procedures you can create local versions of the remote procedures that accept the same parameters and simply pass them along to the remote ones. Then when the remote location changes you just need to modify the local front ending procedures to have the change take effect everywhere.
Do I need to try to come up with a fourth option?
OK. You talked me into it.
You can combine both of the above. The environment table can tell you A) if it is the same server and B) the database name. If it is on the same database you can use a USE(dbname) command to switch local database context. Otherwise if the client is using seperate instances you can revert to the 2nd process. With this way you still need to support the configurable stored procedure approach, but only when the client wants to change servers. This would require those that follow option A to do less work and would reduce one SP call.
Do I need to try to come up with a fifth option?
....
Sorry. I've got nothing this time.
August 19, 2004 at 6:05 pm
Thanks, Aaron. Those ideas may well prove useful at some point in this project. However, my question is really:
Is there a way to create a stored procedure that references a table that does
not currently exist?
For example:
SELECT * FROM
[linked-server].[Finance].[dbo].[ChartOfAccounts]
This will produce an error when I try to create/save the SP,if the linked server doesn't exist, or the ChartOfAccounts table doesn't exist in the Finance database, etc, etc.
When I deploy the SP, all of the specifics for the linked-server may not be
set up.
Is there a way to have the objects checked at run-time, rather than at
CREATE PROCEDURE time?
I still expect to get errors at run time if the objects haven't been properly configured. It's driving me nuts that I can't even save the SP if all the pieces are not in place.
Thanks.
August 19, 2004 at 7:02 pm
CREATE PROCEDURE time?
Yes but I don't know how. I'm actually surprised you have that problem because I have the opposite problem. If I address a table that does not exist it parses fine and so allows the save to occur but since it didn't find the table it doesn't check any of the column references either and so you can get into a real mess if you typo the table name. Perhaps it is a system setting at our shop, but I think we are running all the defaults in our development server. I believe I've seen this on both 7.? and 2000 but can't swear to either one (but it has happened). Of note is that this has happened when altering existing SPs from either Enterprise Manager or Query Analyzer. Perhaps its a CREATE vs ALTER difference, but that seems very far fetched to me. Also note that I can go into Query Analyzer and type xxx.yyy.zzz.aaa (literally) and it syntax checks just fine but of course complains about not know about server xxx when I actually run it.
Hopefully the amount of reply activity doesn't scare off people who actually know the answer to this one. You might consider a new thread. Sorry.
August 19, 2004 at 7:07 pm
I was going to start one, but I wanted to respond to this first so you didn't think I was leaving you haning. Maybe we'll have better luck on the new one. Thanks for your efforts.
August 20, 2004 at 12:39 pm
Use Client Netwok Utility to create aliases for the two servers, and create linked servers to the aliases.
Then you can have queries written with four-part names that are independent of the actual server names.
Note that not all T-SQL statements accept four-part names, but the basic SELECT, UPDATE, INSERT, and DELETE will work.
August 20, 2004 at 1:01 pm
Scott, that looks really promising.
Is there a programatic interface to that functionality?
August 20, 2004 at 1:10 pm
Client Network Utility is a separate program under Microsoft SQL Server on the Start menu.
I am not aware of any programmable interface for it.
August 20, 2004 at 1:18 pm
Scott, I was beginning to approach this from a different angle. When creating the linked server, I'll do:
sp_addlinkedserver
@server = 'stored-procedure-server-name'
,@srvproduct=N''
,@datasrc = [real-server-name]
,@provider='SQLOLEDB'
,@catalog= 'default-database'
The SPs would be written with the 'stored-procedure-server-name'.
Does this have the same affect as creating an alias using the CNU?
August 20, 2004 at 2:42 pm
IMHO, I would say that you should create a third "control" database. In the control database have a list of the required tables in the sub-database(s). Also include tables to hold the server(s) names and database(s) names. During install of the control it asks for the names of the servers and the names of the databases.
Then have stored procedures that read the environment variables and do pings, read the sysdatabase and sysobjects table to verify the database. Maybe even make it a standalone app the can put out a text report log.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
August 20, 2004 at 2:55 pm
Jim, if the inter-database communication were on a larger scale, I would investigate your suggestion. Our design only has 5 tables that are 'shared', and there are never more than 2 servers involved.
Thanks for your time.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply