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.

---

Linux vs Windows Operating Systems

With so many Microsoft Windows related viruses, errors, and other... Read More

Microsoft CRM Implementation for Large Corporation ? overview

Microsoft Business Solutions CRM is now approaching the phase of... Read More

eConnect: eCommerce Development for Microsoft Great Plains

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

Microsoft Great Plains - Typical Problems And Fixes ? Overview For IT Administrators

How to delete the user? This is the first problem... Read More

Keep Your Software Simple! A Review of EditPlus

I like my software simple. If it's too complex or... Read More

How a Bug Challenges to a Software Professional

It is really interesting that a bug can create problem... Read More

Microsoft Great Plains SOP: Sales Order Processing

Microsoft Business Solutions Great Plains is marketed for mid-size companies... Read More

Reporting Tools for Microsoft Great Plains ? Overview For Developers

Looks like Microsoft Great Plains becomes more and more popular,... Read More

Microsoft CRM Integration With Microsoft Office Documents ? Overview For System Integrator

Microsoft CRM is CRM application, maintained and supported by Microsoft... Read More

Microsoft Great Plains Integration with Microsoft Access ? Overview for Developer

... Read More

Mapping Software: Putting Data Visualization on the Map

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

Microsoft CRM: Implementation, Customization, Support ? Consultant Overview

Microsoft Business Solutions CRM is present several years on the... Read More

Spyware Protection: A Must for Any Home Computer

Now that spyware is the single most dangerous threat to... Read More

Backing Up And Restoring Your MySQL Database

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

Navision Attain C/ODBC Crystal Report ? Customization Example

Microsoft Business Solutions Navision is main ERP application for European,... Read More

MySQL for Beginners ? How to Create a MySQL Database

Whether you are an experienced web programmer or a complete... Read More

Microsoft Great Plains: Getting New Users Licenses ? Annual Service Plan FAQ

Microsoft Business Solutions Great Plains, Solomon, Navision, Axapta, Microsoft CRM... Read More

Great Plains Custom Development: Dexterity, VBA, SQL, Crystal, eConnect ? Overview For Programmer

Microsoft Great Plains is main Microsoft Business Solutions accounting package... Read More

Causes of ERP Failures

ERP is the acronym of Enterprise Resource Planning. Multi-module ERP... Read More

CRM and Customer Life Cycle

Customer Relationship Management or CRM is a combination of enterprise... Read More

Exactly What Does FTP Stand for and What Can it do for Me?

FTP stands for "file transfer protocol". FTP is basically a... Read More

These Items Are A Must Before Making The Decision To Purchase Any Off-The-Shelf Software

1. What determines the software price? Is it Per Seat... Read More

Why Java RDBMS?

It is a well known fact that Java as a... Read More

Can Group Collaboration Software Meet Business Needs?

According to a survey conducted by InfoTrends/CAP Ventures entitled "Content-Centric... Read More

When is a Software Engineer Not a Software Engineer?

The title of "software engineer" has got to be among... Read More

What is Fleet Maintenance Software?

Simply put, fleet maintenance allows companies to monitor and maintain... Read More

40/sec to 500/sec

IntroductionSurprised, by the title? well, this is a tour of... Read More

Theres Gold in Your Websites Server Log

Many Webmasters have never bothered to view their website's server... Read More

PHP On-The-Fly!

IntroductionPHP can be used for a lot of different things,... Read More

Intro to UNIX Shells

A UNIX Shell is in simplest terms, a command line... Read More

Put Some Pizazz in Your Software Arsenal

The software giants don't do everything and don't always produce... Read More

Groupware and Version History: Collaboration Series #1

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

Who?s Watching What You Type?

If someone entered your home, uninvited and installed numerous cameras... Read More