Where did I put that trigger?

I needed to put some SQL triggers on my NAV tables the other day and when I came to check which triggers existed today, I had forgotten how I did it originally.

Here's a quick SQL script that will show you all table triggers in the current database.

SELECT [Trigger Name]=TRIG.name, [Table Name]=PARENT.name 
FROM (select * from sys.all_objects where type = 'TR') TRIG
JOIN (select * from sys.all_objects) PARENT
ON TRIG.parent_object_id = PARENT.object_id

I love TRANSFERFIELDS.

I've been doing a fair bit of coding recently and I must admit I love the concept of transferfields and the simplicity. It's really nice to know that when a customer wants a field to work it's way through to the posted documents from the source documents, you can do it all without writing any code.

Still, it would be nice if there were a way of stopping it from going wrong.

I hate TRANSFERFIELDS - Part II

I hate the TRANSFERFIELDS command. The last time I ranted about it I seemed to stir up some strong feelings, so I figured it’s time to bring this one up again.

Last time I commented on how the unsuspecting developer has no way of knowing what is going to happen when they use this command due to the way it copies fields between tables using the field ID to map the tables. Since then I have been tripped up again by this evil function and instead of simply moaning about it, I thought I would suggest an alternative.

So here I am going to introduce the all-new transferfields functionality. Microsoft, please feel free to take this idea and implement it in NAV.

The TRANSFERFIELDS command is programmed the same as now but in order to use it, a field mapping between the two tables must be defined at the table definition level. Let’s say we want to transfer fields from the Purchase Header table and the Purch. Inv. Header tables. You can find an example of this in the Purch.-Post codeunit as follows:

PurchInvHeader.TRANSFERFIELDS(PurchHeader);

This line takes the fields from the PurchHeader record variable and transfers the fields to the fields with the same field ID in the PurchInvHeader record variable.

If you have not defined a filed mapping between these two tables, the above code will not compile. In order to define the mapping I see it working like this.

Go to edit the Purchase Header table and select View menu and select the Table Mappings option. This will launch a form similar to the following:



On this form, you can insert a new line and enter the To Table ID to identify the table you want to map to. It would probably make sense at this stage for the system to automatically insert mapping between the two tables based upon the matching field IDs of the two tables. In this respect replicating the existing behavior would be pretty easy.

To customize the field mappings for the tables, the user will simply click the assist edit button on the Mapped Fields field and this will show the Field Mapping form.



Here the user can set up the field mapping. There are a couple of great things about this approach, you can ignore fields that you would never want to copy, such as the No. field. You can also map fields with different IDs. The other great thing is that field mappings can be managed by end users without the need to write code. An example would be if the user creates a new custom field on the Purch. Inv. Header table called Original Purchase Order No. The user can then make sure that the purchase order number ends up in this field by just using the mappings.

We could even add some rules on what to do when the fields don’t match in type, i.e. should we truncate the string or throw a run-time error.

Help!

In May I wrote a short piece for MSDynamicsWorld.com on on-line help and offered some thoughts on how it could be improved. In response to this article, I received a question asking how to configure the online help in Dynamics NAV.

In truth I have never done this. I knew I had read a document on how to do it but to be honest, it looked so hard I didn't bother. When I came to reply with the details, I really struggled to find the document I was looking for that described the help creation process. If you're looking for it, it's called NOHG.pdf

One of my points in the article is that we really need an easy-to-use help editor, similar to the one in Dyamics AX. Doh! I can't believe I just admitted something was better in AX!

Error: [Microsoft][ODBC SQL Server Driver]Database is invalid or cannot be accessed State ID: HY024

What a great title for a blog post?

I got this error when a customer had tried updating their NAV 4.0 SP3 to use Standard security as opposed to Enhanced.

I suggested all sorts of fancy SQL to resolve the situation thinking it was a permissions error as I know that changing a database to use Standard causes some problems on permissions with the Session and Database File view.

It turns out the user had left the database in single user mode - a pre-requisite of being able to change the security model.

I used Activity Monitor in SQL 2005 Management Studio to kill the connected process, then logged in to NAV and used File, Database, Alter to remove the single user mode from the database. Problem sorted. But what about those missing permissions?

If you create a NAV database with standard security, the system will allocate permissions to the Database File and Session views to the application server role $ndo$shadow. However, if you change a database from Enhanced to use Standard, these permissions do not get assigned and you therefore hit errors unless you are a db_owner.

To resolve this you can grant the permissions yourself as follows:

GRANT DELETE ON [dbo].[Database File] TO [$ndo$shadow]
GRANT INSERT ON [dbo].[Database File] TO [$ndo$shadow]
GRANT REFERENCES ON [dbo].[Database File] TO [$ndo$shadow]
GRANT SELECT ON [dbo].[Database File] TO [$ndo$shadow]
GRANT UPDATE ON [dbo].[Database File] TO [$ndo$shadow]
GRANT DELETE ON [dbo].[Session] TO [$ndo$shadow]
GRANT INSERT ON [dbo].[Session] TO [$ndo$shadow]
GRANT REFERENCES ON [dbo].[Session] TO [$ndo$shadow]
GRANT SELECT ON [dbo].[Session] TO [$ndo$shadow]
GRANT UPDATE ON [dbo].[Session] TO [$ndo$shadow]

User Acceptance Testing - the Key to Surviving an ERP Go Live

This posting was first published on the Intergen Blog Site on the 12th June.

When I came to New Zealand, nearly six years ago, I wanted to throw myself off a bridge with some elastic tied to my legs. I had heard that this was something Kiwis did and I wanted to fit in to my newly adopted environment. The world’s first commercial bungy jump started in the mid ‘80s in Queenstown, New Zealand. And it was there, on the Kawarau Bridge, that I took the plunge in 2002.

It was frightening but totally worth it. I knew it was safe but that didn’t stop me from being absolutely terrified. Sometimes in life, no matter how scared you are, you need to take a leap of faith. 5, 4, 3, 2, 1, bungeeeeeeeeey!

There are some similarities between bungy jumping and going live with an ERP solution and it was thinking about those similarities that inspired me to write this piece. Don’t panic, I’m not going to try and contort what I have to say about the ERP go live process to be all about bungy jumping using some clever metaphors. If, however, you do find yourself standing on the edge of an ERP implementation, you should read this article before jumping head first.

First of all, let’s consider the timing of the go live. How do you know when an ERP implementation is ready to go live? Is it when you have run out of time, or money? Often those, or some other seemingly arbitrary factor, are the main reasons for deciding on a go live date, but are you really ready to go live and how do you know?

The Six Ps

Like many things in life, prior preparation prevents pretty poor performance. In the case of an ERP implementation, the preparation comes in the form of user acceptance testing (UAT). User acceptance testing is often used as a project milestone for contractual reasons; completing UAT signifies that the solution has reached an acceptable level of stability and this in turn can be linked with the issue of who is going to pay for fixing defects. UAT is actually far more important than that — it is your key to project success. Imagine being the first person to bungy jump from the Kawarau Bridge. You’re standing 43 metres above the river with a bungy cord around your legs. Are you going to jump because you believe it should work in theory? I think not. Before you jump you’re going to do a little testing first: maybe throw a crash test dummy off the bridge to see if the harness holds, to ensure the rope is the right length. It’s important to iron out the issues in a safe environment first, and for an ERP implementation the safe environment is UAT.

Every issue that is found during UAT is one less issue that will need to be solved after go live, and the thing about go live issues is they can be really dangerous. When an issue occurs in a production system in a go live environment, it needs to be fixed quickly, and there is typically a great deal of stress associated with the issue. Being hurried in a stressful environment does not make for good programming and it certainly doesn't allow for well thought out design.

In order to avoid this stressful and potentially business-damaging situation you must start your preparation early, but what exactly is UAT. And, more specifically, what should you be testing?

Test the Entire Solution

In UAT, you are trying to simulate your go live situation. The closer you can get your UAT environment to your go live environment, the more confident you will be that you’re going to survive. Here’s a list of some of the things you need to be testing:

• The configuration of the production server hardware and server component installation, such as database, application server, SharePoint server, IIS, report server.


• The ability to install client software. It may be that you have a dedicated set of machines for the users to perform UAT, but if you are planning on installing client software as part of your go live, you should be testing your installation procedures by installing some software on some client machines that will be used for the testing. Go live day is not the day to hear, “Well it worked in the testing room - what’s different?”


• The configuration of your production ERP environment. The testing must be performed using the system with configuration settings as they will be in the live environment. It is inevitable that you will need to make some configuration changes during UAT in order to resolve issues; however, you must ensure that the same changes are logged and applied to the live environment. And you should also be aware that any change in configuration could invalidate all testing that has been completed so far.


