User or role already exists in the current database

When you try to give a user access to a database you get the above message. To resolve this you need to have access to SQL Server Enterprise Manager.

Open Enterprise Manager then go to Security>Logins, right-click on the user having this problem then go to Properties. Open the Database Access tab then Unmark or Uncheck the database you're giving the user access to.

Go back to Great Plains then try to give the user access again.

Microsoft Dynamics GP Demos

I've been seeing a lot of questions in forums asking if there are available demos for Microsoft Dynamics GP. Here are links to the demos:

Foundation I
Foundation II
Financial Management Overview
Financial Management I
Financial Management II
Distribution I
Distribution II

Product Reviews

I’ve been approached by some vendors to give product reviews in this blog. I've also thought of doing this in the past but wanted to concentrate on GP, SQL Server and Crystal. Since the emails have not stopped, I've decided to do it starting 2007.

I'll give product review on one product per month. Please feel free to contact me with your suggestions. I'm only going to review products I'm currently using and had experience in the past. If you want your product reviewed, try to contact me as well.

Webcast: Reviewing the XBRLGL Proposed Recommendation

Here's a webcast on XBRL GL:

XBRL GL working group is in the process of releasing the Global Ledger taxonomy for public review and comment. The GL Working Group is hosting an outreach webcast to assist and encourage people in reviewing and assessing the taxonomy. Please join us on Thursday, November 16th at 9:00 AM EST [1] for step by step guidance on reviewing the XBRL GL taxonomy.

Meeting Details
Subject: Reviewing the XBRL GL Proposed Recommendation
Start Time: Thursday, Nov 16, 2006 9:00 AM EST
End Time: Thursday, Nov 16, 2006 10:00 AM EST

Attendee URL: https://www. livemeeting. com/cc/pricewate rhousecoopers/ join?id=K65FFP&role=attend&pw=z9h-%3Ex% 3AsR
Attendee Meeting Key: z9h->x:sR
Meeting ID: K65FFP

Dial-in:+1 866-462-0164
International: +1 205-354-0249
Code: *717-0411*

XBRL in Microsoft FRx

What is XBRL?
eXtensible Business Reporting Language is an XML-based language being developed specifically for the automation of business information requirements, such as the preparation, sharing, and analysis of financial reports, statements, and audit schedules.

Who needs to use it?
All types of organisations can use XBRL to save costs and improve efficiency in handling business and financial information. Because XBRL is extensible and flexible, it can be adapted to a wide variety of different requirements. All participants in the financial information supply chain can benefit, whether they are preparers, transmitters or users of business data.

DATA COLLECTION AND REPORTING
By using XBRL, companies and other producers of financial data and business reports can automate the processes of data collection. For example, data from different company divisions with different accounting systems can be assembled quickly, cheaply and efficiently if the sources of information have been upgraded to using XBRL. Once data is gathered in XBRL, different types of reports using varying subsets of the data can be produced with minimum effort. A company finance division, for example, could quickly and reliably generate internal management reports, financial statements for publication, tax and other regulatory filings, as well as credit reports for lenders. Not only can data handling be automated, removing time-consuming, error-prone processes, but the data can be checked by software for accuracy.

Small businesses can benefit alongside large ones by standardising and simplifying their assembly and filing of information to the authorities.

DATA CONSUMPTION AND ANALYSIS
Users of data which is received electronically in XBRL can automate its handling, cutting out time-consuming and costly collation and re-entry of information. Software can also immediately validate the data, highlighting errors and gaps which can immediately be addressed. It can also help in analysing, selecting, and processing the data for re-use. Human effort can switch to higher, more value-added aspects of analysis, review, reporting and decision-making. In this way, investment analysts can save effort, greatly simplify the selection and comparison of data, and deepen their company analysis. Lenders can save costs and speed up their dealings with borrowers. Regulators and government departments can assemble, validate and review data much more efficiently and usefully than they have hitherto been able to do.

How can my company benefit from XBRL?
Through the use of XBRL in companies, you will be able to:

