Programmed Nirvana

BCP, tab-delimited hell and a good text editor

2011-12-05T19:05:00.001-08:00

If you've ever had a need of quickly loading lots of data into Microsoft Sql Server, then you'll probably want to use BCP.exe.

BCP is a command line tool for exporting and importing data from tab-delimited files, and believe me when I say it's fast, it's FAST. When you are trying to insert hundreds of thousands of records into a table in Sql Server, normal inserts can take hours. With BCP, we're talking mere seconds.

There is a drawback, however. The tool is not without it's flaws. The BCP export command will spit out a query to a tab-delimited file, which you can then use to import that data at a later time. But there are times that BCP doesn't quite do its job right. You'll find what I refer to as "tab-drift", where BCP will insert incorrect numbers of tabs, particularly when your data has nulls or empties in some of the columns. What ends up happening is when you import your data, certain rows end up with the columns shifted along, so what was supposed to be in column A ends up in column B, and so on.

Sometimes BCP can correct this if you re-run the BCP command on the original table to re-export the data. Other times, no such luck. In this case, you'll probably need to correct a couple rows by hand. First you need to find the offending rows (please don't let there be too many!), then you have to fix them in the bcp export file.

And how hard is this? Have you ever opened a tab-delimited file before? If not, there's not much to look at. A lot of text, a lot of space. Nothing else.

Here we go in notepad...

In Visual Studio...

You need to be able to see the tabs. You can do this quite easily by using a good text editor, like Scite, or if you have Visual Studio, use the following command ctrl+E, S

What you'll get now is much more usable...

Each of those little blue arrows are tabs. Tabs indicate the separator between columns. If you need to drop in null / blank columns, just drop in additional tabs as needed and you should be all fixed up.

To turn off viewing tabs, just hit the same ctrl+E, S key again.

Hope this helps. It definitely saved me.

LoadError: no such file to load -- watir

2010-12-15T19:22:00.000-08:00

For you web developers who need to write good, coded regression tests, Watir is an excellent choice.

As quoted from the Watir website:

Watir, pronounced water, is an open-source (BSD) family of Ruby libraries for automating web browsers. It allows you to write tests that are easy to read and maintain. It is simple and flexible.

Watir drives browsers the same way people do. It clicks links, fills in forms, presses buttons. Watir also checks results, such as whether expected text appears on the page.

An awesome tool, no doubt about it. It saves us heaps of time on regression testing (our tester is a little inexperienced), load testing, and trying out new stuff and making sure all is running well.

However, you might encounter a problem when trying to run up their Quick Start Tutorial or their examples. When you attempt to run anything from the irb or from a ruby script with the following statement:

require "watir"

You get the following error back:

LoadError: no such file to load -- watir

C:\>irb

irb(main):001:0> require 'watir'
LoadError: no such file to load -- watir
from (irb):1:in `require'
from (irb):1
irb(main):002:0> exit

You can now make sure watir is installed and the version of ruby with the following commands:


C:\>gem list --local watir
*** LOCAL GEMS ***
watir (1.6.7)

C:\>ruby -v
ruby 1.8.7 (2010-08-16 patchlevel 302) [i386-mingw32]

The fix is simple.  You need to load rubygems first.

C:\>irb
irb(main):001:0> require 'watir'
LoadError: no such file to load -- watir
from (irb):1:in `require'
from (irb):1
irb(main):002:0> require 'rubygems'
=3D> true
irb(main):003:0> require 'watir'
=3D> true irb(main):004:0>

If this helps you, you owe me a beer.

Transact Sql Unit Testing

2010-09-21T15:39:00.000-07:00

Unit testing is awesome. It has become my new software development passion. Where once I tried to live without it, now I try to dream up tests I haven't thought of, just so I can write more tests (geeky, I know). All of our C# libraries are unit tested, down to a very, very fine detail, and it is incredible how much cleaner our application is before each release.

Which led me to the idea: why can't I unit test my sql scripts as well? Seems a logical extension, right? I write a lot of sql procs / scripts for updating data. Why shouldn't I unit test these changes as well?

So I looked around, and while I found a few frameworks, and a lot of advice, I didn't find anything that would work particularly well in our space. The thing was, the frameworks were all written in different languages, like Java or Python. The advice was mostly around commonsense things to do, like after running an update, run another query to ensure the data was there.

