SQL could use some real string manipulation

In order to remove the first "-" from a string, this is the query I just had to run:

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))

Does that seem like overkill to anyone else? Let's compare it to Python...

BBG_ticker.replace("-"," ",1)

Meeting new people in Laptopistan

This 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

1) made me explain the meaning of my t-shirt

2) asked to use my laptop (instead of hers, for some reason?) to check her email

3) inquired about what I do for a living

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).

Well, I suppose it's more interesting than doing R regressions while sitting at home.

Life as a contractor

It'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.

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.

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.

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.

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 sans token.

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.

Anyway, I know that my blog is occasionally perused by people from my office, so let me reiterate that this is not a big deal 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.

Loading Fixed Width files with BCP / Bulk Insert

I 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.

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:

<record>
<field id="1" type="CharFixed" length="12" />
<field id="2" type="CharFixed" length="3" />
<field id="3" type="CharFixed" length="5" />
</record>
<row>
<column source="1" name="series" type="SQLCHAR" />
<column source="2" name="pool" type="SQLCHAR" />
<column source="3" name="deal" type="SQLCHAR" />
</row>

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.

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.

i.e.

SELECT series, deal FROM
OPENROWSET(BULK 'source.txt',
FORMATFILE='sample.xml'
) as x;

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.

P.S. Gotta throw in a plug for tohtml.com here for making my code actually paste into Blogger and look decent to boot.

Laptopistan upgrade

3G 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).

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.

SSIS, MSDTC, and SQL Server clusters

Note to self: when receiving the following error from an SSIS package, the first thing to check is the Authentication setting for MSDTC.

Description: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00E "The transaction has already been implicitly or explicitly committed or aborted".

The security setting should be set to No Authentication Required. Mutual does not appear to work with clusters. This is especially significant if, like me, you have a non-clustered instance in QA and a clustered one in Prod and you have been beating your head against the wall trying to figure out why MSDTC, with all the same settings, works in the former but not the latter.

The Query Did Not Run

Why thank you, Excel, that's a very helpful error message. Except I was watching the query execute on SQL Server, and it DOES run. Perhaps there could be a different problem?

The actual, exact text of the error is

The query did not run, or the database table could not be opened.

The sheet in question was running a stored procedure, which had been working fine, albeit very slowly. A coworker tuned the proc and happened to use a temp table to accumulate the results for multiple queries instead of a UNION, producing better execution plans for each. However, despite being 4x faster, the proc stopped running from Excel, throwing the error message above.

At first I thought there might be a problem with the final DROP #table statement in the proc, so I removed that. No help. I adjusted various properties of the database connection from Excel. No change. I fiddled with the table setup in Excel. Nothing.

Then I remembered that I had some difficulties with pyodbc a while back that were resolved by setting NOCOUNT ON in the query. I can't recall the exact cause, but it's something about the driver seeing the first count returned as the query result, when of course it's not in a proc that populates a table with a few queries and then selects from it as the final step. Anyway, I set NOCOUNT ON at the beginning of the proc and OFF at the end and voila, it worked. So try that if you're having difficulties with Excel not executing your stored proc correctly.