Mitch Wardrop

Hey everyone hopefully someone might be able to shed some light on an issue I'm having with sqlbulkcopy, I use it to copy a couple of tables between client and server machine via a webservices, basicly just calls a function that has the sqlbulkcopy command on the server.

What I do is use a dataadapter to pull all the data I require out of the database on the client so "SELECT * FROM tableIneed WHERE clause" and load it into a datatable I than take that datatable put it into a dataset and send it to the function on the server where I extract the datatable and run the following code.

Try

conn.Open()

Datasources.Columns.

Dim bulkcopy As New SqlBulkCopy(conn)

bulkcopy.ColumnMappings.Clear()

bulkcopy.DestinationTableName = "tblDataSources"

bulkcopy.WriteToServer(DataSources)

conn.Close()

bulkcopy.Close()

Catch ex As Exception

Return ("Code205 : " & ex.ToString)

End Try

Problem I have is that the webservice returns :

System.InvalidOperationException: The given ColumnMapping does not match up with any column in the source or destination.

at System.Data.SqlClient.SqlBulkCopy.AnalyzeTargetAndCreateUpdateBulkCommand(BulkCopySimpleResultSet internalResults)

at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()

at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)

at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)

at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)

at ServerUpload.WellDaq.DataSourcesSync(DataSet DataSourcesDataSet, String JobDate, String Job) in C:\Documents and Settings\Mitch\My Documents\Programming\DataHub\Server\WellDaq.asmx.vb:line 130

Both the table on the server and the table on the client are identical, I've tried running the entire datatable through a for each row loop and removing the unique identifer tag and setting it to DBNULL so that it would automatically assign a new one and that didnt work. I also tried making the unique identifying column, not unique wich also never worked.

I have this exact same code working for other datatables in the exact same manner and they work fine just this one table gives me grief, any insight into the cause of this would be greatly appreciated.


Re: .NET Framework Data Access and Storage sqlbulkcopy "The given ColumnMapping does not match up with any column in the source or destination."

Bill Lin - MSFT

Do you have TimeStamp columns in your table Or do you have identity columns If you have identity columns, you need to specify SqlBulkCopyOptions.KeepIdentity




Re: .NET Framework Data Access and Storage sqlbulkcopy "The given ColumnMapping does not match up with any column in the source or destination."

Dragthor

I am performing a SqlBulkCopy (.DBF to Sql Server 2005).... the column mappings are case sensitive on both datasources.




Re: .NET Framework Data Access and Storage sqlbulkcopy "The given ColumnMapping does not match up with any column in the source or destination."

Dragthor

Actually.... I know this sounds weird (or backwards) but the Sql Server 2005 destination columns are case sensitive.






Re: .NET Framework Data Access and Storage sqlbulkcopy "The given ColumnMapping does not match up with any column in the source or destination."

Bill Lin - MSFT

This case-sensitivity thing is sql server setting. Please refer to http://msdn2.microsoft.com/en-US/library/aa197951(sql.80).aspx




Re: .NET Framework Data Access and Storage sqlbulkcopy "The given ColumnMapping does not match up with any column in the source or destination."

tKourbanis

The number of fields in .Dbf mast <= of columns in SQL table.
Use comumnMappings with less fields.