Post reply

Invalid character value for cast specification

  • October 10, 2005 at 4:53 am

    #66708

    I would like to determine which column in SQLServer 7 is causing this error. Upon running Database>All tasks>Import Data, then following the wizard to import tables from an Access 2000 DB, I get Error reported and a row no. and that's all.

    Can anyone advise please?

    thanks

    Robin Pearce

     

  • October 10, 2005 at 6:28 am

    #596241

    It is usually a string character going into an integer field.

    Script your table, then use the Access documentor to document the offending table and compare the two scripts.

  • October 10, 2005 at 7:54 am

    #596263

    Thanks for your reply. I'm afraid I don't know the procedure for either using the Access Documentor or scripting my table.

    There is definitely something wrong with a data cell because the SQL import works fine up to record 2547 before the error occurs! Looking at every column of record 2548, in Access, I can't see anything different in layout at all.

    Where can I find documentation for the method you described?

     

     

     

  • October 10, 2005 at 10:36 am

    #596329

    Within Access from the Tools menu select Analyse/Documentor and follow the Wizard.

    Within SQL Server Enterprise Manager choose your table, right-click, choose All Tasks/ Generate SQL Script

  • October 11, 2005 at 8:15 am

    #596542

    This is a test post.

     

  • October 11, 2005 at 8:21 am

    #596543

    David.....I have been having problems posting, so I will try again, picking up the thread where left off:

    David,

    Thanks for your response, I managed to lose my reply this morning (hit Post Reply and lost everything I typed (??)). So if something similar to the following appears, please ignore:

    I managed to compare the Access field types with SQL7 types. The differences were a)Access Date/Time length 8 becomes SQL7 smalldatetime length 4  b)Memo field becomes ntext length 16. I changed smalldatetime to datetime length 8 and this enabled me to read another 300 records or so. I now have the char-cast error again, and there is no obvious reason for it, I've checked every field in row 2867 - SQL7 import reads the first 2866 records no problem.

    What I really need is some utility, in SQL7 or Access, which will tell me precisely where the Import process first encounters a problem (and then quote it). Should I forget using SQL7>Database>Import and write my own loader??

    Granted that our dataset is not perfect, the only other thing I can think of is comparing every byte in Access, but surely there's a more efficient way to track this down?

     

  • October 11, 2005 at 10:02 am

    #596575

    Are there any null values in your Access date columns? This will do it. If so, try updating the null dates to some arbitrary date first, then retry. I had this problem before.

    OBTW, is there a reason you are using SQL7 instead of SQL2000? Probably a stupid question, but SQL2000 DTS makes importing data a little easier.

    (Not a newbie, but never posted here before)

     

  • October 11, 2005 at 10:35 am

    #596583

    There are null values all over the place, before and after where Import decides it has a problem. Just for grins I redid all four dates in record 2867, but this made no difference.

    There really is nothing even remotely visible that's different at that particular point in the table!

    BTW we are using SQL7 for purely internal administrative reasons.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply