Post reply

Execute tsql script against multiple sql server instances

  • October 19, 2016 at 1:05 am

    #400890

    Hi All,

    I have below script which is a mix of TSQL+Powershell. Basically, it gives report of drive space.

    PFA screenshot. Now, what I am looking for is, how can I execute the script against multiple sql server instances and load it into a centralised table. How can I achieve this?

    -- To allow advanced options to be changed.

    EXEC sp_configure 'show advanced options', 1

    GO

    -- To update the currently configured value for advanced options.

    RECONFIGURE

    GO

    -- To enable the feature.

    EXEC sp_configure 'xp_cmdshell', 1

    GO

    -- To update the currently configured value for this feature.

    RECONFIGURE

    GO

    SET NOCOUNT ON

    declare @serverName varchar(500)

    declare @sql varchar(400)

    set @serverName = @@SERVERNAME

    set @sql = 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@serverName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[diskspaceRpt]'))

    DROP table [dbo].diskspaceRpt

    CREATE TABLE #Result

    (list varchar(255))

    insert #Result

    EXEC xp_cmdshell @sql

    select rtrim(ltrim(SUBSTRING(list,1,CHARINDEX('|',list) -1))) as DriveName

    ,round(cast(rtrim(ltrim(SUBSTRING(list,CHARINDEX('|',list)+1,(CHARINDEX('%',list) -1)-CHARINDEX('|',list)) )) as Float),0) as 'Total Capacity(MB)'

    ,round(cast(rtrim(ltrim(SUBSTRING(list,CHARINDEX('%',list)+1, (CHARINDEX('*',list) -1)-CHARINDEX('%',list)) )) as Float),0)as 'Free Space(MB)'

    ,round(cast(rtrim(ltrim(SUBSTRING(list,CHARINDEX('|',list)+1,(CHARINDEX('%',list) -1)-CHARINDEX('|',list)) )) as Float)/1024,0) as 'Total Capacity(GB)'

    ,round(cast(rtrim(ltrim(SUBSTRING(list,CHARINDEX('%',list)+1, (CHARINDEX('*',list) -1)-CHARINDEX('%',list)) )) as Float) /1024 ,0)as 'Free Space(GB)'

    from #Result

    where list like '[A-Z][:]%'

    order by drivename

    go

    drop table #Result

    go

  • October 19, 2016 at 1:32 am

    #1907334

    The most easy way to do it is using PowerShell. The invoke-sqlcmd (see URL invoke-sqlcmd) can be used to run a SQL command (or SQL file) against an instance. Put it in a FOREACH loop to run it against multiple servers. Capture the output and redirect it to the central repository.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • October 19, 2016 at 1:37 am

    #1907337

    I found a blog that describes a possible solution: Use PowerShell to Collect Server Data and Write to SQL[/url]

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • October 19, 2016 at 4:04 am

    #1907357

    Thanks a ton Hanshi.

Viewing 4 posts - 1 through 3 (of 3 total)

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