≡ Menu

SQLIse A PowerShell SQL Server Query Tools Gets an Update

After the first release of SQLIse (“SQL Ice”)–a basic IDE for T-SQL that includes ability to edit, execute, parse, format SQL code from within PowerShell ISE, several people contacted me to help add new features. The result is SQL Server PowerShell Extensions (SQLPSX) 2.2 with many enhancements to SQLIse…

SQLIse Features

  • Offline parsing of T-SQL code
  • Formatting (prettifying) of T-SQL with an extensive customization abilities
  • Comment/Uncomment T-SQL code
  • Uppercase/Lowercase T-SQL code
  • Execute T-SQL code and output to grid, text, text file or CSV file
  • Apply any of the above actions to selections of code by highlighting
  • Table and object browsers*
  • PoshMode (like sqlcmdmode)*
  • Save output to variable*
  • Save and manage connections*
  • Tab Expansion of schemas, tables, views, functions, procedures, columns and parameters.*
  • OracleIse module*
  • OracleClient module used by OracleIse*
  • Redistributed WPK and ISECreamBasic modules from PowerShellPack and ISECream projects*
  • Created an SQLPSX installer*

* = New features for release 2.2

Credits

My thanks to

  • Steve Murawski for his work creating most of the new GUIs (table/object browsers, saved connections, and output to variable).
  • Bernd Kriszio for his work in adding a slimmed down ISE menu module, IceCreamBasic, all of the Oracle components and his willingness to test and provide feedback to the rest us
  • Max Trinidad put a lot of work into creating an installer for SQLPSX built entirely in PowerShell. Max was very patient in our requests to build several interactions of the installer as we worked through various scenarios.

Next Steps

Comments on this entry are closed.

  • Greg Duncan June 4, 2010, 12:06 pm

    I think the final link on the page is jacked?

  • Chad Miller June 4, 2010, 12:31 pm

    ahh, Trying to use SmartYouTube WP plugin for first time. I put the tag in the wrong place. I think its fixed now.

  • Nick Wber June 14, 2010, 12:27 pm

    I’m trying to re-create your demo in the video but having issues with it. Below is what i have. I enable PoshMode and output to Grib but I keep getting the follow message.

    ###################ERROR#####
    PS C:#[CONNECTED][dcsql8v100vm.master]: > $m = gmo SQLIse; & $m Invoke-ExecuteSql
    Exception calling “Fill” with “1” argument(s): “Incorrect syntax near ‘#’.
    Incorrect syntax near ‘1.0’.”
    At C:UsersnwrDocumentsWindowsPowerShellModulesSQLPSXModulesadolibadolib.psm1:334 char:10
    + $da.fill <<<< ($ds) | Out-Null
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

    ##############SCRIPT#########

    !!setvar dirlist (dir | select name, lastwritetime, length | ConvertTo-Xml -NoTypeInformation -As string)
    declare @doc xml
    set @doc = '$dirList'

    CREATE TABLE #dirlist
    (filename varchar (128),
    LastWriteTime datetime,
    FileSize int)

    insert #dirlist
    SELECT
    item.ref.value('(Property/text())[1]', 'nvarchar(128)') AS Name
    ,item.ref.value('(Property/text())[2]', 'nvarchar(128)') AS LastWritetime
    ,item.ref.value('(Property/text())[3]', 'nvarchar(128)') AS Length
    FROM (SELECT @doc AS feedXml) feeds(feedXml)
    CROSS APPLY feedXml.nodes('/Objects/Object') AS item(ref)

    SELECT * FROM #dirlist

    What am I doing wrong?

  • Chad Miller June 14, 2010, 1:11 pm

    Found a bug with PoshMode after we released 2.2.2. The PoshMode feature requires Beta version 2.2.3 or higher http://sqlpsx.codeplex.com/releases/view/46399 This version is not the default download.

    Are using the 2.2.3 version?

  • Nick Wber June 14, 2010, 2:29 pm

    Just upgraded to the beta 2.2.3 and still getting the same error as above.

  • Chad Miller June 14, 2010, 5:02 pm

    Hmm, I just re-downloaded 2.2.3 and copied/pasted and ran your script without issue. Can you verify 2.2.3, you should see this on line 33 of SQLIse.psm1 file in the SQLIse directory:
    Set-Alias Expand-String $psScriptRootExpand-String.ps1

    Running this in the command-plane $options.PoshMode should return true. One other option, just to verify SQL (must be SQL 2005 or higher):

    From command pane run this after the dirList varaible is set (note clip is only available in Vista or higher OS):
    $dirList | clip

    paste the XML in SSMS and enclose in single quotes. You should be able to run the query in SSMS:
    declare @doc xml
    set @doc =

    CREATE TABLE #dirlist
    (filename varchar (128),
    LastWriteTime datetime,
    FileSize int)

    insert #dirlist

    SELECT
    item.ref.value(‘(Property/text())[1]’, ‘nvarchar(128)’) AS Name
    ,item.ref.value(‘(Property/text())[2]’, ‘nvarchar(128)’) AS LastWritetime
    ,item.ref.value(‘(Property/text())[3]’, ‘nvarchar(128)’) AS Length
    FROM (SELECT @doc AS feedXml) feeds(feedXml)
    CROSS APPLY feedXml.nodes(‘/Objects/Object’) AS item(ref)

    SELECT * FROM #dirlist

  • Nick Weber June 14, 2010, 11:21 pm

    After a few more tests I decided to test it on another Dev VM and it worked. 🙂
    I’m not sure why my main laptop is throwing a error the only difference is that it has SMO for SQL 2008 R2.

    Thanks for your help!!!

    Nick Weber
    @Toshana

%d bloggers like this: