none
Need to save to file from data stored in varbinary(max) SQL Server from MS Access linked table RRS feed

  • Question

  • First, many people are going to tell me to post this in a C# forum but the reason I am posting here is to understand what Access is doing to this data because its not just saving the binary data. The files are 2 times bigger than they should be. I am wondering if Access is not wrapping this with an OLE Header. Which I can't extract the file using C# because of this.

    The database of files has gotten way out of control and I need to download all these files and store them in a file system on a web server for many reasons.

    I have a database which stores customer files. I have a linked table in my MS Access 2016 project to a SQL Server table called Customer_Files. The table has a column called File_Data which is VARBINARY(MAX). When I link the table in Access it shows it as an OLE Object data type.

    To get the files into the column I use this code:

    Function modFileIO_UploadFromFile(fileField As Field, fileSizeField As Field, sourceFile As String)
    ' reads sourceFile into varbinary Field
        On Error GoTo Err_Handler
        Dim blocks As Long
        Dim blockCount As Long
        Dim extraBytes As Long
        Dim fileSize As Long
        Dim fileBuffer As String
        Dim fileNumber As Long
        On Error GoTo Err_Handler
        ' Open sourceFile
        fileNumber = FreeFile ' gets next available file number for use with Open statement
        Open sourceFile For Binary Access Read As fileNumber
        fileSize = LOF(fileNumber)
        ' Find number of blocks and extra bytes
        blocks = fileSize \ blockSize
        extraBytes = fileSize Mod blockSize
    
        ' allocate space in the buffer
        fileBuffer = Space(blockSize)
        For blockCount = 1 To blocks
            Get fileNumber, , fileBuffer
            fileField.AppendChunk (fileBuffer)
        Next
        ' resize the buffer for any extra data
        If extraBytes <> 0 Then
            fileBuffer = Space(extraBytes)
            Get fileNumber, , fileBuffer
            fileField.AppendChunk (fileBuffer)
        End If
        fileSizeField = fileSize
        Close fileNumber
        modFileIO_UploadFromFile = True
        Exit Function
    Err_Handler:
    Stop
        modFileIO_UploadFromFile = False
        Exit Function
    
    End Function

    What is Access doing to the binary data? I thought it would just read it and store it, but obviously it is doing something else to the data because I cannot simply read the stream from the column in C# and fill a Byte array and write that to file. The files are turning out 2 times as big as they should.

    Thanks for the help!


    Tuesday, January 8, 2019 10:42 PM

Answers

  • Ok, at least some progress here.

    My bad – don’t use

    System.Text.Encoding.BigEndianUnicode.

       GetBytes(System.Text.Encoding.UTF32.GetString(b))

    But use this:

    System.Text.Encoding.Default.

        GetBytes(System.Text.Encoding.Unicode.GetString(b))

    The above works for me.

    My c# is not that great (I can read it quite well, but not write without thinking).

    This bit of c# works for me:

                string select = "SELECT MyFile from BigIntTest where id = 1";
    
                SqlConnection con = new SqlConnection("SERVER=ALBERTKALLAL-PC\\SQLEXPRESS;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=test3");
    
                con.Open();
                SqlCommand com = new SqlCommand(select, con);
                com.CommandType = CommandType.Text;
                SqlDataReader reader = com.ExecuteReader();
                reader.Read();
    
                byte[] blob;
                byte[] blob2;
    
                blob = (byte[])reader[0];
    
                blob2 = System.Text.Encoding.Default.GetBytes(System.Text.Encoding.Unicode.GetString(blob));
    
                using (var fs = new FileStream(destination, FileMode.Create, FileAccess.Write))
    
                {
                    fs.Write(blob2, 0, blob2.Length);
                    fs.Flush();
                }
                return "ok";
    
            }
    

    As I stated, you can’t really make this conversion on your own.

    The windows system “converted” the original byte() stream into unicode. That is “universal”. So, you need to convert that Unicode back to the original byte format.

    (a windows computer running English text).

    Remember, “Hello” as Unicode is double in size. To display “hello” on YOUR computer, the UniCode is converted back from Unicode to “English” to only display the 5 characters.

    Of course, the Access solution messed you up, since conversion to Unicode should NOT have been allowed, nor have occurred.

    Note that the default for net is Unicode, but you STILL need to convert that back to a byte array based on your default code page (English).

    Also, if you looking for some additional speed, then we can get rid of the byte->to string->to byte conversion with this bit:

    reader.Read();
    byte[] blob;
    byte[] blob2;
    blob = (byte[])reader[0];
    
    System.Text.Encoding cFrom = System.Text.Encoding.Unicode;
    System.Text.Encoding  cTo  = System.Text.Encoding.Default;
    
    blob2 = System.Text.Encoding.Convert(cFrom, cTo, blob);
    
    using (var fs = new FileStream(destination, FileMode.Create, FileAccess.Write))
        {
        fs.Write(blob2, 0, blob2.Length);
        fs.Flush();
        }
    

    As I stated, these routines are “internal” serialization routines – you mess with one byte, and it likely to break. You don't need (nor want) to use that looping code to strip out anything - the above should convert correctly for you - and you WILL get the correct file size back!

    Good luck!

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by Love2Code2Much Tuesday, January 22, 2019 4:47 PM
    Friday, January 11, 2019 6:10 AM

