Sunday, June 14, 2015

Powershell Connection string to an instance of SQL Server 2012 on local computer

We are assuming a named instance 'RegencyPark' on a laptop machine named 'Hodentek8'. The Connection string used for connecting to this instance using PowerShell is in the string variable $ConnectionString:


$ConnectionString="Server=Hodentek8\RegencyPark;database=AdventureWorks2012;trusted_connection=true;"

Windows PowerShell ISE provides intellisense support and for the Connection provides the following drop-down:



You can use the following statements to connect to the instance and open the connection (Properties and Methods):



$ConnectionString="Server=Hodentek8\RegencyPark;database=AdventureWorks2012;trusted_connection=true;"
$Connection = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)
$Connection.Open()
$Connection.State

The last statement when executed displays 'Open'

You can further looks at the properties of the connection string as shown here:
------
PS C:\Users\Jayaram>
 $ConnectionString="Server=Hodentek8\RegencyPark;database=AdventureWorks2012;trusted_connection=true;"

PS C:\Users\Jayaram>  $Connection = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)

PS C:\Users\Jayaram>  $Connection.Open()

PS C:\Users\Jayaram>  $Connection.State
Open

PS C:\Users\Jayaram>  $Connection.Database
AdventureWorks2012

PS C:\Users\Jayaram>  $Connection.Credential

PS C:\Users\Jayaram>  $Connection.DataSource
Hodentek8\RegencyPark

PS C:\Users\Jayaram>  $Connection.ConnectionTimeout
15