通過powershell從sql表查詢

我在本地機器上創建了一個帶有簡單表的 SQL Server 數據庫。我嘗試從這個表中查詢一些資訊。這是我的程式碼：

$connection= New-Object system.data.sqlclient.sqlconnection 
$Connection.ConnectionString = `
 "server=.;database=sharespace;trusted_connection=True" 
$connection.open()
#########query drop paths##########################################
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
$SqlQuery = "select Droppath from sharespace" 
$SqlCmd.CommandText = $SqlQuery

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$SqlCmd.Connection = $SqlConnection

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet) 
$SqlConnection.Close()

$DataSet.Tables[0]

這失敗了，出現以下錯誤：

Exception calling "Fill" with "1" argument(s): "A network-related or 
instance-specific error occurred while establishing a connection to SQL Server. 
The server was not found or was not accessible. Verify that the instance name is
correct and that SQL Server is configured to allow remote connections. 
(provider: SQL Network Interfaces, error:
25 - Connection string is not valid)"
At D:\query.ps1:31 char:17
+ $SqlAdapter.Fill <<<< ($DataSet) 
+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException

我在“########query drop paths#########”上面成功了，我想我已經連接到本地數據庫，但是為什麼其餘部分失敗了？

有人願意幫助我嗎？請…

問題是您定義了一個名為的連接對象 $ connection but then use $ sqlconnection 你的程式碼。

這是正確的程式碼：

######connect to windows authentication local database################################ 

$connection= new-object system.data.sqlclient.sqlconnection #Set new object to connect to sql database 

$Connection.ConnectionString ="server=.;database=sharespace;trusted_connection=True" # Connectiongstring setting for local machine database with window authentication 

Write-host "connection information:" 

$connection #List connection information 

Write-host "connect to database successful." 

$connection.open() #Connecting successful 

#########query drop paths############################################################ 

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand #setting object to use sql commands 

$SqlQuery = "select Droppath from sharespace" #setting query "get drop paths"  

$SqlCmd.CommandText = $SqlQuery # get query 

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter # 

$SqlAdapter.SelectCommand = $SqlCmd # 

$SqlCmd.Connection = $connection 

$DataSet = New-Object System.Data.DataSet 

$SqlAdapter.Fill($DataSet)  

$connection.Close() 

$DataSet.Tables[0] 

引用自：https://serverfault.com/questions/287398