Dave's Blog

Config File Gotcha

David Roys — Fri, 03 Feb 2012 10:08:44 GMT

I came across something that was a bit weird in an implementation where every user in a Citrix deployed RoleTailored client suddenly connected to the TEST system instead of the LIVE system. I thought I’d share this gotcha to save others from making the same mistake.

When you start Dynamics NAV 2009’s RoleTailored client, there are a three ways for the client to determine which server to connect to. This is handy to know because most people want to have a live system and a test system shortcut on their desktop and it’s nice to know where you’re going when you start the client.

Default ClientUserSettings.config

The first method is the default method where the system simply reads your settings from the ClientUserSettings.config file which you can find in

%ProgramData%\Microsoft\Microsoft Dynamics NAV\

That’s pretty easy because you don’t need to do anything. The file gets created automatically and if you connect to a different server, the new value gets saved in the file. Not very useful for switching between Live and Test though.

Startup .config file

This one’s pretty neat. When you start the client, you can add a command line parameter to specify the location of the config file you want to use. The parameter is settings and here’s an example of the shortcut you would need.

Microsoft.Dynamics.Nav.Client.exe -settings:NavLive.config

But this is where the gotcha comes in. If you use this settings file to start the client and then connect to a different server, the change gets written back to the config file. That’s not so good when you were trying to use different files to point you to either Live or Test systems. It’s even worse if you are using Citrix to publish the application and every user is updating the same config file. Whilst you don’t get errors, it can be a bit confusing if one user starts up the LIVE system and then jumps to the TEST server which writes the settings back to the config file so the next time a user tries to open the LIVE system they end up in TEST. One solution to this is to make the config files read-only, the other is to use the startup URI.

Startup URI

I quite like this option for a couple of reasons. First of all, it’s really obvious which system you’re connecting to. Secondly, if you’re connecting to a system that is not your default server (remember the ClientUserSettings.config file?) then the RTC puts up a helpful message telling you. This is great for having two shortcuts that will take you into live or test and it’s not going to mess with your ClientUserSettings.config file either.

The syntax for the URI allows you to specify which company you want to start in too. You could use this to start up on a specific page if you like.

Microsoft.Dynamics.Nav.Client.exe "dynamicsnav://NAVSERVER/ServiceName/CompanyName"

The double quotes are needed if your company name contains spaces.

Service Tier and Classic Client Monstrous Nightmare with FieldRef

David Roys — Fri, 12 Aug 2011 09:49:27 GMT

I came across an error today that was a monstrous nightmare to track down. First of all, it worked in the Classic client but not in the RoleTailored client. Sigh. Don’t you just hate that? It means going through the pain of debugging the RoleTailored client.

The error was caused because the FieldRef.Value function for a FieldRef that points to an Option type field returns different values in the Classic client and RoleTailored client.

If you want proof, try running this Codeunit in both the RTC and Classic.

// ServiceTier returns different values for FieldRef.Value for Option
// fields compared to Classic client.

lRecordRef.OPEN(DATABASE::"Sales Header");

lFieldRef := lRecordRef.FIELD(1);

MESSAGE('ISSERVICETIER=%1\Document Type=%2',ISSERVICETIER,lFieldRef.VALUE);

You’ll see the following results. Classic client first…

Then the RoleTailored client…

You see that? One returns the string value and the other returns the numeric value for the option string.

The error we were getting was because the field was being used as an Integer which was throwing a run time error whenever it hit that particular bit of code but only when using the RTC.

Most of the time in finding this error was in tracking down the line of code that was causing the error to be thrown. I was reading the Statement of Direction for NAV the other day and noticed it suggests we’ll be getting an easy-to-use debugger for the RTC in a future version – I can’t wait! Let’s hope in the future I can say “break on error” and just run my RoleTailored client and find out what the issue is straight away.

Creating New Windows Logins in NAV Through a SQL Sproc

David Roys — Sat, 23 Jul 2011 09:02:42 GMT

If you’ve been following my on-going battle against the pigs, you’ll be pleased to know that I now have attained three stars on all of the levels in Angry Birds Seasons, Angry Birds, and Angry Birds Rio, so with no more pigs to kill until the next update, I figured I’d write another NAV blog.

One of the reasons I started blogging was to have a place where I could save little gems so they can be easily found later on. One of the directors at Intergen suggested that I blogged the information and then use Google to find it – so that’s what I did. This week I needed to find something I knew I’d done before, but it took me quite a while to track it down, so I figured it’s time to blog the solution so I need never struggle again – and who knows, it may help some of you out too.

Have you ever had a SQL Backup to restore to a SQL Server that does not allow mixed mode (no database logins, only Windows users) and you need to open the NAV database using NAV but don’t have a login? I created the following SQL code to do this, but you may find it useful to be able quickly add all of the Windows users for your team to a customer’s database. Or how about using the ability to call SQL Stored Procs from NAV (recently blogged about by Waldo) to allow new Windows users to be added to NAV from the RoleTailored client – now that sounds useful!

Don’t forget to tell SID

NAV stores security for Windows users with a SID which is a funny string that starts with “S” and the first thing I need is something that will give me the SID string for a Windows login.

Create the following function in the master database.

