Solved

SqlBulkCopy

Posted on 2010-11-30
8
1,348 Views
Last Modified: 2012-05-10
I seem to have stumbled across an issue with c# and the SqlBulkCopy import when bulk importing thing inside asp.net

When we import the data from a console application it works fine.
When we import the data from inside asp.net and certain char's with accents and the like seem to be converted to a ?

We are struggling to get to the bottom of this.
Or is there any way to set the encoding type to avoid this problem from happen?





0
Comment
Question by:Mistralol
  • 5
  • 3
8 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 34239826
How are you importing the data that is passed to the SqlBulkCopy?  I don't know of any encoding that can be applied to the SqlBulkCopy, so my thinking is that it is an importing problem, and not an SqlBulkCopy problem...
0
 
LVL 6

Author Comment

by:Mistralol
ID: 34240310

Its using a fixed with idatareader we wrote. Its reading this with a forced encoding type of ASCII.
However this work when it is in the console app.

0
 
LVL 6

Author Comment

by:Mistralol
ID: 34240562

I think we may have found the problem. Its something i was not aware of. and the issue is because the text file is being opened from insider a zip file :/

0
 
LVL 6

Accepted Solution

by:
Mistralol earned 0 total points
ID: 34247231

Ok so i guess we did't find the problem.

But here is a bit more information.


This works ... (with reduced dataset)

            IDataReader Reader = new Forth.Data.Standard.FixedDataReader("data.txt",
                new int[] { 40 },
                Forth.Data.Standard.FixedDataReaderOptions.RecordTerminatedCarriageReturnLineFeed);

            Conn.ImportNoMapping(Reader, "CharTest");

This Also works .... (with reduced dataset)

            byte[] b = File.ReadAllBytes("data.zip");
            ZipFile Zip = new ZipFile(new MemoryStream(b));
            Reader = new Forth.Data.Standard.FixedDataReader(Zip.GetInputStream(Zip.GetEntry("data.txt")),
                new int[] { 40 },
                    Forth.Data.Standard.FixedDataReaderOptions.RecordTerminatedCarriageReturnLineFeed);

            Conn.ImportNoMapping(Reader, "CharTest2");

However when run across the full datafile either method fails in console or web envirorment!
The encoded text seems to fail again.



0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 96

Assisted Solution

by:Bob Learned
Bob Learned earned 500 total points
ID: 34247829
Are you saying that the Unicode characters are not getting translated to question marks during the reading process?  I don't remember ever seeing that problem with bcp and SqlBulkCopy, so it would most likely be a mysterious problem under the hood.   That kind of problem is difficult to solve for myself, let alone for someone else...
0
 
LVL 6

Author Comment

by:Mistralol
ID: 34248665

Yeah we did get to the bottom of the problem. Involving the FixedDataReader. It was reading the file in the wrong mode. I switched it to binary and the issue magicly vanished. The interesting part is that the data in the file was changing the bug somehow.



0
 
LVL 96

Assisted Solution

by:Bob Learned
Bob Learned earned 500 total points
ID: 34249065
That would make sense if the data wasn't always Unicode, since those are the characters that get translated to ? with ASCII representation...
0
 
LVL 6

Author Closing Comment

by:Mistralol
ID: 34636486
We did get it fixed.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now