* Obtain more rapid and reliable data on company financial performance.
* Greatly reduce effort and costs in gathering and analysing data.
* Simplify and automate tasks.
* Focus effort on analysis and value-added work.
* Make better use of software to improve efficiency and speed.

In summary, XBRL can speed up, reduce effort and increase reliability in accounting and auditing tasks.

Is XBRL available in FRx?
Yes, you can go to CustomerSource and look for instructions on how to use the XBRL Taxonomy for FRx reports. Below is part of these instructions:
Create the link with these steps:

1. From the Row Format menu, select Link | Open Link Window. The link dialog box appears.
2. From the Links record control box, click New.
3. Click the Link Type arrow and select XBRL as the link type.
4. Type a name for the link in the Link Name box.
5. Click the XBRL Taxonomy Files arrow and select the taxonomy file. XBRL taxonomy files have the .xsd file extension.
6. From the Links record control box, click Save.
7. Click Yes in the Save changes to Link confirmation box. The new link column, showing the link name and taxonomy name, appears in the row format window.
8. Repeat steps 1 through 7 for each taxonomy you want to include in your Row Format. Each XBRL Link column corresponds to one taxonomy file.

Are there available online trainings on XBRL?
I would recommend the free training by KPMG which goes more detail to explain the underlying technology behind XBRL which is XML then would give you parts of the XBRL Taxonomy. Here's the link:

Doug Burgum's departure

And I thought he was staying as Chairman of MBS but in order for this to happen he'll be in Redmond full time. I think geography played a key role in his decision.

I first saw Doug in Convergence and was still hosted by Great Plains Software in Fargo. Always soft spoken and poised but very smart & decisive. The venue was smaller then and he'll always joke with customers.

But we're in good hands with Satya's vision of bringing the products to the next level and he's talked before about how role-based productivity, collaboration and business intelligence will drive the growth of this business unit.

My 2 cents, the converged product would be 60% Axapta/Navision, 20% Solomon and 20% Great Plains.

You'll be missed Mr. Burgum!!!

CIO - On the Rise or Losing Ground

"I had to admit that I believe the role as we know it would disappear at many companies—and in the not-too-distant future." - Shopping for a Future by Asiff Hirji's in CIO.com

"As technology leadership becomes a competency required for any senior executive at the management table so the traditional CIO role disappears and the role of "head of manufacturing" in Financial Services and the hybrid supply chain technology role in Consumer Goods emerge. The source of these executive is diverse and more difficult to source. The CIO role is becoming a viable stepping stone to the CEO role. CIO experience will become as valuable as that of CFO." - Kelvin Thompson, co-Head of Global CIO Practice and Head of Innovation at Heidrick & Struggles

I think this would depend on the industry and as roles evolved based on the complexity of the business. The CIO role in the past had been dubbed as the subject matter expert in technology but that has changed with some getting involved with business and project management. Now organizations see the CIO as business savvy that knows the industry, and can provide project and IT management.

Just my 2 cents.

Sept 2006 Articles

1. Adding Color to your Crystal Reports

One of the more requested features in Crystal Reports is color. The user wants to create conditional formulas in the report and show it in different colors. The easiest way to do this is to add conditional formulas in the Detail section. To do this, go to Insert>Section> Make sure you’re on the Details section then choose the Color tab. Mark the Background Color field then open Formula Editor. Once in Formula Editor is open you can create formula for you color, example shown below:

If {SOP10200.XTNDPRCE} > 1000 then RED else NOCOLOR

2. Using SQL Stored Procedures in Crystal Reports

This is a common question from the community. Yes, it is possible to use SQL Stored Procedures in Crystal Reports. To do this go to File>Options then go to the Database tab and mark Stored Procedures. Try to add a new table and you’ll see in your current database connection that both Tables and Stored Procedures would be available.

Google Search

