April 24, 2015 at 3:35 pm
Hello all
I have this requirement where some store procedures from a "seed" database need to be replicated to another database (on demand, so replication is not suppose to be use in this scenario).
I know it can be achieved by exporting the store procedures and then execute that at the B database but I want something a bit more automatic since it can be a large number of sprocs.
I am trying something like this (still in dev):
SET NOCOUNT ON;
--
SELECT ROW_NUMBER() OVER(ORDER BY definition) seq, definition base
into #sprocs
FROM databaseA.[sys].[procedures] p
INNER JOIN databaseA.sys.sql_modules m ON p.object_id = m.object_id
ORDER BY Base;
DECLARE
@Loop as INT ,
@QT as INT ,
@Base as VARCHAR(200),
@name varchar(100),
@sql nvarchar(4000)
set @name ='databaseB'
set @Loop = 1
set @QT = (SELECT COUNT(1) FROM #sprocs)
WHILE @Loop <= @QT
BEGIN
SET @sql = (SELECT Base FROM #sprocs WHERE Seq = @Loop);
SET @sql = REPLACE(@sql,'''','''''')
SET @sql = 'USE [' + @Name + ']; EXEC(''' + @sql + ''')'
print(@sql)
SET @Loop = @Loop + 1;
END;
But I am sure there are way better ways to accomplish that, has anyone have to do something like that?
April 24, 2015 at 3:48 pm
You can create a server group and then in one window in SSMS that is connected to the whole group run the script that creates or edits the procedures. I've been doing so when I have to deploy changes in all environments where I work. The URL is for an article that explains how to do it - http://blog.hoegaerden.be/2012/11/24/ssms-connect-to-several-servers-in-one-click-okay-two/
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 25, 2015 at 8:31 am
I'd suggest writing a PowerShell script to run the export and import of the procedures. That should be fully automated and ought to work well.
Another approach would be to put the procedures into source control (where they should be anyway). You can then use PowerShell or even a third party tool to deploy from there in an automated fashion.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 25, 2015 at 8:38 am
I haven't try something like that from powershell but sounds like a good idea, the source code will be in github so I am looking at what are my options
Thanks!
April 25, 2015 at 8:40 am
In that case I'd just pull them directly from source and publish them that way. PowerShell will be fully automatable, you can connect to multiple servers, and with threading you can run them all at once if you wanted.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply