Trouble connecting to SQL database

Ask your PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 15 years and 9 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
oceanhai

Trouble connecting to SQL database

Post by oceanhai »

I'm trying to write a script that will connect to the master db on a list of SQL servers and run a query. I've tried creating the connection objects both inside and outside of the loop but I get various errors. Could somebody point me in the right direction on this?# ==============================================================================================$aServerList = (Get-Content C:ScriptspowershellSQLSQLServers.txt) | Sort-Object$strDatabase = "master"$strSqlQuery = @"select @@version"@$SqlConnection = New-Object System.Data.SqlClient.SqlConnection$SqlConnection.ConnectionString = "Server = $Server; Database = $strDatabase; Integrated Security = True"$SqlCmd = New-Object System.Data.SqlClient.SqlCommand$SqlCmd.CommandText = $strSqlQuery$SqlCmd.Connection = $SqlConnection$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter# $SqlAdapter.SelectCommand = $SqlCmd$DataSet = New-Object System.Data.DataSet$SqlAdapter.Fill($DataSet)$SqlConnection.Close()clear$DataSet.Tables[0]foreach ($Server in $aServerList ){Write-Output $Server$SqlAdapter.SelectCommand = $SqlCmd}
oceanhai

Trouble connecting to SQL database

Post by oceanhai »

I'm trying to write a script that will connect to the master db on a list of SQL servers and run a query. I've tried creating the connection objects both inside and outside of the loop but I get various errors. Could somebody point me in the right direction on this?# ==============================================================================================$aServerList = (Get-Content C:ScriptspowershellSQLSQLServers.txt) | Sort-Object$strDatabase = "master"$strSqlQuery = @"select @@version"@$SqlConnection = New-Object System.Data.SqlClient.SqlConnection$SqlConnection.ConnectionString = "Server = $Server; Database = $strDatabase; Integrated Security = True"$SqlCmd = New-Object System.Data.SqlClient.SqlCommand$SqlCmd.CommandText = $strSqlQuery$SqlCmd.Connection = $SqlConnection$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter# $SqlAdapter.SelectCommand = $SqlCmd$DataSet = New-Object System.Data.DataSet$SqlAdapter.Fill($DataSet)$SqlConnection.Close()clear$DataSet.Tables[0]foreach ($Server in $aServerList ){Write-Output $Server$SqlAdapter.SelectCommand = $SqlCmd}
oceanhai

Trouble connecting to SQL database

Post by oceanhai »

Thanks for your response. I've tried it as shown below and it does return a result but there are a few problems:1. An error flashes in the console just before the query result but does not appear long enough to be read.2. I've currently got two servers in my list and only one result is given3. The line "Write-Output $Server" does not appear at the top of the result (this part works fine if I comment all of the SQL connection info)4. The script is slowMy goal is to loop through the list and run a simple select query against each server.# ================================$aServerList = (Get-Content C:ScriptspowershellSQLSQLServers.txt) | Sort-Object$strDatabase = "master"$strSqlQuery = @"select @@version"@foreach ($Server in $aServerList ){Write-Output $Server$SqlConnection = New-Object System.Data.SqlClient.SqlConnection$SqlConnection.ConnectionString = "Server = $Server; Database = $strDatabase; Integrated Security = True"$SqlCmd = New-Object System.Data.SqlClient.SqlCommand$SqlCmd.CommandText = $strSqlQuery$SqlCmd.Connection = $SqlConnection$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter$SqlAdapter.SelectCommand = $SqlCmd$DataSet = New-Object System.Data.DataSet$SqlAdapter.Fill($DataSet)$SqlConnection.Open()$SqlConnection.Close()clear$DataSet.Tables[0]}
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Trouble connecting to SQL database

Post by jvierra »

Remove teh "clear" statement and report the "first" error.

What yo are seeing is caused by the screen being cleared and by the error causing termination of the pipline "foreach" on teh first error. We need to isolate that exact first error.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Trouble connecting to SQL database

Post by jvierra »

If the server doesn't exist or if it is the wrong type you will get that error. It's generic for an "open" failure with the NET SQLClient. Try using iSQL or oSQL to connect to the server with you credentials.

The SQL Client must be installed on the local machine for any connections to work.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Trouble connecting to SQL database

Post by jvierra »

The server name you are using in the conenct string must be of the form server/instance for non-default instances or for the default instance of SQLExpress.

What is you exact SQLCMD connection line? I suspect their is an issue of instance naming.

The connection for a local SQL instance is as follows:
sqlcmd -S localhost -d master -E
sqlcmd -S localhostinstance -d master -E

This would parallel your connection string in PoSH.

I have tested this on default installations of both SQS200 and SQS2005/Express.

FOr the default instance of most installations of SQLExpress:
sqlcmd -S servernameSQLExpress -d master -E

If these are the string you are using then perhaps your version of PoSH is not up to date or you may be missing NET 2.0 service packs (this is only a guess).

As far as I know the SQLClient for NET 2.0 only uses the installed SQL client components but should have all of teh correct NET components. You might want to explore the version of teh SQLClient NET components and, if SQS2005 or SQLExpress has been installed you might look into the security settings for the transports. They are set very restrictive by default although I don't remember any per prog4ram blocking.

jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Trouble connecting to SQL database

Post by jvierra »

Looks good to me.

SQS 2005 so you have teh latest SQS 2005 client installed on teh local machine?

Do you have SP1 for SQS2005 installed on teh local machine?
What are you network security settings set to for teh SQS2005 client?

Is SQLServer installed on teh local machine?
What network protocols are "enabled" on the local machine?
jvierra2008-05-19 12:47:07
oceanhai

Trouble connecting to SQL database

Post by oceanhai »

I also tried it this way, following jvierra's script, and I get similar errors. Note, I also used a different query just to rule out that being a problem. Thanks again for any suggestions you can provide.# ouput/result begins here =========================PS C:scriptspowershellSQL> ./test2Exception calling "Fill" with "1" argument(s): "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"At C:scriptspowershellSQLtest2.ps1:31 char:28+ $x=$SqlAdapter.Fill( <<<< $DataSet)Unable to index into an object of type System.Data.DataTableCollection.At C:scriptspowershellSQLtest2.ps1:33 char:21+ $DataSet.Tables[0 <<<< ]name----mastermaster# output/result ends here ================================# Script begins here =========================$aServerList = (Get-Content C:ScriptspowershellSQLSQLServers.txt) | Sort-Objectfunction get-versions( $aServerList ){$strDatabase = "master"$strSqlQuery = @"select top (1) name FROM sysdatabases"@foreach($server in $aServerList){ $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server = $Server; Database = $strDatabase; Integrated Security = True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $strSqlQuery $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $x=$SqlAdapter.Fill($DataSet) $x=$SqlConnection.Close() $DataSet.Tables[0] }}get-versions $aServerList
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Trouble connecting to SQL database

Post by jvierra »

You will get that error whenever the NET SQL client cannot connect to the target machine.

I cn run your exact code with no changes except changing the machine names to match hosts I have on my network.

If I use a bad name or turn of the protocols I get the same error you are getting.

To avoid issues with code try running this on a machine that has alocal SQLServer running to see how it works.


Check to be sure you have your protocols enabled in the SQS 2005 configuration tools.
oceanhai

Trouble connecting to SQL database

Post by oceanhai »

The server list file appears to be fine because the array is created successfully, I can do "Write-Output $Server" and I can query the databases. I did try running this locally on the SQL server and it runs fine, so you're right that it must be something with the setup on my local machine. I reinstalled SQL 2005 sp2 and insured that the connection settings were identical as those on the server. I'll also reinstall .NET 2.0 to see if that makes any difference. Any other ideas? Thanks!
This topic is 15 years and 9 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked