Microsoft Dynamics NAV: What’s in the Option’s OptionString?

If you have ever tried to read data from Microsoft Dynamics NAV SQL tables then you quickly found out that Option type fields are stored in the table as integer values. Unfortunately, it is difficult for users to relate the option representation with the integer value. NAV performs the translation of the integer to the appropriate option value for display within the application and doesn’t offer much in the way of translation outside of the application.

Generally I have come across cases where people have built the translation directly into their source, as this SQL example:

 

Document_Type = CASE [Document Type]
		WHEN 0 THEN 'Quote'
		WHEN 1 THEN 'Order'
		WHEN 2 THEN 'Invoice'
		WHEN 3 THEN 'Credit Memo'
		WHEN 4 THEN 'Forecast Order'
		WHEN 5 THEN 'Return Order'
	ELSE ''
	END

 

This may solve the issue of frustration however, what happens if options v alues are changed or added? Under this scenario the query would need to be recoded. Depending on the number of queries and their integration this can become tedious and time ($) consuming task.

So, what’s the alternative? Wouldn’t it be nice if you could dynamically read the options value? There really isn’t a dynamic way to externally translate the values outside of NAV however; you can read the values from a table. Before thinking that this requires a lot of data entry; NAV does allow for you to read these values from within the application.

Option OPtionString

The solution that I use is to dynamically populate (and update regularly) a table that contains the Table, Field and Option value information. This option allows for the selection of the option value based on table and field number. If option values are added, removed or changed they are reflected in the table and there isn’t a need to make any change to any queries.

I have attached to this post the text file for the objects used >>HERE<<.

SQL: How to test a SQL Backup

When reviewing disaster recovery processes I often get asked how a verify / test that a SQL Backup is “good”. Restoring the backup file is a solid and reliable way to verify the backup file, however it may not always be practical due to space and/or time restrictions.

During the backup task, you can check the “Verify backup when finished” reliability option to verify the backup set is complete and readable.

After the backup file has been create you can execute the following commands:

RESTORE VERIFYONLY FROM DISK=‘<your backup file name.bak>’
http://msdn.microsoft.com/en-us/library/ms188902.aspx
This option verifies the backup set is complete and readable, however it does not validate the data structure.

RESTORE HEADERONLY FROM DISK=<your backup file name.bak>’
http://msdn.microsoft.com/en-us/library/ms178536.aspx
Retrieve the header information of the backup set.

RESTORE FILELISTONLY FROM DISK=‘<your backup file name.bak>’
http://msdn.microsoft.com/en-us/library/ms173778.aspx
Returns a list of files in the backup set.

There are options available to test the integrity of the backup file, however you should periodically test your disaster recovery / business continuity plan. It is important to verify that the contents of your “backup” contains the pertinent data for a proper recovery.

SQL: Microsoft Dynamics NAV “kill” an idle session

One issue with applications that use a concurrent license model is the dreaded “idle user”. This is the user that had gone to lunch, to a meeting or even home for the and didn’t “logout” of the application. Under the concurrent license model this user does take up a user session, which prevents other users from accessing the application if the maximum number of seats has been reached or lead to the purchase of additional and unnecessary user licenses due to the perception of not enough seats.

Microsoft Dynamics NAV is an example of application that uses a concurrent license model and is also one application that people often ask “How can I disconnect an idle user?” While there isn’t a magic “kill idle” user setting there are a several options available. A few of these options are:

  • If the users are using Remote Desktop you can set an idle timeout to force a disconnect or manually force a disconnect of idle remote users
  • Manually remove idle users from the Microsoft Dynamics NAV session list
  • Kill a user’s SQL Server connection

Let’s jump into the last option that is listed above. In Microsoft SQL server there isn’t an “idle time” value defined for a user, however there is a last_batch value that indicates the last time the users session sent an execution to the SQL server. With that information we can easily create (and schedule) a script that can compare the current date and time to the last_batch value to see how long a user has been “idle” and kill their session if it is over a certain amount of time (script should be adjusted for your implementation):

set nocount on;
set ansi_padding on;
set ansi_nulls on;
set quoted_identifier on;

-- declare variables.
declare @spid as varchar(20)
declare @loginname as varchar(255)
declare @kill as int
declare @cmd as varchar(max)

