Created
January 18, 2016 22:20
-
-
Save josephbales/de3a7f1cc8bffe66db2f to your computer and use it in GitHub Desktop.
Latest homepage.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <!DOCTYPE html> | |
| <html> | |
| <head> | |
| <title>Test File</title> | |
| <meta http-equiv="Content-Type" content="text/html;charset=utf-8" /> | |
| <link rel="stylesheet" href="https://storage.googleapis.com/code.getmdl.io/1.0.6/material.green-blue.min.css"> | |
| <script src="https://storage.googleapis.com/code.getmdl.io/1.0.6/material.min.js"></script> | |
| <link rel="stylesheet" href="https://fonts.googleapis.com/icon?family=Material+Icons"> | |
| <!-- Uses a transparent header that draws on top of the layout's background --> | |
| <style> | |
| .demo-layout-transparent { | |
| background: url('demo.jpg') center / cover; | |
| } | |
| .mdl-layout__header, .mdl-layout__header .mdl-layout__drawer-button, | |
| .mdl-layout__header .mdl-navigation__link { | |
| /* This background is dark, so we set text to white. Use 87% black instead if | |
| your background is light. */ | |
| color: white; | |
| } | |
| </style> | |
| </head> | |
| <body> | |
| <div class="mdl-layout mdl-js-layout mdl-layout--fixed-header"> | |
| <header class="mdl-layout__header"> | |
| <div class="mdl-layout__header-row"> | |
| <!-- Title --> | |
| <span class="mdl-layout-title">Title</span> | |
| <!-- Add spacer, to align navigation to the right --> | |
| <div class="mdl-layout-spacer"></div> | |
| <!-- Navigation. We hide it in small screens. --> | |
| <nav class="mdl-navigation mdl-layout--large-screen-only"> | |
| <a class="mdl-navigation__link" href="">Blog</a> | |
| <a class="mdl-navigation__link" href="">Writings</a> | |
| <a class="mdl-navigation__link" href="">Projects</a> | |
| <a class="mdl-navigation__link" href="">Links</a> | |
| <a class="mdl-navigation__link" href="">Contact</a> | |
| <a class="mdl-navigation__link" href="">About</a> | |
| </nav> | |
| </div> | |
| </header> | |
| <div class="mdl-layout__drawer mdl-layout--small-screen-only"> | |
| <span class="mdl-layout-title">Title</span> | |
| <nav class="mdl-navigation"> | |
| <a class="mdl-navigation__link" href="">Blog</a> | |
| <a class="mdl-navigation__link" href="">Writings</a> | |
| <a class="mdl-navigation__link" href="">Projects</a> | |
| <a class="mdl-navigation__link" href="">Links</a> | |
| <a class="mdl-navigation__link" href="">Contact</a> | |
| <a class="mdl-navigation__link" href="">About</a> | |
| </nav> | |
| </div> | |
| <main class="mdl-layout__content"> | |
| <div class="page-content"> | |
| <div class="mdl-grid"> | |
| <div class="mdl-cell mdl-cell--6-col mdl-cell--8-col-tablet"> | |
| <article> | |
| <h2><a href="https://josephbales.wordpress.com/2015/09/15/error-converting-data-type-dbtype_dbtimestamp-to-datetime2/" target="_blank">Error converting data type DBTYPE_DBTIMESTAMP to datetime2</a></h2> | |
| <time>Tue, 15 Sep 2015 23:06:41 +0000</time> | |
| <div><p>Okay, so here is a post that has been months in the making.</p> | |
| <p>Lately I’ve been working on an <a href="https://en.wikipedia.org/wiki/Extract,_transform,_load" target="_blank">ETL</a> project, mostly the E and T parts. The project involves exporting data from a database via an <a href="https://en.wikipedia.org/wiki/Open_Database_Connectivity" target="_blank">ODBC</a> driver (<a href="http://supportline.microfocus.com/Documentation/AcucorpProducts/docs/v6_online_doc/acuodbc/acuodbct.htm" target="_blank">AcuODBC</a> to be exact). I don’t have any control over the database being accessed other than being able to extract data and neither the client nor I have much knowledge of how the database actually works. For my part I just know that I can access it via the AcuODBC driver.</p> | |
| <p>I am using .NET and C# to do the data extraction and translating, but I’ve run into an issue on several tables that has perplexed me for some time. It is the error message that I’ve used as the title of this post:</p> | |
| <pre>Error converting data type DBTYPE_DBTIMESTAMP to datetime2. | |
| </pre> | |
| <p>No matter how I was trying to get at the data, I kept getting this error. I tried <a href="https://msdn.microsoft.com/en-us/library/ms187928.aspx" target="_blank">CAST and CONVERT</a> SQL commands. I tried <a href="https://msdn.microsoft.com/en-us/library/ms162802.aspx" target="_blank">BCP</a>. I tried using <a href="https://msdn.microsoft.com/en-us/library/ms188029.aspx" target="_blank">SELECT INTO</a>. I tried <a href="https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx" target="_blank">SqlBulkCopy</a>. I finally got all the way down to using an <a href="https://msdn.microsoft.com/en-us/library/system.data.odbc.odbcdatareader(v=vs.110).aspx" target="_blank">OdbcDataReader</a> object to try to move the data, but I kept getting the error when I used <code>reader[index].ToString()</code> to access the data.</p> | |
| <p>But then I noticed there was a method called <a href="https://msdn.microsoft.com/en-us/library/system.data.odbc.odbcdatareader.getstring(v=vs.110).aspx" target="_blank">GetString</a> in the OdbcDataReader object. I tried it and by golly it worked! (If you have been wondering what the cause of this error has been all along, it is an invalid date that was entered into the database with the year 0000). Yes, it worked, but it worked sloooooooow. Veeeeery slow. It was processing about 2 records per second, which isn’t too bad until you scale that up to over 1.1 million records. The time to extract just that one table would be over 6 days! That would not do.</p> | |
| <p>I sat on it for a few days and thought about all the potential solutions. I could bulk copy all the rows except the ones in question, but then I’d have to go through each table and find the culprit(s) and write special select statements to exclude those rows, then special select statements to grab those rows minus the offending fields. Then I’d have to update my table with the omitted records. Not something I really wanted to do.</p> | |
| <p>Then today I noticed that when I used the <code>reader[index].ToString()</code> method to get the data, it came out at a much faster, more acceptable speed. Okay, so what if I wrap that in a try block and then in the catch block I use the slower GetString method? If that worked then only the offending data would get extracted using the slow GetString method and everything else would use the other method. And what do you know, it DID work! Here’s the code for posterity (yeah, I’m stripping out all the new lines and spaces).</p> | |
| <p><script src="https://gist.github.com/josephbales/b2f6d4b93113b6ae5ec5.js"></script></p><br /></div> | |
| </article> | |
| </div> | |
| </div> | |
| </div> | |
| <footer class="mdl-mini-footer"> | |
| <div class="mdl-mini-footer__left-section"> | |
| <div class="mdl-logo">Title</div> | |
| <ul class="mdl-mini-footer__link-list"> | |
| <li><a href="#">Help</a></li> | |
| <li><a href="#">Privacy & Terms</a></li> | |
| </ul> | |
| </div> | |
| </footer> | |
| </main> | |
| </div> | |
| </body> | |
| </html> | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment