Nav 2009 SP1 RTC Images

A handy link to the available RTC Images.

NAV 2009 Role Tailored Client Development Quick Start

I am muddling my way through adding some classic client items to role tailored client in NAV 2009 and thought I would keep track of things that I found to be difficult. The most helpful resource online I have found so far is on MSDN:

Pages - These are simple to create and add fields to, but you can't format them beyond changing captions.

Menus - These are very confusing, there are 2-3 types that all have to be updated if you create new pages and want your users to have access to them:

--Menu Suite - You have to create NEW menu suites, separate from the Classic suites - go to New and select "Design For.. Role Tailored Client"

--Roles - Users are assigned to different roles (See Departments -> Administration -> Application Setup -> Role Tailored Client), so you have to update all the pages tied to the roles that are related to the page you have created. The pages are found in the 9000-9999 range.

Page Links/Actions - To create links between pages you have to add actions. Open the Page, select a blank line at the bottom of the list of elements, select View Actions.

You can create several types. Here is some more information on Actions.

Some key info: For basic stuff, the actions go into an Action Container, either RelatedInformation or ActionItems. You can have then show with their own icon if you indicate they are Promoted in their properties. You have to indicate which group they show up in. There are 3 default named groups, New, Process, and Report. You can assign names to several additional groups in the Page Properties.

Javascript Command Line

I never really mastered shell programming, but I do have JS skills. Maybe this article will come in handy.

Granting Execute Permissions to SQL Users

Granting exectution permission to users in SQL used to mean giving them DBO or granting permission to each stored proc. individually.

Last year I found a stored proc that would grant permissions when called but when new stored procs are created, you have to re-run it.

Today I found out that there is a way in SQL 2005 and later to create a new role that has execute permissions to all with the following Code:

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

After you have executed that script you can then see the role in the permissions list and assign it there. The user will then have permission to stored procedures added later.

VMWare Tips

VMWare Player is free, and it works ok, the UI is a little slow, but it works well otherwise.

VMWare Server is also free, and it works well but does not support audio.

