Post reply

DTS Import wizard not picking up some column names

  • March 3, 2005 at 10:47 am

    #59995

    I'm trying to import some data from Oracle 9iR2 to SQL Server 2000 using the Oracle ODBC driver.

    However, the import process "often" fails because it fails to get column names sometimes.  For example, the following oracle table:

    SQL> desc ADSWEBSITE.FLASHCOOKIES;

     Name                Null?    Type

     ---------------------------------------------

     FLASHID             NOT NULL CHAR(35)

     VALID_FROM          NOT NULL TIMESTAMP(0)

     USERID              NOT NULL CHAR(10)

    The import process actually created a DTS step to create the table that looks like this:

    :CREATE TABLE [ADSWEBSITE].[dbo].[FLASHCOOKIES] (

    [FLASHID] char (35) NOT NULL,

    [] char (35) NOT NULL,

    [] char (35) NOT NULL

    )

    Which generates an error (obviously):  "Cannot use empty object or column names.  Use a single space if necessary"

    This happens on a lot of my tables for some reason.

    Any ideas?

     

  • March 3, 2005 at 11:01 am

    #545292

    What are the names of the missing columns?

  • March 3, 2005 at 11:44 am

    #545307

    I posted the table description in my original post.

    The missing columns are "VALID_FROM" and "USERID"

     

  • March 3, 2005 at 11:45 am

    #545308

    I thaught that there might have been some "forbidden" characters in the string but obviously not... I can't help you then.

    Good luck.

  • March 4, 2005 at 7:57 am

    #545471

    I suggest creating the "temporary" tables ahead of time with the column names that you want.

    I do daily importing of data from Oracle, but I had to do a two-step process because our admins were afraid to put Oracle Net on our production SQL Server. It worked out though because the SQL*Plus scripts allow me to do some data manipulation before the CSV data is imported with DTS.

  • March 4, 2005 at 8:02 am

    #545476

    Also, which driver are you using? Oracle? Microsoft?

    It is interesting that the column type/size seems to be a repeat of the first and not related to the remaining columns.

    TIMESTAMP columns are quite different between Microsoft and Oracle and that might cause a problem, but you would expect another error indication.

  • March 4, 2005 at 8:14 am

    #545483

    I was unable to use the Microsoft OLE DB Provider for Oracle for some reason (see this thread) so I ended up using the Oracle ODBC driver.

    I didn't notice that the datatypes didn't match the original table..

    Yeah I'm going to try to create the tables manually then do the import.

     

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

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