|
SQL Administrator skills required to support Microsoft Great Plains
By Andrew Karasev
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 About the Author 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.
Article Source: http://www.simplysearch4it.com/article/5219.html
If you wish to add the above article to your website or newsletters then please include the "Article Source: http://www.simplysearch4it.com/article/5219.html" as shown above and make it hyperlinked. |
Some other articles by Andrew Karasev | SAP Business One Implementation for Multinational Companies with subsidiaries in Brazil SAP B1 has over 200 installations in Brazil at this time and the number of new customers is growing. In this article we would like ...
SAP Business One in Illinois: implementation, upgrade, customization, integration Chicago metropolitan area and downstate Illinois might be characterized as mid-west distribution place. This means that we see large number of diversified mid-size businesses, engaged in light assembly, shipping and receiving ...
SAP Business One Chicago Market Review, plus customization, integration & data conversion SAP B1 is becoming more popular in the USA, increases number of implementation each year substantially. Originally designed by one ...
Microsoft Dynamics GP Office Stack Integration advances – Microsoft Great Plains Dynamics GP is on the way of Microsoft Dynamics or former Microsoft Project Green advances. Great Plains Dexterity is still a core of fat client workstation plus it defines ODBC Dex database access, requiring ...
eConnect and Microsoft Dexterity: Dynamics GP development tools comparison notes This article is dedicated to Microsoft Great Plains eCommerce and web software development primarily, plus Microsoft Dynamics GP customization, integration, data ...
Microsoft Dynamics GP Dexterity Customization Microsoft Great Plains software development, customizations, reporting. Microsoft Dexterity programming as reliable alternative to newer technologies and platforms, such as .Net, C# and VB MS Visual Studio development, ...
|
|
| |