New Article on MSDynamicsWorld.com: MVP Mark Polino alerts everyone on GP Alerts

“This disk will self destruct in 5 seconds”

As with the movies, MVP Mark Polino makes a theatrical appearance on MSDynamicsWorld showing Microsoft Dynamics GP alert mechanisms. He takes a quick run down through Reminders, SmartList Reminders, Business Alerts and SQL Server alerts. It’s very easy to forget that Microsoft Dynamics GP offers a range of mechanisms to work proactively, instead of spending tons of hours at crunch time trying to figure out why AR does not tie with GL.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Dex – Enabling long physical table names

As many of you may (or may not) know Dexterity had its origins in the days of the 8.3 file length limitation, imposed by the FAT file system found in the early versions of Windows — for more information, see the article Understanding how Microsoft Dynamics GP works with Microsoft SQL Server by David Musgrave over at Developing for Dynamics GP — This limitation reflected on the names of the physical files supported by the ISAM file servers at the time, FairCom (Ctree) and Btrieve.

The physical name constraints carried over to Microsoft SQL Server table naming convention, leaving tables with what a new bread of database administrators, systems administrators, and developers have denominated “cryptic names”. The fact is, reworking table physical names to support SQL Server would have been quite an expensive task, so the decision was made to continue with these names.

Nonetheless, Dexterity was enhanced to allow long physical table names, but this option needs to be activated prior to adding new tables to a Dexterity-based application. To enable long table names, follow these steps:

1. In Dexterity, click on the Edit menu option, then choose Options…

2. In the Options window, click on Allow Long Physical Table Names to enable the checkmark.

3. Click Ok to accept the changes. That’s it!

When the long physical table names option is not enabled, Dexterity may trick you into believing that you can enter those long names. Such physical names will then get truncated to 8 characters without any warnings.

New Microsoft Dynamics GP modules and third party applications now display long physical table names with explicit meaning, facilitating reporting from external sources. However, I still love the ‘old’ table names… they keep me gainfully busy!

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Microsoft Dynamics GP network data encryption

If your organization happens to be PCI-compliant you are already too familiar with the data encryption requirements imposed by the financial institutions you currently deal with.

As part of your certification and compliance process, you probably had to find a way to encrypt customer and credit card data traveling between your organization and the credit card processor. This means you had to enable SSL encryption on your servers all the while obtaining a certificate issued by a certificate authority.

But what about your customer data traveling between your SQL Server and your Microsoft Dynamics GP client, and viceversa.

Here are three methods to encrypt Dynamics GP data traveling over the network:

1) You can enable strong data encryption at the DSN level if using a Microsoft SQL Server Native Client DSN to connect to your Dynamics GP databases on a Microsoft SQL Server 2005 or Microsoft SQL Server 2008 database server. To enable encryption, check the Use Strong Data Encryption option on the 4th dialog page, as shown below.

If using this option, you will want to make sure it’s enabled on all workstations, and provide a centralize way to administrate it, for example, via the Group Policy Data Sources preferences extension.

2) To encrypt data traveling from clients to server, you can use Microsoft SQL Server Configuration Manager to configure the SQL Server Native Client protocols used to communicate to the SQL Server Database Engine.

The Force protocol encryption option will request a connection using SSL.

When Trust Server Certificate is set to No, the client process attempts to validate the server certificate. The client and server must have each have a certificate issues from a public certification authority. If the certificate is not present on the client computer, or if the validation of the certificate fails, the connection is terminated.

When set to Yes, the client does not validate the server certificate, thereby enabling the use of a self-signed certificate.

Trust Server Certificate is only available if Force protocol encryption is set to Yes (See method 3).

3) You can use Microsoft SQL Server Configuration Manager to setup a server side certificate, preferrably issued by a certificate authority. To encrypt connections, you should provision the SQL Server Database Engine with a certificate. If a certificate is not installed, SQL Server will generate a self-signed certificate when the instance is started. This self-signed certificate can be used instead of a certificate from a trusted certificate authority, but it does not provide authentication or non-repudiation.

NOTE: Secure Sockets Layer (SSL) connections encrypted using a self-signed certificate do not provide strong security. They are susceptible to man-in-the-middle attacks. You should not rely on SSL using self-signed certificates in a production environment or on servers that are connected to the Internet.

The login process is always encrypted. When Force Encryption is set to Yes, all client/server communication is encrypted, and clients connecting to the Database Engine must be configured to trust the root authority of the server certificate.

While your organization may not necessarily be seeking PCI-compliance, having strong data encryption for your accounting data is just another way to position your company with customers and vendors.

Related Articles

