Cannot access destination table 'table-name' Error – During SqlBulkCopy

Problem Description:

The above error cannot access destination table might occur when bulk copying data to Sql server table using SqlBulkCopy.

Possible Reasons:

1. If the destination table name contains spaces

2. If the destination table name contains - (hyphen) symbol

Example:

Using bulkCopy As SqlBulkCopy = _

New SqlBulkCopy(targetConnectionString, SqlBulkCopyOptions.TableLock)

bulkCopy.DestinationTableName = "abb po" '- This will Fail –Bcoz the table name contains space.

bulkCopy.DestinationTableName = "abb-po" '- This will Fail –Bcoz the table name contains space.

Try

' Write from the source to the destination.

bulkCopy.WriteToServer(OdbcDr)

Catch ex As Exception

Console.WriteLine(ex.Message)

End Try

End Using

Solution Description:

The table name should be preceded and followed by "[" and "]" respectively.

Example:

Using bulkCopy As SqlBulkCopy = _

New SqlBulkCopy(targetConnectionString, SqlBulkCopyOptions.TableLock)

bulkCopy.DestinationTableName = "[abb po]" 'Correct Code

bulkCopy.DestinationTableName = "[abb-po]" 'Correct Code

Try

' Write from the source to the destination.

bulkCopy.WriteToServer(OdbcDr)

Catch ex As Exception

Console.WriteLine(ex.Message)

End Try

End Using

SQL BULK COPY error: invalid attempt to call fieldcount when reader is closed Error

Problem Description:

The error " invalid attempt to call fieldcount when reader is closed" can occur when bulk copying data to Sql server table using SqlBulkCopy.

Consider the following example:

The following example throws the error "Invalid attempt to call fieldcount when reader is closed"

Example:

' Retrieval of the data from the source ODBC connection table

Dim sODBCCmd As New OdbcCommand(strODBC, sODBCConn)

sODBCConn.Open()

OdbcDr = sODBCCmd.ExecuteReader()

sODBCConn.Close() ' We are Closing the connection

' SQL Bulk Copy

Using bulkCopy As SqlBulkCopy = _

New SqlBulkCopy(targetConnectionString, SqlBulkCopyOptions.TableLock)

bulkCopy.DestinationTableName = "[" & sTableName & "]"

Try

bulkCopy.WriteToServer(OdbcDr)

Catch ex As Exception

Console.WriteLine(ex.Message) ' Error Invalid attempt to call fieldcount when reader is closed

End Try

End Using

Possible Reasons:

The data reader is closed.

sODBCConn.Close() (which closes the connection) after ExecuteReader() is called and thus closing the
datareader (the datareader is tied to the connection).

Remarks: The connection.Close() statement closes the data reader as well.

Solution:

We must either keep the connection open and close it and the reader when you are done with it, or make use of a dataset.

sODBCConn.Open()

OdbcDr = sODBCCmd.ExecuteReader()

Using bulkCopy As SqlBulkCopy = _

New SqlBulkCopy(targetConnectionString, SqlBulkCopyOptions.TableLock)

bulkCopy.DestinationTableName = "[" & sTableName & "]"

Try

' Write from the source to the destination.

bulkCopy.WriteToServer(OdbcDr)

Catch ex As Exception

Console.WriteLine(ex.Message) ' Error Throws Here

Finally

OdbcDr.Close()

sODBCConn.Close() ' Should be closed Here to avoid the error

End Try

End Using

Monthly Award Winner from Dot net spider for the Month of July

Following my contributions to DOTNETSPIDER, yet another award in my bag, this time as Monthly award winner, the message from the forum below,

http://www.dotnetspider.com/forum/217934-Monthly-winner-for-month-July.aspx

Hello DNS'ians,

We are glad to announce, the Winners of the month of July, 2009 which goes to the following members.


1. Viji Raj Kumar -- Free License of Aspose.Chart Developer Enterprise for .NET valued $249

Viji Raj Kumar is a Diamond member of DNS who joined DNS in June, 2009. she is an active member of DotNetSpider.


2. Deepika Haridas -- Electronic Gadget,

Deepika Haridas is one of the Most Valuable Member of DNS and is a Diamond member at present. She was recently awarded with MVM Award and many other awards from DNS. Deepika is a very active member of DNS.


3. R.Jaya Kumar -- Electronic Gadget,

R.Jaya Kumar has joined DNS in May, 2009 and is now a Diamond Member of DNS.

Dotnetspider Team congratulates the winners on their achievement.

We have sent a mails to the Winners about their winning the award. We have sent a mail to Mr. Merrit Cooper or www.aspose.com to award the free license to Viji Raj Kumar. The winners are requested to send their shipping address and their contact numbers.

You can catch the other Winners list at :-->> List of DNS Award Winners

Members can join us to Congratulate the winners on this great achievement.

Thank you


Raghav
Web Master