Wait a sec, did I read that right?

I thought this would have been fine ...

I had to read this several times, have some coffee, stop drinking for a week and then mouth out the individual syllables out loud. Yes, it really does say this.

Honestly, there's no shopping here. Start a project to use MSMQ and then try to directly set the correlationId of a message. Easily repeatable.

No man is an island, no company is perfect, software is hard.


Soft Issues - everyone's got 'em

Things humans are good at:

So I'm playing with Microsoft's Visual Studio 2010 and the entity framework. One of the nifty new features I noticed when making a new "ADO .NET Entity Data Model" was the option to "Pluralize or singularize generated object names". The created items then just make more sense and it is a welcome addition.

Never mind the fact that my database table "Octopi" did not translate to "Octopus" as we would all expect. That is an edge case for sure and I don't really use Octopi that much. My issue is how it took my table named "Movies" and ended up with the singular model name "Movy". You could argue that the spelling is kinda cute, but you couldn't argue it is correct.

I'm just saying ...


You couldn't handle the text file

OK - so the deal is to figure out why a simple 2300 line SQL query that took 1:08 to run in the SQLServer Management tool during testing, is running for over an hour on another site and doesn't seem to be any where near completion. They can see it running,  but nothing is changing in the database. Testing was on a copy of the same database. Two other running remote databases that are smaller are also not complete and running forever.


Turns out PRINT statements can be evil if you use enough of them. The update SQL was outputing some good-old-fashioned debugging information using PRINT "something" generously. These were withing a cursor, that was then in a loop that ran another loop - each of these with their own generous set of debugging data for the world to review upon completion.

While reviewing the output, I noticed that there were quite a few of them. In management studio, you can see the number of lines. In this case, it was over five million. Lines. Figure each line had 20-30 characters on it. This wasn't an issue in the GUI, but the SMO used had to marshall all that data somewhere. Takes some time. If you run it remote across the country, well, that's a long drive.

We killed the job, remoted locally and ran it in th GUI on the target server. Done in 1:10. Go figure.

Further testing showed that
a) even the GUI couldn't actually save the output results it showed. It would get an exception error.
b) SMO would probably eventually finish some day on its own - but no one in testing wanted to wait to see and killed it after two hours
c) Taking the print statements out allowed the SMO to finish in 1:20

Couldn't find any Microsoft reference on a MAX text results size ...


It gets easier with the title ...

The SENIOR DB developer removing duplicates swore that this was going to be much faster:
DELETE #someLocalTempTable
    + NamesStringColumn
   IN (
    + NamesStringColumn
    FROM ##AGlobalTempTable

FROM #someLocalTempTable     t (NOLOCK)
JOIN ##AGlobalTempTable     s (NOLOCK)
ON t.SomePKIDEN_Id         = s.SomePKIDEN_Id
    AND t.NamedStringColumn = s.NamedStringColumn
    AND t.SomeFKIDEN_Id     = s.SomeFKIDEN_Id
    AND t.SomeOtherFKIDEN_ID= s.SomeOtherFKIDEN_ID
Fast correlated sub-queries. Nice concept.


I swear I don't make this up ...

This is the latest software configuration option. It has a yes/no setting.




Bugs that make money

 Here is a find from "P", a potential bug had the database fields been used as intended. Its a big change in reporting that QA has never seen so you know it is in some very obscure code path. Something to be said for code review!

I'm looking at a stored procedure that's reporting $$ for a Revenue report by multiplying Rate * Total, instead of Rate * Quantity (which is the same as just Total).

So what we are looking at is the potential of ordering 50 $1 gift cards, but reporting $2500 for the entry. Just the kind of thing to get you over that bumpy quarter. ;)


No we won't learn

Not more than a couple days of dealing with an obscure OSQL parsing bug (see link) that we should have known about, we QA-catch another that we already knew about.

If you fully qualify a table in another database and give it an alias, OSQL command parsing will not use the alias. That is you cannot
SELECT something FROM foobarDB.dbo.footable AS otable WHERE otable.someColumn IS NULL
The "otable.xxx" will cause an error as it cannot find that alias.
2005 et.al. seem to be fine.

License & copyright

Creative Commons License
StinkySQL weblog and any related pages, including the weblog's archives are licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License.