Using invoke-sqlcmd with SQL Server 2005

Question

Using invoke-sqlcmd with SQL Server 2005

SwedishOrr

SSCrazy

Points: 2955
More actions
July 14, 2010 at 1:01 pm

#148074

I've got a powershell script that uses invoke-sqlcmd. This works just fine on the server running SQL Server 2008 when I execute the command via a SQL Server Agent job (Powershell subsystem).
However, when I try to run the same script on a server running SQL Server 2005 (CmdExec subsystem), I get an error saying that invoke-sqlcmd is not a recognized cmdlet.
I followed the steps outlined in the following page, but to no avail:
http://blogs.mssqltips.com/forums/t/1156.aspx
i.e.
PS> add-pssnapin sqlserverprovidersnapin100
Add-PSSnapin : No snap-ins have been registered for Windows PowerShell version 2.
Once I've installed the SMO and PS Extension MSIs, I try to see the new snapins via get-pssnapin -reg but nothing shows up. They don't show up when I execute get-pssnapin either.
Both servers are x64 and both have PowerShell 2.0 installed. If anyone can help me out on this I would be extremely appreciative. Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 1

Is the SQL Server 2005 a 64 bit install? If it's a 32 bit version, it'll be trying to load the 32 bit version of the commandlet. I've run into this problem with a 3rd party tool.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 2

Hi

Powershell integration with SQL Server is introduced in SQL Server 2008.

I don't think you can use powershell from within SQL Server 2005 or through Agent Job.

Thank You,

Best Regards,

SQLBuddy

SwedishOrr SSCrazy Points: 2955 More actions · Answer 3

Hey guys, thanks for the quick replies. The SQL Server 2005 instance is Standard Edition 64-bit. I do see the SQLPS utility in the C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Redist directory now. I'm using this mini-shell to run my powershell script using the CmdExec subsystem in the job step.

This solves the issue for the short-term. But something about it just reeks of sloppyness, shodddiness, brittleness, you name it.

What do you think Grant?

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 4

SwedishOrr (7/14/2010)
Hey guys, thanks for the quick replies. The SQL Server 2005 instance is Standard Edition 64-bit. I do see the SQLPS utility in the C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Redist directory now. I'm using this mini-shell to run my powershell script using the CmdExec subsystem in the job step.
This solves the issue for the short-term. But something about it just reeks of sloppyness, shodddiness, brittleness, you name it.
What do you think Grant?

SQLBuddy nailed it. You can't run PowerShell directly from 2005. But it's possible to instantiate the SQL snap-ins into your standard PowerShell interface by modifying your user profile script. Follow the directions here adding the SQL Provider and SMO scripts then you should be OK. Then you don't have to run the, I think, stupid SQLPS utility. Then you'll still have to run it through a command line, not through the Agent like in 2008.

Another option is to use 2008 and multi-server management there to run your powershell scripts across multiple environments.

Another option, one that I'm just now exploring (look for a blog post if I figure it out), is to use invoke-command to run scripts in parallel across multiple servers.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SwedishOrr SSCrazy Points: 2955 More actions · Answer 5

Thanks for the replies guys. I haven't had any luck instantiating the snap-ins into the PowerShell. I also had trouble finding the actual profile to use. I tried notepad $profile but that told me "The system could not find the specified path."

These two reasons pushed me to just using the SQLPS minishell for the time being. If anyone has a good, succinct, accurate article on either of those two topics, I would be very interested and appreciative. I'm still searching...

Thanks again!

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 6

SwedishOrr (7/15/2010)
Thanks for the replies guys. I haven't had any luck instantiating the snap-ins into the PowerShell. I also had trouble finding the actual profile to use. I tried notepad $profile but that told me "The system could not find the specified path."
These two reasons pushed me to just using the SQLPS minishell for the time being. If anyone has a good, succinct, accurate article on either of those two topics, I would be very interested and appreciative. I'm still searching...
Thanks again!

If you just run $profile you get a path not found error? That's not good. Usually you'll see the profile in $\Documents\WindowsPowerShell and it's called Microsoft.PowerShell_profile.ps1. If you're getting something else, you've got configuration issues to deal with. You might do a little registry walk and see what you have under hkey_current_user\environment.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning