MS Access to SQL Server gone bad - please help!

6

68shelby

Perch
large Access 2000 database is causing problems on the CFMX server, so JH converted it to MSSQL (using DTS) and now the website is broken & I'm catching alot of heat.

I did not design the site or DB and I'm not a DBA. The company webmaster is scrambling to correct the issues caused by the migration/conversion because portions of the website are now broken. The errors appear to be related to writing to the DB versus reading from it. Here are two sample errors:

ERROR MESSAGE: The Primary key for table "xxx" was not found in the datasource "xxx". This error may be caused by not specifying the table name in the format that the database server expects.

Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword " "

Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver][SQL Server]Line x: Incorrect syntax near '#'


I'm convninced that errors #2/3 are related to something in CFM, but #1 is definitely a MSSQL issue. Perhaps this is a common post-migration problem with a simple solution (wishful thinking). One JH user recommended a database management tool called "EMS SQL Manager for SQL Server Lite". Although I have been successful logging into the SQL server, I am quickly discovering that it does NOT allow me to backup or restore. This is a problem. How can we correct the errors above if we can't pull the SQL DB down, edit it and re-upload it? Is a tool like this simply intended to modify data directly on the server? Without a backup and the abilty to restore quickly, this seems a little risky.

Can someone please give advice on the *easiest* way to fix the error(s) above? If so, please explain it to me like I'm a 5-year old because SQL is way beyond my level of expertise. Thank you. ~68shelby
 
I have some CF experience and did a quick Google search. Here's a thread talking about the same thing:

http://www.mail-archive.com/[email protected]/msg168459.html

Not that it helps much but it seems that your application unfortunately uses the CFUPDATE tag which can be a time saver for the developer (rather than writing his own SQL) but it doesn't give much control and relies on CF figuring out what the primary key is for the table. It may just be that the table does not have a primary key. Can you find the CF code where that error happens so you can see what table it is? Perhaps if you describe that table then someone might be able to help.

SQL Enterprise Manager or, the newer, "SQL Server Management Studio" is the tool you really need to fiddle with the database. I'm not sure how you legally get that without owning SQL Server. I don't think you get it with the free SQL Express download. I'm not familiar with SQL Server Lite. There are web based alternatives which I've heard are fairly limited but probably good enough for your purposes. I think there a web based SQL management app within HSphere.

Cheers
Ross
 
You can still legally get Enterprise Manager with any of the many instructional books that have a demo copy of SQL2000 on CD(s) with the book. These books should now be drastically reduced in price at your local book store or try Amazon.com.

You really should not be doing development work on production servers anyway.:)
 
Tetranz, thanks for the CF link. There seems to be some good info there (I searched for more), but I didn't find anything specifically related to the error "This error may be caused by not specifying the table name in the format that the database server expects. (For example...SQL Server, the format is "databasename.username.tablename", such as in customers.dbo.orders).

I think it may may actually be a CF query coding problem. We'll see.
 
phpmyadmin is a central install, MSSQL online manager can not work on CF servers becasue CF is not allowed and for some reason it wants to install it on every clients site(something I never understood their reasoning for)
 
still having some problems...

I asked JH to convert an Access database (~60MB) to MSSQL (via DTS) and I ended up with a 396MB .BAK file! I resubmitted a ticket and asked what happened and they responded with "ok, we fixed it". Now the SQL database is 450MB+.

I asked again what happened, and they told me "...we did convert it through DTS, if you have any doubt you can do at your end. Just download your ms-access database at your local machine and convert it at your end."

Three things:
One, if I had SQL Server running on my end, I wouldn't be asking them to do it for me. Two, when JH converted the DB for me two weeks ago (for the 1st time), the SQL database ended up ~10MB larger than my original Access db. Three, is it "normal" for MSSQL to be so bloated and upsize the database 6X it's original size? What's happening? Thanks ~68shelby
 
You must log in or register to reply here.
Back
Top