Home
>
Software
>
SQL Administrator Skills Required to Support Microsoft Great Plains

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.

maid service near Northbrook ...

In The News:

Lima, Ohio, has installed 3,120 floating solar panels on its Twin Lakes Reservoir, projecting nearly $10 million in electricity cost savings.

Amazon's Alexa for Shopping now lets customers verify suspicious emails, texts and phone calls against the company's own communication records.

Lindsey Brooke Isaacs spent 13 days in jail after a Flock camera flagged her Dodge Durango near a fatal Florida crash that killed three people.

AI-powered smart grocery carts are linked to 32% higher spending, according to new research, as Instacart Caper Carts expand across U.S. stores.

A VPN encrypts your connection on hotel, airport and café Wi-Fi abroad, but streaming catalogs and live sports access still depend on provider rules.

Gmail's Verified Sender Program launches September 8, 2026, giving U.S. political committees a more reliable path to your personal email inbox.

Elroy Air's Chaparral can carry over 500 pounds and fly 450 miles without a runway, and the FAA just tested it under a new federal program.

Bedrock Robotics says its autonomous excavators are doing paid earthwork on active job sites in Texas and Nevada with no operator in the cab.

A dedicated banking computer isolates your financial accounts from malware and phishing, but passwords, SIM swapping and outdated software remain risks.

The OlloNi SS1 AI robot pet uses a 4K camera and facial recognition to learn your family, but its removable Red Heart memory module raises questions.

Nine identity theft warning signs range from unfamiliar credit inquiries to SIM swap attacks that can quietly drain your bank account funds.

Robot.com's R-dog uses articulated legs and wheels to climb stairs and deliver packages, while its built-in screens display paid ads along the route.

MyChart phishing emails mimic real patient portal alerts with fake lab results and malware tricks that target Windows users across the country.

The Fox News AI Newsletter covers the latest artificial intelligence technology advancements, including the challenges and opportunities AI presents now and for the future.

Google's Motion Assist places animated dots on Pixel screens to combat motion sickness in vehicles, but the Android 17 rollout remains limited.

Moving to a 55+ community can quickly expose your data to scammers and data brokers before you even unpack, putting your whole family at risk.

Amazon Prime Air drone delivery plans to reach nearly 500 U.S. cities by the end of 2026, but your yard and ZIP code may determine eligibility.

The FBI reports nearly $21 billion in cybercrime losses for 2025, up 26%, as AI scams make fake voices and identities more convincing than ever.

Uber teen accounts now feature live video streaming from the driver's camera, but encrypted recordings stay on Uber's servers for up to 14 days.

Router firewalls and software firewalls serve different roles in protecting your home Wi-Fi from unwanted traffic and suspicious connections.

A Chrome extension with 70,000 users and a 4.7-star rating began injecting fake update warnings after a threat actor acquired and weaponized it.

Amazon's Alexa+ rolls out free to Fire TV devices across the U.S., but hands-free microphones and stored voice data raise key privacy questions.

Meta's new teen accounts settlement sets a default two-hour daily limit on Instagram and Facebook, plus night mode and school mode restrictions.

The CareCloud data breach exposed Social Security numbers, banking information and medical records belonging to more than 3.75 million people.

Waymo reveals its custom 5-nanometer chip and dual computing systems designed to take over instantly if the robotaxi's primary computer fails.

SQL Administrator Skills Required to Support Microsoft Great Plains

Software