Excel to SQL
-
I received the following error (see line *********error***********) when trying to transfer data from excel to sql using the code below. The sql table is in the database and columns all match the store procedure, does anyone have any idea what creates this problem. Thanks in advance, Michael Failed to obtain column collation information for the destination table. If the table is not in the current database the name must be qualified using the database name (e.g. [mydb]..[mytable](e.g. [mydb]..[mytable]); this also applies to temporary-tables (e.g. #mytable would be specified as tempdb..#mytable). void ExcelToSqlBulkCopy(String strPath, String strFile, String strTableName) { strExcelStoredProcedure = "SELECT ID,City,State FROM [Cities$]"; String excelConnectionString = y; String sqlConnectionString = z; // Create Connection to Excel Workbook using (OleDbConnection connection = new OleDbConnection(excelConnectionString)) { OleDbCommand command = new OleDbCommand(strExcelStoredProcedure, connection); connection.Open(); // Create DbDataReader to Data Worksheet using (DbDataReader dr = command.ExecuteReader()) { // Bulk Copy to SQL Server using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString)) { bulkCopy.DestinationTableName = "[MyDatabase].[dbo].[" + strTableName + "]"; bulkCopy.WriteToServer(dr); *********error*********** } } connection.Close(); } }
-
I received the following error (see line *********error***********) when trying to transfer data from excel to sql using the code below. The sql table is in the database and columns all match the store procedure, does anyone have any idea what creates this problem. Thanks in advance, Michael Failed to obtain column collation information for the destination table. If the table is not in the current database the name must be qualified using the database name (e.g. [mydb]..[mytable](e.g. [mydb]..[mytable]); this also applies to temporary-tables (e.g. #mytable would be specified as tempdb..#mytable). void ExcelToSqlBulkCopy(String strPath, String strFile, String strTableName) { strExcelStoredProcedure = "SELECT ID,City,State FROM [Cities$]"; String excelConnectionString = y; String sqlConnectionString = z; // Create Connection to Excel Workbook using (OleDbConnection connection = new OleDbConnection(excelConnectionString)) { OleDbCommand command = new OleDbCommand(strExcelStoredProcedure, connection); connection.Open(); // Create DbDataReader to Data Worksheet using (DbDataReader dr = command.ExecuteReader()) { // Bulk Copy to SQL Server using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString)) { bulkCopy.DestinationTableName = "[MyDatabase].[dbo].[" + strTableName + "]"; bulkCopy.WriteToServer(dr); *********error*********** } } connection.Close(); } }
-
I received the following error (see line *********error***********) when trying to transfer data from excel to sql using the code below. The sql table is in the database and columns all match the store procedure, does anyone have any idea what creates this problem. Thanks in advance, Michael Failed to obtain column collation information for the destination table. If the table is not in the current database the name must be qualified using the database name (e.g. [mydb]..[mytable](e.g. [mydb]..[mytable]); this also applies to temporary-tables (e.g. #mytable would be specified as tempdb..#mytable). void ExcelToSqlBulkCopy(String strPath, String strFile, String strTableName) { strExcelStoredProcedure = "SELECT ID,City,State FROM [Cities$]"; String excelConnectionString = y; String sqlConnectionString = z; // Create Connection to Excel Workbook using (OleDbConnection connection = new OleDbConnection(excelConnectionString)) { OleDbCommand command = new OleDbCommand(strExcelStoredProcedure, connection); connection.Open(); // Create DbDataReader to Data Worksheet using (DbDataReader dr = command.ExecuteReader()) { // Bulk Copy to SQL Server using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString)) { bulkCopy.DestinationTableName = "[MyDatabase].[dbo].[" + strTableName + "]"; bulkCopy.WriteToServer(dr); *********error*********** } } connection.Close(); } }
bulkCopy.DestinationTableName = strTableName is sufficient, de database is in the sqlConnectionString