Microsoft Free eBooks

At my local user group NEByes yesterday evening we enjoyed a session on Mobile Business Intelligence Using Microsoft (Jen Stirrup) and Tableau and SharePoint Virtualisation (Level 100) IT Pro or Technical Beginner (John Timney)

The following free ebooks were pointed out, although I knew some were available, it was a prod to go and investigate, here are the ones I found…

More...

After test upgrade of one of our GP installations we were getting the above error when trying to create a purchase order via eConnect. ‘PA01901’ is one of the project accounting tables, a module we don’t have installed.

Late into the night I Googled the issue and found almost nothing about it, also tried Customer Source where again there were some whispers but no substance.

I downloaded the service packs for eConnect. After applying eConnect Service packs for eConnect version11 up to pack 2 we still had the error.

Service packs tried;
MicrosoftDynamicsGP11-eConnect-x86-KB2386133-E.msp
MicrosoftDynamicsGP11-eConnect-x86-KB2435574-E.msp
MicrosoftDynamicsGP11-eConnect-x86-KB2561289-E.msp

After some pointers from a GP contact I have, it turns out that the eConnect stored procedure. taPoHdr is to blame. You can see the check below for the existence of the table in that procedure, the point at which the failure occurs.

if exists(select 1 from dbo.sysobjects (nolock) where name  = 'PA01901')
 and  exists (select 1 from POP10110 (nolock) where PONUMBER = @I_vPONUMBER) 
 and  not exists 
    (select 1 from PA01901 (nolock) where PATranType = 6 and PADocnumber20 = @I_vPONUMBER) 
begin 
...

The statement tries to see if it has any rows in the table if it exists or not. Obviously if it does not exist you can’t read the rows and falls over, it should have been nested logic here. An easy scripting mistake to make, shame it made it into production code though. It does prove the importance of coverage in testing and checking all code paths…

Solutions

This can be corrected as shown below by nesting correctly. This is how the problem is solved if GP2010 SP2 is applied. This change to the stored procedure is prevented as a user as this is a protected stored procedure, encrypted to prevent tinkering. I imagine if you managed to decrypt the stored proc and apply the fix to a live environment, it would not be supported.

if exists(select 1 from dbo.sysobjects (nolock) where name  = 'PA01901')
 begin
 if  exists (select 1 from POP10110 (nolock) where PONUMBER = @I_vPONUMBER) 
     and  not exists 
        (select 1 from PA01901 (nolock) where PATranType = 6 and PADocnumber20 = @I_vPONUMBER) 
     begin 
...
end

Alternative

You could try scripting out the table from your Fabrikam or TWO sample databases and using that to create an empty PA01901 table. However if you have not installed project accounting, it is unlikely to be there either. My other concern with that approach is that if you put those table in, other scripts might start trying to behave as if Project Accounting is installed, trying to add data to other tables that may not exists.
Safer to just get GP2010 SP2 installed.

http://community.dynamics.com/product/gp/f/32/p/42635/77416.aspx

The above post was the only related post I could find.

Replicating GP price table to website

To provide our website with bang up to date product prices as they are in our ERP system, we replicate the price table from our ERP system to the website SQL server database. The price table holds nearly two million price rows consisting of many combinations of currency, item, price quantity, units of measure, discount breaks and customer specific price lists.

The replicated table works great, until a big price update is required. If most of the prices are updated say in line with inflation, it hits a good number of those rows in the database. This causes a BIG transaction to make its way through the relatively thin wire to our website. From opening the transaction (and thus locking the subscriber table) to committing the transaction can take a long time locally and then for that to make its way through the slow connection to the website and be committed. All these processes take a finite amount of time. The lock caused on the price table at the website database while all this is happening causes a problem. That lock caused any reads on the price table to be blocked for a long time until everything had passed through, bringing the website to a halt for tens of minutes.

To avoid this queries at the website that interrogate the replicated publisher table could be set to READ_UNCOMMITTED transactions. However, potentially this could lead to problems in reading “dirty records” that are not ready for public consumption. This is significant when you consider these are price tables and reading prices that are in an unknown state is a no-no.

Solutions

First Idea

One was to take a database snapshot before the bulk price update, switch all the views on the table to use the snapshot letting replication take its time and update the records in the underlying table. Once finished the views could  be switched back to point at the original table again. This could, perhaps be controlled by a replicated signalling table from the ERP system so that we don’t have to issue SQL between the databases. It should work well in that once the all clear signal is set in the publication database, it will not propagate through to the subscriber until after all the other changes in the log have been replayed and committed to the subscriber.

Second Idea

The second idea was to switch the subscriber database into ALLOW_SNAPSHOT_ISOLATION ON mode. Simply by executing the following commands:

ALTER DATABASE [ERPReplicationdatabase]
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE [ERPReplicationdatabase]
SET READ_COMMITTED_SNAPSHOT ON

Once the database is in snapshot isolation mode, the reads will use versioned row numbers from the database. The reads are never blocked as when a transaction begins, the row version before the transaction and below is used for any reads. So reading the website is not blocked while a transaction is underway, and what is read is the state before the transaction started, that for our application is perfect.

No DML commands need issuing or signalling between the databases. This is the cleanest solution for what is required. The next task is to ensure that all the changes made to the price tables are made inside one transaction to keep the reads off any of the new changed data until is fully committed to the database.

“String or binary data would be truncated”, an error raised by SQL server when trying to update or insert data into a column that is bigger than that column can accommodate.

On my wish list for SQL server for a long time has been that this error should provide the column that raised the error as part of the reported error. Our Microsoft ERP system has very wide tables, approximately two hundred columns in some of them. When this error happens, it becomes a pain to track down the failing column update.

The only way I’ve got to do this at the moment is to add the LEN() function around every possible culprit and check the returned lengths manually against the schema. Whilst this works, it takes a long time, just for lack of the column name in the error raised.