Geeks With Blogs

Lance's TextBox

SQL Server 2008 PowerShell Cmdlets + /n software PowerShell Server v2 = Execute SQL Queries Anywhere.

If you don’t already have the SQL cmdlets for PowerShell, you’ll need to download and install SQL Server 2008.  A warning for those of you running on x64 machines - you’ll have to export the SQL Server keys in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1 (there should be 2 in PowerShellSnapIns and 1 in ShellIds) and reimport them to Wow6432Node.

If you don’t already have /n software’s PowerShell Server V2, you’ll need to install that and run it on the same machine on which the SQL cmdlets are installed.  This server can run as an application or as a service, it doesn’t matter which.

Once you have this setup, you can go to anyplace that has SSH capabilities and open a secure connection to the PowerShell Server.  There are SSH clients nearly everywhere:  Windows, Linux, Cell Phones, Websites, you name it.  Once connected to the PowerShell Server, just send the command.

I created some scripts that I can call (so I don’t have to type so much if I am on a mobile device or something) that issue the actual sql cmdlet commands themselves.  One of them is called sql-getsearches.ps1, and it just tells SQL Server’s invoke-sqlcmd cmdlet to run a stored procedure for me that gives me back the results I want:

$query = "EXEC [table1].[dbo].[sp_LANCE_SEARCH]"
invoke-sqlcmd -query $query -ServerInstance myserver -U myusername -P password

Note: The use of invoke-sqlcmd requires add-pssnapin SqlServerCmdletSnapin100, which I include in my PowerShell Server specific profile (nsoftware.PowerShell_profile.ps1).

If anybody has any questions about getting the SQL Server 2008 cmdlets installed or using the PowerShell Server, feel free to shoot me and email or send me a tweet.







Technorati Tags: , ,
Posted on Monday, December 22, 2008 3:13 PM Software , PowerShell , iPhone | Back to top

Comments on this post: How to Execute SQL PowerShell Commands Anywhere

# re: How to Execute SQL PowerShell Commands Anywhere
Requesting Gravatar...
Quite inspiring,
This is great i can now execute SQL PowerShell commands anywhere,
Anyway, thanks for the post
Left by custom software on Jan 29, 2010 5:41 AM

# re: How to Execute SQL PowerShell Commands Anywhere
Requesting Gravatar...
I need a similar how to for android. That would be very helpful
Left by Dave on Oct 17, 2011 2:43 PM

# re: How to Execute SQL PowerShell Commands Anywhere
Requesting Gravatar...
I am lost on getting a simple powershell to call SQL Server. I finally got over the signed file stuff. But I have no idea what is needed to run a query against SQL Server 2008 in powershell. I did not think it would be this difficult. If you can send me any helpfull ideas as to what to do.
Left by Derrick on Nov 22, 2011 7:31 PM

Your comment:
 (will show your gravatar)

Copyright © Lance Robinson | Powered by: