Powershell – query from sql table via powershell

powershellquerysql

I've created a SQL Server database with a simple table on my local machine. I try to query some information from this table. Here is my code:

$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]

This failed with the following errors:

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

I got a succeed above "########query drop paths#########", I think I have connect to the local database, but why the rest part failed?

Could anyone would like to help me? please…

Best Answer

The problem is that you defined a connection object called $connection but then use $sqlconnection your code.

Here's the correct code:

######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]
Related Topic