3

I have a query in a stored procedure that needs to be executed on different servers and databases according to some parameters.

How can I do this without using neither exec, nor sp_executesql?

I'm using SQL Server 2008.

Thank you.

UPDATE

I've found some links

http://www.eggheadcafe.com/software/aspnet/29397800/dynamically-specify-serve.aspx

http://www.sommarskog.se/dynamic_sql.html

Is using SYNONYM possible solution? If yes, than how?

UPDATE 2

I forgot to mention that all this servers are linked to the server where stored procedure is stored.

UPDATE 3

OPENROWSET or OPENDATASOURCE are not accessible either. I need a solution without building query string concating server name, schema name, db name.

It surely can be done by using if or case in stored procedure, but if we have 37 variations, then it's not a good solution.

Any other suggestions?

hgulyan
  • 8,099
  • 8
  • 50
  • 75
  • How many combination do you have of server & database, for which you want to execute the sql dynamically? example: db1.sp1, db1.sp2, db2.sp1, db2.sp2, db2.sp3? – shahkalpesh Jun 09 '10 at 06:29
  • 37, do you suggest using case or if? – hgulyan Jun 09 '10 at 06:38
  • SYNONYM wouldn't really help as you'd still need to change where the synonym points to which would require either dynamic SQL or a big hardcoded conditional statement. Could also cause problems with concurrency as the Synonym can only point to one thing at a time. – Martin Smith Jun 09 '10 at 07:37
  • @Martin Smith, thank you for describing synonym:) – hgulyan Jun 09 '10 at 09:41

3 Answers3

1

Nobody wants to answer, so I'll do it myself, just to have accepted answer.

There's isn't any way to do this. You need to use one of specified suggestions, anyway the query must be generating by concatenating.

hgulyan
  • 8,099
  • 8
  • 50
  • 75
0

Does OPENROWSET or OPENDATASOURCE help?

EDIT: If it works, you can change the database at runtime & execute the query using the present connection. I cannot see any other way of executing query the way you want.

What is wrong with running query using string i.e dynamic query?

shahkalpesh
  • 33,172
  • 3
  • 63
  • 88
  • These are linked servers, so I don't have problems with connecting to them. OPENROWSET or OPENDATASOURCE don't solve my problem, I just don't want to put my query in a string type. – hgulyan Jun 09 '10 at 06:58
  • @hgulyan But these take a string which you can easily build by concatenation. – Martin Smith Jun 09 '10 at 07:39
  • @Martin Smith, I would do that with parameterized sp_executesql, if I have wanted to build my query string. – hgulyan Jun 09 '10 at 09:40
  • @shahkalpesh, I have edited my questions. You see, the reason why I don't want exec or sp_executesql is that I don't want generating query string concating, as I said in update 3. – hgulyan Jun 09 '10 at 09:53
  • @hgulyan Well it isn't possible then without some massive set of if statements. – Martin Smith Jun 09 '10 at 10:36
  • @Martin Smith, than write it as an answer and I'll accept it. I consider that I have to use sp_executesql. – hgulyan Jun 09 '10 at 10:46
  • @hgulyan - Leave the question open for a bit incase someone does have an idea of how this can be achieved. I definitely can't think of any way though! – Martin Smith Jun 09 '10 at 10:51
  • @Martin Smith, I can't find any other solution either:( p.s. anyway post your answer. I'll wait a while and accept your answer. – hgulyan Jun 09 '10 at 11:14
  • @hgulyan Does gbn's answer here help at all or is it not applicable to what you are doing? http://stackoverflow.com/questions/2073544/set-database-name-dynamically-in-sql-server-stored-procedure – Martin Smith Jun 09 '10 at 12:18
  • I didn't get how it works. Where should I add my query and what @p1, @p2? – hgulyan Jun 09 '10 at 13:04
0
/* DO THIS FOR EACH TABLE IN THE PROCEDURE*/ 

--BEGIN TABLE_1  
DECLARE @LINKEDSERVER AS VARCHAR(50) 
 SET @LINKEDSERVER = DBO.FN_RETURN_SERVER('SBROUBLES')

DROP SYNONYM MYLINKEDSERVER
EXEC (' CREATE SYNONYM MYLINKEDSERVER FOR ' + @LINKEDSERVER + 'ANYDB.DBO.ANYTABLE')

--- END TABLE_1

-- UTILIZATION
SELECT COUNT(*) FROM MYLINKEDSERVER


--AND FN_RETURN_SERVER COULD BE ANY SELECT CASE ON SQL AS WELL
Diogo Bandeira França
  • 1