SQL Server Import from Access issue

[Guest]

Greetings

Finally converting all DB's from Access to SQL Server (2005) CF9.

One issue I have encountered is that when using the import tool in SQL Server, the tables come in with no primary key, although they are designated primary keys in Access. I modify the row to establish the PK, it defaults to Datatype: int, and won't allow me to change that to uniqueidentifier.

When I run an ENTER INTO query (creating a new record) , it errors out because the ENTER INTO command is not creating a new uniqueidentifier, and that row will not allow a NULL value?

What am I doing wrong?

Thanks in advance-

sakonnetweb

[Adam Cameron.]

What am I doing wrong?

At a guess, you're asking a very MS-Access & SQL Server-specific question on a CF forum.

This is probably not the cause of your problem, but it could well impede you resolving it.

I don't mean to be overly obtuse (a bit obtuse, sure, but not overly obtuse), but you're severely limiting your chances of getting an expedient answer by asking questions in the wrong place.  You're basically relying on a CF developer who has migrated Access to SQL Server before, and who has had your problem.  That's gonna be a niche market.

On the other hand, if you asked on an Access or a SQL Server forum, you are going to be right amidst the community that specialise on this thing.

You've basically walked into a butchers shop and asked what shelf to find the potatoes on.

--

Adam

[Guest]

Actually, I have been using CF since it was 2 words (Cold Fusion version 3.1). In 2002, I attended an Advanced CF Development course in Boston when Macromedia was taking over the business from the Allaire brothers- which was a little over my head at the time, I must admit, but I got my certificate anyway.....

That being said, I have 50-60 Applications running in an CF/IIS/Access that I am finally converting to CF9/IIS/SQLSERVER 2005. Several of the Access DB's have gotten to around 10,000 records, which is about the limit for Access.

==cfSearching==- , Dan Baruk and others have been extremly helpful and have been advising me to switch over for years. Becuase there are many subtle nuances how the SQL in a query needs to be written in the way  that CF handles the query, put another way, I am asking why, after 50 apps that have working for years using Access as a container for data are getting hung when the DB's are imported to SQL.

BTW here is the erreor when attempting to add a new record:

Error Executing Database Query.

[Macromedia][SQLServer JDBC Driver][SQLServer]Cannot insert the value NULL into column 'accident_ID', table 'Incidents.dbo.accidents'; column does not allow nulls. INSERT fails.

Thanks

Norman

[Adam Cameron.]

Absolutely.  I did not mean to cast any aspersions on your credentials.  But what you are describing is a vagary of the import (which is Access => SQL Server), not a vagary of CF.  As I imagine you know, CF is completely ignorant of DBs and simply passes the any SQL within <cfquery> tags to the DB to do everything.  It doesn't even know or care whether you're using Access or SQL Server (that's @ JDBC level, not CF).

You error message is simply declaring what you already said: the import screwed up some of your constraints or something.  That's nowt to do with CF, and all about your data migration.  So you need to fix your data migration before you start worrying about CF errors.  IE: if you sort out how to do the migration properly, and then redo it, then you won't get this error.  The CF error is a symptom of the problem, not the problem itself.

This is why I suggested asking on a MS DB forum.  They'll be well-placed to know what the story is, as it's their bread and butter.

Make sense?

--
Adam

[Guest]

No aspersions taken-

Well, you are correct in that CF doesn't know or care whether you're using Access or SQL Server, but there are some SQL edits that I will need to make once I get past the import issue, I'm sure.

I found a great post (IOW answerd my own question) that should help with that issue should someone encounter the same problem in the migration process (using a ColdFusion query example):

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23877

I'll post the results....

Thanks again

Norman

[-__cfSearching__-]

Yep, that sounds right (and why I was asking about the data type of the original column ;-).  I know MS Access typically defaults to using autonumber/long integer for primary keys, but I wanted to make sure that is what you were using.   Not sure why it was not carried over to an identity column during the import. But an identity column is what you need.

[-__cfSearching__-]

What is the data type of your original column?

> won't allow me to change that to uniqueidentifier.

uniqueidentifier contains alphanumeric strings like 6F9619FF-8B86-D011-B42D-00C04FC964FF and is probably not the correct type in this case.