Jumping on the Windows 8 Metro bandwagon

September 26, 2011

In my most recent In My Humble Opinion article I weigh in on the subject of Windows 8 Metro and Microsoft Dynamics GP “12” web client, and Microsoft’s decision to go it the Silverlight route. Read why I think Silverlight is still the best choice as a development platform and how Windows 8 can still drive business applications requiring rich controls on the front-end.

You can read the article at – Jumping on the Windows 8 bandwagon on the Community site.

For more information on Windows 8, including a link to download the Developer’s edition, click below:

Read more at the Microsoft News Center

Download the Windows Developer Preview

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Advertisements

Year-to-year Inventory Margin Report using the PIVOT operator in T-SQL – Follow up!

September 23, 2011

Ok, of course my dear friend Corey in Louisville, Ohio had to challenge my post from yesterday and ask a question that is, frankly, a logical and natural progression of things – see Year-to-year Inventory Margin Report using the PIVOT operator in T-SQL.

In yesterday’s post I showed a query that could create a year-to-year Inventory Margin report using T-SQL’s PIVOT operator. The query, while very useful, hardcodes the years you want to display in the pivoted columns list. An excerpt of the PIVOT operator and the pivoted columns list as follows:

.
.
.
PIVOT (
SUM(MARGIN)
FOR TRXYEAR IN ([2014], [2015], [2016], [2017])
) AS pvt

So naturally, Corey’s question was, “What if I want to add the pivoted columns list dynamically, instead of hardcoding them?“. Since all transactions have a date, it would make sense to add the list of years based on the document dates. The good news is this is possible. For this we would need to create a dynamic pivoted list using, well, dynamic SQL, as follows:

DECLARE @listCol NVARCHAR(MAX), @sqlstmt NVARCHAR(MAX);

SELECT @listCol = STUFF(
( SELECT DISTINCT '],[' + CONVERT(NVARCHAR(5), YEAR(DOCDATE))
FROM SOP30200
ORDER BY '],[' + CONVERT(NVARCHAR(5), YEAR(DOCDATE))
FOR XML PATH('')
),
1,
2,
'') + ']'

SET @sqlstmt =
N';WITH CTE AS (
SELECT YEAR(b.DOCDATE) AS TRXYEAR, a.ITEMNMBR, a.ITEMDESC, SUM(a.XTNDPRCE) AS XTNDPRCE, SUM(a.EXTDCOST) AS EXTDCOST, SUM(a.XTNDPRCE - a.EXTDCOST) AS MARGIN
FROM SOP30300 a LEFT OUTER JOIN SOP30200 b ON (a.SOPTYPE = b.SOPTYPE) AND (a.SOPNUMBE = b.SOPNUMBE)
WHERE (b.SOPTYPE = 3) AND (b.VOIDSTTS = 0)
GROUP BY YEAR(b.DOCDATE), a.ITEMNMBR, a.ITEMDESC
)
SELECT ITEMNMBR AS [Item Number], ITEMDESC AS [Item Description],' + @listCol + N'
FROM (
SELECT TRXYEAR, ITEMNMBR, ITEMDESC, MARGIN
FROM CTE
) p
PIVOT (
SUM(MARGIN)
FOR TRXYEAR IN (' + @listCol + N')
) AS pvt
ORDER BY ITEMNMBR;';

EXEC sp_executesql @sqlstmt;

Couple observations…

We use the FOR XML clause with the PATH mode to build a list of elements and attributes based on the distinct year values stored in the document date (DOCDATE) column in our SOP30200 (SOP History) table, which we store in the @listcol variable length Unicode character data type (NVARCHAR).

Our CTE is now embedded in an NVARCHAR variable, which we call @sqlstmt, with just the right breaks to concatenate our pivoted column list variable, @listcol as part of the overall SQL statement character string that will be executed.

We finally use the sp_executesql system stored procedure to run our dynamic SQL query and produce the results below:

