Run a SQL Server Command from PowerShell without the SQL Server Provider

 

Run a SQL Server Command from PowerShell without the SQL Server Provider

Published 13 April 09 08:57 AM | Buck Woody

Some folks don't have SQL Server 2008 installed - shame on you! If you're in that sad state, you can still run a query against a SQL Server. You will still need the client connection software installed on your system - you'll have that with any 2005 edition of SQL Server, and in many cases you'll have it with just plain old Microsoft Office. It certainly won't hurt to try this script - on a TEST system, of course. Change the Server, instance and database names as appropriate:

# Connect and run a command using SQL Native Client, Returns a recordset

# Create and open a database connection

$sqlConnection = new-object System.Data.SqlClient.SqlConnection "server=SERVERNAME\INSTANCE;database=AdventureWorks;Integrated Security=sspi"

$sqlConnection.Open()

#Create a command object

$sqlCommand = $sqlConnection.CreateCommand()

$sqlCommand.CommandText = "select FirstName, LastName from Person.Contact where LastName like 'W%'"

#Execute the Command

$sqlReader = $sqlCommand.ExecuteReader()

#Parse the records

while ($sqlReader.Read()) { $sqlReader["LastName"] + ", " + $sqlReader["FirstName"]}

# Close the database connection

$sqlConnection.Close()

As always, this warning applies to any script you find anywhere, including here.

Carpe Datum : Run a SQL Server Command from PowerShell without the SQL Server Provider