DynamicsGeek

My Shiny New Blog

2010-08-16T18:40:00.000-07:00

Well, I think it's time to admit that work in Dynamics GP is taking an extended break. With a new position, comes new focus, and sadly GP is not a part of that. However, I have certainly not left the world of technology, but have moved out of the world of debits and credits to pure database development.

In that vein, I have launched a new blog, DataGeek, that will be more data and database development focused. Life is journey into the unknown and I may yet return to GP, it's certainly been fun.

Until then....

Strip Time from SQL Date

2010-04-09T19:45:00.000-07:00

Often, particularly when saving dates to Dynamics GP, we need to strip the time out of a SQL date value. I found a neat little trick for doing that recently in an article on SQLServerCentral.com by Seth Phelabaum.

SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0).

This works by getting the number of days since date 0, which truncates the time, and then adding it back on to date 0.

In his article he has a few other DATEADD/DATEDIFF tricks. It’s well worth a read.

King Me

2010-04-07T19:04:00.001-07:00

It appears that I had a bit of a hiatus from blogging. This was primarily due to a whirlwind of a successful job search, followed by a brief transition. The search itself was quite enlightening as the last time I formally interviewed for a job was 1997 and the last time I actually got a job from the resume/interview process was 1987.

Anyway, during one of my interviews I was asked what I would do as a manager if three of my developers came up with equally good but different solutions to a problem but couldn’t agree on which one to implement. My answer was something to the affect that, although I prefer to be a consensus builder, management by committee is no management at all, the buck would stop with me and I would have to pick one.

My interviewer had another interesting idea. If remember it correctly, he called it his “King Theory”. What he would do is pick one of the developers and make him or her “The King”. It would be his or her job to pick one of the solutions and be responsible for it’s success. They would be able to pick any or any combination of the solutions but the buck would stop with them.

Now the interesting question is what would be the best solution for the developer to choose and what would they? The natural instinct would probably be to pick their own but might it not be better to pick someone else’s or a combination to invest the other team members in the success of the implementation?

I thought it was a pretty interesting solution to the problem. Has anyone else run across this? What do you think of it?

SQL Server Recursive Common Table Expressions…And Other Ways to Generate a Stack Overflow in SQL Server

2010-02-19T12:19:00.000-08:00

OK, so won’t actually generate a stack overflow error in SQL Server, but it makes for a good title. Some time ago I was asked to write a custom deferred revenue integration into GP. There were some quirks to their recognition schedule that prevented them from using the standard deferred revenue module.

For those of you not familiar with deferred revenue, here’s a quick primer. If you pay $24 for a 12 month magazine subscription, the publisher is not allowed to recognize and report to their stockholders the entire $24 when they receive it. They can only recognize revenue as they’ve delivered the service, in this case each magazine, to you. As such, they will recognize and report $2 in revenue each month.

OK, so how do you write a SQL statement that takes a set of rows and creates multiple rows from each individual row. Let’s say that we have a sales table, tblSales, that has an amount to be recognized, a starting month and a number of months to be recognized over as follows:

OrderNumber	Amount	StartingMonth	RecognitionMonths
1	$120.00	3/2010	3
2	$257.00	2/2010	7

To make this easy, we’ll recognize the revenue equally over the number of months. Often revenue needs to be recognized according to the number of days in the month and take into account starting and ending dates that are mid-month. In this case we’ll use a simple model and simply need to return a dataset of 10 rows as follows:

OrderNumber	AmountRecognized	MonthRecognized
1	$40.00	3/2010
1	$40.00	4/2010
1	$40.00	5/2010
2	$36.71	2/2010
2	$36.71	3/2010
2	$36.71	4/2010
2	$36.71	5/2010
2	$36.71	6/2010
2	$36.71	7/2010
2	$36.74	8/2010

So how, in a SQL statement, can we get from 2 rows to 10? As with any problem, I’m sure we could come up with any number of solutions using WHILE loops and table variables, but I’m going to use the opportunity to introduce recursion in SQL.

Recursion is a feature of the T-SQL Common Table Expression. It is essentially a common table expression (CTE) that references itself. Let’s look at the following statement:

WITH DeferredRevenueEntries(
OrderNumber
,AmountRecognized
,MonthRecognized
,RecognitionMonths
,CurrentMonthIndex) AS
(
-- Anchor member
-- The anchor member creates the entry for the first month.
SELECT
OrderNumber
,AmountRecognized = Amount / RecognitionMonths
,MonthRecognized = StartingMonth
,RecognitionMonths
,1 AS CurrentMonthIndex
FROM tblSales
UNION ALL
-- Recursive member
-- Recurse one time for each additional month to be recognized
SELECT
OrderNumber
,AmountRecognized
,MonthRecognized = DATEADD(month, 1, MonthRecognized)
,RecognitionMonths
,CurrentMonthIndex = CurrentMonthIndex + 1
FROM DeferredRevenueEntries
WHERE CurrentMonthIndex < RecognitionMonths
)

SELECT
OrderNumber
,AmountRecognized
,MonthRecognized
FROM DeferredRevenueEntries
ORDER BY
OrderNumber
,MonthRecognized

Here we’re defining a CTE, DeferredRevenueEntries. This is a UNION query. the first part of the union is called the anchor member. This is a SELECT statement against our source table, tblSales. This part of will return the first month rows for each of the sales records, with the amount to be recognized being the total amount divided by the number of months.

The second part is the recursive member. Note that the FROM clause is referencing the CTE itself. This will take the initial two records from the first part of the union, and keep returning them, adding one month each time. To make sure that we only return the number of records matching the number of months to be recognized, we add the field CurrentMonthIndex, increment it each time we recurse and make sure we only return records where the CurrentMonthIndex is less than the number of months to be recognized.

OK, so what happens if we forget to put in the WHERE clause in the recursive member? Will we in fact cause the aforementioned stack overflow? The answer is no. SQL is too smart for that. Instead you will recieve the following error:

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

By default, SQL Server limits you to a maximum recursion level of 100. It is possible to override that limit by using the MAXRECURSION option in the final SELECT statement such that it looks as follows:

SELECT
OrderNumber
,AmountRecognized
,MonthRecognized
FROM DeferredRevenueEntries
ORDER BY
OrderNumber
,MonthRecognized
OPTION (MAXRECURSION 1000)

Now you might say, well what if I put in a MAXRECURSION of 10000000000? Is there some way I can create a stack overflow? Again, SQL will outwit you. The maximum MAXRECURSION limit is 32767.

OK, so there’s one final issue. You’ll note in my results example above that the recognition amount for order 2 was $36.71 except for the last entry where it’s $36.74. This is because never met an accountant who could deal with as much as a single penny off no matter how high the total. To fix that I create the following modified version of the statement:

WITH DeferredRevenueEntries(
OrderNumber
,AmountRecognized
,MonthRecognized
,RecognitionMonths
,CurrentMonthIndex) AS
(
-- Anchor member
-- The anchor member creates the entry for the first month.
SELECT
OrderNumber
,AmountRecognized = ROUND((Amount / RecognitionMonths), 2)
,MonthRecognized = StartingMonth
,RecognitionMonths
,1 AS CurrentMonthIndex
FROM tblSales
UNION ALL
-- Recursive member
-- Recurse one time for each additional month to be recognized
SELECT
OrderNumber
,AmountRecognized
,MonthRecognized = DATEADD(month, 1, MonthRecognized)
,RecognitionMonths
,CurrentMonthIndex = CurrentMonthIndex + 1
FROM DeferredRevenueEntries
WHERE CurrentMonthIndex < (RecognitionMonths - 1)
)

SELECT
OrderNumber
,AmountRecognized
,MonthRecognized
FROM DeferredRevenueEntries

UNION ALL

-- Create last entry manually to adjust for rounding errors.

SELECT
tblSales.OrderNumber
,AmountRecognized = ROUND(tblSales.Amount - DeferredRevenueEntryTotals.TotalAmountRecognized, 2)
,MonthRecognized = DATEADD(month, 1, DeferredRevenueEntryTotals.LastMonthRecognized)
FROM tblSales
INNER JOIN (
SELECT
OrderNumber
,TotalAmountRecognized = SUM(AmountRecognized)
,LastMonthRecognized = MAX(MonthRecognized)
FROM DeferredRevenueEntries
GROUP BY OrderNumber) AS DeferredRevenueEntryTotals
ON tblSales.OrderNumber = DeferredRevenueEntryTotals.OrderNumber

ORDER BY
OrderNumber
,MonthRecognized

You’ll notice a few changes here. The first is that I round the AmountRecognized value to 2 digits. The second is that the CTE is now only going to recurse through the number of months – 1. This is because we’re create the final entry in the last select in the UNION query by taking the total sales amount from tblSales and then subtracting the total recognized prior to the final month, giving us an amount that is adjusted for any rounding issues.

I hope that this has given you a brief look at the power of the recursive CTE and that you are reassured that you will not break SQL Server by using it. There are certainly many other ways to use the, such as traversing hierarchical lists. Go ahead, recurse away!

Wither the SQL Developer? Is T-SQL the Next Assembly Language?

2010-02-03T18:47:00.001-08:00

So today I’m going to veer completely away from GP and talk about SQL in general. The reality is that I’m a data and database geek first and a Dynamics GP geek second; and my (current) database language of choice is T-SQL.

But what is the future of that technology? Is T-SQL a data dinosaur? Years ago I actually programmed in assembly language, the language of the CPU itself (ok so technically that would be machine code but let’s not split hairs). Today, processing power and memory have gotten to the point that you can run a language like PHP or C# so many layers above the CPU as to make it invisible. You have an JIT compiled language running on a CLR on top of an operating system on a virtual processor on a hypervisor on a physical processor. Only the most determined driver developer would even consider assembly.

So what of T-SQL? With Visual Studio 2010, Microsoft will be releasing their latest iteration of the ADO.NET Entity Framework, the latest in a long line of tools to abstract away the data layer and hide the dirty underwear of SQL from the developer. The Entity Framework essentially allows you to design, build and implement your database without ever having to get your hands dirty in SQL Management Studio.

Tools like the Entity Framework, LINQ and even the old standby, ADO.NET datasets allow you to do some pretty cool things like being able to join data and enforce referential integrity across disparate data sources and in-memory data structures. They make it easier to make your applications database agnostic.

To be fair, there has always been significant proportion of the developer community who have lived by the philosophy, for some very valid reasons, that the database should be simply a dumb data store. Even GP was built with that philosophy when it supported multiple back end technologies.

I have tended towards embedding as much logic as possible in the database itself; to use the power of functions, views, referential integrity and constraints; to make make stored procedures the middle tier. I have also not found anything quite like T-SQL for querying and analyzing data. The ability to twist and munge and aggregate your data in a single statement of sub-queries (or better yet, Common Table Expressions)

So one day soon will T-SQL be standing among the assemblers and punch card readers in the dusty closet of computer history? Or will the it continue to have a place in our data toolbox?

Common Table Expressions – Dispensing with the Sub-Query

2010-01-18T18:14:00.000-08:00

The Common Table Expression (CTE) is a construct that was introduced in SQL Server 2005. It allows you to define a SELECT statement outside of your main query and then reference it within the query. It’s a great replacement for sub-queries, which can be difficult to debug and maintain, especially once their nested. You can think of CTEs as SQL views that exist only within the scope of a particular SQL statement.

Let’s start with an query example similar to one we were asked to do recently. Let’s imagine you’re asked to write an inventory analysis report that requires the following columns:

Item ID
Item Description
Item Class
Standard Cost
Quantity On Hand
Count Sold – The total number of items sold.
Average Unit Price – The average unit price that the
Unique Customers Count – The count if individual customers who have purchased the item
Percentage of Customers – The percentage of all customers who have purchased the item