Item Number Item Description 2014 2015 2016 2017 2018
100XLG Green Phone NULL NULL 307.05000 40.05000 NULL
3-B3813A Keyboard NULL NULL NULL 40.00000 NULL
3-C2924A SCSI Cable, 2.5m. 68-pin HI-Density NULL NULL NULL 148.50000 NULL
3-C2924A T0101 - SCSI Cable, 2.5m. 68-pin HI-Density NULL NULL NULL NULL 84.38000
3-C2924A T0102 - SCSI Cable, 2.5m. 68-pin HI-Density NULL NULL NULL NULL 67.50000

Finally, you will notice NULL values for some of the years where there was no sales activity for an item. You can take care of these and how they display, directly on your report or Excel spreadsheet.

Not bad at all!

For a primer on the Do’s and Don’ts of dynamic SQL, I invite you to read SQL Server MVP, Earland Sommarskog article, The Curse and Blessings of Dynamic SQL.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/


Year-to-year Inventory Margin Report using the PIVOT operator in T-SQL

September 22, 2011

As of late I have been camping out at the SQL Developer Center’s  Transact-SQL Forum and I have to say, I have learned a great deal from my fellow SQL Server MVPs. Very alike the Microsoft Dynamics GP MVPs, these folks are willing to resolve pretty much any T-SQL request that comes across the forum.

So long you follow some simple posting guides, like including your table definitions, providing some sample data and detailing your expected results, there’s nothing these folks won’t do for you.

I decided to put some of what I have learned to the test by creating a T-SQL query that would produce a Year-to-year Inventory Margin Report (in currency) by using the T-SQL PIVOT operator. My fellow MVP Mark Polino is very well versed with Microsoft Excel and PowerPivot and I thought this would be a way to demonstrate that you can still use T-SQL to resolve some very complex issues which otherwise would require the use of front-end tool or some back-end cubes.

The following query can be run against the Fabrikam test company and adjusted to meet your specific needs:

;WITH CTE AS (
SELECT YEAR(b.DOCDATE) AS TRXYEAR, a.ITEMNMBR, a.ITEMDESC, SUM(a.XTNDPRCE) AS XTNDPRCE, SUM(a.EXTDCOST) AS EXTDCOST, SUM(a.XTNDPRCE - a.EXTDCOST) AS MARGIN
FROM SOP30300 a LEFT OUTER JOIN SOP30200 b ON (a.SOPTYPE = b.SOPTYPE) AND (a.SOPNUMBE = b.SOPNUMBE)
WHERE (b.SOPTYPE = 3) AND (b.VOIDSTTS = 0)
GROUP BY YEAR(b.DOCDATE), a.ITEMNMBR, a.ITEMDESC
)
SELECT ITEMNMBR AS [Item Number], ITEMDESC AS [Item Description],
COALESCE([2014], 0) AS Y2014,
COALESCE([2015], 0) AS Y2015,
COALESCE([2016], 0) AS Y2016,
COALESCE([2017], 0) AS Y2017
FROM (
SELECT TRXYEAR, ITEMNMBR, ITEMDESC, MARGIN
FROM CTE
) p
PIVOT (
SUM(MARGIN)
FOR TRXYEAR IN ([2014], [2015], [2016], [2017])
) AS pvt
ORDER BY ITEMNMBR;

The script uses a query encapsulated in a Common Table Expression (CTE) to produce a temporary result for the sales data by item, grouped over each year. However, this result would produce rows of records for each year, as shown below:

SELECT YEAR(b.DOCDATE) AS TRXYEAR, a.ITEMNMBR, a.ITEMDESC, SUM(a.XTNDPRCE) AS XTNDPRCE, SUM(a.EXTDCOST) AS EXTDCOST, SUM(a.XTNDPRCE - a.EXTDCOST) AS MARGIN
FROM SOP30300 a LEFT OUTER JOIN SOP30200 b ON (a.SOPTYPE = b.SOPTYPE) AND (a.SOPNUMBE = b.SOPNUMBE)
WHERE (b.SOPTYPE = 3) AND (b.VOIDSTTS = 0)
GROUP BY YEAR(b.DOCDATE), a.ITEMNMBR, a.ITEMDESC

/* Results */

TRXYEAR ITEMNMBR ITEMDESC XTNDPRCE EXTDCOST MARGIN
2014 ACCS-CRD-12WH Phone Cord - 12' White 39.80000 13.16000 26.64000
2014 ACCS-RST-DXBK Shoulder Rest-Deluxe Black 29.85000 13.65000 16.20000
2014 ACCS-RST-DXWH Shoulder Rest - Deluxe White 39.80000 18.20000 21.60000
2014 ANSW-ATT-1000 Attractive Answering System 1000 119.95000 59.29000 60.66000
2014 ANSW-PAN-1450 Panache KX-T1450 answer 219.90000 100.50000 119.40000
2014 FAXX-CAN-9800 Cantata FaxPhone 9800 23999.50000 11970.00000 12029.50000
2014 FAXX-HLP-5433 Hewlett Packard FAX-310 854.50000 395.10000 459.40000
2014 FAXX-SLK-0172 Sleek UX-172 fax 2699.90000 1349.00000 1350.90000
2014 HDWR-PNL-0001 Control Panel 609.95000 303.85000 306.10000
2014 HDWR-PRO-4862 Processor 486/25MHz 5999.95000 2998.15000 3001.80000
2014 PHON-ATT-53BL Cordless-Attractive 5352-Blue 1139.70000 561.30000 578.40000
2014 PHON-ATT-53WH Cordless-Attractive 5352-White 189.95000 92.59000 97.36000
2014 PHON-BUS-1250 Handset,multi-line 359.95000 165.85000 194.10000
2014 PHON-PAN-2315 Panache KX-T231 wall 119.90000 59.50000 60.40000
2014 WIRE-SCD-0001 Single conductor wire 8.75000 4.00000 4.75000
2016 100XLG Green Phone 4136.55000 3829.50000 307.05000

But in order to produce the results we want, having a true year by year comparison, it is necessary to pivot the results of the above query. Here’s where the PIVOT operator comes into play, as shown in the initial query. This is the output produced:

Item Number       Item Description      Y2014  Y2015  Y2016  Y2017
100XLG Green Phone 0.00000 0.00000 307.05000 40.05000
3-B3813A Keyboard 0.00000 0.00000 0.00000 40.00000
3-C2924A SCSI Cable, 2.5m. 68-pin HI-Density 0.00000 0.00000 0.00000 148.50000
3-C2924A T0101 - SCSI Cable, 2.5m. 68-pin HI-Density 0.00000 0.00000 0.00000 0.00000
3-C2924A T0102 - SCSI Cable, 2.5m. 68-pin HI-Density 0.00000 0.00000 0.00000 0.00000
3-D2657A T0101 - DB 15 Male Adapter 0.00000 0.00000 0.00000 0.00000
3-D2657A T0102 - DB 15 Male Adapter 0.00000 0.00000 0.00000 0.00000
3-D2659A T0101 - DB 25 Female Adapter 0.00000 0.00000 0.00000 0.00000
3-D2659A T0102 - DB 25 Female Adapter 0.00000 0.00000 0.00000 0.00000
3-E4471A HP Extractor Fan, 200-240V 0.00000 0.00000 0.00000 134.00000
3-E4592A SurgeArrest Plus 0.00000 0.00000 0.00000 180.00000
3-E4592A T0101 - SurgeArrest Plus 0.00000 0.00000 0.00000 0.00000
3-E4592A T0106 - SurgeArrest Plus 0.00000 0.00000 0.00000 0.00000
3-J2094A HP-PB 16 Channel RS-232C Modem Conn MUX 0.00000 0.00000 0.00000 473.00000
4-A3666A 4.2GB LP Disk Drive 0.00000 0.00000 0.00000 350.00000
5-FEE Per Call Fee 0.00000 0.00000 0.00000 0.00000
5-FEE T0101 - Per Call Fee 0.00000 0.00000 0.00000 0.00000
5-FEE T0102 - Per Call Fee 0.00000 0.00000 0.00000 0.00000
5-OVTLABOR T0101 - Overtime service labor 0.00000 0.00000 0.00000 0.00000
5-OVTLABOR T0102 - Overtime service labor 0.00000 0.00000 0.00000 0.00000
5-STDLABOR T0101 - Standard service labor 0.00000 0.00000 0.00000 0.00000
5-STDLABOR T0102 - Standard service labor 0.00000 0.00000 0.00000 0.00000
5-STDLABOR T0106 - Standard service labor 0.00000 0.00000 0.00000 0.00000
5-TVLLABOR T0101 - Travel Labor 0.00000 0.00000 0.00000 0.00000
5-TVLLABOR T0102 - Travel Labor 0.00000 0.00000 0.00000 0.00000
5-TVLLABOR T0106 - Travel Labor 0.00000 0.00000 0.00000 0.00000
ACCS-CRD-12WH Phone Cord - 12' White 26.64000 0.00000 199.80000 226.44000
ACCS-CRD-25BK Phone Cord - 25' Black 0.00000 0.00000 69.85000 83.82000
ACCS-HDS-1EAR Headset-Single Ear 0.00000 0.00000 1034.00000 813.35000
ACCS-HDS-2EAR Headset - Dual Ear 0.00000 0.00000 0.00000 527.67000
ACCS-RST-DXBK Shoulder Rest-Deluxe Black 16.20000 0.00000 226.80000 226.80000
ACCS-RST-DXWH Shoulder Rest - Deluxe White 21.60000 0.00000 163.20000 205.20000
ANSW-ATT-1000 Attractive Answering System 1000 60.66000 0.00000 242.64000 303.30000
ANSW-PAN-1450 Panache KX-T1450 answer 119.40000 0.00000 1194.00000 1134.30000
ANSW-PAN-2460 Panache KX-T2460 answer 0.00000 0.00000 149.60000 169.60000
FAXX-CAN-9800 Cantata FaxPhone 9800 12029.50000 0.00000 66987.41000 51129.85000
FAXX-HLP-5433 Hewlett Packard FAX-310 459.40000 0.00000 0.00000 0.00000
FAXX-RIC-060E Richelieu Fax 60E 0.00000 0.00000 2404.50000 2404.50000
FAXX-SLK-0172 Sleek UX-172 fax 1350.90000 0.00000 1350.90000 675.45000

Happy pivoting.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/


Running Fixed Assets Depreciation causes Microsoft Dynamics GP to "hang"

September 19, 2011

I just completed a full upgrade of Microsoft Dynamics GP from version 9 to version 2010 R2 for a client and they were going through their first month-end closing in the upgraded system. 3 weeks ago, after the upgrade, they reported experiencing an issue running Fixed Assets depreciation from two laptop computers, where apparently, when running depreciation the system would hang. The only option to recover would be to terminate the Dynamics.exe process from Task Manager. Nonetheless, we did not pay much attention to this at the time since the process was completed successfully from another machine, just in time to close the month of August – more on this later.

The client called back on Thursday morning, letting me know they were ready to run Fixed Assets depreciation again, and this time I offered to be onsite to see the problem first hand. So effectively this past Friday morning I drove to their location and stood behind the Sr. Accountant to see the process in action and spot any possible issues while there. The accountant proceeded to log into the company database for which he would run the depreciation, entered his September cutoff date and clicked on the Depreciate button… as luck would have it with some support cases, nothing happened and the process completed successfully. Well, after some chuckles and the typical apologies from the client, I was back in my car on the way home.