• The data conversion process. Your data conversion process needs to be repeatable; you need to be able to extract the data to be converted consistently, hopefully using programs or scripts with a minimum amount of manual manipulation. The import of the data must obviously be done through dataports or other programs and the UAT is where you will test the success of those procedures. If you make changes to your data as a result of issues found during testing, you should ensure you can repeat this when you do go live and document the changes to the data conversion process.


• The modifications that have been made to the system: the new reports, forms, codeunits and external programs, interfaces, and reports all need to be tested. All too often this is the only thing that is covered in UAT. Once again, corrections may mean that previously accepted tests need to be re-tested. This is known as regression testing—it’s not what you fixed that’s important, it’s the things you broke along the way that you need to be aware of.


• The configuration of security privileges. This is to ensure that users can perform the tasks they need to perform without getting error messages and also to ensure that users do not have access to the data that they are not privileged to see.

Plan your testing and test your plan

The only way to have a successful UAT is to write down what you plan to test and record the issues you have found when they are tested. When you need to make the decision to go live or not, you will want to see the list of business process with lots of little ticks against them showing that they have been successfully performed. Remember the problem of regression testing: when you make a change to configuration, data conversion, or programming, you may as well erase all the ticks you have so far – it’s not what you fixed that’s important, it’s what you may have broken. If there are issues, you want to know what they are before you go live; it’s OK to go live with issues, it’s just important to know beforehand they are there and to have taken an informed decision.

When it comes to preparing test cases—well that’s another posting in its own right. Maybe one of our QA team can offer some advice in that area.

Get Some Help

If you have done your job properly, the go live should be painless. There may be a few surprises, but by and large you’ll be going live knowing that the critical things are going to work. It’s important to have good people around to help you through the go live process. When you’re standing on the platform with your towel wrapped tightly around your ankles, gazing at the horizon, it’s good to know that if you do freeze up, one of the professionals stood behind you is going to be quite happy to give you a little push.

Fast SQL Copy for Employee Portal

Recently I wanted to copy the contents of my Employee Portal setup from one company to another and then eventually from my test system to my live system. Now I know I could probably do this with a dataport and, to be honest, I did try that but something didn’t quite work. I probably made a silly mistake, but it did inspire me to create a SQL stored procedure to do the job for me.

This stored procedure uses the INFORMATION_SCHEMA in SQL to find the field names of any matchining tables and creates some SQL for me to execute. For safety, I only generate the SQL and do not execute it, giving you a change to change your mind if you don’t like what it is about to do.

The really neat trick is the fact that it will handle fields with an AutoIncrement property set in NAV.

I adapted this from a similar stored procedure I created a while ago that would copy all tables from one company to another which is considerably faster than the NAV backup/restore. For Employee Portal tables I am just assuming they start with ‘EP’ – another good reason to inspect the generated SQL before you execute it.

If you use this code and destroy anything, you only have yourself to blame and I will take no responsibility. This code is posted here “as is” for the sake of sharing knowledge and I will not be held responsible for any loss of data or hair that may result from running it.

Here’s the code...

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[NAV_EP_Copy]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
 DROP PROCEDURE dbo.NAV_EP_Copy
END
GO

CREATE PROCEDURE [dbo].[NAV_EP_Copy]
  @SourceCompany varchar(31), 
  @TargetDatabase varchar(31),
  @TargetCompany varchar(31) 
AS

--Version 2 - added target database and row count checks

DECLARE @SourceTableName varchar(100), @TargetTableName varchar(100)
DECLARE @TmpColumnName varchar(30), @TmpColumnType varchar(100)
DECLARE @SQLStr1 varchar(8000), @SQLStr2 varchar(8000), @SQLStr3 varchar(8000), @Values varchar(8000)
DECLARE @Identity int
DECLARE @ErrorCount int, @Debug int

SET @Debug = 1
SET @ErrorCount = 0

IF @Debug = 0 BEGIN
  PRINT 'Copying NAV Employee Portal'
  PRINT ''
  PRINT ' From ' + @SourceCompany
  PRINT ' To   ' + @TargetDatabase + ' ' + @TargetCompany
  PRINT ''
  PRINT ''
END

IF SUBSTRING(@SourceCompany,LEN(@SourceCompany),1) <> '$' BEGIN
  RAISERROR('Source Company Name must use SQL name (replace chars with underscore) and end in $.',1,1)
  SET @ErrorCount = @ErrorCount + 1
END


IF SUBSTRING(@TargetCompany,LEN(@TargetCompany),1) <> '$' BEGIN
  RAISERROR('Target Company Name must use SQL name (replace chars with underscore) and end in $.',1,1)
  SET @ErrorCount = @ErrorCount + 1
END

-- Debug mode does not do anything other than generate messages so who cares if there is data there alredy.
EXEC('set nocount on select top 1 1 from [' + @TargetDatabase + '].[dbo].[' + @TargetCompany + 'EP WP Request Table Tab'+']')

IF (@@rowcount > 0) BEGIN
 IF @Debug <> 1 BEGIN
  RAISERROR('Target EP WP Request Table Tab table has data.',1,1)
  SET @ErrorCount = @ErrorCount + 1
 END ELSE BEGIN
  PRINT '***** WARNING *****'
  PRINT 'Target EP WP Request Table Tab table has data.'
  PRINT 'Executing these commands will delete that data.'
  PRINT ' '
  PRINT 'Data Loss May Occurr.'
  PRINT '***** WARNING *****'
  PRINT ' '
 END
END

IF @ErrorCount > 0 GOTO ENDHERE

DECLARE srcTable CURSOR FOR
  SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_NAME LIKE(@SourceCompany+'EP%')

OPEN srcTable

FETCH NEXT FROM srcTable INTO @SourceTableName
WHILE @@FETCH_STATUS = 0 BEGIN
  
  SET @TargetTableName = @TargetCompany+SUBSTRING(@SourceTableName,LEN(@SourceCompany)+1,100)
  SET @SQLStr1 = ''
  SET @SQLStr2 = ''
  SET @SQLStr3 = ''
  SET @Values = ''

  IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'IsIdentity')=1 AND TABLE_NAME = @SourceTableName) > 0 BEGIN
    SET @SQLStr1 = 'SET IDENTITY_INSERT [' + @TargetDatabase + '].[dbo].[' + @TargetTableName+ '] ON '
    SET @Identity = 1
  END

  SET @SQLStr1 = @SQLStr1 + 'TRUNCATE TABLE [' + @TargetDatabase + '].[dbo].[' + @TargetTableName+ '] '
  
  DECLARE srcColumn CURSOR FOR
    SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @SourceTableName
  
  OPEN srcColumn

  SET @SQLStr2 = @SQLStr2 + 'INSERT INTO [' + @TargetDatabase + '].[dbo].[' + @TargetTableName+ '] ('

  FETCH NEXT FROM srcColumn INTO @TmpColumnName, @TmpColumnType
  
  WHILE @@FETCH_STATUS = 0 BEGIN
    
    SET @SQLStr2 = @SQLStr2 + ' ['+@TmpColumnName+']'
    
    IF @TmpColumnType = 'timestamp'
      SET @Values = @Values + ' NULL'
    ELSE 
      SET @Values = @Values + ' ['+@TmpColumnName+']'
  
    FETCH NEXT FROM srcColumn INTO @TmpColumnName, @TmpColumnType
  
    IF @@FETCH_STATUS = 0 BEGIN
      SET @SQLStr2 = @SQLStr2+',' 
      SET @Values = @Values+',' 
    END
  END

  SET @SQLStr2 = @SQLStr2 + ')'
  SET @SQLStr3 = 'SELECT ' + @Values + ' FROM ['+@SourceTableName+'] ' 

  CLOSE srcColumn
  DEALLOCATE srcColumn

  IF @Identity = 1 BEGIN
    SET @SQLStr3 = @SQLStr3 + ' SET IDENTITY_INSERT [' + @TargetDatabase + '].[dbo].[' + @TargetTableName+ '] OFF '
    SET @Identity = 0
  END

  IF @Debug = 1 BEGIN
    PRINT @SQLStr1
    PRINT @SQLStr2
    PRINT @SQLStr3
  END ELSE BEGIN
    PRINT ''
    PRINT '-------------------------------------------------------------'
    PRINT @TargetDatabase + ' ' + @TargetTableName
    EXECUTE ( @SQLStr1 + @SQLStr2 + @SQLStr3 )
  END

  FETCH NEXT FROM srcTable INTO @SourceTableName
END

CLOSE srcTable
DEALLOCATE srcTable

ENDHERE: