Running scripts from a share/git etc

Question

Running scripts from a share/git etc

Mad_cow

SSC Rookie

Points: 39
More actions
December 18, 2019 at 4:01 am

#3708679
I have been playing with this for the last few days and have come up with nothing, so over to you all for advice.
I am looking to setup a system where we can push the Ola maintenance scripts out to all servers.
- The scripts must be version controlled. Share Point/Git etc. but just to get this moving a fileshare is ok.
- The scripts must be pushed out to each server from a central server
- The list of servers are kept in a table on the central server.
- Using a batch file is not a preferred method.
This is what I have so far:
I have managed to get the listing from the registered servers list and can put it into a table. I have been looking at using SQLCMD to run the external scripts, but I cant work out how to use the populated variable of the server name to pass into the SQLCMD to connect.
I have had a look at using cursors and IF @@Rowcount and doesn't seem to getting anywhere.
Here is a sample of the code I have so far, its really rough but this is nowhere near a finished product.

IF OBJECT_ID('tempdb..#Serverlist') IS NOT NULL DROP TABLE #ServerList
GO
--set rowcount 0
declare @Servers varchar( 100 )

;WITH server_groups (server_group_id, name, parent_id)
AS
(
SELECT server_group_id, name, parent_id
FROM [dbo].[sysmanagement_shared_server_groups] g
WHERE name = 'ONLINE'
UNION ALL
SELECT g.server_group_id, g.name, g.parent_id
FROM [dbo].[sysmanagement_shared_server_groups] g
INNER JOIN server_groups sg ON g.parent_id = sg.server_group_id
)
SELECT Server_name into #ServerList
FROM [dbo].[sysmanagement_shared_registered_servers] s
INNER JOIN [dbo].[sysmanagement_shared_server_groups] g ON s.server_group_id = g.server_group_id
INNER JOIN server_groups sg ON sg.server_group_id = g.server_group_id

DECLARE ServerList CURSOR
FOR SELECT
Server_name
FROM
#ServerList

OPEN ServerList;

FETCH NEXT FROM ServerList INTO
@servers

WHILE @@FETCH_STATUS = 0
BEGIN
:r sqlcmd -E @servers
--Select @@servername
FETCH NEXT FROM ServerList INTO
@servers
END;
CLOSE ServerList;
DEALLOCATE ServerList;

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

You could try using xp_cmdshell instead of SQLCMD mode, something like this:

DECLARE @cmd varchar(500);
DECLARE @Script varchar(200) = 'X:\MyFolder\Ola.sql';
SET @cmd = 'sqlcmd -E -S ' + @servers + ' -i ' + @Script;
EXEC xp_cmdshell @cmd;

John