sql - Powershell:使用动态SQL运行存储的proc不起作用

Question

我正在使用下面的Powershell代码来调用SP，它将完成一些工作。 SP具有动态sql，在下面。我要从中提取的文件在那里并且有数据。实际上，当我直接从SQL运行proc时，它工作正常，因此PS某种程度上不喜欢此调用。有人可以在这里看到任何错误吗？我唯一能想到的是动态SQL无法通过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'') 
 INSERT 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

Answer 1

从您发布的内容来看，最近三个电话中缺少括号:

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

我在这里测试了您的代码，效果很好。这是我所做的。

我在测试数据库中创建了一个StatInfo表，该表具有与## Stats完全匹配的模式。我知道情况并非如此，但这足以证明它有效

我以这种方式创建sp过程:

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

我创建文件C:\ Stats.csv，如下所示:

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

我在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()

之后，我检查了Test.dbo.StatInfo的内容并确认C:\ Stats.csv中的日期没有出现在这里。

请注意，为了避免名称冲突，将临时表命名为 #Stats而不是 ##Stats可能更好。区别在于 ##Stats在创建它的连接外部可见，而 #Stats不可见。