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;

   

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  December 18, 2019 at 10:20 am

  #3708731

  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