2

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?

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
9
  • 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?
    – Vysakh
    Commented Jun 20, 2013 at 11:22
  • Take a look at this link on installing SMO: msdn.microsoft.com/en-us/library/ms162189.aspx
    – Thomas Stringer
    Commented Jun 20, 2013 at 12:06
  • 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???
    – Vysakh
    Commented Jun 20, 2013 at 12:18
  • 1
    @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).
    – Aaron Bertrand
    Commented Jun 20, 2013 at 12:21
  • @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".
    – Thomas Stringer
    Commented Jun 20, 2013 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.