none
How can I copy database from Pervasive SQL to MSSQL Server 2008?

    Question

  • I would like to copy a complete PSQL 11 database to MSSQL Server 2008 R2  x64.  I have tried the import file and I chose the Copy data from one or more tables or views.  When I click next to start the process I get - Cannot find column -1.   messages

    Does anyone have any ideas?

    Wednesday, January 23, 2013 7:06 PM

Answers

  • There is no mapping file for Teradata in \Program Files\Microsoft SQL Server\100\DTS\MappingFiles and not sure if you can create your own, this leaves perhaps no options than creating a migration script programmatically for all the tables.

    Arthur My Blog

    • Marked as answer by Eileen Zhao Wednesday, January 30, 2013 7:05 AM
    Wednesday, January 23, 2013 7:47 PM
    Moderator

All replies

  • Last time I used Pervasive was years ago, I recall it has only a JDBC provider and the Data Export Wizard does not connect to it, not sure if something has changed since then.

    I guess you do not have the correct one any way.

    Your option would be to re-create the schema on the SQL Server side, then extract the table data into flat files and finally import into SQL Server.


    Arthur My Blog

    Wednesday, January 23, 2013 7:11 PM
    Moderator
  • Pervasive has ADO.NET providers, OLE DB providers, ODBC providers, etc.  I think the issue is a datatype mapping issue at a very low level.  I found a MS link that referenced this issue with older versions of pervasive but the changes didnt help.


    Wednesday, January 23, 2013 7:20 PM
  • Then I would try connecting using a different provider. OLEDB would be my 1st choice.

    Arthur My Blog

    Wednesday, January 23, 2013 7:28 PM
    Moderator
  • yep, i tried ado, oledb and odbc

    also, if i use the query option i can get it to work but i have to do it a table at a time.  I have about 400 tables i need to copy.

    Wednesday, January 23, 2013 7:36 PM
  • There is no mapping file for Teradata in \Program Files\Microsoft SQL Server\100\DTS\MappingFiles and not sure if you can create your own, this leaves perhaps no options than creating a migration script programmatically for all the tables.

    Arthur My Blog

    • Marked as answer by Eileen Zhao Wednesday, January 30, 2013 7:05 AM
    Wednesday, January 23, 2013 7:47 PM
    Moderator
  • yep, thats where i came to and thus thats the reason i put a question out here.  

    i may post the same question on a PSQL forum.

    thanks for your help

    Wednesday, January 23, 2013 7:51 PM