Using The RANK() Function

Hey everyone! Welcome to (or back to) SQL Something!

Quick filler for this month: Using The RANK() Function.

I will demonstrate this function using a very simplistic example.

Firstly let's look at a table I created called [Files] that consists of the below dummy data:

Fig. 1: Important data...

Mini Post: SSIS Excel Destination Failed Validation And Returned Error Code 0xC020801C

Hey everyone! Welcome back to SQL Something!

Before the month is up (and I apologize for missing last month) let's get in a quick post. This time we look at a simple SSIS validation error: "SSIS Excel Destination Failed Validation And Returned Error Code 0xC020801C".

This little guy popped when I attempted to export data to an Excel Destination in SSIS:

Fig. 1: Final Destination

Looking up the error code can yield the following: "SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.".

The error was produced because excel does not have a 64 bit driver. If your OS is 64 bit, SSIS will, by default, attempt to run all drivers as 64 bit. It is simply a matter of going to the project properties and changing the Run64BitRuntime property to FALSE:

Fig. 2: Finding Properties

Fig. 3: Make the change to FALSE

And that's all there is to it. :-)


DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: The data types ntext and varchar are incompatible in the equal to operator.

Hello and welcome back to SQL Something!

It's Geon coming in at the eleventh hour to drop a post before the month is up.

Today we are going to look at the following error(s):

Msg 402, Level 16, State 1, Line <number>
The data types ntext and varchar are incompatible in the equal to operator.

OR

Msg 306, Level 16, State 2, Line <number>
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Fig. 1: #ntextProblems

As the text is basically saying, these errors occur when you try to use an equal operator ("=") to compare a varchar value to a ntext value.
So what can you use to compare? The answer is right in the second error. You can use a LIKE.


See the example below:

...WHERE ntextValue LIKE 'SomeTextValue'...

Simple enough. :-)



DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

List Multiple Row Values on Single Row (Comma Separated List)

Hey every and any body! Welcome back (or welcome to) SQL Something!

The main post for this month is looking at how to create a comma separated list for a one to many relationship (found either within the same table or between two tables).

Let's look at the following. Suppose I have the following values in a table:

Fig. 1: Transactions and their Messages...

You can see there exists a one to many relationship between this table (Table1) and another table (Table2) where multiple messages in Table1 correspond to single transaction in Table 2.

Suppose we wanted to list only the transactions and their corresponding messages. We can list the results as follows:

Fig. 2: List of Transactions and their Messages...

But suppose we want to list all the corresponding messages in one field as follows:

Single row list of Transactions and their Messages...

To achieve that we'll have to use FOR XML, specifically the FOR XML PATH variant. We can then use the below query:

--------------------------------------------------------------------------------------

SELECT T1.Tran_ID,
        (   SELECT       T2.Message + ','
            FROM          [Table1] T2
            WHERE       T2.Tran_ID = T1.Tran_ID
            ORDER BY T2.Message
            FOR XML PATH('')
        ) AS Transactions
FROM [Table1] T1
GROUP BY Tran_ID;

--------------------------------------------------------------------------------------

What this query does is reference itself with a subquery for each instance of a Tran_ID. For each instance of a Tran_ID, it looks for the corresponding messages and, using FOR XML PATH(''), it concatenates them with a comma separator.

By providing a 'blank' hierarchy in the FOR XML PATH clause, SQL does not create any element tabs and so we get one continuous string. A very good read up on the various FOR XML variations can be found here.

And that's it. :-)

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: Using sp_executesql

Hey everyone and welcome back to SQL Something!

Today I'm going to slip in a quick post before the month ends. Today we are going to look at sp_executesql.

This system stored procedure allows users to run batches of T-SQL code. Its use of parameters allowed for a bit more security as opposed to using something like EXEC by itself. Below we can see a simple example of the format needed to use sp_executesql.

--------------------------------------------------------------------------------------

DECLARE @SQLString NVARCHAR(500); 
SET @SQLString = N'SELECT @MessageOut = f.message
                                     FROM Test.dbo.Files f 
                                     WHERE File_ID = @level';

DECLARE @ParmDefinition NVARCHAR(500);
SET @ParmDefinition = N'@level tinyint,
                                            @MessageOut varchar(30) OUTPUT';

DECLARE @Result VARCHAR(30);

EXEC sp_executesql
@SQLString,
@ParmDefinition,
@level = 2,
@MessageOut = @Result OUTPUT;

SELECT @Result;

-------------------------------------------------------------------------------------- 

To use sp_executesql we can to do the following:

• Declare a SQLString variable to hold the T-SQL query/batch we would like to run.
• Declare a Parameter Definition string to hold a string of parameters that we would like to use. We can also declare a OUTPUT parameter here in order to pass information back to the caller.
• If you are returning information you may want to declare a variable to store the returned information (@Result).
• Use the sp_executesql system stored procedure and give it the SQL String variable as well as the parameter definition and the values for each parameter defined. If you have an OUTPUT parameter you can save the result to your @Result variable.
• Finally, select your @Result if you have one.

