Powershell: Running a stored proc with dynamic sql not working

I'm using the Powershell code below to call the SP that will do some work. The SP has dynamic sql and is below. The file I'm pulling from is there and has data. In fact when I run the proc from SQL directly it works fine, so PS somehow does not like this call. Can anyone see anything wrong here? Only thing I can think is dynamic SQL won't run through PS...??

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=myserver;Database=Stats;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "spCSVToSQL 'H:\Stats\Stats.csv'"
$SqlCmd.Connection = $SqlConnection
$SqlConnection.Open
$sqlCmd.ExecuteNonQuery
$SqlConnection.Close
===================================================================================
ALTER procedure [dbo].[spCSVToSQL] @filePath varchar(250)
as
Begin

declare @sql varchar(2500) =
'CREATE TABLE ##Stats
(
[StatDate] [varchar](50) NULL,
[Server] [varchar](50) NULL,
[DriveLetter] [varchar](50) NULL,
[Label] [varchar](50) NULL,
[Capacity] [varchar](50) NULL,
[FreeSpace] [varchar](50) NULL,
[PercFree] [varchar](50) NULL,
[PercFrag] [varchar](50) NULL
)
BULK INSERT ##Stats
 FROM ''' + @filePath + '''
 WITH (FIELDTERMINATOR = '','', FIRSTROW = 2, ROWTERMINATOR = ''\n'') 
 INSER开发者_Python百科T INTO [cStats].[dbo].[StatInfo]
 SELECT Convert(VARCHAR, RTRIM(LTRIM(REPLACE([StatDate], ''"'',''''))), 113)
      ,CONVERT(VARCHAR, RTRIM(LTRIM(REPLACE([Server], ''"'',''''))))
      ,CONVERT(VARCHAR, RTRIM(LTRIM(REPLACE([DriveLetter], ''"'',''''))))
      ,CONVERT(VARCHAR, RTRIM(LTRIM(REPLACE([Label], ''"'',''''))))
      ,CONVERT(BIGINT, RTRIM(LTRIM(REPLACE([Capacity], ''"'',''''))))
      ,CONVERT(BIGINT, RTRIM(LTRIM(REPLACE([FreeSpace], ''"'',''''))))
      ,CONVERT(INT, RTRIM(LTRIM(REPLACE([PercFree], ''"'',''''))))
      ,CONVERT(INT, RTRIM(LTRIM(REPLACE([PercFrag], ''"'',''''))))
      FROM ##Stats

      drop table ##Stats'      
   exec(@sql)      
  End

From what you have posted, you are missing brackets in the last three calls:

$SqlConnection.Open()
$sqlCmd.ExecuteNonQuery()
$SqlConnection.Close()

I tested your code here and it works fine. Here is what I did.

• I created a StatInfo table in the Test database with schema that matches exactly ##Stats. I know that this is not the case for you, but this is enough to prove that it works

• I create the sp procedure this way:

  CREATE procedure [dbo].[spCSVToSQL] @filePath varchar(250)
  as
  Begin
  
  declare @sql varchar(2500) =
  'CREATE TABLE ##Stats
  (
  [StatDate] [varchar](50) NULL,
  [Server] [varchar](50) NULL,
  [DriveLetter] [varchar](50) NULL,
  [Label] [varchar](50) NULL,
  [Capacity] [varchar](50) NULL,
  [FreeSpace] [varchar](50) NULL,
  [PercFree] [varchar](50) NULL,
  [PercFrag] [varchar](50) NULL
  )
  BULK INSERT ##Stats
   FROM ''' + @filePath + '''
   WITH (FIELDTERMINATOR = '','', FIRSTROW = 2, ROWTERMINATOR = ''\n'') 
   INSERT INTO [Test].[dbo].[StatInfo]
   SELECT Convert(VARCHAR, RTRIM(LTRIM(REPLACE([StatDate], ''"'',''''))), 113)
        ,CONVERT(VARCHAR, RTRIM(LTRIM(REPLACE([Server], ''"'',''''))))
        ,CONVERT(VARCHAR, RTRIM(LTRIM(REPLACE([DriveLetter], ''"'',''''))))
        ,CONVERT(VARCHAR, RTRIM(LTRIM(REPLACE([Label], ''"'',''''))))
        ,CONVERT(BIGINT, RTRIM(LTRIM(REPLACE([Capacity], ''"'',''''))))
        ,CONVERT(BIGINT, RTRIM(LTRIM(REPLACE([FreeSpace], ''"'',''''))))
        ,CONVERT(INT, RTRIM(LTRIM(REPLACE([PercFree], ''"'',''''))))
        ,CONVERT(INT, RTRIM(LTRIM(REPLACE([PercFrag], ''"'',''''))))
        FROM ##Stats
  
        drop table ##Stats'      
     exec(@sql)      
    End
  
  GO
  

• I create file C:\Stats.csv that looks like this:

  1,2,3,4,5,6,7,8
  1,2,3,4,5,6,7,8
  1,2,3,4,5,6,7,8
  1,2,3,4,5,6,7,8
  1,2,3,4,5,6,7,8
  1,2,3,4,5,6,7,8
  1,2,3,4,5,6,7,8
  1,2,3,4,5,6,7,8
  1,2,3,4,5,6,7,8
  1,2,3,4,5,6,7,8
  

• I ran the following in Powershell:

  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  $SqlConnection.ConnectionString = "Server=(local);Database=Test;Integrated Security=True"
  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
  $SqlCmd.CommandText = "spCSVToSQL 'C:\Stats.csv'"
  $SqlCmd.Connection = $SqlConnection
  $SqlConnection.Open()
  $sqlCmd.ExecuteNonQuery()
  $SqlConnection.Close()
  

• After this I checked the contents of Test.dbo.StatInfo and confirmed that the date from C:\Stats.csv no appear there.

Note, that in order to avoid name clash it might be better to name the temp table #Stats and not ##Stats. The difference is that ##Stats is visible outside the connection that create it, and #Stats is not.

A couple of things stand out: open and close are methods, so use parenthis: i.e.

$SqlConnection.Open()

include an "exec" statement in commandtext: "exec spCSVToSQL 'H:\Stats\Stats.csv'"

I've never had any issues running any kind of sql through Powershell. What happens when "PS somehow does not like this call"? Do you get an error message?