Encryption Connections to SQL Server – Microsoft SQL Server Books Online.
How to: Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager) – Microsoft SQL Server Books Online.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

SQL Server Management Studio Standard Reports

If you have worked with SQL Server Management Studio (SSMS) in either Microsoft SQL Server 2005 or Microsoft SQL Server 2008, you may have inadvertly overlooked one of its key features: Standard Reports.

Standard Reports are Reporting Services (SSRS) reports that can provide all sort of statuses and and information about the database engine and its management components and well as the databases themselves in real time — the reports are refreshable! When executed, the reports are embedded in tabs within a new SSMS tab. As a consultant, I find these reports particularly useful when attempting to establish the health of a Microsoft Dynamics GP SQL Server installation. I can immediately relay critical SQL Server performance information to my clients and suggest preventative or corrective actions to mitigate the issues, saving them money in the process.

So lets take a look at the available reports by node…

Node	Report
Server	Server Dashboard
Server	Configuration Changes History
Server	Schema Changes History
Server	Scheduler Health
Server	Memory Consumption
Server	Activity – All Blocking Transactions
Server	Activity – All Cursors
Server	Activity – Top Cursors
Server	Activity – All Sessions
Server	Activity – Top Sessions
Server	Activity – Dormant Sessions
Server	Activity – Top Connections
Server	Top Transactions by Age
Server	Top Transactions by Blocked Transactions Count
Server	Top Transactions by Locks Count
Server	Performance – Batch Execution Statistics
Server	Performance – Object Execution Statistics
Server	Performance – Top Queries by Average CPU Time
Server	Performance – Top Queries by Average IO
Server	Performance – Top Queries by Total CPU Time
Server	Performance – Top Queries by Total IO
Server	Server Broker Statistics
Server	Transaction Log Shipping Status
Database	Disk Usage
Database	Disk Usage by Top Tables
Database	Disk Usage by Table
Database	Disk Usage by Partition
Database	Backup and Restore Events
Database	All Transactions
Database	All Blocking Transactions
Database	Top Transactions by Age
Database	Top Transactions by Blocked Transactions Count
Database	Top Transactions by Locks Count
Database	Resource Locking Statistics by Object
Database	Object Execution Statistics
Database	Database Consistency History
Database	Index Usage Statistics
Database	Index Physical Statistics
Database	Schema Changes History
Database	User Statistics
Database	Active Full-Text Catalogs
Logins	Login Statistics
Logins	Login Failures
Logins	Resource Locking Statistics by Logins
Management	Tasks
Management	Number of Errors
Notification	Services General
SQL Server Agent	Job Steps Execution History
SQL Server Agent	Top Jobs

To access a specific report, just right-click on the desired node then choose Reports > Standard Reports, select the desired report. The following is an example of the navigation to the Server node reports.

Lets take a look at some sample standard reports…

Server Dashboard report (server > Reports > Standard Reports > Server Dashboard)

This report provides detailed configuration information including, but not limited to the SQL Server startup time, product version and edition, server collation, the number of processors in used by the SQL Server instance, CPU usage by database, and number of active databases.

Disk Usage by Top Tables report (database > Reports > Standard Reports > Disk Usage by Top Tables)

This reports shows vital table information such as the number of records in the table, the amount of disk space reserved for the table, spaced occupied by data and indexes, and the unused space. This information can be used to plan for disk space optimization and establish whether it will be necessary to increment the number of partitions or relocate the databases.

Backup and Restore Events report

This one is got to be one of the most important database level reports as it shows statistics about the backups completed on a specific database: average time, size of backups, whether the database backup was complete or differential, etc.

I hope you like this SQL Server gem and start to explore these reports. There is valuable information that can be used to administer your Dynamics GP and overall SQL Server environment.

Until next post!

MG.-
Mariano Gomez, MIS, MCP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

How to transfer data between two Microsoft Dynamics GP companies using Microsoft SQL Server Import and Export Wizard

One of the tasks that is often necessary in any Dynamics GP implementation is to get data across from the test company to the production “Go Live” company once we are ready to make the cut over. Those records are usually configuration and master records, but can also be beginning balances for subsidiary modules and General Ledger. This article will show you how to use Microsoft SQL Server 2008 Import and Export Wizard to transfer data across company databases.

The article assumes you are familiar with SQL Server Management Studio and that you know your way around databases and objects.

1. Click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.

2. In the Connect to Server window, click SQL Server Authentication in the Authentication list, and then type the sa user name and sa password. Click Connect.

3. In Object Explorer, double-click Databases. Right-click the database from which you want to export the data, point to Tasks, and then click Export Data.

4. In the Welcome window, click Next.

