☰

通过 powershell 从 sql 表进行查询

tag-icon • tag-icon sql powershell query
通过 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 path#######”成功了，我认为我已经连接到本地数据库，但是为什么其余部分失败了？

有人愿意帮助我吗？拜托...

答案1

问题在于您定义了一个名为 $connection 的连接对象，但在代码中使用了 $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]

相关内容