The report will have start and end date range parameters (Quantity On Hand will be the current regardless of the date range.

Item ID, Item Description, Item Class and Standard Cost come from the item master table. Quantity On Hand comes from the item quantity master table. Count Sold, Average Unit Price, Count of Unique Customers and Percentage of Customers come from a combination of the Sales Order and Customer Master tables.

Now I suspect there may be a way to do this without sub-queries using aggregates and CASE statements but that’s a challenge for another day. When solving a problem I like to break it into manageable chunks. In this case I’d looking at breaking this into two pieces:

Returning the core item information, Item ID, Item Description, Standard Cost and Quantity On Hand.
Returning the sales information, Count Sold and Average Unit Price, Unique Customer Count and Percentage of Customers.

Let’s start with the core item info. That query would look as follows:

SELECT
        ItemNumber = ISNULL(ItemMaster.ITEMNMBR, QuantityMaster.ITEMNMBR)
        ,ItemDescription = ItemMaster.ITEMDESC
        ,ItemClass = ItemMaster.ITMCLSCD
        ,StandardCost = ItemMaster.STNDCOST
        ,QuantityOnHand = ISNULL(QuantityMaster.QTYONHND, 0)
    FROM IV00101 AS ItemMaster
        FULL JOIN IV00102 AS QuantityMaster
            ON ItemMaster.ITEMNMBR = QuantityMaster.ITEMNMBR
    WHERE ISNULL(QuantityMaster.RCRDTYPE, 1) = 1

Here we’re joining the item master (IV00101) with the item quantity master (IV00102). A couple of notes:

We’re using a FULL JOIN to ensure we pick up all items. I have found examples where there are records in the item master or quantity master but not a corresponding record in the other.
We’re restricting the quantity master records to a record type (RCRDTYPE) of 1, which only returns quantities that apply to all sites.

Next we’ll look at the statistics. The query would look as follows:

SELECT
        ItemNumber = SOPLineItems.ITEMNMBR
        ,CountSold = SUM(SOPLineItems.QUANTITY)
        ,AverageUnitPrice =
            CASE
                WHEN SUM(SOPLineItems.QUANTITY) = 0 THEN 0.00
                ELSE SUM(SOPLineItems.QUANTITY * SOPLineItems.UNITPRCE) / SUM(SOPLineItems.QUANTITY)
            END
        ,CustomerCount = COUNT(DISTINCT CUSTNMBR)
        ,CustomerPercentage = CAST(COUNT(DISTINCT CUSTNMBR) AS float) /
            CAST ((SELECT COUNT(*) FROM RM00101) AS float)
    FROM SOP30300 AS SOPLineItems
        INNER JOIN SOP30200 AS SOPHeaders
            ON SOPLineItems.SOPTYPE = SOPHeaders.SOPTYPE
                AND SOPLineItems.SOPNUMBE = SOPHeaders.SOPNUMBE
    WHERE SOPHeaders.SOPTYPE = 3
        AND SOPHeaders.GLPOSTDT BETWEEN @StartDate AND @EndDate
    GROUP BY SOPLineItems.ITEMNMBR

Here we’re pulling the data from SOP line items and SOP headers with a total count from the customer master. A couple of notes:

We’re using a CASE statement in the Average Unit Price to ensure we don’t get a divide by zero error.
We’re filtering the rows on the SOP header GL posting date (GLPOSTDT).
We’re getting the item customer count using the COUNT(DISTINCT ) construct.
While getting the customer percentage we’re explicitly casting the counts as floats so that it doesn’t perform integer division and only return 0 and 1.

So traditionally, we would combine these together as two sub-queries as below:

SELECT
        ItemNumber = Items.ItemNumber
        ,ItemClass = Items.ItemClass
        ,ItemDescription = Items.ItemDescription
        ,StandardCost = Items.StandardCost
        ,QuantityOnHand = Items.QuantityOnHand
        ,CountSold = ISNULL(ItemStatistics.CountSold, 0)
        ,AverageUnitPrice = ISNULL(ItemStatistics.AverageUnitPrice, 0)
        ,CustomerCount = ISNULL(ItemStatistics.CustomerCount, 0)
        ,CustomerPercentage = ISNULL(ItemStatistics.CustomerPercentage, 0)
    FROM (
            SELECT
                    ItemNumber = ISNULL(ItemMaster.ITEMNMBR, QuantityMaster.ITEMNMBR)
                    ,ItemDescription = ItemMaster.ITEMDESC
                    ,ItemClass = ItemMaster.ITMCLSCD
                    ,StandardCost = ItemMaster.STNDCOST
                    ,QuantityOnHand = ISNULL(QuantityMaster.QTYONHND, 0)
                FROM IV00101 AS ItemMaster
                    FULL JOIN IV00102 AS QuantityMaster
                        ON ItemMaster.ITEMNMBR = QuantityMaster.ITEMNMBR
                WHERE ISNULL(QuantityMaster.RCRDTYPE, 1) = 1) AS Items
        LEFT JOIN (
                SELECT
                        ItemNumber = SOPLineItems.ITEMNMBR
                        ,CountSold = SUM(SOPLineItems.QUANTITY)
                        ,AverageUnitPrice =
                            CASE
                                WHEN SUM(SOPLineItems.QUANTITY) = 0 THEN 0.00
                                ELSE SUM(SOPLineItems.QUANTITY * SOPLineItems.UNITPRCE) / SUM(SOPLineItems.QUANTITY)
                            END
                        ,CustomerCount = COUNT(DISTINCT CUSTNMBR)
                        ,CustomerPercentage = CAST(COUNT(DISTINCT CUSTNMBR) AS float) /
                            CAST ((SELECT COUNT(*) FROM RM00101) AS float)
                    FROM SOP30300 AS SOPLineItems
                        INNER JOIN SOP30200 AS SOPHeaders
                            ON SOPLineItems.SOPTYPE = SOPHeaders.SOPTYPE
                                AND SOPLineItems.SOPNUMBE = SOPHeaders.SOPNUMBE
                    WHERE SOPHeaders.SOPTYPE = 3
                        AND SOPHeaders.GLPOSTDT BETWEEN @StartDate AND @EndDate
                    GROUP BY SOPLineItems.ITEMNMBR) AS ItemStatistics
                        ON Items.ItemNumber = ItemStatistics.ItemNumber

Although this works, it’s starting to get a little difficult to read. If there were additional sub-queries, particularly nested ones, it would be much harder to follow and debug. If we’re using CTEs we would create the query below:

-- Items common table expression returns item list with quantities
-- for all sites.
WITH Items
AS (
    SELECT
            ItemNumber = ISNULL(ItemMaster.ITEMNMBR, QuantityMaster.ITEMNMBR)
            ,ItemDescription = ItemMaster.ITEMDESC
            ,ItemClass = ItemMaster.ITMCLSCD
            ,StandardCost = ItemMaster.STNDCOST
            ,QuantityOnHand = ISNULL(QuantityMaster.QTYONHND, 0)
        FROM IV00101 AS ItemMaster
            FULL JOIN IV00102 AS QuantityMaster
                ON ItemMaster.ITEMNMBR = QuantityMaster.ITEMNMBR
        WHERE ISNULL(QuantityMaster.RCRDTYPE, 1) = 1
),

-- Item statistics common table expression returns the relevant
-- statistics for each item.
ItemStatistics
AS (
    SELECT
            ItemNumber = SOPLineItems.ITEMNMBR
            ,CountSold = SUM(SOPLineItems.QUANTITY)
            ,AverageUnitPrice =
                CASE
                    WHEN SUM(SOPLineItems.QUANTITY) = 0 THEN 0.00
                    ELSE SUM(SOPLineItems.QUANTITY * SOPLineItems.UNITPRCE) / SUM(SOPLineItems.QUANTITY)
                END
            ,CustomerCount = COUNT(DISTINCT CUSTNMBR)
            ,CustomerPercentage = CAST(COUNT(DISTINCT CUSTNMBR) AS float) /
                CAST ((SELECT COUNT(*) FROM RM00101) AS float)
        FROM SOP30300 AS SOPLineItems
            INNER JOIN SOP30200 AS SOPHeaders
                ON SOPLineItems.SOPTYPE = SOPHeaders.SOPTYPE
                    AND SOPLineItems.SOPNUMBE = SOPHeaders.SOPNUMBE
        WHERE SOPHeaders.SOPTYPE = 3
            AND SOPHeaders.GLPOSTDT BETWEEN @StartDate AND @EndDate
        GROUP BY SOPLineItems.ITEMNMBR
)

-- Final query joins the two CTEs together.
SELECT
        ItemNumber = Items.ItemNumber
        ,ItemClass = Items.ItemClass
        ,ItemDescription = Items.ItemDescription
        ,StandardCost = Items.StandardCost
        ,QuantityOnHand = Items.QuantityOnHand
        ,CountSold = ISNULL(ItemStatistics.CountSold, 0)
        ,AverageUnitPrice = ISNULL(ItemStatistics.AverageUnitPrice, 0)
        ,CustomerCount = ISNULL(ItemStatistics.CustomerCount, 0)
        ,CustomerPercentage = ISNULL(ItemStatistics.CustomerPercentage, 0)
    FROM Items
        LEFT JOIN ItemStatistics
            ON Items.ItemNumber = ItemStatistics.ItemNumber

In the query above we have two common table expressions, Items and ItemStatistics. We declare the first CTE using the WITH clause. Subsequent CTEs are separated with a comma. Note that all CTEs must be declared at the beginning of the query. The final query simply joins the two CTEs together

The biggest advantage to CTEs is their readability, manageability and the ease of troubleshooting. It’s much easier to break the query down and troubleshoot its individual components than to do the same with nested sub-queries. Other things to note about CTEs:

A CTE can reference other CTEs defined above it.
A query can join to a CTE multiple times. i.e., wherever you might use the same sub-query multiple times you can now define it once in a CTE and simply reference the CTE multiple times.
You can reference parameters and variables in a CTE and thus optimize your queries by filtering out records prior to joining them in your main query.
If you have any statements above a CTE, such as DECLARE or SET statements in a stored procedure, you must terminate those prior statements using a semicolon.

So let us bid farewell to the much vaunted and occasionally overused sub-query and hello to our new friend, the Common Table Expression.

Next up, the Recursive Common Table Expression.

Excellent Consulting Tips Article

2010-01-14T19:00:00.000-08:00

I’m veering off the technical path here, but I came across this great article on the things NOT to do as a consultant.

10 things you should never do on a consulting job (http://blogs.techrepublic.com.com/10things/?p=1290&tag=nl.e550)

My Adventures using eConnect Stored Procedures – Part 2, Error Handling

2010-01-13T18:52:00.001-08:00

I’m a big believer in doing things in a consistent fashion, be it formatting SQL code or loading the dishwasher. To that end, I like to handle errors consistently in my applications and that means using C#’s standard method using Try/Catch.

The eConnect stored procedures don’t raise an error if one occurs but rather, as mentioned in Part 1, pass back error information in two output parameters, @O_iErrorState, an integer and @oErrString a 255 character varchar.

Now unfortunately, neither of these variables on their own provide much interesting information. @O_iErrorState returns a single integer representing an error. @oErrString sends a list of integers, separated by spaces, if more than one error is occurs. So where do I get the text that corresponds to the numbers?

The answer is in the taErrorCode table in the DYNAMICS database. The error codes returned by the eConnect stored procedures correspond to the values in the ErrorCode column. Using that you can return the stored procedure the error code relates to from the SourceProc column and the error description from the ErrorDesc column.

So that solves the first part of my problem, which is how to retrieve readable text from the error codes, but how do I then go and throw an exception with all of the error information instead of relying on the output parameters? The first step is to consolidate the information for one or more errors into a single string. For this I created a user defined scalar function that takes an error code list, as returned by the @oErrString output parameter and returns a single string as follows (best read by cutting and pasting into Management Studio):

CREATE FUNCTION [dbo].[fnvGeteConnectErrorMessage]
(
    @eConnectErrorString nvarchar(255)
)
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @ErrorString nvarchar(255)
    DECLARE @ErrorMessage nvarchar(max)
    DECLARE @DelimiterIndex int
    DECLARE @CurrentErrorCode nvarchar(10)
    DECLARE @CRLF nvarchar(2)

--Initialize variables
    SET @CRLF = CHAR(13) + CHAR(10)
    SET @ErrorString = @eConnectErrorString
    SET @ErrorMessage = 'The following error(s) were returned:' + @CRLF + @CRLF
    SET @ErrorString = LTRIM(RTRIM(@ErrorString))

--If no errors then return empty string
    IF LEN(@ErrorString) = 0
    BEGIN
        RETURN N''
    END

--Find first delimiter
    SET @DelimiterIndex = CHARINDEX(' ', @ErrorString, 0)
    IF @DelimiterIndex = 0
    BEGIN
--If no delimiter, return entire string
        SET @CurrentErrorCode = @ErrorString
    END
    ELSE
    BEGIN
--If delimiter found, set current error code to first error code in string
        SET @CurrentErrorCode = SUBSTRING(@ErrorString, 1, (@DelimiterIndex - 1))
    END

--Loop until there are no error codes remaining
    WHILE LEN(@CurrentErrorCode) > 0
    BEGIN
--Return error stored procedure and message and append to error message to be returned
        SELECT
                @ErrorMessage = @ErrorMessage + 'Source procedure - ' + ISNULL(SourceProc, '')
                    + @CRLF + 'Error - ' + ISNULL(ErrorDesc, '') + @CRLF + @CRLF
            FROM DYNAMICS.dbo.taErrorCode
            WHERE ErrorCode = @CurrentErrorCode

--Remove current error code from string and get next error code
        SET @ErrorString = RTRIM(LTRIM(SUBSTRING(@ErrorString, (LEN(@CurrentErrorCode) + 1), (LEN(@ErrorString) - LEN(@CurrentErrorCode)))))
        SET @DelimiterIndex = CHARINDEX(' ', @ErrorString, 0)
        IF @DelimiterIndex = 0
        BEGIN
            SET @CurrentErrorCode = @ErrorString
        END
        ELSE
        BEGIN
            SET @CurrentErrorCode = SUBSTRING(@ErrorString, 1, (@DelimiterIndex - 1))
        END
    END

    -- Return the result of the function
    RETURN @ErrorMessage
END

What this function is ultimately doing is taking the space delimited list of error codes, looping through them, retrieving the source procedure and error description, appending it to the overall error message and then returning the whole thing.

Now that I can return a consolidated error string, how do I throw an error? I handled this by creating a wrapper stored procedure. This not only allows me to handle errors the way I want, but also have a stored procedure with only the parameters I need in my code, with readable names. Below is the wrapper stored procedure I created to insert a GL transaction header:

CREATE PROCEDURE [dbo].[spvGLInsertGPGLHeader]
    @BatchNumber char(15)
    ,@JournalEntry int
    ,@JournalReference char(30)
    ,@TransactionDate datetime
AS

    DECLARE @RC int
    DECLARE @ErrorState int
    DECLARE @ErrorString varchar(255)
    DECLARE @ErrorMessage nvarchar(max)

--Execute the taGLTransactionHeaderInsert eConnect stored
--procedure with passed parameters and required defaults.
    EXECUTE @RC = [PHI2].[dbo].[taGLTransactionHeaderInsert]
        @I_vBACHNUMB = @BatchNumber
        ,@I_vJRNENTRY = @JournalEntry
        ,@I_vREFRENCE = @JournalReference
        ,@I_vTRXDATE = @TransactionDate
        ,@I_vTRXTYPE =0
        ,@I_vSERIES = 2
        ,@I_vRequesterTrx = 1
        ,@O_iErrorState = @ErrorState OUTPUT
        ,@oErrString = @ErrorString OUTPUT

--If any errors are returned, get the consolidated error message
--and raise a SQL error.
    IF LEN(@ErrorString) <> 0
    BEGIN
        SET @ErrorMessage = dbo.fnvGeteConnectErrorMessage(@ErrorString)
        RAISERROR (@ErrorMessage, 18, 1)

        RETURN
    END

As you can see, we’re using TSQL’s RAISERROR (doesn’t the missing “E” in the middle bug you a little) to throw an error back to your calling code. Also note that we have a much cleaner stored procedure to reference from our calling application.

Next up, calling this from an ADO.NET table adaptor in a Dynamics GP .NET add-in.

My Adventures using eConnect Stored Procedures – Part 1

2010-01-12T20:23:00.000-08:00

OK. So over a month ago I talked about doing an integration using the eConnect stored procedures directly instead of the COM+ or Message Queue interfaces. Well, I’m happy to report that the first one is completed. It’s a GL integration with AA (Analytical Accounting) transactions from an Excel file. Each row represents a single journal entry distribution and has two columns with AA dimensions. Each AA column represents a different dimension with a code for each that is allocated 100% of the distribution amount.

As I noted in the previous post, each eConnect XML node represents a single stored procedure of the same name. In this integration we’re using the following nodes:

taGLTransactionHeaderInsert
taGLTransactionLineInsert
taAnalyticsDistribution

Each of these stored procedures has a set of parameters that matches the parameters of the XML node and follows the same rules in terms of defaults and whether it’s required or not. In addition there are two output parameters at the end of the parameters list, @O_iErrorState, an integer and @oErrString a 255 character varchar. These parameters return information an any errors that occurred in processing.

In addition to the stored procedures that represent the eConnect XML nodes, there was an additional stored procedure, taGetNextJournalEntry, I used to return the next journal entry number. This stored procedure has the following three parameters:

@I_vInc_Dec – An integer that is either 1 or 0 to indicate whether to increment or decrement the number (in case you’re rolling back a transaction)
@O_vJournalEntryNumber – A 13 character output parameter that returns the next journal entry number.
@O_iErrorState – An integer output parameter that returns an error number or 0 if no errors occurred.

A lesson I learned was the importance of running the stored procedures in the correct order. My gut told me to create the header first, then the transaction lines and AA distributions. My gut was wrong.

Actually after creating the header and then the transaction lines I was mostly OK. The transactions did get created with the proper distributions. The only clue that something was wrong was that the batch transaction count and total was incorrect. However, after adding the AA logic I got no AA distributions.

Integrating AA has never failed to frustrate me. A couple of hours of foul language and troubleshooting later I ended up writing a little test app that wrote added the transactions through the COM+ interface. After running a SQL trace in SQL Server Profiler, I discovered that the transaction header insert stored procedure needs to be run last, not first. Once the calls were rearranged all was happy!

What did I learn? When you avoid the COM+/XML parsing/DTC overhead it’s incredibly fast. That you should have a test application ready to create the transactions using the COM+ interface to determine the correct stored procedure order. That you’ll need to handle transactions in your own code instead of relying on the COM+ object and DTC. And that it’s really not that hard to do and a good option if you have direct access to the database.

In future posts I’ll talk about some of the other things I did in this integration including handling the errors returned from the eConnect stored procedures and connecting to the database using the end-user’s GP credentials.

SSRS Expressions Tips & Tricks

2010-01-12T13:10:00.001-08:00

Wow! Time flies when you’re having fun. Anyway, between taking some time off for the holidays, preparing to take time off for the holidays and digging out of the email from taking time off for the holidays, I guess I’ve neglected the blog a bit.

This will just be a short post. A colleague of mine, Liz Pfahler, passed this link along to me. It’s a good comprehensive list of tips and tricks for SSRS expressions. Everything from how to set alternating row colors, to returning the first or last day of the month.

There’s a few things I’ve used in the past but there’s definitely some new ones and it’s great to have them all in one place.

Check it out.

http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/67660/

eConnect Message Queue vs. COM+ vs. Stored Procedures

2009-11-19T12:26:00.001-08:00

A few months ago I wrote about how, for various reasons, I was fed up with the Dynamics GP Web Services and was moving towards writing my own web services working against eConnect. (Click here for that article)

Now the reality is that most of the integrations I write are small Windows Forms or intranet applications run within the local LAN and can directly access the SQL server. I also wondered about writing GP Add-Ins using the Visual Studio Tools for Dynamics GP.

In either of these scenarios, not only do I not see the sense of using the Dynamics GP Web Services, but of even using the two most documented methods of accessing eConnect using the COM+ or Message Queue interfaces. I’ve started looking at using the base stored procedures directly in the database.

Since each XML node maps one to one to a stored procedure of the same name, I’m able to just use the schema documentation to see what the various parameters mean. I haven’t put anything into production yet with this, but I’ll let you know how it goes.

Anyone else use eConnect this way? Let me know.

Accessing the Dynamics GP Configuration File (Dynamics.exe.config) from a Visual Studio Tools for Dynamics GP Add-In – Part 3

2009-11-01T17:37:00.001-08:00

This is Part 3 of a three part article on working with the Dynamics.exe.config .NET configuration file. with the following articles:

Part 1 – Creating a configuration handler for a custom configuration section.
Part 2 – Testing the configuration handler.
Part 3 – Using the configuration handler in a Visual Studio Tools for Dynamics GP Add-In

[Click here for the Visual Studio solution used in these articles]

So finally, in this part, we’ll put it all together and demonstrate how to access the Dynamics.exe.config configuration from a Visual Studio Tools for Dynamics GP add-in. For this, we’ll create a simple “Hello Configuration” add-in. We’ll attach it to the Additional menu of the SOP Batch Entry form as we might a SOP integration.

There’s really only two parts of this add-in:

Initialization where we verify the existence of the configuration section and add it if it’s missing and add a menu item and associated event handler to the Additional menu of the SOP Batch Entry form.
Event handler, that in this case simply displays the “Hello Configuration” caption, as stored in the configuration section, and the hypothetical import file name from the same section.

Thus the code consists of a single class file in the HelloConfigurationAddIn project of the solution linked to above. The project was created using the Microsoft Dynamics GP Add-In template. A reference is added to the GPAddInConfiguration project. The entire code for the add-in is as follows:

using System.Configuration;
using GPAddInConfiguration;

namespace HelloConfigurationAddIn
{
    public class GPAddIn : IDexterityAddIn
    {
        public void Initialize()
        {
            // Check to see if the section exists and if not create it.
            this.VerifyConfigurationSection();

            // Add to Additional menu for SOP batch form.
            Dynamics.Forms.SopBatchEntry.AddMenuHandler(HelloConfigurationEvent,"Hello Configuration");
        }

        // Verifies the existence of the section and adds it if missing.
        void VerifyConfigurationSection()
        {
            // If section doesn't exist create it.
            if (ConfigurationManager.GetSection("GPAddIn") == null)
            {
                // Get the current configuration file for writing.
                Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);

                // Create the new section.
                GPAddInSectionHandler section = new GPAddInSectionHandler();
                config.Sections.Add("GPAddIn", section);
                section.SectionInformation.ForceSave = true;

                // Save the section
                config.Save(ConfigurationSaveMode.Full);

                ConfigurationManager.RefreshSection("GPAddIn");
            }
        }

        // Script to handle menu entry callbacks
        void HelloConfigurationEvent(object sender, EventArgs e)
        {
            GPAddInSectionHandler section = (GPAddInSectionHandler)ConfigurationManager.GetSection("GPAddIn");
            MessageBox.Show(section.HelloConfigurationCaption + "\n\nImport File: " + section.ImportFileName, "Hello Configuration");
        }
    }
}

The Initialize method calls the VerifyConfigurationSection function that checks for the existence of the section in the Dynamics.exe.config configuration, and if missing, adds it with the default values. Additionally, it adds a menu handler for the Additional menu of the SOP Batch Entry form, passing the HelloConfigurationEvent function as the event handler.

The HelloConfigurationEvent simply opens the GPAddIn section of the configuration and displays the HelloConfigurationCaption and ImportFileName values in a message box.

To properly deploy the add-in you need to copy the HelloConfigurationAddIn assembly DLL to the GP AddIns folder as you would any add-in. However, you also need to deploy the GPAddInConfiguration assembly to the GP folder that contains the Dynamics.exe.config file. Configuration handler assemblies must reside in the same folder as the host application.

So give it a whirl and see how changes to the values in the configuration file show up in the message box. Note that in this case, changes don’t show up until you restart the application.

I hope these posts have inspired you to make use of the power of the configuration file in your Dynamics GP add-ins. There’s certainly much more you can do once you dig into them. Let me know what you find.

Accessing the Dynamics GP Configuration File (Dynamics.exe.config) from a Visual Studio Tools for Dynamics GP Add-In – Part 2

2009-11-01T16:16:00.001-08:00

This is Part 2 of a three part article on working with the Dynamics.exe.config .NET configuration file. with the following articles:

Part 1 – Creating a configuration handler for a custom configuration section.
Part 2 – Testing the configuration handler.
Part 3 – Using the configuration handler in a Visual Studio Tools for Dynamics GP Add-In.

[Click here for the Visual Studio solution used in these articles]

Now that we’ve created a configuration section handler, let’s create a little console application to test it out and see some of the things we can do with it. Note that we’re working with the section handler independently of Dynamics GP. Section handlers are not tied to a particular applications configuration file, but can be referenced in any application.

In this test we’ll do the following:

Attempt to get the section, and if it doesn’t exist create it.
Attempt to update the configuration settings on a section object retrieved using the ConfigurationManager objects GetSection object.
Show how to update and save changes to the section object.

This application is the ConfigurationSectionTester project in the solution. It is a simple console application and references the System.configuration and the GPAddInConfiguration project assembly. Note that the configuration assembly must be deployed in the same folder as the calling application and it’s corresponding configuration file.

I’ve created static functions to perform each of the tasks listed above and call them from the Main function so that it looks as follows:

static void Main(string[] args)
{
    verifyAndGetSection();
    attemptUpdateOfReadonlySection();
    updateSection();

    Console.WriteLine();
    Console.WriteLine("Hit Enter to continue...");
    Console.ReadLine();
}

So let’s look at each of the functions individually. First the verifyAndGetSection function.

private static void verifyAndGetSection()
{
    GPAddInSectionHandler section;

    Console.WriteLine();
    Console.WriteLine("----------");
    Console.WriteLine("Getting or creating section");

    // Attempt to get section from the static ConfigurationManager object.
    section = (GPAddInSectionHandler)ConfigurationManager.GetSection("GPAddIn");

    // If config section doesn't exist, create the section entry
    // in <configSections> and the
    // related target section in <configuration>.
    if (section == null)
    {
        // Open current configuration file for writing.

        Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
        section = new GPAddInSectionHandler();
        config.Sections.Add("GPAddIn", section);
        section.SectionInformation.ForceSave = true;
        config.Save(ConfigurationSaveMode.Full);
        ConfigurationManager.RefreshSection("GPAddIn");

        Console.WriteLine("New section, {0}, created", section.SectionInformation.Name);
    }
    else
    {
        Console.WriteLine("Section {0} opened.", section.SectionInformation.Name);
    }

    Console.WriteLine();

    Console.WriteLine("Hello Configuration Caption - {0}", section.HelloConfigurationCaption);
    Console.WriteLine("Import File Name - {0}", section.ImportFileName);
}

In this function, we first attempt to retrieve the custom section by calling the ConfigurationManager object’s static GetSection method, passing it the section’s name. If successful, this returns a read-only copy of the section. If unsuccessful, it returns null (or Nothing in VB.NET).

If the returned section is null then we open a writeable copy of the configuration using the static OpenExeConfiguration method of the ConfigurationManager object. Note that the parameter passed to this method indicates whether we’re getting the application or user specific configuration file.

Once we have the configuration object, we can add the new section to the Sections collection, mark it to force a save, and then save the configuration. Note that all of this code works even if a configuration file doesn’t even exist. Saving the configuration will create a new configuration file if one doesn’t already exist.

Calling the RefreshSection method forces the new section to be loaded when it is next requested within the application. Otherwise attempting to load the section using the GetSection method prior to restarting the application will return a null object.

In the second function, attemptUpdateOfReadonlySection, we’ll show what happens if you attempt to update a read-only section object.

private static void attemptUpdateOfReadonlySection()
{
    Console.WriteLine();
    Console.WriteLine("----------");
    Console.WriteLine("Attempting to update a readonly section.");

    // Getting a section using GetSection returns a read-only section.
    GPAddInSectionHandler section = (GPAddInSectionHandler)ConfigurationManager.GetSection("GPAddIn");

    try
    {
        section.ImportFileName = "NewFileName.txt";
    }
    catch (Exception ex)
    {
        Console.WriteLine("Error setting section attribute - {0}", ex.Message);
    }
}

Here, we again attempt to retrieve the section using the ConfigurationManager objects static GetSection method. GetSection returns a read-only copy of the section. If you attempt to set any of the properties it will throw an error. Note that this is true whether or not you intend to ultimately save the update to disk.

In the final function, updateSection, we show how to successfully update a sections properties and then save it to disk. This is very similar to how we created a new section in the verifyAndGetSection function.

private static void updateSection()
{
    // To get a writeable section you open the configuration using OpenExeConfiguration
    // and the return the section from the Sections collection.
    Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
    GPAddInSectionHandler section = (GPAddInSectionHandler)config.Sections["GPAddIn"];

    Console.WriteLine();
    Console.WriteLine("----------");
    Console.WriteLine("Updating a section.");

    section.HelloConfigurationCaption = section.HelloConfigurationCaption + " - Modified";
    section.ImportFileName = "NewFileName.txt";

    // Set ForceSave to true to ensure the section is saved even if not updated
    section.SectionInformation.ForceSave = true;
    config.Save(ConfigurationSaveMode.Full);

    Console.WriteLine();

    Console.WriteLine("Hello Configuration Caption - {0}", section.HelloConfigurationCaption);
    Console.WriteLine("Import File Name - {0}", section.ImportFileName);
}

Here we open the configuration using the static OpenExeConfiguration method of the ConfigurationManager object, again, specifying whether we want the application or user specific version of the configuration. We then retrieve the section from the Sections collection rather than using the GetSection method. This gives us a writeable version of the section. Once we’ve updated the section we then use the Save method of its parent configuration to save it to disk.

When testing this, run it from the command line rather than from the Visual Studio IDE. When running it from the IDE, the initial state of the configuration file (i.e. no file), is restored each time.

The first time you run this, make sure you should start with no configuration file and your command window should look as follows:

Note that first function is creating a new section and setting the values to their defaults. When you run it a second time you should see the following:

Here you’ll note that the section is being opened and showing the section values updated from the defaults in the previous run. Also note the “The configuration is read only” error message when attempting to update the section returned by the GetSection method.

Hopefully this has given you some of the basic ways of using successfully using your configuration handler. In the Part 3, we’ll tie it all together in a Dynamics GP Add-In.

Accessing the Dynamics GP Configuration File (Dynamics.exe.config) from a Visual Studio Tools for Dynamics GP Add-In – Part 1

2009-10-31T17:31:00.000-07:00

This is Part 1 of a three part article on working with the Dynamics.exe.config .NET configuration file. with the following articles:

Part 1 – Creating a configuration handler for a custom configuration section.
Part 2 – Testing the configuration handler.
Part 3 – Using the configuration handler in a Visual Studio Tools for Dynamics GP Add-In

[Click here for the Visual Studio solution used in these articles]

So one of the great things I love about .NET is the configuration file. A more flexible file format than the old .ini file and not the registry (need I say more?). That being said, I’ve tended to rely on the simple AppSettings mechanism making it fairly easy to create and manage your settings in C# or VB.NET projects.

I love using them for things like default import file paths, remembering user interface settings, connection strings, URLs and such. Now I know there are other ways to store settings, such as in a database table, but sometimes the configuration file is the best tool for the job.

However, .NET DLLs, for a number of reasons I won’t get into here, don’t inherently have their own configuration files. They must access their parent application’s file. Although you can, in fact, use AppSettings, as long as you modify the host application’s (in this case Dynamics’) configuration file, there are some great reasons to go custom. And it’s easier than you may think.

Since a Dynamics GP Add-In is a .NET DLL, the configuration file we have access to is the Dynamics.exe.config file. So how do we use it to store our own settings that can be used by our Add-Ins?

Step one in this process is to understand the structure and handling of configuration files.

Configuration files are broken into sections (there are also section groups but we’ll ignore those for this project). A section is simply an XML node off the top level configuration node. There’s really no restrictions on its structure as long as it’s valid XML. In GP 10 there is a single section, shell.

Each section must have a section definition that includes the assembly that contains the configuration handler object that reads that section. Let’s take a look at the Dynamics.exe.config file.

[Click here to download the default Dynamics.exe.config configuration file]

Notice that the first node in the configuration file is the configurationsections node. This is where sections are defined. Notice that here, Dynamics is defining the shell section, which you will note is the next node after the configuration sections node. The section definition consists of the name, which is the name of the top level node for that section, followed by the type, which defines the assembly and handler that reads that section.

So to add our own section to the configuration file, we simply need to add a section definition and then the corresponding section…oh yeah, and write a configuration section handler assembly.

In this example, we’re going to create a fairly. It will have a single node, GPAddIn and two properties, HelloConfigurationCaption for our message box, and ImportFileName for our hypothetical file integration. The properties are going to be implemented as attributes of the GPAddIn node, leaving us without the need to handle sub-nodes.

When all is said and done our modified Dynamics.exe.config file will look like this:

[Click here to download the configuration file with the custom section]

Note that we’ve added a new section definition for the GPAddIn section and the section itself with our two settings as attributes. In the type attribute of the section definition, we have “GPAddInConfiguriation.GPAddInSectionHandler”, the actual class that reads the GPAddIn section, and “GPAddInConfiguration”, the name of the assembly itself.

So now that we can read the configuration file and define a custom section, how do we write the code to read it?

So a couple of basics. The section handler class itself is a class that derives from the System.Configuration.ConfigurationSection class. The actual assembly must be a .NET DLL and must be deployed to the same folder as the parent executable. Note that this means that it is in the Microsoft Dynamics\GP folder, not the AddIns folder that the add-in itself will be deployed to.

So let’s write some code! First things first. The source code for this solution can be downloaded from here.

We’re going to start with the configuration handler DLL project, GPAddInConfiguration. This project is a Class Library project and consists of a single C# class file containing a single class, GPAddInSectionHandler, derived from System.Configuration.ConfigurationSection. You will need to reference the System.Configuration assembly in the project.

The beginning of your class file will look as follows:

using System;
using System.Configuration;

namespace GPAddInConfiguration
{
public class GPAddInSectionHandler : ConfigurationSection
{

Next we’ll define the constructors:

// CustomSection constructor.
public GPAddInSectionHandler()
{
}

public GPAddInSectionHandler(string helloConfigurationCaption, string importFileName)
{
HelloConfigurationCaption = helloConfigurationCaption;
ImportFileName = importFileName;
}

After that we define the configuration attributes. We’re using the declarative method so we don’t need to worry about the configuration property collection or private variables:

[ConfigurationProperty("HelloConfigurationCaption", DefaultValue = "Hello Dynamics GP Configuration", IsRequired = true)]
[StringValidator(MinLength = 1, MaxLength = 60)]
public string HelloConfigurationCaption
{
get
{
return (string)this["HelloConfigurationCaption"];
}
set
{
this["HelloConfigurationCaption"] = value;
}
}

[ConfigurationProperty("ImportFileName", DefaultValue = "importfile.txt", IsRequired = true)]
[StringValidator(InvalidCharacters = " ~!@#$%^&*()[]{}/;'\"\\",
MinLength = 1, MaxLength = 255)]
public string ImportFileName
{
get
{
return (string)this["ImportFileName"];
}
set
{
this["ImportFileName"] = value;
}
}

Here we’ve defined the two configuration properties, HelloConfigurationCaption and ImportFileName. Using the ConfigurationProperty attribute we’ve defined their type, default value and whether or not their required without having to write any of the handling code.

Additionally, using the StringValidator attribute we can define what constitutes a valid value, such as maximum length, and in the case of the ImportFileName, invalid characters.

And that’s it. Configuration handler completed. Obviously there’s a lot more that you can do such as child elements and even complete custom handling of the section XML, but for a basic replacement of AppSettings, this works quite nicely, and I would argue, makes for a more readable configuration file.

Hopefully this has demystified the configuration file and will allow you to move beyond the simple AppSettings object. In Part 2, well take our section handler and run it through its paces in a test application.

Error When Running Dynamics GP SQL Reporting Services Trial Balance Summary or Detail Reports

2009-10-30T01:03:00.001-07:00

This post deals with the following error message:

The EXECUTE permission was denied on the object 'smGetMsgString', database 'DYNAMICS', schema 'dbo'.

We recently deployed the Dynamics GP SQL reports to a client. I think this must be the first client who actually went through all of them because we came across a bit of a bug in the security in the Trial Balance Summary and Detail Reports.

Now admittedly, we diverted somewhat from the standard install by modifying the shared data sources to use a fixed SQL login rather than integrated security but we dutifully added that login to the rpt_poweruser role in each of the company databases and the DYNAMICS database.

When the client went to run the Trial Balance Detail report they got the following error:

An error occurred during client rendering.

An error has occurred during report processing.

Query execution failed for dataset 'dsProc'.

The EXECUTE permission was denied on the object 'smGetMsgString', database 'DYNAMICS', schema 'dbo'.

The same error occurred on the Trial Balance Summary report. In reviewing the permissions on the smGetMsgString stored procedure, I found that only the DYNGRP role int he DYNAMICS database had EXECUTE permissions on it. After giving the three “rpt_” roles EXECUTE permissions, the report ran fine.

Has anyone else run across this or similar issues on other reports? Has Microsoft fixed this in a service pack?

Dynamics GP/SQL Database Login Disconnect

2009-10-28T12:01:00.001-07:00

This article deals with the following error messages in Dynamics GP:

“A get/change first operation on table 'coProcess' failed accessing SQL data”
“[Microsoft][SQL Native Client][SQL Server]The server principal "LESSONUSER2" is not able to access the database "TWO" under the current security context.”
“The user could not be added to one or more databases.”
“A get/change first operation on table 'SY_Users_MSTR' failed accessing SQL data”
“[Microsoft][SQL Native Client][SQL Server]The server principal "LESSONUSER1" is not able to access the database "DYNAMICS" under the current security context.”
“[Microsoft][SQL Native Client][SQL Server]EXECUTE permission denied on object 'zDP_SY01400SS_1', database 'DYNAMICS', schema 'dbo'.”

Dynamics GP tracks users separately from SQL Server. However, there is a one to one relationship between a Dynamics GP user and a SQL Server login. Both the Dynamics GP user and corresponding SQL Server login have the same username. Additionally, when a user is granted access to a company, their SQL login is added as a SQL user in that company’s database as part of the DYNGRP role. The SQL logins associated with Dynamics GP users also have an associated SQL database user in the DYNAMICS database also a member of the DYNGRP role.

Problems happen when the Dynamics GP users and SQL logins get out sync. There are five situations when these get out of sync:

The Dynamics GP user has been given access to a company in GP but the corresponding SQL login doesn’t have access to that company.
The Dynamics GP user doesn’t have access to a company in GP but the corresponding SQL login does have access to the company.
The Dynamics GP user doesn’t have access to the DYNAMICS database.
The Dynamics GP user’s corresponding SQL login doesn’t have access to the Dynamics database.
The Dynamics GP user’s corresponding SQL login isn’t a member of the DYNGRP role in the databases it is supposed to have access to.

First we’ll look at each of these situations individually and how to individually resolve them. Then we’ll look at a script that will clean up all of them at once.

Dynamics User w/ Missing SQL User

This is the case where a Dynamics GP user has been given access to a company in GP but the corresponding SQL login doesn’t have access to that company. In this case, the user will receive the following error message when logging into Dynamics GP:

Clicking on “More Info” returns the following:

“[Microsoft][SQL Native Client][SQL Server]The server principal "LESSONUSER2" is not able to access the database "TWO" under the current security context.”

To resolve this error, go into SQL Management Studio and add the corresponding SQL login to the company database as a member of the DYNGRP role.

SQL User in Company Database w/o Corresponding Dynamics GP User Access

In this case, the Dynamics GP user does not have access to a particular company but the corresponding SQL login does have access. This causes an error in the User Access form if you attempt to add the user to the offending company as follows:

“The user could not be added to one or more databases.”

In this case, going into SQL Management Studio and removing the SQL login’s access to the company database will resolve the error and allow the Dynamics GP user to be given access to the company.

SQL User Doesn’t Have Access to the DYNAMICS Database

In this case, the Dynamics GP user has access to the company database, but doesn’t have access to the DYNAMICS database. If this happens, the user will see the following error when attempting to login to Dynamics GP.

Clicking on “More Info” returns the following:

“[Microsoft][SQL Native Client][SQL Server]The server principal "LESSONUSER1" is not able to access the database "DYNAMICS" under the current security context.”

To resolve this error, go into SQL Management Studio and add the corresponding SQL login to the DYNAMICS database as a member of the DYNGRP role.

SQL User in Company Database w/o Membership in DYNGRP SQL Database Role

In this case, the Dynamics GP user has access to a company and their corresponding SQL login has access to the company database, however, they are not a member of the DYNGRP role in the company database. In this case the user will see the following error when trying to login to the company in question:

Clicking on “More Info” returns the following:

“[Microsoft][SQL Native Client][SQL Server]EXECUTE permission denied on object 'zDP_SY01300F_1', database 'TWO', schema 'dbo'.”

To resolve this, go into SQL Management Studio and add the corresponding SQL login to the DYNGRP in the company database in question.

SQL User Is Not a Member of the DYNGRP Role in the DYNAMICS Database.

In this case, the SQL user is properly setup in the company database and has access to the DYNAMICS database, but is not a member of the DYNGRP role in the DYNAMICS database. When logging in in this situation, the user will receive the following error:

Clicking on “More Info” returns the following:

“[Microsoft][SQL Native Client][SQL Server]EXECUTE permission denied on object 'zDP_SY01400SS_1', database 'DYNAMICS', schema 'dbo'.”

To resolve this, go into SQL Management Studio and add the corresponding SQL login to the DYNGRP in the DYNAMICS database.

A Script to Fix it All

So the individual fixes are all well and good, but we recently ran a Reformatter project, and in the process, moving from one SQL server to another, at a client with 50+ users and 230+ companies. In addition, we were reconfiguring all of their security to take advantage of GP 10’s role based security. Something we hadn’t done during the original upgrade.

When all was said and done, we ended up with a number of situations where there were various disconnects in the users, mostly SQL logins missing from companies when they should have been there and in companies they shouldn’t. Fixing all this by hand would have been a nightmare, so we decided to write a script that would fix it. In this case we had to write a script to write a script (see prior article related to this and how to setup SQL Management Studio to do this). Here, we not only had to write a script that created a script against each company, but also against each user as follows:

-- Don't forget to set Results to Text in your
-- SQL Management Studio query window.
--
-- Set NOCOUNT on so that row count doesn't show
-- up in the text results.
SET NOCOUNT ON;

-- Common table expression that returns every
-- possible combination of user and company.
WITH UserCompanyCombinations
AS (
    SELECT
            SY01400.USERID
            ,SY01500.CMPANYID
            ,SY01500.INTERID
        FROM SY01400 -- User Master table
            CROSS JOIN SY01500 -- Company Master table
        WHERE SY01400.USERID NOT IN ('sa', 'DYNSA')
)
-- Script to add all users to DYNAMICS database
SELECT
        SQLStatement =
            REPLACE(
'
USE DYNAMICS
GO

PRINT ''ADDING USER {USERNAME} to DYNAMICS database.''

CREATE USER [{USERNAME}] FROM LOGIN [{USERNAME}]
GO

EXEC sp_addrolemember ''DYNGRP'', ''{USERNAME}''
GO

----------

',
            '{USERNAME}', USERID)
    FROM SY01400 -- User Master table
    WHERE USERID NOT IN ('sa', 'DYNSA')

UNION ALL

-- Script to remove users from company databases they shouldn't
-- have access to.

SELECT
        SQLStatement =
            REPLACE(REPLACE(
'
USE {COMPANYDB}
GO

PRINT ''REMOVING USER {USERNAME} from {COMPANYDB} database.''

DROP USER [{USERNAME}]
GO

----------

',
            '{COMPANYDB}', UserCompanyCombinations.INTERID)
            , '{USERNAME}', UserCompanyCombinations.USERID)
    FROM UserCompanyCombinations
        LEFT JOIN SY60100 -- User Access table
            ON UserCompanyCombinations.USERID = SY60100.USERID
                AND UserCompanyCombinations.CMPANYID = SY60100.CMPANYID
    WHERE SY60100.CMPANYID IS NULL

UNION ALL

-- Script to add users to company databases they should have access to
SELECT
        SQLStatement =
            REPLACE(REPLACE(
'
USE {COMPANYDB}
GO

PRINT ''ADDING USER {USERNAME} to database {COMPANYDB}''

CREATE USER [{USERNAME}] FROM LOGIN [{USERNAME}]
GO

EXEC sp_addrolemember ''DYNGRP'', ''{USERNAME}''
GO

----------

',
            '{COMPANYDB}', SY01500.INTERID)
            , '{USERNAME}', SY60100.USERID)
    FROM SY01500 -- Company Master table
        INNER JOIN SY60100 -- User Access table
            ON SY01500.CMPANYID = SY60100.CMPANYID
    WHERE SY60100.USERID NOT IN ('sa', 'DYNSA')

As you can see this is a union of three separate queries as follows:

The first query creates a SQL statement for each user, adds them to the DYNAMICS database and then makes them a member of the DYNGRP role.
The second query creates a SQL statement for each user and company that the Dynamics GP user doesn’t have access to a company as defined by the SY60100 user access table and removes their SQL user from the associated company database.
The final query creates a SQL statement for each user and company database that the Dynamics GP users do have access to as defined by the SY60100 user access table, adding the corresponding SQL login to the database as a member of the DYNGRP role.

You’ll note that in all of the queries we’re excluding the “sa” and “DYNSA” users as these are treated as special users in Dynamics GP. “sa” should always be in the “sysadmin” server role, giving it full access to all databases. The “DYNSA” user maps to the “dbo” (database owner) user in each database rather than a user of the same name. This gives the “DYNSA” user special privileges in the database, such as the ability to modify database objects.

When you run the script above you should receive something like the following in the results pane:

USE DYNAMICS
GO

PRINT 'ADDING USER LESSONUSER1 to DYNAMICS database.'

CREATE USER [LESSONUSER1] FROM LOGIN [LESSONUSER1]
GO

EXEC sp_addrolemember 'DYNGRP', 'LESSONUSER1'
GO

----------

USE DYNAMICS
GO

PRINT 'ADDING USER LESSONUSER2 to DYNAMICS database.'

CREATE USER [LESSONUSER2] FROM LOGIN [LESSONUSER2]
GO

EXEC sp_addrolemember 'DYNGRP', 'LESSONUSER2'
GO

----------

USE FOUR
GO

PRINT 'REMOVING USER LESSONUSER2 from FOUR database.'

DROP USER [LESSONUSER2]
GO

----------

USE TWO
GO

PRINT 'ADDING USER LESSONUSER1 to database TWO'

CREATE USER [LESSONUSER1] FROM LOGIN [LESSONUSER1]
GO

EXEC sp_addrolemember 'DYNGRP', 'LESSONUSER1'
GO

----------

USE TWO
GO

PRINT 'ADDING USER LESSONUSER2 to database TWO'

CREATE USER [LESSONUSER2] FROM LOGIN [LESSONUSER2]
GO

EXEC sp_addrolemember 'DYNGRP', 'LESSONUSER2'
GO

----------

USE FOUR
GO

PRINT 'ADDING USER LESSONUSER1 to database FOUR'

CREATE USER [LESSONUSER1] FROM LOGIN [LESSONUSER1]
GO

EXEC sp_addrolemember 'DYNGRP', 'LESSONUSER1'
GO

----------

Note that this is a little quick and dirty and running this script will cause errors when it tries to create users that already exist or drop users that don’t. Adding some extra code to check for a user’s existence or not wouldn’t be that difficult.

Just Getting It Done

2009-09-28T14:08:00.001-07:00

Here’s a link to a great blog entry by Joel Spolsky (Joel on Software) about programmers who just get the job done and delivered. I’m a great believer in “good enough”. Not perfectly designed, n-tier architecture, reusable web services, but maintainable code, works, gets the job done and gets delivered.

Enough said. Read the article.

Stuck Open Payables Voucher

2009-09-25T08:37:00.001-07:00

We recently had a client call with two payables voucher that were stuck open. The amount remaining was $0 but there were no payments associated with it. Because the amount remaining was $0, they were unable to apply any further payments. In addition, running Check Links and Reconcile didn’t resolve the situation.

Time to open up Query Analyzer (yes they’re still on SQL 2000). After some digging, we found that there were Apply records in the PM10200 (apply work table) and the PM20100 (apply open table) for the full amount of the transaction. Additionally, the date invoice paid off (DINVPDOF) was set and the current transaction amount (CURTRXAM) was set to $0.

The solution was to remove the apply records, clear out the date invoice paid off and set the current transaction amount to the document amount as follows:

--Delete orphanned apply to records from the
--work table.
DELETE FROM PM10200
     WHERE VENDORID = '<<MyVendorID>>'
         AND APTVCHNM IN ('<<MyVoucherNumber1>>', '<<MyVoucherNumber2>>')

DELETE FROM PM20100
     WHERE VENDORID = '<<MyVendorID>>'
         AND APTVCHNM IN ('<<MyVoucherNumber1>>', '<<MyVoucherNumber2>>')

--Update the PM transaction header to set the
--current amount due (CURTRXAM) to the full
--document amount and the date invoice paid off
--(DINVPDOF) to empty (1/1/1900)
UPDATE PM20000 SET
        CURTRXAM = DOCAMNT
        ,DINVPDOF = '1/1/1900'
    WHERE VENDORID = '<<MyVendorID>>'
        AND VCHRNMBR IN ('<<MyVoucherNumber1>>', '<<MyVoucherNumber2>>')

For good measure, we ran Check Links against the Payables Transaction Logical File after completing this. It didn’t register any errors with the specific vouchers.

Dynamics GP Security Console Error - “Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))”

2009-08-27T12:45:00.001-07:00

We recently had to add a user to an app that uses the Dynamics GP web services. Since the web services seem to only allow you to assign permissions to individual users and not Active Directory groups, I was required to go into the Dynamics GP Security Console. Sadly this often turns into an adventure in error messages and this time was no different.

First the console errored out at the top level. A little digging and I discovered that of the three app pools setup by the GP Web Services in IIS, GPWebServicesAppPool, DynamicsSecurityServiceAppPool and DynamicsSecurityAdminServiceAppPool, the first two were using the service account we’d originally setup for this. The DynamicsSecurityAdminServiceAppPool, however, was using an administrator end user’s account. Not exactly best practice.

Anyway, figured that changing it to the same account as the other two app pools would do the trick. And so it seemed. I was able to open up the Security Console and navigate down to the policies node, usually the mark of a functioning console.

However, when I went to add the new user to the appropriate application level group, I received an error message when I clicked Apply, “An unexpected error has occurred. See the event log for further details.”

So into the event log I dove and found the following error:

Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

Pretty much left me stuck. A couple of emails back and forth with MS support and it turns out that during the Web Services installation, another account is created, DynamicsGPAdm. This is a local account and is apparently given additional privileges beyond those of a local administrator. I’m guessing that these are to ADAM.

Of course, I had no idea what the password was as this was setup during the install, but fortunately, this account isn’t used for anything else. So a quick password change and an update to the app pool and I was back on my way.

However, the complexity (do we really need three separate app pools?) and fragility of the GP Web Services, is one of the main reasons I’ve moved away from using them have moved creating my own web services using eConnect directly. ADAM may be a great idea in theory, but it has caused me no end of grief.

“Unable to load client print control” Error When Trying to Print from SQL Reporting Services

2009-07-27T14:06:00.001-07:00

We’ve seen this error on and off for quite some time but for some reason we’ve had a batch of calls recently regarding it.

The error occurs when a user attempts to print a report from SQL Server Reporting Services (SSRS) Report Manager using the printer button on the report viewer. The issue is caused by an update to Windows Server (ActiveX Kill Bits security update) that breaks the printing function in SSRS.

You can read about the update in http://support.microsoft.com/kb/956391.

The easiest fix is to apply SQL Server Service Pack 3.

“A fatal error was encountered” When Applying Service Pack 3 to Dynamics GP 9.0 on Windows Server 2008

2009-07-24T18:44:00.001-07:00

First things first. I would like to give credit to one of our technical consultants, Henry Pollock, for getting to the bottom of this one.

During a recent upgrade, we received the following error when attempting to apply Service Pack 3 on a Dynamics GP 9.0 install on a new Windows Server 2008 box.

Note that the original install of GP 9.0 had gone fine. A little digging and it turns out you need to disable UAC (User Access Control) on Windows Server 2008 prior to running the service pack install.

If I were a betting man, I’d guess we’d come across a similar issue on Vista workstations.

Calculating Aggregates in Dynamics GP Integration Manager

2009-07-20T16:50:00.001-07:00

We recently came across a bit of a kink when developing an accounts payable integration using Integration Manager for a client. The client was providing us with a single CSV file of expense report line items. Each line item represented a distribution for an expense report that would contain one or more distributions.

No problems so far. We created two sources from the same file, header and distributions. The header source was grouped by Expense Report ID. With that we were all set except that it turned out the client was unable to supply us with the expense report total in the source file. Since Integration Manager doesn’t have any facility to aggregate values when grouping we were stuck. We had no way to set the Purchases amount in the AP document header.

We briefly considered preprocessing the source file to add the required column but decided to explore some of the scripting capabilities we hadn’t used in the past. 20 minutes and 5 lines of VBScript later we’d solved our problem.

The solution is to keep a running total of the distribution amounts as you’re integrating them and then assign that to the AP Purchases amount before saving each document. To start with we need a global variable to store our running total and need to initialize it to $0.00. To do this we add the following VBScript to the Before Integration event of the integration:

SetVariable "PurchaseAmount", CCur(0.00)

The SetVariable statement saves a value that can be accessed from any other script in the integration. Note that we’ve explicitly converted it to a currency amount using CCur(0.00). Global variables are Variants and will take on the type of their initial value.

Now that we’ve initialized our variable we need to set the running total by capturing the distribution amounts as we process them. We do this by setting the Debit Amount of the distribution to Use Script and setting the following script”

SetVariable "PurchaseAmount", _
GetVariable("PurchaseAmount") + _
SourceFields("Distributions.ExpenseAmount")

CurrentField = SourceFields("Distributions.ExpenseAmount")

The first line is adding the expense amount to the running total. The GetVariable function is returning the value of the global variable we set using SetVariable. The second line is setting the Debit Amount to the current expense amount. Obviously this is a simple example where we’re assuming that the expense amounts are always positive.

Once we’ve got our total for the document we want to assign it to the document Purchases amount. To do this we go back to our integration level events and assign the following script to the Before Document Commit event that takes place right before saving each AP document:

DestinationFields("Purchases") = _
GetVariable("PurchaseAmount")
SetVariable "PurchaseAmount", CCur(0.00)

The first line sets the current documents Purchases amount to the running total. Since we’re not in working within the destination mappings, we set the Purchases field using the DestinationFields object that gives us access to all of the destination fields. The second line reinitializes the running total to $0.00 for the next document.

So there you have it. The ability to calculate aggregates and running totals within Integration Manager. Beyond the obvious AP and AR integrations, I’m sure there are numerous useful and probably less the useful purpose for this.

Aggregate away!

Automating the Transfer of Dynamics GP Databases – Part 4

2009-07-17T10:42:00.001-07:00

In parts 1 through 3 we:

In Part 4 we’re going to tie it altogether. And to do that we’re going old school. The batch file. The following batch file will perform the entire process.

ECHO OFF

ECHO Deleting previous backup files from both source and destination folders
DEL "E:\SQLBackups\*.bak"
DEL "E:\SQLBackups2\*.bak"

ECHO Creating backup script
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S GP10DEVPLAYPEN -E -i CreateBackupScript.sql -b -o BackupScript.sql

ECHO Creating restore script
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S GP10DEVPLAYPEN -E -i CreateRestoreScript.sql -o RestoreScript.sql

ECHO Creating login transfer script - Using the -d parameter to ensure connecting to the master db
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S GP10DEVPLAYPEN -E -d master -i CreateLoginTransferScript.sql -o LoginTransferScript.sql

ECHO Backing up databases
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S GP10DEVPLAYPEN -E -i BackupScript.sql -b -o Backup.log

ECHO Copy back files to folder accessible to second SQL server or instance.
XCOPY "E:\SQLBackups\*.bak" "E:\SQLBackups2\"

ECHO Stop and start second SQL instance to ensure that all connections have been cleared out
net stop mssql$SQL2005_2
net start mssql$SQL2005_2

ECHO Restoring databases
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S GP10DEVPLAYPEN\SQL2005_2 -E -i RestoreScript.sql -b -o Restore.log

ECHO Transferring logins
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S GP10DEVPLAYPEN\SQL2005_2 -E -i LoginTransferScript.sql -b -o LoginTransfer.log

It’s a little difficult to read here (better to cut and paste into a text editor) so here’s what it’s doing:

Deleting all backup files in both the source and destination folders. Don’t use folders that are being used by other backup files as they will be deleted as well.
Creating the backup, restore and login transfer scripts. Note that all of the creation scripts are run against the source server.
Running the backup script against the source server.
Copying all of the backups to the destination server folder. In a multi-server environment this would presumably be a share on the destination server.
This procedure will pick up new company databases and SQL logins used by GP but will not remove deleted ones.
Stopping and starting the destination SQL instance. This is a quick and dirty way to kill any connections to the databases you’re restoring.
Restoring the databases to the destination server.
Running the logins script.

A few things to take note of:

I’m performing this entire operation on single server with two instances of SQL 2005. However, I am performing all of the actions necessary to do this between two machines.
This was written and tested using SQL 2005. Some of the constructs, particularly common table expressions and the use of SQLCMD.EXE in the batch file won’t work in SQL 2000 and would have to be modified. My guess is that most of this would work in SQL 2008.
I’m assuming integrated logins. In other words, the logged in user’s AD login or service account that’s running the batch file needs to be a member of the sysadmin role on both SQL server instances.
I came across an error in the restore when there was an additional but empty data file associated with a database. It wasn’t included in the backup and so errored on the MOVE clause in the restore for that file.

The trickiest part of this was developing the restore script as I needed to take into account all of the possible scenarios of the database files. Certainly, having the same volume and folders structure on your destination environment would simplify that.

Are There Better Ways of Doing This?

That’s always the question isn’t it. There are a hundred ways to skin a cat. Was the 8 hours I spent putting all this together worth it? I certainly learned a few new things.

There are certainly other ways of doing it. Other technologies that would be interesting to explore in this regard include:

SSIS
.NET or ASP.NET apps
PowerShell. I haven’t had the chance to play with PowerShell but it’s becoming the default command line tool for most Microsoft products.

for now I’ll leave that exploration to others.

Automating the Transfer of Dynamics GP Databases – Part 3

2009-07-16T19:23:00.001-07:00

In Parts 1 and 2 we created scripts that backup all of the Dynamics GP databases and then restore them to another SQL instance. Once that’s done we’ll need refresh security. To do that we need to transfer the SQL logins, except sa, used by Dynamics GP.

The following SQL script creates a login script that can be run against the second SQL to create the logins, including their original passwords and SIDS.

-- Ensure that when running this script using SQLCMD.EXE that you set the
-- database to master.
--
-- Setting NOCOUNT on prevents the row count message from appearing at the bottom.
-- Note that the SQL statements do not include column headers. This makes
-- the entire output of the results window executable.

SET NOCOUNT ON

IF EXISTS (
        SELECT *
            FROM sys.objects
            WHERE object_id = OBJECT_ID(N'[dbo].[fn_Hexadecimal]')
                AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[fn_Hexadecimal]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Limey
-- Create date: 7/16/2009
-- Description:
--   This function converts a varbinary to a
--   hexadecimal string value.
--   It is used to convert a password hash and
--   login SID to a string representation.
-- =============================================
CREATE FUNCTION dbo.fn_Hexadecimal
(
    -- Add the parameters for the function here
    @binvalue varbinary(256)
)
RETURNS varchar (514)
AS
BEGIN
    DECLARE @charvalue varchar (514)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF'
    WHILE (@i <= @length)
    BEGIN
      DECLARE @tempint int
      DECLARE @firstint int
      DECLARE @secondint int
      SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
      SELECT @firstint = FLOOR(@tempint/16)
      SELECT @secondint = @tempint - (@firstint*16)
      SELECT @charvalue = @charvalue +
        SUBSTRING(@hexstring, @firstint+1, 1) +
        SUBSTRING(@hexstring, @secondint+1, 1)
      SELECT @i = @i + 1
    END

    -- Return the result of the function
    RETURN @charvalue
END
GO

-- This common table expression returns all enabled Dynamics GP
-- logins and their properties.
WITH DynamicsGPLogins
AS (
    SELECT
            SID = dbo.fn_Hexadecimal(ServerPrincipals.sid)
            ,UserName = ServerPrincipals.name
            ,DefaultDatabaseName = ServerPrincipals.default_database_name
            ,IsPolicyChecked =
                CASE
                    WHEN SQLLogins.is_policy_checked = 1 THEN 'ON'
                    ELSE 'OFF'
                END
            ,IsExpirationChecked =
                CASE
                    WHEN SQLLogins.is_expiration_checked = 1 THEN 'ON'
                    ELSE 'OFF'
                END
            ,PasswordHash = dbo.fn_Hexadecimal(CAST(LOGINPROPERTY(ServerPrincipals.name, 'PasswordHash') AS varbinary (256)))
        FROM sys.server_principals AS ServerPrincipals
            LEFT JOIN sys.syslogins AS Logins
                ON Logins.name = ServerPrincipals.name
            LEFT JOIN sys.sql_logins AS SQLLogins
                ON ServerPrincipals.name = SQLLogins.name
        WHERE ServerPrincipals.type IN ( 'S', 'G', 'U' )
            AND ISNULL(Logins.hasaccess, 0) = 1
            AND ISNULL(Logins.denylogin, 1) = 0
            AND ISNULL(ServerPrincipals.is_disabled, 1) = 0
            AND ServerPrincipals.name <> 'sa'
            AND ServerPrincipals.name IN (
                    SELECT
                            USERID
                        FROM DYNAMICS.dbo.SY01400)
)

-- This select statement uses the list of Dynamics GP
-- logins to create a create login script.
SELECT
'
IF EXISTS (
        SELECT *
        FROM sys.server_principals
        WHERE name = N''' + UserName + ''')
    DROP LOGIN ' + QUOTENAME(UserName) + '
GO

CREATE LOGIN ' + QUOTENAME(UserName) + '
    WITH PASSWORD = ' + PasswordHash + ' HASHED,
    SID = ' + SID + ',
    DEFAULT_DATABASE = ' + DefaultDatabaseName + ',
    CHECK_POLICY = ' + IsPolicyChecked + ',
    CHECK_EXPIRATION = ' + IsExpirationChecked + '
GO
'
    FROM DynamicsGPLogins

There are two basic sections to this query. The first creates a scalar function in the master database that converts a varbinary to a varchar. This is used to convert the SID and password hash to a text representation that can be put in the CREATE LOGIN statement.

The second section of the script actually creates the login script. There are two parts to this are:

The common table expression that returns SQL logins associated with Dynamics GP (from the DYNAMICS SY01400 table) and the associated properties. It filters out any logins that are disabled or have been denied access.
The final part is a select that uses this common table expression to actually create the CREATE LOGIN statements.

This is a simplified version of the SQL login transfer script found in Microsoft Knowledgebase Article 918992 and will produce the following SQL script:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

IF EXISTS (
        SELECT *
        FROM sys.server_principals
        WHERE name = N'DYNSA')
    DROP LOGIN [DYNSA]
GO

CREATE LOGIN [DYNSA]
    WITH PASSWORD = 0x01008E7655067D77DE8FCC167C184C22B8B47BC3CA3CA2FFA86E HASHED,
    SID = 0x42F411B53282584D96B94C0B09491091,
    DEFAULT_DATABASE = master,
    CHECK_POLICY = OFF,
    CHECK_EXPIRATION = OFF
GO

IF EXISTS (
        SELECT *
        FROM sys.server_principals
        WHERE name = N'LESSONUSER1')
    DROP LOGIN [LESSONUSER1]
GO

CREATE LOGIN [LESSONUSER1]
    WITH PASSWORD = 0x0100F81A338610819AD7298F890F6DF93302BC329D14B0F23D9D HASHED,
    SID = 0xA33F190403906349BA4074FC68B7F883,
    DEFAULT_DATABASE = master,
    CHECK_POLICY = OFF,
    CHECK_EXPIRATION = OFF
GO

IF EXISTS (
        SELECT *
        FROM sys.server_principals
        WHERE name = N'LESSONUSER2')
    DROP LOGIN [LESSONUSER2]
GO

CREATE LOGIN [LESSONUSER2]
    WITH PASSWORD = 0x0100C093A6586BBC58FE0CB74886EC4F0AB442FC60766EF9805E HASHED,
    SID = 0xBF47CE1CB51C1A4CABC2A75980AAF78D,
    DEFAULT_DATABASE = master,
    CHECK_POLICY = OFF,
    CHECK_EXPIRATION = OFF
GO

Note that since this creates logins with the same SIDs, they’ll automatically be associated with database users.

In Part 4 we’ll look at putting this all together.

Automating the Transfer of Dynamics GP Databases – Part 2

2009-07-16T14:37:00.001-07:00

In Part 1 we wrote a SQL script that will create a backup script for all of the Dynamics GP databases to help us automate the transfer all of Dynamics GP’s databases to another SQL server or instance. In Part 2 we’ll do the same for restoring the databases.

First, let’s review the T-SQL for restoring a database. In this case the DYNAMICS database.

RESTORE DATABASE DYNAMICS
    FROM DISK = 'E:\SQLBackups2\DYNAMICSTransfer.bak'
    WITH RECOVERY,
        MOVE 'GPSDYNAMICSDat.mdf' TO 'E:\SQLData2\GPSDYNAMICSDat.mdf',
        MOVE 'GPSDYNAMICSLog.ldf' TO 'E:\SQLData2\GPSDYNAMICSLog.ldf'

Not too bad but there’s a bit of a kink here. The MOVE clauses put the the database files in the desired location on the destination instance. If you're putting the files in the exact same folder structure on the destination as the source then we’re all set and can skip the MOVE clauses and build our RESTORE statements the same way as the backup script as follows:

-- Setting NOCOUNT on prevents the row count message from appearing at the bottom.
-- Note that the SQL statements do not include column headers. This makes
-- the entire output of the results window executable.

SET NOCOUNT ON

DECLARE @BackupFolder nvarchar(max)
SET @BackupFolder = 'E:\SQLBackups2\'

-- Create statement to backup DYNAMICS database
SELECT
        '
        RESTORE DATABASE DYNAMICS
            FROM DISK = ''' + @BackupFolder + 'DYNAMICSTransfer.bak''
            WITH RECOVERY
        GO
        '

UNION ALL

-- Create statements to backup company databases.
SELECT
        REPLACE(
        '
        RESTORE DATABASE {COMPANYDB}
            FROM DISK = ''' + @BackupFolder + '{COMPANYDB}Transfer.bak''
            WITH RECOVERY
        GO
        ',
        '{COMPANYDB}', LTRIM(RTRIM(INTERID)))
    FROM DYNAMICS.dbo.SY01500

If, as in many cases, your folder structure is different on the machine you’re transferring to, you need to deal with the MOVE clause. To do this we need to know how many files there are and their logical file names.

If we were dealing strictly with Dynamics GP Utilities created databases with the default files we could easily just hardcode the two MOVE clauses and insert the company ID in the appropriate places. However, if you’ve added files to a database (such as spanning it over multiple volumes), or restored one company database over another (such as restoring a production database over a training one for training purposes), your logical file names will not be standard.

The database file information is most easily extracted from the master.sys.master_files system view in the master database. Once we have the list of files we can build a statement that creates a restore script as follows:

-- Setting NOCOUNT on prevents the row count message from appearing at the bottom.
-- Note that the SQL statements do not include column headers. This makes
-- the entire output of the results window executable.

SET NOCOUNT ON;

--Declare and set variables for the restore folders
DECLARE @BackupFolder nvarchar(max);
DECLARE @NewDataFolder nvarchar(max);
DECLARE @NewLogsFolder nvarchar(max);

SET @BackupFolder = 'E:\SQLBackups2\';
SET @NewDataFolder = 'E:\SQLData2\';
SET @NewLogsFolder = 'E:\SQLData2\';

-- This common table expression returns the list of Dynamics
-- databases, identifying the min and max file IDs.
WITH Databases
AS
(
    SELECT
            DatabaseID = Databases.database_id
            ,DatabaseName = Databases.name
            ,MinFileID = MIN(DatabaseFiles.file_id)
            ,MaxFileID = MAX(DatabaseFiles.file_id)
        FROM master.sys.databases AS Databases
            INNER JOIN master.sys.master_files AS DatabaseFiles
                ON Databases.database_id = DatabaseFiles.database_id
        WHERE Databases.name = 'DYNAMICS'
            OR Databases.name IN (
                SELECT
                        INTERID
                    FROM DYNAMICS.dbo.SY01500)
        GROUP BY
            Databases.database_id
            ,Databases.name
),

-- This common table expression returns the list of files associated
-- with the Dynamics databases, identifying the first and last files
-- in the list.
DatabaseFiles
AS
(
    SELECT
            DatabaseName = RTRIM(LTRIM(Databases.DatabaseName))
            ,LogicalFileName = DatabaseFiles.name
            ,PhysicalFileName =
                CASE
                    WHEN DatabaseFiles.file_id > 2 THEN
                        CASE
                            WHEN type_desc = 'ROWS' THEN 'GPS'
                                + RTRIM(LTRIM(Databases.DatabaseName))
                                + 'Dat_' + CAST(DatabaseFiles.file_id AS nvarchar(3)) + '.' + SUBSTRING(physical_name, (LEN(physical_name) - 2), 3)
                            ELSE 'GPS' + RTRIM(LTRIM(Databases.DatabaseName)) + 'Log_'
                                + CAST(DatabaseFiles.file_id AS nvarchar(3)) + '.' + SUBSTRING(physical_name, (LEN(physical_name) - 2), 3)
                        END
                    ELSE
                        CASE
                            WHEN type_desc = 'ROWS' THEN 'GPS'
                                + RTRIM(LTRIM(Databases.DatabaseName)) + 'Dat.' + SUBSTRING(physical_name, (LEN(physical_name) - 2), 3)
                            ELSE 'GPS'
                                + RTRIM(LTRIM(Databases.DatabaseName)) + 'Log.' + SUBSTRING(physical_name, (LEN(physical_name) - 2), 3)
                        END
                    END
            ,FileType = type_desc
            ,FileID = DatabaseFiles.file_id
            ,IsFirstFile =
                CASE
                    WHEN DatabaseFiles.file_id = Databases.MinFileID THEN 1
                    ELSE 0
                END
            ,IsLastFile =
                CASE
                    WHEN DatabaseFiles.file_id = Databases.MaxFileID THEN 1
                    ELSE 0
                END
        FROM Databases
            INNER JOIN master.sys.master_files AS DatabaseFiles
                ON Databases.DatabaseID = DatabaseFiles.database_id
)

-- The SELECT statement outputs the appropriate parts of the
-- RESTORE statement depending on whether this the first, last
-- or a middle file.
SELECT
        CASE
            WHEN IsFirstFile = 1 THEN
'
RESTORE DATABASE ' + DatabaseName + '
    FROM DISK = ''' + @BackupFolder + DatabaseName + 'Transfer.bak''
    WITH RECOVERY,
        MOVE ''' + LogicalFileName + ''' TO ''' + @NewDataFolder + PhysicalFileName + ''',
'
            WHEN IsLastFile = 1 THEN
'        MOVE ''' + LogicalFileName + ''' TO ''' + @NewDataFolder + PhysicalFileName + '''
GO
'
            ELSE
'        MOVE ''' + LogicalFileName + ''' TO ''' + @NewDataFolder + PhysicalFileName + ''',
'
        END
    FROM DatabaseFiles
    ORDER BY
        DatabaseName,
        FileID

So this is a little involved and is best read by copying into a query window but essentially we’re doing the following:

The first common table expression, Databases, returns a row for each Dynamics database and includes the minimum and maximum file IDs. This will allow us to know what the first and last files are for a particular database and thus know when to prepend the RESTORE clause at the beginning and the GO statement at the end.
The second common table expression, DatabaseFiles, returns the list of database files with their logical names and new physical locations. Additionally, it indicates whether a particular file is the first or last one in the list.
The final SELECT statement actually builds the RESTORE statement using the DatabaseFiles common table expression.

Using my two company test environment, you get the following restore script:

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

RESTORE DATABASE DYNAMICS
    FROM DISK = 'E:\SQLBackups2\DYNAMICSTransfer.bak'
    WITH RECOVERY,
        MOVE 'GPSDYNAMICSDat.mdf' TO 'E:\SQLData2\GPSDYNAMICSDat.mdf',
        MOVE 'GPSDYNAMICSLog.ldf' TO 'E:\SQLData2\GPSDYNAMICSLog.ldf'
GO

RESTORE DATABASE FOUR
    FROM DISK = 'E:\SQLBackups2\FOURTransfer.bak'
    WITH RECOVERY,
        MOVE 'GPSFOURDat.mdf' TO 'E:\SQLData2\GPSFOURDat.mdf',
        MOVE 'GPSFOURLog.ldf' TO 'E:\SQLData2\GPSFOURLog.ldf',
        MOVE 'FOURDat_2' TO 'E:\SQLData2\GPSFOURDat_3.ndf'
GO

RESTORE DATABASE TWO
    FROM DISK = 'E:\SQLBackups2\TWOTransfer.bak'
    WITH RECOVERY,
        MOVE 'GPSTWODat.mdf' TO 'E:\SQLData2\GPSTWODat.mdf',
        MOVE 'GPSTWOLog.ldf' TO 'E:\SQLData2\GPSTWOLog.ldf'
GO

Note that the FOUR company database has had it’s additional data file properly listed in the MOVE clauses.

If we now run this script against our second server (or server instance), we’ll restore all of the databases we backed up previously.

In our Part 3 we’ll look at how to reattach logins to database users.

Automating the Transfer of Dynamics GP Databases – Part 1

2009-07-15T18:28:00.001-07:00

Once upon a time I wrote a blog entry on how to write a script that writes a script that runs against every Dynamics GP company database. Now let’s do something useful with it.

More and more of our clients are having to deal with the alphabet soup regulatory compliance such as SOX (Sarbanes Oxley), PCI DSS, HIPPA etc. or simply deciding to implement good IT change control policies. Often this means that before deploying that fancy new eConnect integration to production, we need to test it in a separate test environment.

No big deal. Build out a new server, install SQL, GP, copy over the databases…How many companies did you say you had? 200…huh…Better cancel my dinner plans.

Best of all, you really need to keep that environment refreshed on a pretty regular basis for support purposes. So how do you transfer 200 company databases (and don’t forget they lowly DYNAMICS database) and still manage to make it home for The Daily Show?

In this four part entry we’ll figure out how to:

Backup all of the Dynamics GP databases.
Restore all of the databases.
Transfer all of the logins.
Pull all of the tasks together.

Let’s start with backing up. Time to dig up the dusty Books Online and find out how to backup a database in the query window. No double clicking here. Here’s how we’d backup the DYNAMICS database:

BACKUP DATABASE DYNAMICS
    TO DISK = 'E:\SQLBackups\DYNAMICSTransfer.bak'
            WITH FORMAT,
        MEDIANAME = 'DYNAMICSBackup',
        NAME = 'Full Backup of DYNAMICS for Transfer'

So, let’s use our handy dandy code to blow that out so that we build a script that backs up the DYNAMICS database and then each of the company databases.

-- Setting NOCOUNT on prevents the row count message from appearing at the bottom.
-- Note that the SQL statements do not include column headers. This makes
-- the entire output of the results window executable.

SET NOCOUNT ON

DECLARE @BackupFolder nvarchar(max)
SET @BackupFolder = 'E:\SQLBackups\'

-- Create statement to backup DYNAMICS database
SELECT
        '
        BACKUP DATABASE DYNAMICS
            TO DISK = ''' + @BackupFolder + 'DYNAMICSTransfer.bak''
                    WITH FORMAT,
                MEDIANAME = ''DYNAMICSBackup'',
                NAME = ''Full Backup of DYNAMICS for Transfer''

        GO
        '

UNION ALL

-- Create statements to backup company databases.
SELECT
        REPLACE(
        '
        BACKUP DATABASE {COMPANYDB}
            TO DISK = ''' + @BackupFolder + '{COMPANYDB}Transfer.bak''
                    WITH FORMAT,
                MEDIANAME = ''{COMPANYDB}Backup'',
                NAME = ''Full Backup of {COMPANYDB} for Transfer''

        GO
        ',
        '{COMPANYDB}', LTRIM(RTRIM(INTERID)))
    FROM DYNAMICS.dbo.SY01500

This should produce the following backup script that will backup all of the Dynamics databases:

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

        BACKUP DATABASE DYNAMICS
            TO DISK = 'E:\SQLBackups\DYNAMICSTransfer.bak'
                    WITH FORMAT,
                MEDIANAME = 'DYNAMICSBackup',
                NAME = 'Full Backup of DYNAMICS for Transfer'

        GO

        BACKUP DATABASE FOUR
            TO DISK = 'E:\SQLBackups\FOURTransfer.bak'
                    WITH FORMAT,
                MEDIANAME = 'FOURBackup',
                NAME = 'Full Backup of FOUR for Transfer'

        GO

        BACKUP DATABASE TWO
            TO DISK = 'E:\SQLBackups\TWOTransfer.bak'
                    WITH FORMAT,
                MEDIANAME = 'TWOBackup',
                NAME = 'Full Backup of TWO for Transfer'

        GO

At this point we’re all set to backup the databases. In Part 2 we’ll create a script that restores them.

Dynamics GP, SQL 2008, Windows 2008 and 64bit

2009-07-15T17:15:00.001-07:00

Wow, it’s been over a month since my last post. I guess between a week and a bit of unplugged (from work at least) vacation and the resulting crush of undone work afterword got me out of the swing of things.

Due to the upcoming end of support for GP version 8, we’re in the midst of a lot of version 10 upgrades. For those clients still on version 8, they’re often on older hardware, OS and SQL versions and so are often moving to new hardware and are looking for OS and SQL Server guidance. Should they go to SQL 2008? What are the advantages of 64bit?

These questions have come from our clients and functional consultants (the people may not be able to write a recursive common table expression but actually know what deferred revenue is). Rather than simply continue to answer queries from clients and consultants on a one-off basis I sent the following in an email to our consultants and thought I might share it.

Why 64bit?

32bit Windows can only access up to 4GB or RAM. In addition, 2GB is reserved for the OS with only 2GB remaining for applications. This means that at a base level, 32bit SQL Server can access at most, 2GB of RAM. There are some system gymnastics that will allow you to access up to 3GB on Windows Server Standard or 7GB on Windows Server Enterprise but due to the required gymnastics these are not ideal. In addition, due to the fact that a 64bit OS processes data in 64bit rather than 32bit chunks, it can process more data faster. No gymnastics are required.

The 64bit version of Windows Server Standard(2003 or 2008) allows you to address up to 32GB of physical RAM. The 64bit version of Windows Server Enterprise allows you to address up to 1TB of physical RAM.

So how does this help with SQL Server?

The more of a database that SQL Server can load into memory, the faster its performance will be. If it can cache an entire database into memory then the only time it has to access the disk is for writes. All reads are done from memory and thus considerably faster than reading from disk. In an ideal world you would want to have enough free memory to load all of your commonly used databases. So if you have 4 3GB databases, you would ideally want to have 12GB or RAM plus the RAM required for the OS and any other applications. In addition to being able to address this amount, a 64bit SQL Server will have improved performance due to the 64bit data processing itself. In fact, there should be performance improvements even when running 64bit SQL on a server with only 4GB or RAM.

How does 64bit SQL Server affect my Dynamics GP implementation?

64bit SQL Server itself should not present any issues with a Dynamics GP implementation. GP will see 64bit or 32bit SQL as the same. However, due to the fact that SQL Server 64bit must be installed on a 64bit OS there are issues with regards to installing the Dynamics GP client on the SQL Server. Some components, most notably the FRx client are not supported on 64bit Oss, even though they support connecting to a 64bit SQL Server.

So what about the Dynamics GP client and components?

In the past, we’ve generally installed a “server” install of the Dynamics GP client on the SQL Server. This includes the Dyndata file share that is used by the workstation clients. Unlike earlier versions Dynamics GP, there is no longer a separate “server” install of the client. All clients are equal and all “server” functions can be performed from any client workstation with little to no adverse affects on performance.

As a best practice, it is always advisable to avoid installing additional applications on the SQL Server for the following reasons:

To maximize the amount of resources (CPU, RAM and disk) available for SQL itself.
To reduce or eliminate the need to remote desktop directly into the SQL server for security reasons. This also reduces the possibility of inadvertent reboots or system changes of a production SQL server.
To reduce the possibility of additional applications or services interfering with SQL or other services and causing instability issues on the SQL Server.
Avoiding issues with clustering if the SQL server currently is or is planned to by clustered.
Avoiding issues with installing the client on a 64bit OS.
To allow the SQL hardware to be upgraded/replaced without affecting the Dynamics GP install (except for possibly the client ODBC connections)

So where should I install the Dynamics GP client?

It is recommended that a “server” or “administrative” copy of the Dynamics GP client be installed on a dedicated application server. This application server should be a 32bit OS. This is where the Dyndata folder will reside as well as additional components such as eConnect, Web Services, RDM etc. In addition, it is recommended that a copy of the client’s current version of Microsoft Office (at least Excel) also be installed on this server for the purposes of troubleshooting GP/Office functionality. Also, a copy of the SQL Server Client Tools (SQL Server Management Studio and SQL Server Business Intelligence Studio) should be installed on this server so that most, if not all, SQL administrative functions can be performed without remote desktopping into the SQL server.

As noted, this should ideally be a dedicated server so that the Dynamics GP install and the various components do not adversely affect or are not adversely affected by other applications installed on the server. Since many of our clients have implemented virtual environments, and this is a perfect candidate for a virtual server (despite Microsoft’s statements to the contrary), it should not be an additional burden to create and support this environment.

When is it appropriate to recommend a single server environment?

If the client has a small number of users (<=10) and it is not expected that the total size of all of the active databases (Dynamics or other applications) will exceed 4GB over the life of the installation and the client is not in a virtualized environment and so would require additional hardware and OS licenses for an application server then it would be appropriate to recommend a single server environment. This environment would be required to be 32bit OS and 32bit SQL Server.

Is a two server environment the most I’ll need?

No. If you have a particularly high transaction environment on your SQL server, such as high transaction Dynamics environment or other large, high transaction databases other than Dynamics GP, then it would be appropriate to split off other components of SQL Server, such as Reporting Services and Analysis Services to their own dedicated servers. Note that in a clustered SQL Environment, Reporting Services and Analysis Services would be required to be put on servers other than the SQL Server. If this were the case then the Enterprise version of SQL Server would be required.

What about SQL Server 2008?

Now that Microsoft has released Service Pack 1 for SQL Server 2008, more clients may be looking to deploy it to take advantage of some of its new features. Most of the commonly used components are Dynamics GP, including SRS reports, are supported on SQL Server 2008 when using SP2 or SP3 of Dynamics GP or the particular GP component. FRx is currently not supported on SQL Server 2008, however, it has been installed and is working correctly on SQL Server 2008 in the Caturano Dynamics GP environment. Microsoft is currently evaluating FRx’s compatibility with SQL Server 2008 and expects to support it in FRx 6.7 SP 11, due out in July, 2009.

When might I recommend SQL Server 2008?

SQL Server 2008 has a number of new features that some clients my find compelling including:

A (reported) 10-15% performance improvement over SQL Server 2005.
Transparent Data Encryption that encrypts the entire database, log and backup files. This feature might be of interest to clients that store credit card numbers, 1099 vendor SSNs, employee payroll information etc. and may be subject to Payment Card Industry Data Security Standards (PCI DSS) or the Mass Privacy regulations going into affect 1/1/2010.
Built in data (including data viewing) and configuration auditing for clients that might face compliance issues (PCI, SOX, HIPPA, Mass Privacy)
Completely rewritten SQL Server Reporting Services with improved performance (particularly multi-user performance) and new more user-friendly report builder for report authoring.

What about Windows Server 2008?

Like SQL Server 2008, Windows Server 2008 has passed the initial service pack test (it’s currently on SP2) and so many clients may be migrating to it or at least setting up new hardware/VMs with it. Like SQL Server 2008, some components, most notably FRx are not supported on Windows Server 2008.

Can you give me a quick reference on component support?

Sure. The following components are supported on 32bit only. Thus if you use any of these components, they must be installed on a 32bit OS, ideally an app server:

Rapid Implementation Tools (including Rapid Configuration and Rapid Migration)
Business Portal for Microsoft Dynamics GP 10.0, Internet Information Services server
Catalog Maintenance
Microsoft Dynamics Security Synchronization Utility
Business Portal Migration Utility
Analysis Cubes for SQL Server 2000 (client and server). Note that this is for SQL Server 2000. The Analysis Cubes for SQL Server 2005 are supported on 64bit.
Benefit Self Service Suite and Certification, Licensing, and Training Manager for Business Portal
Personal Data Keeper
Workflow
Workflow Software Development Kit
Workflow History Archiving Utility
Microsoft FRx Reporter 6.7
Microsoft FRx Forecaster 6.7
Microsoft FRx Web Port 6.7
Enterprise Reporting 7.5
SmartTags
eExpense
Field Service Anywhere
Microsoft Dynamics Solution Accelerator for Compliance Management

The following components are not supported on Windows Server 2008 (Assume SP3 for all supported components). Thus if you use any of these components, the app server must be running Windows 2003:

Rapid Implementation Tools (including Rapid Configuration and Rapid Migration)
Analysis Cubes for SQL Server 2000 (client and server). Note that this is for SQL Server 2000. The Analysis Cubes for SQL Server 2005 are supported on 64bit.
Workflow. Note that Workflow will be supported with the release of Microsoft Office SharePoint Server Service Pack 2.
Microsoft FRx Reporter 6.7. Note that it is expected to be supported on the release of SP 11.
Microsoft FRx Forecaster 6.7
Microsoft FRx Web Port 6.7
Enterprise Reporting 7.5
SmartTags
Field Service Anywhere
Microsoft Dynamics Solution Accelerator for Compliance Management

The following components are not supported on SQL Server 2008 (Assume SP3 for all supported components). Thus if you use any of these components, you must use SQL Server 2005 (64bit or 32bit is fine):

Rapid Implementation Tools (including Rapid Configuration and Rapid Migration)
Analysis Cubes for SQL Server 2000 (client and server). Note that this is for SQL Server 2000. The Analysis Cubes for SQL Server 2005 are supported on 64bit.
Microsoft FRx Reporter 6.7. Note that it is expected to be supported on the release of SP 11.
Microsoft FRx Forecaster 6.7
Microsoft FRx Forecaster 7.0
Microsoft FRx Web Port 6.7
Enterprise Reporting 7.5
SmartTags
Field Service Anywhere
Microsoft Dynamics Solution Accelerator for Compliance Management

Can you give me a quick summary of the environments?

Absolutely. Here they are:

Recommended Standard Environment:

2 Servers
Server 1 (SQL Server) – Windows Server 2003 or 2008 64bit, SQL Server 2005 (or 2008 based on compatibility) 64bit.
Server 2 (Application Server) – Windows Server 2003 32bit, Dynamics GP full client install (“admin” or “server” install), all required components (FRx, eConnect, Web Services), Microsoft Office (clients current version), DynData file share, SQL Server Client Tools (SQL Server Management Studio, SQL Server Business Intelligence Studio).

Low Volume Environment:

1 Server – Windows Server 2003 32bit, SQL Server 2005 32bit, Dynamics GP full client install (“admin” or “server” install), all required components (FRx, eConnect, Web Services), Microsoft Office (clients current version), DynData file share.

High Transaction Volume Environment:

3 Servers
Server 1 (SQL Server) – Windows Server 2003 or 2008 64bit, SQL Server 2005 (or 2008 based on compatibility) 64bit database engine only. Do not install Reporting Services or Analysis Services.
Server 2 (Application Server) – Windows Server 2003 32bit, Dynamics GP full client install (“admin” or “server” install), all required components (FRx, eConnect, Web Services), Microsoft Office (clients current version), DynData file share, SQL Server Client Tools (SQL Server Management Studio, SQL Server Business Intelligence Studio).
Server 3 (Reporting & Analysis Server) – Windows Server 2003 or 2008 64bit, SQL Server 2005 Reporting Services(or 2008 based on compatibility) 64bit (32bit if reporting from external 32bit ODBC sources such as MySQL or MS Access), SQL Server Analysis Services, SQL Server Integration Services.

High Transaction Volume/High BI Volume Environment:

4 Servers
Server 1 (SQL Server) – Windows Server 2003 (or 2008 based on compatibility) 64bit, SQL Server 2005 (or 2008 based on compatibility) 64bit database engine only. Do not install Reporting Services or Analysis Services.
Server 2 (Application Server) – Windows Server 2003 32bit, Dynamics GP full client install (“admin” or “server” install), all required components (FRx, eConnect, Web Services), Microsoft Office (clients current version), DynData file share, SQL Server Client Tools (SQL Server Management Studio, SQL Server Business Intelligence Studio).
Server 3 (Reporting Server) – Windows Server 2003 or 2008 64bit, SQL Server 2005 Reporting Services(or 2008 based on compatibility) 64bit (32bit if reporting from external 32bit ODBC sources such as MySQL or MS Access).
Server 4 (Analysis Server) –Windows Server 2003 or 2008 64bit, SQL Server Analysis Services, SQL Server Integration Services.

Dynamics GP and SQL Server 2008 Reporting Services

2009-06-09T13:10:00.001-07:00

As of Dynamics GP 10.00 Service Pack 3, SQL Server 2008, including Reporting Services is now supported. One little item to note however, is that in the Reporting Tools Setup, Reporting Services Tab, the Report Server URL needs to reference the ReportService2005.asmx web service file instead of the ReportService.asmx file as the ReportService.asmx file no longer exists.

As such the URL should be:

http://MySSRSServer/ReportServer/ReportService2005.asmx

Quick ODBC Administrator Tip When Installing Dynamics GP on 64bit Systems

2009-06-09T11:52:00.001-07:00

We’ve started to run across clients requesting that we install Dynamics GP clients on systems with 64bit operating systems. One of the first issues we came across was not being able to find the Dynamics ODBC DSN. A DSN created by the Dynamics GP install was not visible in the ODBC Administrator tool and a DSN created in the Administrator tool was not visible to Dynamics.

It turns out that the default ODBC Administrator tool under the Windows Administrative Tools menu on a 64bit OS is the 64bit version for 64bit ODBC. Since Dynamics is still a 32bit app we need to find and run the 32bit ODBC Administrator tool. The executable is Odbcad32.exe and is located in the %systemdrive%\Windows\SysWoW64 folder.

eConnect + Custom Web Service vs. Dynamics GP Web Services

2009-06-03T14:43:00.001-07:00

Recently, I’ve begun questioning whether I should bother using the Dynamics GP Web Services or simply write my own against eConnect. In the past I’ve done both. I’ve written several integrations using the Dynamics GP Web Services. I’ve also written a couple where I wrote my own custom web service against eConnect. Specifically, there were cases where I needed to develop GL integrations that used Analytical Accounting, functionality provided by eConnect but not Web Services.

My latest conundrum has come about because I have a client I’ve written three integrations for using Web Services. An enhancement they’ve asked for requires me to integrate invoices and returns and then apply them to each other. It appears that this is doable through the taRMApply node (though I haven’t had a chance to try it yet) but not available through Web Services.

So now it looks like I need to create a custom web service, outside of the Dynamics GP Web Service security domain, to simply implement the apply functionality. Beyond the missing eConnect functionality I’ve developed a few other pet peeves:

I have a <50% success rate on a first time install of Web Services. Sometimes a repair or reinstall will work. Often a support ticket on PartnerSource is required (I’ve noted that when entering a PartnerSource support ticket Web Services Installation is now a separate category from Web Services).
I can’t add an Active Directory group to a role assignment. I must add individual users.
You must pass complex objects to the methods so they can’t easily be called by InfoPath or SharePoint Designer workflows. OK, I can’t really be too hard on them for this as I’m not sure how you easily generically build a web service to allow an integration to pass an AP document header and distributions in a simple web method, but still it means I’m writing my own web services.
Performance, especially that first call, leaves something to be desired.
Significantly less extensibility than eConnect and as far as I can tell unable to make use of any eConnect extensions.

OK, so when it does meet your requirements it works quite well. It also has an exceptionally robust role based security model I certainly wouldn’t want to have to reproduce, but quite frankly it’s overkill for what I need it for.

So I think for now I’m going to lean towards a roll your own web service model on top of eConnect and wait to see what GP 11 holds in store.

Let me know what your thoughts are on this.

Securing Dynamics GP with SQL Server 2008

2009-06-03T10:03:00.001-07:00

I recently co-authored a Microsoft sponsored whitepaper with Cynthia Papas of the accounting firm Parente Randolph on how to deploy and configure SQL Server 2008 in a compliance with the Payment Card Industry Data Security Standards (PCI DSS) (click here to download the whitepaper and here to replay the TechNet webcast).

Although this paper is about SQL 2008 in general and PCI DSS in particular, I wondered how it might apply to my Dynamics GP clients. The reality is that few of them do credit card processing out of Dynamics GP and so don’t need to comply with PCI DSS. However, here in Massachusetts we’ve enacted the Mass. Privacy regulations that go into affect 1/1/2001 and cover the protection of personal information such as Social Security Numbers, driver’s license numbers, bank account numbers etc. Many other states have similar regulations (I believe the Mass. ones are based on California’s). In addition there’s HIPPA, SOX, and on and on. And the reality is is that it’s just good business to protect your customer, vendor and employee data.

So other than credit card numbers, what sort of data might be sitting in GP to protect? A couple of months ago I had to fix a companies 1099 data. Stored in the Vendor master table were hundreds of 1099 vendors with their unencrypted SSNs. Same would be true if you’re running payroll through GP.

If you’re a healthcare provider and billing out of GP, in all likelihood there’s medical data in those invoices that come under the jurisdiction of HIPPA.

If you use EFT with your customers or vendors you’re storing bank account numbers for them.

Most of these regulations deal with not only the protection (access control and encryption) of the data but also auditing the viewing of the data. SQL 2008 helps on both these fronts.

First, with Transparent Data Encryption (TDE), SQL will encrypt the entire database, as well as log and backup files, in a way that the consuming application (in this case Dynamics GP), need know nothing about.

Additionally, SQL 2008 offers built-in auditing, including audits on SELECT statements (no need to use SQL Profiler). Unlike most homegrown or off the shelf auditing tools, SQL 2008’s auditing capabilities are not based on triggers and work asynchronously, thus having a minimal impact on performance.

There’s more detail in the whitepaper as well as on Microsoft’s site and other third party sites. The thrust of this entry is to get you thinking about whether there’s data in your GP databases that needs to be protected and might it be worth considering an upgrade to SQL 2008 to do that given the risk of identity theft and its associated liabilities.

Support Note: To my knowledge, only Dynamics GP 10.0 SP2 and above supports SQL Server 2008.

Changing the Server Name of your SQL Server

2009-05-18T13:39:00.001-07:00

In this day and age of virtualization, it’s not uncommon to copy a base VM, rename it and away you go. But what if SQL Server 2005 is already installed on it? How does renaming affect it?

At first glance it may appear that it has had no affect at all, queries will still work, logins still work. However, there are a few subtle and not so subtle side affects. The first is what SQL Server thinks it’s name is. If you run the following statement:

SELECT @@SERVERNAME

You’ll notice that it returns the old server name, not the new one. This happens even though the name shown in the Object Explorer in Management Studio is showing the correct new name. This will cause a clean Dynamics GP 10 install to fail when attempting to create the “Remove Posted PJOURNALs From All Companies” SQL Agent job as it uses the @@SERVERNAME variable to set that up.

To configure SQL Server to know its new name you’ll have to run the following two statements:

EXEC sp_dropserver 'OLDSERVERNAME'
EXEC sp_addserver 'NEWSERVERNAME', local

Once you’ve run these statements, restart the SQL Server service. This will cause SQL Server to now return the new server name from the @@SERVERNAME variable.

A more noticeable side affect of the server name change is that SQL Reporting Services (SSRS) will not work. This is because it is looking to the old server name for the reporting services databases (ReportServer and ReportServerTempDB). To modify this, perform the following steps:

Run the Reporting Services Configuration tool (Start/All Programs/Microsoft SQL Server 2005/Configuration Tools/Reporting Services Configuration).
Navigate to the “Database Setup” page.
Change the Server Name to the new server name and click Apply.

SSRS will now work.

Now what about changing domains? That’ll have to be another post.

Run a Script Against Multiple Companies by Creating SQL from SQL

2009-05-07T13:39:00.001-07:00

This subject isn't the grand topic I had envisioned for my initial post but is a neat little trick I think many would find useful. So here goes the blog!

We recently had to create users and assign SQL permissions to over 200 Dynamics GP companies for an integration. The integration is external to the Dynamics GP client and accesses the SQL databases directly rather than through the client, so SQL security had to be setup. There are a couple of options:

Click through SQL Management, creating users and assigning to the appropriate role (very tedious).
Create a SQL script that sets up the security and run it against each company database (slightly less tedious).

Let’s rule out the first option right away. With the second option, the script might look something like this:

CREATE USER [MYDOMAIN\INTEGRATION USERS] FROM LOGIN ''MYDOMAIN\INTEGRATION USERS''

GO

EXEC sp_addrolemember [Integration Users], [MYDOMAIN\INTEGRATION USERS]

GO

Here we’re creating a user in the database and adding it to the appropriate role. However, we still have to run it against each company databases. Pick database from dropdown, click execute, wait, repeat.

If only there were an easy way to generate a script that would run against each database.

Well there is! With a simple SQL statement we can generate a script that will run against every company.

-- Don't forget to set Results to Text in your
-- SQL Management Studio query window.
--
-- Set NOCOUNT on so that row count doesn't show
-- up in the text results.
SET NOCOUNT ON;

SELECT
REPLACE(
'
USE {COMPANYDB}
GO

CREATE USER [MYDOMAIN\INTEGRATION USERS] FROM LOGIN [MYDOMAIN\INTEGRATION USERS]

GO

EXEC sp_addrolemember ''Integration Users'', ''MYDOMAIN\INTEGRATION USERS''

GO
',
'{COMPANYDB}', INTERID)
FROM DYNAMICS.dbo.SY01500

In the SQL statement above we’ve taken our security script, added a USE statement at the top to set the company and placed it inside a SQL statement. Note that the USE statement has tag {COMPANYDB} instead of a SQL database name. The SELECT statement replaces this with the INTERID from the SY01500 GP companies table in the DYNAMICS database.

This statement will create a single row for each company containing a single column with the resulting security script. When concatenated together you get the following script (this was run against a GP instance with two companies):

USE AAA
GO

CREATE USER [MYDOMAIN\INTEGRATION USERS] FROM LOGIN [MYDOMAIN\INTEGRATION USERS]

GO

EXEC sp_addrolemember 'Integration Users', 'MYDOMAIN\INTEGRATION USERS'

GO

USE TWO
GO

CREATE USER [MYDOMAIN\INTEGRATION USERS] FROM LOGIN [MYDOMAIN\INTEGRATION USERS]

GO

EXEC sp_addrolemember 'Integration Users', 'MYDOMAIN\INTEGRATION USERS'

GO

To get this to work in SQL Management Studio we need to send the results to text instead of the grid. This can be set in the Query/Results To menu as shown here

or clicking on the To Text button in the tool bar shown below:

Note that by default, the maximum number of characters show column per row when sending results to text is 256. This can be changed in Tools/Options screen, navigating to Query Results/SQL Server/Results to Text and updating the Maximum number of characters in each column to the maximum number of characters in your company script.

This trick could be used for any number of tasks that need to be run against every company such as creating custom stored procedures or performing mass backups and restores into test environments.