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.

Guess?

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
WHERE     CONVERT(VARCHAR(20),SomePKIDEN_Id)
    + NamesStringColumn
    + CONVERT(VARCHAR(20),SomeFKIDEN_Id)
    + CONVERT(VARCHAR(20),SomeOtherFKIDEN_ID)
   IN (
    SELECT CONVERT(VARCHAR(20),SomePKIDEN_Id)
    + NamesStringColumn
    + CONVERT(VARCHAR(20),SomeFKIDEN_Id)
    + CONVERT(VARCHAR(20),SomeOtherFKIDEN_ID)
    FROM ##AGlobalTempTable
    )

AS OPPOSED TO THIS:

DELETE t
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.

AllowBadAddressOnValidation




LOL

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.

Will we ever learn?

A while back I posted about a Microsoft bug in 2000 server installations. Caused a production error again, then this was followed by another (SELECT 1 FROM SYS.SYSINDEXES) the next day.

Turns out it is related to the OSQL/ISQLW/ISQL installed and not the server. You can run this same script from a machine with the 2005 tools installed and point to the 2000 server and there is no error. As we deploy using processes that run the file via an isql.exe -I command line, it is a big deal.

Point is then, when we are testing for compatibility of our scripts, we can't run it from the 2005 management studio. We will get a false positive. We have to run it through the local version of the tools!

ERROR 1
PRINT 'The error is in parsing the GO incorrectly'
/*
This is a comment
GO
*/

ERROR 2
--The error here is the prefix of the SYS. that will not parse correctly
SELECT * FROM SYS.SYSINDEXES

It could be a rumor ...

My favorite programmer asks: Which genius put this into the common library?

return bool.Parse("true");

instead of

return true;



I don't care where you come from, that's funny!

That's not real food you're eating

Got a bug that we don't make a log entry for a module in one case. Turns out they are reading the text of the log to determine how to make calculations for one of their reports.

Who bases $ calculations on log entries? It's not real data. I think Joe Celko would have a good rant over that.

The reasoning was that the comparison between created date and updated date was unreliable. Instead of a trigger band-aid which would have left their code the same after the reliability issue was resolved, this solution was devised.

Two days and many man-hours of research later we found that the reason they had no log was that someone updated the database through an Ad-Hoc SQL query. The log is generated through the GUI changes.

Exactly how did you test that?

The comment found in the code of an old stored procedure states the following:

--Using dynamic SQL (faster than static SQL for large tables)


To add to the confusion, the procedure neither had dynamic SQL nor returned more than one record.

Wonky and still logically incorrect

This is another result from the school of cut-and-paste polymorphism. The original IF statement checked for two items in an OR condition, explaining the apparent redundancy in the second IF - but not why anyone would check @@error twice and expect the same value...

IF (@@error <> 0)

BEGIN

IF @@error <> 0

BEGIN

Set @intResultCode = @@error

Set @chvResultText = 'Error when registering the Station/Date. ' +

(SELECT description from master..sysmessages where error = @intResultCode and msglangid = 1033)

END
END

Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement being verified, or save it to a local variable that can be checked later. Source: MSDN

Why we need code reviews

The ever diligent programmer P noted this bit of code today:

• throw(new ArgumentException(errorMessage, "It is a very twisted intersection!"));

I would hate to be the person reading through the error logs a year from now ...

Simplify Simplify Simplify

Here is the code as found ::

private string FormatTime(object DateTimeValue)
{
string result = " ";
if (DateTimeValue != null)
{
DateTime dt = DateTime.Parse(DateTimeValue.ToString());
if (dt.Hour < 10)
result = FILLER_NUMBER + dt.Hour.ToString() + ":";
else
result = dt.Hour.ToString() + ":";

if (dt.Minute < 10)
result += (FILLER_NUMBER + dt.Minute.ToString() + ":");
else
result += (dt.Minute.ToString() + ":");

if (dt.Second < 10)
result += (FILLER_NUMBER + dt.Second.ToString());
else
result += dt.Second.ToString();
}

return result;
}

Here is the refactored version::

return DateTime.Parse(DateTimeValue.ToString()).ToString("HH:mm:ss");

The wheels of the object go round and round

Notice that if dsSTData were null you will allocate it, but not use it to look up the ThingyMin :)

public int ThingyMin
{
get
{
int _ThingyMin = 0;
if (dsSTData == null)
{
this.dsSTData = ONAME.ProdName.Common.Business.CONAMESystemDataList.STs;
}
else
{
foreach (System.Data.DataRow row in sSTData.Tables[this.DEFAULT_TABLE].Rows)
{
if (Convert.ToInt32(row[0]) == this._STID)
{
_ThingyMin = int.Parse(row[OllCommon.COL_THINGY_MIN].ToString());
break;
}
}
}
return _ThingyMin;
}
}

How to ruin a set of data

From my bud. Not strictly SQL but close enough. He would like to know:

I'm not asking a lot. I just would rather see

"if (this.Order.IsPacked)"

instead of

"if (
this._DataSet.Tables[OrderCommon.TBL_ORDER].Rows[OrderCommon.DEFAULT_ROW][OrderCommon.COL_IS_PACKED] == DBConst.DBNull)"

Built In Bugs :: VarChar

Alexander Kuznetsov pointed this one out on his blog. He goes on to a longer explanation and how to test for the issue in general (see link). SS

In Transact SQL you can assign a 10-character value to a VARCHAR(5) variable, as follows:

DECLARE @c VARCHAR(5); SET @c='1234567890'; PRINT @c

12345

The value will be silently truncated without raising an error. Because of this behavior it may be very easy to make mistakes.

Please use comments ...

Many management types continually rant about leaving comments in the code so that the next person will be able to figure out what is going on. Sometimes it just proves something else ...

'* Comments: IMPORTANT: The global constants are being temporarily prefaced with "" until
'* all the reports can be integrated with calls to the delegate methods. Afterwards,
'* a global search/replace of "" with "" should rename the constants to coding
'* standards compliancy.

This is going to hurt a little

Imaging a 5100 line stored procedure that is doing a conversion of some data. After hundreds of site updates, you get called in to find a wee little bug that seems to have no consistent behavior but it is definitely in the conversion. Then some where in that 5100 lines of code you find these snippets - several hundreds of lines apart but interacting just as viciously:

CREATE TABLE #Duplicates
(
RecID INT IDENTITY (1,1)
,i_id INT
, and other things
)

..eventually some insert code fills this temp table. Later we find this new table:

CREATE TABLE #Itemslist
(
RecID INT IDENTITY(1,1)
,INo INT
, and other things
)

...which also gets properly loaded. Eventually final results are loaded into yet another temporary table. I will spare you the details and just point out the join here;

SELECT some stuff
FROM #Itemslist il
JOIN #Duplicates d ON il.RecID = d.RecID
JOIN etcetera

Fortunately the duplicate table was very small which is the only thing that contained the error that propagated from this.

Where else does one equal zero?

From more of the old code from Hyberia: We figure that this is the lazy man's (person's) method to making insert tables without all that messy typing.


SELECT rlk.*, rlk.i_id as i_id2 --CREATE #RSLAG table
INTO #RSLAG --note.. the i_id2 column was added to
FROM RSLAG rlk --cause the original i_id column to be
WHERE 1 = 0 --created without the IDENTITY property

SELECT rlk.* --CREATE #RSLAG_total table
INTO #RSLAG_total
FROM #RSLAG rlk
WHERE 1 = 0