Invoke-NAVSQL: Execute SQL queries on NAV databases with PowerShell

Just another CmdLet to bother you with (no really, it’s quite an interesting one – try to keep awake … ;-)).

The more you use PowerShell .. and the more you use it for NAV .. you just realize that you have to call out to SQL Server quite regularly. Just a few examples:

• Backup and restore – Get the default restore directories
• Update/select the UidOffSet
• Unlocking all objects while upgrading
• Granting service user db-owner access
• … (and a lot more…)

Some time ago, I have been spending time in making the call to an NAV database a little bit simpler from PowerShell.

Goals

Simplicity is key here – I just want to provide a ServerInstance, and the function needs to figure out how to connect to the database. Next, if I had some kind of “select” statement, it needed to be simple to get to the data. As you know, PowerShell returns Objects .. so in case of this new function, columns needed to be properties, and records needed to be elements in my resulting object collection.

Invoke-NAVSQL

I decided to call the function “Invoke-NAVSQL” – according to a similar existing function part of the SQLPS module. You can find the function on my github.

A few major things that stand out in the script:

• First of all, it is going to get all details from the Server Instance, which it needs to figure out on which database details it has to execute the SQL command. I do this with Get-NAVServerInstanceDetails (also part of my github module).
• I use quite a default way (.Net) to connect to the database and execute the query to get back a dataset. In my understanding, this way of working with SQL Server is not dependent of any modules, but it just going to use the libraries in .Net – which makes the function somewhat more independent. But I could be wrong (comments always appreciated ;-)).
• At the end, I’m converting the dataset to a collection of objects, where all fields are properties.

So, if the SQLCommand was a SELECT, this function returns you a clear and user friendly object model.

Some examples

To have a look at the UidOffSet property of our database that is attached to server instance “DynamicsNAV90”:

$dbproperties = Invoke-NAVSql -ServerInstance 'dynamicsNAV90' -SQLCommand 'Select * From [$ndo$dbproperty]'
$dbproperties.uidoffset

Get all companies, and get the customers of the first company:

$Mycompanies = Invoke-NAVSql -ServerInstance 'dynamicsNAV90' -SQLCommand 'Select * From Company'

$Customers = $Mycompanies |
                  select -First 1 |
                        foreach{ Invoke-NAVSQL -ServerInstance 'dynamicsNAV90' -SQLCommand "select * from [$($_.Name)$('$Customer')]" -ShowWriteHost }

$Customers | select 'No_', Name, 'Credit Limit (LCY)' | Format-Table -AutoSize

Assumptions

I know .. “Assumption is the mother of all fuckups“. But simplicity often comes with a number of assumptions, doesn’t it? ;-). My big assumption is the security-part. Namely, I assume the PowerShell user (Windows Authentication) has got access to the SQL Server, and obviously enough permissions to perform the query.