Skip navigation
Currently Being Moderated

SQL Server Import from Access issue

Jan 23, 2012 1:22 PM

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

 
Replies
  • Currently Being Moderated
    Jan 23, 2012 1:37 PM   in reply to sakonnetweb

     

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 23, 2012 1:38 PM   in reply to sakonnetweb

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 24, 2012 7:08 AM   in reply to sakonnetweb

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 24, 2012 8:05 AM   in reply to sakonnetweb

    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.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points