Category Archives: SQL

Using FusionCharts in SSRS reports

Microsoft’s SSRS is pretty advanced reporting system with multitude of advanced features. SSRS also has charting capabilities, but it’s somewhat lacking compared to more advanced desktop or web charting suites

On the other hand FusionCharts offers very cool charting package with gazillion of chart types and very cool features. But it uses JavaScript engine and renders charts client-side only!

What if there was a way to marry the two technologies together – to render cool FusionCharts in advanced SSRS repots? Continue reading →

SSRS and HTML rendering of ordered list

Microsoft’s SQL Server Reporting Services supports rendering of HTML tags, but for some reason that support stuck in 1990s – only very limited set is supported. And even using that set is problematic.

Case in point – ordered list. While officially supported – the way it is rendered is the stuff nightmares are made off. Jumble of original tags generously intermixed with DIVs and SPANs – it’s a wonder it renders at all.

And sometimes it doesn’t. If you try to view a report in Internet Explorer (especially from older, but still actively used versions of SSRS like 2008) numbering get screwed.
Continue reading →

TSQL ISNUMERIC and “String or binary data would be truncated” error

If you’re using TSQL ISNUMERIC function in a query, e.g.

select * from MYTABLE WHERE ISNUMERIC(MYSTRING) = 1

You may receive unexpected error:

String or binary data would be truncated.

ISNUMERIC must truncate string data, and if you experienced the above error, some of your data is over the limit. But you can augment the above query:

select * from MYTABLE WHERE ISNUMERIC(LEFT(MYSTRING, 8000)) = 1

But cutting only 8000 chars you will avoid the error, and I seriously doubt you will have number over 8000 digits long so it’s a safe bet as well

Cancel long running SQL Command in ASP.NET WebForm application

It’s an all too common scenario when your ASP.NET page takes too long to load and the culprit is slow, long running SQL query. It shouldn’t come to this, you should optimize your DB stuff to minimize delays, but if you’re trying to decode feline genome or find alien live in the neighboring galaxies – that’s unavoidable. So the page is running and at some point you decide enough is enough and decide you need to cancel it. But you want to do it gracefully, for example slow page is in an IFRAME and you want to remain in the parent page and you don’t want to close/reload the whole thing.

There’s a way. The idea is, every time you create an SqlCommand – you add it to static (shared in VB.NET) list. If command runs successfully – you remove it from the list. But if it takes too long – you can issue an AJAX call from client page to cancel the command stored in that list.

Thanks Arsalan Tamiz for posting this solution to my question on StackOverflow. His demo project was in C# (you can download it from the above link). but since most of my projects are in VB.NET – I did a conversion with some adjustments.
Continue reading →

TSQL: NOT IN doesn’t work

If you encounter a weird scenario when query

SELECT Something FROM Table1 WHERE SomethingElse IN (SELECT Lookup FROM Table2)

work perfectly, but the opposite query

SELECT Something FROM Table1 WHERE SomethingElse NOT IN (SELECT Lookup FROM Table2)

doesn’t return any results – and you know for a fact that there’re results – check values returned by the subquery. Chances are there’re NULLs among those values. If that’s the case – NOT IN won’t return any results.

As a quick workaround you can add IS NOT NULL condition to the subquery:

SELECT Something FROM Table1
WHERE SomethingElse NOT IN (SELECT Lookup FROM Table2 WHERE Lookup IS NOT NULL)

and this should do the trick.

Credit: This Stack Overflow answer.

Solution for SqlDataReader.ReadColumnHeader NullReferenceException

This post related to the previous one, but I decided to write a separate article because it seems to be a common problem.

Sometimes when you use SqlDataReader, you would get an exception:

NullReferenceException {“Object reference not set to an instance of an object.”}
at System.Data.SqlClient.SqlDataReader.ReadColumnHeader(Int32 i)
at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i)

And the maddening thing – it doesn’t happen often, just every once in a while. And it happens at different times too, sometimes reader would read 100 records, sometimes 200 etc.

One possible case – SqlDataReader is losing its connection. And one possible reason for that – connection goes out of scope.

Consider following scenario – you have a function that returns SqlDataReader:

Function GetTheReader() as SqlDataReader
   Dim oConn As New SqlConnection("Connection String") : oConn.Open()
   Dim oComm As New SqlCommand("Stored Procedure", oConn)

   Dim oReader As SqlDataReader = oComm.ExecuteReader(CommandBehavior.CloseConnection)

   Return oReader
End Function

And you use it like this:

Dim oReader as SqlDataReader = GetTheReader()
'Begin use reader - loop, read data etc.

The problem with this approach that connection used to create the reader is stored in a private variable inside of `GetTheReader` function and when the function exits – the variable goes out of scope. Eventually, sooner or later Garbage Collector will collect it and close and dispose of connection – and at this time your SqlDataReader will fail.

The solution? Either use SqlDataReader at the same scope level you created it, or, if you do need to use function – pass connection object into it as one of the parameters, so it would remain valid after function exits.

