Category Archives: SQL

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:

Unsorted SELECT

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:

  1. @val1 is quite large
  2. 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 →

SQL Server stored procedure runs slow from .NET code

This has probably been discussed a lot before, but just in case here it is again, possible solution for following scenario:

You’re calling SQL Server stored procedure from your .NET code and it runs extremely slow. When you run same SP with exactly the same parameters (as captured by SQL Server Profiler) directly in SQL Server Management Studio, it runs very fast. What gives?

Chances are – that SP was executed before and query plan was cached for the specific parameters. To avoid this add WITH RECOMPILE option to your CREATE PROCEDURE or ALTER PROCEDURE statement. This will force SQL Server to create a new query plan every time SP runs, perhaps adding slight overhead, but creating an optimized path that will cover that overhead tenfold.

Solution for “OraOLEDB.Oracle Provider is not registered” error

While connecting from an ASP.NET application to an Oracle database via OLEDB I got following error:

OraOLEDB.Oracle Provider is not registered on the local machine

Now I know that the driver was installed and registered. I downloaded an official driver from Oracle WebSite. In my case it was ODAC112021Xcopy_x64.zip 64-bit version for XCopy deployment. It installs in 2 easy steps:

  1. Unzip downloaded file into any folder
  2. Run (as administrator) command: INSTALL TYPE PATH NAME DEPENDANCIES

Where

  • TYPE – type of installation (e.g. OLEDB, basic etc.)
  • Path – where you want driver installed
  • Name – Oracle home name
  • Dependencies – true/false whether to install dependencies (e.g. instant client)

So my command was

INSTALL ALL “C:\Program Files\Oracle64Driver” Oracle64Driver TRUE

Which copied the files and created correct Registry entries (I checked). And still I was getting the error. I Googled it (a lot) but majority of suggestions was that the error is due to Windows ACL and correct permissions should be set on the driver folder. Didn’t help me.

So I fired up trusted ProcessMonitor and it showed that W3WP.EXE (ASP.NET process) was trying to access missing OCI.DLL file in the path C:\Program Files\Oracle64Driver\Bin, e.g. in the Bin folder of the path were the driver was installed. Looking back at the place were I unzipped the original driver files I found that DLL inside of “instantclient” folder. So I copied entire content of that folder into Bin folder at the destination. And Voila! The error disappeared.

Apparently Instant Client files aren’t copied by the installer even when Dependencies option is set to true.

Stored Procedure in LINQ2SQL query

Linq2Sql has a great use of stored procedures – it converts them into methods which you can easily call using standardized .NET syntax. For example if you have SP:

ALTER PROCEDURE MyProcedure(MyParam int) ...

after dragging it into Linq2Sql designer you can call it in your .NET code like this:

Dim aResults = MyDbContext.MyProcedure(2011)

but there are 2 caveats. Continue reading →

SSRS: How to implement build-in parameter based on external parameter passed from ReportViewer

Imagine following scenario: an SSRS report has a dropdown list “lookup” parameter based on a stored procedure. When report runs, user selects a value from the dropdown, clicks “View Report” and report is generated. The challenge is – the “lookup” parameter (and underlying stored procedure) needed to be filtered by another “filter” parameter – and this one is not available in SSRS interface, but instead is passed from ReportViewer control from an ASP.NET application.

In order to successfully implement this use case 2 items need to be addressed:

First in the report itself parameters need to be ordered in such way so “filter” comes before “dropdown”. Parameters can easily be arranged in Business Intelligent Development Studio, by expanding Parameters node, selecting a parameter, and using arrow buttons in Report Data menu.

Second in the ASP.NET application, configuring ReportViewer control (setting credentials, server URL, report path and our “filter” parameter) needs to be done in Page_Init event in the “If Not IsPostback” block.

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 →