Fixed Assets – Depreciation Process Information


Halfway through, I received an email saying that as soon as I left, they logged into another company and were able to reproduce the hung up issue.

Now, I began playing all the typical troubleshooting plots in my head… the problem happens only in one company, the problem can be reproduced by all users, the problem can be reproduced on all machines. Typically, when an issue is constrained to one company, it’s related to some problem with the data or the way that company is configured. Not a bad proposition since I was only dealing with some 300 assets… but I am in my car, remember? So I offered the client to look at the issue when I was back in front of my computer, since I had discarded a user or workstation being the culprits.

Back at home I VPN’d into their system, then RDP’d to the SQL Server. I had the Sr. Accountant log into GP and start the depreciation process again. In troubleshooting the issue, I could see that the depreciation process was being correctly added to the Process Monitor and that the process showed Active, but it did not seem to complete.

Process Monitor

I also ran a SQL Profiler and noticed that the same set of T-SQL instructions would appear to be processed over and over at the database level. This told me the depreciation process was in an endless loop of some kind and something was preventing it from finishing.

SQL Profiler Trace

I then offered to run the process from the server with the ‘sa’ user and noticed that the depreciation was stopping on a particular asset ID (by clicking on the Progress button). This was now promising, because I now had a piece of data to look at.

Fixed Assets Progress window

I queried the Asset Master table and noticed that this particular asset had an acquisition cost of zero. In looking at the Asset Book, I noticed that the Cost Basis was USD $.01 (1 penny). Not sure why this grabbed my attention, but I asked the Sr. Accountant why had they set this asset up this way and he replied that they did it only to record the asset and keep track of its location, but that it had been fully depreciated in the past.

Asset General Information
Asset Book

He also added that the process was working fine in GP 9.0

So I figured I would try something by changing the Depreciation Method to “No Depreciation”. After all, if the asset had an acquisition cost of zero and a Cost Basis of 1 penny, what was there to depreciate? I ran the following statement to change the Depreciation Method to “No Depreciation”:

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.

-- Remove the lock for the book being depreciated
DELETE FROM FA40203;

-- Change the depreciation method
UPDATE FA00200 SET DEPRECIATIONMETHOD = 15 WHERE COSTBASIS <= .01;

I then asked the Sr. Accountant to re-run the process and this time it completed in less than 10 seconds and produced the reports he was expecting.

Since it was not enough to fix this issue, I went out to the Microsoft Dynamics GP Partner Online Technical forum and found a case where a partner reported having the same issue at her client’s site. It seems Microsoft has identified and logged this as a problem report, but no concrete fix date has been given for it. So for now, the above query should do.

Also, you could end up with a cost basis of 1 penny at the end of the useful life of an asset, which would throw the system into an endless loop if you attempt to depreciate such assets once more. If you feel this is your case, the above script should also correct the problem.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/


Microsoft Dynamics GP, the next generation of end-user customization tools?

September 9, 2011

My new article is out on my Community column blog, In my humble opinion with The Dynamics GP Blogster. This time, I take a swipe at some significant improvements that could be added to the customization and integration tools like Modifier, Report Writer, and Integration Manager, just by switching out the programming environment and scripting languages, Visuals Basic for Applications and VBScript for Visual Studio Tools for Applications and PowerShell, respectively.

Far fetched? Not quite, go on and read my reasoning behind this, over at the Community’s website.

Microsoft Dynamics GP, the next generation of end-user customization tools?

For more information on all the programming languages and environments, check the following links:

VBScript
   http://msdn.microsoft.com/en-us/library/cc175562(v=vs.90).aspx

Visual Basic for Applications
   http://support.microsoft.com/kb/163435

Visual Studio Tools for Applications 2.0
   http://msdn.microsoft.com/en-us/library/cc175562(v=vs.90).aspx

Scripting with Windows PowerShell
   http://technet.microsoft.com/en-us/scriptcenter/dd742419

