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
		,[File Name]
		,[File Path]
	from  [Table1]
	order by 
		[File Name]

-- open the cursor.
open items

-- loop through all the indexes.
while (1=1)
		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
				--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)

-- close cursor
close items;
deallocate items;


Microsoft Dynamics NAV: Find that object on the Menu

Have you ever wondered where on a Microsoft Dynamics NAV menu an object existed? Did you ever wish that there was an easier way to find or search for objects on a Microsoft Dynamics NAV menu?

MenuRead is a utility that was designed to make an easier task out of finding where items are on a Microsoft Dynamics NAV (classic) menu. Using MenuRead you can easily find where objects are located within the Microsoft Dynamics NAV (classic) menu structure. MenuRead's user interface simplifies the task of browsing through the menu structure. MenuRead has a friendly user interface that allows for the searching by object name or number. MenuRead even searches by partial match. Once a menu item is found its relative path can be easily copied to the clipboard for further reference.

Using MenuRead is as simple as exporting the Menu objects from Microsoft Dynamics NAV as text and loading the exported text file within the application.

MenuRead can be downloaded >>here<< and require the Microsoft .NET Framework 4.

If you have any suggestions please let me know.  I will release updates as they are available.

Download MenuRead


Microsoft Dynamics NAV: Action Image Library (RTC)

When adding a new Action to a page’s Action Pane in the Role Tailored Client (RTC) the default image is a boring “dot”. The image property of the action is used to specify the image that is displayed for the action. Question: Where is the list of images that can be used in this property? Answer: MSDN (http://msdn.microsoft.com/en-us/library/dd568728.aspx).

Action Image Library

Microsoft Dynamics NAV: Activity Button Images (RTC)

When adding a new Activity Button to a page in the Role Tailored client (RTC) the default image is basic and a bit boring. The image property of the action is used to specify the image that is displayed for the action. Where is the list of images? Microsoft was kind enough to place a list of the images on MSDN (http://msdn.microsoft.com/en-us/library/dd568709.aspx).

Activity Button Image Library

Installing Windows 2012 Server in Hyper V

In order to become familiar with Windows Server 2012 and ensure application compatibility I decided to install Windows Server 2012 within a Hyper V machine. Virtual Machines are wonderful. The ability to create, snapshot, and restore “machines” without having to having all this hardware is wonderful.

I downloaded the Windows Server 2012 ISO from MSDN and attached it to DVD Drive media of a newly created virtual machine. I booted the machine and started down the road of a Windows Server installation.

Windows Server 2012 Installation Screen

The process started ok, however once I started the installation I received a driver error:

"A media driver your computer needs is missing. This could be a DVD, USB, or Hard disk driver. If you have a CD, DVD, or USB flash drive with the driver on it, please insert it now."

Windows Server 2012 Installation Error

I reviewed all of the settings, hardware configurations and even verified that Windows Server 2012 runs within Hyper V. Everything checked out and I started the install again and received the same error. Needless to say it was a bit frustrating. The physical server is only 5 months old and I have installed several other test servers, from ISO files, running in Hyper V without an issue. I was pretty comfortable with the hardware configurations and even verified the ISO file was valid.

After several failed attempts to load the server from the ISO file I decided to burn the ISO to a DVD. I then loaded the DVD into the server and attached the physical DVD drive media to the virtual machine. This time the installation process started and finished without an issue.

It seems that the installation was not able to process/cache the full tree of files and using a physical disk did the trick.

Reading Stuff

Information in this document subject to change without notice.
All Software source code published is for demonstration and knowledge sharing purposes only. The Code is supplied "as is" without warranty as to result, performance or merchantability. Use at your own risk.
The opinions expressed herein are the opinions of the author and do not reflect those of any other entity.