title: Importing Caché dB into SQL Server 2008 R2 using SSMS
body: <p>I've been tasked with extracting the data from a Caché dB (Specifically SunGard AddVantage)and relocating it to a new SQL Server. I was provided with a ODBC in order to connect to the server. When I attempt to Import via the "SQL Server Import and Export Wizard", I get the following error:</p>
  
  <pre><code>TITLE: SQL Server Import and Export Wizard
  ------------------------------
  
  Column information for the source and the destination data could not be retrieved, or the data types of source columns were not mapped correctly to those available on the destination provider.
  
  
  "Addv"."ACCOUNT" -&gt; [Addv].[ACCOUNT]:
  
        - The data type could not be assigned to the column "BI6_APPOINT_DATE" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "BI7_OPEN_DATE" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "BI8_IRS_DATE" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "BI17_START_TERMINATE" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "BI18_FINAL_TERMINATE" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "BI35_DATE_FORM_W9_RECEIVED" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "BI85_INCEPTION_DATE" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "BI91_LAST_REVIEW_DATE" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "AI25_DATE_CONVERTED_TO_SINGLE_L" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "AI34_DATE_CONVERTED_FOR_DRP_LOT" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "AI35_DATE_CONVERTED_FOR_RIC_LOT" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "FP10_FIRST_FEE_START_DATE" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "IV13_PREVIOUS_MV_DATE" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "PI2_DATE_CREATED" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "PI3_DATE_FUNDED" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "PI4_DATE_OPENED_REOPENED" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "PI5_DATE_OF_LAST_MASTER_MOD" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "PI6_LAST_ACTIVITY_DATE" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "PI7_PREVIOUS_LAST_ACTIVITY_DATE" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "PI8_DATE_CLOSED" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "PI21_DATE_PRIN_CASH_BECAME_OD" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "PI22_DATE_PRIN_CASH_EXCEEDED_LG" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "PI23_DATE_INC_CASH_BECAME_OD" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "PI24_DATE_INC_CASH_EXCEEDED_LG" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "PI27_PREV_DATE_OF_LAST_MASTER_M" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "PI28_AMORTIZATION_START_DATE" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "PI29_ACCRETION_START_DATE" in "SQL Server Native Client 10.0".
        - The data type could not be assigned to the column "PI30_LAST_SMAC_ACTIVITY_DATE" in "SQL Server Native Client 10.0".
  
  
  ------------------------------
  BUTTONS:
  
  OK
  ------------------------------
  </code></pre>
  
  <p>After several attempts, I thought maybe I could import the data into Access and then move it over to SQL. I was able to extract all the tables, except for one (due to its size and Access' size limits, the table had 5.7 million rows). I've attempted to link the table as a view to export in order to bypass the size restriction, but it crashes my system. Additionally I attempted importing into Excel, but again run into the size limitation (this time the 4GB because the FAT32 format). I am used to working in SQL, but this is my first experience doing a Caché to SQL conversion, so I am in unknown waters. I have tried getting DTS as many posts have said to, but I can't seem to find it after I run the setup. I am posting here as even an outside consultant didn't know how to proceed. If more specific information is needed, please let me know and I will provide. </p>
  
  <p>Update: I discovered that through the wizard the Cache column data types were not correct. After re-mapping and updating, I now get this error:</p>
  
  <pre><code>TITLE: SQL Server Import and Export Wizard
  ------------------------------
  
  Could not connect source component.
  
  Error 0xc0047062: Source - ACCOUNT [1]: System.Data.Odbc.OdbcException (0x80131937): ERROR [42000] [Cache ODBC][State : 42000][Native Code 1]
  [D:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTSWizard.exe]
  [SQLCODE: &lt;-1&gt;:&lt;Invalid SQL statement&gt;]
  [Location: &lt;Prepare&gt;]
  [%msg: &lt; IDENTIFIER expected, : found^select * from :%qpar&gt;]
  at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
  at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
  at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
  at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
  at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
  at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
  at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.ReinitializeMetaData()
  at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostReinitializeMetaData(IDTSManagedComponentWrapper100 wrapper)
  
  ------------------------------
  ADDITIONAL INFORMATION:
  
  ERROR [42000] [Cache ODBC][State : 42000][Native Code 1]
  [D:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTSWizard.exe]
  [SQLCODE: &lt;-1&gt;:&lt;Invalid SQL statement&gt;]
  [Location: &lt;Prepare&gt;]
  [%msg: &lt; IDENTIFIER expected, : found^select * from :%qpar&gt;] (CacheODBC35.DLL)
  
  ------------------------------
  BUTTONS:
  
  OK
  ------------------------------
  </code></pre>