Windows PowerShell Getting Started Guide
   http://msdn.microsoft.com/en-us/library/aa973757(v=vs.85).aspx

Modifier with VBA for Microsoft Dynamics GP 2010 Sample Applications
   http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=9304

Microsoft Dynamics GP 2010 Tools Documentation: Integration Manager
   http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=10955
Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/


Getting the Next Voucher Number for a Payables Transaction Integration

September 7, 2011

At the beginning of the year, I wrote a 2-part series on retrieving document numbers assigned by Microsoft Dynamics GP when a field rule is set to Use Default in Integration Manager, see:

IM – Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager – Part 1

IM – Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager – Part 2

Back then, I was addressing an issue faced by many working with integrations that require you to pass back some value to a source system and the complexities involved in retrieving those values, especially when the field rule is set to Use Default, this is, Microsoft Dynamics GP is assigning the field value, not the integration (via source file or otherwise).

Today, I am looking at a slightly different issue, nonetheless, related.

In this occasion, the client wanted to retrieve the next voucher number before hand for a payables transaction integration and assign it to the voucher number field, but did not want to have to write their own script. In essence, they wanted to leverage whatever mechanism built already in Microsoft Dynamics GP’s business logic to get the next voucher number.


Payables Setup Options – Next Voucher Number field

 Indeed, writing their own code would involve retrieving the field value and incrementing the numeric part of the string. This sometimes can be a nightmare, especially when having to deal with record collisions and users accessing the system while the integration was running.

In doing some additional reading, I realized that eConnect already included this method, so all I had to do was find the SQL Server stored procedure to get the next voucher number. That stored procedure is conveniently named taGetPMNextVoucherNumber. One down, 2 more to go!

The second challenge with eConnect stored procedures is to determine the parameters that need to be passed in, but all eConnect stored procedures are created with encryption, so editing them was not an option. However, I remembered that in SQL Server Management Studio, you have the ability to execute a stored procedure from the Management Studio UI and that this would in effect display a window with the parameters, furthermore detailing data types and whether they are input or output type parameters.

Execute Stored Procedure option (Right-click)

Execute Procedure window

The good thing about this window is you can enter values for your input parameters and click on OK, and SQL Server will automatically generate a template for executing the stored procedure, with variable declarations, types, and all. The construct looks something like this:

USE [TWO]
GO

DECLARE @return_value int,
@O_vCNTRLNUM varchar(21),
@O_iErrorState int

EXEC @return_value = [dbo].[taGetPMNextVoucherNumber]
@O_vCNTRLNUM = @O_vCNTRLNUM OUTPUT,
@I_sCNTRLTYP = 0,
@O_iErrorState = @O_iErrorState OUTPUT

SELECT @O_vCNTRLNUM as N'@O_vCNTRLNUM',
@O_iErrorState as N'@O_iErrorState'

SELECT 'Return Value' = @return_value
GO

This was fantastic, because now I did not have to struggle with understanding what needed to be passed in. It so happens that the control type parameter, @I_sCNTRLTYP, requires a zero to retrieve the next voucher number. In essence, I played with the parameter value and compared to what I was seeing in the GP interface (above), so here are the parameter values accepted for control type:

0 – Next Voucher Number
1 – Next Payment Number
2 – Next Alignment Number

Two down, 1 more to go.

Finally, the rest is putting the scripts together in Integration Manager to call the stored procedure.

As a best practice,  I tend to make the connections to the database persistent throughout the integration. This assures me that connections are only opened once, and closed at the end of the integration, improving the overall performance of the integration and reducing the points of failure. So, as you can imagine, a before document or a field script aren’t the places to open and close connections, as these events occur over and over, based on the number of records being integrated.

I typically open the connection in the Before Integration event script, so this is what this script looks like:

' Created by Mariano Gomez, MVP
' This code is licensed under the Creative Commons
' Attribution-NonCommercial-ShareAlike 2.5 Generic license.
'
' Persisting ADO connection

