SQL Server connectivity and script execution using PowerShell

Question

I am designing a PowerShell script that can execute some sql scripts in a folder. Actually this must be done remotely. I am able to connect to the particular server remotely using:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = servername; Database = dbname; Integrated Security = True" 
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

But when I use:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$server = new-object ("Microsoft.SqlServer.Management.Smo.Server")"SQL01\Instancename" 

I am getting an error related with SMO dll. Since I am trying to execute remotely, in this machine SQL Server is not installed so SMO.dll is not available. How can I achieve my aim?

Answer 1

You need to have the accompanying assembly on the machine that you are trying to load on.

If you want to use SMO you have two options:

1. Install the required files on that machine
2. Run your script on a box that already has SMO

In order to install SMO without having to install SQL Server, follow the instructions on this BOL reference, Installing SMO.

• Vysakh: Thanks thomas..But I copied that dll to the server and tried specifying the path of that dll,but that also failed..Is there any other alternative other than SMO?
• Thomas Stringer: Take a look at this link on installing SMO: msdn.microsoft.com/en-us/library/ms162189.aspx
• Vysakh: Thanks for that..But as I said I cant install SQL server in this server(since I am trying to connect it remotely)..Any other alternative???
• Aaron Bertrand: @Vysakh installing SMO is not as simple as copying a .dll. You either need to install SMO on the machine where you're trying to run the PowerShell script, or choose a machine that already has SMO, or save the PowerShell script on the SQL Server machine and run it from there (it will need to have access to whatever folder the scripts are in).
• Thomas Stringer: @Vysakh As Aaron said, you can't just do the copy. When you run the SQL Server setup on your machine, you don't necessarily have to install SQL Server. From that link I provided in my above comment: "To install the Client Tooks SDK without installing SQL Server, install Shared Management Objects from the SQL Server feature pack".