Executing SQL Procedure from Powershell : not working ( SQL 2012 )

Aug 16, 2013 at 7:53 PM
Hello,
      I have a powershell script which collects performance counter data, parses the data and loads the data to a SQL Server DB.
The section of the code which execute the stored procedure on the SQL server , is not working, and i am at my wits end, in trying to figure out what the problem is.

The counter data is collected in a CSV files, and parsed.

I provide the name of the csv file to the SQL Proc, for it to bulk load the data from the CSV file to the SQL Table.

The powershell code is sending the appropriate execute store procedure command to the SQL Server. I used the SQL Server Profiler to trace the command being send by the powershell script.

Following is what i see in the profiler :

RPC:Completed exec UspPushPowershellDatatoSQL @FileLocation='PerfMon_14-Aug-2013-14-28-55.csv' .Net SqlClient Data Provider LoginID LOGINID

However the SQL server proc never executes and the data is not loaded to the SQL tables.

However, when i use the above command and run it in SQL Server using the same login ID, it works.

Am I missing something? Any suggestions ?

Following is the powershell code:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection("Server=XXXXXX;Database=YYY;Integrated Security=SSPI");
        $SqlConnection.Open()

        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
        $SqlCmd.Connection = $SqlConnection

        $SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
        $SqlCmd.CommandText = $ProcName

        $SqlCmd.Parameters.Add("@FileLocation",[system.data.SqlDbType]::VARCHAR) | out-null
        $SqlCmd.Parameters['@FileLocation'].Direction = [system.data.ParameterDirection]::Input
        $SqlCmd.Parameters['@FileLocation'].Value = $FileLocation

        $SqlCmd.ExecuteNonQuery()|out-null

        $SqlConnection.Close()

Following is the Sql Proc Code:

CREATE PROCEDURE dbo.DatatoSQL
(
 @FileLocation NVARCHAR(MAX)
)

AS
/************************************************************************************
DESCRIPTION: This proc is used to push Powershell collected data to SQL
*************************************************************************************/
BEGIN
SET NOCOUNT ON

DECLARE @FileLocationLocal NVARCHAR(MAX) = @FileLocation

--delete from dbo.Cnt_Stg

BEGIN TRY
-- generate bulk insert commands
DECLARE @InsertFileName TABLE
(
    fl_Name VARCHAR(2048)
);

INSERT @InsertFileName
EXEC MASTER..xp_cmdshell 'dir /b "FolderLocationWhereFileExists\*.csv" ';

SELECT * FROM @InsertFileName

--DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
DECLARE myCursor CURSOR FOR
SELECT fl_name FROM @InsertFileName

OPEN myCursor
DECLARE @sql NVARCHAR(MAX)
DECLARE @id VARCHAR(2048)

FETCH NEXT FROM myCursor INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @id
    IF @id = @FileLocationLocal

    BEGIN

    SET @sql =  'BULK INSERT dbo.Cnt_Stg  FROM "FolderLocationWhereFileExists\' + @id + '"
    WITH (FIRSTROW = 2, ROWTERMINATOR=''\n'', FIELDTERMINATOR='','');'

    EXEC (@sql)

   -- select * from dbo.Cnt_Stg

    END

FETCH NEXT FROM myCursor INTO @id
END
CLOSE myCursor
DEALLOCATE myCursor

