Running scripts from a share/git etc

  • 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


    --set rowcount 0

    declare @Servers varchar( 100 )



    ;WITH server_groups (server_group_id, name, parent_id)



    SELECT server_group_id, name, parent_id

    FROM [dbo].[sysmanagement_shared_server_groups] g

    WHERE name = 'ONLINE'


    SELECT g.server_group_id,, 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











    OPEN ServerList;







    :r sqlcmd -E @servers

    --Select @@servername




    CLOSE ServerList;

    DEALLOCATE ServerList;


  • 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;


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

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