我正在尝试从powershell脚本中的SQL表中读取数据。我可以在reader对象中看到数据,但是当使用While (readerobject.read()){}
读取它时,它不会进入循环中。
Powershell
$cmd = $sqlConn.CreateCommand()
$cmd.CommandText ="SELECT * from user"
$movedUserDetails = $cmd.ExecuteReader()
while ($movedUserDetails.Read())
{
"[0] : " + $movedUserDetails.GetValue(0)
}
$movedUserDetails.Close()
发布于 2016-01-16 00:05:50
语法是正确的,但是一旦进入循环,就不会对值做任何操作。你想以某种方式持久化它。下面是一个在powershell中运行一些基本SQL的示例,使用两种不同类型的命令(Text/SP)和两种不同的执行方法(DataAdapter/DataReader)。其中任何一个都应该工作得很好。
# config
$svr = "serverName"
$db = "databaseName"
# connection
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server=$svr;Database=$db;Integrated Security=True"
$sqlConnection.Open()
# command A - text
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.Connection = $sqlConnection
$sqlCmd.CommandText = "SELECT name AS TABLE_NAME FROM sys.tables"
# command B - stored procedure
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.Connection = $sqlConnection
$sqlCmd.CommandText = "sys.sp_tables"
$sqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
$sqlCmd.Parameters.Add("@table_owner", "dbo")
# execute A - data reader
$reader = $sqlCmd.ExecuteReader()
$tables = @()
while ($reader.Read()) {
$tables += $reader["TABLE_NAME"]
}
$reader.Close()
# execute B - data adapter
$dataTable = New-Object System.Data.DataTable
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sqlAdapter.SelectCommand = $sqlCmd
$sqlAdapter.Fill($dataTable)
$sqlConnection.Close()
发布于 2015-05-21 23:30:22
下面的答案是一个但已经过时了。您现在应该使用Invoke-Sqlcmd。
Invoke-Sqlcmd -Query $sqlStatement -ConnectionString $ConnectionString;
您可能需要执行Install-Module -Name SqlServer
才能安装它。
如果您不想安装SqlServer
模块,或者您的Powershell版本是2或更低,您可以尝试使用SqlDataAdapter
。我制作了这个Powershell模块,用于使用SQL获取记录。它从未让我失望过
function Invoke-SqlSelect
{
[CmdletBinding()]
Param
(
[ValidateNotNullOrEmpty()]
[Parameter(ValueFromPipeline=$True,Mandatory=$True)]
[string] $SqlServer,
[Parameter(ValueFromPipeline=$True,Mandatory=$False)]
[string] $Database = "master",
[ValidateNotNullOrEmpty()]
[Parameter(ValueFromPipeline=$True,Mandatory=$True)]
[string] $SqlStatement
)
$ErrorActionPreference = "Stop"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server=$SqlServer;Database=$Database;Integrated Security=True"
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.CommandText = $SqlStatement
$sqlCmd.Connection = $sqlConnection
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sqlAdapter.SelectCommand = $sqlCmd
$dataTable = New-Object System.Data.DataTable
try
{
$sqlConnection.Open()
$sqlOutput = $sqlAdapter.Fill($dataTable)
Write-Output -Verbose $sqlOutput
$sqlConnection.Close()
$sqlConnection.Dispose()
}
catch
{
Write-Output -Verbose "Error executing SQL on database [$Database] on server [$SqlServer]. Statement: `r`n$SqlStatement"
return $null
}
if ($dataTable) { return ,$dataTable } else { return $null }
}
发布于 2016-12-13 20:27:46
我有完全相同的问题,我相信原因如下(对我有效):
数据库连接并不总是关闭的,例如,在出错的情况下。如果它没有关闭,它将跳过while循环。将您的代码更改为以下代码:
$sqlConn.Open()
$cmd = $sqlConn.CreateCommand()
$cmd.CommandText ="SELECT * from user"
$movedUserDetails = $cmd.ExecuteReader()
try
{
while ($movedUserDetails.Read())
{
"[0] : " + $movedUserDetails.GetValue(0)
}
}
catch
{
#log error
}
finally
{
$sqlConn.Close()
}
finally语句将始终执行,并将确保连接正确关闭。
https://stackoverflow.com/questions/30375519
复制相似问题