What I wanted was a simple, lightweight set of unit testing tools to help me as I coded my SQL. These would be called to verify my changes either immediately after I updated the data, or in a separate set of scripts. Just like unit tests, right?

So what I came up with was a simple set of Assert stored procedures.

Assert_IsTrue

Assert_IsFalse

Assert_AreEqual

Assert_AreNotEqual

Assert_Fail

Assert_IsNull

Assert_IsNotNull

Each of these, like their java / C# / ruby counterparts, allow me to check a given bit of change, and report back if it failed.

As an example, let me show you a simple call to Assert_IsTrue:



declare

@Result int

,@ErrorMessage nvarchar(1000)



select @Result = COUNT(1) from etBillingTaxRate where TaxRateAmount = 153.0



set @ErrorMessage = 'Could not find a billing tax rate of 153.00'



exec dbo.Assert_IsTrue @Result, @ErrorMessage

As you can see, the Assert_IsTrue takes 2 parameters: an integer and an nvarchar. The nvarchar is obviously the message to return if the assert fails. For Assert_IsTrue and Assert_IsFalse, I used an integer (as Transact SQL has no boolean data type). If this integer is 1 or greater, the tests passes. If the integer is 0 or less, we get the following when we run the script:

Msg 50000, Level 15, State 1, Procedure Assert_IsTrue, Line 19
Could not find a find a billing tax rate of 153.00

You can download my assert stored procs from here TSQL_UnitTestingProcs.sql, and use them in your own Transact SQL projects. For developers of other databases, it would be very easy to port these to your language. They are ridiculously simple, but make your sql coding life so, so much easier.

Try Catch and TransactSQL, oh my

2010-07-06T21:08:00.000-07:00

One of the great features to TransactSQL since Sql Server 2005 is the Try Catch block.

For the SQL developer who isn't also a programmer (are there any of you left out there?), the try catch block is a very useful concept from programming which can serve you very well in your sql scripts.

Take the following scenario

begin a transaction

do something and check if it went down okay

do something else and check if it went down okay

the last one didn't?! Something went wrong

Rollback!

Commit

This works, but it lacks:

1. Ease of reading. If I'm a sql developer I can make sense of this, but it isn't necessarily clear to anyone else. Why is that commit hanging around at the end?

2. A mechanism for grouping all your steps together when things go wrong. What if you want to do something else (aka log the attempt) after you rollback? Yes, you can use an IF statement, but again, your code becomes more unreadable.

Enter the Try Catch. Let's take the same statement

begin a transaction

begin a try

do something

Commit

end try

begin catch

Rollback

end catch

Much easier to read. Much cleaner to use.

Let's look at a real world example

begin transaction

begin try

delete from SomeTable where Id = 24

delete from SomeOtherTable where Id = 22

--this will fail as the table above has a foreign key constraint on this table's records

--and thus must be deleted first

delete from FinalTable where Id=26

commit transaction

end try

begin catch

rollback transaction

end catch

This was good, but we can do better. My queries are protected, but I'm still no wiser as to what the problem was in the first place. Nothing could be simpler. Inside the catch, we have access to the error which threw the exception in the first place. You can do what you like with it, even re-throw it. TransactSQL provides 3 functions to get at our error message and details:

ERROR_MESSAGE() - This returns the error message

ERROR_SEVERITY() - This returns the severity, if you want it

ERROR_STATE() - This returns the error state, though I've never needed it to date.

declare

@ErrorMessage NVARCHAR(4000)

,@ErrorSeverity INT

,@ErrorState INT

begin transaction

begin try

delete from SomeTable where Id = 24

delete from SomeOtherTable where Id = 22

--this will fail as the table above has a foreign key constraint on this table's records

--and thus must be deleted first

delete from FinalTable where Id=26

commit transaction

end try

begin catch

rollback transaction

SELECT

@ErrorMessage = ERROR_MESSAGE(),

@ErrorSeverity = ERROR_SEVERITY(),

@ErrorState = ERROR_STATE();

--At this point you can do what you like with the error. I'm just rethrowing it after my rollback

RAISERROR (@ErrorMessage, -- Message text.

@ErrorSeverity, -- Severity.

@ErrorState -- State.

);

end catch

So what I now get back is the following:

(1 row(s) affected)

Msg 50000, Level 16, State 0, Line 28

The DELETE statement conflicted with the REFERENCE constraint "FK_SomeOtherTable_FinalTable". The conflict occurred in database "ChrisDB", table "dbo.SomeOtherTable", column 'FinalTableId'.

