Using The LEAD() And/Or LAG() Functions

Heyyy, sliding in at the eleventh hour (as usual) we have the first SQL Something for 2016!
Hard to believe that I've actually managed to, sort of, maintain this for 3 years.

Anyhow, we end the year with a couple functions that follow a similar format to RANK(): LEAD()/LAG().

LEAD() and LAG() allow users to list the value of the next row or previous row respectively with some given offset from the current row.

Simple example time.

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.