SBS - Installing an ACCPAC Database on SQL2005

This process will work for both new and existing MS SQL database setups on ACCPAC.

Note that the methodology is pretty straight foward for a new setup: The process finishes after one has run the ACCPAC Setup Wizard to configure the company.

Our primary focus in this post is a very long struggle to get an existing ACCPAC client migrated to a new SBS installation that upgraded a peer to peer setup.

1. Create SQLUser in ADUC with a static password and no Exchange attributes
2. Create SQLUser2 in ADUC with a static password and no Exchange attributes
• Make sure to follow the SBS password security guidlines for the client's setup when creating the user passwords for these accounts.
• Note that their passwords must be set to not expire.
• Depending on the client configuration, further restrictions on these users can be initiated via OU and Group Policy Objects.
3. Create SQLUser in SQL Server Management Studio --> MySBS --> Security--> Logins and tie it to MySBSDomain\SQLUser
4. Create blank MYCSYS ACCPAC system Db in SQL2K5 with SQLUser as owner
5. Double click on the above created SQLUser in SQL2K5 manager and set MYCSYS as the default Database
6. Create blank MYCDAT ACCPAC data Db ini SQL2K5 with SQLUser as owner
7. Create SQLUser2 in SQL Server Management Studio --> MySBS --> Security--> Logins
8. Right click MYCSYS database and Properties
1. Permissions
2. Click on MySBSDomain\SQLUser
3. Verify "Connect" is checked
9. Click + MYCDAT (repeated for MYCSYS too) --> Security --> Users --> Right Click in the white space under the user list on the right and "New User"
10. User name: SQLUser2
11. Login name: MySBSDomain\SQLUser2
12. Set the Default schema to "dbo"
13. Leave Schemas owned by this user blank
14. Set "db_owner" under Database role membership
15. Click OK (just to repeat: do this for both MYCSYS & MYCDAT database Users)
• We now have two blank databases to work with that are ready for the ACCPAC setup wizard.
• If a mistake is made, all we need to do to start over is delete any tables under MYCSYS and MYCDAT --> Tables (click on the first table - NOT THE SYSTEM TABLES FOLDER - then SHIFT + the last table entry, right click and Delete.
16. For new installations, run the ACCPAC setup wizard and input all of the relevant dates, data, permissions, and users with their passwords.
• Note that there will be other setup steps that will need to be accomplished such as setting up security on the ACCPAC Database Setup console that are beyond the scope of this post.
17. For existing installations, keep things relatively simple: set the date as 1/1/200x, currency type, and then Next repeatedly until the process runs through creating the database tables.
18. Click on ACCPAC Database setup and both MYCSYS & MYCDAT will show up.
19. Run the ACCPAC Database Load utility to recreate the company data from the last backup made before the last server was downed.
20. If the Database Load utility somehow does not get all of the up to date data installed, the fix is very simple: Stop the default SQL server instance and copy the following files out of the old ACCPAC data directory:
• MYCDAT.mdf
• MYCDAT_log.ldf
• MYCSYS.mdf
• MYCSYS_log.ldf
21. Restart the default SQL instance service
22. Logon to ACCPAC on the server as the data's admin.
23. All should be good to go from there to install the software on the workstations and connect to the server.
• Note that the ACCPAC Database Setup utility on any workstation will muck up the server settings if the MYCSYS and MYCDAT entries are modified/deleted!
24. ODBC System DSN settings should be taken care of by the ACCPAC install.

After we were finished the above setup steps, we had our client's ACCPAC installation fully functional on the SBS 2003 Premium server with MS SQL 2005 SP1 installed.

If need be, for larger firms, we can initiate the SQL 2K5 install routine to install a dedicated SQL2K5 instance for ACCPAC's purposes prior to performing the above steps.

Philip Elder
MPECS Inc.
Microsoft Small Business Specialists

*All Mac on SBS posts are posted on our in-house iMac via the Safari Web browser.

Sage Accpac ERP - Version Mismatch Error - Refer to Solution ID 22331

A recent SBS Premium setup needed to have Sage Accpac ERP installed and configured with a SQL backend on the server.

This setup came after the fact.

The actual setup of the databases, connectivity, DSNs, and more will be in a separate post when we have a little more time.

For now, we had the following error on every workstation:

Sage Accpac ERP

Version mismatch. Two Microsoft support files (SQLSRV32.DLL and ODBCBCP.DLL) are different versions. Please refer to Solution ID 22331 for more information

A search for said Solution ID turned up nothing.

Since we were working with a SQL 2005 installed database, the thought was that something within Accpac was expecting an older SQL version, or some files were updated while others were not.

So, we searched the server and found the following files:

• SQLSRV32.DLL = Version 2000.86.1830.0
• ODBCBCP.DLL = Version 2000.86.1830.0

Since the versions on the server were seemingly the same, we moved onto the workstation and found the following:

• SQLSRV32.DLL = Version 2000.85.1117.0
• ODBCBCP.DLL = Not Found

We made sure to shut down Accpac, then we placed a copy of the SQLSRV32.DLL from the server on a network share.

Both files were found under %windir%\System32 on the server.

We copied the SQLSRV32.DLL off the share to the same %windir%\system32 location on the workstation after making sure Accpac was shut down properly.

We then tried to run Accpac and received the same Version Mismatch error.

So, we copied the ODBCBCP.DLL file off of the server to the same share location. We then copied the file from the share to the %windir%\System32 on the workstation.

We fired Accpac up again, logged on to the company file and we were connected successfully.

Here is a screen shot of what both the server and the workstation file compare after completing the file copy to the workstation:

Correct file versions on both the Server and Workstation

By the way, while searching for the solution, we came upon one so-called solution site promising a video of it. We were invited to install an ActiveX component before we could view the video though. We were not permitted to close the Tab that the site was in nor were we permitted to click the X to close the browser. That browser and its tabs could only be shutdown via the Task Manager and an End Task. As always, Internet "Street Smarts" are important!

Philip Elder
MPECS Inc.
Microsoft Small Business Specialists

*All Mac on SBS posts are posted on our in-house iMac via the Safari Web browser.

Accpac Workstation Setup: BTrieve Error 35

We were migrating an existing peer to peer network to a SBS Premium based domain.

When we had most of the bugs ironed out, but the one that had us struggling the most was this error when we ran the Accpac client on the workstation:

BTRIEVE file directory is invalid(btrieve error 35)

This error had absolutely no indication as to the cause.

By this time, it was getting quite late, and the answer just did not seem to be forthcoming in our Web searches.

So, we took a break from it. The following day, we were able to finally discover what was causing the problem: We had setup the ODBC Datasources using the mapped network drive for the setting in the ODBC Administrator.

We needed to change the settings to the UNC path: \\mysbsserver\accpac

That fixed it.

A huge thank you to NextLevel Information Solutions for sharing their fix for the problem. It is a little over half way down in the FAQ.

Philip Elder
MPECS Inc.
Microsoft Small Business Specialists

*All Mac on SBS posts are posted on our in-house iMac via the Safari Web browser.