And a query of my tables shows no rows were deleted due to the rollback.

Hope this helps someone out there.

Windows 2008 and COM dlls

2010-06-14T18:55:00.000-07:00

This was hell.

I have a pretty standard DLL, TidyCOM.dll, which I use in my applications to well, you know what HTML Tidy does. If not, go look it up.

So, I figure it's as simple as usual: copy the TidyCOM.dll to the system32 directory and run regsvr32.exe against it, as in

Nope. Fail. There is an error message but I won't bother you with it. No luck in the universe would make this thing install.

This led to a lot of time spent searching the internet for an answer. What I found was this:

If you are running on a 64-bit machine, the programs in system32 AREN'T 32bit, but instead 64 bit (WTF?!). If you want to register this dll, copy your DLL to the Sys64WOW folder, open a command prompt as an administrator (right click on command prompt and choose "Run as Administrator"), navigate to the windows\Sys64WOW folder and try the above command (regsvr32.exe DLLName.dll) and VOILA! All registered.

And yes, you owe me a coke.

ADO.Net Entity Framework Connection String: "Keyword not supported: 'Data Source'"

2010-05-26T19:12:00.000-07:00

So you're new to ADO.Net Entity Framework, and you build a lovely little app, and you use your old connection string from a pre-linq application or website. You put your connection string in the app.config / web.config, which most likely looks something like this:

data source=ServerName;Initial Catalog=SomeDatabase;User Id=sa;Password=pwd

You reference this connection string in your code like this:

