home Links Articles Books Past Meetings Photos SiteMap
The MDCFUG is sponsored by TeraTech. Visit us at www.TeraTech.com

Please send
comments/questions to

michael@
teratech.com

 
 
Practical CF:
Upsize
from MS Access to SQL Server

by Eron Cohen and Michael Smith
TeraTech http://www.teratech.com/
 

 
Afraid to upsize to SQL Server? Don't be -- we will show you how step-by-step!
 
If you are using ColdFusion with Access today then it is likely that you will be using it with SQL Server tomorrow. Luckily, Microsoft has provided some tools that make upgrading to SQL Server a relatively easy process. You'll be surprised to learn how simple it is to upgrade and how easy SQL Server can be to work with once you're done. This article will cover the basics of using these tools and will help you to easily plan for a database upgrade. You'll learn when and how to upgrade, and how to work with the Microsoft Access 2000 "front end" to SQL Server to make your transition as painless as possible.
Knowing when to upgrade
There are lots of reasons to upgrade from Microsoft Access to SQL Server. But the most important reason is a simple one: many of us are discovering that MS Access really isn't a stable platform when working with ColdFusion. In my experience, I've found that sites become more stable and easier to work with as a result of the upgrade to SQL Server. The fact of the matter is that Access is not really meant to be a client/server database, which is in effect the way you use it when you connect it to the web with ColdFusion. Furthermore, both Allaire and Microsoft themselves point out that the database should not be used in this way. This may come as a surprise to ColdFusion sites that have used Access successfully for years. But those sites probably have limited amounts of traffic or don't do many online updates. In my experience, I've found that sites become much more stable and easier to work with as a result of the upgrade. And they can run faster too!

So how do you know when to upgrade? First and most importantly, if you find your website/webserver is unstable, very often the trouble has to do with using Microsoft Access. Before pointing your finger at Access, however, be sure to check a few things. For instance, be sure you are using CFLOCKs wherever necessary…especially around reading/writing session/application variables and any unthreaded CFX/ COM objects. Next, check to be sure you've got the latest Microsoft Data Access Components (MDAC) from Microsoft's website: www.microsoft.com/data. Be careful to follow Microsoft's directions if you do upgrade, as installing the MDAC can be tricky. Especially make sure you stop every service in your service's control panel…otherwise you'll end up with a zombie installation of the MDAC (ie, from the land of the living dead)! If you are already using the latest MDAC and you're still experiencing inexplicable server crashes, check your ColdFusion server logs. Look and see if you have any Windows NT 232 errors, which are often result of problems caused by using MS Access. If you have lots of these, then you'll know its time to move to a better database.

What SQL Server improves over Access
There are also "positive" reasons to upgrade. First off, if you plan to have multiple servers sharing the same database, you should really upgrade. Although it is possible to share an MS Access database using UNCs, its strongly discouraged for performance and security reasons. Furthermore, with proper configuration, multiple servers in different physical locations can share the same SQL Server database. For instance, if your company has a ColdFusion server in Los Angeles and another one in Tokyo, both of them could use the same SQL Server as a datasource. If you're a developer working remotely on a site, you'll definitely find SQL Server the platform of choice. Instead of having to download the database every time you want to change or add a new field, you can access the table structures remotely and change whatever you like…and not even disrupt the website while you're making changes. In other words no more errors because the table is "exclusively locked" by a developer making a change to a table or even just looking at it in design view. Furthermore, SQL server uses multiple processors and makes better use of your server's RAM to improve performance of your database queries. And let's not forget about all the great features SQL Server has that MS Access just doesn't even begin to address. For instance, SQL Server has the ability to use stored procedures and full text searching-both of which can dramatically improve the performance and functionality of your website. There are all sorts of other improvements and enhancements!

SQL Server is a more complete implementation of the SQL standard. From an application development point of view, you can now handle multiple schemas and complex security requirements. You have additional data integrity features, including triggers. You can choose among various concurrency modes to enhance performance. And there are additional tools that work with SQL Server. If you cut your programming teeth on C, there's an Embedded C interface where you will feel comfortable programming reports to look exactly as you want them to.

The Tools of the upgrade
Of course, before you can begin, you are going to have to have SQL Server setup somewhere. Setting up SQL Server is beyond the scope of this article. Simply installing the software is not all that difficult, but setting it up properly and securely certainly is quite intricate. If you're using an ISP or web-hosting provider, they will set it up for you and give you an IP Address and a username and password to use to get to it. If it will be set up on a machine in your office, hopefully you already have a network administrator or someone experienced with SQL Server to help you get it going. Be sure and keep up with all of the service packs that come out for SQL Server. (Currently Microsoft is shipping Service Pack 2.)
Assuming you already have SQL Server setup someplace, you'll need some sort of "client" software to administer it. On the most basic level, you could use CFQUERY through ColdFusion to issue commands to the SQL Server. This is definitely a viable way to communicate with your database, but not the easiest way.

