Re-"pointing" Stored Procedures

How do I "point" a set of stored procedures to operate on different linked servers?

In other words, I have the following linked servers:

DatabaseA
DatabaseB
DatabaseC
DatabaseD
...and in the future, there may be added additional linked servers.

All the linked servers have identical schema, but they contain unique data--each linked server represents a company.

I have a database which will contain stored procedures which I will want to operate against these linked servers. How can I "redirect" my stored procedures to operate against a chosen linked server?

If these were not linked servers, but SQL Server databases, I'd be able to replicate the same stored procedures in each database. Then, when I called a stored procedure, it would act against the data in that database. But these aren't SQL Server databases, so that idea is out.

Unfortunately, the USE command cannot be used within a stored procedure, and even if you could, I can't get it to respond to a database name given as a variable. That idea is out.

The only alternative I have left is to use the string catenation facility of the EXECUTE command. Unfortunately, with 100s of complex queries, setting that up is going to be a nightmare.

Does anyone have any ideas?With all due respect, having separate companies in separate databases is a bad idea. You've set a new standard - separate companies in separate databases on linked servers that are not SQL Server. Congrats.

Assuming this design is something over which you have no control (sounds like a consulting gig), how 'bout another new standard - separate SQL Server databases with stored procedures that act on separate linked servers (which are really linked databases) that are not SQL Server? You maintain 1:1 relationship b/w SQL Server databases and the linked servers their stored procedures act on.

Then, you write stored procedure generator that replaces all linked server references with the 4-part name to which the db corresponds (using syscomments, PatIndex and cursors) and sticks 'em in the right database. Ugly, eh? Remember, we're reaching for new heights.

You will need a separate database that is your (source) control database in which your stored procedures originate - sounds like you already have that - and a table that maps your SQL Server databases to your linked servers.

Good luck.|||Wow, that sounds like one heck of a way to get hurt really, really bad!

Could you build views that combine the data from all of the various servers, or DTS packages to scoop them into a single container to make management simpler?

If you really, truly want to continue to process the data on N different servers (with N being a variable, not a constant), then I'd suggest that you convert your stored procedures to DTS packages... These could operate against DSN or udl names, which would at least confine the chaos to a much smaller area.

Good luck!
-PatP|||Pat and Max, you guys both came up with good ideas.

Yes, this is a consulting gig. Yes, these are pre-existing, PervasiveSQL databases which are the backend to someone else's product. The separate database for each company is thier concept and there is nothing I can do about that.

We have an existing reporting package that operates against an MSSQL backend. Our client has asked us to port our product to work with this PervasiveSQL-multi-company arrangement.

I like both your ideas. Here's another I came up with while waiting for responses:

Write my stored procedures using a token for the database name. Store those stored procedures as text in a table, much like syscomments stores stored procedures. Then write a "master" execution stored procedure which loads the stored procedure text from that table, uses the replace command to substitute the correct database name for the token, and then use the EXECUTE command to execute the stored procedure. The problem I see with my idea, as compared to your ideas, is that I am not acutally executing stored procedures, hence I lose any precompilation advantage of a true stored procedure.

Thanks for you ideas guys.|||The problem with objects being directly accessed from a linked server is the long naming convention. I guess you guys would agree that most bugs related to objects from linked servers were due to typos on the full object name.

In one of my projects here in the Philippines, a GIS application needs to pull data from a central data repository. The problem is the GIS server (with its own database server) is not allowed to directly access the data repository.

As a work around, I defined both the GIS database server and the central repository in another server as linked servers. Thus, the two servers are linked via the third server. then I did the following:

1. I defined details of the data to needed by the GIS apps and put it in a view in the central repository.
CREATE VIEW vw_PROPERTY_RE_GIS
AS
SELECT B.s_prop_no, B.s_re_no, A.s_PLOP, A.s_loc_id
FROM T_PROPERTY A, T_RE_PROPERTY B
WHERE A.s_prop_no = B.s_prop_no AND A.s_loc_id = b.s_loc_id

2. Defined a view in the third to access the view in the data repository.
CREATE VIEW vw_PROPERTY_RE_GIS
as
SELECT * FROM CNTRL_DB.REMS.REMS.vw_PROPERTY_RE_GIS
3. Defined a view in the GIS DB to access the view in the third server.
CREATE VIEW vw_PROPERTY_RE_GIS
as
SELECT * FROM ALPS.REMS.ALPS.vw_PROPERTY_RE_GIS

Thus, whenever my GIS app would need information about a certain real estate, it would simply kick the simple query SELECT * FROM vw_PROPERTY_RE_GIS.

And if by chance there is a need to add more fields on the data to be extracted, I would just have to modifiy the view on the central repository.

This approach works if there is restriction on how servers are linked and performance is not so much an issue.|||I would NEVER recommend this, but you can use the USE statement in a stored procedure and pass the linked server name as a variable. But you have to use...wait for it...DYNAMIC SQL.

(Pause for shrieks of horror from all competent DBAs...)

I've only implemented this once, for a database schema snapshot application that had to run against any and all databases on a server. It was a mess to program, but it has run very smoothly and reliably since then. The code hasn't changed much in four years and two SQL Server upgrades.|||Blindman,

How were you able to accomplish this ?

I've attempted to use the USE statement in stored procedures and, when compiled, they've always come back with an error stating the the USE statement is not permited in stored procedures. The USE page in the Books Online also state this.

Additionally, the use of a variable for the database with the USE statment is also prohibited. I've tested this several times and have not been successful. Apparently, the USE statement requires a literal for the database name.

If you've found some way to get around these problem, let me know, 'cause they'd make tackling these problems a whole lot easier.

Thanks.

Ken|||create procedure GetDataFromPubs
as
begin
declare @.SQLString varchar(4000)
set @.SQLString = 'Use Pubs Select * from Authors'
execute (@.SQLString)
end

The executed statement runs in it's own scope, and so after completion focus returns to the calling database.