All replies

  • > "The files are 2 times bigger than they should be"

    I think the Get Statement interprets the contents of the file as ASCII characters and pads each byte with a 0 to represent it as a VBA string. 

    fileField.AppendChunk (StrConv(fileBuffer, vbFromUnicode))

    This way you should get back the original bytes.

    Matthias Kläy, Kläy Computing AG

    Tuesday, January 8, 2019 11:11 PM

  • I can already get them back using VBA. But I'm trying to get them back using C# from the SQL Server VARBINARY(MAX) column they are stored in. There are hundreds of thousands of files and Access freezes and takes days to even save a portion of them. This won't work because I need to download them all within 1 day so that the I can implement a new storage feature for them in the project and get the users back up and running. They can't be down for days while I wait for Access to save them to file. Everything runs on the UI thread and it freezes. All the files are already stored in the SQL Server (using Access and the posted code above), but I need to retrieve them another way.

    I've gone through the Byte array and saw that every other byte was 0. I removed all of those empty bytes from the array and tried to save it to file. When I save the file using C# and save the same file using Access and open them in a text editor, they are almost identical except there are characters randomly missing from the one in C#.

    The accuracy is relative, but the file I am looking at is a PDF. Most of the characters in the text editor are the same.

    Here is an example of missing characters.

    You can see the second character is missing, and others:

    C#: H ¼UÝkÛHß¿b%8­vg¿!Z§)½#ÐbÁ=´Ç¡ ؍,;¶Ó4ÿýÍH²cÚ> ö·óý¡!r6WÐìAì ^ ï Þí Ö+] »VÜ á­ 5Q-þ HÚ ½ VÙ Ö2§S

    VBA: 

    H‰¼UÝkÛHß¿b%8­vg¿!Z§)½#ÐbÁ=´Ç¡“•Ø,;¶Ó4ÿýÍH²cÚ>– ö·óý¡™r6WÐìAì›^”ï Þí…Ö+]’»VÜŠ‡á­“5Q-þ†žHÚ ½‹VÙ ŸÖ2§S

    Wednesday, January 9, 2019 6:46 PM
  • Hum, we do have a problem here.

    Your VBA upload code (well, ok, it likely not yours!) did for some reason “covert” the files into UTF-32 (a guess on my part).

    So when the VBA code in Access un-corks the files, it un-converted for you and thus the VBA code works.

    What I suggest is take your resulting byte[] array in your .net code, and whack it with a conversion back.

    I just ran your posted up-load code, and while access EVEN after an upload shows the binary column as correct in size, if you check on sql server, you see DOUBLE the size was saved.

    As a result, writing out the file in .net fails.

    If you JUST before writing out the byte array in .net do this converting, then the output file DOES work.

            b2 = System.Text.Encoding.BigEndianUnicode.

                 GetBytes(System.Text.Encoding.UTF32.GetString(b))

    So, we convert your byte array into a string (UTF32), and then convert it back into a byte array assuming BigEnianUnicode.

    The above works for me, but the resulting file is not exactly half the size. So, I can’t say why this is the case? (anyone???).

    Anyway, the resulting pdf file(s) are able to be launched by my PDF viewer without issues if I add the one line of converting in my .net code.

    Give the above convert a try. (b, and b2 in above are byte[] arrays. The above was vb.net, but this should work in c#. (and I broke the line into 2 lines (for vb.net, it needs to be one line.

    As for speed and performance? Well, .net not going to run any faster than the VBA code. Keep in mind that both platforms are really just calling windows and system routines. So the speed of VBA code vs say .net code not going to make a difference here – it is all system “io” limited anyway.

    I should also point out that other VBA routines do NOT result in double the SQL (varbinary(max)) column showing double the size, but your posted code does.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Thursday, January 10, 2019 4:26 AM
  • I have tried so many variations Albert, but I am unable to get it to work. I've tried with removing the 0 bytes in the array (as in below code) and without. Here is one version of what I've tried:

            public string CopyFilesFromDbToDir(string table, string bytefield, string filename, string destination, string connection)
            {
                string select = "SELECT TOP 3 " + bytefield + ", " + filename + ", DATALENGTH(File_Data) AS DataSize FROM " + table + " WHERE File_Name LIKE '%.pdf'";
                string message = "";
                SqlConnection con = new SqlConnection(connection);
                try
                {
                    con.Open();
                    try
                    {
                        SqlCommand com = new SqlCommand(select, con);
                        com.CommandType = CommandType.Text;
                        SqlDataReader reader = com.ExecuteReader();
                        while (reader.Read())
                        {
                            var blob = new byte[Convert.ToInt32(reader[2].ToString())];
                            var blob2 = new byte[Convert.ToInt32(reader[2].ToString())];
                            reader.GetBytes(0, 0, blob2, 0, blob2.Length);
    
                            for (int i = 0; i < blob2.Length; i += 2)
                            {
                                blob[i / 2] = blob2[i];
                            }
    
                            blob = System.Text.Encoding.BigEndianUnicode.GetBytes(System.Text.Encoding.UTF32.GetString(blob));
                            using (var fs = new FileStream(destination + reader[1].ToString(), FileMode.Create, FileAccess.Write))
                            {
                                fs.Write(blob, 0, blob.Length);
                                fs.Flush();
                            }
                        }
                        message = "Files successfully downloaded from DB to " + destination;
                    }
                    catch (Exception e) { message = e.Message; }
                    finally { con.Close(); }
                }
                catch(Exception e) { message = e.Message; }
                finally { con.Close(); }
                return message;
            }
    Thursday, January 10, 2019 7:28 PM
  • Well, we can't use "trial and error" here.

    You not going to re-produce the uni-code to byte converting on your own (well, you might, but that's way too difficult).

    You have a bunch of int32 conv’s and all kinds of messing with the raw byte array.

    Do NOT mess with anything.

    Read the byte array, do the conversion I suggested, and then write out the data.

    Remember, you want to re-produce (reverse) that unfortunate conversion that windows (access) introduced. Do not chop bytes, or touch ONE thing in YOUR code.

    Do NOT introduce the Convert.ToInt32 etc. as you have.

    This code was able to un-cork a file uploaded with your posted VBA code:

       

         Dim command As New 
    
                SqlCommand("SELECT MyFile from BigIntTest where id = 1", connection)
    
            connection.Open()
    
            Dim reader As SqlDataReader = command.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)
    
            reader.Read()
    
            Dim b() As Byte
            b = reader(0)
    
            Dim b2() As Byte
    
            b2 = System.Text.Encoding.BigEndianUnicode.GetBytes(System.Text.Encoding.UTF32.GetString(b))
    
         Return b2

    Do NOT TOUCH or do anything else to that raw byte array. ONLY execute the above conversion into the resulting byte array, and then write that out to disk.

    (Your write out code looks fine to me).

    I not been able to determine why the file size did not match the original. However, if above works for a few pdfs, then you should be fine, and we can drink coffee for another day to figure out exactly why these file don’t match the original size. (I try this later today with a jpeg and see what occurs).

    The Encoding.BigEndianUnicode to a byte array will remove all the SECOND HIGH ORDER BYTES FOR YOU! You don’t need your own code (or loop) to chop out these bytes.

    You want to replicate EXACT what the system did. Remember, Access/VBA that writes out these files is doing this conversion for you also!!!

    You as so much as chop, touch, swap, change or split  ONE SINGLE byte in the whole array? Well then those system routines are screwed and will not work!!

    Only use the system routines that converted to/from Unicode to bytes. You mess with ANYTHING in that array? This will not work.

    ONLY execute that ONE converting on the data. The BigendianUnicode to bytes will chop out every 2<sup>nd</sup> byte for you.

    The problem is however “more complex” then every 2nd byte – we don’t want to “know” about how windows does this converting, but only call a routine that does all the dirty work for us. (it is to my knowledge somewhat more complex than JUST ignoring every 2nd byte, because of some uni-code extensions, it is NOT that simple). (Some charades can be 3 bytes). We are not going to reverse engineer that converting process - but only use it!!!

    Give above a try – don’t mess with anything else – let the windows converting routines strip out the every second byte (or so, or somewhat, or however the heck that crazy windows Unicode to byte routine does, or did, or is supposed to do).

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada



    Thursday, January 10, 2019 8:39 PM
  • With this code, I am able to open the PDF with a pdf reader. The dimensions of the PDF are correct. But they are all white, no graphics show or colors. Just a white rectangle. When I download the PDF with Access, its the same dimensions, that is how I can tell.

            public string CopyFilesFromDbToDir(string table, string bytefield, string filename, string destination, string connection)
            {
                string select = "SELECT TOP 3 " + bytefield + ", " + filename + ", DATALENGTH(File_Data) AS DataSize FROM " + table + " WHERE File_Name LIKE '%.pdf'";
                string message = "";
                SqlConnection con = new SqlConnection(connection);
                try
                {
                    con.Open();
                    try
                    {
                        SqlCommand com = new SqlCommand(select, con);
                        com.CommandType = CommandType.Text;
                        SqlDataReader reader = com.ExecuteReader();
                        while (reader.Read())
                        {
                            var blob = new byte[Convert.ToInt32(reader[2].ToString()) / 2];
                            var blob2 = new byte[Convert.ToInt32(reader[2].ToString())];
                            reader.GetBytes(0, 0, blob2, 0, blob2.Length);
                            blob2 = System.Text.Encoding.BigEndianUnicode.GetBytes(System.Text.Encoding.BigEndianUnicode.GetString(blob2));
                            for (int i = 0; i < blob2.Length; i += 2)
                            {
                                blob[i / 2] = blob2[i];
                            }
                            using (var fs = new FileStream(destination + reader[1].ToString(), FileMode.Create, FileAccess.Write))
                            {
                                fs.Write(blob, 0, blob.Length);
                                fs.Flush();
                            }
                        }
                        message = "Files successfully downloaded from DB to " + destination;
                    }
                    catch (Exception e) { message = e.Message; }
                    finally { con.Close(); }
                }
                catch(Exception e) { message = e.Message; }
                finally { con.Close(); }
                return message;
            }
    

    Thursday, January 10, 2019 8:39 PM
  • Ok, at least some progress here.

    My bad – don’t use

    System.Text.Encoding.BigEndianUnicode.

       GetBytes(System.Text.Encoding.UTF32.GetString(b))

    But use this:

    System.Text.Encoding.Default.

        GetBytes(System.Text.Encoding.Unicode.GetString(b))

    The above works for me.

    My c# is not that great (I can read it quite well, but not write without thinking).

    This bit of c# works for me:

                string select = "SELECT MyFile from BigIntTest where id = 1";
    
                SqlConnection con = new SqlConnection("SERVER=ALBERTKALLAL-PC\\SQLEXPRESS;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=test3");
    
                con.Open();
                SqlCommand com = new SqlCommand(select, con);
                com.CommandType = CommandType.Text;
                SqlDataReader reader = com.ExecuteReader();
                reader.Read();
    
                byte[] blob;
                byte[] blob2;
    
                blob = (byte[])reader[0];
    
                blob2 = System.Text.Encoding.Default.GetBytes(System.Text.Encoding.Unicode.GetString(blob));
    
                using (var fs = new FileStream(destination, FileMode.Create, FileAccess.Write))
    
                {
                    fs.Write(blob2, 0, blob2.Length);
                    fs.Flush();
                }
                return "ok";
    
            }
    

    As I stated, you can’t really make this conversion on your own.

    The windows system “converted” the original byte() stream into unicode. That is “universal”. So, you need to convert that Unicode back to the original byte format.

    (a windows computer running English text).

    Remember, “Hello” as Unicode is double in size. To display “hello” on YOUR computer, the UniCode is converted back from Unicode to “English” to only display the 5 characters.

    Of course, the Access solution messed you up, since conversion to Unicode should NOT have been allowed, nor have occurred.

    Note that the default for net is Unicode, but you STILL need to convert that back to a byte array based on your default code page (English).

    Also, if you looking for some additional speed, then we can get rid of the byte->to string->to byte conversion with this bit:

    reader.Read();
    byte[] blob;
    byte[] blob2;
    blob = (byte[])reader[0];
    
    System.Text.Encoding cFrom = System.Text.Encoding.Unicode;
    System.Text.Encoding  cTo  = System.Text.Encoding.Default;
    
    blob2 = System.Text.Encoding.Convert(cFrom, cTo, blob);
    
    using (var fs = new FileStream(destination, FileMode.Create, FileAccess.Write))
        {
        fs.Write(blob2, 0, blob2.Length);
        fs.Flush();
        }
    

    As I stated, these routines are “internal” serialization routines – you mess with one byte, and it likely to break. You don't need (nor want) to use that looping code to strip out anything - the above should convert correctly for you - and you WILL get the correct file size back!

    Good luck!

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by Love2Code2Much Tuesday, January 22, 2019 4:47 PM
    Friday, January 11, 2019 6:10 AM
  • I'm still not getting the correct file size (its double), and cannot open the PDF. But I don't understand how its double the size still. Visual Studio is on a 2016 Windows Server, and the machines used to persist the bytes from Access are Windows7, 8, 8.1, and Server 2008 R2. I have checked these machines and they all appear to have the same regional settings: English (United States)

    Here is the new code I am using as you suggested:

    while (reader.Read())
                        {
                            byte[] blob;
                            byte[] blob2;
                            blob = (byte[])reader[0];
                            blob2 = Encoding.Default.GetBytes(Encoding.Unicode.GetString(blob));
    
                            using (var fs = new FileStream(destination + reader[1].ToString(), FileMode.Create, FileAccess.Write))
                            {
                                fs.Write(blob, 0, blob.Length);
                                fs.Flush();
                            }
                        }

    I am going to go through the VBA code to make sure no other conversions are being made. Could it have anything to do with the driver being used by Access v.s. Visual Studio? The size of the data in the field and the way Access, DAO, or anything sends the data to the SQL Server? The SQL Server settings or Visual Studio settings?

    Give me some time and I will create an Access project and an Azure SQL database and publish it for anyone to look at. I will use the exact code from my project in case some VBA before or after DB persistence and File writing is effecting the encoding. Any other information I can include that would help? Settings in Access or Access's IDE? I will even provide a copy of the PDFs.

    As a side note, this database is not just PDFs. It has any file type the user felt like saving in the database. But that shouldn't matter. Just noting it so someone doesn't come up with a solution for PDFs somehow that wouldn't work for other files types. I realize its likely an encoding issue, but I'm not experienced enough with encoding to understand where in the encoding process the root of the issue lies precisely. 

    Is there is an issue with the code above I am now using?

    BTW, the language doesn't matter to me. I prefer C#, but VB.NET is fine too. I was just hoping to use multiple threads and error logging while downloading these hundreds of thousands of files, and be able to start/stop/pause the app while its working because this is alot of files to persist at once. Plus Access keeps freezing up, takes an eternity, and locks the UI so its hard to tell progress.


    Friday, January 11, 2019 2:04 PM
  •         fileField.AppendChunk (fileBuffer)
    

    What is Access doing to the binary data?


    Hi Love2Code2Much,

    What is the efffect of surrounding   fileBuffer   with parenthesis? I can remember from long ago that when surrounding an expression with parenthesis, Access does some "evaluation".

    What happens if you use:            fileField.AppendChunk fileBuffer

    Imb.

    Friday, January 11, 2019 7:34 PM
  • Great catch on your part. I upvoted this. The problem of course is this can fix this issue for the "future". However, the poster has a "large" number of existing files already done the wrong way!!! The poster is looking to decode the files from sql server to a local computer. And the poster has verified (I hope!!!) that all the files in the sql "blob" are double size.

    However, you are correct - a simple introduction of a set of () has created a rather HUGE mess here. Those "pesky" computers and a tiny change in syntax has resulted in a "major" problem for this poster.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Saturday, January 12, 2019 5:51 PM
  • Ok, a few things.

    Because you’re using a code page (your computers setting) to Unicode, when you go from Unicode back to the code page then your code page setting on the computer has to match the original computer settings.

    When I run the sample VBA code to save a file to sql server, we find double the size. This as we all agree should NOT have occurred. (As noted, my other VBA routine does NOT double the file size – so it not converting to Unicode).

    This suggests standard windows Unicode, but you STILL have to be careful.

    I assume you have a local copy of sql server running, and you save a test file to sql server. You run that sample un-cork to save a file, and all should work.

    You REALLY want to test this. This will verify that you have working code.

    Once you sure that works, then you can try/test against the sql server file that you have that is not working.

    This is why I suggested to remove your “byte” looping code.

    And this is also why I suggested to use the byte array converting code I posted as opposed to the byte->string->byte array code snip. Once again, while both work for me, why mess with fate?

    (Ie: just use the byte array converting approach).

    Right now, as I stated, the posted code I have works – but of course on MY computer!!!

    Your code page settings MUST match the original computers used to create the sql server file data. I am betting your settings do, buy they MIGHT not!!!

    It takes all of 15 seconds to check this. If you don't check this, then you spend days testing code.

    On your working computer, fire up the command line processor, and type in CHCP. This will display your current code page.

    You need to ensure that the code page setting on your computer matches the computers used to encode the Unicode.

    The topic of uni-code is “way” beyond the scope of a simple post here, but read the following:

    The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!)

    https://www.joelonsoftware.com/2003/10/08/the-absolute-minimum-every-software-developer-absolutely-positively-must-know-about-unicode-and-character-sets-no-excuses/

    If the file save to sql and retrieve works with your given code on YOUR computer, and then the data on that production sql server does not to YOUR computer, then the issue is the code page.

    If that sql server file is less than 10 gigs, then you can simply take a back up of the production sql file and restore/run it local on your computer with the free edition of sql express.

    However, if the file is larger than 10 gigs (or company policies etc) don't allow this, then I would STILL test against the free edition of sql server running local.

    A few quick few tests on your local computer with the given VBA code + your .net code to grab the files and see if they work).

    I had sql express already setup on my local laptop, so I just created a small table, ran the VBA code, and then the .net code to test this – less than 15 minutes to test this.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Saturday, January 12, 2019 5:55 PM
  • Having said all of above?

    You CAN specify the code page with this:

    Dim cFrom As System.Text.Encoding = System.Text.Encoding.Unicode

    Dim cto As System.Text.Encoding = System.Text.Encoding.GetEncoding(437)

    b2 = System.Text.Encoding.Convert(cFrom, cto, b)

    So you can try/test 850, or 437. However, just do a CHCP on your computer, and one of the original computers used.

    The encoding used here might not be an issue – but my best guess is that this is the issue.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada



    Saturday, January 12, 2019 6:29 PM
  • I checked the code pages and they match on all machines.

    The SQL Database is in Azure. We migrated to Azure a couple years back (The file storage feature of the Customer_Files table was implemented after migrating the db to Azure hosting). One of the reasons for removing the files from that table is because it takes like 6 hours to make a copy of the DB from Azure to Azure. I've tried downloading the database from Azure to do local testing and if my memory serves it took over a day.

    I am going to run the C# file saving code on the machines used to persist the files originally from Access and see if that works. I will run SQL Server Express on the machines too and see if I can solve or replicate the issue that way.

    Sunday, January 13, 2019 7:50 PM
  • It works! I can't believe I didn't notice the error in the code I last posted right before Imb's post. But either did anyone else. When I went to test on the local machines, I saw I was writing out to file from blob, instead of blob2.

    So what solved it was leaving the byte array alone and using Albert's suggestion:

    blob2 = Encoding.Default.GetBytes(Encoding.Unicode.GetString(blob));

    Which fixed the encoding issue. But it was my fault for not seeing that I was writing out the incorrect blob. Files are downloading as correct size, and open just fine! I'm sorry for not catching that! Thank you all so much for your help!! It even works on the 2016 server.

    Full working code if anyone ever has same dilemma:

    public string CopyFilesFromDbToDir(string table, string bytefield, string filename, string destination, string connection)
            {
                string select = "SELECT TOP 3 " + bytefield + ", " + filename + ", DATALENGTH(File_Data) AS DataSize FROM " + table + " WHERE File_Name LIKE '%.pdf'";
                string message = "";
                SqlConnection con = new SqlConnection(connection);
                try
                {
                    con.Open();
                    try
                    {
                        SqlCommand com = new SqlCommand(select, con);
                        com.CommandType = CommandType.Text;
                        SqlDataReader reader = com.ExecuteReader();
                        while (reader.Read())
                        {
                            byte[] blob;
                            byte[] blob2;
                            blob = (byte[])reader[0];
                            blob2 = Encoding.Default.GetBytes(Encoding.Unicode.GetString(blob));
    
                            using (var fs = new FileStream(destination + reader[1].ToString(), FileMode.Create, FileAccess.Write))
                            {
                                fs.Write(blob2, 0, blob2.Length);
                                fs.Flush();
                            }
                        }
                        message = "Files successfully downloaded from DB to " + destination;
                    }
                    catch (Exception e) { message = e.Message; }
                    finally { con.Close(); }
                }
                catch(Exception e) { message = e.Message; }
                finally { con.Close(); }
                return message;
            }

    I am going to do some more testing Monday with many more files and file types just to be sure it all works and there are no more hiccups, then I will come back and mark it as resolved. You have all helped me get a much better understanding of what Access is doing and about encoding. Life savers! Have a great day.

    Sunday, January 13, 2019 8:20 PM
  • Excellent!

    You could skip the reader->blob->blob2

    And go:

    To reader->”convert to blob2 to same some memory, but not a big deal.

    As I “warned”, you could be “really” hooped here due to this “accident”, since the original code page could have been different.

    To my knowledge, if the code page was a “moving target” from different computers then indeed you would have a real mess on your hands.

    All in all?

    Well, at least the issue of Unicode and the code page was great learning experiment – despite it not actually being your issue.

    Good luck!

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Sunday, January 13, 2019 10:13 PM