Solved

How do I identify the version (patch level) of an SQL company database in GP

Posted on 2010-11-29
11
855 Views
Last Modified: 2012-05-10
I applied a service pack to both my Production and test GP 9 installations (on SQL 2005 Express).  I then took a backup of a company database from production and restored it to my test server.  When I tried to start test GP the GP_loginerrors.log said that the database was version 90372 and the client version is 90385.  I could not open test GP until I had upgrade the restored table.   Since I patched both production and test everything should be at 80385.  We are not getting errors when opening GP on the production server.   How can I check the production server to make sure that the company databases updated correctly?  Is there a query to ask for version information of the company database?  
0
Comment
Question by:Bonnie_T
  • 4
  • 2
  • 2
  • +3
11 Comments
 
LVL 2

Expert Comment

by:yatin_81
ID: 34232873
select serverproperty('productlevel')
0
 
LVL 2

Expert Comment

by:yatin_81
ID: 34233041
Sorry.... ignore the above code. Use this code instead :

select databaseproperty('DatabaseName','version')

Open in new window

0
 
LVL 7

Expert Comment

by:rmm2001
ID: 34233117
Also SELECT @@Version
0
 

Author Comment

by:Bonnie_T
ID: 34233405
I don't think that is the right query.  All I get is SP3.  I want to know if the individual company databases are 90372 or 90385.
0
 
LVL 7

Expert Comment

by:rmm2001
ID: 34233444
Wait I told you wrong...that's to get the server info. Did you try @yatin_81 said?  
select databaseproperty('DatabaseName','version')

Open in new window

0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 18

Accepted Solution

by:
Steve Endow earned 250 total points
ID: 34234138
Hi Bonnie_T,

I believe that the Dynamics GP company version information that you are looking for is in the DYNAMICS database.

The following queries will show you the database version information for all modules that GP believes are installed in each company database.  

SELECT * FROM DYNAMICS..DU000020
SELECT * FROM DYNAMICS..DB_Upgrade

To see just the main Dynamics GP version, you can filter the query:

SELECT * FROM DYNAMICS..DU000020 WHERE PRODID = 0
SELECT * FROM DYNAMICS..DB_Upgrade WHERE PRODID = 0


As you pointed out, the caveat is that it is technically possible to restore a different / older version of a company database, in which case these version numbers in DYNAMICS would not really match the objects in the company database.  Unfortunately, I'm not aware of a way to determine the version number for a company database--it seems the version info is only stored in DYNAMICS.

As such, my current understanding is that GP doesn't really know the version of the company database.  It only looks at the records in the DYNAMICS database to see what version is recorded there.

I have seen several instances where certain module records in the DU000020 table were not updated to the current or proper version following an update or upgrade.  If that is happening to you, you could try manually updating the version in the table, but obviously that could just cover up a problem that occurred during a patch or upgrade.

If you do modify the version info in the Dynamics tables, if you receive any type of table errors while using GP, then that could indicate that some company database objects did not upgrade properly, in which case you may need to contact support to resolve those issues.

Let me know if that helps.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34234144
What you are looking for is a server property and not a database property:
SELECT SERVERPROPERTY('ProductVersion')
0
 
LVL 18

Expert Comment

by:Victoria Yudin
ID: 34234433
Steve Endow is exactly right, there is no way to tell what version/build of GP a GP company SQL database really is because this is not stored anywhere in the database itself.  The reason for this is that company databases in GP do not work independently.  They are all tied together by a central system database called DYNAMICS.  

While restoring company databases is a frequent occurrence for testing purposes, it looks like something has gone wrong in your case.  If what you are trying to do is create a copy of the production GP environment in the test environment, you may want to copy/restore all the databases, or at least the company database and DYNAMICS.  That is the recommended approach and a much safer bet typically.
0
 

Author Comment

by:Bonnie_T
ID: 34234478
After I restored the database copies to the test server if wanted to upgrade to 90385 (latest patch applied).  It has done that with all of the 3 copanies I restored so I have nothing left that would give me a differential.  I ran the database property query and got 611 for all of the test companies and all of the production companies.  That would tend to indicate that the patch was successful on all company databases.  But it is was, then why did the restored companies need to be patched again?  I will try the DU query.  Am I going about this all wrong?  Is there a better way to be sure that the patch worked on the production server?  When I go into GP on the production server it lists 90385 in the lower left corner.
0
 

Author Comment

by:Bonnie_T
ID: 34234515
When I restored the databases that were backups of the production database after the production database had run the service pack, then the backup copies should be at the latest service pack level. But the test server thinks that they were not.  It said that they were version 90372.  This is what they were before the patch.  That is why I am trying to confirm on the production server that the service pack was successfully applied to all company databases.  After the test server upgraded them again it was happy with them.  My production server is not complaining, but I am trying to get everything ready to upgrade to GP 2010, so I want to be sure that the GP 9 service pack did in fact work.
0
 

Author Closing Comment

by:Bonnie_T
ID: 34234592
Select * from Dynamics..Db_Upgrade where PRODID=0 appears to give me the results I was looking for.  Thanks to all  of you for responding
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Automatically creating a Trello card using data from a Microsoft Dynamics CRM record turned out to be an easy project that yielded great results.  Here's how I did this for an internal team at General Code.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now