• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 925
  • Last Modified:

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

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
Bonnie_T
Asked:
Bonnie_T
  • 4
  • 2
  • 2
  • +3
1 Solution
 
yatin_81Commented:
select serverproperty('productlevel')
0
 
yatin_81Commented:
Sorry.... ignore the above code. Use this code instead :

select databaseproperty('DatabaseName','version')

Open in new window

0
 
rmm2001Commented:
Also SELECT @@Version
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Bonnie_TAuthor Commented:
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
 
rmm2001Commented:
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
 
Steve EndowMicrosoft MVP - Dynamics GPCommented:
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
 
Anthony PerkinsCommented:
What you are looking for is a server property and not a database property:
SELECT SERVERPROPERTY('ProductVersion')
0
 
Victoria YudinCommented:
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
 
Bonnie_TAuthor Commented:
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
 
Bonnie_TAuthor Commented:
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
 
Bonnie_TAuthor Commented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now