using (var ents = new LinqADOEntities(Settings.Default.ConnectionString)) {

And yet when you fire it up, .NET freaks out!

Well that's no good. There are countless posts / forums / blogs on the web around this, most of which are confusing, wrong or otherwise leading you down the primrose path.

Here's the real skinny:

If you're using ADO.Net Entity Framework, it generates its OWN connection string directly into your app.config / web.config, and it looks NOTHING like an old connection string.

<connectionStrings>
<add name="STS_Content_TFSEntities" connectionString="metadata=res://*/TFS2005_Documents.csdl|res://*/TFS2005_Documents.ssdl|res://*/TFS2005_Documents.msl;provider=System.Data.SqlClient;provider connection string="Data Source=someServer;Initial Catalog=SomeDB;User Id=sa;Password=pwd;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient" />
</connectionStrings>

WTF?! Yeah. So if you want to store this in another place, then copy the whole wacky thing over to the other place, wierd metadata= stuff and all. Otherwise, you'll need to change the values in the app.config / web.config to match your database configuration and call your ADO Entity with an empty constructor. It will find the default connection string as defined in the App.config / web.config. As in...

using (var ents = new LinqADOEntities()) {

If I've saved anyone the 1.25 hours it cost me, I'll consider it worth my while.

Sql Chat: Avoid the 'OR'

2010-05-17T21:06:00.000-07:00

Sql. Structured Query Language. The joy and bane of so many developers' lives. It can make or break you in the development game, and have you up at three in the morning saying "but WHY am I getting duplicates?!"

I have friends who try very hard to avoid using it. They are the products of modern programming. Data Entities that automatically update your data when you call a save method. Code layers that remove the complexity of data from the developer. Blah. Blah. Blah.

This is really nice-sounding and lots of guys I know swear they don't need to know Sql. Then their boss wants a report, tabulated and summed and pivoted by all kinds of crazy things. Entity objects suddenly become Satan's little minions. If the only tool you have is a hammer, soldering that tiny wire back into place is gonna be one nasty job.

If you're using a database in your application, and you don't know Sql, here's some free advice: Man up, get a book (or look online) and learn it. You'll never regret it. It always is a bonus knowing at least a little Sql. It impresses database admins, and who doesn't want to impress them, right? Plus you can lord it over your other geek friends who DON'T know it.

Over the coming weeks and months, I'll be writing a regular blog on Sql. This will NOT be an introduction to Sql. If you want an intro to Sql, here you go:
http://www.w3schools.com/sql/sql_intro.asp

What I'll be focusing on is some of those tricks and traps in Sql which will set you apart from the average Sql nerd.

************************************************************************************************************

With that said, let's get stuck in. Today, we'll be talking about the  OR statement, and why you should avoid this like the damned plague.

At first glance, an OR statement would seem like a reasonably good idea.

select * from ObjectTable where ObjectId = 1234 or ObjectId = 2345

Get one OR the other, right? Sure. Good idea in simple scenarios such as this one, and fine if you are dealing with primary keys. Nice and clean. However, let's go a little further with this example:

select * from ObjectTable where ObjectTypeCode = 'Code1' or  ObjectTypeCode = 'Code2'

Now we aren't talking about a primary key any more. We're talking about a string hanging off the side which is a property. Now the database is scanning to find which records have ObjectTypeCode of 'Code1' OR have 'Code2'. This is a lot of scanning. Your db server is saying:

"This one? No. This one? Yes. This one? No. This one? Yes"

This is time consuming, and at all costs we wish to consume less time on the db. Less time means less CPU being used, and less CPU means...well less CPU. Yes, indexing can (and will help), but indexing for indexing sake can actually end up biting you in the ass. Too many indexes is worse than too few, as the system wastes ridiculous amounts of time updating all these indexes that are only there to help you out in those rare scenarios where you want to pull off an OR statement.

And for all you smart-asses out there, don't think this trick will help you either:

select * from ObjectTable where ObjectTypeCode in ('Code1','Code2')

This is basically the same thing, just in prettier shorthand. Trust me. I tried it.

So how do we avoid this nasty OR statement (if we can help it)? Obviously, there are times we NEED an OR statement.   What are our alternatives.

Remember that SQL is a set-based language. When you make a query in Sql, we are trying to group together things that meet the criteria in your joins and where clause. The tighter this joining is, the less time your database server will use trying to find it. So here are some good rules:

1. If you query for these values OFTEN, maybe you need to rethink your data structure. Rather than this:

You might consider this:

Note the column 'GroupingCode'. You can group your ObjectTypeCode values using the GroupingCode as in:

ObjectTypeCode	GroupingCode
Code1	Group1
Code2	Group1
Code3	Group2
Code4	Group2
Code5	Group1

Now you have a usable set which can grab records quickly.

select * from ObjectTable ecot
join ecObjectType ecott on ecot.ObjectTypeId = ecott.ObjectTypeId
where ecott.GroupingCode = 'Group1'

This is going to be far faster, because we are joining on a single key, not multiples that require 2x the table searching.

2. If you can, try the reverse. For example, if you have 3 values in ObjectType, our two above and another one called 'Bleck', try this instead:

select * from ObjectTable where ObjectTypeCode <> 'Bleck'

3. Like this?
select * from ObjectTable where ObjectTypeCode like 'Counter%'

This doesn't work in all flavors of Sql, but in transact-sql it can be lightning fast, particularly if you full-text index the column.

That's it! Hope it helps. Remember, dodge that OR. There is usually a solution around it.

Create a Stored Procedure with SQL SMO

2010-02-11T17:23:00.000-08:00

I am currently rewriting an existing data generator which was written in Sql dmo, but now must be in Sql Smo.

Creating a stored procedure is a pretty straightforward affair. Add your header references:

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Smo.Agent;

using Microsoft.SqlServer.Management.Common;

And here's the most commonly used code I've found online:

StoredProcedure mySP;
String procString = "PUT YOUR PROCTEXTHERE";
mySP = new StoredProcedure(sqlserver.database,"SomeProcName");
mySP.TextBody = procString;
mySP.Create();

Something important to note here is that very often you will get back a nasty exception if you're not careful:

Create failed for StoredProcedure 'dbo.SomeProcName'.

WTF?! Read the exception more carefully and you'll find the following explanatory note:

To accomplish this action, set property ExecutionContext.

Hmmmm. What the hell is this, then? The problem is the context of who is attempting to create this procedure on your database. Okay, so we attempt to set the execution context to, say the owner, but the moment you try to set the executioncontext on your stored procedure, you will get another exception!

Writing property ExecutionContext of StoredProcedure 'dbo.SomeProcName' is not allowed when TextMode has been set.

Why is anyone's guess, but I haven't found a satisfactory answer anywhere on MSDN or the web. I decided to take a more tai chi approach to this problem and dodged the stored proc object entirely:

   ServerConnection conn = new ServerConnection("ServerName", "UserName", "Password");
   Server myServer = new Server(conn);
   String procString = "create procedure [dbo].[SomeProcName]() as begin select top 1 from someTable; end";
   Database myDb = myServer.Databases["DatabaseName"];

   myDb.ExecuteNonQuery(procString);

Here's hoping this saves you some headache as it did me.