5. In the Data Source window, you can choose between Windows Authentication and SQL Server Authentication. It’s always been my preference to use Windows Authentication, especially if your Windows user belongs to the SQL Server sysadmin role. If you are not sure, click Use SQL Server Authentication in the Authentication area, and then type the sa user name and sa password. Click Next.

6. In the Destination window, type the server name in the Server Name area. In the Authentication area, click Use SQL Server Authentication, and then type the sa user name and sa password. In the Database list, click to select the destination database. Then, click Next.

7. In the Specify Table Copy or Query window, click Next to accept the default entries.

NOTE: The table(s) to which you are exporting data must exist in the destination database. If the table does not exist, you must follow the installation routines for the add-on or third party to which the tables are related. You can also use the SQL Maintenance window to identify and create the missing table(s). Start Microsoft Dynamics GP and go to File > Maintenance > SQL. While the SQL Server Import and Export Wizard has the ability to create the missing destination tables, this feature will not replicate certain table constraints and indexes that may exist in the source table.

8. Select the check boxes next to the tables that you want to copy to the destination database.

9. Highlight all tables to be exported, then click on the Edit Mappings button. Select Delete rows in existing destination tables and Enable identity insert. If you use Analytical Accounting, and you transfer Analytical Accounting tables, you cannot ignore DEX_ROW_ID for the following tables: Customer Master – RM00101, Vendor Master – PM00200, Item Master – IV00101, and Site Setup – IV40700. Hence, as a standard practice I enable this option to transfer data. Click on OK to continue.

In the Select Source Tables and Views window, click Next.

10. In the Save and Run Package window, click Next to accept the default entries.

11. Now, sit back and watch your data go across!

General Notes

These steps were performed using Microsoft SQL Server 2008. The Import and Export Wizard is fairly standard between this version of SQL Server and Microsoft SQL Server 2005 and you should be able to obtain very similar results. Consult your Microsoft SQL Server documentation for further information on the Import and Export Wizard feature.

Related Articles

KB article 874208 – How to transfer setup information between company databases by using SQL Server 2005 or SQL Server 2000.

Moving data between Microsoft Dynamics GP companies – This site, click here.

The often overlooked, yet powerful Table Import – This site, click here.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Why you should upgrade to Microsoft SQL Server 2008 if you are using Microsoft Dynamics GP

The Microsoft Dynamics GP Product Management and Marketing team has published some performance stats that are good enough reasons to upgrade to Microsoft SQL Server 2008 if you are a Microsoft Dynamics GP customer running SQL Server 2005 or even SQL Server 2000. Their stats revolve around the use of SQL Server 2008’s row compression and page compression features and their ability to save a good amount of storage space.

Don’t forget that the vardecimal storage format, the predecesor of row compression and page compression, was made available in Microsoft SQL Server 2005 SP2 and can be enabled to save data storage once you understand all its capabilities. For more information on vardecimal storage format check the following article:

• Microsoft Technet: Reducing Database Size by Using Vardecimal Storage Format

For more information on Microsoft SQL Server 2008 row compression and page compression features visit the following articles:

• Microsoft Technet: Creating Compressed Tables and Indexes

• Microsoft SQL Server Storage Engine team: Types of Data Compression in SQL Server 2008

Please remember that Microsoft SQL Server 2008 is only supported if you are running Microsoft Dynamics GP v10 with Service Pack 2 (Build Number: 10.00.1061) or later. For information on Microsoft SQL Server versions and service packs, check my previous article here.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

The inner workings of Microsoft Dynamics GP on Microsoft SQL Server

Unless you have been working with Dynamics GP from the days of Ctree and Btrieve, it is quite difficult to comprehend why Dynamics GP seems to behave (as in act up) in certain ways on SQL Server — how would I say this… not quite like your other Windows applications that run on SQL Server.

To understand some of these behavioral issues, David Musgrave brings a two-part series on Understanding how Microsoft Dynamics GP works with Microsoft SQL Server — if it was my article I would have labeled it Dynamics GP technological idiosyncrasies, but then again, I did not write it.

• Part 1 of Understanding how Microsoft Dynamics GP works with Microsoft SQL Server
• Part 2 of Understanding how Microsoft Dynamics GP works with Microsoft SQL Server

Ever wonder why the cryptic table names and columns? A lot of it has to do with the origins of the application and the multiple operating systems and ISAM platforms supported back in the 80’s and 90’s. Know your Microsoft Dynamics GP history… in the early days, long before Microsoft SQL Server, Dynamics GP ran on the Mac OS platform and supported Ctree and Btrieve as file server platforms. I guess at the end of the day, the development team figured it would be a daunting effort to make those tables and columns names meaningful for SQL Server.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Microsoft SQL Server versions and editions

