March 19, 2007 at 2:44 pm
I have an issue where I have a variety of stored procedure calls that for different client bases could be either directed toward a single server or over cross servers and databases. What I am trying to figure out is how to either dynamically change the [Server].Database instance at run-time based on a setup table that I have already established with the needed server and database informtion or am I barking up the wrong tree here and need to look at it from a completly different angle.
For instance one client may have a Stored proc that looks like this:
SELECT * FROM SERVER1.Database1.dbo.Table
Where as another client may just be the following:
SELECT * FROM Table
Basically what I want is a way to either set these at creation time when the DB is created or to have the Server and Database values populated at execution. Anyone have any ideas?
March 19, 2007 at 3:44 pm
1) Idea is around for I don't remember how many years. They name it "connection string".
You may store it either in .ini file or in a table in database, does not matter. It's gonna be connection string anyway.
And you build remote calls dynamically, no matter where do you do it, in C# code or in SQL SP.
2) you should not have SP like this:
SELECT * FROM SERVER1.Database1.dbo.Table
SP must be
SELECT * FROM dbo.Table
on every server.
And you must call it like this:
EXEC SERVER1.Database1.dbo.MySP
_____________
Code for TallyGenerator
March 19, 2007 at 3:59 pm
I guess I didn't fully qualify my beginning statement. I apologize. The problem is that the Stored procedure is made up of a query that could represent multiple databases on different servers such as the following:
SELECT
*
FROM
[SERVER1].DBName1
.dbo.Table1 s INNER JOIN [Server2].DBName.dbo.Table2
WHERE
(fieldname NOT IN (SELECT fieldname FROM [Server3].DBName3.Table3
So there is no way that I can see to execute the SP based on Server1.Databasename and return results.
March 20, 2007 at 2:55 pm
How many rows do you have in those tables?
10? 20?
Will it always stay this way?
For testing purposes populate tables with 100k rows each and run this query.
You better do it just before leaving work place, next morning you'll see how long did it take.
Remote query downloads whole remote table to local tempdb. Then it starts doing joins.
So, you'll not gonna lose performance if you build SP like this:
INSERT INTO #Table1
EXEC [SERVER1].DBName1.dbo.Table1_SP
INSERT INTO #Table2
EXEC [SERVER2].DBName1.dbo.Table2_SP
INSERT INTO #Table3
EXEC [SERVER1].DBName1.dbo.Table3_SP
SELECT *
FROM
#Table1 T1 INNER JOIN #Table2 T2 ON T1.F1 = T2.F2
WHERE NOT EXISTS (select 1 FROM #Table3 T3 where T1.F2 = T3.F1)
And with this approach you don't need to give all local users access to all tables on all remote servers.
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply