Developing for Dynamics GP

eConnect error - The stored procedure doesn't exist

Patrick Roth — Fri, 10 Feb 2012 10:32:00 GMT

I ran across an odd eConnect issue a while back that I thought I'd share today.

In this case, the customer had recently updated to GP 2010 and updated their application to eConnect 2010 from the previous version that worked fine.

But now under GP 2010, when trying to run their eConnect integration it would fail with the error:

Microsoft.Dynamics.GP.eConnect.eConnectException: The stored procedure 'taCreatePayrollBatchHeaderInsert' doesn't exist.
at Microsoft.Dynamics.GP.eConnect.ServiceProxy.CreateTransactionEntity(String connectionString, String xml)
at Microsoft.Dynamics.GP.eConnect.eConnectMethods.EntityImportImplementation(String connectionString, String sXML, Boolean isTransaction)
at Microsoft.Dynamics.GP.eConnect.eConnectMethods.CreateTransactionEntity(String connectionString, String sXML)
at DirectDocSenderDotNet.FrmDocSender.btnSend_Click(Object sender, EventArgs e)

Now this was interesting in that it wasn't a standard business logic error and was a straight SQL exception instead.

Checking SQL, we verified that indeed the proc DID exist and the DYNGRP did have permissions to it (even though this didn't appear to be a permissions issue). The "eConnect" user was assigned to the DYNGRP role.

For kicks, we used DBMaintenance.exe to recreate the Dynamics database procs again - still the same issue despite the fact that the proc was there.

We executed a SQL trace on the server during this process but that seemed normal - except for the error.

We then checked the User settings and the ISV noticed something unusual.

For some reason, the Default Schema for this user for the database was set to "eConnect" instead of "dbo".

Now this made more sense - since the Default Schema for the database was not dbo, it couldn't see any objects that were dbo. We didn't notice any other issues with this user because it was the eConnect user and not a GP user so there were no Dynamics errors to worry about.

From there, it was a simple matter of setting the user default schema to 'dbo' and the application worked correctly from there.

Best regards,
Patrick Roth
Dynamics GP Developer Support

From the Blogsphere: Passwords, Hollywood and Jivtesh Downunder

David Musgrave — Fri, 10 Feb 2012 01:00:00 GMT

Here are some post from the blogsphere that caught my attention.

Simplify Your Passwords

Steve Endow from Dynamics GP Land brought this comic from XKCD.com to my notice a few months back and I have been meaning to re-post it for a while. I think that this comic/cartoon highlights that most computer users are creating "strong" passwords (ie. at least 8 characters with 3 out of uppercase, lowercase, numeric & symbols) that are hard for humans to remember and fairly easy for computers to crack. It suggests using a pass phrase of random words that have a meaning to you instead.

Click on the image to show full size. Image from http://xkcd.com/936/

Have a read of Steve's post: Simplify Your Passwords.

Microsoft Dynamics GP in Hollywood?

Francisco Hillyer posted at article on the Rose Business Solutions Dynamics GP blog, where he noticed Microsoft Dynamics GP being used in a movie. See the screenshot below:

Notice that this is an alternate window from Bill Marshall's MC²

For the details of which movie, you will have to have a read of Francisco's post: Microsoft Dynamics GP in Hollywood?

I have known that this was coming for a little while, but now it is public. Jivtesh Singh MVP and his wife Rubal have recently moved to my home town of Perth, Western Australia.

See Jivtesh's post: Down Under

Enjoy

David

Purchase Order Returns Modification

David Musgrave — Wed, 08 Feb 2012 01:00:00 GMT

This post is a public service announcement for those customers and partners using the free customization from Microsoft, Purchase Order Returns Modification (Product ID: 4159) for Microsoft Dynamics GP versions 9.0 and 10.0.

Note: The functionality of this customization has been incorporated into the core Dynamics.dic dictionary for Microsoft Dynamics GP 2010 and so is no longer required.

The customization provides a number of business logic tweaks to improve the functionality of Purchase Order Returns, such as making sure that the returned items use the same Unit of Measure as the received items. Also some of the reports and inquiry windows have been updated to include the return transactions.

So, why I am posting about this customization?

Last week, I had a support case for a site using this customization for an issue with the Purchase Order Received Not Invoiced (RNI) report, where the return transactions were not calculating the extended cost correctly and the Unit of Measure was not shown on the report.

I could not replicate this issue on my installation with the customization installed. Then I remembered that these issues sounded like some of the problems I fixed in the code back in September 2010. I then realised that the site was not running the latest code as I could see data that was missing on a screenshot.

The problem is that with free code like this, there is no mechanism to tell the customers and partners if there has been an update to the code, so all the sites who obtained the older "pre-fix" code will still have that older build.

While I am mentioning this customization, I will also say that in July 2008, I also added functionality that makes sure that the quantity returned against a shipment line is not greater than the quantity original shipped. To enable this check, you will need to add the following line into the Dex.ini file (as explained in the documentation):

POReturnsMod_ReturnedQTYCheck=TRUE

So, check if you have the latest version of the code by printing the Purchase Order Analysis report for Received Not Invoiced and check if the return transactions are indicated with an asterisk (*) at the end of the transaction number (as long as the transaction number is not the maximum character length) and that the Unit of Measure is shown.

Received Not Invoiced Report without Purchase Order Returns Modification

                                                                                                         Functional / Originating
     Receipt No.       Receipt Date  Site ID    U of M          Qty Shipped       Qty Invoiced  Shipment Unit Cost    Amt Not Invoiced
                                                               Qty Returned   Qty Inv Returned                          /Less Returned
 -------------------------------------------------------------------------------------------------------------------------------------
 PO2074             Z-US$            12/04/2017       ACETRAVE0001       A Travel Company
   100XLG                         Green Phone                                                  10  Each                   $30.00
                                                                                                                                
     RCT1163           12/04/2017    WAREHOUSE  Each                      6                  0              $30.00             $180.00
                                                                          3                  0                                  $90.00
                                                                                                                                      
                                                                                                                                      
                                                                                                                   -------------------
                                                                    Functional Amount Not Invoiced:                            $180.00
                                                                    Functional Amount Not Invoiced Less Returned:               $90.00
 

Received Not Invoiced Report with old Purchase Order Returns Modification

                                                                                                         Functional / Originating
     Receipt No.       Receipt Date  Site ID    U of M          Qty Shipped       Qty Invoiced  Shipment Unit Cost    Amt Not Invoiced
                                                               Qty Returned   Qty Inv Returned                          /Less Returned
 -------------------------------------------------------------------------------------------------------------------------------------
 PO2074             Z-US$            12/04/2017       ACETRAVE0001       A Travel Company
   100XLG                         Green Phone                                                  10  Each                   $30.00
                                                                                                                                
     RCT1164           12/04/2017    WAREHOUSE                            0                  3              $30.00            ($30.00)
                                                                          0                  0                                   $0.00
                                                                                                                                      
                                                                                                                                      
     RCT1163           12/04/2017    WAREHOUSE  Each                      6                  0              $30.00             $180.00
                                                                          3                  0                                   $0.00
                                                                                                                                      
                                                                                                                                      
                                                                                                                   -------------------
                                                                    Functional Amount Not Invoiced:                            $150.00
                                                                    Functional Amount Not Invoiced Less Returned:                $0.00

 

The above report adds the return transaction to the report but has a number of issues (highlighted): The Unit of Measure is missing. The returned quantity shows in the Qty Invoiced field. The extended cost field is not multiplied by quantity. The amount returned is treated as a negative invoice rather than a return. The Amount Not Invoiced Less Returned on the original transaction has been cleared to zero. The totals are not correct and do not match the original report.

Received Not Invoiced Report with new Purchase Order Returns Modification

                                                                                                         Functional / Originating
     Receipt No.       Receipt Date  Site ID    U of M          Qty Shipped       Qty Invoiced  Shipment Unit Cost    Amt Not Invoiced
                                                               Qty Returned   Qty Inv Returned                          /Less Returned
 -------------------------------------------------------------------------------------------------------------------------------------
 PO2074             Z-US$            12/04/2017       ACETRAVE0001       A Travel Company
   100XLG                         Green Phone                                                  10  Each                   $30.00
                                                                                                                                
     RCT1164*          12/04/2017    WAREHOUSE  Each                      0                  0              $30.00               $0.00
                                                                          3                  0                                ($90.00)
                                                                                                                                      
                                                                                                                                      
     RCT1163           12/04/2017    WAREHOUSE  Each                      6                  0              $30.00             $180.00
                                                                        (3)                  0                                 $180.00
                                                                                                                                      
                                                                                                                                      
                                                                                                                   -------------------
                                                                    Functional Amount Not Invoiced:                            $180.00
                                                                    Functional Amount Not Invoiced Less Returned:               $90.00

 

The fixed report now has the following fixes (highlighted): The Unit of Measure is now included. The returned quantity shows in the Qty Returned field. The extended cost is now multiplied by the quantity. The amount returned is shown as the Amount Not Invoiced ($0) less Returned (which is why it is negative). The Amount Not Invoiced Less Returned on the original transaction shows the amount of the transaction less returned ($0), because the returned amount is now on a separate line. Finally, the totals are now correct and match the original report.

The fixed report also includes a couple of additional features (highlighted): Return transactions will have an asterisk (*) added to the transaction number (space permitting). The quantity returned from each receivings transaction is shown on the receivings transaction in the Qty Returned field in parenthesis, this figure is for information only and is not used on the report.

Note: This report is not a modified or alternate report. It is still the original report, however the data in the temporary table used to generate the report has been manipulated by the customization just before the report is printed to add the return transactions into the report and adjust the receivings transactions accordingly.

If you don't have the latest code, please contact Professional Services (mbsprosv at microsoft dot com) to obtain the update.

David

The Kinect Effect and Kinect Star Wars: Duel

David Musgrave — Mon, 06 Feb 2012 01:00:00 GMT

Another off topic post, but one that shows lots of potential for many applications.

The Kinect was originally created for use with the Xbox 360, but has now found its way into many other applications. Applications such as optical camouflage, puppetry, robot automation and even real time lightsabers. For more examples go to http://kinecthacks.net/.

Anyhow Microsoft have acknowledged the possibility of using the Kinect with a PC for other non-gaming uses and as at last week on the 1^st of February have released the Kinect for Windows SDK. For more details go to http://www.kinectforwindows.com/.

We built Kinect to revolutionize the way you play games and how you experience entertainment. But along the way, people started using Kinect in ways we never imagined. From helping children with autism, to helping doctors in the operating room, people are taking Kinect beyond games. And that’s what we call the Kinect Effect.

Kinect Effect

(Please visit the site to view this video)

While we are talking about Kinect, one of the most anticipated Kinect games ever is getting closer ... Kinect Star Wars is coming soon....

Chris Pratt becomes more powerful than you can possibly imagine in this video for Kinect Star Wars (T), coming to Xbox 360 with Kinect!

Kinect Star Wars: Duel

(Please visit the site to view this video)

Note the custom R2-D2 Xbox 360 console (which beeps and whistles when turned on and off and when the drive tray is opened and closed) and C-3PO game controller at the end of the video. Also check out the trailers for the game: Teaser Trailer and E3 2011 Game Trailer

Check out http://kinect.com/ and http://www.kinectforwindows.com/ for more information and finally, to bring this post back on topic, have a look at Jivtesh Singh MVP using Microsoft Kinect with Microsoft Dynamics GP.

David

Microsoft Dynamics Convergence 2012 Houston Sessions

David Musgrave — Fri, 03 Feb 2012 01:30:00 GMT

It is now February and Converegence 2012 Houston is happening next month (March 18-21). If you are not registered to attend yet ..... why not? Sign up now click on the image above or the link below

Mariano Gomez (The Dynamics GP Blogster) and I will be back to present one session at the GPUG Day One event and two sessions (both repeated) for Convergence.

Make sure you come and learn all about the Support Debugging Tool and what it can do for you. We will be demonstration all the goodness in the tool including the new features in build 16.

The session details are:

UBGP18 Where do the Experts Turn for Answers?

Date and Time: Sunday, March 18 from 1:30 pm - 3:00 pm
Track: Microsoft Dynamics GP
Speaker(s): Mariano Gomez, David Musgrave
Audience: Business Decision Maker, End User, IT Professional, Power User, Prospect, Technical Decision Maker
Session Type: User Group Breakout
Skill Level: 100 - Introductory

Ever wonder where the leading Microsoft Dynamics GP community experts turn for help when they are “stuck”? We've interviewed Microsoft Dynamics GP Consultants and will put things in perspective for you. Come and learn about the resources they rely upon to research and troubleshoot issues with Microsoft Dynamics GP, or simply find the answer to that pressing question. Gain knowledge of the top sites, blogs, and discover where to find SQL scripts, free customizations, or entire reviews on existing functionality. * Presented by GPUG. *

CSGP19 Administering Microsoft Dynamics GP Like a Pro with the Support Debugging Tool (repeated)

Date and Time: Monday, March 19 from 3:30 pm - 4:30 pm & Wednesday, March 21 from 11:00 am - 12:00 pm
Track: Microsoft Dynamics GP
Speaker(s): David Musgrave, Mariano Gomez
Audience: IT Professional, Power User
Session Type: Concurrent Session
Skill Level: 200 - Intermediate

Following the success of this session at previous Convergence events, David Musgrave and Mariano Gomez return with a number of advanced features included in the popular Support Debugging Tool. Learn how to maintain a clean DEX.INI in a Terminal Server environment; execute complex profiling of transactions and data; and resolve day-to-day security issues and problems with dictionaries. Discover tricks used by Microsoft Support and partners, and witness real life scenarios--and resolutions.

Key Learning 1: Get valuable tricks on supporting a customer's environment easily

Key Learning 2: Learn what functionality is in the Support Debugging Tool

Key Learning 3: Understand how to have a clean terminal services environment with the Support Debugging Tool

CSGP23 Microsoft Dynamics GP: Advanced Administration with the Support Debugging Tool (repeated)

Date and Time: Tuesday, March 20 from 11:00 am - 12:00 pm & Wednesday, March 21 from 3:00 pm - 4:00 pm
Track: Microsoft Dynamics GP
Speaker(s): David Musgrave, Mariano Gomez
Audience: IT Professional, Power User
Session Type: Concurrent Session
Skill Level: 300 - Experienced

The Support Debugging Tool is recognized by system administrators and consultants as a valuable tool that eases the task of administering Microsoft Dynamics GP. In this new session, David Musgrave and Mariano Gomez, MVP will show you some of the advanced capabilities of the tool. Come learn about the Advance Debugger Mode; enabling SQL Server traces; and some of the programmability aspects of the Support Debugging Tool through practical, hands-on examples--while having fun doing so.

Key Learning 1: Learn how to program using the tool.

Key Learning 2: Learn about Advanced Debugger Mode

Key Learning 3: Learn how to enable SQL traces and how to interpret them

Also see Mariano's post: Microsoft Dynamics Convergence 2012 sessions now published.

Hope to see you in Houston.

PS: The Schedule Builder and Convergence Connect are now online too.

David

Quick Tip: Solving Dexterity Trigger Clashes

David Musgrave — Wed, 01 Feb 2012 01:00:00 GMT

In a recent support case, I had the rare situation where Dexterity Triggers clashed. Dexterity allows third party developers to create triggers against various events and scripts in a Dexterity application (like Microsoft Dynamics GP).

There are times where more than one third party product registers a trigger against the same event or script. Most of the time this does not cause issues and both products work together without issues. But every now and then, you will have a situation where the triggers "clash". A clash is when one trigger somehow prevents the other trigger from working correctly (or at all).

The best way to explain how this could happen is to take you through the situation in the support case.

The case was raised by a developing partner who was working on a customization to the Cash Receipts Entry (RM_Cash_Receipts) form in Microsoft Dynamics GP. Part of the customization required some conditions to be checked before a Cash Receipt document can be deleted. If the conditions are not met the requested delete action should be aborted.

This functionality was achieved with a trigger running before the change event of the Delete Button. This trigger can then use the reject script command to abort any further processing when the appropriate conditions are met.

All worked fine until the code failed to work at a customer's site. After some research it was discovered that the Payment Document Management (PDM) module was installed at this site and the code was failing because PDM was somehow interfering with our customization.

So firstly, how did we identify that Payment Document Management was the problem?

The first step is to capture a Dexterity Script Log (either using the the ScriptDebugger=TRUE Dex.ini setting or the Manual Logging Mode of the Support Debugging Tool). Capturing the Script Log when pressing the Delete Button on the RM_Cash_Receipts window.

On a working system we can see the call to change script of the button:

'RM_Cash_Receipts Delete Button CHG on form RM_Cash_Receipts'

On the non working system, the above script was not called at all, instead the first call was to:

'rvlrmPmntDocCashReceiptsDelete'

To identify the product that the trigger script comes from, go to the Customization Status window (Tools >> Customization >> Customization Status) and print the Trigger Report. Then use the Find menu to look for the script name (rvlrmPmntDocCashReceiptsDelete). In our case the report showed that the trigger was from Payment Document Management (PDM) and is registered before the Delete Button's change script..

Looking at the products in the Dynamics.set we found Payment Document Management (Product ID: 2150) was installed on this system and not on our test system.

The second step was to prove that PDM was the cause. You can do this be disabling the product and seeing if the code works again. You can disable the product by using one of the three methods below:

Removing the product's entries from the DYNAMICS.SET (after making a backup). This requires restarting the system and is more work than needed.
You can use Tools >> Customize >> Customization Status to disable a product's triggers for the current session.
Or you can use the Support Debugging Tool's Dictionary Control window which can also disable Alternate windows (without changing security) and also can remember that the products should be disabled until re-enabled.

So now we knew the exact cause of the issue...... The Payment Document Management dictionary was registering a trigger in the same location (Before Delete Button Change) and was using reject script to prevent the original script in the Dynamics dictionary from running. This in turn means that any other triggers on that same location will also be prevented from running.

So what is the solution?

Well, the solution is simple. We need our trigger to fire before PDM's trigger, so we can use reject script to abort further processing if needed. In our case we are using reject script to prevent further processing after some condition has failed, when PDM is using reject script to replace the original code with its own code.

Theory bit: Triggers on the same event are executed in the order they were registered. Triggers are usually registered in the Startup global procedure (or code called from Startup). The Startup global procedure is executed in the order the dictionaries are loaded. The order the dictionaries are loaded is controlled by the order of the products in the DYNAMICS.SET launch file.

So by placing our customization in the DYNAMICS.SET launch file before the Payment Document Management product, we can execute our trigger first and our code will work as desired.

To change the order of products in the DYNAMICS.SET you can manually edit the file with Notepad.exe or use the buttons on the right hand side of the Support Debugging Tool's Dictionary Control window (see screenshot above).

Once the change was made, the issue was resolved and the triggers no longer clashed.

Below is a script excerpt which shows how you can pop up a warning dialog when products are not listed in the correct order in the DYNAMICS.SET. You can use this code when you have a known clashing issue to resolve.

Code Excerpts from Startup Global Procedure and Trigger Handler script

{ Startup }
local integer l_result;
local string l_message;
if Launch_GetProdPosition(2150) > 0 then
 if Launch_GetProdPosition(2150) < Launch_GetProdPosition(Runtime_GetCurrentProductID()) then
  l_message = "Please adjust the DYNAMICS.SET launch file to place %1 before %2."; { should be using getmsg() }
  substitute l_message, Launch_GetProdName(Runtime_GetCurrentProductID()), Launch_GetProdName(2150);
  warning l_message;
 end if;
end if;
{Turn off the warning for literal strings}
pragma(disable warning LiteralStringUsed);
l_result = Trigger_RegisterFocus(anonymous('Delete Button' of window RM_Cash_Receipts of form RM_Cash_Receipts),
    TRIGGER_FOCUS_CHANGE, TRIGGER_BEFORE_ORIGINAL, script MBS_RM_Cash_Receipts_Delete_Button_CHG_PRE);
if l_result <> SY_NOERR then
    warning "RM_Cash_Receipts Delete Button CHG PRE Focus trigger registration failed.";
end if;
{Turn the literal string warning back on}
pragma(enable warning LiteralStringUsed);

{ MBS_RM_Cash_Rececipts Delete Button CHG PRE }
if ask("Delete Button: Issue Reject Script?", getmsg(YES), getmsg(NO), "") = ASKBUTTON1 then
 reject script;
end if;
 

I hope you find this information useful. It describes the methods to troubleshoot and test as well as methods to fix and avoid the issue in the future.

David

// Copyright © Microsoft Corporation. All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

Microsoft Dynamics Convergence 2012 Houston Videos

David Musgrave — Mon, 30 Jan 2012 01:00:00 GMT

Microsoft Dynamics Convergence 2012 in Houston on March 18-21 is getting closer by the day. Here are some videos to get you in the mood.

Welcome to Houston

(Please visit the site to view this video)

Welcome to the World

(Please visit the site to view this video)

See you there.

David

Failed eConnect document leaves orphaned records

Patrick Roth — Fri, 27 Jan 2012 09:15:00 GMT

I assisted on an eConnect incident today that was quite interesting and I thought I'd share the case and the solution.

The issue:

The ISV was integrating a Payables Invoice using eConnect 10.0.

The XML document had 2 distributions and the invoice data.

What the ISV found was that there was an issue in the "header" portion of the document and eConnect was giving the appropriate error for that mistake.

What was unusual in this case was that he found the distributions were still in the PM10100 after the error!!!!

The ISV submitted the XML file in a case using Fabrikam data so we could try it here.

Using the Direct Document Sender against Fabrikam on GP 2010 (it was handy) we couldn't reproduce the issue.

Well, that was 2010 and the ISV used 10.0 - that must be the issue and why we couldn't repro.

So trying the Direct Doc Sender with the XML on 10.0 to repro, nope still couldn't as the distributions were properly rolled back.

During the test, we did a SQL trace while running the integraton.

TRANSACTION START

The call to the proc for the taPMDistributionCreate runs twice - once for each distribution inserted.

Next the taPMTransactionInsert to create the header record

Because of the error in the header (it doesn't matter what the error is), the taUpdateString proc is called.

Lastly, the TRANSACTION ROLLBACK happens due to the error

So looks like what we'd expect and of course querying the PM10100 does not have the orphaned distributions.

Because we couldn't repro the issue with the XML, that would suggest the issue is either environment or with the application.

As a coding issue is easier to troubleshoot, we got the app from the ISV to test here.

The ISV app was straightforward (perhaps a test app he was using?) and easy to run. It essentially was a rewrite of the Direct Document Sender app and allowed the user to pick the XML file and then send it to eConnect - nothing fancy.

So it should work right?

Well, no it didn't. We got the same result as the ISV did - eConnect throws the exception on the document failure but the distributions are left in the table and not rolled back as part of the transaction.

What the heck?

Looking over the app, there really wasn't a lot of code for a bug to hide.

Push Button to import a text file.

Read throught it and put into a text field.

Set the connection string.

Send to the eConnect EntryPoint_Main() function.

Done.

What could be simpler and what could possibly be the issue?

Running a SQL trace looked similar as the trace from the Direct Doc Sender except for the beginning & ending lines. There was no TRANSACTION START of TRANSACTION ROLLBACK commands.

Puzzling - and why we were getting the orphaned distributions.

If there isn't a transaction rollback happening, then the 'bad' data inserted into the distribution table isn't rolled back with the failure. There isn't bad data inserted to the PM10000 table because with the error the insert never happened and so didn't need to roll back that piece.

So that explains that - but why?

Looking at the connection string more closely, I noted a property I've never used before - something that I'd never seen before honestly.

This extra property was

enlist=false

Enlist? What is that? Such as "Enlist into a SQL transaction = false?"

Sounded plausible to me. We took that out and re-ran the test with the ISV app.

Bingo - worked as expected. The SQL trace now did show the transaction start and rollback and the distributions were no longer in the table as desired.

I did a little research on this and this MSDN article talks about the property. Actually that isn't quite right and I can't find the exact article on MSDN that IS the right one. But this one does a good job of explaining the behavior.

So just a word of warning out there - you don't want to use this property in your eConnect integrations.

Best Regards,

Patrick Roth
Senior Escalation Engineer
GP Developer Support

Off Topic: Australia Day 2012 Eat Lamb Campaign

David Musgrave — Thu, 26 Jan 2012 01:00:00 GMT

Back in 2005, the Meat & Livestock Australia (MLA) organisation started a marketing campaign to encourage Australians to eat lamb on Australia Day (26^th January).

They enlisted Sam Kekovich, ex Australian Rules Football player, sports commentator and media personality (Wikipedia), to promote lamb with his deadpan presentation of monologues. These monologues have lots of Australianisms in them and have been a funny and satirical (and sometimes controversial) way to promote eating lamb cooked on the bar-be-que (BBQ or "barbie") on Australia Day as the Australian thing to do. Anything else is just un-Australian.

This year Lambassador of Australia, Sam Kekovich, is back with another address to the nation:

After years travelling the world on his one-man crusade against unAustralianism, Sam Kekovich has realised the job is too big for him and his dodgy knees alone. Old-fashioned diplambacy just isn't cutting it in this day and age. So it's time to change his tune and embrace popular culture. If UnAustralianism has gone viral, Sam's only antidote is to do the same.

(Please visit the site to view this video)

So here is the video of the Chop Culture hit: Barbie Girl (originally by Aqua):

Pop culture's now rife with unAustralian behaviour. Sam's only antidote is to create 'Chop Culture' and go viral himself with his 'chop song': "Barbie Girl". Help Sam get as many hits as possible for his chop hit Barbie Girl to get it to the top of the Nova Australia Day Countdown. You know it makes sense. (Starring pop starlet Melissa Tkautz, Australia's Got Talent winners Justice Crew and pop guru Richard Wilkins).

(Please visit the site to view this video)

To the Australian's around the world, Happy Australia Day. To everyone else, join us with a fun laugh at ourselves and some very clever marketing.

David

The Windows Phone $100 Challenge #smokedbywindowsphone

David Musgrave — Wed, 25 Jan 2012 01:00:00 GMT

At the recent CES 2012 conference Ben the PC Guy (@benthepcguy) and the Windows Phone team setup a challenge. They would give US$100 to anyone who could perform and everyday action on their non Windows Phone faster than Ben could on his HTC Titan Windows Phone.

The challenges included tasks such as

Taking a photo and posting it on Facebook
Locating a nearby 4 star restaurant and finding directions
Posting a status update onto multiple social networks
Texting to a friend
Listening to a song and then identify it, download it and play it

If the challenger wins they would get US$100. If the Windows Phone wins, the challenger has to admit on camera that they were "Smoked by a Windows Phone" (#smokedbywindowsphone). Some of the losers were lucky enough to be given a free Windows Phone.

The challenge ran for duration of the conference and the results were 30 wins, 3 losses and 1 draw. That's a score of 88%.... and some of the losses were by a second or so.

Below is a sample of the videos for the challenge

Introduction to the Windows Phone $100 Challenge

(Please visit the site to view this video)

Windows Phone $100 Challenge Day 1 Recap

(Please visit the site to view this video)

Windows Phone $100 Challenge Day 2 Recap

(Please visit the site to view this video)

Best of Windows Phone $100 Challenge Final Recap

(Please visit the site to view this video)

For more information check out Ben's posts on the Windows Phone Blog:

Try a Windows Phone today, I am sure you will find it faster and easier to use for everyday tasks than its competitors.

Enjoy

David

Microsoft Dynamics Knowledge Base Articles available from Microsoft Support

David Musgrave — Mon, 23 Jan 2012 01:00:00 GMT

I have been meaning to post something about this for a while and am finally getting around to it.

You know how Knowledge Base (KB) Articles (previously known as Tech Knowledge) are behind the PartnerSource/CustomerSource firewall meaning that you need a PartnerSource or CustomerSource login to be able to view them? And you know how there have been "issues" with the search engine for Knowledge Base?

Well, this is no longer a problem.

Knowledge Base articles are now available from the Microsoft Support site which is available to everyone and not behind a firewall or login. This means that the articles are now available to any search engine.

For example: Search for Frequently asked questions role based security in Microsoft Dynamics GP and it will find the article on the Microsoft Support site:

Frequently asked questions about role-based security in Microsoft Dynamics GP 10.0 and Microsoft Dynamics GP 2010 (KB 951229)
http://support.microsoft.com/kb/951229

So when you come across an old style Knowledge Base article (like below), or you know the KB article number you want, you can just change the URL to access the article from Microsoft Support.

https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;951229

You can also use the following link to open the Microsoft Support search with the Dynamics GP product already selected.

http://support.microsoft.com/search/?spid=641&adv=1

Hope you find this information useful.

David

From the Blogsphere: Sure Step Online, Hyper-V images and RapidTools

David Musgrave — Fri, 20 Jan 2012 01:00:00 GMT

Here is another update from the blogsphere.

The Microsoft Dynamics Sure Step tool has been updated and now has a Sure Step Online Project Wizard, check out Errol's post below for some of the benefits.

Post from Errol Schoenfish: NEW Microsoft Dynamics Surestep Online
Microsoft Dynamics Sure Step Online (CustomerSource)

The team at Inside Dynamics GP have posted a quick FAQ article to help people use the Dynamics GP 2010 R2 64-bit Hyper-V images:

FAQ post from Jay Manley: Dynamics GP 2010 R2 64-bit Hyper-V FAQs
Original post from Jay Manley: Microsoft Dynamics GP 2010 R2 64-bit Hyper-V Released!
Microsoft Dynamics GP 2010 R2 Hyper-V Demonstration Image Set (PartnerSource)

Jay Manley also has posted a few articles on the Rapid Tools and how they can work for you. Have a read of what he has to say:

Enjoy

David

From the Blogsphere: Victoria Yudin MVP, Statement of Direction and CRM Connector

David Musgrave — Wed, 18 Jan 2012 01:00:00 GMT

There has been lots happening and the year has only just started.

To start with, my friend and long time Microsoft Community member, Victoria Yudin has been awarded Microsoft Most Valuable Professional for Microsoft Dynamics GP for the eighth consecutive year. Congratulations Victoria, this is well deserved recognition for your work in the community.

Post from Victoria: Happy New Year - 2012
Microsoft Dynamics GP MVP List

The Microsoft Dynamics GP product team at Inside Dynamics GP have started the year by releasing a new Statement of Direction for Microsoft Dynamics GP document. The document refrences GP "15" which indicates a long and rich future for Microsoft Dynamics GP is ahead.

Post from Errol Schoenfish: Happy New Year with a NEW Statement of Direction
Statement of Direction for Microsoft Dynamics GP (PartnerSource)
Statement of Direction for Microsoft Dynamics GP (CustomerSource)

The Connector for Microsoft Dynamics Team have released a new version of the CRM Connector for Microsoft Dynamics. This version allows integration between Microsoft CRM and all four Microsoft ERP systems (GP, AX, NAV and SL). They have also released a couple of Connector for Microsoft Dynamics training videos.

Post from the team: New Connector for Microsoft Dynamics release available
Post from the team: Installing Connector for Microsoft Dynamics

(Please visit the site to view this video)

Post from the team: Upgrading Connector for Microsoft Dynamics to the latest version

(Please visit the site to view this video)

I am looking forward to a great year ahead.

David

How to configure SQL Profile Tracing using the Support Debugging Tool

David Musgrave — Mon, 16 Jan 2012 01:00:00 GMT

Following the release of Build 16 of the Support Debugging Tool, I want to take you through how to configure the SQL Profile Tracing functionality so you can gain the benefits of this awesome feature.

The Support Debugging Tool has always been able to capture a DEXSQL.LOG of the communication between the Dynamics GP Application and the SQL Server, but when a Stored Procedure is called on the SQL Server, we lose visibility of what is actually happening at the SQL Server. Now with SQL Profile Tracing we can see exactly what is happening at the server as well and match this information with other logs we have at the user interface, Dexterity application and SQL Server levels.

This information is available from the PDF User Guide that comes with the Support Debugging Tool in the archive file. The PDF should be installed along with the CNK file so that pressing F1 for help when on a Support Debugging Tool window will open the guide for you.

Below are step by step instructions to configure the recommended settings for SQL Profile Tracing:

On the SQL Server machine create a folder on a local drive for where the SQL Profile Trace files will be stored while they are being created. Note this local path for later.
Share this local folder on the network, so that all Microsoft Dynamics GP users will have Full Control to the folder. Note this network UNC path for later.
Create a user (for example: SQLTraceUser) to be used by SQL Profile Tracing system. The user can be a local user on the SQL Server or a domain user, but needs local Administrator rights on the SQL Server machine. It is recommended to set the password to not expire. Note the User ID and password for later.
Log into Microsoft Dynamics as ‘sa’ or a user with similar permissions.
Open the Support Debugging Tool main window (Microsoft Dynamics GP >> Tools >> Support Debugging Tool).
From the Support Debugging Tool main window select Options >> Dex.ini Settings.
From the main window select Options >> Administrator Settings to open the Administrator Settings window.

NOTE: If the Administrator Settings choice is not available on the menu, then Advanced Mode is not yet enabled. Please enable Advanced Mode using the Enable Debugger Advanced Mode Features from the Debug tab of the Dex.ini Settings window.
From the Administrator Settings window, on the General tab, click Edit SQL Profile Trace Settings to open the SQL Profile Trace Settings window.
On the SQL Profile Trace Settings window, make sure Single User Authentication Mode is selected. In this mode only the single user created earlier will need permissions to create SQL Traces and the permissions for individual users do not need to be changed or elevated.
Enter the user created previously and press tab. The system will then ask if you want to process the SQL Server Actions to enable the Authentication Mode, click Yes.
As each step of the SQL Server actions needed to enable the Authentication Mode are completed a desktop alert will be displayed. You will also be asked for the password for the user for the Enable xp_cmdshell proxy account step. The password is not validated at this time, so please ensure it is entered correctly.

NOTE: To see the list of individual steps for enabling or disabling the Authentication Mode, click the Process Single User Mode SQL Server Action or Process Multi User Mode SQL Server Action button. You can select to manually run all of the steps or select individual steps from the list.
You can change the Maximum Trace file size and Maximum number of Trace files if desired, or just leave the default values.
Enter in the Local Path set up previously (as created in step 1) and press tab. The system will then ask if you want to create the SQL Profile Trace SQL Components, click Yes to create the stored Procedures in the DYNAMICS system database.
Enter the UNC Network Path set up previously (as created in step 2) and press tab.
Make sure the Copy SQL Profile Trace files to Debugger Settings location option is enabled. This will copy the completed trace files from the SQL Server to the folder used for the Debugger Settings and logs.
Click OK to save the settings and close the SQL Profile Trace window.
On the Administrator Settings window, on the General tab, enable the Capture SQL Profile Trace option and set the desired Trace Mode (use Small, if unsure). This will enable SQL Profile Tracing for Manual Logging Mode and as the default value for Automatic Debugger Mode.
Click OK to save the settings and close the Administrator Settings window.

That's all folks.

Now when logging (both Manual Logging Mode and Automatic Debugger Mode) you have the option to capture a SQL Profile Trace as well as a DEXSQL.LOG, Dexterity Script Profile, Dexterity Script Log and a Macro recording.

David

Microsoft Dynamics GP Technical Conference Dates and Convergence Early Bird Reminder

David Musgrave — Fri, 13 Jan 2012 01:00:00 GMT

Pam and the team at Inside Dynamics GP have announced the dates for the Microsoft Dynamics GP Technical Conference for 2012. This will be the "Must Attend" event for all partners wanting to learn about GP "12".

There will be two tracks at the conference so you can focus on Developer content or Consultant content.... or even mix and match based on what interests you.

Save the date and come visit Fargo, ND, USA on the 12-14 September 2012.

While we are talking conferences, Microsoft Dynamics Convergence 2012 is getting closer. The event is in Houston, TX, USA on the 18-21 March 2012.

Mariano and I will be presenting again along with lots of great presenters from the MVP, Partner, Customer and Microsoft Community.

This is a reminder that the special Early Bird discount finishes soon in under 2 weeks, so go register now, click on the image below:

Hope to see you in Houston.

David

Support Debugging Tool Build 16 released

David Musgrave — Thu, 05 Jan 2012 21:00:00 GMT

A week into the New Year and no posts.... what's happening? Read on to understand where all my spare time has been going....

After over four months of development, testing and documentation, I am thrilled to announce that build 16 of the Support Debugging Tool is now available for download from PartnerSource. This build has over 50 changes and focuses on functionality for logging and debugging issues with Microsoft Dynamics GP, while still having some improvements for the database or application Administrator.

A quick hint about how much is included in this build is that the User Guide PDF has grown from 161 pages to 193 pages.

Highlights include:

Macro Recording Logging
SQL Profile Tracing Logging
Improved Trigger functionality including a Trigger Administration window
SQL Role Views from the Security Information window

Below is a summary of the changes made for releases 10.00.0016 and 11.00.0016, I have divided them into logical sections:

Fixes

Fixed Security Information Show Resources not displaying when a Security Role not assigned to any users was selected.
Fixed Logs, screenshots and table/record dumps being overwritten when new logs started in the same second by adding suffix to filename.
Update Trigger handling to allow Restore Value option to work for Text Field datatypes.
Fixed Security Privilege errors for non Dictionary Resource Security Objects not being logged when Security Profiler is running in the background.
Fixed Manual stopping of non-logging triggers using MBS_Trigger_Stop helper function not working.
Fixed Field Physical Name search in Resource Information window to handle Composite and Array fields correctly.
Fixed Tables Containing Field Lookup in Resource Explorer to handle Composite and Array fields.
Fixed Screenshot not being able to save image files to a UNC pathname. Files now saved to temp and moved to UNC Path.
Added check to see if email system is in use when emailing Screenshots as part of processing a trigger.

Enhancements

Updated Support Debugging Tool main window to move Logging Options to a sub window and just show current status.

Added optional display of form details when Security Privileges error is displayed to Administrator Settings.

Added Table restricted to Table Trigger Type to Automatic Debugger Mode to provide form restricted database triggers.
Added Focus Event with Table Trigger Type to Automatic Debugger Mode to provide access to a specified table buffer from the form.
Added Form Menu Trigger Type to Automatic Debugger Mode.
Added Minimize Debugger Log Entries for use with Automatic Debugger Mode Non-Logging Triggers to avoid writing to the Debugger Log.
Add option to Exclude Selected Users on Automatic Debugger Mode Triggers rather than include selected users.
Added option to control which logging modes are restarted when Automatic Debugger Mode trigger fires.
Added MBS_Logging_Start and MBS_Logging_Stop helper functions to programattically turn on and off Manual Logging Mode.
Updated ScreenShot's System Status report with Debugger Version and Settings folder, SQL Session SPID, and fixed Data Folder Path.
Added HOMEDRIVE and HOMEPATH Environment Variables to ScreenShot's System Summary Report.
Added Physical Memory Status to Screenshot's System Status Report.
Added Database information about System and Company Databases to ScreenShot's System Status report.
Added Dex.ini Setting MouseWheel to the Other tab of the Dex.ini Settings window.
Added MBS_Debug_LogAppDetails Dex.ini Setting to log details of the current application, available from Startup tab of Dex.ini Settings window.
Added Export to File or Email option to Tables Containing Field Lookup in Resources Explorer.
Added Export to File or Email option to Associated Tables Lookup in Resources Explorer.

Performance

Added performance enhancements to reading of the debugger.xml setup file using ctree caching files.
Improved performance for XML Export, Resource Information and Resource Explorer windows when displaying the number of records in tables.

Usability

Extended Automatic Debugger Mode Trigger Name field size from 30 to 60 characters and updated windows and reports to match.
Extended Runtime Execute Script Name field size from 30 to 60 characters and updated windows and reports to match.
Extended SQL Execute Script Name field size from 30 to 60 characters and updated windows and reports to match.
Extended XML Export Profile Name field size from 30 to 60 characters and updated windows and reports to match.
Added smart positioning for initial position of Support Debugging Tool main window based on primary screen resolution.
Added smart positioning for initial position of Automatic Debugger Mode Status window based on primary screen resolution.
Added Window Position Memory to Automatic Debugger Mode Status window using MBS_Debug_WinDebuggerStatus Dex.ini setting.
Added separate Auto Open setting to automatically open the Support Debugging Tool main window after login, previously was controled by Debugger Setup Mode setting.
Added checks for Logging Paths selection to check folder exists and has write access.
Added highlighting of Linked Table on the Associated Table Lookup for a Form in Resource Explorer.
Updated Automatic Debugger Mode Trigger Administration and Configuration Export/Import windows to show non-logging triggers with a different icon.

Features

Added Macro Recording Facility to Individual Logging, Manual Logging Mode and Automatic Debugger Mode.
Added check for MouseWheel=FALSE on version 10.0 and disable Macro Recording if Mouse Scroll Wheel not disabled.
Added SQL Profile Tracing Facility to Individual Logging, Manual Logging Mode and Automatic Debugger Mode.
Add features to Administrator Settings to configure, create and remove SQL Profile Tracing SQL Components.

Added Support for SQL Profile Tracing to specify Maximum Trace File Size and Maximum Number of Trace Files.
Added Version Control Checking for SQL Profile Tracing Stored Procedures to ensure SQL Tracing is only enabled when latest stored procedures are installed.
Added Check for SQL version to ensure that SQL Profile Tracing Events not compatible with SQL Server 2005 are excluded from the stored Procedures.
Added SQL Profile Trace SQL Components and permissions cleanup into un-install feature.
Added Detection and cleanup of stranded SQL Profile Traces on login.
Added SQL Profile Trace List window to display active traces and allow for stopping of stranded traces.

Added Trigger Administration Window for quick changes to delete, enable/disable or change start mode of triggers.

Added by User, by Database and by Role SQL Server Roles Views to right hand pane of Security Information Window.

Diagnostics

Reserve Tilde (~) prefix for Trigger IDs, Script IDs and Profile IDs for use by Microsoft Support.
Added Automation Functionality for use by Microsoft Support. Added Dex.ini settings MBS_Debug_Automate_File, MBS_Debug_Automate_Script, MBS_Debug_Automate_Status.
Added Additional User Confirmation Dialogs for Microsoft Support Diagnostics.

Downloads

Support Debugging Tool for Microsoft Dynamics GP 10.0

Support Debugging Tool for Microsoft Dynamics GP 2010 (v11.0)

NOTE: The tool is posted on PartnerSource and so can be downloaded by partners. If you are a customer, you will need to obtain the tool through your partner. Any feedback or questions about the tool will be handled via the Microsoft Dynamics GP Community Forum.

Please check out the Support Debugging Tool Portal (http://aka.ms/SDT) page, your one-stop-shop for all things relating to the Support Debugging Tool.

Please post your feedback on what you think of this build and what you would like to see in the future.

Thanks to Mariano Gomez, Robert Cavill and especially Sivakumar Venkataraman and Allan Cahill for their efforts in testing all the new features to make sure they worked as desired. Thanks also to Kelly Youells for getting the files and PartnerSource pages updated.

David

Merry Christmas 2011 and Happy New Year 2012 to you

David Musgrave — Fri, 23 Dec 2011 10:00:00 GMT

It is that time of year again....

The silly season is upon us. It seems that the religious meaning of Christmas has taken a back seat to the commerical side and spending money shopping. Even if you are not interested in the religious meaning, this is a time to celebrate life with friends and family.

Mike Tompkins is an extremely talented acapella artist who has written a song that is a fun reminder that Christmas is not just about material gifts.

(Please visit the site to view this video)

Note: I highly recommend watching his other videos at his pbpproductions's Channel.

On that note, I will leave you with one of my favourite funny videos for the year.

(Please visit the site to view this video)

Wishing you all the best for the Holiday Season and into the New Year.

David and the Developing for Dynamics GP team.

From the Blogsphere: Microsoft Dynamics GP 2010 R2 64-bit Hyper-V, Microsoft Dynamics GP 2010 Performance White Paper and Dynamics GP Blogster YouTube Channel

David Musgrave — Fri, 23 Dec 2011 09:00:00 GMT

This is just a quick post to update you with some of the recent happenings around the blogsphere.

On the Inside Dynamics GP Blog, Jay Manley (with help from Kelly Youells, Lyle Curry, Jason Siever, Ryan Sandness, Daniel Sly, Jon Studsrud, and John Dooley) has completed the first-ever 64-bit Hyper-V images for Microsoft Dynamics GP 2010 R2.

Please see the link below for the downloads of the 3 images:

Microsoft Dynamics GP 2010 R2 Hyper-V Demonstration Image Set (PartnerSource)

For all the details on what each image contains, see Jay's post:

Microsoft Dynamics GP 2010 R2 64-bit Hyper-V Released!

The Microsoft Dynamics GP 2010 Performance Whitepaper has been released:

Optimizing and Maintaining Performance for Microsoft Dynamics GP 2010 (PartnerSource)
Optimizing and Maintaining Performance for Microsoft Dynamics GP 2010 (CustomerSource)

Watch the Dynamics GP Support and Services Blog next week for more information. [Edit] Here is the link:

Check Out the New Performance White Paper for Microsoft Dynamics GP 2010!

My good friend and co-presenter, Mariano Gomez (The Dynamics GP Blogster) has created a YouTube channel and published some create demos of the Support Debugging Tool.

For details see Mariano's post:

The Dynamics GP Blogster's YouTube Channel launches with Decisions Fall 2011

Enjoy

David

29-Dec-2011: Added Link to Dynamics GP Support and Services Blog post.

Quick Tip: Working with VBA, Extender and Sales Transaction Entry

David Musgrave — Fri, 23 Dec 2011 08:00:00 GMT

I have completed the development project I was working last week and have another Quick Tip that came out of the project. For the previous Quick Tips related to this project have a look at Quick Tip: Passing Variables to SQL Server and Quick Tip: Passing Date and Time Variables to SQL Server.

The project involved Visual Basic for Applications (VBA) code as well as an Extender window linked to the Sales Transaction Entry and Sales Item Detail Entry windows. Both the VBA code and the Extender window object needed to reference the primary key fields for the Sales Order Processing (SOP) transaction.

The primary key for the SOP_HDR_WORK (SOP10100) table contains the 'SOP Type' (SOPTYPE) and the 'SOP Number' (SOPNUMBE) fields. So you would think that it alright to use these two fields when defining your Extender Window and add these two fields to Visual Basic for use with your VBA code. Until a few versions ago, you would have been fine with that assumption.

When the Advanced Distribution module was introduced and Fulfillment Order document type was added, a potential issue was created.

Without Advanced Distribution registered, the Fulfillment Order document type is not included and the value (by position) of the 'SOP Type' field on the window matches the value stored in the database tables.

Document Type Window Value Database Value

Quote 1 1

Order 2 2

Fulfillment Order N/A N/A

Invoice 3 3

Return 4 4

Back Order 5 5

Document Type	Window Value	Database Value
Quote	1	1
Order	2	2
Fulfillment Order	N/A	N/A
Invoice	3	3
Return	4	4
Back Order	5	5

With Advanced Distribution registered, the Fulfillment Order document type is included after the Order document type and the value (by position) of the 'SOP Type' field on the window no longer matches the value stored in the database tables.

Document Type Window Value Database Value

Quote 1 1

Order 2 2

Fulfillment Order 3 6

Invoice 4 3

Return 5 4

Back Order 6 5

Document Type	Window Value	Database Value
Quote	1	1
Order	2	2
Fulfillment Order	3	6
Invoice	4	3
Return	5	4
Back Order	6	5

In Dexterity, there is a global function SOPTypeToInt() in the DYNAMICS.DIC to handle the mapping of Window Value to Database Value for us, but how do we handle this for Extender or VBA?

So this is where my Quick Tip comes in...

Instead of using the 'SOP Type' field in your Extender objects or VBA code which will not always match the true value stored in the table, use the 'SOP Type Database' field. This field will match the value stored in the table and will avoid any confusion about whether an Invoice document is SOP Type 3 or 4.

For Extender, this change is simple, when defining the object, just select 'SOP Type Database' instead of 'SOP Type' on the windows where the 'SOP Type Database' field exists.

For VBA, selecting the 'SOP Type Database' field is a little harder as the field is a hidden field that can't be easily added to Visual Basic. It is possible to modify the window to make it visible so it can be added to VBA and then hiding it again. However there is a quicker (but unsupported) method.

The quicker method involves adding the SOP Type field (or any other field) to Visual Basic instead. Then saving the window as a Package file using Tools >> Customize >> Customization Maintenance. Make a backup of the Package file. Then open the Package file with Notepad.exe and find the field you added, for example:

    WindowField "SOP Type"
    {
     ArrayIndex "0"
     DisplayName "SOPType"
     Local "false"
     ProgrammerName "SOP Type"
    }

Edit the package to change the Field information. WindowField is the name that Dexterity uses with spaces and including the "(L) " for local fields. DisplayName is the Name to be used in VBA and is usually the name of the field (or prompt) without spaces (or the "(L) " for local fields). Local needs to be set to true or false depending on if the field is a local field, ie. does it have the "(L) " prefix for WindowField. Finally the ProgrammerName is the name of the field with spaces, but not including the "(L) " prefix for local fields.

    WindowField "SOP Type Database"
    {
     ArrayIndex "0"
     DisplayName "SOPTypeDatabase"
     Local "false"
     ProgrammerName "SOP Type Database"
    }

Save the edited package file and import it back into Dynamics GP. Now when you write your VBA code, use SOPTypeDatabase instead of SOPType.

Hope you find this tip helpful.

David

Cool Videos: Nokia Lumia 800 UK Launch & Bing with Jay-Z's Book - Decoded

David Musgrave — Fri, 23 Dec 2011 07:00:00 GMT

Another week has gone without any posts. That's OK as I have a number of posts to publish with some GP related news, another Quick Tip and some fun videos.

So starting with the fun Microsoft related videos.

Nokia Lumia Live ft deadmau5 lights up London with amazing 4D projection

On Monday 28^th November Nokia Lumia 800 with Windows phone http://nokia.ly/uBVXxw brought deadmau5 and the world's most advanced 4D technology together and created an amazing free light show at Millbank Tower, London.

Millbank was plunged into darkness with the iconic tower acting as the canvas for a never-before-seen spectacular. Each of the 120 metre high building's 800 windows were covered with vinyl as 16 powerful projectors, stationed 300 metres away on the other side of the river, beamed 3D images onto the structure. Huge butterflies flew across the London skyline and the tower was twisted, pulsated and even fell down. Billed as the "future of live events" the spectacular show was accompanied by music from super producer deadmau5, who created exclusive remixes for the performance -- adding the 4^th dimension.

The much-anticipated Nokia Lumia 800 is here. View all your texts and chats in one seamless conversation. Take, tag and upload photos to Facebook in seconds, and browse the web like you're on a PC. Find your way with satnav that even works without a signal. Nokia Lumia 800 makes the everyday amazing.

(Please visit the site to view this video)

After watching the actual performance, watch the following "Behind The Scenes" video to see how much work was involved to make it happen:

(Please visit the site to view this video)

Decoded - Advertising campaign of Jay-Z's book with Bing Maps

Bing and Jay-Z's publisher partnered for a advertising campaign to market both the book Decoded and search engine Bing.

By placing all the pages of text of his book on the locations where it took place and showing them in Bing Maps, the campaign resulted in a successful launch of his memoires and an increase in traffic for Microsft's Bing

(Please visit the site to view this video)

Enjoy

David

Quick Tip: Passing Date and Time Variables to SQL Server

David Musgrave — Fri, 16 Dec 2011 02:30:00 GMT

As a follow on from yesterday's post, Quick Tip: Passing Variables to SQL Server, I wanted to discuss the related topic about passing of date and time variables.

There are two main issues we see with passing Dates and Times and both can cause SQL Server to throw the following exception:

/* Date: 12/15/2011 Time: 10:44:04
SQLSTATE:(22008) Native Err:(241) stmt(99398048):*/
[Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date and/or time from character string.*/

Issue 1 - Date Variables

The first issue relates to the handling of Dates and does not show up in US sites, but does almost everywhere else in the world. It relates to the format of the Date and regional settings. SQL Server expects dates as a string in the format MM/DD/YYYY or YYYYMMDD. So when creating code to pass through to SQL Server I can use the str() function to convert the date to a string.

sqlstring = "select * from GL10000 where POSTDATE = '" & Str(Date) & "'"

This works fine in the United States, but has unexpected results elsewhere. The code would work without errors, but would have incorrect results for the first 12 days of a month, then starting on day 13 we would see the error above being generated. In the really early days of Microsoft Dynamics GP, we (the support team looking after Australia and New Zealand) saw this issue quite a bit with modules that are heavily optimized for SQL (meaning using lots of passthrough SQL from Dexterity).

The problem is that the code was written and tested in the US where the date format specified in the regional settings (MM/DD/YYYY) matches the format that SQL Server expects dates to be provided in. So using the str() function works, but elsewhere in the world the date format specified in the regional settings (DD/MM/YYYY) does not match and so the code fails.

Issue 2 - Time Variables

The second issue is related to Times and again is caused by regional settings. SQL Server expects times as a string in the format HH:MM:SS (24 hour time) or HH:MM:SS XX (12 hour time), where XX is a 2 character case insensitive suffix (AM, am, PM or pm). Now these two formats are the defaults for 24 and 12 hour time and so using the str() function works almost all the time.

sqlstring = "select * from GL10000 where TIME1 = '" & Str(TimeValue) & "'"

It can fail when the suffix characters have been altered by a user (or administrator). For example: the addition of periods/dots/full stops, such as A.M. and P.M. This no longer matches the acceptable formats and the error above would be generated.

Solutions

The solution for the date issue in Dexterity is to use the sqlDate() global function, this breaks down the date and builds a string that will always be in YYYYMMDD format (note the Dexterity function does not add the single quotes like SQL_FormatStrings so these need to be added in the code).

The solution for the time issue at this stage is to change the regional settings to 24 hour time (where the suffix is not used) or to change the suffixes back to the supported formats of AM/PM or am/pm. As this issue is very rare and easily fixed, a Dexterity sqlTime() function has not been created, but you can create one if needed.

The Knowledge Base (KB) articles I wrote a while back talk about these issues as well as the single quote issue in strings:

So to help VBA developers, I have created a similar sqlDate() function. You can add this function to each module as you need it or create a new code module so the single piece of code can be reused.

VBA Code for sqlDate()

Option Explicit
Public Function sqlDate(IN_Date As Date) As String
    sqlDate = Right("0000" + Trim(Str(Year(IN_Date))), 4) & _
              Right("00" + Trim(Str(Month(IN_Date))), 2) & _
              Right("00" + Trim(Str(Day(IN_Date))), 2)
End Function
 

While I am at it, here is a sqlTime() function you can use to get the time in 24 time. You can add this function to each module as you need it or create a new code module so the single piece of code can be reused.

VBA Code for sqlTime()

Option Explicit
Public Function sqlTime(IN_Time As Date) As String
    sqlTime = Right("00" + Trim(Str(Hour(IN_Time))), 2) & ":" & _
              Right("00" + Trim(Str(Minute(IN_Time))), 2) & ":" & _
              Right("00" + Trim(Str(Second(IN_Time))), 2)
End Function

Taking it one step further here is a function which handles date and time and also adds the single quotes.

VBA Code for sqlDateTime()

Option Explicit
Public Function sqlDateTime(IN_DateTime As Date) As String
    sqlDateTime = "'" & sqlDate(IN_DateTime) & " " & sqlTime(IN_DateTime) & "'"
End Function

Now before I get too many comments telling me this is not the most efficient method, I will mention that VBA has a Format() function that can do most of the formatting for us (including the single quotes).

sqlstring = "select * from GL10000 where POSTDATE = " & Format(Date, "'yyyymmdd'")

sqlstring = "select * from GL10000 where TIME1 = " & Format(TimeValue, "'hh:mm:ss'")

Hope you find this information useful.

David

PS: Yes, I know that the use of parameters as discussed in the comments of the previous post might avoid the need for these functions.

PPS: Let the comments discussion begin...

Quick Tip: Passing Variables to SQL Server

David Musgrave — Thu, 15 Dec 2011 12:00:00 GMT

Wow, the week is almost over and I am yet to post something.

This week I was working on a small development project using Visual Basic for Applications (VBA) to execute some commands at the SQL Server level after some user interface events. The code to be executed needed to have values from the windows passed through to the Transact-SQL. Now most people just write the code and pass through the value of a string field concatenated before and after with single quotes.

sqlstring = "select * from IV00101 where ITEMNMBR = '" & CStr(ItemNumber) & "'"

This is fine most of the time..... but what happens when the string field itself contains a single quote character.

Well this issue can occur in most languages when the string terminating character is included in the string itself and causes an early termination of the string. While the code will probably fail badly, this is also a security risk as it could be exploited to inject SQL commands.

I wrote a couple of Knowledge Base (KB) articles a while back that talked about this issue in relation to Dexterity and how you need to use the SQL_FormatStrings() global function to create the string value to send to SQL.

The SQL_FormatStrings() function adds the single quote delimiters but also will double up any single quotes in the string to ensure the resulting string does not terminate early. For example:

Field = "This is my test" so SQL_FormatStrings(Field) = 'This is my test'

Field = "This is Pat's test" so SQL_FormatStrings(Field) = 'This is Pat''s test'

Well, while writing this VBA project, I decided I needed a similar function for use with VBA and so I created a SQL_FormatStrings() function. You can add this function to each module as you need it or create a new code module so the single piece of code can be reused.

VBA Code for SQL_FormatStrings()

Option Explicit
Public Function SQL_FormatStrings(IN_String As String) As String
    Dim X As Integer, Y As Integer
    Dim l_String As String
    
    l_String = Trim(IN_String)
    
    X = 1
    Y = InStr(X, l_String, "'")
    While Y > 0
        l_String = Left(l_String, Y) + Mid(l_String, Y)
        X = Y + 2
        Y = InStr(X, l_String, "'")
    Wend
    
    SQL_FormatStrings = "'" & l_String & "'"
End Function
 

To use the function you just need to add it around your field. Note you can remove the single quotes from the query as the function adds them for you.

sqlstring = "select * from IV00101 where ITEMNMBR = " & SQL_FormatStrings(CStr(ItemNumber))

Adding the solution suggsted by Jon in the comments using the VBA Replace() function:

sqlstring = "select * from IV00101 where ITEMNMBR = '" & Replace(CStr(ItemNumber),"'","''") & "'"

Potential problem solved!!!

For related posts have a look at

Hope you find this useful.

David

16-Dec-2011: Added method using Replace() function.

SQL Server Error: Cannot resolve the collation conflict

David Musgrave — Fri, 09 Dec 2011 08:00:00 GMT

Today I have a post on the solution to a tricky SQL issue.

When working with SQL Server and creating a join that links tables in two databases, all goes well when the two databases have the same collation. However, if the databases have different collations the query it will fail in a writhing blubbery gelatinous slimy mess of confused character mappings, or at least return something similar to the following error:

Msg 468, Level 16, State 9, Line 4
Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and "Latin1_General_CI_AS" in the equal to operation.

This is because SQL Server cannot compare character or text fields across different collations. This issue affects the char, varchar, text, nchar, nvarchar, and ntext data types.

I came across this issue yesterday on my system as I was writing some code that used the sp_MSforeachdb command (see related post: Running SQL commands against all GP Company Databases). I had a chat with my friend and resident SQL guru, Robert Cavill, and he helped come up with the solution.

It turns out that the ReportServer$SQL2008R2 and ReportServer$SQL2008R2TempDB databases created on my SQL Server 2008 R2 instance have a different collation to the system default and the rest of the databases.

If you run the following SQL commands you can see the collation information for the system and the databases:

exec sp_helpsort
exec sp_helpdb

Note: For the database collation, look in the status column for the Collation property.

An easier method could be to retrieve the collation property directly with the following commands:

print convert(varchar(max),SERVERPROPERTY('Collation'))
print convert(varchar(max),DATABASEPROPERTYEX('ReportServer$SQL2008R2', 'Collation'))
print convert(varchar(max),DATABASEPROPERTYEX('DYNAMICS', 'Collation'))
print convert(varchar(max),DATABASEPROPERTYEX('TWO', 'Collation'))

On my system, this returned the results below, which shows the report server database has a different collation:

Latin1_General_CI_AS
Latin1_General_CI_AS_KS_WS
Latin1_General_CI_AS
Latin1_General_CI_AS

So if I create a query that joins between the Report Server and DYNAMICS databases it will fail with the error message above, for example:

select R.UserName from ReportServer$SQL2008R2.dbo.Users R
join DYNAMICS.dbo.SY01400 U on U.USERNAME = R.UserName

The solution is to use the COLLATE command to cast the collation of a character or text field into a specified collation. For more information about the COLLATE command, have a look at http://msdn.microsoft.com/en-us/library/ms184391.aspx

Using the COLLATE command I can force the collation for the fields used in the join expression to match, you can either change the first to match the second or the second to match the first. For example:

select R.UserName from ReportServer$SQL2008R2.dbo.Users R
join DYNAMICS.dbo.SY01400 U on U.USERNAME collate Latin1_General_CI_AS_KS_WS = R.UserName

select R.UserName from ReportServer$SQL2008R2.dbo.Users R
join DYNAMICS.dbo.SY01400 U on U.USERNAME = R.UserName collate Latin1_General_CI_AS

Or (based on Jon's feedback in the comments) you can use DATABASE_DEFAULT on both sides to make sure they match:

select R.UserName from ReportServer$SQL2008R2.dbo.Users R
join DYNAMICS.dbo.SY01400 U on U.USERNAME collate DATABASE_DEFAULT = R.UserName collate DATABASE_DEFAULT

Which way around you you write the code would depend on which would need less COLLATE statements when the query was more complex.

The funny thing is that today, I had a support case which asked about joining data between Microsoft Dynamics GP and Microsoft Dynamics CRM when the databases have different collations. As I had just dealt with the same issue, I could give them the answer straight away. This is what prompted me to write the solution as a blog article for all to benefit.

David

12-Dec-2011: Add example with DATABASE_DEFAULT collation.
collation.

No Blog Post Today

David Musgrave — Wed, 07 Dec 2011 01:00:00 GMT

Hey everyone, today there will be no blog post.

This is because it is my 20^th Wedding Anniversary and I have decided that I don't have to post anything today. Not even a picture that shows how young, slim and clean shaven I looked back then, like the one below:

I am sure you will forgive me, I will post something new soon.

David

Granting Access and Binding Defaults when recreating SQL Tables

David Musgrave — Mon, 05 Dec 2011 01:00:00 GMT

Added to the Useful SQL Scripts Series.

This is a follow on post from my previous article: Backing up and Restoring data when recreating SQL Tables which explains how to recreate tables (usually to fix table structure issues) while making sure that the data is preserved.

In the previous post; it has a step 3 to recreate the table and suggested that you can use the SQL Maintenance window from the application or T-SQL commands.

However, if you are in the middle of an upgrade with a failed table conversion, you will need to ensure that the table has the structure of the version you are upgrading from and not the version you are upgrading to. This will then allow the Microsoft Dynamics GP utilities to complete the table conversion for us (including any data manipulation required). So using SQL Maintenance while logged into an already upgraded company back to the failed company database is not the correct method.

Another method of re-creating the table is to generate the script from a correct version in a pre upgrade database. You can use the Script Table as and Script Stored Procedure as options to generate the scripts to Drop and Create the Table and its associated zDP Stored Procedures.

Using the scripts generated by this method miss a couple of steps. They don't:

Grant Access to DYNGRP for the Table and Stored Procedures
Bind Defaults for datetime, character, integer and currency datatypes

Granting access is required to allow users (other than 'sa') to be able to access the newly created SQL objects. Binding defaults sets up a default value for table columns to use when no data is provided on an insert statement. This will avoid "Cannot insert NULL" errors when inserting into a table without providing data for every column.

Note: Mariano has provided a method to get the Access and Defaults included in the scripts generated by SQL Server. Please see his post Granting Access and Binding Defaults when recreating SQL Tables: a follow up for details.

The following script (also attached at the bottom of this article) can be used to grant access to DYNGRP for the table and stored procedures and Bind Defaults for all tables in the database.

T-SQL Script Code

/* After creating a table and its associated stored procedures using  */
/* Script As >> Drop and Create To >> New Query Window, the following */
/* script will grant the access to DYNGRP and Bind the defaults       */
/* Written by David Musgrave, Last Modified: 02-Dec-2011              */
declare @Table varchar(20)
set @Table = 'SOP10110'
declare @cStatement varchar(255) 
declare G_cursor CURSOR for 
 select 'grant execute on ' + convert(varchar(64),name) + ' to DYNGRP' 
 from sysobjects  where type = 'P' and name like 'zDP%' + @Table + '%'
set nocount on 
OPEN G_cursor FETCH NEXT FROM G_cursor INTO @cStatement 
WHILE (@@FETCH_STATUS <> -1) begin 
 print (@cStatement)
 EXEC (@cStatement)
 FETCH NEXT FROM G_cursor INTO @cStatement 
end 
DEALLOCATE G_cursor
set @cStatement = 'grant select,update,insert,delete on ' + @Table + ' to DYNGRP' 
print (@cStatement)
EXEC (@cStatement)
print 'Bind Defaults for Date, String, integer and currency datatypes'
exec smBindTableDefaults 0 -- 0 = All datatypes
/*
// Copyright © Microsoft Corporation.  All Rights Reserved.
// This code released under the terms of the 
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)
*/

Hope you find this script useful.

David

06-Dec-2011: Added a link to Mariano's post which shows the steps to avoid the need for the additional script provided in this post.