{"id":500,"date":"2013-03-11T13:22:41","date_gmt":"2013-03-11T18:22:41","guid":{"rendered":"https:\/\/danvanfleet.com\/?p=500"},"modified":"2013-03-23T16:08:45","modified_gmt":"2013-03-23T21:08:45","slug":"softpro-proform-standard-mdb-to-sql-upgrade","status":"publish","type":"post","link":"https:\/\/danvanfleet.com\/index.php\/softpro-proform-standard-mdb-to-sql-upgrade\/","title":{"rendered":"SoftPro ProForm Standard MDB to SQL upgrade"},"content":{"rendered":"

There are things that need checked after converting a database from Access to SQL with SoftPro<\/a> Standard.  I’ve been through a couple of these, there are things to watch out for. Once the data is converted and with each database showing no unfixed issued during the conversion process, there are some additional items that need done, or checked.<\/p>\n

<\/p>\n

 <\/p>\n

\"image\"<\/a> First the File Numbering Manager is may be reset to the default of something like yySSSSS, Change that in SPAdmin under Preferences, to your previous file numbering format. <\/p>\n

 <\/p>\n

The other things under Preferences are normally set correctly but they are worth mentioning. \"image\"<\/a> First verify the Directories are set correctly.<\/p>\n

If you don’t know what they should be set to, here’s what I know.  A default installation will have everything except Data Files Directory set to your SoftPro<\/a> Shared Folder. If you have a consistent mapped path as the example shows, use that mapped path.  Otherwise you’ll be entering a UNC Path, something like \\\\SBS2011\\SoftPro<\/a>\\<\/a> works fine. Many times this is a UNC path, which I highly recommend not using for Data Files Directory.<\/p>\n

 \"image\"<\/a> <\/p>\n

Make sure the Default Template and GFE Template are properly set. <\/p>\n

Verify the other options are as you expect them to me.  The sample is how I expect it.<\/p>\n

Next Click on Global Preferences<\/p>\n

\"image\"<\/a> <\/p>\n

If Default to GFE Files in 2010 Should be selected, be sure it is.  This was the second thing that was missed during the most recent conversion.  If you use ProScheduler check that setup screen, my clients tend to use ProCal<\/a> an automatic calendaring system for ProForm which we built. It’s pretty cool, it even has merge to email.<\/p>\n

This next item won’t apply if you don’t run Pro1099.  If you do, there is some information \"image\"<\/a>that needs updated in Pro1099. <\/p>\n

Start Pro1099 and navigate to the Tools -> Preferences window. If it’s blank like this example, fill it out, then we need to update a database field.<\/p>\n

 <\/p>\n

Start Microsoft SQL Server Manager<\/p>\n

\"image\"<\/a>Navigate to your SoftPro<\/a> instance, or the default instance, then to the Pro1099 table and finally the P1WinSetup Table<\/p>\n

Select Edit Top nnnn Rows, <\/p>\n

\"image\"<\/a> <\/p>\n

Notate the column HighLockFile, if it’s 00000001 it needs updated.<\/p>\n

To determine the proper value Click on New Query \"image\"<\/a> <\/p>\n

 <\/p>\n

Put this in the Query Window<\/p>\n

Use Pro1099
Select LockFile from P1Win order  by P1Win.lockfile Desc <\/p>\n

\"image\"<\/a> <\/p>\n

  Click Execute \"image\"<\/a> <\/p>\n

\"image\"<\/a> <\/p>\n<\/p>\n<\/p>\n<\/p>\n<\/p>\n<\/p>\n

And the Query results will show the lock file column with the highest one at the top.<\/p>\n

That is the value that we need in this case it is 000002PR.  So right click on that and select Copy<\/p>\n

\"image\"<\/a> <\/p>\n

 <\/p>\n

Now we have the value we need in the Copy Paste buffer.  Select the P1WinSetup Table and paste the value in the HighLockFile column’s only row, or most current row as described by the VersionDate Column.<\/p>\n

\"image\"<\/a> <\/p>\n

 <\/p>\n

Usually there is only one row.<\/p>\n

 <\/p>\n

An issue here will show itself as a Pro1099 Error (P1WIN-042) Violation of Primary Key constraint ‘Pk_P1Win”. Could not insert duplicate key in object ‘dob.P1Win’. TheSelec duplicate key value is (00000001)<\/p>\n

\"image\"<\/a> <\/p>\n

Update the HighLockFile column to fix.  <\/p>\n

ProTrust also has a HighLockFile and may experience the same sort of error message.<\/p>\n

\"image\"<\/a>Run the following SQL Command<\/p>\n

Use ProTrust
Select lockfile from PtWin order by PTWin.lockfile Desc <\/p>\n

To find the high lock file.<\/p>\n

Click Execute as above to see the Results.<\/p>\n

Place the highest High Lock from the  query in  PtWinSetup’s HighLockFile column of the most current row. This one wasn’t on today’s table of issues, I’ve seen it before and wanted to mention it.<\/p>\n

So those are some of the issues that we’ve run across when updating ProForm Standard 12.2 from using Microsoft Access data tables, to using Microsoft SQL data tables.  We’re not using ProIndex, I expect the same sort of issue HighLockFile to exist there also.  One of the beautiful things about ProForm is the consistency of the product.  Select, Enterprise, Standard all follow a consistent pattern, clearly Select has it’s own pattern, and is more consistent, but they all duplicate thoughts well across themselves.<\/p>\n","protected":false},"excerpt":{"rendered":"

There are things that need checked after converting a database from Access to SQL with SoftPro Standard.  I’ve been through a couple of these, there are things to watch out for. Once the data is converted and with each database showing no unfixed issued during the conversion process, there are some additional items that need […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[3,23],"tags":[6,21,8],"class_list":["post-500","post","type-post","status-publish","format-standard","hentry","category-general","category-softpro-2","tag-error","tag-proform","tag-softpro","entry"],"aioseo_notices":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p4wyVb-84","jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/posts\/500","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/comments?post=500"}],"version-history":[{"count":3,"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/posts\/500\/revisions"}],"predecessor-version":[{"id":537,"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/posts\/500\/revisions\/537"}],"wp:attachment":[{"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/media?parent=500"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/categories?post=500"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/tags?post=500"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}