USE master     
GO     
CREATE FUNCTION fn_SIDToString     
(     
 @BinSID AS VARBINARY(100)     
)     
RETURNS VARCHAR(100)     
AS  
 BEGIN     
    IF LEN(@BinSID)%4<>0 RETURN(NULL)     
     
    DECLARE @StringSID VARCHAR(100)     
    DECLARE @i AS INT     
    DECLARE @j AS INT     
     
    SELECT @StringSID='S-'+CONVERT(VARCHAR,CONVERT(INT,CONVERT(VARBINARY,SUBSTRING(@BinSID,1,1))))     
     
    SELECT @StringSID=@StringSID+'-'+CONVERT(VARCHAR,CONVERT(INT,CONVERT(VARBINARY,SUBSTRING(@BinSID,3,6))))     
     
    SET @j=9     
     
    SET @i=LEN(@BinSID)     
     
    WHILE @j<@i     
    BEGIN     
        DECLARE @val BINARY(4)     
         
        SELECT @val=SUBSTRING(@BinSID,@j,4)     
         
        SELECT @StringSID=@StringSID+'-'+CONVERT(VARCHAR,CONVERT(BIGINT,CONVERT(VARBINARY,REVERSE(CONVERT(VARBINARY,@val)))))     
         
        SET @j=@j+4     
    END     
     
    RETURN(@StringSID)     
END

Between a Sproc and a Hard Place

Once you have this function, the following stored procedure will create a Windows Login, add it as a windows user, and finally make the user a member of the SUPER role. I’ve hard-coded the database name in this sproc, but you should be able to easily make this a parameter to the sproc if you wish.

CREATE PROCEDURE [dbo].[proc_CreateNavUser]    
@User  sysname    
AS  
SET NOCOUNT ON    
    
DECLARE @SQL VARCHAR(MAX)    
DECLARE @RetVal INTEGER    
    
SET @RetVal=0    
    
BEGIN TRY    
    IF(SELECT COUNT(*)    
       FROM master.sys.server_principals    
       WHERE name=@User)=0    
    BEGIN    
        SET @SQL='use master CREATE LOGIN ['+@User+'] FROM WINDOWS WITH DEFAULT_DATABASE=[master]'    
         
        EXECUTE(@SQL);    
    END    
     
    IF(SELECT COUNT(*)    
       FROM [Demo Database NAV (6-0)].sys.database_principals    
       WHERE name=@User)=0    
    BEGIN    
        SET @SQL='use [Demo Database NAV (6-0)] CREATE USER ['+@User+'] FOR LOGIN ['+@User+']'    
         
        EXECUTE(@SQL);    
    END    
END TRY    
        BEGIN CATCH    
    SELECT Retval=@RetVal    
     
    RETURN    
END CATCH    
    
BEGIN TRY    
    SET @RetVal=1    
     
    INSERT INTO [Demo Database NAV (6-0)].dbo.[Windows Login]([SID])    
    SELECT master.dbo.fn_SIDToString(sp.sid)    
    FROM master.sys.server_principals sp    
    WHERE sp.name=@User AND master.dbo.fn_SIDToString(sp.sid) NOT IN(SELECT sid    
                                                                     FROM [Demo Database NAV (6-0)].dbo.[Windows Login])    
           
--Add Roles (SUPER)    
           
    INSERT INTO [Demo Database NAV (6-0)].dbo.[Windows Access Control]([Login SID],[Role ID],[Company Name])    
    SELECT master.dbo.fn_SIDToString(sp.sid),'SUPER',''    
    FROM master.sys.server_principals sp    
    WHERE sp.name=@User AND master.dbo.fn_SIDToString(sp.sid) NOT IN(SELECT [Login SID]    
                                                                     FROM [Demo Database NAV (6-0)].dbo.[Windows Access Control]    
                                                                     WHERE [Role ID]='BASIC')    
     
    SELECT Retval=@RetVal    
END TRY    
        BEGIN CATCH    
     
END CATCH

Enjoy the srpoc. If you do write something that uses this – like a tool to create Windows logins in the RoleTailored client, why not share your solution too?

Now it’s time to play Cut the Rope.

Make me squeal

David Roys — Mon, 20 Jun 2011 10:07:14 GMT

If God had wanted me to work, he wouldn’t have given me Angry Birds.

I figured it was about time I wrote something about Dynamics NAV again, so setting my continuing mission to get three stars on all levels to one side, I’ve finally taken time to pass on a couple of tips around .NET Interop and executing SQL Stored Procs from within NAV (I used to work with someone that pronounced SQL as “Squeal” instead of “Sequel” hence the title of this post).

Way back in April (wow has it been that long?) I wrote about how you could use .NET Interop to execute SQL commands using NAV 2009 R2. In my example I executed a simple SQL command and showed how you could parse the dataset returned from the command.

When I finally came to put this into practice I discovered I had made two errors in my original post which proved to me that I am better at Angry Birds than I am at NAV programming and that, whilst I get many people reading my blog, it’s rare that people actually try out the things I write about.

The Host with the Most

My first mistake was when I said that I would use the session table to get the machine name of the SQL Server. Wrong! Yes it worked in my example, but that was only because I was running the NAV Server on the same box as the SQL Server. The “Host Name” field on the Session table is the machine name that initiated the connection, so when I moved my code to three tiers it didn’t work. So how do you find the name of the SQL Server you are connecting to?

Here’s one way that works. I created a view that includes the server name like so:

CREATE VIEW [dbo].[SQLConnectionDetails] AS
SELECT @@ServerName AS [Server Name],
DB_NAME() AS [Database Name]

Then I made a linked table in NAV over the top of the view. Now I can use SQLConnectionDetails.[Server Name] as part of my connect string. Phew!

Enum Nuts

My next mistake was when I assumed that the enumerated value for a SQL Stored Proc command type was 1. Wrong again! I’d made that assumption because the documentation on the properties of the CommandType showed a table with three options then they would be numbered 0, 1, and 2. Finding the correct integer value was not easy but in the end it was Waldo to the rescue. I knew I had read a post from Waldo about using enumerated types in .NET interop so I used his code to parse the value. Here’s how to do it for the SQL command type.

