How to SqlBulkCopy to Linked Server Oracle 11g???

• kizildagcenk

  SSC Journeyman

  Points: 85

  More actions

  August 10, 2011 at 1:11 pm

  #245977

  Hi,

  We are using SQL Server 2008 R2 and we have a linked server to Oracle 11g. I would like to insert 10000 records to oracle table from Excel sheet. Its working for SQL Server but for Linked Server Oracle, when I try to use this Sql Bulk Copy, I am getting the error below:

  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).

  I am using VS.NET 2010 and code sample is as follows:

  ...

  Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(destinationConnection)

  bulkCopy.DestinationTableName = "[DatabaseName]..[Schema].[TableName]"

  bulkCopy.ColumnMappings.Clear()

  bulkCopy.ColumnMappings.Add("VENDORID", "VENDORID")

  bulkCopy.ColumnMappings.Add("REQUESTDATE", "REQUESTDATE")

  bulkCopy.ColumnMappings.Add("ITEMID", "ITEMID")

  bulkCopy.ColumnMappings.Add("PACKAGEID", "PACKAGEID")

  bulkCopy.ColumnMappings.Add("QUANTITY", "QUANTITY")

  bulkCopy.ColumnMappings.Add("TOTAL", "TOTAL")

  bulkCopy.ColumnMappings.Add("DELIVERYID", "DELIVERYID")

  bulkCopy.ColumnMappings.Add("REQUESTTYPE", "REQUESTTYPE")

  bulkCopy.ColumnMappings.Add("STATUSID", "STATUSID")

  bulkCopy.ColumnMappings.Add("APPROVED", "APPROVED")

  bulkCopy.ColumnMappings.Add("DISPATCHDATE", "DISPATCHDATE")

  bulkCopy.ColumnMappings.Add("APPROVEDBY", "APPROVEDBY")

  bulkCopy.ColumnMappings.Add("APPROVEDATE", "APPROVEDATE")

  bulkCopy.WriteToServer(dr)

  bulkCopy.Close()

  End Using

  Any help please?

  Best Regards.