-- set the variable @kill to 1 to actually perform the “kill or set it to 0 to see a list of users that
-- would be killed
set @kill = 0

declare users insensitive scroll cursor
for    
      select
            spid
            ,loginame
      from
            sys.sysprocesses
      where
            -- NAV Db Name    
            (db_name(dbid) = N'CRONUS')
            and (program_name = N'Microsoft Dynamics NAV Classic client')
            -- number of seconds idle -- remember this does not mean there isn't a long job running
            -- 3600 is 1 hour
            and (datediff(ss, last_batch, getdate()) &gt; 3600)


open users
while (1=1)
      begin
            fetch next from users into @spid, @loginname;
            if @@fetch_status <> 0 break;
            
            set @cmd = 'kill ' + @spid
            if @kill = 1 
              exec (@cmd)
            else
              print @cmd + N' ' + @loginname;
      end

close users;
deallocate users;

go

 

One thing to point out is that a user may be idle because their session is performing a lengthy operation and they have not had any session activity while they are awaiting results for their command. There tends to be some disconnect and excitement when someone is in the middle of some operation and their session is killed.

It should be evident that there is a risk when abnormally terminating a user’s session. There is the risk of data loss or corruption so be certain to understand the environment that you’re in and the implications of terminating a user’s session in a not so elegant way (i.e. properly logging off).

SQL: Check if a File Exists

There may be a case where a SQL table contains a field that references a filename that is stored on the file system. The file could be an image (for example in the case of a web application that displays images) or another document that is linked from an application. To avoid errors it may be necessary to verify the existence of the file (i.e. the file exists). Fortunately, this is easily done with the xp_fileexist command. The xp_fileexist command returns an integer result that indicates if the file exists. The usage of the command is:
EXECUTE xp_fileexist [, OUTPUT]
The following script shows and example of looping through a set of records and validating, with xp_fileexist, that a file exists. If it doesn’t exist it updates the field with another value (if you are referencing images it could be a default image filename).
set nocount on;
set ansi_padding on;
set ansi_nulls on;
set quoted_identifier on;

-- declare variables.
declare @item as varchar(255)
declare @filename as varchar(255)
declare @filepath as varchar(255)
declare @cmd as varchar(1024)
declare @result as int

-- declare a cursor for storing the records in the table.
declare items insensitive scroll cursor
for
	select
		[Item]
		,[File Name]
		,[File Path]
	from  [Table1]
	order by 
		[File Name]

-- open the cursor.
open items

-- loop through all the indexes.
while (1=1)
	begin
		fetch next from items into @item, @filename, @filepath;
		if @@fetch_status <> 0 break;
		
		set @filepath = @filepath + @filename
		exec master.dbo.xp_fileexist @filepath, @result OUTPUT
		if (@result = 0) -- image does not exist
			begin
				--print (@result)			
				-- Update the record to point to a generic image
				set @cmd = 'UPDATE [Table1] SET [File Name] = ''NA.jpg'' WHERE [Item] = ''' + @item + ''''
				-- print (@cmd)
				exec (@cmd)
			end
	end

-- close cursor
close items;
deallocate items;

go

SQL: SSIS, a Flat File and the _x003C_none_x003E__x003C_none_x003E_ Text Qualifier

I had recently experienced a strange issue with a SSIS Package.  This was basic package that copied data from a SQL Server table to a text file. This SSIS package was deployed in production for several months without issue, until – a minor change needed to be made.  The change was insignificant; the location (path) of the output file (a config file is usually used but in this case I wanted to edit the package) needed to be changed.   I had changed the path, built and deployed the package. Soon after deploying the package I received word that the file created was not correct.

I reviewed the output file and noticed that each record (line) ended with strange hex looking data: _x003C_none_x003E__x003C_none_x003E_

_x003C_none_x003E__x003C_none_x003E_ text qualifier

I scratched my head for a bit, reviewed the package’s source and destination connections and could not find where this odd _x003C_none_x003E_ data was coming from. I decided to view the package with a different editor and realized that somehow the Text Qualifier property for the Flat File Connection Manager was set to this “strange” sequence.

TextQualifier Property _x003C_none_x003E__x003C_none_x003E_

I removed the value, built and deployed the package and everything was back to normal – well as normal as it could be…