Alter Procedure auf Verbindungsserver

Question

Hallo, 

ich habe folgendes Problem:

Ich habe 2 Sql Server (Version 2005, Server A und Server B) wenn auf Server A eine Stored Procedure geändert wird, so soll dies auch auf Server B passieren. Die Prozeduren sollen auf Server A und B synchron sein.

Deshalb soll nach dem Alter Procedure Statement eine neue Stored Procedure aufgerufen werden, die dann die Prozedur auf Server B überträgt.

Diese Prozedur liest den Code (das Create Procedure Statement) der Stored Procedure aus und soll dieses dann auf Server B ausführen. Server B ist als Verbindungsserver in Server A eingerichtet. Das Auslesen des Code erfolgt mittels sys.procedures und EXEC sp_helptext 'ProcedureName'. Ich habe damit das Create Statement in einer Variablen und muss dies auf Server B ausführen. Dabei ist aber sowohl der Servername wie auch der Ziel DB Name von Server B dynamisch, d.h. ich habe den Namen des Servers und der Datenbank in einer Variablen vom Typ varchar.

Meine Idee war 

EXECUTE( @CreateStmt ) AT @NameServer2

wobei @CreateStmt den DbNamen vor dem Create angibt, d.h. ALTER PROCEDURE DBName2.dbo.Prozedurname, aber zum einen kann man den DB Namen nicht bei dem CREATE mit angeben und zum anderen darf nach dem "AT" keine Variable folgen.

Hat jemand eine Idee, wie ich das Alter/Create Statement von Server A auf Server B ausgeführt bekomme.

Vielen Dank.

Martin

Answer 1

Hallo,

folgender SQL-Code erstellt die Prozedur auf dem Verbindungsserver, aber ich habe beim EXEC immer noch fest den Servername hinter dem AT kodiert.

Dieser Servername muss nun noch durch eine Variable ersetzt werden.

DECLARE @dbServer2 varchar(50)
SET @dbServer2 = 'DbServer2'

DECLARE @sql nvarchar(max)
SET @sql ='
CREATE PROCEDURE Test
AS 
BEGIN
	SELECT TOP(1) * FROM Test
END
'

EXEC('exec '+ @dbServer2 +'.dbo.sp_executesql @statement =N''' +@sql + ' ''') AT Server2

Answer 2

hmm, ich würde das mittels sqlcmd machen.. btw, synchron ist eigentlich ohne down-time nicht machbar.

Answer 3

Hallo Stefan, 

danke für deine Antwort. 

Per SqlCmd wäre es sicher auch machbar, aber aus dem Setup Programm, dass die Prozeduren aktualisiert kann ich das so ohne weitere nicht aufrufen. 
Deswegen ist die SQL Lösung wichtig. 

Nach langem Try and Error und Websuche habe ich jetzt die Lösung gefunden. Die Lösung ist den Servernamen nicht mit AT anzufügen, sondern noch vor den Namen der DB zu schreiben.

In @code steht dann das CREATE PROCEDURE ...

EXEC('exec ' +@serverName + '.' + @dbName +'.dbo.sp_executesql @statement =N''' +@code	 + '''') 				

Gruß

Martin