The purpose of this article is to describe how to identify your current Microsoft SQL Server version number and the corresponding product or service pack level. It also describes how to identify the specific edition of SQL Server you are using in your environment.

How to determine which version of SQL Server 2008 is running

To determine which version of Microsoft SQL Server 2008 is running, connect to SQL Server 2008 by using SQL Server Management Studio, and then run the following Transact-SQL statement.

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The following results are returned:
The product version (for example, 10.0.1600.22)
The product level (for example, RTM)
The edition (for example, Enterprise)
For example, the results resemble the following.

10.0.1600.22 RTM Enterprise Edition

The following table lists the product version and file version (Sqlservr.exe) numbers for SQL Server 2008:

                               Product Version      File Version (SQLSERVR.EXE)

RTM                            10.0.1600.22         2007.100.1600.0SQL Server 2008 Service Pack 1 10.0.2531.0          2007.100.2531.0

How to determine which version of SQL Server 2005 is running

To determine which version of Microsoft SQL Server 2005 is running, connect to SQL Server 2005 by using SQL Server Management Studio, and then run the following Transact-SQL statement.

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The following results are returned:
The product version (for example, 9.00.1399.06)
The product level (for example, RTM)
The edition (for example, Enterprise Edition)

For example, the results resemble the following.

9.00.1399.06 RTM Enterprise Edition

The following table lists the product version and file version (Sqlservr.exe) numbers for SQL Server 2005:

                                       Product Version      File Version (SQLSERVR.EXE)

RTM                                    9.00.1399            2005.90.1399SQL Server 2005 Service Pack 1         9.00.2047            2005.90.2047SQL Server 2005 Service Pack 2         9.00.3042            2005.90.3042SQL Server 2005 Service Pack 3         9.00.4035            2005.90.4035

How to determine which version of SQL Server 2000 is running

To determine which version of SQL Server 2000 is running, connect to SQL Server 2000 by using Query Analyzer, and then run the following code.

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The following results are returned:

The product version (for example, 8.00.534)
The product level (for example, “RTM” or “SP2″)
The edition (for example, “Standard Edition”).

For example, the results resemble the following:

8.00.534 SP2 Standard Edition

The following table lists the product version and file version (Sqlservr.exe) numbers for SQL Server 2000:

                                       Product Version      File Version (SQLSERVR.EXE)

RTM                                    8.00.194             2000.80.194.0SQL Server 2000 SP1                    8.00.384             2000.80.384.0   SQL Server 2000 SP2                    8.00.534             2000.80.534.0SQL Server 2000 SP3                    8.00.760             2000.80.760.0SQL Server 2000 SP3a                   8.00.760             2000.80.760.0SQL Server 2000 SP4                    8.00.2039            2000.8.00.2039

For releases of SQL Server prior to SQL Server 2000, check Microsoft Support KB article 31185. Keep in mind also there were a number of Cumulative Updates for each version, which are not listed above. For a detail list of Cumulative Updates version information check SQL Team’s article on SQL Server Versions.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

How to create a smartlist to show invoices without a physical PDF document

Not too long ago, I answered a pretty interesting question on the Dynamics GP newsgroup. The user’s company images signed delivery tickets and store these as PDF documents on a shared location on their network. The PDF document is named after the corresponding invoice number in Dynamics GP, for example, if the invoice number is INV010001, the corresponding image of the delivery ticket would be stored as INV010001.PDF

The user wanted to know if it was possible to create a smartlist to show invoices without a scanned PDF image of the delivery ticket.

Solution

When you think of this problem from the eyes of a user, it seems almost impossible to write a SmartList that would produce the results wanted by the user. But when you breakdown the problem, the real issue is, how do we create a SQL Server view that is able to show whether a file exists for a specific Dynamics GP invoice document.

The solution is to create a SQL Server user-defined function (UDF) that is able to return whether a file exists or not. Then we can incorporate this UDF into our view query. Since views can be addressed from SmartList Builder then we are good to go.

First the UDF.

dbo.IsFileExist

-- Created by Mariano Gomez, MVP

-- Check if the UDF exists and dropIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].   [IsFileExist]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

   DROP FUNCTION dbo.IsFileExistGO

