Hey, Scripting Guy! How Can I Use the SQL Server Mini-Shell Version of Windows PowerShell?

re: Hey, Scripting Guy! How Can I Use the SQL Server Mini-Shell Version of Windows PowerShell?

Buck Woody — Tue, 21 Dec 2010 17:00:45 GMT

No - you can just install the "Client Tools" from 2K8 or higher to get the snap in. Or just run this command with the right server name:

# 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=(local);database=AdventureWorks;Integrated Security=sspi"

$sqlConnection.Open()

# Create a command object

$sqlCommand = $sqlConnection.CreateCommand()

$sqlCommand.CommandText = "SELECT @@VERSION"

# Execute the Command

$sqlReader = $sqlCommand.ExecuteReader()

# Parse the records

while ($sqlReader.Read())

{

Write-Host $sqlReader["FirstName"]

Write-Host $sqlReader["LastName"]

}

# Close the database connection

$sqlConnection.Close()

$Cellrange.Font.Bold = $True

$Cellrange.EntireColumn.AutoFit()

re: Hey, Scripting Guy! How Can I Use the SQL Server Mini-Shell Version of Windows PowerShell?

Ben Weeks — Wed, 07 Jul 2010 14:23:57 GMT

I would like to connect to SQL server remotely, for example:

(Invoke-Sqlcmd -ServerInstance $ServerInstance -InputFile "C:\WEBTECHY\SQL\databaseusers.sql" -Variable $VariableArray | ConvertTo-XML -NoTypeInformation).save("C:\WEBTECHY\BACKUPS\$ServerInstance\$DatabaseName-Logins.xml")

I can connect to a remote SQL Server if the local machine has SQL Server installed and the snapin added, however, I am unable to do from a machine that does not have full SQL Server installed it seems. I have tried just installing the following SQL Server 2008 features:

# Client Tools Connectivity

# Management Tools - Basic

I was hoping that would install the Snap-In. However, I still can't install the snap-in ... is a full SQL install of the database engine really required?

NB I don't want to have to enable remote PowerShell on the DB servers if I can help it.