Enterprise Manager and other SQL Server Tools

The second way to administer with the database is to install the SQL Server client software on the machine you'll be using to develop your websites. This includes the SQL Server Enterprise manager, Import and Export Data Wizard and Query Analyzer. To install these, just take the SQL Server CD and put it in. The auto-menu will pop-up and offer to install SQL Server for you. When you choose to install it by clicking on "Install SQL Server 7.0 Components", you'll be given the option to only install the client software. Be sure and choose "local install" when prompted for that information. Also note that you DO need to reboot the computer after the install is complete, even though you will not be prompted to do so.

The tools will come in handy whether you choose to adopt this software as your main method of setting up your databases or not. For instance, the Query Analyzer is a real help in figuring out how to optimize your queries and where to add indexes to tune the performance of the database.

A common variant of this second way to administer SQL server is to have your ISP install these tools remotely and use PCanywhere to run them on a collocated machine at there.

Using MS Access Projects to Manage SQL Server
The third method is the one that I find to be the easiest and most convenient. You can use the Microsoft Access 2000 interface to access and manipulate SQL Server databases. Although the interface is not exactly the same as when you're using it with an actual Microsoft Access database, it is similar enough that you will be able to quickly get your bearings and get to work. To get started, you need to create a new "project" in Access. To do so, choose FILE > NEW. You will most likely want to choose Project (Existing Database) to allow you to gain access to a SQL Server database that is already in service. It might surprise you the first time you do this that you'll be immediately prompted to save an .ADP file. This file is the "project" file. It will be a small file that simply contains pointers and preferences for accessing the SQL Server database. Just point it to a convenient directory and continue from there.

Next you'll need to fill out the "Data Link Properties." The dialog box is depicted below. For #1, typically you'll enter either an IP address or a machine name to get to your SQL Server. This information will be provided by whoever setup your database. #2 will also depend on the way the database was setup. Once #1 and #2 are setup correctly, you'll be able to pull down the dialog box in #3 and select the database you want to work with.
To verify that you have typed the correct information you can click on the TEST CONNECTION button to see if it fails and to help you ascertain why. Reasons for the failure could be because of incorrect settings, bad passwords or because of the way the security on the SQL server is setup. Another possibility is because Access is trying to use the wrong protocol to communicate with the database. To determine this, open up the "Client Network Utility" which may be found in your SQL Server or MSDE program group. Be sure the correct protocol is selected as the default. For instance, if you are supposed to be using TCP/IP be sure the default network library is not set to Named Pipes or some other protocol.

After you press the OK button, something similar to the old, familiar MS Access 2000 interface will open and present you with tables from your SQL Server database. There are a few prominent differences that should be mentioned. First off, you will find that your QUERIES tab is replaced with "VIEWS". SQL Server's Views are along the same lines as queries in MS Access. Next you'll notice that the Database diagrams option is built right into the menu. You'll find the diagram builder is a definite improvement over the one that you'd normally use with MS Access, but it still is lacking. Finally, you'll see a new item "Stored Procedures." If the idea of Stored Procedures is a new one for you, you'll be glad to know something about them. They can speed up your queries tremendously by keeping precompiled versions of them waiting to run on the SQL Server. This way, you need only supply the values for a variable or two and POW! There's your data.

Using the new MSDE ("SQL Server Lite")
As a developer using SQL Server, you'll want to be aware of the "free" Microsoft Data Engine (MSDE). It is a new product that came out as free add-on to Office 2000. You can think of the MSDE as "SQL Server 7.0 Lite", because it works like SQL Server but is licensed for less demanding use. Installing it will put a mini-SQL server on your workstation. Once you have it installed, you will have the option of creating your databases using MSDE instead of the native Jet Engine that MS Access would normally default to using. From a ColdFusion developer's point of view, choosing MSDE instead of the Jet Engine will provide you a very easy upgrade path to SQL Server. In other words, when you create a database using the MSDE, it is actually already in the native SQL Server format. Moving your database design to a full version of SQL Server is a relatively simple matter and there is no danger of any compatibility problems in either the data or the SQL statements in your ColdFusion project.

MSDE is managed via MS Access, through an Access project (.ADP file). It does not come with it's own version of SQL Server Enterprise manager, or the other SQL tools, although if you happen to have a copy of Enterprise Manager on your machine, you can use it with MSDE.

The place to find a copy of the MSDE installer is on your Microsoft Office 2000 or Microsoft Access 2000 CD. It may be installed on Windows 95, 98 or Windows NT. To get started, run SQLSERVERSTP.EXE in the \SQL\INSTALL\SETUP directory. An install wizard will guide you through the process.

Once you've successfully installed MSDE, you'll find an extra icon in your system tray. It is a computer with a green arrow (if you see a red square instead, it means that for some reason the service hasn't started. You may need to reboot or simply right click on the icon and choose "MSSQLserver - Start"). In order to use the MSDE you will need to setup an Access Project as described above.

Summary of Part I
We have covered reasons to upsize your Access database to SQL Server, the tools you will use to do it and the "free" MSDE "SQL Server Lite". Next month we will explain in detail how to copy data to SQL Server and troubleshooting your upsizing project.

[ End of part I here ]

Last month we covered reasons to upsize your Access database to SQL Server and the tools you will use to do it. In this article we explain in detail how to copy data to SQL Server and troubleshooting your upsizing project.

Copying your data to SQL Server
The IMPORT AND EXPORT data wizard (a.k.a. the DTS Wizard - Data Transformation Services) certainly helps get you going with building tables in SQL Server. It takes an Access database (as well as many other database types!) and import its table structures and data to a Microsoft SQL Server. Since it uses a wizard format, its simple to use as long as you know what data you want to transform! To start the wizard choose START > MSDE > IMPORT AND EXPORT DATA or START > Microsoft SQL Server 7.0 > IMPORT AND EXPORT DATA.

On the first data-gathering screen, you'll be prompted for information about the datasource. Choose Microsoft Access from the drop-down menu for the datasource (or whatever is appropriate for you're the database you actually intend to import to SQL Server. You'll find there's quite a long list of possibilities!) Then select the appropriate .MDB file and type your username and password if necessary. When you are finished, click the Next > button to continue on to setup the destination database.

On this page, you'll most likely leave the destination database on the default "Microsoft OLE Provider for SQL Server." This is the selection you normally would use to import the MS Access database you chose on the previous page of the wizard into a SQL Server database. Next you'll need to provide the name of the server and the security information. Typically this information is provided to you by your system administrator or the by the web hosting company that houses your SQL Server. Once this information is correct you'll be able to select the actual database that you wish to import the tables into. If you have not already created a database to house your new data, choose from the Database dropdown menu. Avoid making the typical mistake of importing the tables into the MASTER database or one of the example databases Microsoft provides. You'll want to start off fresh if you can. A dialog box will open prompting you for the name and size of your database. Type the name and accept the defaults. When you press okay, the new database will be created. After this is complete, press next again. You'll be shown a lovely pictorial of the action that is about to be taken-namely importing the MS Access database into the SQL Server database. Choose the "Copy Tables from the source database" radio button and push Next.

Transformation
Now you'll be given a list of all the tables in your database. Notice that you will NOT be given the names of any queries or reports. Choose the individual tables that you wish to import or just press the SELECT ALL button. As you select the tables you'll find a field activates that will allow you to change the name of the destination table. Do so if necessary. There is also a Transform button. This will allow you to make changes to the table's structure as it is being brought into the new SQL Server table. If you do choose to transform anything, you'll want to be aware of the new datatypes that are available in SQL Server, but not in MS Access. There are some confusing things. For instance, a Text field in Access is similar to an NVARCHAR or VARCHAR datatype in SQL Server. On the other hand a Text field in SQL Server is like a memo field in MS Access. Please check your SQL Server references for more of the specifics of these datatypes and their exact characteristics.

On the last page of the wizard, you'll be able to select when the import should be executed. You'll probably want to choose "Run Immediately" and just choose NEXT>. Finally, press FINISH and watch the action. If anything goes wrong on the data import, the wizard will let you know what the problems were. You'll need to fix them in the Transformations and then retry the tables that failed. Referential integrity can complicate the process of retrying individual tables. If life becomes too complicated, you should consider dropping the referential constraints for the tables in question and them re-creating those constraints after the upgrade. When you are done I suggest you review the table structure in the design table view. You will probably want to at least add the primary key constraint by clicking on the key symbol to you key field and look at which fields allow nulls. You might also check the Identity box if your key field was AutoNumber in Access and pick some fields to index. It is a good idea to look at the data by right clicking on the table and doing the action open all records to make sure nothing went wrong.

Finding and Troubleshooting Upgrade Problems
After you've finished importing your data you'll need to thoroughly test all of your queries. There are a few differences between MS Access's version of SQL and the implementation that SQL Server uses.