//Set the command type as StoredProcedure.
dnCommandType := dnSQLCommand.CommandType;
dnSQLCommand.CommandType := dnCommandType.Parse(dnCommandType.GetType(),'StoredProcedure');

Much better to have this code that is self-documenting than put in the numeric values but if you’re feeling lazy, the number values are: Text is 1, StoredProcedure is 4 and TableDirect is 512.

Pulling my heir out

My final problem (not a mistake this time but something I thought I would pass on) was when I came to try to add parameters to my stored procedure I hit a problem I thought I couldn’t get past.

I could not instantiate a Parameter collection on my SQL Command because NAV thought for some crazy reason that there were two Parameters properties. Now .NET (as far as I know) does not support overloaded properties so this is not possible and I can only put it down to a bug in the R2 release. I have reported this to the product team and I am assured this problem will be fixed in a later release.

So if I can’t add parameters to my stored proc, how can I get around it? My solution was to instantiate a variable of type System.Data.Common.DbCommand which the SQLCommand class inherits. I could then use the Parameters property of the Common.DbCommand and assign this to my SQLParameterCollection. Don’t you just love object oriented?

dnCommonDBCommand := dnSQLCommand;

// Need to use the dnCommonDBCommand.Parameters becuase the SQLCommand.Parameters gets confused in the R2 Release
dnSQLParameterCollection := dnCommonDBCommand.Parameters();
dnSQLParameterCollection.Add(pParameterName,pParameterValue);

Now I have instantiated my SQLParameterCollection, I can use the Add method and add my new parameters.

Well that’s it for now. Time to try to crack three stars on level 10. Die pigs, die!

20-30% Discount on Dynamics Books During May 2011

David Roys — Tue, 17 May 2011 07:25:11 GMT

Those people at PACKT have been busy making more and more books on Dynamics covering GP 2010, NAV 2009, AX 2009, and Sure Step, so whichever flavour of Dynamics you prefer, there’s something for you to get your teeth into.

To celebrate the recent publication of the Microsoft Dynamics GP 2010 Reporting book, PACKT has announced a series of attractive discounts on their Dynamics books:

Buy any Microsoft Dynamics printbook and get 20% off
Buy any Microsoft Dynamics eBook and get 30% off

So if you’ve been waiting to buy a copy of the book I wrote with Vjeko (Implementing Microsoft Dynamics NAV 2009) now’s your chance to order your copy and save some money. Our book has funny references and quotes, a comparison between the Chart of Accounts and a Hippo’s Bottom, and a section on how to perform analysis and design that uses super villains from a comic book as the users. There’s even some sensible stuff in there too. OK I’ll admit that some of the details about Web services is old news now and was written for NAV 2009, and there’s nothing on the things that came with SP1 and R2, but it’s a good fun read and, who knows, you might learn a thing or two along the way.

Execute SQL Command using .NET Interop

David Roys — Wed, 13 Apr 2011 10:12:00 GMT

OK, I’ll admit it, I’m obsessed with the .NET Interop features in NAV 2009 R2. My last two blog posts have been on this and now here’s another one.

Today I wanted to see if I could use the .NET Interop features in NAV 2009 R2 to execute a SQL Stored Procedure and extract the results. I know you can do this using COM, but I figured it would be fun to try this using .NET. The great thing about .NET is it’s so well documented. My starting point for this exercise was to search for executing a SQL Stored Procedure in C# which gave me this post http://support.microsoft.com/kb/320916

I then used that to figure out which .NET classes I needed to write my C/AL code.

In this sample I’m using a SQL Command rather than a stored procedure call, but that’s just so you can try it for yourself easily without needing to create a stored procedure. It’s easy enough to change it to a stored procedure and I left the code in there but commented out.

The worst thing about using this stuff is the number of variables you need to define. I put this down to the fact that the .NET interop does not support multi-dotting where I can write things like foo.bar.thing(). I’m hoping we’ll get multi-dotting in a future version.

So for this to work, you’ll need to create a whole bunch of variables. Rather than list them here, I’ve included a text export of my Codeunit as a download.

The example is really simple. I select the names of the companies in the current database and display the Row Count and then the first 5 company names.

Here’s my code:

//Use the session table to find the database and server name for the current connection
Session.SETRANGE("My Session",TRUE);
Session.FINDFIRST;

//Open a connection using windows authentication to the current session database and server
dnSQLConnection := dnSQLConnection.SqlConnection('server='+Session."Host Name"+';'+
'database='+Session."Database Name"+';'+
'Trusted_Connection=sspi');

//Create a DataAdapter
dnSQLDataAdapter := dnSQLDataAdapter.SqlDataAdapter();

{
// Example of using a stored procedure and not a select string

//Create a SQL Command on our connection
dnSQLCommand := dnSQLCommand.SqlCommand('myStoredProc',dnSQLConnection);
//Set the command type as StoredProcedure.
dnSQLCommand.CommandType := 1;
}

//Create a SQL Command on our connection
dnSQLCommand := dnSQLCommand.SqlCommand('select * from Company',dnSQLConnection);

//Assign the SQL command as the select command on my data adapter.
dnSQLDataAdapter.SelectCommand := dnSQLCommand;

//Create a dataset
dnDataSet := dnDataSet.DataSet;

//Fill the dataset by executing my command on the data adapter.
dnSQLDataAdapter.Fill(dnDataSet);

//Get the collection of tables
dnDataTableCollection := dnDataSet.Tables;

//Get the first table
dnDataTable := dnDataTableCollection.Item(0);

//Get the collection of rows
dnDataRowCollection := dnDataTable.Rows;

MESSAGE('There are %1 Rows', dnDataRowCollection.Count);

currentRow := 0;

REPEAT
//Get the currentRow row
dnDataRow := dnDataRowCollection.Item(currentRow);
MESSAGE('%1',dnDataRow.Item(1));
currentRow += 1;
UNTIL (currentRow = 5) OR (currentRow = dnDataRowCollection.Count);

