Problem with SqlBulkCopy when destination table name has apostrophe (').  
Author Message
RomaP





PostPosted: .NET Framework Data Access and Storage, Problem with SqlBulkCopy when destination table name has apostrophe ('). Top

I have a problem with tables names like a'b.

WriteToServer(IDataReader) throws Exceptions:

Exception type: System.InvalidOperationException
Exception text: Cannot access destination table 'BPMExpert.dbo.[a'b]'.
Inner Exception type: System.Data.SqlClient.SqlException
InnerException text: Incorrect syntax near 'b'. Unclosed quotation mark after the character string ''.

Escaping the name using surrounding brackets, as the documentation says [<database>.<owningschema>.<tableName>], doesn't help. Simple select "SELECT count(*) from BPMExpert.dbo.[a'b]" works great. The problem exists only with apostrophe ('). With other characters, such as ampersand (&),percent (%), etc, which demand qoutation in identifiers no problems.
I've looked through SqlBulkCopy.WriteToServer(IDataReader) code using reflector and I think exceptions are throwing in MultipartIdentifier.ParseMultipartIdentifier method. But I don't I understand the reason.
May be someone could help me




.NET Development3  
 
 
Paul Domag





PostPosted: .NET Framework Data Access and Storage, Problem with SqlBulkCopy when destination table name has apostrophe ('). Top

Hi,

Have you tried doubling your quotes coz doubling your quotes would let sql server see it as a string quote instead of a closing quote for a string...

BPMExpert.dbo.[a''b] - use two quotes not double quotes in case you misunderstood me... :)

cheers,

Paul June A. Domag



 
 
alazela





PostPosted: .NET Framework Data Access and Storage, Problem with SqlBulkCopy when destination table name has apostrophe ('). Top

This is a bug in SqlBulkCopy -- one of the internal queries it generates doesn't correctly handle quote characters in the table name.  Unfortunately, doubling the quotes dosn't allow you to work around the problem, since other queries in the sequence ARE correctly handled (so you end up targetting different tables).  I don't see a good workaround at the moment, other than to avoid quotes in the table name if you are going to use SqlBulkCopy.

One possible workaround: bulk copy into a temp table (that doesn't have a quote in the name), then INSERT SELECT from there into the destination table.  Perf will suffer, so it may not be workable in your situation.



 
 
RomaP





PostPosted: .NET Framework Data Access and Storage, Problem with SqlBulkCopy when destination table name has apostrophe ('). Top

Yes, I have tried doubling, but unfortunately it hasn't resolved the problem.



 
 
RomaP





PostPosted: .NET Framework Data Access and Storage, Problem with SqlBulkCopy when destination table name has apostrophe ('). Top

This is a bug in SqlBulkCopy -- one of the internal queries it generates doesn't correctly handle quote characters in the table name. Unfortunately, doubling the quotes dosn't allow you to work around the problem, since other queries in the sequence ARE correctly handled (so you end up targetting different tables). I don't see a good workaround at the moment, other than to avoid quotes in the table name if you are going to use SqlBulkCopy.

One possible workaround: bulk copy into a temp table (that doesn't have a quote in the name), then INSERT SELECT from there into the destination table. Perf will suffer, so it may not be workable in your situation.



Thanks for reply. You have confirmed my supposition. Unfortunatily, I can't use temp tables and INSERT because of perf suffering. I hope this bug will not remain without attention of employees of Microsoft Corp.


 
 
alazela





PostPosted: .NET Framework Data Access and Storage, Problem with SqlBulkCopy when destination table name has apostrophe ('). Top

If this is blocking your development or business, please contact customer support and have a case started. That will increase the priority for fixing this problem.