limited odbc connections in sql server 2008 import wizard

Question

Issue: pervasive odbc driver ((called: "Pervasive ODBC engine interface") is visible in ODBC(odbcad32.exe). However, the same odbc driver is not visible in SQL server 2008 import wizard, although I can see the same odbc driver in SQL server 2000 import wizard.

I am using 32-bit win 7 OS with SQL server 2008, SQL server 2000 and pervasive SQL v11. any solution will be very helpful...Many Thanks!

Answer 1

I could never figure out how to do make the 'Import/Export' wizard work in Sql Server Management Studio. I even tried to modify the 'ProviderResources.xml' file as I saw in another response.

I was attempting to migrate Sage Timberline Office data which uses a proprietary 'Timberline Data' ODBC driver. That driver is missing the 'ORDINAL_POSITION' column when you call the 'GetSchema' function in .NET. So 'Import/Export' in Sql Server Management Studio fails.

I ended up having to write my own app to copy the data over to SQL server. The only downside is it doesn't know about primary keys, indexes, or other constraints. Nonetheless, I get the data in MSSQL so I am happy.

I am sure this code will be useful to others, so here you go.

Program.cs

using System;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Data;
using System.Collections.Generic;
using System.Diagnostics;

namespace TimberlineOdbcSync
{
    class Program
    {
        static string currentTableName;

        const string sourceOdbcDriver = "{Timberline Data}"; 
        const string sourceOdbcDsn = "timberline data source";
        const string sourceOdbcUid = "user1";
        const string sourceOdbcPwd = "user1a";

        const string destSqlServer = "SERVER5";
        const string destSqlDatabase = "TSData";
        const string destSqlUsername = "";
        const string destSqlPassword = "";
        const string destSqlOwner = "dbo";

        public static void Main(string[] args)
        {
            DateTime allStartDate = DateTime.Now;
            DateTime allEndDate;

            DateTime tableStartDate = DateTime.Now;
            DateTime tableEndDate;

            TimeSpan diff;

            string errMsg;

            int pCount; //pervasive record count
            int sCount; //sql server record count

            string sourceOdbcConnString =  
                "Dsn=" + sourceOdbcDsn + ";" +
                "Driver="+ sourceOdbcDriver +";" +
                    (!string.IsNullOrEmpty(sourceOdbcUid) ? "uid="+ sourceOdbcUid +";" : "") + 
                    (!string.IsNullOrEmpty(sourceOdbcUid) ? "pwd="+ sourceOdbcPwd +";" : "");

            string destSqlConnString = 
                "Server=" + destSqlServer + ";" + 
                "Database=" + destSqlDatabase+ ";" +
                (!string.IsNullOrEmpty(destSqlUsername) && !string.IsNullOrEmpty(destSqlPassword) ? 
                 "User Id=" + destSqlUsername + ";" + 
                 "Password=" + destSqlPassword + ";" 
                 :
                 "Trusted_Connection=true;");

            try{
                using(OdbcConnection pConn = new OdbcConnection(sourceOdbcConnString)){

                    pConn.Open();

                    List<string> tables = new List<string>();

                    //get a list of all tables
                    using(DataTable tableschema = pConn.GetSchema("TABLES"))
                        foreach(DataRow row in tableschema.Rows)
                            tables.Add(row["TABLE_NAME"].ToString());

                    foreach(string tableName in tables){

                        //set the current table name
                        currentTableName = tableName;

                        try{

                            //get the schema info for the table (from pervasive)
                            DataTable dtSchema = pConn.GetSchema("Columns", new string[]{null, null, tableName});

                            //if we could not get the schema
                            if(dtSchema == null || dtSchema.Rows.Count <= 0){
                                pConn.Close();
                                errMsg = "Error: Could not get column information for table " + tableName;
                                Trace.WriteLine(errMsg);
                                WriteErrorEvent(errMsg);
                                return;
                            }

                            //emit the table name
                            Trace.Write("[" + tableName + "]");

                            //get the number of records in this table
                            pCount = TableCount(tableName, pConn);

                            //emit the number of records in this table
                            Trace.Write(" = P:" + pCount);

                            //create a data reader to read the pervasive data
                            string sql = "select * from \""+ tableName + "\""; 
                            OdbcCommand cmd = new OdbcCommand(sql, pConn);
                            OdbcDataReader dr = cmd.ExecuteReader();

                            //create a connection to SQL Server
                            using (SqlConnection sConn = new SqlConnection(destSqlConnString)){

                                //open the connection
                                sConn.Open();

                                //if the table already exists
                                if(TableExists(tableName, sConn)){

                                    //get the record count for this table
                                    sCount = TableCount(tableName, sConn);

                                } else {

                                    //set the record count to zero
                                    sCount = 0;
                                }

                                //output the record count
                                Trace.Write(", S: " + sCount);

                                //if the record counts match
                                if( pCount == sCount ){

                                    //output an indicator that we are skipping this table
                                    Trace.WriteLine(" -- Skipping");

                                    //skip this table and go to the next
                                    continue;   

                                }

                                //output a blank line
                                Trace.WriteLine("");

                                //create the table in SQL Server using the schema info from Pervasive
                                CreateTableInDatabase(dtSchema, destSqlOwner, tableName, sConn);

                                // Copies all rows to the database from the data reader.
                                using (SqlBulkCopy bc = new SqlBulkCopy(sConn))
                                {
                                    // Destination table with owner - 
                                    // this example does not check the owner names! It uses dbo exclusively.
                                    bc.DestinationTableName = "[" + destSqlOwner + "].[" + tableName + "]";
                                    bc.BulkCopyTimeout = 30;
                                    bc.BatchSize = 3000;
                                    bc.BulkCopyTimeout = 12000;

                                    // User notification with the SqlRowsCopied event
                                    bc.NotifyAfter = 1000;
                                    bc.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);

                                    //output the date and time so we know when we started
                                    tableStartDate = DateTime.Now;
                                    Trace.WriteLine("Copying " + pCount + " records to " + destSqlServer + " - " + tableStartDate.ToString("g"));

                                    // Starts the bulk copy.
                                    bc.WriteToServer(dr);

                                    tableEndDate = DateTime.Now;
                                    diff = tableEndDate - tableStartDate;

                                    Trace.WriteLine(String.Format(
                                        "Completed {4} at {0}\r\nDuration: {1}:{2}:{3}",
                                          tableEndDate.ToString("g"),
                                          diff.Hours.ToString(), diff.Minutes.ToString(), diff.Seconds.ToString(),
                                          tableName));

                                    // Closes the SqlBulkCopy instance
                                    bc.Close();
                                }

                                dr.Close();

                            }
                        }catch(Exception ex){
                            errMsg = "Error: " + ex.Message + Environment.NewLine + 
                                              "Stack: " + ex.StackTrace + Environment.NewLine;
                            Trace.WriteLine(errMsg);
                            WriteErrorEvent(errMsg);
                            if( !ReadBool("Do you want to continue? [y/n]") ){
                                break;
                            }
                        }//end try
                    }//end for
                }//end using

                allEndDate = DateTime.Now;
                diff = allEndDate - allStartDate;

                Trace.WriteLine(
                    "Bulk copy operation complete" + Environment.NewLine +
                    "Started: " + allStartDate.ToString("g") +  Environment.NewLine + 
                    "Current: " + allEndDate.ToString("g") + Environment.NewLine +
                    String.Format("Duration: {0}:{1}:{2}", 
                        diff.Hours.ToString(), 
                        diff.Minutes.ToString(), 
                        diff.Seconds.ToString()));

            }catch(Exception ex){

                errMsg = 
                    "Error: " + ex.Message + Environment.NewLine +
                    "Stack: " + ex.StackTrace;

                Trace.WriteLine(errMsg);
                WriteErrorEvent(errMsg);

            }//end try

            Console.Write("Press any key to continue . . . ");
            Console.ReadKey(true);
        }

        static bool TableExists(string tableName, SqlConnection sqlConn){
            int retVal = 0;
            try{
                using(SqlCommand command = sqlConn.CreateCommand()){
                    command.CommandText = "IF OBJECT_ID('dbo." + tableName + "', 'U') IS NOT NULL SELECT 1 as res ELSE SELECT 0 as res";
                    retVal = Convert.ToInt32(command.ExecuteScalar());
                }
            }catch(Exception ex){
                string errMsg = 
                    "Error: Could not determine if table " + tableName + " exists."+ Environment.NewLine +
                    "Reason: " + ex.Message + Environment.NewLine + 
                    "Stack: " + ex.StackTrace;
                Trace.WriteLine(errMsg);
                WriteErrorEvent(errMsg);
                retVal = 0;
            }//end try
            return (retVal==1);
        }

        static int TableCount(string tableName, IDbConnection anyConn){
            int retVal = 0;
            try{
                using(IDbCommand command = anyConn.CreateCommand()){
                    command.CommandText = "SELECT count(*) FROM \"" + tableName + "\"";
                    retVal = Convert.ToInt32(command.ExecuteScalar());
                }
            }catch(Exception ex){
                string errMsg = 
                    "Error: Could not get table count for " + tableName + "." + Environment.NewLine +
                    "Reason: " + ex.Message + Environment.NewLine + 
                    "Stack: " + ex.StackTrace;
                Trace.WriteLine(errMsg);
                WriteErrorEvent(errMsg);
                retVal = 0;
            }//end try
            return (retVal);
        }

        static bool ReadBool(String question) {
            while (true) {
                Console.WriteLine(question);
                String r = (Console.ReadLine() ?? "").ToLower();
                if (r == "y" || r == "yes" || r == "1")
                    return true;
                if (r == "n" || r == "no" || r=="0")
                    return false;
                Console.WriteLine("Please Select a Valid Option!!");
            }//end while
        }

        static void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e) { 
            Trace.WriteLine(String.Format("-- [{1}] Copied {0} rows.", e.RowsCopied, currentTableName));
        }

        private static string s(object o){
            return (Convert.IsDBNull(o) ? "" : Convert.ToString(o));
        }

        private static string _drToColSql(DataRow dr){
            string colName = s(dr["COLUMN_NAME"]);
            string ret = "[" + colName + "] ";
            string typeName = ((string)s(dr["TYPE_NAME"])).ToLower();
            switch(typeName){
                case "char":
                    ret += "CHAR(" + s(dr["LENGTH"]) + ")";
                    break;
                case "byte":
                    ret += "CHAR(" + s(dr["PRECISION"]) + ")";
                    break;
                case "text":
                    ret += "VARCHAR(" + s(dr["PRECISION"]) + ")";
                    break;
                case "date":
                    ret += "DATE";
                    break;
                case "time":
                    ret += "TIME(7)";
                    break;
                case "double":
                    ret += "DECIMAL(16,2)"; // + c(dr["PRECISION"]) + "," + c(dr["LENGTH"]) + ")";
                    break;
                case "usmallint":
                case "smallint":
                    ret += "SMALLINT";
                    break;
                case "utinyint":
                case "tinyint":
                    ret += "TINYINT";
                    break;
                case "identity":
                case "integer":
                    ret += "BIGINT";
                    break;
                case "smallidentity":
                case "short":
                    ret += "INT";
                    break;
                case "longvarchar":
                case "memo":
                    ret += "TEXT";
                    break;
                case "checkbox":
                    ret += "BIT";
                    break;
                case "real":
                    ret += "REAL";
                    break;
                default:
                    //this was an unexpected column, figure out what happened
                    Trace.WriteLine("ERROR - Column '" + colName + "' Details: ");
                    Trace.WriteLine("\tCOLUMN_NAME: " + s(dr["COLUMN_NAME"]));
                    Trace.WriteLine("\tTYPE_NAME: " + s(dr["TYPE_NAME"]));
                    Trace.WriteLine("\tDATA_TYPE: " + s(dr["DATA_TYPE"]));
                    Trace.WriteLine("\tLENGTH: " + s(dr["LENGTH"]));
                    Trace.WriteLine("\tPRECISION: " + s(dr["PRECISION"]));
                    Trace.WriteLine("\tSCALE: " + s(dr["SCALE"]));
                    Trace.WriteLine("\tNULLABLE: " + s(dr["NULLABLE"]));
                    throw new Exception("Unexpected data type: " + typeName);
            }

            if(s(dr["NULLABLE"])=="1"){
                ret += " NULL"; 
            }

            return ret;
        }

        private static bool CreateTableInDatabase(DataTable dtSchemaTable, string tableOwner, string tableName, SqlConnection sqlConn) {
            // Generates the create table command.
            string ctStr = "CREATE TABLE [" + tableOwner + "].[" + tableName + "](\r\n";
            for (int i = 0; i < dtSchemaTable.Rows.Count; i++)
            {
                ctStr += _drToColSql(dtSchemaTable.Rows[i]);
                if (i < dtSchemaTable.Rows.Count)
                    ctStr += ",";
                ctStr += "\r\n";
            }
            ctStr += ")";

            // Emit SQL statement
            Trace.WriteLine("-".PadLeft(30, '-'));
            Trace.WriteLine(ctStr + Environment.NewLine);

            // Runs the SQL command to make the destination table.      
            using(SqlCommand command = sqlConn.CreateCommand()){
                command.CommandText = "IF OBJECT_ID('dbo." + tableName + "', 'U') IS NOT NULL DROP TABLE dbo." + tableName;
                command.ExecuteNonQuery();
                command.CommandText = ctStr;
                command.ExecuteNonQuery();
            }
            return true;
        }

        private static bool WriteErrorEvent(string errMsg){
            const string sSource = "PervasiveOdbcSync";
            const string sLog = "Application";
            try{
                if (!EventLog.SourceExists(sSource))
                    EventLog.CreateEventSource(sSource,sLog);
                EventLog.WriteEntry(sSource, errMsg);
                EventLog.WriteEntry(sSource, errMsg, EventLogEntryType.Error, 128);
                return true;
            }catch(Exception ex){
                Trace.WriteLine("Unable to write error to event log. Reason: " + ex.Message);
                return false;
            }
        }

    }
}

---

You'll want to add a System.Diagnostics.ConsoleTraceListener to your app.config file. That way you can see everything that is being outputted. If you also add a System.Diagnostics.TextWriterTraceListener, you can make the app also output everything to a log file.

Answer 2

On my PSQL v11 box which also has SQL Server 2008 R2 installed, I don't see a "Pervasive ODBC Engine Interface" listed in the "Data Source" dialog of the SQL Server Import and Export Wizard. I do see the "Pervasive PSQL OLEDB Provider" and "Pervasive Provider, release v4.0" (and 3.5 and 3.2). THe Pervasive Provider is an ADO.NET provider. I do see a ".Net Framework Data Provider for ODBC" and if I put a DSN name for a Pervasive DSN (like DEMODATA), it works.