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

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

  • Comments 4

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.

Leave a Comment
  • Please add 7 and 1 and type the answer here:
  • Post
  • PingBack from http://fixmycrediteasily.info/story.php?id=7922

  • This is exactly what I have been looking for. So many of them want to run with the SQL Powershell plugin. Well what if I want to use a separate computer.

    Anyway Thanks.

  • This is great. How do I send the output into a CSV file?

  • The aboved script didn't work , it stopped at $sqlConnection.Open()

    Exception calling "Open" with "0" 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: 26 - Error Locating

    Server/Instance Specified)"

    At line:7 char:1

    + $sqlConnection.Open()

    + ~~~~~~~~~~~~~~~~~~~~~

       + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

       + FullyQualifiedErrorId : SqlException

    Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader requires an open and available Connection. The connection's current state is closed."

    At line:12 char:1

    + $sqlReader = $sqlCommand.ExecuteReader()

    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

       + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

       + FullyQualifiedErrorId : InvalidOperationException

    You cannot call a method on a null-valued expression.

    At line:14 char:8

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

    +        ~~~~~~~~~~~~~~~~~

       + CategoryInfo          : InvalidOperation: (:) [], RuntimeException

       + FullyQualifiedErrorId : InvokeMethodOnNull

Page 1 of 1 (4 items)