Running scripts from a share/git etc

  • Mad_cow

    Grasshopper

    Points: 19

    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;

     

  • John Mitchell-245523

    SSC Guru

    Points: 148761

    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

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

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