INSERT INTO dbo.PerfCnt(Server, Path, InstanceName, CounterType, Timestamp , CookedValue)  
SELECT
[Server] = REPLACE(SUBSTRING(REPLACE([Path],'"', ''),CHARINDEX('\\',REPLACE([Path],'"', ''), 0) ,CHARINDEX('\\',REPLACE([Path],'"', ''), 2)),'\',''),
[Path]      = SUBSTRING(REPLACE([Path],'"', ''),CHARINDEX('\\',REPLACE([Path],'"', ''), 2)+ 2,len([Path])),
[InstanceName] = REPLACE(InstanceName, '"', '') ,
[CounterType] = REPLACE(CounterType, '"' , '') ,
[Timestamp] = CAST(REPLACE((REPLACE([Timestamp], '"', '')), 'PM', '') AS datetime2),
[CookedValue] = CAST(REPLACE(CookedValue, '"', '') AS DECIMAL(22,10))
FROM    dbo.Cnt_Stg;



   -- RETURN 1; -- success

END TRY
BEGIN CATCH
--    RETURN 0;
END CATCH
SET NOCOUNT OFF
END

GO

Any help will be greatly appreciated.

Thanks,

AD_MUD
Aug 30, 2013 at 4:29 PM
AD_MUD,

I have to read through your post again to see how to solve this issue, but I just want to throw another idea out there first.

Have you thought of using the function Write-DataTable instead?
http://gallery.technet.microsoft.com/scriptcenter/2fdeaf8d-b164-411c-9483-99413d6053ae

I use it very succesfully in my code. You are using so many different things that can go wrong in your code plus xp_cmdshell, which most orginizations regard as evil.

I have a few questions but I will give you a possible solution anyway.
  1. Why do you use a CSV? Does that come from a different process? If not, then you can just pull the data into a datatable and load the datatable using Write-Datatable.
  2. Do you have to use the SP to load the data?
My (possible) Solution:
I am assuming that you have a job that collects the data for the CSV and that this data comes from numerous servers. You then you need to parse the data and load it into a table.

I assume you already have the list of servers. I am calling mine $Servers
I am also making this a bit more verbose than normal, just to show you that you are not limited to just one option.

Create DataTables

$MasterTable = New-Object system.Data.DataTable “MasterTable”

Create Columns for MasterTable

$col11 = New-Object system.Data.DataColumn Instance,([string])
$col12 = New-Object system.Data.DataColumn InstNmbr,([int])
$col12 = New-Object system.Data.DataColumn LoadDate,([datetime])

Add Columnd for MasterTable

$Mastertable.columns.add($col11)
$Mastertable.columns.add($col12)
$Mastertable.columns.add($col13)

loop through list of servers

foreach($server in $Servers) {
#get data from $row
#Populate DataTable
$Data = Invoke-Sqlcmd -Query $qry -ServerInstance $server | Out-Datatable
$MasterData += $Data
}

Populate the MasterTable

$GetDate = Get-Date -f o #You want this if you want the same date for every column

If you do not care about the same date, then you can just use the below line rather than the one I gave in the loop

$row.LoadDate = [datetime]::Now.Date ;

$MasterData | % {$row = $MasterTable.NewRow();
             $row.Instance = $($_.Instance -replace ('"','')) ; #Fix data right here
             $row.InstNmbr = $_.InstNmbr ;
             $row.LoadDate = $GetDate ;
             $MasterTable.Rows.Add($row) }
Write-DataTable -ServerInstance "SRVNAME\INSTNAME,PORT" -Database DATALOAD-TableName INSTANCE_INFO -Data $MasterTable

This is just an example and you will have to tweek it a bit, but it should give you a good idea what to do. The above should accomplish everything your current code does. Note that I fix the data when loading the MasterTable ($row.Instance)
This way you only need 1 insert into the database.
Aug 31, 2013 at 5:56 AM
Sorry, I just noticed that my formatting is busted. Let me try again ...
#Create DataTables
$MasterTable = New-Object system.Data.DataTable “MasterTable”
#Create Columns for MasterTable
$col11 = New-Object system.Data.DataColumn Instance,([string]) 
$col12 = New-Object system.Data.DataColumn InstNmbr,([int]) 
$col12 = New-Object system.Data.DataColumn LoadDate,([datetime])
#Add Columnd for MasterTable
$Mastertable.columns.add($col11) 
$Mastertable.columns.add($col12) 
$Mastertable.columns.add($col13)
#loop through list of servers
foreach($server in $Servers) {
#get data from $row and Populate DataTable
$Data = Invoke-Sqlcmd -Query $qry -ServerInstance $server | Out-Datatable
$MasterData += $Data
}
#Populate the MasterTable
$GetDate = Get-Date -f o #You want this if you want the same date for every column
#If you do not care about the same date, then you can just use the below line rather than the one I gave in the loop
#$row.LoadDate = [datetime]::Now.Date ;
$MasterData | % {$row = $MasterTable.NewRow();
             $row.Instance = $($_.Instance -replace ('"','')) ; #Fix data right here
             $row.InstNmbr = $_.InstNmbr ;
             $row.LoadDate = $GetDate ;
             $MasterTable.Rows.Add($row) }
Write-DataTable -ServerInstance "SRVNAME\INSTNAME,PORT" -Database DATALOAD-TableName INSTANCE_INFO -Data $MasterTable