I would like to thank everyone for supporting my blog. A colleague told me today that my blog shows up on the first page of Google search engine for the following keywords:
- Microsoft Dynamics GP (#6)
- Microsoft Dynamics AND SQL (#4)
- Crystal Reports AND Microsoft Dynamics (#3)
- Sarbanes Oxley AND Microsoft Dynamics (#8)

Although my objective is to help the community learn more about the products featured in this blog, it’s very rewarding to know this things and makes me more eager to help.

SAS 99 in Microsoft Dynamics GP

As the administrator of our ERP system, Microsoft Dynamics GP, I spent about 20-25% of my time dealing with our external auditor either to:
a) comply with a report request
b) walk them through the system security
c) audit the database to find historical data submitted to them during the audit
d) other audit requirements for private companies

I found that the audit season had changed a lot after Sarbanes Oxley Act (SOX) started in 2002. Although we are a private company, somehow we are still affected by the Act because of Statement of Accounting Standards (SAS).

What is SAS 99?

Statement of Accounting Standards 99 or SAS 99 (Consideration of Fraud in a Financial Statement Audit) provides needed guidance to auditor charged with providing assurances that clients have not committed fraud. Some experts say " The easiest way to think of SAS 99 is that it's Sarbanes-Oxley for everybody." SAS 99 applies to audits of privately help companies as well.

SAS 99 defines fraud as an intentional act that results in a material misstatement in financial statements. There are two types of fraud considered:
1) misstatements arising from fraudulent financial reporting (eg. falsification of accounting records) and;
2) misstatements arising from misappropriation of assets (eg. theft of assets or fraudulent expenditures)

How to create SAS 99 report in Great Plains?

To comply with the auditor, I had to create a Crystal Report that would:
a) show historical GL data
b) create a parameter for historical year
c) include the Journal Entry Nbr, Account Nbr & Description, Source, Document Type, Transaction Date, Posting Date, Debit/Credit Amounts, Reference and User ID

SQL Tables and linking them:
Step 1
My main table was GL30000 then linked GL00105 by ACTINDX. Last table was GL00100 which was linked to GL00105 by ACTINDX as well.

Step 2
Drag all required fields to the report details including Journal Entry Nbr, Account Nbr & Description, Source, Document Type, Transaction Date, Posting Date, Debit/Credit Amounts, Reference and User ID

Step 3
Create your parameter using GL30000.HSTYEAR field then value type should be Number. Remember to create descriptions for each year since GP would year with comma likie 2,001.00 so you should use 2001(Description).

SQL Server 2005 vs 2000 Components

When customers received from Microsoft the latest CDs it included GP 9.0, FRx 6.7 and SQL Server 2005.

A lot of questions had been asked in forums about the difference between SQL Server 2000 and 2005. Below is a table which shows the components of both versions:

Component	SQL Server 2000	SQL Server 2005
Extract, transformation, and load	Data Transformation Services (DTS)	SQL Server 2005 Integration Services
Relational data warehouse	SQL Server 2000 relational database	SQL Server 2005 relational database
Multidimensional database	SQL Server 2000 Analysis Services	SQL Server 2005 Analysis Services
Data mining	SQL Server 2000 Analysis Services	SQL Server 2005 Analysis Services
Managed reporting	SQL Server 2000 Reporting Services	SQL Server 2005 Reporting Services
Ad hoc reporting		SQL Server 2005 Reporting Services
Ad hoc query and analysis	Microsoft Office products (Excel, Office Web Components, Data Analyzer, SharePoint Portal)	Microsoft Office products (Excel, Office Web Components, Data Analyzer, SharePoint Portal)
Database development tools	SQL Server 2000 Enterprise Manager, Analysis Manager, Query Analyzer, various other	SQL Server 2005 Business Intelligence Development Studio
Database management tools	Enterprise Manager, Analysis Manager	SQL Server 2005 SQL Server Management Studio

Microsoft MVP for Dynamics GP

I'm very proud to say that I've been awarded the Microsoft Most Valuable Professional (MVP) for Dynamics GP. I'm very passionate about this product and had been supporting it since 1996. I’ll be around the community and hopefully would be fortunate to see the next wave of transformation including the deployment of the merge product in the near future. My congratulations to the other awardees.

