Error when trying to alter an existing stored procedure with PowerShell

Question

I follow this MSDN article to alter an existing stored procedure with PowerShell: https://msdn.microsoft.com/en-us/library/ms162190.aspx
(No need to read that)

Inside this example the following line throws an error:

$sp = New-Object -TypeName Microsoft.SqlServer.Management.SMO.StoredProcedure -argumentlist $db, "GetUserPhoneForService"

The error is

New-Object : The type [Microsoft.SqlServer.Management.SMO.StoredProcedure] cannot be found. Be sure you loaded the assembly containing this type.

(I translated the error message from German. So the wording might be a bit different)

Other database actions like simple updates work without any problem.

My first guess is that you didn't start powershell from SQL Management Studio (right click on server and select "Start Powershell") and therefore don't have the SQL module imported into the shell. Is that the case? — jeffesp
– jeffesp, Commented Jun 24, 2015 at 12:35
That is right. But I did not have to start from Management studio when updating data, either (using system.data.sqlclient.sqlcommand). I am trying to write a script for my admin collegues to run directly from shell. — Ole Albers
– Ole Albers, Commented Jun 24, 2015 at 12:41
See @vonPryz answer for the way to import the module you need. System.Data.SqlClient.SqlCommand is something in the default .NET Framework, where the SMO objects are only there if Management Studio is installed in my experience. — jeffesp
– jeffesp, Commented Jun 24, 2015 at 12:44

vonPryz · Accepted Answer · 2015-06-24 12:36:11Z

1

You need to load the Sql Powershell provider. Use Get-Module -ListAvailable to see all the offered modules. Load the sqlps with Import-Module like so,

import-module sqlps

answered Jun 24, 2015 at 12:36

vonPryz

24.3k10 gold badges58 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error when trying to alter an existing stored procedure with PowerShell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related