0

When I run the script below the query connection from my local SSMS session is interrupted with the following error:

Msg 64, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

I walked through the script and this is the offending line:

 INSERT INTO #data
    EXEC xp_cmdshell 'powershell.exe -noprofile -command "Get-WmiObject Win32_LogicalDisk -filter "MediaType=12" | Format-Table DeviceID"'

So... It's not until I attempt to run PowerShell that the issue arises. I've checked the local box and and can run the command in a cmd window just fine. I've attempting other script options such as "-ExecutionPolicy bypass" but that has not solved the problem. Does anyone have an idea as to why 1) the command won't complete and 2) the SSMS connection is killed, because I'm out of ideas.

DECLARE @SQL VARCHAR(MAX)
DECLARE @i INT
DECLARE @Count INT
DECLARE @Disk VARCHAR(100)
DECLARE @RowNumber INT
DECLARE @WmiServiceLocator int
DECLARE @WmiService int
DECLARE @CounterObject int
DECLARE @CounterName varchar(255)
DECLARE @Compressed varchar(255)
DECLARE @DeviceID varchar(255)
DECLARE @DriveType varchar(255)
DECLARE @BlockSize varchar(255)
DECLARE @FileSystem varchar(255)
DECLARE @FreeSpace varchar(255)
DECLARE @Size varchar(255)
DECLARE @VolumeName varchar(255)
DECLARE @SerialNumber varchar(255)
DECLARE @Name VARCHAR(255)

IF object_id('tempdb..#data') IS NOT NULL
    DROP TABLE #data
IF object_id('tempdb..#LogicalDisk_data') IS NOT NULL
    DROP TABLE #LogicalDisk_data
IF object_id('tempdb..#Volume_DeviceID') IS NOT NULL
    DROP TABLE #Volume_DeviceID
IF object_id('tempdb..#output') IS NOT NULL
    DROP TABLE #output
IF object_id('tempdb..#output_volume') IS NOT NULL
    DROP TABLE #output_volume
IF object_id('tempdb..#output2') IS NOT NULL
    DROP TABLE #output2

CREATE TABLE #data (
    RowNumber INT IDENTITY(1,1), 
    data  NVARCHAR(100));

CREATE TABLE #LogicalDisk_data (
    RowNumber INT IDENTITY(1,1), 
    data  NVARCHAR(100));

CREATE TABLE #Volume_DeviceID (
    RowNumber INT IDENTITY(1,1), 
    data  NVARCHAR(100));

CREATE TABLE #output (
    DeviceID varchar(255),
    Compressed varchar(255),
    Name varchar(255),
    DriveType varchar(255),
    FileSystem varchar(255),
    FreeSpace varchar(255),
    Size varchar(255),
    VolumeName varchar(255))

CREATE TABLE #output_volume (
    DeviceID varchar(255),
    Compressed varchar(255),
    Name varchar(255),
    DriveType varchar(255),
    [BlockSize] varchar(255),
    FileSystem varchar(255),
    FreeSpace varchar(255),
    Size varchar(255),
    SerialNumber varchar(255))

CREATE TABLE #output2 (
    DeviceID varchar(255),
    Name varchar(255),
    Col1 varchar(255),
    Col2 varchar(255),
    Col3 varchar(255),
    Col4 varchar(255),
    Col5 varchar(255),
    Col6 varchar(255),
    Col7 varchar(255),
    Col8 varchar(255),
    Col9 varchar(255),
    Col10 varchar(255),
    Col11 varchar(255),
    Col12 varchar(255),
    Col13 varchar(255),
    Col14 varchar(255),
    Col15 varchar(255),
    Col16 varchar(255),
    Col17 varchar(255),
    Col18 varchar(255),
    Col19 varchar(255),
    Col20 varchar(255),
    Col21 varchar(255))

