query from sql table via powershell

Question

I am a freasher powershell learner. Here is a problem stucked...please give me some suggestion!

the case is:

I create a sql server database with one simple table on my local machine. I try to query some information from this table. Here is my 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 = $SqlConnection

$DataSet = New-Object System.Data.DataSet

$SqlAdapter.Fill($DataSet) 

$SqlConnection.Close()

$DataSet.Tables[0]

I 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...

Answer 1

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]