//Tidy up
dnSQLDataAdapter.Dispose();
dnSQLConnection.Close();

In a New Zealand demo database this gives the following messages:

You can download the Codeunit here.

.NET Interop—I’m Lovin’ It!

David Roys — Wed, 16 Feb 2011 08:33:37 GMT

If this was a blog about grammar, I might be asking whether a verb like “to love” can be conjugated in a progressive tense to give us the phrase “I’m Lovin’ It”. But it’s not, it’s a blog about Dynamics NAV and I’m writing about .NET Interop in Dynamics NAV 2009 R2 which has got to be the coolest feature for NAV ever!

Let’s assume that you’re into Dynamics NAV and not dynamic verbs. The question I’m really asking here is how do you find the temporary path in Dynamics NAV? Easy, I hear your shout. You just use ENVIRON(‘TEMP’);

Aha, but what if you’re using the RoleTailored client and you want to find the temp directory on the client? If you try using the ENVIRON command you’ll get the following error message:

Previously I solved this problem in quite a convoluted way. I created a temporary file (albeit an empty one) in the temporary path on the NAV Server, then I transferred it to the client using the magic path, then I found the path of the location of the file where it got transferred to.

Does that sound crazy? Take a look at the ClientTempFileName() function in the 3-Tier Automation Mgt. codeunit if you don’t believe me. I used that function to get a client temporary file and then used the Path function to find the client temporary file path.

But now with the .NET Interop there is a better way.

Simply create a local variable of type DotNet and Subtype 'mscorlib'.System.Environment. Make sure you bring up the properties of the variable and set the RunOnClient property to Yes.

Now you can write code like this:

IF ISSERVICETIER THEN
MESSAGE('Temp is %1',dnEnvironment.GetEnvironmentVariable('temp'))
ELSE
MESSAGE('Temp is %1',ENVIRON('temp'));
EXIT;

Aaah. I’m Lovin’ It!

P.S. If you’re more interested in grammar than Dynamics NAV, check out http://grammar.quickanddirtytips.com/im-loving-it-grammar.aspx to find out why McDonald’s slogan is such bad grammar.

Regular Expressions in NAV

David Roys — Wed, 02 Feb 2011 07:32:55 GMT

A friend of mine recently asked if there was a standard NAV function to find the first alphabetical character in a string. STRPOS comes close but you can't say "tell me the first position for any one of these characters in this string" because it looks for an exact match of the substring and not any character from the substring.

The AL code to do this isn't hard but I figured this was a perfect excuse to play with the new .NET Interop feature in NAV 2009 R2.

With the release of NAV 2009 R2, we have the ability to use the . NET Framework directly from within AL code. Now we can use simple .NET classes to do things with strings that previously required several lines of code. I'm thinking of regular expressions of course.

The regular expression for any character between A and Z or a and z is [A-Z,a-z].

