SQL Administrator Skills Required to Support Microsoft Great Plains

Microsoft Great Plains is becoming more and more popular and we see new purchases among midsize and large corporations, where, in our opinion, Microsoft SQL Server DB administrator position is a must. If you are database administrator and you are part of the decision making team, purchasing Microsoft Business Solutions Great Plains for your corporation, we would like to set your expectation on the level of SQL knowledge, skills and experience needed to support Microsoft Great Plains in-house

? Data querying. Microsoft Great Plains has decent ReportWriter, which allows users to create and modify existing reports, however the recommended tool, and you will soon understand that if you need really challenging reports (Sales Commissions, Bill of Lading, Sales Order Status with Purchasing info, Regional Sales, etc.) ? you need to deploy Crystal Reports or Crystal Enterprise. Crystal Report itself is usually based on the view or stored procedure. So ? you should expect to be familiar with Great Plains tables architecture and be capable to create select statement with multiple joins and unions

? Data Repair. You should be ready to save GP table prior to data repair: select * into SOP10100_Copy from SOP10100 and then use update statement to fix incorrect values. If you need to load SOP10100 back from the backup table ? first drop DEX_ROW_ID column and then use: insert into SOP10100 select * from SOP10100_Copy.

? Backup and Restore. Great Plains has DYNAMICS database which stores system info, such as users, security, companies, etc. and each company has its own database. You should be able to restore mostly company databases from the backup. This seems to be trivial routine.

? Technical Support. When you have issues with posting or need help from Microsoft Business Solutions Technical Support, you place case through customer source and you should expect the technician provide you with SQL scripts to fix your data.

? Some Useful Scripts. You need to be able to delete locked user: delete activity where userid='TOM', also very popular is batch recovery script, you need to unlock the batch in SY00500 table: Posting Definition Master

? Do not expect. Replications (except probably snapshot) ? it adds GUID column and then Great Plains Dexterity runtime can not upgrade the modified tables. Referential Integrity is not used, Varchar or NVarchar are not used ? the reason is ? Dexterity architecture, which was initially designed with database independence in mind. Logins and access management ? Great Plains uses its own security, which is translated into SQL logins with encrypted password.

? Moving Great Plains on the new SQL Server. It is often confusing for the DB Admin. Besides physical move through backups or detach/attach DB you need special scripts to transfer GP security and users logins, these scripts are available through MBS tech support or feel free to call you MBS partner.

? Advanced Techniques. If you need to notify your GP users on the specific event in GP ? such as invoice was paid, you can deploy SQL emailing: xp_send_mail. This procedure could be called in the trigger for the apply payments table.

? Data Integration. XML inbound and outbound streams for the integration stored procedures is becoming defacto standard. Also you should be familiar with heterogeneous SQL queries: OPENROWSET and linked server conceptions.

Happy implementing! You can always appeal to us to help you with your system. Give as a call 1-630-961-5918 or 1-866-528-0577, help@albaspectrum.com

Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies ( http://www.albaspectrum.com ), Microsoft Business Solutions Great Plains, Microsoft CRM, Navision, Microsoft RMS, Microsoft Business Portal customization company, serving clients in Chicago, Boston, New York, Miami, Atlanta, Houston, Dallas, Denver, Los Angeles, San Francisco, San Diego, Seattle, Minneapolis, Phoenix, Toronto, Montreal, Brazil, Mexico, UK, Australia, Canada, UK, Europe, Russia an having small offices in multiple states and internationally.

---

The Religion And Philosophy Of Small Internet Business

I have always had a tendency to focus on the... Read More

An Easy Way to Develop JAVA Enterprise Applications

Research bears that less than 70 percent of development projects... Read More

Backing Up And Restoring Your MySQL Database

If you've been using MySQL database to store your important... Read More

How to Build a Basic CSS Layout

Designing without tables by using CSS layouts is fast becoming... Read More

Programming Language Migration Path

While I was preparing some personal background information for a... Read More

Groupware: Avoid the Ad Hoc Shuffle

GroupwareEfforts are continually made to manage the unavoidable ad hoc... Read More

Selecting Corporate ERP: Microsoft Business Solutions Great Plains ? Estimation Parameters

As we could imagine, if you are reading this article... Read More

Microsoft CRM Implementation - US Market Lessons

Microsoft CRM is CRM answer from Microsoft Business Solutions. If... Read More

How to Get The Best Accounting Software For Your Small Business

Buying accounting software is a major investment. It's an important... Read More

Great Plains Customization Upgrade? Overview For CIO/IT Director

Around the same time Microsoft made its move with .Net... Read More

Free Software for Newbies and Web Developers

Here is some free software tools to help you build... Read More

Groupware as a Document Manager: Collaboration Series #3

This article is the third of a series of articles... Read More

eConnect: eCommerce Development for Microsoft Great Plains

Microsoft Business Solutions Great Plains has several options to enable... Read More

Microsoft CRM Conversation Gateway: VoIP - Implementation & Customization

Microsoft CRM is winning market share step-by-step from such the... Read More

Cross-Platform Custom Software Development & Integration ? IT Strategy for Large Corporation

Microsoft Business Solutions products: Great Plains, MS CRM, Navision, Axapta,... Read More

Dont Choose Adobe When Working With PDF

While Adobe is the most known maker of PDF tools,... Read More

Mapping Software: Putting Data Visualization on the Map

Mapping Software Improves Data VisualizationFrom the outset, it is important... Read More

Is Your Family Protected?

When you think... Read More

The True Meaning of Freeware

The vast majority of us will have, at some point,... Read More

What to Do if All Screensavers Fun is Grayed Out?

Finally, you have some time to personalize your desktop with... Read More

Beware of Spyware

One day, you suddenly realize that your computer started to... Read More

Microsoft Business Solutions Partner ? How to Launch New IT Consulting Practice

In the new era of internet marketing the problem of... Read More

Corporate ERP Selection: Microsoft Great Plains

In our opinion, traditional approach when you select ERP/MRP system... Read More

Defining OLAP Solutions and Data Warehouse design

This tutorial covers OLAP solutions used by Data warehouses and... Read More

Microsoft Great Plains Distribution, Barcoding, Consignment ? overview for consultant

Microsoft Great Plains ? ERM from Microsoft Business Solutions and... Read More

Behave, Word, Behave!

If you copy something from a Web site or elsewhere...... Read More

Online PowerPoint Presentation ? Convert PowerPoint to Flash

Although we don't know whether Microsoft ever envisioned such a... Read More

Microsoft Great Plains Integrations - Tips for Developer

In this short FAQ style article we would like to... Read More

Pros and Cons of Using FREE Software in Your Business

Itâ??s easy to understand why you might be drawn to... Read More

25 Things Mapping Software Can Do For You

1. With mapping software you can create a report that... Read More

Bridging the Gap between Paper and Data

The cornerstone of successful automated office systems is the ability... Read More

Bill of Lading ? Custom Reporting for Microsoft Great Plains ? Overview for Consultant

Bill of Lading is required report for Logistics and Freight... Read More

Microsoft Great Plains: Annual Enhancement Program ? How To Be Re-Enrolled

Microsoft Business Solutions Great Plains is mid and even corporate... Read More