A while back I wrote an article on how to Cancel long running SQL Command in ASP.NET WebForm application
Webucator ASP.NET training was kind enough to turn it into video-lesson. Enjoy!
Tag Archives: SQL
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
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.
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
TSQL: Mix column names and ordinals in ORDER BY
It might be a weird scenario from a parallel universe, but in infinite dimensions it is bound to happen to someone else besides me. If you’re getting results from a query where you know some columns by name and others only by their ordinal position (e.g. if SQL is built dynamically and can change based on different condition) – you add sorting to that query by both ordinal position of a column and column name and even throw an expression into the mix. Allow me to illustrate:
Let’s get back to basics, namely Northwind database. If I ran a query like this:
SELECT * FROM Customers
I will get a result, similar to this:
Now to the weird part. Let’s say I need to sort this query by by Contact’s title and Contact’s last name. I don’t know what the column name for the Contact’s title will be, but I know if will always be a static 4th column. I don’t know where in the resultset Contact’s full name be located (it could be 3rd, or 8th or whatever column) but I do know it will always be called ContactName. Continue reading →
T-SQL String DeAggregate (Split, Ungroup) in SQL Server
A while back I posted a simple way to aggregate strings in SQL Server, but what if your task is opposite – you have a character-separated string values in your fields and you need to slice them into separate rows? Consider following scenario, you have a table:
CREATE TABLE #Employees (Country varchar(50), FirstNames varchar(50))
INSERT INTO #Employees(Country,FirstNames)
VALUES ('UK','Anne,Michael,Robert,Steven')
INSERT INTO #Employees(Country,FirstNames)
VALUES ('USA','Andrew,Janet,Laura,Margaret,Nancy')
SELECT Country, FirstNames FROM #Employees
That looks like this
Country FirstNames UK Anne,Michael,Robert,Steven USA Andrew,Janet,Laura,Margaret,Nancy
and now you need to split FirstNames so that every FirstName is displayed on separate row. Continue reading →
@@ROWCOUNT is affected by IF statement
Let’s say you’re writing T-SQL code and need to make sure that your query returns one and only row. If no records returned – an error message needs to be shown. If more than one record is returned – another error message needs to be show. The code goes something like this:
-- ... query runs here and returns row
IF @@ROWCOUNT = 0 RAISERROR('No records found', 18, 1)
ELSE IF @@ROWCOUNT > 0 RAISERROR('More than one record found', 18, 1)
-- ... continue when exactly one row is returned
Now if your query returns 3 records you’d expect it to trip the second IF statement and raise error with correct message. Unfortunately this doesn’t happen. The reason being – the first IF statement resets @@ROWCOUNT to 0. So, to avoid this we need to preserve the @@ROWCOUNT value in a local variable:
DECLARE @iRowCount int
-- ... query runs here and returns row
SET @iRowCount = @@ROWCOUNT
IF @iRowCount = 0 RAISERROR('No records found', 18, 1)
ELSE IF @iRowCount > 0 RAISERROR('More than one record found', 18, 1)
-- ... continue when exactly one row is returned
This way count of rows returned by the query is saved and is not affected by any following statements.
TSQL: Filling missing date range
Let’s say you have a table with some dates and numeric values e.g.:
2011-08-19 16 2011-08-22 45 2011-08-24 62 2011-08-25 88 2011-08-27 17 2011-08-28 35 2011-09-01 10 2011-09-02 79 2011-09-03 70 2011-09-07 83
As you can see not all dates are in the sequential order, 24th comes after 22nd etc. But in many cases you need this data to be sequential, for example if this data feeds a chart you need to fill it with missing dates and 0 for a value. There is an easy way to achieve this. Continue reading →
Elusive “String or binary data would be truncated” error
This was driving me nuts. I have a very basic SQL code similar to
ALTER PROCEDURE Proc1(@val1 NVARCHAR(max))
AS
BEGIN
-- some code
EXEC Proc2 @val2 = @val1
-- some other code
END
I was getting error “String or binary data would be truncated”, but only when 2 conditions were met:
- @val1 is quite large
- Either SQL Server/computer just restarted or stored procedure Proc2 was just updated (ALTER)
Error happened just by the fact of the EXEC Proc2 being there it didn’t even had to do anything, it could RETURN straight away. Both @val2 an @val1 are of a type NVARCHAR(max) so there is no reason for the error. The error happened only once, after that I can pass data of any size – and no error would happen. Like I said – nuts. Continue reading →
Individual Sorting of SELECT queries in the UNION
UNION is a very common operator used in SQL, it allows to combine result of multiple queries into one. Unfortunately if you want to sort this unified resultset sorting is applied to entire result at once. But what if you wan to sort queries-participants individually?
Let’s say you have 2 following queries from Northwind database:
SELECT TOP (5) CompanyName FROM Suppliers ORDER BY CompanyName
Aux joyeux ecclésiastiques Bigfoot Breweries Cooperativa de Quesos 'Las Cabras' Escargots Nouveaux Exotic Liquids
and
SELECT TOP (5) ProductName FROM Products ORDER BY ProductName
Alice Mutton Aniseed Syrup Boston Crab Meat Camembert Pierrot Carnarvon Tigers
And now you want to combine them into a single result, adding title lines to separate each result. The direct approach would be:
SELECT 'Suppliers' AS Name UNION SELECT TOP (5) CompanyName FROM Suppliers UNION SELECT 'Products' UNION SELECT TOP (5) ProductName FROM Products ORDER BY Name
But result is far from what we wanted
Alice Mutton Aniseed Syrup Aux joyeux ecclésiastiques Bigfoot Breweries Boston Crab Meat Camembert Pierrot Carnarvon Tigers Cooperativa de Quesos 'Las Cabras' Escargots Nouveaux Exotic Liquids Products Suppliers
Entire resultset is sorted uniformally and products are mixed with suppliers.
Continue reading →