Below was the email I got from Microsoft:

Hello everyone,

I know you are all anxious to hear our new MVPs are effective today. Firstly, all existing Dynamics MVPs were re-awarded so there is no one leaving the fold J

So let us welcome the following to our community:

GREAT PLAINS
Duke DelPrado
Richard Whaley
Brenner Klenzman

CRM
Ronald Lemmen
Frank Lee

Navision
Ahmed Amini
Alain Krikilion

Solomon
Toni Savage

I also want to mention Will Hadley. Will was to be awarded an MVP in CRM but sadly Will passed away last week at a very young age.

Melissa Travers
MVP Lead | Microsoft Exchange Server,
ISA Server, Virtual Machine,
Windows Security & Dynamics
Tel. 980.776.9678| mtravers@microsoft.com
http://www.microsoft.com/mvp

No export DLLs found error in Crystal Reports

I've seen this error on a few environment I've worked with and is still seeing it in a couple of forums. Below is a link and the information from Business Objects website:

Link:
http://support.businessobjects.com/library/kbase/articles/c2006348.asp

Information:
An instance scheduled in a Citrix Metaframe or Windows NT Terminal Server environment fails on the Info Desktop with the following error:

"PEExportTo : Missing or out-of-date export DLL."

In the Info Report Designer, attempting to export the report results in one of the following errors:

"No Export DLLs found"

-OR-

"Missing or out of date DLL"

In the Info SQL Designer, attempting to export the query results in the following error:

"Export failed"

Why do these error messages appear?

Solution

These errors messages appear because Seagate Info cannot find the export DLLs in a Citrix Metaframe or NT Terminal Server environment. By default, Seagate Info expects the export DLLs to be located in %systemroot%\Crystal. On a Citrix or Terminal Server computer, the DLLs are located in the following directories:

• \Wtsrv\Profiles\\Windows\Crystal

• \Wtsrv\System32

When Seagate Info is installed onto a Citrix Metaframe or NT Terminal Server computer, the following file types are automatically installed to the \Wtsrv\Profiles\\Windows\Crystal directory:

• Database drivers (P2*.dll)

• Export drivers (U2f*.dll and U2d*.dll)

• User-defined Function Library (UFL) drivers (U2L*.dll)

(The only exception is the P2sodbc.dll, which allows for an ODBC connection to a database. P2sodbc.dll is automatically installed to the \Wtsrv\System32 folder. Thus, Seagate Info will find this file.)

As an example, if the user "Administrator" is logged on to the Citrix Metaframe computer when Seagate Info is installed, the Seagate Info DLLs will be placed in the \Wtsrv\Profiles\Administrator\Windows\Crystal directory. When logged on, "Administrator" is able to export from the Info Report Designer but another user is not because they do not have access to the export DLLs.


Alternatives to deploying DLLs for Seagate Info client tools
--------------------------------------------------------

There are three main methods of deploying the required Seagate Info DLLs on a Citrix Metaframe or Terminal Server computer so that all users will be able to access them. Place the DLLs in:

• \Program Files\Seagate Software\SI\X86 (This is the recommended solution.)

• \Wtsrv\Profiles\\Windows\Crystal

• \Wtsrv\System32

If you utilize the \Program Files\Seagate Software\SI\X86 directory, the Seagate Info DLLs must be copied to this folder once. This will allow all users to run the Seagate Info client tools and only one folder needs to be updated on the Citrix Metaframe or Terminal Server computer. Crystal Decisions recommends this solution.

If you use the \Wtsrv\Profiles\\Windows\Crystal directory, the Seagate Info DLLs must be copied to the profile folder of each user on the Citrix Metaframe or Terminal Server computer. This is the second preferred method for Network Administrators and is more advantageous than method the following method.

If you utilize the global folder \Wtsrv\System32, the Seagate Info DLLs must be copied to this folder only. However, most NT or Network Administrators do not prefer this method due to security reasons.

==============
NOTES:

• Even when using a PATH statement to specify where the DLLs reside, Seagate Info does not find these DLLs.

• When reinstalling Seagate Info or applying a product update or maintenance release, these DLLs must be copied to the appropriate directories again.
==============


Test Results
------------
Testing was performed using Seagate Info version 7.0.2.100 installed onto a Citrix Metaframe computer. Initially, the following conditions were presented:

· Log on to the Citrix Metaframe computer as user "Administrator".
· Configure the Seagate Info NT services; Info APS, Info Sentinel, and Info Agent, to run under a domain user account, Domain\User, which is a member of the local computer's Administrators group.

The three major tests that were performed were:

· How the Info Report Designer reads export DLLs on a Citrix Metaframe computer.
· How an Info Report Server reads export DLLs on a Citrix Metaframe computer when a report is scheduled and the Info Sentinel is running as an application instead of an NT service.
· How an Info Report Server reads export DLLs on a Citrix Metaframe computer when a report is scheduled and the Info Sentinel is running as an NT Service.

Test 1: How the Info Report Designer reads export DLLs on a Citrix Metaframe computer.

The Info Report Designer dynamically reads the DLLs successfully from the following folders:
· \Program Files\Seagate Software\SI\X86
· \Wtsrv\System32
· \Wtsrv\Profiles\\Windows\Crystal


Test 2: How an Info Report Server reads export DLLs on a Citrix Metaframe computer when a report is scheduled and the Info Sentinel is running as an application instead of an NT service. (Test completed while logged on to the Citrix Metaframe computer as the NT Service user account.)

With the Info Sentinel running as an application, at schedule time the Info Report Server only reads the DLLs from the following two locations:
· \Wtsrv\Profiles\\Windows\Crystal
· \Wtsrv\System32

The Info Report Server does not read the DLLs when they are located in the \Program Files\Seagate Software\SI\X86 directory. As a result, scheduled reports will fail.


Test 3: How an Info Report Server reads export DLLs on a Citrix Metaframe computer when a report is scheduled and the Info Sentinel is running as an NT Service.

With the Info Sentinel running as an NT Service, at schedule time the Info Report Server only reads the DLLs from the \Wtsrv\System32 directory.

The Info Report Server does not read the DLLs when they are located in the following two locations and as a result, scheduled reports will fail:
· \Program Files\Seagate Software\SI\X86
· \Wtsrv\Profiles\\Windows\Crystal

SQL Server 2005 SP1

SQL Server 2005 SP1 is now available for download. These packages can upgrade the Enterprise, Standard, Developer and Workgroup versions.

To download SP1 use this link:

http://www.microsoft.com/downloads/details.aspx?
familyid=CB6C71EA-D649-47FF-9176-E7CAC58FD4BC&displaylang=en

You can run the following query to check your build (this query will not run on SQL Server 200 or previous versions):

WITH Version(ver)
AS
(
SELECT SUBSTRING
(
@@VERSION,
CHARINDEX(' - ', @@VERSION)+3,
32
)
)
SELECT Build = LEFT(ver, CHARINDEX(' ', ver))
FROM Version

Microsoft Visual Studio 2005 Tools for Office

How close is Microsoft Dynamics to Microsoft Office? It's VSTO away.

A current GotDotNet project had started which gives users a collection of software programs for Microsoft Dynamics that snap in to Microsoft Office 2003. That's the good news, the bad news is nothing has been done for Dynamics GP. The current snap in were only developed for Dynamics AX and CRM.

The developers promised that snap in for Dynamics GP would be available soon. Snap is a set of add-in applications in Office that surface business data and processes. It uses VSTO to program the Office Object Model and Dynamics Business Connector to interact with the data in Dynamics without requiring any server side code or changes.

Snap in should not be mistaken for Information Bridge Framework (IBF). There are white papers available that could provide more in-depth analysis when to use IBF or VSTO, check this link:
http://msdn.microsoft.com/office/understanding/ibframework/
techarticles/default.aspx>

Microsoft acquires Proclarity

On April 3,2006, Microsoft annouced that it acquired Proclarity Corp., a software company that develops analysis and visualization technologies that work in association with Microsoft’s business intelligence (BI) platform.

SQL Server 2005 would gain more ground on Oracle with this acquisition. Proclarity brings in more than 3000 global customers. There is opportunity for PeopleSoft/JD Edwards customers using Proclarity to now look at Microsoft Dynamics as an alternative system, Oracle's Fusion should show some effective marketing to not lose dissatisfied customers.

Select Statements Part I

A favorite sql script of mine is the 'Select Count(*)' statement. You can use it to get the number of items, customers or vendors in Great Plains by simply running:

Select Count(*) from IV00101 (item master)
Select Count(*) from RM00101 (customer master)
Select Count(*) from PM00200 (vendor master)

You can also use it to check documents in sales or purchasing and include a where clause.

Select Count(*) from SOP10100
where soptype = '2' (to check count for open sales orders)

Select Count(*) from SOP30200
where soptype = '3' and
sopnumbe LIKE 'ORD%' (to check for posted sales invoices)

SQL Server Security - SQL Injection

For the past year I've been working on security policies and procedures. One of the most challenging part of security is knowing which technology and best practice to use. In my case, I tried all avenues including books, forums,etc. and asked relevant questions then worked myself from top to bottom, meaning from Policies to the procedures, then formulated a plan which included securing the SQL Server database.

One of more common security threat to SQL database is SQL Injection. This is a hacking method that allows an unauthorized attacker to access a database server then executes SQL queries. Once the attacker gains access to the database, he/she is then free to export, add, modify, or delete content from the database. If the hacker is very knowledgeable of other hacking tools, he can penetrate past the database server and into the operating system which could harm the whole system.

To secure your company data against SQL Injection attacks, administrators should implement the principle of least privileged access, granting users rights to part of the applications required for them to their daily work. If their sole purpose is to extract user information, limit permissions to a SQL account used by them to SELECT on target database objects. Another approach to limiting access rights is implementing application roles.

Unfortunately, Microsoft Dynamics Great Plains don't use application roles but uses Database Roles. One of the pain in Great Plains database design is the DYNGRP database role. Here's a classic example:

A company needs to create a new user for an external auditor who wants to access Great Plains, run Reports and do Inquiries. The best way to do this was to create the user in Great Plains, go to SQL Enterprise Manager-Security then open the user's Properties. When you go to the Database Access tab, you can mark db_datareader then the user can only do SELECT statements on the database BUT DYNGRP premissions override the db_datareader option. Being a member of DYNGRP gives users access to run all of the SQL commands and they need to be in that group to even be able to log into Great Plains.

The solution from Great Plains about this security flaw was either to modify the field level security on each window and remove access to the save button OR just give them access to Inquiry and Reports window only.

How about SQL Injection on web-applications? The solution is to build web applications and consider security during design, not post-release. Analyze the resulting error messages from malformed sql statements, ensure that you make them as generic as possible. Download the latest patches and use Microsoft Baseline Security Analyzer(MBSA).

GP Product Report III

PROBLEM REPORT:
The bug ID or problem report number is 18409. Title is 'The payment number is not reserved in Receivables Management' message. This applies to all versions of Great Plains eEnterprise including 9.0.

COMMENTS:
When you create a sales invoice from an order with Credit Card, Check or Cash payment and try to post the document, you'll receive an error in the sales edit list - 'The payment number is not reserved in Receivables Management'.

MBS would send you a fix by deleting & recreating RM00401 but it only worked once in my case. The solution would be to:
1) Remove the sales payment on the invoice then Save
2) Enter the same sales payment on the invoice then Save
3) Delete the General Ledger entries because when the sales payment was entered the first time the entries were also posted. If you post the sales payment distribution entries, this would create a duplicate entry

NOTE: If you're trying to remove a sales payment on a previous year, make sure you open the Financial & Sales module on that month/year.

Microsoft SQL Server 2005 System Table Map

You can find the poster of the SQL Server 2005 System Table Map in the December 2005 issue of SQL Server Magazine.

Here is the online version.