tag:blogger.com,1999:blog-20219899305913022252024-03-04T21:02:16.184-08:00NYC DBAAnonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.comBlogger107125tag:blogger.com,1999:blog-2021989930591302225.post-72786671339204366402014-12-12T02:43:00.002-08:002014-12-12T02:43:34.650-08:00Unintentional Hiatus<div dir="ltr" style="text-align: left;" trbidi="on">
I clearly haven't been posting on this blog, due to multiple factors. <div>
<ol style="text-align: left;">
<li>I'm not a DBA any more, so the blog title doesn't make much sense. I've been doing exclusively Front Office-facing software engineering, mostly in Python, for the last 18 months.</li>
<li>I'm not in NYC. Since I started this blog, I've done stints in DC, Denver, and now London, where I'll be based for at least a couple years.</li>
<li>I've been writing internal technical blog posts for my firm. </li>
<li>I've been writing external expat blog posts on <a href="http://brooklynersabroad.wordpress.com/" target="_blank">a Wordpress blog</a>.</li>
<li>I'm now married, have a dog, and am training for a triathlon, so my supply of spare time has declined.</li>
</ol>
<div>
None of these situations are likely to change in the near future, so I wouldn't expect too much content here. I may spin up a new (more generally-named!) technical blog at some point in the future, and I will link this post to it. Til then, good luck to everyone and please enjoy the archives as long as Blogger keeps them active.</div>
</div>
</div>
Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com0tag:blogger.com,1999:blog-2021989930591302225.post-3500600869524748112013-08-02T08:21:00.003-07:002013-08-02T08:21:35.397-07:00Dealing with runaway DBMail messages<div dir="ltr" style="text-align: left;" trbidi="on">
Say you have a particular type of alert configured on your SQL Server. Maybe it sends an email any time there's blocking that lasts longer than a particular threshold.<br />
<br />
Then you have a long-running multi-threaded process that runs on a cluster and hammers your SQL Server with a couple hundred threads for hours, and some of them wind up blocking. You build up a lot of alerts, say 50,000. Your SMTP server can't handle all of them at once, so you (and your team) wind up getting a heavy stream of them for the next few hours or maybe even days.<br />
<br />
Sound like fun? Yeah, not to me either. <br />
<br />
There are multiple ways to handle this, but the cleanest is probably to kill the messages at their source. I'm assuming in this example that the messages have already been queued, so fixing your alert will have to come later. Here's the simplest way to deal with your message problem - stop your DBMail service broker objects, delete the messages, and start things back up:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">use msdb</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">exec sysmail_stop_sp</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">exec sysmail_delete_mailitems_sp @sent_before = '[DATE_OF_SCREWUP]'</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">exec sysmail_start_sp</span><br />
<br />
Your mail server admin and team members will thank you.</div>
Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com0tag:blogger.com,1999:blog-2021989930591302225.post-79595212156980901562013-08-02T08:18:00.001-07:002013-08-02T08:23:21.021-07:00Testing something<div dir="ltr" style="text-align: left;" trbidi="on">
{<br />
"Python":8,<br />
"C++":4,<br />
"SQL":9,<br />
"NoSQL":5,<br />
"C#":6<br />
}</div>
Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com0tag:blogger.com,1999:blog-2021989930591302225.post-17330475713906772022013-07-26T13:14:00.001-07:002013-07-26T13:16:22.961-07:00Commandments for scraping public data sources<div dir="ltr" style="text-align: left;" trbidi="on">
I've used a couple different flavors of publicly available data as data sources, and I can tell you that there's a good reason why firms pay for clean data. Not that even vendor-processed data is usually 100% clean, but that's a different story. Anyway, I've learned a few things the hard way about scraping public data:<br />
<br />
<ol style="text-align: left;">
<li><b>Use a resilient HTML parsing engine</b>. BeautifulSoup is great, and makes it very easy to explore HTML structures, but you'll almost certainly want the LXML backend to avoid blowing up on unclosed tags and nonstandard nesting.<br /><br /></li>
<li><b>Always call strip()</b>. I've had a couple of import busts in which things weren't matching that really looked like they should. 90% of the time it was because there was random whitespace that had infiltrated the actual data. This also leads me to...<br /><br /></li>
<li><b>Anticipate spacing changes.</b> ESPECIALLY with hand-entered data, even if there's a template, there can always be an extra line between the logo and the header, a blank line between data rows, or new spacing for the file date. Whenever possible, search for a reference value that points the way to the data, and throw away those newlines rather than expecting data by default.<br /><br /></li>
<li><b>Keep original copies</b>. The first thing you should do with any parsed source is make a raw copy of it (if you can) so that you can refer back to it when #5 happens.<br /><br /></li>
<li><b>Expect change</b>! No warranty for most public data sources is granted, implied, or hinted at, and sometimes the exact opposite is the case: public data "providers" happily change formats, addresses, and datasets available to stymie anyone making systematic use of such. Make sure you've got good logging and debugging set up so you can figure out quickly where and how something changed.<br /><br /></li>
<li><b>Make things modular</b>. Building on #5 above, if you've got your parser set up to quickly swap out the downloader, parser, normalizer, or persister (for example) for any given source and toggle those sources on and off easily, it'll make things much easier when you need to quickly hack up a new one because somebody changed something somewhere.<br /><br /></li>
<li><b>Handle multiple formats. </b> I've parsed many Excel spreadsheets that usually have an XLDate in a column but, every so often, spit out a text string instead because of the way someone typed it or copied it in. If your date parser function knows about this, you don't have to think about it.<br /><br /></li>
<li><b>Don't hammer</b>. When you're using someone else's data that they're not explicitly making available in a structured format, be nice, download one copy and build your import off of that. Otherwise you're putting undue load on their servers and exposing your IP to possible banning if they decide you're violating their ToS.<br /><br /></li>
<li><b>Inheritance is your friend</b>. Most of the parsers I've written have a lot of common structure that needs a little tweaking for certain sources. If you've built a solid class hierarchy, you can easily override that save() method in the 2 subclasses that need it while only needing to write a basic one for the other dozen. Any time I find myself copying code, I generally try to move it up to the superclass.<br /><br /></li>
<li><b>Pad your dev time estimate</b>! This is a general problem I have, but I always look at a source, pretty quickly shred the data out with bs4 or something similar, and go "yeah, this'll take 2 days." Sure, to code-complete. Then it'll take a week to figure out every stupid corner case, extra whitespace location, and placeholder for None. Trust me (and this goes double for you, future me, when you're re-reading this).</li>
</ol>
</div>
Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com0tag:blogger.com,1999:blog-2021989930591302225.post-41589522156383219552013-06-26T08:36:00.000-07:002013-06-26T08:36:01.941-07:00GetBytes error when using a DATE column<div dir="ltr" style="text-align: left;" trbidi="on">
Quick troubleshooting post. Given this error from a colleague upon execution of a pretty simple function that used a DATE column:<div>
<br /></div>
<div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 8.0pt;">An error occurred while executing batch. Error
message is: Invalid attempt to GetBytes on column 'RelDate'. The GetBytes
function can only be used on columns of type Text, NText, or Image.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div>
I couldn't reproduce the problem on my machine - the same function worked fine for me. Checked all his query execution settings and didn't see anything, and then he realized he was running SQL Management Studio 2005 connected to a 2008 R2 instance. As soon as he fired up SMS 2008 R2, the function worked fine.</div>
</div>
</div>
Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com0tag:blogger.com,1999:blog-2021989930591302225.post-63670818343621460422013-05-31T09:06:00.000-07:002013-05-31T09:12:13.129-07:00Why SQL Server Replication depends on Database Mirroring<div dir="ltr" style="text-align: left;" trbidi="on">
Had a convoluted replication problem this morning that turned out to be caused by mirroring. Users noticed a lack of data on a replicated server that was quickly traced back to replication failing to deliver commands. The Log Reader Agent noted the following:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">Replicated transactions are waiting for next Log backup or for mirroring partner to catch up</span><br />
<br />
There was nothing wrong with log backups, so mirroring was the next thing to be checked. It was suspended, and attempting to resume it failed. Checking the Mirroring Monitor yielded nothing helpful, but the SQL Server error log on the host server showed the following:<br />
<br />
<br />
<br />
<span 12px="" text-indent:="">
<span style="font-family: Courier New, Courier, monospace;">Date<span class="Apple-tab-span" style="white-space: pre;"> </span>x/xx/xxxx x:xx:xx AM</span><br />
<span style="font-family: Courier New, Courier, monospace;">Log<span class="Apple-tab-span" style="white-space: pre;"> </span>SQL Server (Current - x/xx/xxxx x:xx:00 AM)</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">Source<span class="Apple-tab-span" style="white-space: pre;"> </span>spid34s</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">Message</span><br />
<span style="font-family: Courier New, Courier, monospace;">'TCP://foo.bar.com:5022', the remote mirroring partner for database 'SomeDB', encountered error 3624, status 1, severity 20. Database mirroring has been suspended. Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance.</span><br />
</span><br />
<div>
<span 12px="" text-indent:=""><br /></span></div>
<span 12px="" text-indent:="">
</span>
<div>
<span 12px="" text-indent:=""><br /></span></div>
<span 12px="" text-indent:="">
<br />
On the mirrored server, there were additional errors, including a stack dump, but this was the most relevant:<br />
<br />
<br />
<span 12px="" text-indent:="">
<span style="font-family: Courier New, Courier, monospace;"></span><br />
<span style="font-family: Courier New, Courier, monospace;">Date<span class="Apple-tab-span" style="white-space: pre;"> </span>x/xx/xxxx x:xx:xx AM</span><br />
<span style="font-family: Courier New, Courier, monospace;">Log<span class="Apple-tab-span" style="white-space: pre;"> </span>SQL Server (Current - x/xx/xxxx<span style="font-family: Courier New, Courier, monospace;"> x:xx</span>:00 AM)</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">Source<span class="Apple-tab-span" style="white-space: pre;"> </span>spid39s</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">Message</span><br />
<span style="font-family: Courier New, Courier, monospace;">Error: 3624, Severity: 20, State: 1.</span><br />
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">---</span></div>
<br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">Date<span class="Apple-tab-span" style="white-space: pre;"> </span></span><span style="font-family: 'Courier New', Courier, monospace;">x</span><span style="font-family: 'Courier New', Courier, monospace;">/xx/xxxx x:xx:xx</span><span style="font-family: Courier New, Courier, monospace;"> AM</span><br />
<span style="font-family: Courier New, Courier, monospace;">Log<span class="Apple-tab-span" style="white-space: pre;"> </span>SQL Server (Current - </span><span style="font-family: 'Courier New', Courier, monospace;">x/xx/xxxx</span><span style="font-family: Courier New, Courier, monospace;"> x:xx:00 AM)</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">Source<span class="Apple-tab-span" style="white-space: pre;"> </span>spid39s</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">Message</span><br />
<span style="font-family: Courier New, Courier, monospace;">SQL Server Assertion: File: <loglock .cpp="">, line=807 Failed Assertion = 'result == LCK_OK'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.</loglock></span><br />
</span>
<div>
<br /></div>
<div>
Severity 20 == bad. This basically comes down to corruption on the mirror target, which irreparably broke mirroring. This, in turn, broke replication. </div>
<div>
<br /></div>
<div>
So why, the technical user asked, does replication between business-critical Production servers depend on mirroring to a disaster recovery environment? I had to think about that one, but there's a good reason.</div>
<br />
<br />
Assume replication does <i>not</i> depend on mirroring. Server A is both the replication publisher and mirror source. It replicates rows to Server B and mirrors to Server C. Mirroring breaks at time t0. Rows continue to replicate, and are marked as replicated on Server A. <br />
<br />
Server A fails over to Server C at time t1. Replication picks up from there with Server C as published. <b>Server C republishes the rows between t0 and t1 to Server B because it does not have a record of those rows having been replicated.</b><br />
<br />
Makes sense now, right?<br />
</span></div>
Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com1tag:blogger.com,1999:blog-2021989930591302225.post-11257164165650243132012-09-18T07:02:00.002-07:002012-09-18T07:03:03.424-07:00sqlcmd swallows errors by defaultI'm sure this is well-known out in SQL land, but I keep forgetting about it, so I'm writing this short post as a way to cement the knowledge in my brain: <a href="http://msdn.microsoft.com/en-us/library/ms162773.aspx">sqlcmd</a>, the current command line utility for running T-SQL and replacement for OSQL, does not return a DOS ERRORLEVEL > 0 by default, even when the command being executed fails.<br />
<br />
You need to pass the <b>-b </b>flag to raise an ERRORLEVEL 1 when a real error (SQL error severity > 10) occurs, which is crucial when running batch jobs in job systems that report status based on DOS ERRORLEVELs.Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com0tag:blogger.com,1999:blog-2021989930591302225.post-27300019836642094862012-07-12T11:17:00.000-07:002012-07-12T11:17:39.002-07:00pyodbc autocommits when using "with"Discovered an interesting variation in pyodbc behavior today. A coworker asked me if he needed to commit explicitly when using pyodbc to execute a stored procedure. I told him he did, but he protested that his code worked fine without it. Here's a paraphrased version:<br />
<br />
def db_exec(qry):<br />
with pyodbc.connect(cnxn_str) as db:<br />
db.execute(proc)<br />
<br />
I tested it, and what do you know? It works. But the following does not:<br />
<br />
db = pyodbc.connect(cnxn_str)<br />
db.execute(proc)<br />
db.close()<br />
<br />
In this case, the results of <i>proc</i> will be rolled back automatically when closing the connection. So evidently the way that the <i>with </i>handler is coded includes an autocommit.<br />
<br />Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com0tag:blogger.com,1999:blog-2021989930591302225.post-62501679518331315372012-06-20T11:34:00.002-07:002012-06-20T11:37:18.041-07:00Stripping time from GETDATE(), 2008 editionStripping the time from GETDATE() to get the date at 00:00:00 is a common practice, and has been accomplished by various methods in the past, including CASTing to VARCHAR and back, using DATEADD and DATEDIFF, and using FLOOR after casting to INT. It should be less necessary these days since the introduction of the DATE data type, but sometimes it's still a useful comparison trick. <br />
<br />
And now because of DATE there's a simpler way to do the conversion: CAST(@dt as DATE). But is this any faster?<br />
<br />
The short answer is no, but it doesn't seem to be any slower either. I ran a quick test:<br />
<br />
<br />
<pre style="background: #ffffff; color: black;"><span style="color: maroon; font-weight: bold;">declare</span> <span style="color: #797997;">@dtest</span> datetime
<span style="color: maroon; font-weight: bold;">declare</span> <span style="color: #797997;">@i</span> <span style="color: maroon; font-weight: bold;">int</span> <span style="color: #808030;">=</span> <span style="color: #008c00;">0</span><span style="color: #808030;">,</span> <span style="color: #797997;">@j</span> <span style="color: maroon; font-weight: bold;">int</span> <span style="color: #808030;">=</span> <span style="color: #008c00;">0</span>
<span style="color: maroon; font-weight: bold;">declare</span> <span style="color: #797997;">@start</span> datetime
<span style="color: maroon; font-weight: bold;">set</span> <span style="color: #797997;">@start</span> <span style="color: #808030;">=</span> <span style="color: #bb7977; font-weight: bold;">GETDATE</span><span style="color: #808030;">(</span><span style="color: #808030;">)</span>
<span style="color: maroon; font-weight: bold;">while</span> <span style="color: #797997;">@i</span> <span style="color: #808030;"><</span> <span style="color: #008c00;">1000000</span>
<span style="color: maroon; font-weight: bold;">begin</span>
<span style="color: maroon; font-weight: bold;">set</span> <span style="color: #797997;">@dtest</span> <span style="color: #808030;">=</span> <span style="color: #bb7977; font-weight: bold;">DATEADD</span><span style="color: #808030;">(</span>dd<span style="color: #808030;">,</span> <span style="color: #008c00;">0</span><span style="color: #808030;">,</span> <span style="color: #bb7977; font-weight: bold;">DATEDIFF</span><span style="color: #808030;">(</span>dd<span style="color: #808030;">,</span> <span style="color: #008c00;">0</span><span style="color: #808030;">,</span> <span style="color: #bb7977; font-weight: bold;">GETDATE</span><span style="color: #808030;">(</span><span style="color: #808030;">)</span><span style="color: #808030;">)</span><span style="color: #808030;">)</span>
<span style="color: maroon; font-weight: bold;">set</span> <span style="color: #797997;">@i</span> <span style="color: #808030;">=</span> <span style="color: #797997;">@i</span> <span style="color: #808030;">+</span> <span style="color: #008c00;">1</span>
<span style="color: maroon; font-weight: bold;">end</span>
<span style="color: maroon; font-weight: bold;">print</span> <span style="color: #bb7977; font-weight: bold;">datediff</span><span style="color: #808030;">(</span>ms<span style="color: #808030;">,</span> <span style="color: #797997;">@start</span><span style="color: #808030;">,</span> <span style="color: #bb7977; font-weight: bold;">getdate</span><span style="color: #808030;">(</span><span style="color: #808030;">)</span><span style="color: #808030;">)</span>
<span style="color: maroon; font-weight: bold;">set</span> <span style="color: #797997;">@start</span> <span style="color: #808030;">=</span> <span style="color: #bb7977; font-weight: bold;">GETDATE</span><span style="color: #808030;">(</span><span style="color: #808030;">)</span>
<span style="color: maroon; font-weight: bold;">while</span> <span style="color: #797997;">@j</span> <span style="color: #808030;"><</span> <span style="color: #008c00;">1000000</span>
<span style="color: maroon; font-weight: bold;">begin</span>
<span style="color: maroon; font-weight: bold;">set</span> <span style="color: #797997;">@dtest</span> <span style="color: #808030;">=</span> <span style="color: maroon; font-weight: bold;">CAST</span><span style="color: #808030;">(</span><span style="color: #bb7977; font-weight: bold;">GETDATE</span><span style="color: #808030;">(</span><span style="color: #808030;">)</span> <span style="color: maroon; font-weight: bold;">as</span> <span style="color: maroon; font-weight: bold;">DATE</span><span style="color: #808030;">)</span>
<span style="color: maroon; font-weight: bold;">set</span> <span style="color: #797997;">@j</span> <span style="color: #808030;">=</span> <span style="color: #797997;">@j</span> <span style="color: #808030;">+</span> <span style="color: #008c00;">1</span>
<span style="color: maroon; font-weight: bold;">end</span>
<span style="color: maroon; font-weight: bold;">print</span> <span style="color: #bb7977; font-weight: bold;">datediff</span><span style="color: #808030;">(</span>ms<span style="color: #808030;">,</span> <span style="color: #797997;">@start</span><span style="color: #808030;">,</span> <span style="color: #bb7977; font-weight: bold;">getdate</span><span style="color: #808030;">(</span><span style="color: #808030;">)</span><span style="color: #808030;">)</span>
</pre>
<br />
In half a dozen runs, the two versions never varied by more than a handful of ms. Feel free to try on your own and let me know if you see anything different.Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com0tag:blogger.com,1999:blog-2021989930591302225.post-6781805255858260802012-03-30T07:34:00.003-07:002012-03-30T07:37:21.504-07:00Another hazard of Laptopistan - slow WiFiThis is hardly unexpected, but one of the side effects of everyone using Starbucks as a de facto workplace and internet cafe is that their WiFi is heavily stressed. People watching online video, using Remote Desktop (like me), probably even BitTorrent. I don't know what sort of routers they put in these coffee shops, but they often seem to be unequal to the load they're handling. <div><br /></div><div>This morning I'm in a Starbucks, and my connection went from perfectly fine, at 8:30 AM before anyone was here, to barely usable now that I'm surrounded by laptops. One more reason for actual workspace.</div>Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com1tag:blogger.com,1999:blog-2021989930591302225.post-19500735237195628812012-03-16T14:11:00.002-07:002012-03-16T14:26:11.750-07:00Sliding Window partitioning<span >Implemented my first "sliding window" partitioned table. Wound up being easier than I expected, especially since the table will only have 2 partitions for now - Current and Archive. </span><div style="font-family: Georgia, serif; font-weight: normal; "><br /></div><div style="font-family: Georgia, serif; font-weight: normal; ">Most sliding window schemes I've seen out there on the intertubes make use of a date as the partition key and update the sliding window based on the current date. In this case, the data comes in somewhat sporadically, so although it's date-based (a month key), I made the partitioning data-driven instead of date-driven. So for starters we have a RANGE LEFT scheme like this:</div><div style="font-family: Georgia, serif; font-weight: normal; "><br /></div><div style="font-family: Georgia, serif; "><b>Partition<span class="Apple-tab-span" style="white-space:pre"> </span>Month Key</b></div><div style="font-family: Georgia, serif; font-weight: normal; ">Current<span class="Apple-tab-span" style="white-space:pre"> </span>-</div><div style="font-family: Georgia, serif; font-weight: normal; ">Archive<span class="Apple-tab-span" style="white-space:pre"> </span>88</div><div style="font-family: Georgia, serif; font-weight: normal; "><br /></div><div style="font-family: Georgia, serif; font-weight: normal; ">Say we're currently on month 90. So months 89 and 90 are in Current, and 1-88 are in Archive. When the sliding window proc runs, it does essentially this:</div><div style="font-family: Georgia, serif; font-weight: normal; "><br /></div><div style="font-weight: normal; "><span >SELECT @NewBoundary = MAX(monthKey)-2 FROM tbPartitionedFoo</span></div><div style="font-weight: normal; "><span >SELECT @CurBoundary = CAST(prv.value as smallint) FROM sys.partition_functions AS pf</span></div><div style="font-weight: normal; "><span ><span class="Apple-tab-span" style="white-space:pre"> </span>JOIN sys.partition_range_values as prv</span></div><div style="font-weight: normal; "><span ><span class="Apple-tab-span" style="white-space:pre"> </span>ON prv.function_id = pf.function_id</span></div><div style="font-weight: normal; "><span ><span class="Apple-tab-span" style="white-space: pre; "> </span>WHERE pf.name = 'partfn_foo'</span></div><div style="font-family: Georgia, serif; font-weight: normal; "><br /></div><div style="font-family: Georgia, serif; font-weight: normal; ">(If there was more than one partition range value, a MIN() or something would be needed to grab the appropriate value.)</div><div style="font-weight: normal; "><span ><br /></span></div><div style="font-weight: normal; "><span >IF @NewBoundary > @CurBoundary</span></div><div style="font-weight: normal; "><span >BEGIN</span></div><div style="font-weight: normal; "><span ><span class="Apple-tab-span" style="white-space:pre"> </span>ALTER PARTITION FUNCTION partfn_foo() SPLIT RANGE (@NewBoundary)</span></div><div style="font-weight: normal; "><span ><span class="Apple-tab-span" style="white-space:pre"> </span>ALTER PARTITION FUNCTION <span style="font-size: 100%; ">partfn_foo() MERGE RANGE (@CurBoundary)</span></span></div><div style="font-weight: normal; "><span style="font-size: 100%; "><span >END</span></span></div><div style="font-family: Georgia, serif; font-weight: normal; "><br /></div><div style="font-family: Georgia, serif; font-weight: normal; ">So when we start getting monthKey 91 data and the maintenance proc runs, it will do this:</div><div style="font-family: Georgia, serif; font-weight: normal; "><br /></div><div style="font-weight: normal; "><span >IF 89 > 88</span></div><div style="font-weight: normal; "><span >BEGIN</span></div><div style="font-weight: normal; "><span ><span class="Apple-tab-span" style="white-space:pre"> </span><span style="font-size: 100%; ">ALTER PARTITION FUNCTION partfn_foo() SPLIT RANGE (89)</span></span></div><div style="font-family: Georgia, serif; font-weight: normal; "><span style="font-size: 100%; "><br /></span></div><div style="font-family: Georgia, serif; font-weight: normal; "><span style="font-size: 100%; ">Now we have 3 partitions:</span></div><div style="font-family: Georgia, serif; font-weight: normal; "><span style="font-size: 100%; "><br /></span></div><div style="font-family: Georgia, serif; font-weight: normal; "><div><b>Partition<span class="Apple-tab-span" style="white-space: pre; "> </span>Month Key</b></div><div>Current<span class="Apple-tab-span" style="white-space: pre; "> </span>-</div><div>temp<span class="Apple-tab-span" style="white-space:pre"> </span>89</div><div>Archive<span class="Apple-tab-span" style="white-space: pre; "> </span>88</div></div><div style="font-family: Georgia, serif; font-weight: normal; "><span style="font-size: 100%; "><br /></span></div><div style="font-weight: normal; "><div><span ><span class="Apple-tab-span" style="white-space: pre; "> </span>ALTER PARTITION FUNCTION partfn_foo() MERGE RANGE (88)</span></div><span >END</span></div><div style="font-family: Georgia, serif; font-weight: normal; "><br /></div><div style="font-family: Georgia, serif; font-weight: normal; ">And now we're back to 2:</div><div style="font-family: Georgia, serif; font-weight: normal; "><br /></div><div style="font-family: Georgia, serif; font-weight: normal; "><div><b>Partition<span class="Apple-tab-span" style="white-space: pre; "> </span>Month Key</b></div><div>Current<span class="Apple-tab-span" style="white-space: pre; "> </span>-</div><div>Archive<span class="Apple-tab-span" style="white-space: pre; "> </span>89</div></div>Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com0tag:blogger.com,1999:blog-2021989930591302225.post-25232247913540504272012-03-05T19:46:00.003-08:002012-03-05T19:51:53.593-08:00Python Gets Things DoneA quick example of why I've grown to love Python.<div><br /></div><div>My girlfriend had a laboriously scanned document in two pieces that needed to be combined. I had a .NET command line utility that I wrote a couple years ago that does just that, but because one of the scanned pages was a different size, it got cut off in the resulting output file. So I could either try to dig up some docs on the library I used, play around with Intellisense in Visual Studio, or see if there was a Python library that might work.</div><div><br /></div><div>2 minutes of Googling later I had <a href="http://pybrary.net/pyPdf/">pyPDF</a>, and maybe 10 minutes after that, I had reset the mediaBox on the outsized page, written it to a new pdf file, and emailed it off. Q.E.D.</div>Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com0tag:blogger.com,1999:blog-2021989930591302225.post-21244353354530392302012-03-05T13:32:00.003-08:002012-03-05T13:52:11.124-08:00DATEs aren't INTs, at least not any more<div>I updated a database column today to utilize the SQL 2008 DATE type, upgrading from a SMALLDATETIME which always had a 00:00:00 hh:mm:ss component. I was fairly certain that this would not break any existing code, so I did not perform an exhaustive code search. My mistake.</div><div><br /></div><div>A function broke with the following message:</div><div><br /></div><div><div><span >Msg 206, Level 16, State 2, Line 1</span></div><div><span >Operand type clash: date is incompatible with int</span></div></div><div><br /></div><div>"Strange," I thought. "How would an int be getting used as a date?" </div><div><br /></div><div>Of course there's the basic method of datetime construction from a string: CAST("2012-01-01" as datetime), but that doesn't involve any INTs. Did someone try to use a similar INT (20120101) in place of the string? No, because that would cause an arithmetic overflow...</div><div><br /></div><div>Well, it turns out that there's another way of using INTs as DATETIMEs:</div><div><br /></div><div>select cast(CAST('2012-01-10' as datetime) as int)</div><div><br /></div><div>Apparently this doesn't work any more:</div><div><br /></div><div>select cast(CAST('2012-01-10' as date) as int) </div><div><br /></div><div>And this is what was being performed in the function in question:</div><div><br /></div><div>...</div><div>CASE WHEN io_end > 40947 THEN 1 ELSE 0 END</div><div>...</div><div><br /></div><div>Which promptly broke when I changed the column to a DATE. So let this be a warning: even something as innocuous as a DATETIME to DATE change needs QA and code search.</div>Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com0tag:blogger.com,1999:blog-2021989930591302225.post-74662961637720395372012-02-23T12:59:00.003-08:002012-02-23T13:13:41.589-08:00Laptopistan is getting crowdedThis is purely anecdotal, of course, but on the occasions when I've ventured out from my home office to get some fresh air or avoid being pestered by my dog, I've seen more and more people working remotely. Your average Starbucks is completely overrun with laptop-facing contractors, remote workers, and students these days, as are most other public areas with seating and free WiFi. Getting a table all to yourself is becoming rare, and forget about finding an open power outlet!<div style="font-weight: normal; "><br /></div><div style="font-weight: normal; ">This says to me that there is a serious hole in the co-working space market. Assuming these Starbucks campers order an average of a $4 latte per day (some get black coffee, which is cheaper, but some add a muffin), and the cost of a dedicated desk at Affinity Lab is on the order of $900, that works out to a price differential of $800/mo. Surely there must be a point somewhere in the middle for which one could add certain beneficial services while keeping the cost enough to attract customers.</div><div style="font-weight: normal; "><br /></div><div style="font-weight: normal; ">Starbucks, after all, does not exactly inspire loyalty - the seating is functional, but rarely comfortable; there are never enough outlets; the coffee is okay but somewhat expensive; the WiFi is often slow; the environment is noisier than a worker would prefer; and the venues themselves hardly encourage hard work. If I could pay $200/mo for a similarly casual but better-outfitted space in which I could work, chat, and drink coffee, I would jump at the chance. I've been trying to check out DCIOLab, but they haven't emailed me back.</div><div style="font-weight: normal; "><br /></div><div style="font-weight: normal; ">Makes me think there's a definite business opportunity here, but certainly a risky one. Timing is important - is NOW the inflection point in density of remote workers, or a year from now? You can quickly go broke renting a commercial space for a year with not enough customers. Is there enough consistency in the office support requirements of the proposed customers? A good coffee pot or two (or a Chemex or Aeropress, maybe) is definitely necessary, but a copier? Fax machine? Phones? A conference room? </div><div style="font-weight: normal; "><br /></div><div>These are ways to differentiate a <b>work</b>space from a Starbucks, but I think they may raise the cost too much and get too little use. I don't need any of those things besides the coffee-making apparatus. In fact, all I want is coffee, power, internet, good light, and preferably high ceilings. Could one provide those things, pack people in as tightly as they do at Starbucks, and make money charging $200/mo? </div>Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com0tag:blogger.com,1999:blog-2021989930591302225.post-42329911432833504652012-02-16T07:31:00.001-08:002012-02-16T07:41:13.503-08:00Query plan troubleshootingI was troubleshooting a medium-complexity query problem this morning in which the query execution within an app was orders of magnitude slower than the execution with the same parameters within Management Studio. I knew this could be due to different cached plans, but couldn't remember why.<div><br /></div><div>Fortunately, Erland Sommerskog had written a detailed and helpful article explaining why this happens: <a href="http://www.sommarskog.se/query-plan-mysteries.html">http://www.sommarskog.se/query-plan-mysteries.html</a></div><div><br /></div><div>And indeed, the app was using ODBC and therefore the results of sys.dm_exec_plan_attributes showed ARITHABORT OFF, while my instance of Management Studio had it set to ON, therefore generating a separate cached plan. I'm going to update my settings in SSMS to set this off by default, especially since it appears that it has no effect with ANSI_WARNINGS set to on, other than to foil attempts to troubleshoot slow procs.</div>Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com0tag:blogger.com,1999:blog-2021989930591302225.post-57120838490656345152012-01-19T07:31:00.000-08:002012-01-19T07:33:34.171-08:00Refreshing Intellisense in SQL Management Studio 2008If you're like me, you create and drop a lot of tables while developing, and you may be getting annoyed with SQL Server Management Studio because it fills in the names of old tables while you're typing unless you hit Escape. Turns out that refreshing the Intellisense cache is as easy as <div><br /></div><div>Ctrl-Shift-R</div>Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com0tag:blogger.com,1999:blog-2021989930591302225.post-7551294279064229792012-01-18T10:12:00.001-08:002012-01-18T10:28:06.423-08:00If your DATE comes through pyodbc as a unicode, it's probably the driverFile this under "should have been obvious." I had a test script break today because I was trying to do date math on an object that wound up being a <span>unicode</span> instead of a <span>date</span>. I had gotten the variable value from a pyodbc query against a SQL 2008 database table with a DATE column. I knew that DATETIME columns came in as <span>datetime.datetime</span> objects, but the DATE column came in as a <span>unicode</span>, which seemed strange. <div><br /></div><div>Turns out it's the ODBC driver. Demo:<p></p></div><div style="position:relative;padding-bottom:0px;padding-left:3px;padding-right:3px;clear:both;padding-top:0px" id="ColorBandedcontent"><div style="margin-left:12px" id="imcontent"> <div style="direction: ltr; font-size: 9pt; ">In [78]: cnxn = pyodbc.connect("DSN=local<span style="font-size: 12px; ">;")</span><p style="font-size: 12px; "></p><p style="font-size: 12px; ">In [79]: crsr = cnxn.execute("select cast('2012-01-01' as date) as bar")</p><p style="font-size: 12px; ">In [80]: r = crsr.fetchone()</p><p style="font-size: 12px; ">In [81]: type(r.bar)<br />Out[81]: unicode</p></div></div><p><span >Then, when switching to SQLNCLI10</span><span style="font-size: 16px; ">:</span></p></div><div><div style="position:relative;padding-bottom:0px;padding-left:3px;padding-right:3px;clear:both;padding-top:0px" id="ColorBandedcontent"><div style="margin-left:12px" id="imcontent"> <div style="direction: ltr; font-size: 9pt; ">In [96]: cnxn = pyodbc.connect("Driver={SQL Server Native Client 10.0};Server=localhost;Trusted_Connection=Yes;") <p></p> <p>In [97]: crsr = cnxn.execute("select cast('2012-01-01' as date) as bar")</p> <p>In [98]: r = crsr.fetchone()</p> <p>In [99]: type(r.bar)<br />Out[99]: datetime.date</p></div></div></div></div>Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com2tag:blogger.com,1999:blog-2021989930591302225.post-87762498825804133812012-01-17T13:04:00.001-08:002012-01-18T10:29:44.995-08:00Faster, thinner, lighterIt's almost time to retire my trusty old Dell E4300. The warranty just expired, which means that if Dell has optimized their average component MTBF vs warranty span correctly, the laptop will implode shortly. I replaced the original hard drive with a 2nd-gen Sandforce SSD a year ago, and it's made a big difference, but I'm ready for a little more speed. And, with the advent of the "Ultrabook" form factor, for even less weight and size. I've decided I want one.<div><br /></div><div>At CES this year, there were plenty of ultrabooks on display - <a href="http://www.engadget.com/2012/01/14/ces-2012-ultrabook-round-up/">Engadget's roundup</a> has a few, more on <a href="http://www.anandtech.com/show/5418/ces-2012-recap-the-week-in-review">Anandtech</a>). And most of them seemed to get at least some part of the formula right, but I don't think any of them have quite grabbed me yet. Maybe I'm being picky, but I've decided that my top priorities are:</div><div><br /></div><div>- 1600 x 900 resolution</div><div>- Thunderbolt port (for docking)</div><div>- decent battery life (5 hrs+)</div><div>- light weight</div><div><br /></div><div>I'm sick of my current 1280 x 800 screen - the drop in my productivity from 2 big 1920 x 1080 screens down to the tiny laptop screen is painfully apparent. 1366 x 768 is arguably worse, since I really need those vertical lines of resolution. And although I move around a lot with a laptop, which is why I want a good battery and light weight, I also use it as my primary workstation, so I need to be able to dock it and use my big monitors. In fact, a laptop that would support 3 screens instead of 2 like my current Dell would be preferable. Few of the ultrabooks seem to have this capability, but at least a Thunderbolt port would make it theoretically possible. </div><div><br /></div><div>Other requirements are pretty normal. The number of USB ports matters, but not hugely, since most peripherals I use at home while connected to a dock. I do use my SD card reader pretty often, so it would be nice to keep that. A decent keyboard, preferably backlit, would be useful.</div><div><br /></div><div>Requirements in mind, I took a look at the current crop of ultrabooks just demoed at CES, and... was totally disappointed. Nothing had all the features I wanted. The closest ones were:</div><div><br /></div><div>Samsung Series 9</div><div>- no Thunderbolt but both HDMI and DisplayPort connections</div><div>- 1600 x 900 resolution (on what I hear is a great screen)</div><div>- light weight (2.5 lbs)</div><div><br /></div><div>Sony Vaio Z</div><div>- Thunderbolt, sorta, via a proprietary connector as usual (thanks a lot, Sony)</div><div>- 1600 x 900</div><div>- light weight</div><div>- ungodly price ($1900+)</div><div><br /></div><div>Here's <a href="https://docs.google.com/spreadsheet/ccc?key=0AvB08HzkcCDFdEc3alF1bWczU1FDZk1lQ2g5TnZqc3c">my spreadsheet</a> comparing the current crop of ultrabooks and their cousins using my completely biased and proprietary scoring system. It's a definite work in progress and the scoring may change without warning. If something comes along that scores above a 3, I'll probably buy it, but right now, none of these are worth the money.</div>Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com0tag:blogger.com,1999:blog-2021989930591302225.post-57814883852433801712012-01-04T23:00:00.001-08:002012-01-04T23:16:27.201-08:00More fun with SSIS type conversionsJust finished painfully debugging a strange SSIS issue about which I could find no documentation anywhere on the web, so I'm noting it here.<div><br /></div><div>I had changed a type within a large unwieldy data flow from DT_R4 (float) to DT_R8 (double), since the smaller data type was causing some weirdness around the least significant figures on some option prices. This seemed to work in testing, but I got a cryptic error about a conditional operation failing during a derived column transformation when I deployed it to Production. I opened the package, added an error redirection for the component and a data viewer, and sure enough, it was failing on a transformation that utilized some of the columns I had modified. </div><div><br /></div><div>I couldn't find any reason why the conditional would fail, since it was faithfully spitting out BOOLs, but the resulting transformation split the float into Integer and Decimal by casting the value into a WSTR, 20 and then finding the decimal point. After trying a dozen other things, I tried adjusting the size of the WSTR cast to a WSTR, 40. That worked.</div><div><br /></div><div>Note that the values that were failing would not even come close to 40 characters (ex: 110.68), but apparently the possibility of running into an overflow breaks something in the SSIS runtime.</div><div><br /></div><div>So, a word to the wise: if casting from float (DT_R4 or DT_R8) to Unicode string (WSTR), make sure you leave enough room to cast any possible value.</div>Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com0tag:blogger.com,1999:blog-2021989930591302225.post-60788871094111446742011-12-22T07:28:00.000-08:002011-12-22T07:39:42.776-08:00pyodbc isn't playing well with multiprocessingWrote my first Python script yesterday that incorporates the multiprocessing module in order to parallelize some CPU-intensive calculations that were going extremely slowly when run in the database. Mission accomplished there, and it was remarkably easy in Python, but then I hit a brick wall when trying to load everything back to the database. <div><br /></div><div>As it turns out, the calculation time is dominated by the database IO (~25 mil rows in and out), so I went about trying to optimize that as best I could. The reads are coming off an SSD, so they're fine, but the writes are UPDATEs, and I didn't want to issue them one at a time and incur all the connection and transaction overhead for each one. Instead, I created a staging table as a heap and batched up INSERTs of my data in a specified batch size (1000 to start) using SQL 2008 row constructors. Then the main table gets updated from the staging table right at the end, so only one giant transaction and UPDATE statement is necessary.</div><div><br /></div><div>This strategy appeared to work fine when single-threading, but then I figured I'd throw in multiprocessing there, too, since I already had the module imported and had crafted the functions around it. Didn't work. </div><div><br /></div><div>The error message was 42S02, aka Invalid object specified - in other words, the INSERT can't find the staging table, even though I'm creating it within the Python script, during a single-threaded section before I Pool.map() out different calculated shards to be inserted. SQL Server can obviously accept just about as many threads as you want to throw at it, so that's not the problem. The table exists, which I confirmed by adding a breakpoint (well, a set_trace()) and looking at it through Management Studio. It's all using integrated authentication, and I'm a sysadmin on the server, so there can't be any problems with schema, although I threw in some "dbo." references just to make sure. I even tried to <a href="http://msdn.microsoft.com/en-us/library/cc765421.aspx">trace the SQLNCLI driver operations</a>, but haven't managed to get usable output yet.<br /></div><div><br /></div><div>So the only thing I can come up with so far is an incompatibility between pyodbc and multiprocessing. If I get more time to investigate, I'll update the post, but for now I'm just going to have to switch the DB section back to single-threaded. Boo.</div>Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com0tag:blogger.com,1999:blog-2021989930591302225.post-86636005260358863222011-11-30T07:00:00.000-08:002011-11-30T07:02:56.723-08:00SQL could use some real string manipulationIn order to remove the first "-" from a string, this is the query I just had to run:<div><br /></div><div>update tbFoo set BBG_ticker = substring(BBG_ticker, 1, charindex('-', BBG_ticker) - 1) + ' ' + substring(BBG_ticker, charindex('-', BBG_ticker)+1, LEN(BBG_ticker) - charindex('-', BBG_ticker))</div><div><br /></div><div>Does that seem like overkill to anyone else? Let's compare it to Python...</div><div><br /></div><div>BBG_ticker.replace("-"," ",1)</div><div><br /></div><div><br /></div>Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com0tag:blogger.com,1999:blog-2021989930591302225.post-81440011781874761472011-10-22T09:20:00.000-07:002011-10-22T09:34:04.113-07:00Meeting new people in LaptopistanThis morning I'm back at Starbucks, as we haven't yet moved closer in to DC where there are alternatives to corporate coffee world, and I made the acquaintance of a woman who <div><br /></div><div><span class="Apple-tab-span" style="white-space:pre"> </span>1) made me explain the meaning of my <a href="http://www.threadless.com/product/320/Rock_HOWTO">t-shirt</a></div><div><span class="Apple-tab-span" style="white-space:pre"> </span>2) asked to use my laptop (instead of hers, for some reason?) to check her email</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>3) inquired about what I do for a living</div><div><br /></div><div>I'm pretty sure the next step will be to introduce me to her daughter/niece/granddaughter who's been looking for a nice Jewish boy (which I'm not, but I'm sure it wouldn't matter). </div><div><br /></div><div>Well, I suppose it's more interesting than doing R regressions while sitting at home.</div>Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com0tag:blogger.com,1999:blog-2021989930591302225.post-65302590876685049712011-10-14T06:43:00.001-07:002011-10-14T07:06:19.206-07:00Life as a contractorIt's my own fault that I'm a contractor and not a full employee - a year ago, my girlfriend decided to go back to school in another state, and I followed her there. My office was gracious (desperate?) enough to keep me on as a remote employee, but I was converted to contractor status, for both employment flexibility (i.e. they can let me go easily if it doesn't work out) and tax liability purposes. <div><br /></div><div>For the most part, this has worked out fine. Working from home can be a double-edged sword, but the non-existent commute is great and the ability to work from alternate locations seamlessly has been pretty nice - this summer we picked up and moved to Denver for 10 weeks with no interruption of my work, only a slight shift in working hours due to the time zone change.</div><div><br /></div><div>But there are some definite drawbacks. My taxes are far more complicated now, I don't get employer-sponsored health insurance, and there's a bit of a disconnect from the rest of my team and the firm at large. This is both practical - I have difficulty hearing what's going on in many of my meetings, since I'm the only one on the phone instead of there in person - and psychological. </div><div><br /></div><div>The latest case in point, and the catalyst for this post, occurred this morning. The chairman of my firm sent out an email announcing a minor contest sort of thing in which employees can win an electronic gizmo that shall remain nameless in exchange for updating their employee profile. I dutifully went to update mine, which I hadn't looked at since I was a Full-Time Employee... and found that I no longer have one. This is far from a big deal, but it's a reminder of the fact that although I work like an employee (I'm on a fixed rate, not an hourly one), consider myself part of the firm, and try to act in its best interest, there's a psychic distance between me and the Real Employees. </div><div><br /></div><div>There are more examples. The firm celebrated its 20th birthday a short time ago, and Employees all received small gift bags. It should probably go without saying that I did not. On one's 5th anniversary as an Employee, one receives a very small token of acknowledgement of service to the firm. My 5th year is coming up, but I presume it will be <i>sans</i> token.</div><div><br /></div><div>I assume this would be different at firms in which contracting is more widespread - contractors would be held either closer or farther away - but my situation is unusual at my office, so it's not worth HR's time to hold my hand through any weird episodes. Also, these are minor enough issues that I would feel ridiculous complaining about them in person, so I'm using this medium to work through them a little. </div><div><br /></div><div>Anyway, I know that my blog is occasionally perused by people from my office, so let me reiterate that this is <b>not a big deal </b>and not intended to be a passive-aggressive complaint, it's just some musings on this strange state of employment that I, and a growing number of others, find myself in.</div>Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com0tag:blogger.com,1999:blog-2021989930591302225.post-73370821952335792492011-10-05T06:16:00.000-07:002011-10-05T06:34:27.201-07:00Loading Fixed Width files with BCP / Bulk InsertI had to craft some format files in order to load a couple of fixed width files using the SQL Server BULK INSERT / bcp tools, and had some issues with the documentation when trying to get them to work.<div><br /></div><div>So let me state this explicitly: when attempting to load a file with no line breaks, you do not need a terminator for any field in your format file. Just specify all the field lengths, set the xsi:type to CharFixed, and the whole thing should stream in. Ex:</div><div><br /><pre style="color:#000000;background:#ffffff;"><span style="color:#a65700; "><</span><span style="color:#5f5035; ">record</span><span style="color:#a65700; ">></span><br /><span style="color:#a65700; "><</span><span style="color:#5f5035; ">field</span> <span style="color:#274796; ">id</span><span style="color:#808030; ">=</span><span style="color:#0000e6; ">"</span><span style="color:#0000e6; ">1</span><span style="color:#0000e6; ">"</span> <span style="color:#274796; ">type</span><span style="color:#808030; ">=</span><span style="color:#0000e6; ">"</span><span style="color:#0000e6; ">CharFixed</span><span style="color:#0000e6; ">"</span> <span style="color:#274796; ">length</span><span style="color:#808030; ">=</span><span style="color:#0000e6; ">"</span><span style="color:#0000e6; ">12</span><span style="color:#0000e6; ">"</span> <span style="color:#a65700; ">/></span><br /><span style="color:#a65700; "><</span><span style="color:#5f5035; ">field</span> <span style="color:#274796; ">id</span><span style="color:#808030; ">=</span><span style="color:#0000e6; ">"</span><span style="color:#0000e6; ">2</span><span style="color:#0000e6; ">"</span> <span style="color:#274796; ">type</span><span style="color:#808030; ">=</span><span style="color:#0000e6; ">"</span><span style="color:#0000e6; ">CharFixed</span><span style="color:#0000e6; ">"</span> <span style="color:#274796; ">length</span><span style="color:#808030; ">=</span><span style="color:#0000e6; ">"</span><span style="color:#0000e6; ">3</span><span style="color:#0000e6; ">"</span> <span style="color:#a65700; ">/></span><br /><span style="color:#a65700; "><</span><span style="color:#5f5035; ">field</span> <span style="color:#274796; ">id</span><span style="color:#808030; ">=</span><span style="color:#0000e6; ">"</span><span style="color:#0000e6; ">3</span><span style="color:#0000e6; ">"</span> <span style="color:#274796; ">type</span><span style="color:#808030; ">=</span><span style="color:#0000e6; ">"</span><span style="color:#0000e6; ">CharFixed</span><span style="color:#0000e6; ">"</span> <span style="color:#274796; ">length</span><span style="color:#808030; ">=</span><span style="color:#0000e6; ">"</span><span style="color:#0000e6; ">5</span><span style="color:#0000e6; ">"</span> <span style="color:#a65700; ">/></span><br /><span style="color:#a65700; "></</span><span style="color:#5f5035; ">record</span><span style="color:#a65700; ">></span><br /><span style="color:#a65700; "><</span><span style="color:#5f5035; ">row</span><span style="color:#a65700; ">></span><br /><span style="color:#a65700; "><</span><span style="color:#5f5035; ">column</span> <span style="color:#274796; ">source</span><span style="color:#808030; ">=</span><span style="color:#0000e6; ">"</span><span style="color:#0000e6; ">1</span><span style="color:#0000e6; ">"</span> <span style="color:#274796; ">name</span><span style="color:#808030; ">=</span><span style="color:#0000e6; ">"</span><span style="color:#0000e6; ">series</span><span style="color:#0000e6; ">"</span> <span style="color:#274796; ">type</span><span style="color:#808030; ">=</span><span style="color:#0000e6; ">"</span><span style="color:#0000e6; ">SQLCHAR</span><span style="color:#0000e6; ">"</span> <span style="color:#a65700; ">/></span><br /><span style="color:#a65700; "><</span><span style="color:#5f5035; ">column</span> <span style="color:#274796; ">source</span><span style="color:#808030; ">=</span><span style="color:#0000e6; ">"</span><span style="color:#0000e6; ">2</span><span style="color:#0000e6; ">"</span> <span style="color:#274796; ">name</span><span style="color:#808030; ">=</span><span style="color:#0000e6; ">"</span><span style="color:#0000e6; ">pool</span><span style="color:#0000e6; ">"</span> <span style="color:#274796; ">type</span><span style="color:#808030; ">=</span><span style="color:#0000e6; ">"</span><span style="color:#0000e6; ">SQLCHAR</span><span style="color:#0000e6; ">"</span> <span style="color:#a65700; ">/></span><br /><span style="color:#a65700; "><</span><span style="color:#5f5035; ">column</span> <span style="color:#274796; ">source</span><span style="color:#808030; ">=</span><span style="color:#0000e6; ">"</span><span style="color:#0000e6; ">3</span><span style="color:#0000e6; ">"</span> <span style="color:#274796; ">name</span><span style="color:#808030; ">=</span><span style="color:#0000e6; ">"</span><span style="color:#0000e6; ">deal</span><span style="color:#0000e6; ">"</span> <span style="color:#274796; ">type</span><span style="color:#808030; ">=</span><span style="color:#0000e6; ">"</span><span style="color:#0000e6; ">SQLCHAR</span><span style="color:#0000e6; ">"</span> <span style="color:#a65700; ">/></span><br /><span style="color:#a65700; "></</span><span style="color:#5f5035; ">row</span><span style="color:#a65700; ">></span><br /></pre><br /></div><div>You can still mix and match - have a terminator for the last field in each line, for example - but if your file has 0 line breaks, you don't need it.</div><div><br /></div><div>Also, the easiest way I found to handle skipping columns was to use OPENROWSET(BULK). Just make sure you're selecting the names of the columns from the ROW section of the format file, not the RECORD section.</div><div><br /></div><div>i.e.</div><div><br /></div><br /><pre style="color:#000000;background:#ffffff;"><span style="color:#800000; font-weight:bold; ">SELECT</span> series<span style="color:#808030; ">,</span> deal <span style="color:#800000; font-weight:bold; ">FROM</span><br /><span style="color:#800000; font-weight:bold; ">OPENROWSET</span><span style="color:#808030; ">(</span><span style="color:#800000; font-weight:bold; ">BULK</span> <span style="color:#0000e6; ">'source.txt'</span><span style="color:#808030; ">,</span><br />FORMATFILE<span style="color:#808030; ">=</span><span style="color:#0000e6; ">'sample.xml'</span><br /><span style="color:#808030; ">)</span> <span style="color:#800000; font-weight:bold; ">as</span> x<span style="color:#808030; ">;</span><br /></pre><div><br /></div><div>The documentation covers that part a bit better, but just wanted to reproduce it for my own sake, since I'm sure I'll forget how I did it between now and the next time I use BULK INSERT a few years from now.</div><div><br /></div><div>P.S. Gotta throw in a plug for <a href="http://tohtml.com/">tohtml.com</a> here for making my code actually paste into Blogger and look decent to boot.</div>Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com0tag:blogger.com,1999:blog-2021989930591302225.post-66522518016631624292011-10-05T06:08:00.000-07:002011-10-05T06:16:07.736-07:00Laptopistan upgrade3G vs 4G tethering: night and day. I can now click on things through my remote desktop session and have them actually respond, instead of doing the mental 2-count (or switching to Google Reader while waiting, which is always a productivity killer). <div><br /></div><div>I have some concerns about the battery life of my new HTC Thunderbolt, and I'm not blown away by the form factor, but so far I definitely prefer it to my iPhone 3GS. And the 4G factor alone makes it totally worthwhile. </div>Anonymoushttp://www.blogger.com/profile/09313712310740744659noreply@blogger.com0