VMWare Converter is free, it can make a VM from a physical computer (as well as do some other things I haven't looked into yet)

I am very much a rookie at VMWare, but I have found out a couple things about VMWare workstation Settings:

• It is a hassle to increase the size of the C:\ drive so make it big enough to start.
• It is easy to add drives, but you have to format them in Windows before you can use them.
• Use Bridged networking if you need to browse network shares.
• Make sure the sum of RAM available to your VMs does not exceed the amount of RAM on your machine, leaving around 512mb for the root OS (for win XP pro).
• Install VMWare tools or the UI is very clunky
• Use the shared folders feature on the Options tab of the Settings dialog to share drives on the root OS with drives on the VM.
• Don't take a snapshot of a paused machine, or a running machine if you ever want to clone it. You can't clone a running or paused snapshot.

Doing a re-install on a Lenovo T60

After exactly 2 years of use, my Lenovo T60 was all messed up. Here are some of the problems I came to experience:

• Windows required 12-15 minutes to start up.
• It required 5-7 minutes to shut down.
• When "waking" from sleep status onto a new network, the OS could not re-configure itself requiring a re-boot.
• Trying to italicize something in Excel required 2 minutes the first time.
• The 100 gb HD was full and required constant effort to empty so I could work.
• All my music had to be stored on thumb drives
• There were at least a dozen extra services starting up and I had no idea what they did.

So I decided to get a new HD and re-install Windows XP from scratch, and decided not to use the Lenovo system disk since a lot of what was installed was not required.

Further, I decided to ONLY install windows, VMWare workstation, and any VPN software I might need to connnect to my client's networks so that re-boot times and network problems would not recur and to do all future work on VMs. We subscribe to Microsoft's ActionPak program so we get 5 copies of every OS they offer for development purposes.

2 weeks later, I am nearly done. It has taken me at least 24 hours of effort, and almost every day until today I have had to switch back to the original HD to get most of my work done.

I decided to work on 3 VMs:
A pure development VM with

• SQL 2008 Developer,
• VS 2008 and various toolsets I use like telerik and subsonic
• VSS,
• SQL Compare,
• Dynamics NAV
• A good editor ( I used to use JEdit, but it craps out after being left open too long and I don't like to have Java running just for one editor, so I need to find a new one that has a good XML indent feature, side-by-side comparisons, text highlighting, and can deal with VERY large files. If you know of one let me know).

An application VM with

• MS Office (including visio and project)
• Paint.net
• Inkscape

A throw-away VM with

• I-tunes
• Firefox
• Java
• uTorrent
• VLC Media Player
• SQL 2005 for legacy SSIS package work I may need to do.
• All the other crap I have to load to get things done or because I am curious to see how it works

The first problem on the T60 is you have to change your system bios storage settings to be "compatible" to get it to install.

Once that is done, you can run windows update to get some drivers and download the rest of the drivers here for the HD, Intel Chipset, and Wireless, then change the storage settings back. That took me about 4 days to figure out and complete, I did not install any other drivers to ensure I didn't have things installed that I don't need.

Next, validate your XP Install, patch it, and purchase (or demo) VMWare workstation, and use VMWare converter to make an image of your machine and run the VMs, install the appropriate software, and start moving all the data (I bought a USB enclosure for the SATA drive to make that easy).

Install the VMWare tools to increase performance significantly, and if you partition the HD on your root OS, you can share one of the partitions between all the machines as a network share so they all have access to the same data.

Unfortunately that share can not be used for SQL databases, since it is not seen as a local volume, so make the development VM large enought to hold all the databases, and all the backups. I needed to add a 20gb extra drive for a total of 50gb, and I only have about 10gb free on that VM.

I will need some more RAM to run all 3 VMs at once, but I was going to up my RAM from 2gb to 4 gb anyway.

After all that work, it is a bit cumbersome to move things between VMs, but all the issues that led me to this configuration have been resolved and it takes 1-2 minutes to boot up, less than 1 minute to start up a VM that was paused, and 1-3 minutes to shut down. Plus I don't have to shut down to get networking to function when changing networks. I think that this change will be saving me 20-30 minutes a day. So my ROI is about 4 months. If I had to do it over again, I think it would take about half as long, so if you stumbled upon this, hopefully it will take you less time.

And when I get a new machine in 1-2 years, the migration should take less than an hour.

Determining an object size in C#

I am implementing business logic for a web application that will result in a sizeable data structure being housed in a class. I may decide to cache the class in the session at some point and I wanted to know how much RAM the class takes up. To figure that out, the best way I could find (here) was to serialize it and determine the size of the resulting binary. Of course the entire class (and any classes it holds as public properties) needs to be serializable.

            long lSize = 0;
            System.IO.MemoryStream stream = new System.IO.MemoryStream();
            System.Runtime.Serialization.Formatters.Binary.BinaryFormatter objFormatter =
                new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
            objFormatter.Serialize(stream, this.SalesOrder);
            lSize = stream.Length;
            output.Write("Size of the Object: " + lSize.ToString());

Creating a Matrix form in Dynamics NAV

I have always just copied matrix forms and hacked them up to create a new one.  Today, after developing in NAV since it was called Avista in 1996, I created one from scratch.  Here are the steps (sorry there are no images, maybe later):

Create a New, blank form, set the SourceTable of the form to the record to be used as the rows in the matrix.  Add any columns you want on each row to the left of the matrix using the Field Menu.

Create 2 text values: MatrixValue, and MatrixHeader.

Put a Matrix control on the form, assign the MatrixSourceTable to be the record defining the columns of the matrix.

If the form is to be focused on another record (like a matrix of sizes and colors for a single item), you can create a function used to open the form, create the record as a global variable and get the source record in that function.

Add a text box to the right section of the matrix, set the sourceexpression to MatrixValue.

Add a text box to the header portion of the new column, set the sourceexpression to MatrixHeader

If the matrix rec (the record defining the number of columns in the matrix) is bound to the source rec, call 

On form open, set filter on the SourceTable variable, using the source record, changing to filter group 2 before, and then 0 afterward, then filter the MatrixSourceTable if needed as follows:

CurrForm.Matrix.MatrixRec.SETRANGE("Field",SourceTable."Field");

Select the entire Matrix control, and in the Matrix-OnAfterGetRecord() trigger:

MatrixHeader := CurrForm.Matrix.MatrixRec."Field";

Or any other value.  In my case, I appended the code with the description since the users needed both.

Select the right portion of the Matrix control and go to the C/AL code.

In the OnFormat section assign the text to whatever you want displayed in each grid box, using the Currform.Matrix.MatrixRec to determine the Column, and the SourceTable for the row, and the SourceRecord if needed.

That's it!

Resetting password with asp.net security

When using hashed passwords, if a user forgets their password, there is no obvious way to reset it, since the old password is needed to assign a new password, and hashed passwords can not be retrieved from the database, the but it can be done.

The key is to reset the password first, then use the reset password to create a new password entered by the administrator.

I put a couple textboxes up on a page (tbPassword and tbConfirmPassword) and in the Web.Config set the requiresQuestionAndAnswer property of the security provider to false.

With that in place, if the administrator puts a value in the "New Password" text box, I know they intend to change the password and call the following code:

if (tbPassword.Text != "")
{
string tempPassword = Membership.Provider.ResetPassword(membershipUser.UserName, "");
Membership.Provider.ChangePassword(membershipUser.UserName, tempPassword, tbPassword.Text);
}

AJAX and browser cache

When using AJAX to update a page, if the user leaves the page and comes back via the back button, the content will revert to the values the page had when it initally loaded.  To remove that behavior the following c# code will force a refresh from the server and display the page based on the latest information on the server.

Response.Buffer = true;
Response.ExpiresAbsolute = DateTime.Now;
Response.Expires = 0;
Response.CacheControl = "no-cache";
Response.Cache.SetNoStore();

Modifying a table in SQL 2008 Mgt Studio

When adding a field or making other changes that requires a table be dropped and re-added, you may receive an error like 

"Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created."

If that happens, you need to update the management studio options as described here.

ToString()

I am always forgetting how to use ToString() to get things formatted the way I want them.

To format a datetime as a date:

 RequestedShipDate.ToString("d");

 RequestedShipDate.ToString("MM/dd/yyyy");

Log in to web service programatically

Web service calls that are exposed to the public but which you don't want open to public calls can be secured using directory security on the directory IIS uses. This is only a valid method if the call is made over a controlled network since basic authentication password can be snooped.

Microsoft has a great page on how to use it, so I will only summarize

               NavCustomer.CustomerService navCustService = new NavCustomer.CustomerService();
               CredentialCache credentialCache = new CredentialCache();
               NetworkCredential credentials = new NetworkCredential(
                   ConfigurationSettings.AppSettings["WebServiceUserName"],
                   ConfigurationSettings.AppSettings["WebServicePassword"],
                   ConfigurationSettings.AppSettings["WebServiceDomain"]);
               credentialCache.Add(new Uri(navCustService.Url), "Basic", credentials);
               navCustService.Credentials = credentialCache;

IIS 7.0 Application Pools

IIS 7.0 has a new model for application pools.  If you are migrating from IIS 6.0, you need to read this and do what it says to get your application to work in Integrated mode.  There is a classic mode that is supported but not recommended.

Another good article on the topic is here.

Once I understand what is going on better, I will update this post.

Setting up a Windows 2008 server for FTP

Windows 2008 Server Web Edition has some extremely restrictive security settings, and some IIS setup problems at present.

If you install IIS, and enable FTP in the IIS manager, you will see the following in the Windows Firewall inbound rules:  

You may think that you are all set.  You will find though that you can connect to the server, but not browse the directory or modify files (although you can create files for some reason, but they end up as zero bytes).  This is because the TCP port is changed after connection is made and the port it is changed to is blocked by the firewall.

I found this post which instructs you to enter the following commands via and command prompt.  Note that you must select Run as Administrator or you will get a permissions error:

netsh advfirewall set global StatefulFtp enable

Table Wrapper Classes

We use Subsonic to generate DAL classes for our main ASP.NET project, and we are transitioning to LINQ where possible.  One thing we haven't figured out yet is how to return a collection of LINQ "rows" from a web method, and Subsonic is apparently having problems with complex data too, so that means we need to create our own classes to return complex data or collections of data from a web method.  I have written collection classes before as well as DAL classes but it is SUCH a pain to create all the public variables, public properties and all their get/set methods.  I found this on SqlServerCentral today and will try it out next time I have to create a wrapper class for a table:

CREATE PROCEDURE usp_TableToClass
/*
Created by Cade Bryant.
Generates C# class code for a table
and fields/properties for each column.

Run as "Results to Text" or "Results to File" (not Grid)

Example: EXEC usp_TableToClass 'MyTable'
*/

@table_name SYSNAME

AS

SET NOCOUNT ON

DECLARE @temp TABLE
(
sort INT,
code TEXT
)

INSERT INTO @temp
SELECT 1, 'public class ' + @table_name + CHAR(13) + CHAR(10) + '{'

INSERT INTO @temp
SELECT 2, CHAR(13) + CHAR(10) + '#region Constructors' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 3, CHAR(9) + 'public ' + @table_name + '()'
+ CHAR(13) + CHAR(10) + CHAR(9) + '{'
+ CHAR(13) + CHAR(10) + CHAR(9) + '}'

INSERT INTO @temp
SELECT 4, '#endregion' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 5, '#region Private Fields' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 6, CHAR(9) + 'private ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN 'int '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] '
WHEN DATA_TYPE = 'BIT' THEN 'bool '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
ELSE 'object '
END + '_' + COLUMN_NAME + ';' + CHAR(9)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 7, '#endregion' +
CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 8, '#region Public Properties' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 9, CHAR(9) + 'public ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN 'int '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] '
WHEN DATA_TYPE = 'BIT' THEN 'bool '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
ELSE 'object '
END + COLUMN_NAME +
CHAR(13) + CHAR(10) + CHAR(9) + '{' +
CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
'get { return _' + COLUMN_NAME + '; }' +
CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
'set { _' + COLUMN_NAME + ' = value; }' +
CHAR(13) + CHAR(10) + CHAR(9) + '}'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 10, '#endregion' +
CHAR(13) + CHAR(10) + '}'

SELECT code FROM @temp
ORDER BY sort

Using Telerik RadGrid - Codebehind - Subsonic

The following code is required in to populate the grid using subsonic:

        using Telerik.Web.UI;
...
        protected void rgCatalogs_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
        {
            Query qry = SalesCatalog.CreateQuery();
            rgCatalogs.DataSource = qry.ExecuteDataSet();
        }

Using Telerik's RadGrid - aspx code

The following html code is needed to create a table with 4 visible columns:

    <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>
    <telerik:RadGrid ID="rgCatalogs" runat="server" GridLines="None"  
      AutoGenerateDeleteColumn="false" AutoGenerateEditColumn="false" AutoGenerateColumns="false"
      OnNeedDataSource="rgCatalogs_NeedDataSource" >
        <MasterTableView DataKeyNames="rid">
        <Columns>
            <telerik:GridBoundColumn HeaderText="rid" UniqueName="rid" DataField="rid" Visible = "false" ReadOnly ="true"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn HeaderText="Description" UniqueName="Description" DataField="Description" ReadOnly ="false"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn HeaderText="StartDate" UniqueName="StartDate" DataField="StartDate" ReadOnly ="false"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn HeaderText="EndDate" UniqueName="EndDate" DataField="EndDate" ReadOnly ="false"></telerik:GridBoundColumn>
            <telerik:GridHyperLinkColumn HeaderText="ATS"  Text="ATS" UniqueName="ATS" DataNavigateUrlFields="rid" DataNavigateUrlFormatString="~/ATS/CatalogATS.aspx?catid={0}" /> 
        </Columns>
        <RowIndicatorColumn Visible="False">
        <HeaderStyle Width="20px"></HeaderStyle>
        </RowIndicatorColumn>

        <ExpandCollapseColumn Visible="False" Resizable="False">
        <HeaderStyle Width="20px"></HeaderStyle>
        </ExpandCollapseColumn>

        <EditFormSettings>
        <PopUpSettings ScrollBars="None"></PopUpSettings>
        </EditFormSettings>
        </MasterTableView>
    </telerik:RadGrid>

Setting up SQL server 2008 (to host Dynamics NAV)

I installed SQL 2008 Developer edition because the SQL 2005 Express edition I had been using isn't able to be accessed from web applications running outside the VS IIS instance and I needed to test an IIS hosted web service application.

The install took nearly 2 hours!  Becuase I was installing SSIS, SSRS, and the other tools.  It required an upgrade to .Net 3.5 SP1, VS 2008 SP1, in addition to 2.5 gb of HD space.

After the install, SQL 2005 Express could no longer access the databases so I had a momentary freak-out until I could mount them into SQL 2008, at which point I uninstalled SQL 2005 Express.

Upon attempting to connect from Dynamics NAV, I was reminded of the SQL setup you have to do for Dynamics NAV.  I always seem to forget that, and I am not the only one as I have been onsite to implement behind another analyst a few times and found that the setup hadn't been completed.  Here are the SQL modifications required for Dynamics NAV:

You have to set a trace flag by adding "-T 4616" to the startup parameter string:

http://tricksnavision.blogspot.com/2007/10/error-traceflag-4616-at-start-nav-in.html 

You have to add two extended stored procedures (xp_ndo_enumusersids and xp_ndo_enumusergrous) to the master database and grant execute permissions to the public role  as explained here:

http://blogs.msdn.com/nav_developer/archive/2008/11/05/basic-sql-creating-extended-stored-procedure-xp-ndo-dll.aspx

As a side note, all clients from 4.0 SP3 and later are compatible with SQL 2008, so there is no reason to use SQL 2005 with new installs.

Inserting into SQL table with Identity field

Sometimes you want to insert records into a table that has an Identity field but you need to insert a value into that field (normally the system assigns identity values).

The following code allows you to do that:

SET IDENTITY_INSERT BasicAttribute ON

insert into BasicAttribute (rid, typeid, code, description) values (0,0,'DEFAULT','Default')

SET IDENTITY_INSERT BasicAttribute OFF