-- Create the UDFCREATE FUNCTION dbo.IsFileExist(@Path VARCHAR(200), @FileName VARCHAR(200))RETURNS INTAS  BEGIN    DECLARE @FullPath VARCHAR(200);    DECLARE @FileExists INT;

    -- Check for the back-slash at the end of the path string    IF RIGHT(RTRIM(@Path), 1)  '\'      SET @Path = RTRIM(@Path) + '\';

    -- concat the path with the file name    SET @FullPath = RTRIM(@Path) + RTRIM(@FileName);

    -- run the xp_fileexist system stored proc to retrieve wheter the file exists or not    EXEC master.dbo.xp_fileexist @FullPath, @FileExists OUT;    RETURN @FileExists;  END;GO

GRANT EXECUTE ON dbo.IsFileExist TO DYNGRPGO

The above UDF uses the undocumented system stored procedure xp_fileexist which, when executed in its native form, will display information about the file passed in as a parameter. For example:

exec master..xp_fileexist 'c:\boot.ini

… produces the following results:

File Exists File is a Directory Parent Directory Exists----------- ------------------- -----------------------1           0                   1

(1 row(s) affected)

Now that we have the UDF function in place, we can prepare the view to be used with SmartList Builder.

dbo.vShowSOPAttachments

-- Created by Mariano Gomez, MVPCREATE VIEW dbo.vShowSOPAttachments AS  WITH SOP_CTE (SOPNUMBE, SOPPDF) AS (    SELECT SOPNUMBE, RTRIM(SOPNUMBE) + '.PDF' FROM SOP10100    UNION ALL    SELECT SOPNUMBE, RTRIM(SOPNUMBE) + '.PDF' FROM SOP30200  )  SELECT SOPNUMBE, SOPPDF, dbo.IsFileExist('C:\', SOPPDF) AS [File Exists] FROM SOP_CTE;GO

GRANT SELECT ON dbo.vShowSOPAttachments TO DYNGRP

Note the call to the dbo.IsFileExists UDF. With the view in place you can follow standard SmartList Builder procedures to implement and deploy the view. For information managing security and changes to SmartList Builder smartlist click here.

Until next post!

MG.-
Mariano Gomez, MIS
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Using T-SQL and recursive CTE to generate a BOM tree

Ever wonder how to replicate a Dynamics GP Bill of Materials tree with SQL Server? The following query uses recursive CTE to generate a BOM tree.

BOMTree.sql

-- Mariano Gomez, MVP-- This query is provided "AS IS". No warranties expressed or implied.WITH BOMCTE (bom_path, tree_path, item, component, qty, effective_date, lvl) AS (  SELECT CAST(RTRIM(ITEMNMBR) AS VARCHAR(MAX)),    CAST(RTRIM(ITEMNMBR) AS VARCHAR(MAX)),    ITEMNMBR,    NULL,    --CAST(NULL AS VARCHAR(MAX)),    CAST(NULL AS NUMERIC(19, 5)),    effective_date,    0 AS lvl  FROM BM00101


  UNION ALL


  SELECT CAST(RTRIM(H.ITEMNMBR) + '/' +    RTRIM(B.CMPTITNM) AS VARCHAR(MAX)),    CAST(REPLICATE(' ', 13) +    RTRIM(B.CMPTITNM) AS VARCHAR(MAX)),    H.ITEMNMBR,    B.CMPTITNM,    B.Design_Qty,    B.effective_date,    1 AS lvl  FROM BM00101 AS H     JOIN BM00111 AS B ON (H.ITEMNMBR = B.ITEMNMBR)


 UNION ALL


  SELECT C.bom_path + '/' + RTRIM(B.CMPTITNM),    CAST(REPLICATE(' ', (C.lvl + 1) * 13) + RTRIM(B.CMPTITNM) AS VARCHAR(MAX)),    B.ITEMNMBR,     B.CMPTITNM,     B.Design_Qty,     B.Effective_Date,    C.lvl + 1  FROM BM00111 AS B    JOIN BOMCTE AS C ON (B.CMPTITNM = C.item))SELECT DISTINCT bom_path, tree_path, lvlFROM BOMCTEORDER BY bom_path;

A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained.

A query is referred to as a recursive query when it references a recursive CTE. Returning hierarchical data is a common use of recursive queries, for example: Displaying employees in an organizational chart, or data in a bill of materials scenario in which a parent product has one or more components and those components may, in turn, have subcomponents or may be components of other parents.

The bottom line is, you can apply this concept to Dynamics GP’s BOM header and detail tables in both the Inventory Control and Manufacturing modules to generate a path and tree representation of your bill.

Related Articles

Recursive Queries Using Common Table Expressions – MSDN. Click here.
Hierarchies in SQL Server 2008 – Plamen Ratchev. Click here.

Acknowledgements

Thanks to Microsoft SQL Server MVP Plamen Ratchev at SQL Studio for his contribution to this article. You can visit Plamen’s blog at http://pratchev.blogspot.com/

Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/