Knowledgebase
Home » Item #520
Upsizing CactuShop's Access database to MS SQL Server
Question
I would like to upgrade my CactuShop's Access database to MS SQL Server. How do I go about doing this?
Answer
At some point in a store's life you may feel that your database performance would improve if you upgraded your site to MS SQL Server. On most virtual hosting, we would normally suggest that you consider upgrading from Access to MS SQL if your site meets any of the following criteria:
- above 80,000 page views per month
- database file (.mdb) is larger than 25 MB
If your site is running slowly despite neither of the above, the web hosting is probably the suspect area and upgrading to MS SQL is not likely to have any noticeable effect.
There are two ways to upgrade.
If using the latest version of CactuShop, you can use the Data Tool (which is a free download from this site). The Data Tool lets you convert between Access, MS SQL and MySQL databases.
It works by copying data from one database (the source database) to a new one (the destination). So the first step to upgrade your data is to create the new destination database.
To do this, download the v6 MS SQL script from our downloads page and then run the data tool. Next select the 'Execute SQL' option. You will need to enter the details of your MS SQL database. Enter '1' as table prefix. Then you can select the script and run it to create the MS SQL tables required, and populate them with our demo data.
If you encounter an error Could not find stored procedure 'ÿþ', see the following:
Could not find stored procedure 'ÿþ'
Once you have this 'destination' database setup and populated, you can run the data tool again and select 'Data Copy'. Enter the MS Access database as the source and the new MS SQL database and the destination. The upsizing procedure will copy data from the Access database to the MS SQL one and provide visual feedback on the progress.
Alternatively, you may prefer to use the MS Access Upsizing Wizard on the tools > database utilities menu within Access. This may be your only choice if your source database has been modified with extra fields or tables; the data tool might not work if this is the case.
If using the MS Access Upsizing Wizard, there are a few field changes you will need to make to your new MS SQL database afterwards. The wizard upsizes Access 'memo' fields to 'ntext' or 'text' types in MS SQL. You need to change these fields to type 'varchar' and specify an appropriate length. The maximum for varchars is 8000 characters, though we'd suggest making the fields a little smaller if possible.
If your site uses non-English characters, you may need to use Unicode fields types instead, i.e. 'nvarchar' (note that because these support larger character sets like oriental languages, they use more space for storage and hence the maximum field size allowable is generally halved).
Whichever way you choose to upgrade your database, the last step is to edit the config.asp file and change the connection string so that your web site points to the MS SQL database. Comment out the Access strings and use one of the SQL ones (to comment out a line, just put single quote in front of it - this stops that line from being run and is used for putting comments into code). The 'trusted connection' version of the connection string can only be used if your database supports Windows authentication as well as SQL Server authentication. On most web hosts, this method won't work.
You will of course need to enter your own values for the database IP/address, name and password. Finally, if you have CactuShop v5.116 or earlier, you will need to change the numCursorType (cursortype in versions prior to v5) setting to 1, otherwise 'recordcount' commands will fail and the store will behave erratically.