I created a function in a Codeunit called RegExFind that takes two parameters (a string to be search and a string containing the regular expression to match on). It returns an Integer which is the matching position of the first matching character (it returns 0 if it doesn't find a match).

To run the code, you need to add the Codeunit to a RoleTailored MenuSuite as .NET Interop only works on the service tier. You could put this code in a button on a page too, if you wish.

Here is the function:

RegExFind(SearchString : Text[250];RegEx : Text[250]) : Integer

dnMatch := dnRegEx.Match(SearchString,RegEx);

IF dnMatch.Success THEN
EXIT(dnMatch.Index+1)
ELSE
EXIT(0);

In my Function I have the following local variables:

Name	DataType	Subtype
dnRegEx	DotNet	'System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Text.RegularExpressions.Regex
dnMatch	DotNet	'System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Text.RegularExpressions.Match

Notice that we return the Index +1 (as in .NET all arrays start at 0 where in AL they start at 1).

The code to call the function is shown here:

SearchString := '1234 Lines of Code';

FoundPos := RegExFind(SearchString,'[a-z,A-Z]');

IF FoundPos > 0 THEN
MESSAGE ('Found %1 at position %2 in %3',COPYSTR(SearchString,FoundPos,1),FoundPos,SearchString);

SearchString := 'Now I look for 1 or 2 numbers.';

FoundPos := RegExFind(SearchString,'[0-9]');

IF FoundPos > 0 THEN
MESSAGE ('Found %1 at position %2 in %3',COPYSTR(SearchString,FoundPos,1),FoundPos,SearchString);

As you would expect, when I run this Codeunit from my RoleTailored client, I get the following output:

The only problem with the new .NET Interop is knowing what's out there in the framework. The chances are, there's something to handle your requirement and the good news is there'll be plenty of examples of how to use it - just as long as you can translate the C# code into an AL equivalent.

This simple example is just the tip of the iceberg for what you can do with regular expressions. Wouldn't it be nice to use regular expressions to validate user input?

Have you got any favourite .NET tricks that you’d like to do in AL code?

Book Review: Microsoft Dynamics NAV Administration

David Roys — Fri, 31 Dec 2010 03:18:03 GMT

Authors: Sharan Oberoi, Amit Sachdev

Publisher: PACKT

ISBN: 978-1-84719-876-1

Published: September 2010

Once upon a time you couldn’t get books on Dynamics NAV for love nor money, then in October 2007 PACKT published David Studebaker’s book Programming Microsoft Dynamics NAV. I read David’s book and wrote a review (http://gaspodethewonderdog.blogspot.com/2007/12/book-review-programming-microsoft.html) and became inspired to co-write my own book on Dynamics NAV.

Things are very different now; there are loads of books on Dynamics NAV for everyone covering Programming, Implementing, Designing, Cooking, and now Administrating. OK so maybe not cooking, but there is a programming cookbook which I reviewed on this blog (http://www.teachmenav.com/blogs/dave/archive/2010/11/28/book-review-microsoft-dynamics-nav-2009-programming-cookbook.aspx).

With so many books around, you may be wondering what this new book from Canadian-based authors Sharan and Amit offers that can’t be found elsewhere. This is the first book on Dynamics NAV that appears to be aimed squarely at beginners. I can see how this book may appeal to someone who has landed a job with a company that uses Dynamics NAV and wants a quick guide to some of the capabilities of the product and a high level view of how to perform some admin tasks. The book covers essential tasks like creating new users and assigning security permissions, taking backups and restoring backups, and importing programming modifications from a FOB file delivered by a partner. This is all useful stuff for anyone working as a system administrator for NAV. There are only 175 pages (not including index and preface) spread over nine chapters with lots of screen images so it’s not going to take you long to read this.

According to the blurb on the PACKT site, “this book is a tutorial guide that illustrates the steps needed to install, configure, deploy, and administer Dynamics NAV”. Dynamics NAV is a very complex system and this book does not even begin to cover all of the various tasks needed to install and configure the system, but if you need to know how to run the install wizard, this book will show you how to do it and give you a bit more of an idea of what is happening. There are some fundamental things missing, like how to configure the Service Principal Names (SPNs) needed to allow the system to run on three separate machines (SQL Server, NAV Server, and Client); however, this topic is well covered in the MSDN Library (http://msdn.microsoft.com/en-us/library/dd301254.aspx?ppud=4).

There was an interesting section on virtualisation, but like the rest of the book, it doesn’t go in to a great amount of detail. I guess for an administrator, this lack of detail is a good thing as you don’t want to get bogged down with pages and pages of technicalities, but you’re not going to be able to fly solo on most of these topics without getting help from a partner or reading some of the other material available through Customer Source, the online help, or the MSDN library.

Coincidentally, I used to work with one of the authors, Sharan Oberoi, when he worked for Ernst and Young in New Zealand, so I feel I need to focus on the positive aspects of this book and not dwell on the areas where, for me, it fell short. At times I felt as though I was reading a giant product brochure from Microsoft about Dynamics NAV, but on a more positive note, this book provides a good introduction to a wide variety of topics that may be of interest to a systems administrator.

Is there anybody out there?

David Roys — Sat, 27 Nov 2010 22:07:22 GMT

Way way back in July I ran a competition to give away 2 Microsoft Visual Studio 2010 Ultimate with MSDN subscription cards. I said I would announce the winners in September, but didn’t. You see the thing is, the response was pathetic. Only one entry who is of course now the proud owner of an MSDN subscription worth several thousand dollars.

I’ve got one more subscription card still to give away, so the first person to tell me which Pink Floyd song includes the lyrics “Is there anybody out there?” gets it.

The 12-month MSDN subscription has some restrictions with it being a NFR version (technical support benefits and MSDN Magazine are not included, and all software benefits, including Microsoft Office 2010 products, are for development and test purposes only).

The subscriptions need to be registered within 180 days of when I received them (that means you have until around the end of December 2010 to register, but I won’t be responsible for you neglecting to register—if you win a prize, register it straight away).

Book Review: Microsoft Dynamics NAV 2009 Programming Cookbook

David Roys — Sat, 27 Nov 2010 21:34:18 GMT

Author: Matt Traxinger

Publisher: PACKT

ISBN: 978-1-84968-094-3

Published: October 2010

Long ago when I was co-writing Implementing Microsoft Dynamics NAV 2009 one of my colleagues said to me, “You don’t want to write a book like that, you want to write a NAV cookbook.” I must admit I hadn’t read any programming cookbooks and didn’t know what he was talking about so I just smiled and nodded reassuringly before totally ignoring him.

Then, lo and behold, along comes a request from PACKT asking me to do the technical reviewing of a new book being written by Matt Traxinger called Microsoft Dynamics NAV 2009 Programming Cookbook. Now if there’s one thing I like, it’s getting free stuff, and I remember from my authoring of Implementing that the technical reviewers get a free book or two. I also remembered that the work of the technical reviewers on our book helped us (me and Vjeko) immensely and so I looked forward to helping Matt out if I could. Today I received my shiny new copy of Matt’s book (free stuff is so cool, heh, heh, heh) and I figured it was time I wrote something about it on my blog.

So who is Matt Traxinger? I thought I recognised the name, but had never dealt with him directly as far as I was aware. He’s on dynamicsuser.net under the user name MattTrax and he’s made more than 500 posts. Chances are he’s helped me out more than a couple of times. If you want the bio from the book, you can read all about Matt here. I can tell you that after working on reviewing this book he seems like a really great guy and when it comes to knowing about NAV well…

This book is packed full of useful stuff. I’ve been doing NAV programming for quite a while now and I know a thing or two, but I found plenty in the book that I could use straight away in my day to day work. The book is divided into 112 recipes that go from the noddy stuff (like basic building blocks of programming NAV) to pretty advanced topics that require creating .NET controls and automations using Visual Studio. It seems like there is no problem too big for Matt. If NAV can’t do it, he rolls up his sleeves, cranks up Visual Studio and finds a solution. That was the thing I loved about this book. Matt has a great attitude to working with NAV and shows what can be done if you’re prepared to think outside the box.

I loved this book. There’s something in it for everyone. The recipes are fun-sized morsels of knowledge and you can dib in and out when you need to solve a particular problem, or you can start from the beginning and work your way through if you just want to learn as much as you can.

There are plenty of sources of knowledge on NAV now including various books, blogs, and online articles; if you’re a completely stingy git, you may prefer to spend your time trawling through forums and trying lots of things rather than shelling out for Matt’s book. I know I’d rather save my time and go straight to one reference where I can get an easy step-by-step guide on how to get past my current problem and then move on.

If you program NAV, you need this book. Well done Matt!

Creating a view across all companies–part 3

David Roys — Sun, 29 Aug 2010 02:31:22 GMT

This is the final part in a three part series of blog posts. In part 1 I wrote about how you could use a couple of fields in the $ndo$dbproperty table to create a ConvertInvalidChars() function that would turn your NAV company name into the prefix for the table name as it is used in the SQL database. Then in part 2 I showed you how to create a SQL script that would dynamically create your view for all companies that exist in the database. In this final part, I’m going to show you how to link the view to a NAV table and talk about some of the things to watch out for.

If you’ve followed the previous parts, you should now have a SQL view called Company Vendor that contains every field from the Vendor table in NAV plus a new field called Company Name that contains the company name.

Linked Tables

NAV has a neat property on the table definition that allows you to say a table is linked to a view in the database that has the same name as the table name. You can see this property by bringing up the properties for the table and looking at Linked Object. In the next image, you can see the property for my table 50001 is set to Yes. There is another property called LinkedInTransaction and the use of this has often been mysterious, I think because the online help definition is so poor and also because it is very rare for me to try to update the data in the linked object. According to a dynamicsuser.net post from Dean McCrae, when you have LinkedInTransaction set to No, NAV uses a separate connection and separate transaction for accessing the linked tables. This means that if your main connection rollsback due to an error, it does not affect the transaction performed for the linked object. The purpose of LinkedInTransaction is to make the transactions performed on the linked objects isolated from the main transaction. So it only matters if you plan to allow transactions such as inserts, updates and deletes on this linked object. In this example we are not allowing that.

Creating the Linked Table

I have found the quickest way to create my linked table is to edit my original table (in this case the Vendor table) and then edit the properties to change the ID, Name and set the LinkedObject property to Yes and DataPerCompany to no. I can then save my table. Since my view already exists, it all gets plumbed up correctly. I can now run my table and I see data from both of my companies – but unfortunately I can’t see the company name, and that is because I haven’t added the Company Name field. When I edit my table and add the new Company Name field, I come across the first of the problems with this simple technique. I get a message telling me that “The Record variable must belong to 23 and not to 50001”. The reason for this error is that there is programming logic attached to various triggers on the table that is expecting the table to be the Vendor table (23) and not our new Company Vendor table (50001). In short, you are going to need to remove all programming triggers from your table definition. You can do this easily by pressing Ctrl+S to save the table, the error message will be displayed and when you click OK, you will be taken to the code editing window. Now simply press Ctrl+Home to get to the start of the code, hold the Shift key down and press Ctrl+End so that all text in all programming triggers is selected. Then press delete. Finally, call up the Global variables window, open the Functions tab and select all of the functions, then delete. This will remove those functions that get left behind as empty functions when we deleted the code.

Gotchas

Although it was really easy for me to create my linked table by saving the existing table, there are a couple of things wrong with this. First of all, I need to remember to remove the programming code from the table, otherwise bad things could happen unexpectedly (such as data being updated in related tables on validates). The other issue relates to FlowFields. The system does not complain about the flowfield definitions in the table but you should be careful of these. The flow fields (such as Balance) are used to sum up the remaining amount on the Vendor Ledger entries for this Vendor, but the system does not know that the Vendor actually exists in a different company. If you are lucky, the flow fields will simply display 0. If you are unlucky there will be a Vendor with the same code in different companies and as a result, you will see a value that is related to the wrong company. My advice to you is to remove the flowfields and flowfilters from your table definition. On the vendor table this is 56 different fields. It’s at this point that I’m starting to wonder whether I actually saved any time by copying the existing table. It’s not actually that hard to go down the list of fields and delete the ones you don’t think you’ll need. You may find that when you delete certain fields from the table, the system complains that the fields are used in an active key. This is a good reason to delete the keys from the table definition too and make the primary key “Company Name”, “No.”.

Making the Page

My final step is to make a page over my new table. Remember to make the page non-editable and to say Insert Allowed, Delete Allowed and Modify Allowed = No. We don’t want someone accidentally deleting records from the view, because this will delete the actual records from the linked tables (and I have found that it doesn’t care what the underlying table is, even tables like the General Ledger Entry table which you wouldn’t normally be allowed to delete from using a regular user’s license). Here’s a picture of my final page showing how I can search across multiple companies for a specific vendor name.

Have fun with your linked tables.

Creating a view across all companies—part 2

David Roys — Sun, 22 Aug 2010 04:12:14 GMT

In last week’s post I wrote about how you could use a couple of fields in the $ndo$dbproperty table to create a ConvertInvalidChars() function that would turn your NAV company name into the prefix for the table name as it is used in the SQL database. This week we’re going to write some SQL that will create a dynamic select statement that we can use to create our view.

What is Dynamic SQL?

Dynamic SQL is when we use programming code to build up our SQL Select statement in a text variable and then execute that statement. In the case of our all company view, we don’t know how many companies we have or what they’re called, so we’re going to need to write something that will read the companies in the database and generate the correct SQL statement for us.

Here a simple example that shows that you can build up your SQL statement as a string and then use the EXEC() command to execute it.

DECLARE @SQLCommand AS VARCHAR(4000)     
    
SET @SQLCommand = 'SELECT Name FROM Company'     
    
EXEC( @SQLCommand)

The results of running this SQL is exactly the same as typing SELECT Name FROM Company into a query window and pressing F5.

Going Loopy with Cursors

Now we need to build up our SQL statement by looping through each of the companies in the database. To do this we’re going to use a cursor. A cursor is good because it allows you to fetch data back from the database one row at a time and then do something with the results. The downside to cursors is that they are slow (and this is one of the reasons that the performance of NAV is not as good as it could be as it makes extensive use of cursors when fetching data from the server).

In addition to using a cursor, we’re going to use the UNION ALL statement that will join the results from our select statements together into a single dataset – this is useful since we are trying to create a combined view, which means we need a single result set. Here’s the code:

DECLARE @CompanyName AS VARCHAR(30)     
DECLARE @SQLCommand AS VARCHAR(4000) = ''     
DECLARE mycur CURSOR FOR SELECT      
                                    Name     
                         FROM        
                                    Company;     
    
OPEN mycur     
    
FETCH mycur INTO @CompanyName     
    
WHILE @@FETCH_STATUS = 0     
BEGIN     
    IF @SQLCommand <> '' SET @SQLCommand += 'UNION ALL '     
     
    SET @SQLCommand += 'SELECT [Company Name] = ''' + @CompanyName + ''', * FROM [' + dbo.ConvertInvalidChars( @CompanyName) + '$Vendor] '     
     
    FETCH mycur INTO @CompanyName     
END;     
    
--EXEC(@SQLCommand)     
    
PRINT @SQLCommand     
    
CLOSE mycur     
    
DEALLOCATE mycur

As you can see I’ve commented out my EXEC command and replaced it with a PRINT command so we can see the SQL that gets generated in the message window. Here’s the SQL code it generates (obviously it doesn’t generate all of the formatting, I just inserted that to make it easier to follow the code):

SELECT      
           [Company Name] = 'CRONUS Australia Pty. Ltd.',     
           *     
FROM        
           [CRONUS Australia Pty_ Ltd_$Vendor]     
UNION ALL     
SELECT      
           [Company Name] = 'CRONUS New Zealand Ltd.',     
           *     
FROM        
           [CRONUS New Zealand Ltd_$Vendor]

If you copied that SQL statement into a query window, you’d see a dataset that contains all of the fields from our Vendor table for each of the companies with a new first column called “Company Name”. Obviously you’d need to change this if you had an actual field called “Company Name” in your table.

Making the View

The only thing left to do now is put in the CREATE VIEW AS code to create our view. I’m going to call my view Company Vendor. Here’s the final SQL that will create my view.

DECLARE @CompanyName AS VARCHAR(30)    
DECLARE @SQLCommand AS VARCHAR(4000) = ''    
DECLARE mycur CURSOR FOR SELECT      
                                    Name    
                         FROM        
                                    Company;    
    
OPEN mycur    
    
FETCH mycur INTO @CompanyName    
    
WHILE @@FETCH_STATUS = 0    
BEGIN    
    IF @SQLCommand <> '' SET @SQLCommand += 'UNION ALL '    
    ELSE SET @SQLCommand += 'CREATE VIEW [Company Vendor] AS '    
     
    SET @SQLCommand += 'SELECT [Company Name] = ''' + @CompanyName + ''', * FROM [' + dbo.ConvertInvalidChars( @CompanyName) + '$Vendor] '    
     
    FETCH mycur INTO @CompanyName    
END;    
    
EXEC( @SQLCommand)    
    
PRINT @SQLCommand    
    
CLOSE mycur    
    
DEALLOCATE mycur

Next week we'll look at the final steps of making this view available as a linked table in NAV and talk about some of the potential gotcha’s.

Creating a view across all companies–part 1

David Roys — Sun, 15 Aug 2010 00:31:11 GMT

The Linked Table

Have you ever wanted to search across multiple companies in a NAV database simultaneously? It's really not that difficult as long as you're using a SQL database. In this series of blog posts, I'll give you a step by step guide on how to create a combined vendor list. This principal can be applied to any of the tables in NAV. First of all, we need to cover some basics.

NAV has the facility to create a table definition that is linked to a view in the database that has the same name as the table. This can be a useful way to directly access data from other systems from within NAV, but it can also be useful for aggregating data that already exists in the NAV database. This could be combining data from multiple companies into a single view, as in this example, or could be combining G/L Entries and Dimensions into a single screen with the ability to search and filter on the dimensions. You could even use this technique to speed up slow processes by using SQL's powerful set-based queries to replace a lot of effort needed by NAV's much slower cursor-based operations.

To follow this example you'll need a sample NAV database that you can play in without breaking anything and access to SQL Management Studio.

Getting Rid of Invalid Characters

If you’ve ever looked at the table names in a SQL database for Dynamics NAV, you’ll have noticed that the table names have the company name at the start and that certain characters have been replaced. In the demo database for New Zealand, we have two companies: CRONUS Australia Pty. Ltd. and CRONUS New Zealand Ltd. If I look for the Vendor table in SQL I’ll see the following two table names:

CRONUS Australia Pty_ Ltd_$Vendor
CRONUS New Zealand Ltd_$Vendor

As you can see it’s basically the company name with a dollar sign followed by the table name, but some characters have been replaced. This is because when NAV creates the underlying SQL tables, it replaces some “invalid characters”. It does the same for the field names too.

In this first part of our series on creating a view across all companies, we’ll create a function that will help us find the correct company name as used as the prefix for NAV tables.

The characters that get replaced are stored in a field called invalididentifierchars in a system table [$ndo$dbproperty] (the square brackets aren’t actually part of the table name, they just make tell SQL to ignore funny characters like the dollar sign and any spaces that may be part of the table name). There’s another useful field in the [$ndo$dbproperty] table that we’ll need for this exercise called convertidentifiers. This tells the system whether we want to bother converting identifiers or not. You can change both of these fields by going to the Classic Client for SQL and selecting Database > Alter from the File menu. Click on the Integration tab and you’ll see the options.

We need to check to see if the Convert identifiers flag is set and, if it is, remove each of the characters from our company name and replace with an underscore.

I’m going to create a SQL Function called ConvertInvalidChars that will do this for me. Here’s the SQL code. You should execute this in the NAV database.

IF OBJECT_ID( N'dbo.ConvertInvalidChars',N'FN') IS NOT NULL DROP FUNCTION dbo.ConvertInvalidChars;     
GO     
CREATE FUNCTION dbo.ConvertInvalidChars     
(      
       @p_StringToConvert AS VARCHAR(250)     
)     
RETURNS     
VARCHAR(250)     
AS  
 BEGIN     
    DECLARE @InvalidIdentifierChars AS VARCHAR(128)     
    DECLARE @X AS INTEGER     
    DECLARE @ConvertIdentifiers AS INTEGER     
     
    SELECT      
               @ConvertIdentifiers = convertidentifiers,     
               @InvalidIdentifierChars = invalididentifierchars     
    FROM        
               [$ndo$dbproperty]     
     
    IF @ConvertIdentifiers <> 1 RETURN(@p_StringToConvert)     
     
    SET @X = 1     
     
    WHILE @X <= LEN( @InvalidIdentifierChars)     
    BEGIN     
        SET @p_StringToConvert = REPLACE( @p_StringToConvert,SUBSTRING( @InvalidIdentifierChars,@X,1),'_')     
         
        SET @X = @X + 1     
    END     
     
    RETURN(@p_StringToConvert)     
END     
GO

Testing our Function

That previous bit of SQL will give us a SQL function that we can use in our code later on. Here’s an example of a select statement that uses the function.

SELECT      
           Name,    
           [No Invalid Chars Name] = dbo.ConvertInvalidChars( Name),    
           [NAV Vendor Table Name] = '[' + dbo.ConvertInvalidChars( Name) + '$' + dbo.ConvertInvalidChars( 'Vendor') + ']'    
FROM        
           Company

and here’s the output from that select statement.

Name	No Invalid Chars Name	NAV Vendor Table Name
CRONUS Australia Pty. Ltd.	CRONUS Australia Pty_ Ltd_	[CRONUS Australia Pty_ Ltd_$Vendor]
CRONUS New Zealand Ltd.	CRONUS New Zealand Ltd_	[CRONUS New Zealand Ltd_$Vendor]

Hopefully you can see where I’m going with this. Next week, I’ll show you how we are going to use this function to build a SQL View for combining multiple company tables together.

You Could Win Visual Studio 2010 Ultimate + MSDN

David Roys — Sun, 25 Jul 2010 00:02:17 GMT

In order to drive awareness of the recent launch of Visual Studio 2010, Microsoft has given me 2 x Not for Resale (NFR) Visual Studio 2010 Ultimate with MSDN subscription cards to give away.

Take a look on the Visual Studio Product Site and you’ll see that to buy one of these subscriptions will set you back a lot of money—but I’m giving them away for free!

What do you need to do?

As you know, Microsoft Dynamics NAV 2009 SP1 introduced a new feature called Control Add-ins. These are .NET controls that let you do all kinds of stuff like display customer locations using Bing Maps, display Visio diagrams, capture signatures, display charts, have spell checking, edit HTML…all within the RoleTailored client. What a lot of stuff! The thing is, you’re only really limited by your imagination – these are just some examples of controls that have already been created. What control would you create?

I’m not asking you to build one of these controls to win the prize, I just want you to think of an idea for a control add-in and write about it. Write a Word document describing your concept and include pictures (mock-ups or sketches) of how you see it working. You can use any version of Word for your submission, but don’t use other formats because if I can’t read it, you can’t win. Also all submissions must be in English and if you write in Text Speak or some strange form of English that I can’t understand, you’re chances of winning are slim. You’re submission should be around two pages in Word with a picture or two.

How do you submit your entry?

The purpose of this competition is to get people thinking about custom controls for NAV 2009 SP1 and sharing their ideas. I have created a Forum for you to upload your ideas so they are then shared with everyone. Write a forum post giving a brief outline of your idea and you should be able to attach a Word document to the forum post. The Control Add-ins Forum is here. You need to be registered on TeachMeNAV to be able to make posts. I will need to send an e-mail to you about the prize if you win, so make sure you use a real e-mail address when you register. I’ll put in a sample posting so you know what I’m looking for. I’ll be searching for your idea online so don’t bother just posting someone else’s idea or blog post.

Closing Date and Terms and Conditions

I’ll leave the forum open until the end of August 2010. You must make your submission before then to be in to win.
Only submissions made through the Control Add-ins Forum will be considered.
I am going to pick the winners and my decision is final.
The Visual Studio 2010 MSDN subscription cards are the only prizes. There is no cash alternative and if for any reason your subscription card does not work, you’ll need to sort this out with Microsoft and not with me.
I’ll announce the winning entries before the end of September 2010.
The competition is not open to Dynamics NAV MVPs (sorry guys but you already have an MSDN subscription, so let’s give someone else a chance).
By submitting your ideas, they are shared with the world. If someone decides to build your idea, good luck to them.

What’s the catch?

There’s no catch, but there are some conditions and limitations surrounding the prize.

Resources

The official Microsoft documentation about add-ins online at http://msdn.microsoft.com/en-us/library/dd983700.aspx

Freddy Kristiansen’s Blog http://blogs.msdn.com/b/freddyk/archive/2009/06/07/integration-to-virtual-earth-part-4-of-4.aspx

Christian Abeln’s blog on Add-ins http://blogs.msdn.com/b/cabeln/archive/2009/05/06/add-ins-for-the-roletailored-client-of-microsoft-dynamicsnav-2009-sp1-part1.aspx