And there you go. You can help prevent SQL Injection in your applications this way. :-)

EDIT: Also, and I may go into this in more detail later, but I saw a very good demonstration illustrating that sp_executesql caches execution plans even if you use different parameter values. This can help speed up queries a good bit. :-)


DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

How to Get the Last (Latest/Newest) Record in a One-to-Many Relationship/Join

Good day everyone and welcome back to SQL Something!

Today we're looking at an issue that was presented to me today: How to get the last record in a one to many relationship.

Some digging online found this original post here, which I then had to deconstruct to understand it. To that end, I made a couple test tables and then went through the query, while making small modifications to suit my needs.

Let's go through it.

Mini Post: Specifying A Failover Partner In A Connection String

Hey everyone, Geon here again with another SQL Something post!

This time we are going to look really quickly at modifying a connection string to have a failover partner. What this means is if you have a database setup that includes Mirroring (circa SQL Server 2008), then you should make sure your applications take advantage of it. Your applications (when configured correctly) can automatically failover to the secondary DB when the current DB becomes unavailable.

Which is pretty cool by itself, but also very useful as it minimizes downtime and any manual intervention needed to point the apps to the failover DB.

And it is really simple to do.

Mini Post: Saving changes is not permitted. The changes you have made require...

Hey Everyone! (Capital 'E' since you are all that important!)

Welcome to or welcome back to SQL Something!

A real quick one today to maintain my quota: Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.

Fig. 1: Oh Noes!

You may encounter this error when you try to make adjustments to columns of a table that has data in it. Examples are dropping/adding a column(s).

Simple enough to 'fix'.

Mini Post: List All Stored Procedures/Functions In A Database (Information_Schema.Routines)

Hey all! Welcome to, or welcome back, to SQL Something!

Today we're taking a quick look at how to list all Stored Procedures (and Functions) in a database. As per usual, I needed to do this at some point for my job and so I'm sharing what I found. :-)

In order to get the information we need we will be querying information_schema.routines as follows:

SELECT    ROUTINE_NAME as 'Stored Procedure',
        ROUTINE_CATALOG as 'Database Name',
        ROUTINE_SCHEMA as 'Database Schema',
        ROUTINE_TYPE as 'Object Type (PROCEDURE/FUNCTION)',
        ROUTINE_DEFINITION as 'Stored Procedure Definition',
        CREATED as 'Date Created',
        LAST_ALTERED as 'Date Last Altered'
FROM information_schema.routines with (nolock)
WHERE ROUTINE_TYPE = 'PROCEDURE'
ORDER BY ROUTINE_NAME

The above will list all Store Procedures for the database that you run the query on. Very helpful! :-)


Now you'll notice the WHERE clause specifies the PROCEDURE type only. If we wanted info on Functions as well as Stored Procedures, we can remove this clause.


If we wanted info on Functions only and wanted some additional info like what value the Function returns, we tweak the query as follows:

SELECT    ROUTINE_NAME as 'Function Name',
        ROUTINE_CATALOG as 'Database Name',
        ROUTINE_SCHEMA as 'Database Schema',
        ROUTINE_TYPE as 'Object Type (PROCEDURE/FUNCTION)',
        ROUTINE_DEFINITION as 'Function Definition',
        DATA_TYPE as 'Return Value Type',
        CREATED as 'Date Created',
        LAST_ALTERED as 'Date Last Altered'
FROM information_schema.routines with (nolock)
WHERE ROUTINE_TYPE = 'FUNCTION'
ORDER BY ROUTINE_NAME

DATA_TYPE returns the data type of the Function's return value or it returns TABLE if it's a table valued function.


DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: Each GROUP BY expression must contain at least one column that is not an outer reference

Good day and welcome back to SQL Something!

I'm ashamed at how long I've been away and there is really no excuse. It is, however, a new year (2015 people!), so I'm going to try to be much better. Here's hoping!

We'll start this year with a very simple error that you may see when running a query:

"Each GROUP BY expression must contain at least one column that is not an outer reference"

The error itself is a bit vague, but refers to something quite simple. As the above error indicates, a "Group By" is involved. Maybe your query involves a variable like so:

SELECT COUNT([Column1]) AS TotalCount, [Column2], @Variable as VariableName
FROM [Schema1].[Table1]
WHERE [Column2] = @Variable
GROUP BY [Column2], @Variable;

Do you know what's wrong? It's the variable in the GROUP BY clause.

Normally, when using GROUP BY, you are required to put all the columns in the SELECT clause that are not part of a aggregate function into the GROUP BY clause. Following that logic you should also put any local variables that you are using in the SELECT clause. This however is not so.

Removing the variable like so fixes the query:

SELECT COUNT([Column1]) AS TotalCount, [Column2], @Variable as VariableName
FROM [Schema1].[Table1]
WHERE [Column2] = @Variable
GROUP BY [Column2];

Thank you, as usual, for your time. :-)


DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)