Const adUseClient = 3
Dim oCn

Set oCn = CreateObject("ADODB.Connection")
With oCn
.ConnectionString = "database=" & GPConnection.GPConnIntercompanyID
.CursorLocation = adUseClient
End With

GPConnection.Open(oCn)
SetVariable "gblConn", oCn

Note that the connection object is stored in a global variable, gblConn, using the SetVariable statement in Integration Manager.

Once we have the connection piece sorted out, now we can focus on adding code to the Voucher Number field script to call the eConnect stored procedure, as follows:

'
' Created by Mariano Gomez, MVP
' This code is licensed under the Creative Commons
' Attribution-NonCommercial-ShareAlike 2.5 Generic license.

' Prepare the SQL statement and retrieve the next voucher number
Const adCmdStoredProc = 4
Const adVarchar = 200
Const adInteger = 3
Const adParamInput = 1
Const adParamOutput = 2
Const PMVoucher = 0

Set oCmd = CreateObject("ADODB.Command")
With oCmd
.ActiveConnection = GetVariable("gblConn")
.CommandType = adCmdStoredProc
.CommandText = "taGetPMNextVoucherNumber" 'the eConnect stored proc

.Parameters.Append .CreateParameter ("@O_vCNTRLNUM", adVarchar, adParamOutput, 25)
.Parameters.Append .CreateParameter ("@I_sCNTRLTYP", adInteger, adParamInput)
.Parameters.Append .CreateParameter ("@O_iErrorState", adInteger, adParamOutput, 4)

oCmd.Parameters("@I_sCNTRLTYP").Value = PMVoucher
.Execute
NextVoucher = oCmd.Parameters("@O_vCNTRLNUM").Value
CurrentField.Value = NextVoucher
'MsgBox NextVoucher
End With

Set oCmd = Nothing

Note how in this occasion, we are using the GetVariable function to retrieve a pointer to the connection object stored in the global variable. We then access the Parameters object to add the different parameters and set the value for the input parameter to the stored procedure.

When this script is executed within the context of the integration, it effectively returns the next voucher number for the transaction being integrated, from which you can proceed to update this information in your source system, if needed.

Note that by using standard Microsoft Dynamics GP business logic, your integration can now be supported if you need to open a support incident. Indeed another method for retrieving a document number for your transaction.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/


Could not load file or assembly ‘Microsoft.ReportViewer.WinForms’ after upgrading to Integration Manager 2010

September 6, 2011

When trying to run an integration in GP2010 (after just upgrading from 9.0), you may receive the following error:

Log Report Failure
Could not load file or assembly ‘Microsoft.ReportViewer.WinForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ or one of its dependencies. The system cannot find the file specified.

The latest versions of Integration Manager now incorporate the ReportViewer Control for displaying the different reports generated by the application.

If you receive the above error, install the ReportViewer Redistributable component from one of the following locations:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=21916 (Visual Studio 2005 components)

or

http://www.microsoft.com/download/en/details.aspx?id=6576 (Visual Studio 2008 components)
The latter will work just fine with Microsoft Dynamics GP 2010 or 2010 R2.

It is also recommended to install the 2007 Office System Driver Data Connectivity Components, which can be downloaded from:
http://www.microsoft.com/download/en/details.aspx?amp;displaylang=en&id=23734

or

Microsoft Access Database Engine 2010 Redistributable, which can be downloaded from:
http://www.microsoft.com/download/en/details.aspx?id=13255

Keep in mind that the above are not a substitute for Microsoft Office and are just intended to facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access 2010 (*.mdb and *.accdb) files and Microsoft Office Excel 2010 (*.xls, *.xlsx, and *.xlsb) files to other data sources such as Microsoft SQL Server. Connectivity to existing text files is also supported. ODBC and OLEDB drivers are installed for application developers to use in developing their applications with connectivity to Office file formats.
Once done, reboot the machine.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/