BEGIN
    exec sp_OACreate 'WbemScripting.SWbemLocator', @WmiServiceLocator output, 5
    exec sp_OAMethod @WmiServiceLocator, 'ConnectServer', @WmiService output, '.', 'root\cimv2'
    if isnull(@WmiService,-1) <= 0
    begin
        exec sp_OADestroy @WmiServiceLocator
        raiserror('Could not access WMI service.', 16, 1)
        return
    end

    INSERT INTO #data
    EXEC xp_cmdshell 'powershell.exe -noprofile -command "Get-WmiObject Win32_LogicalDisk -filter "MediaType=12" | Format-Table DeviceID"'

    IF EXISTS (SELECT data FROM #data WHERE data LIKE '%powershell.exe%')
    BEGIN
        TRUNCATE TABLE #data
        DBCC CHECKIDENT('#data',RESEED,1)
        INSERT INTO #data
        EXEC xp_cmdshell 'C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe -noprofile -command "Get-WmiObject Win32_LogicalDisk -filter "MediaType=12" | Format-Table DeviceID"'
    END

    IF EXISTS (SELECT data FROM #data where data LIKE '%The system cannot find the path specified.%')
    BEGIN
        RAISERROR ('The system cannot find the path specified: C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe.',16,1)
    END

    SELECT @Count=COUNT(*) FROM #data

    SELECT @i=1
    WHILE @i<=@Count
    BEGIN
        SELECT @Disk=NULL
        SELECT @Disk=data
        FROM #data
        WHERE RowNumber=@i AND data not like '%----%' AND data not like '%DeviceID%' AND data is not null

        IF @Disk IS NOT NULL
        BEGIN
            SELECT @CounterName='Win32_LogicalDisk.DeviceID="' + RTRIM(@Disk) + '"'
            exec sp_OAMethod @WmiService, 'Get', @CounterObject output, @CounterName

            exec sp_OAGetProperty @CounterObject, 'Compressed', @Compressed output
            exec sp_OAGetProperty @CounterObject, 'DeviceID', @DeviceID output
            exec sp_OAGetProperty @CounterObject, 'DriveType', @DriveType output
            exec sp_OAGetProperty @CounterObject, 'FileSystem', @FileSystem output
            exec sp_OAGetProperty @CounterObject, 'FreeSpace', @FreeSpace output
            exec sp_OAGetProperty @CounterObject, 'Size', @Size output
            exec sp_OAGetProperty @CounterObject, 'VolumeName', @VolumeName output

            INSERT #output
            SELECT
                @DeviceID DeviceID, @Compressed Compressed,@DeviceID Name,@DriveType DriveType,@FileSystem FileSystem,@FreeSpace FreeSpace,@Size Size,
                @VolumeName VolumeName
        END

        SELECT @i=@i+1
    END

    exec sp_OADestroy @CounterObject
    exec sp_OADestroy @WmiService
    exec sp_OADestroy @WmiServiceLocator

    exec sp_OACreate 'WbemScripting.SWbemLocator', @WmiServiceLocator output, 5
    exec sp_OAMethod @WmiServiceLocator, 'ConnectServer', @WmiService output, '.', 'root\cimv2'
    if isnull(@WmiService,-1) <= 0
    begin
        exec sp_OADestroy @WmiServiceLocator
        raiserror('Could not access WMI service.', 16, 1)
        return
    end

    TRUNCATE TABLE #data
    INSERT INTO #data
    EXEC xp_cmdshell 'powershell.exe -noprofile -command "Get-WmiObject Win32_Volume -filter "DriveType=3" | Format-Table Name"'

    IF (SELECT data FROM #data WHERE RowNumber=1) LIKE '%powershell.exe%'
    BEGIN
        DELETE #data
        INSERT INTO #data
        EXEC xp_cmdshell 'C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe -noprofile -command "Get-WmiObject Win32_Volume -filter "DriveType=3" | Format-Table Name"'
    END

    INSERT INTO #Volume_DeviceID
    EXEC xp_cmdshell 'powershell.exe -noprofile -command "Get-WmiObject Win32_Volume -filter "DriveType=3" | Format-Table DeviceID"'

    IF (SELECT data FROM #Volume_DeviceID WHERE RowNumber=1) LIKE '%powershell.exe%'
    BEGIN
        DELETE #Volume_DeviceID
        INSERT INTO #Volume_DeviceID
        EXEC xp_cmdshell 'C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe -noprofile -command "Get-WmiObject Win32_Volume -filter "DriveType=3" | Format-Table DeviceID"'
    END

    INSERT INTO #LogicalDisk_data
    EXEC xp_cmdshell 'powershell.exe -noprofile -command "Get-WmiObject Win32_LogicalDisk -filter "MediaType=12" | Format-Table DeviceID"'

    IF (SELECT data FROM #LogicalDisk_data WHERE RowNumber=1) LIKE '%powershell.exe%'
    BEGIN
        DELETE #LogicalDisk_data
        INSERT INTO #LogicalDisk_data
        EXEC xp_cmdshell 'C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe -noprofile -command "Get-WmiObject Win32_LogicalDisk -filter "MediaType=12" | Format-Table DeviceID"'
    END

    SELECT @Count=COUNT(*) FROM #data

    SELECT @i=1
    WHILE @i<=@Count
    BEGIN
        SELECT @Disk=NULL
        SELECT @RowNumber=NULL

        SELECT @RowNumber=@i
        FROM #data d
        LEFT JOIN #LogicalDisk_data l ON d.RowNumber=l.RowNumber
        WHERE d.RowNumber=@i AND d.data not like '%----%' AND d.data not like '%Name%' AND d.data is not null

        SELECT @Disk=d.data
        FROM #Volume_DeviceID d
        LEFT JOIN #LogicalDisk_data l ON LTRIM(RTRIM(l.data)) + '\'=LTRIM(RTRIM(d.data))
        WHERE d.RowNumber=@RowNumber AND d.data not like '%----%' AND d.data not like '%DeviceID%' AND d.data is not null AND l.data IS NULL

        IF @Disk IS NOT NULL
        BEGIN
            SELECT @CounterName=REPLACE('Win32_Volume.DeviceID="' + RTRIM(@Disk) + '"','\','\\')
            exec sp_OAMethod @WmiService, 'Get', @CounterObject output, @CounterName

            exec sp_OAGetProperty @CounterObject, 'Compressed', @Compressed output
            exec sp_OAGetProperty @CounterObject, 'DeviceID', @DeviceID output
            exec sp_OAGetProperty @CounterObject, 'DriveType', @DriveType output
            exec sp_OAGetProperty @CounterObject, 'BlockSize', @BlockSize output
            exec sp_OAGetProperty @CounterObject, 'FileSystem', @FileSystem output
            exec sp_OAGetProperty @CounterObject, 'FreeSpace', @FreeSpace output
            exec sp_OAGetProperty @CounterObject, 'Capacity', @Size output
            exec sp_OAGetProperty @CounterObject, 'Name', @Name output
            exec sp_OAGetProperty @CounterObject, 'SerialNumber', @SerialNumber output

            INSERT #output_volume
            SELECT
                @DeviceID DeviceID,@Compressed Compressed,@Name Name,@DriveType DriveType,@BlockSize [BlockSize],@FileSystem FileSystem,@FreeSpace FreeSpace,@Size Size,@SerialNumber SerialNumber
        END

        SELECT @i=@i+1
    END

    exec sp_OADestroy @CounterObject
    exec sp_OADestroy @WmiService
    exec sp_OADestroy @WmiServiceLocator

    SELECT
        v.Name,
        VolumeName = o.VolumeName,
        v.FileSystem,
        CAST(CAST(((CAST(v.Size AS NUMERIC(20,2)) / 1024.0) / 1024.0) AS NUMERIC(20,2)) AS VARCHAR(255)) AS Size,
        CAST(CAST(((CAST(v.FreeSpace AS NUMERIC(20,2)) / 1024.0) / 1024.0) AS NUMERIC(20,2)) AS VARCHAR(255)) AS FreeSpace,
        CAST((CAST(CAST(v.FreeSpace AS FLOAT) / CAST(v.Size AS FLOAT) AS NUMERIC(6,5))*100) AS NUMERIC(10,2)) AS PercentFree,
        v.[BlockSize],
        v.DriveType,
        v.Compressed
    FROM #output_volume v
    LEFT JOIN #output o ON v.Name=o.Name + '\'
    LEFT JOIN #output2 d ON v.Name=d.Name + '\'
    ORDER BY v.Name
END

DROP TABLE #data
DROP TABLE #LogicalDisk_data
DROP TABLE #Volume_DeviceID
DROP TABLE #output
DROP TABLE #output_volume
DROP TABLE #output2
  • What happens if you run that same powershell command as a simple EXECUTE command, without INSERTing the output into a temp table? Modify the command slightly to route output to a file, if necessary. Do you get the same error if the statement is not interacting with the DB directly? – RDFozz Jul 26 '17 at 22:29
  • 7
    So you're using SSMS to run xp_cmdshell to call out to powershell and sp_OA, oh my. Why? Why not just check disk space and wmi from powershell and leave both SSMS and SQL Server out of it? Or have powershell insert the data into a table if you really ever need this data to be in SQL Server? – Aaron Bertrand Jul 26 '17 at 23:40
  • @Aaron: I know, right? I inherited the script (this is only a part of it) and like the result set it returns but I did develop a PoSh script which is called from a scheduled task. The task reports any drives and / or mount points with less than 3 GB available. The problem with this approach, however, is that someone could stop, disable, or otherwise compromise the alerts. Group Policies, for some reason, are not yet in place on any of the database servers across the company. So my goal is to run this as an Agent job which I can control and keep an eye on. I'd love to hear better solutions! – SQL_Underworld Jul 27 '17 at 16:30
  • 2
    If someone could stop the scheduled task, someone could stop the Agent job. This does not seem to be a good reason to be doing this work in SQL Server. – Aaron Bertrand Jul 27 '17 at 16:32