Field and table names

  • Be careful what you name your fields and tables in MS Access: use only letters A-Z, digits 0-9, and underscores - no spaces or other funny characters allowed unless you want to "delimit your identifiers with square brackets []" (You don't)
  • Avoid reserved words for field names or you will get errors when you use them in SQL queries - for example DESC, AND, OR.

Field types

  • Logical fields in Access can be True, False or Null - the default equivalent bit field in SQL Server can be only 0 or 1. For this reason you may prefer to transform logical fields to integers instead. Also if you have any queries that compare a bit field to TRUE or -1 you will have problems.
  • AutoNumber fields are equivalent in SQL Server to an Identity field (a checkbox which you turn on when you set up a new field). Be aware that Identity fields in SQL server 6.5 had some problems and can be replaced by longs and your own next id code if you prefer.
  • Memo fields are converted to TEXT or NTEXT fields.
  • Access will auto trim text for fields that are too long while SQL server will give an error if you try to insert too long a piece of text. Check the length of the fields and use the left function to trim to correct length eg if the firstname field is varchar 30 then in your insert statement put VALUES('#left(firstname,30)#) instead of VALUES('#firstname#')
SQL and function differences
The syntax for some SQL statements, functions and operators are different between Access and SQL server:
Access SQL Server Notes
SELECT DISTINCTROW SELECT DISTINCT No duplicate rows
LIKE "cat" LIKE "%cat%" Wildcards is * or %
DELETE * FROM tablename DELETE FROM tablename Leave out the * when you are deleting rows from SQL Server
Now() GetDate() Current date/time
Instr() CharIndex() Searching a string
Ucase() Upper() Upper case
Lcase Lower() Lower case
Trim() Rtrim(ltrim()) Removing extre spaces
int(x) convert(int,x) Convert to integer
A & B A + B String concatenation operator
 
Changing SQL and Access queries
If you have ColdFusion Studio, use the extended search to find the places where these problems may occur and deal with them. For instance, the above-mentioned problem with DELETE * could be dealt with by simply doing an extended search for DELETE * and replacing it with DELETE. However be careful with function names that are in both Access SQL and ColdFusion, such as ucase() and #ucase()#, because you only want to change the Access SQL function to upper()! We suggest you carefully watch your ColdFusion application server log after switching over to find any issues you may have missed.

Access Queries become Views in SQL Server. To convert a query to a view cut and paste from the Access SQL view into SQL Server's View creation designer. Just be aware that unlike Access queries they can't use an ORDER BY clause - you will need to do the ordering when you run the view SQL in ColdFusion.The view designer can also be useful when you want to test some SQL from ColdFusion to find out why it has a syntax error. Alternatively you may choose to create a stored procedure for a query, especially if you need to parameterize the WHERE clause. Stored procedures also let you code multiple queries and procedural code such as IF statements inside SQL Server.

Summary
Upgrading to SQL Server may sound intimidating, but Microsoft has provided tools to ease your way. Many of SQL Server interfaces either look similar to Access or can be administered using Access as a front end. Upgrading to SQL Server is a big step, but the payoffs are well worth it for ColdFusion programmers. Plus the MSDE version of SQL Server is free with Office! As you get used to SQL Server, you'll find that it is superior to Microsoft Access in many ways, and you'll have an excellent addition to your resume!
Resources
General SQL Information http://www.sql-zone.com/
SQL Tutorial http://www.dbbm.fiocruz.br/class/Lecture/d17/sql/jhoffman/sqltut.html
Microsoft's SQL Server site http://www.microsoft.com/sql/
Sams Teach Yourself Sql in 10 Minutes by Ben Forta http://www.amazon.com/exec/obidos/ASIN/0672316641/
SQLServer 7.0 Books Online available for download from the following Microsoft Web site: http://download.microsoft.com/download/SQL70/File/2/Win98/En-US/SQLBOL.exe
 
Bio
Eron Cohen is ColdFusion programmer, MDCFUG speaker and author. Michael Smith is president of TeraTech http://www.teratech.com/ , a 11-year-old Rockville, Maryland based consulting company that specializes in ColdFusion, Database and Visual Basic development. Michael runs the MDCFUG and recently organized the two-day, Washington, DC-based CFUN-2k conference that attracted more than 750 participants. You can reach Michael at michael@teratech.com or 301-424-3903.

Home | Links | Articles | Past Meetings | Meeting Photos | Site Map
About MDCFUG | Join | Mailing List |Forums | Directions |Suggestions | Quotes | Newbie Tips
TOP

Copyright © 1997-2017, Maryland Cold Fusion User Group. All rights reserved.
< >