Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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] 
share|improve this answer
    
Thanks!! I got the result!! –  pansal Jul 7 '11 at 1:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.