When SqlDataReader is missing rows

I have a very basic scenario:

  1. Execute TSQL Stored procedure
  2. Return a DataReader
  3. Read data from the Reader

This is ADO.NET 101. There is one problem: DataReader loses rows. This problem has haunted me forever, extensive research and numerous suggestion didn’t help, even though the code is extremely basic:

Get the reader:

m_dbSel.CommandType = CommandType.StoredProcedure
m_dbSel.CommandText = "SP_Name"
oResult = m_dbSel.ExecuteReader()

Pass the reader to class constructor to fill Generic List (of Integer): 

Public Sub New(i_oDataReader As Data.SqlClient.SqlDataReader)

    m_aFullIDList = New Generic.List(Of Integer)

    While i_oDataReader.Read
        m_aFullIDList.Add(i_oDataReader.GetInt32(0))
    End While

    m_iTotalNumberOfRecords = m_aFullIDList.Count

End Sub

This problem occurs when number of rows returned by the reader is relatively large (over 600,000 records). If this happens – number of rows added to the list from the reader is inconsistent, but always less than real one. Most often “magic” number of 524289 rows is returned.

Well, this is no longer a mystery, thanks to the great people from Stack Overflow @RBarryYoung, @granadaCoder and especially @MartinSmith who was the first to point me in the right direction – and here it is.

Even though the problem is with SqlDataReader – it is happening because it is used in conjunction with Generic List. List, as you may know has a flexible Capacity for number of elements it can store. When count of elements exceeds capacity – capacity increases and always to a power of 2. E.g.

When the count exceeds 4 elements – capacity is set to 8 (2^3)
When the count exceeds 8 elements – capacity is set to 16 (2^4)
When the count exceeds 16 elements – capacity is set to 32 (2^5)

etc..

This is what makes Generic List such a powerful tool, used by many large scale .NET projects, e.g. bingogodz.com. And ordinary this is not a problem. Unfortunately this is not the case when it is used together with SqlDataReader. When count of items in the List exceeds 524,288 (2^19) and its capacity is set to 1,048,576 (2^20) – SqlDataReader’s Read method suddenly returns False even though not all records have been read. No exception is thrown, it simple stops.

The only possible workaround I’ve found so far (I am still looking for better ones) is to pre-set List capacity in advance. Since, when using DataReader, you do not know number of rows, you’re left either with hardcoding the number or running another DB query to retrieve number of rows via something like COUNT(*). Like I said, I don’t like this workaround, please let me know if you find a better one.

UPDATE: Finally figured it out: http://stackoverflow.com/a/18520609/961695

TSQL: work with yyyy-mm-dd date format in British English

If you work with SQL Server that is set to use UK (British English) format you may experience a strange behavior: when you try to assign a date that is in ‘yyyy-mm-dd’ format to a DateTime variable or insert it into a DateTime column – an error is thrown

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

or similar. Or even worse – no error is thrown, but an incorrect date is inserted. This is happening because even though ‘yyyy-mm-dd’ looks like universal ISO format, a UK SQL Server interprets it as ‘yyyy-dd-mm’. So a date like ‘2011-07-21’ will throw the above error, but the date ‘2011-08-01’ will be stored as ‘January 8, 2001’ instead of expected ‘August 1, 2011’.

To alleviate this problem simple add a time part to your date – make the format ‘yyyy-mm-ddThh:mm:ss’. So the dates above will become ‘2011-07-21T00:00:00’ and ‘2011-08-01T00:00:00’ and be interpreted correctly by SQL Sercer

Correctly compare file date against TSQL stored date

If you need to track whether a particular file has changed or not (and I am not talking about FileSystemWatcher, often it’s an overkill), one way is to compare file’s LastWriteTime against stored value. But if you store the date value in SQL Server you may be in for a surprise – even if file date hasn’t changed – dates will not compare as equal. Consider following example:

'Reading current system file date/time
Dim dCurrentFileDate As DateTime = File.GetLastWriteTime(sFilePath)

'Storing file date/time in SQL Server table
CMyDatabaseClass.SaveDate(sFilePath, dCurrentFileDate)

'Immediately reading date/time value back
Dim dStoredFileDate As DateTime = CMyDatabaseClass.LoadDate(sFilePath)

'Comparing stored value against current
If dCurrentFileDate = dStoredFileDate
'
End If

The If statement on Line 11 will produce False results even if the dates seem identical. The reason – millisecond part of the datetime. SQL Server and .NET disagree on how to treat it so the best thing to do is to strip milliseconds from system file date/time before storing it or comparing against stored value.

Joe from StackOverflow.com offers a very elegant solution. That solution is in C# and here it is converted to VB.NET. To apply it to the example above simple add after the second line:

dCurrentFileDate = dCurrentFileDate.Value.AddTicks(-(dCurrentFileDate.Value.Ticks Mod TimeSpan.TicksPerSecond))

This will remove milliseconds from the date/time value and next time if you compare to the same value – values will be really equal.