Run a SQL Server Command from PowerShell without the SQL Server Provider
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.
