TSQL Archives » SQLErudition.com

SSIS Error DTS_E_OLEDBERROR with code: 0x80040E55

November 30, 2016Aalam RangiLeave a comment

SQL Agent and SSIS packages have multiple failure codes but DTS_E_OLEDBERROR or 0x80040E55 don’t tell you much about the cause.

One of my jobs failed sporadically with the following error.

Message
Code: 0xC0202009
Source: [29]
Description: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred.
Error code: 0x80040E55.
An OLE DB record is available.
Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80040E55
Description: "Column does not exist.".
End Error

Error: 2016-11-30 11:20:03.11
Code: 0xC0047038
Source: SSIS.Pipeline
Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
The PrimeOutput method on returned error code 0xC0202009.
The component returned a failure code when 
the pipeline engine called PrimeOutput().
The meaning of the failure code is defined 
by the component, but the error is fatal and 
the pipeline stopped executing.
There may be error messages posted before this
with more information about the failure.
End Error

The error message “Column does not exist.” led me to think that the stored procedure or one of the tables have been altered.

A quick look at the created date and modified date of the stored procedure and the tables confirmed that there were no schema changes.

So why the missing column error?

I executed the stored procedure manually and that revealed the issue.

There was TRY CATCH block in the procedure. It didn’t re-raise the error but returned it with a SELECT statement.

For example –

BEGIN TRY
  -- some sample sql
  SELECT
    o.OrderNumber,
    o.OrderLineNumber
  FROM
    dbo.Order as o
END TRY
BEGIN CATCH
  SELECT
     ERROR_NUMBER() AS ErrorNumber
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

So when there was any error, the error message was returned as a SELECT output.

The column names of this output were different from the original columns. This caused the SSIS package to fail.

Take away –
Use RAISERROR or THROW in TSQL to return the errors to the calling application. Using a SELECT for this purpose can confuse the calling application to consider that as a data set.

SQL Formatting and Object Naming Convention

September 15, 2016September 15, 2016Aalam RangiLeave a comment

SQL formatting may not increase the functionality of the code but good and consistent formatting by the developers certainly affect the readability of the code and therefore makes it easier to maintain. Keyword and separator alignment, spaces and tabs provide opportunities for a lot of variations in code layout. A case-insensitive server/database give further flexibility in formatting with choosing upper or lower casing of the text.

Just as an experiment to see the effect of casing and object naming conventions on readability, I created this dummy stored procedure and formatted it in four different ways. The white-space layout, i.e. tabs, spaces, newline etc is same in all of them.

Which one do you prefer?

Do you have your own layout options that you would like to share?

Feel free to copy the script and share your formatted version in comments.

Version 1

-- underscore as word separator
-- lowercase keywords
create procedure dbo.sp_get_employee
	@employee_id int,
	@department_id int
as
begin
	select
		e.first_name,
		e.last_name,
		e.designation,
		e.salary,
		e.manager_first_name,
		e.manager_last_name,
		d.department_name
	from
		dbo.employee as e with (nolock)
		inner join dbo.department as d with (nolock)
		on e.department_id = d.department_id
	where
		employee_id = @employee_id and
		department_id = @department_id;

	return 0;
end

Version 2

-- underscore as word separator
-- UPPERCASE keywords
CREATE PROCEDURE dbo.sp_get_employee
	@employee_id INT,
	@department_id INT
AS
BEGIN
	SELECT
		e.first_name,
		e.last_name,
		e.designation,
		e.salary,
		e.manager_first_name,
		e.manager_last_name,
		d.department_name
	FROM
		dbo.employee AS e WITH (NOLOCK)
		INNER JOIN dbo.department AS d WITH (NOLOCK)
		ON e.department_id = d.department_id
	WHERE
		employee_id = @employee_id AND
		department_id = @department_id;

	RETURN 0;
END

Version 3

-- camel/pascal casing for objects names
-- lowercase keywords
create procedure dbo.spGetEmployee
	@EmployeeId int,
	@DepartmentId int
as
begin
	select
		e.FirstName,
		e.LastName,
		e.Designation,
		e.Salary,
		e.ManagerFirstName,
		e.ManagerLastName,
		d.DepartmentName
	from
		dbo.Employee as e with (nolock)
		inner join dbo.Department as d with (nolock)
		on e.DepartmentId = d.DepartmentId
	where
		EmployeeId = @EmployeeId and
		DepartmentId = @DepartmentId;

	return 0;
end

Version 4

-- camel/pascal casing for objects names
-- UPPERCASE keywords
CREATE PROCEDURE dbo.spGetEmployee
	@EmployeeId INT,
	@DepartmentId INT
AS
BEGIN
	SELECT
		e.FirstName,
		e.LastName,
		e.Designation,
		e.Salary,
		e.ManagerFirstName,
		e.ManagerLastName,
		d.DepartmentName
	FROM
		dbo.Employee AS e WITH (NOLOCK)
		INNER JOIN dbo.Department AS d WITH (NOLOCK)
		ON e.DepartmentId = d.DepartmentId
	WHERE
		EmployeeId = @EmployeeId AND
		DepartmentId = @DepartmentId;

	RETURN 0;
END

Use 130 Characters to Store an Object Name

November 11, 2015Aalam RangiLeave a comment

Some index maintenance scripts or dynamic SQL scripts use SQL object names as values for variables or columns i.e. database name, table name, index name etc.

SQL Server object names can be at most 128 characters long, so common wisdom is to declare the holding variable or column as SYSNAME data type or one of the alphanumeric datatype with a width of 128 characters.

Example –

declare @dbname sysname
declare @tablename nvarchar(128)
declare @indexname varchar(128)

This is technically correct.

Another technically correct thing to do is to use the QUOTENAME function to wrap the object name in [ and ] brackets. This handles those cases where there are special characters in the name or the name is a reserved keyword.

Example –

set @dbname = quotename(db_name(db_id()))
set @tablename = quotename(object_name(object_id))
set @indexname = quotename(object_name(object_id))

The Issue

The brackets will add two more characters to the value.

So for really long object names that are 128 characters in length, the value would be 128 + 2 = 130 characters.

The two extra characters will break the variable assignment or row insert statements with truncation error –

String or binary data would be truncated. [SQLSTATE 22001] (Error 8152).

In my shop, we do have some long index names and I have had to debug some scripts for this issue. And this is not the first time!

The Solution

I suggest that you use 130 as the width for the variables or columns that will store an object name. SQL Server object names can be maximum 128 characters long so using 130 characters in scripts will handle the extra two characters.

TSQL Gotcha – Order of expressions for a range search with BETWEEN

November 27, 2014Aalam RangiLeave a comment

Do you trust your users to always pass range search parameters in the correct order? Or do the users trust that the code will take care of a small thing like parameter ordering?

If you have a stored procedure or a script that accepts two parameters to do a range search using a BETWEEN keyword (or maybe with a >= and <=) then it is important to verify that the start expression and end expression are in the correct order. The correct order for numeric values is smaller value before a larger value. The correct order for character values is the dictionary order. A user might pass on the expression values the wrong way around and see no results returned.

The following demonstration has a small check for the correct order of values and to do a quick reorder to fix any issues. To help things a bit more, instead of using generic variable names like @param1 and @param2, they can be made self-documenting by being descriptive like @begin_param and @end_param.

Demonstration with numeric values:

declare @DemoTableNumeric table 
(col1 int)

-- Insert 10 rows in the demo table
insert into @DemoTableNumeric (col1)
values 
(1),(2),(3),(4),(5),
(6),(7),(8),(9),(10)

-- verify data
select * 
from @DemoTableNumeric

declare 
@param1 int, 
@param2 int

-- Assign values
-- Note: Param1 &gt; Param2
select 
@param1 = 7, 
@param2 = 4

-- The following return zero rows
-- because the first expression is
-- greater than the second expression
-- It is a wrong order of values.
select * 
from @DemoTableNumeric
where 
col1 between @param1 and @param2

select * 
from @DemoTableNumeric
where 
col1 &gt;= @param1 and col1 &lt;= @param2

-- It is important to verify the expression
-- values and reorder them if necessary
-- The following IF condition does that
if @param1 &gt; @param2
begin
	declare @temp int
	set @temp = @param1
	set @param1 = @param2
	set @param2 = @temp
end

-- Now both queries return rows
select * 
from @DemoTableNumeric
where 
col1 between @param1 and @param2

select * 
from @DemoTableNumeric
where 
col1 &gt;= @param1 and col1 &lt;= @param2

Demonstration with character values:

declare @DemoTableChar table 
(col1 varchar(10))

insert into @DemoTableChar (col1)
values
('Alpha'),
('Golf'),
('Kilo'),
('Oscar'),
('Tango'),
('Zulu')

-- verify data
select * 
from @DemoTableChar

declare 
@param1 varchar(10), 
@param2 varchar(10)

-- Note: Param1 &gt; Param2
select 
@param1 = 'Tango', 
@param2 = 'Golf'

-- This returns zero rows
-- because the first expression is
-- greater than the second expression
-- It is a wrong order of values.
select * 
from @DemoTableChar
where 
col1 between @param1 and @param2

select * 
from @DemoTableChar
where 
col1 &gt;= @param1 and col1 &lt;= @param2

-- It is important to verify the expression
-- values and reorder them if necessary
if @param1 &gt; @param2
begin
	declare @temp varchar(10)
	set @temp = @param1
	set @param1 = @param2
	set @param2 = @temp
end

-- Now both queries return rows
select * 
from @DemoTableChar
where 
col1 between @param1 and @param2

select * 
from @DemoTableChar
where 
col1 &gt;= @param1 and col1 &lt;= @param2

Suppress the Error Number, Severity Level and State Number in the Error output

September 2, 2014Aalam RangiLeave a comment

For any reason, if you don’t want to show the Error Number, Severity Level and the State Number along with the Error Message, use the Severity Level 0 (Zero) or 10. The Severity Number 10 is converted to Zero internally.

Demonstration:

A severity number except 0 or 10 displays the Error Number, Severity and State information.

A severity number 0 or 10 suppresses the Error Number, Severity and State information.

Further Reading:

Database Engine Error Severities
http://msdn.microsoft.com/en-us/library/ms164086(v=sql.105).aspx

Find Top 25 Inefficient Query Plans by CPU, IO, Recompiles, Execution Count

June 2, 2014Aalam RangiLeave a comment

Microsoft TechNet Gallery is a treasure trove of scripts that can save you a lot of coding time or sometimes introduce creative ways of solving a challenge.

I came across this collection of scripts that will show you the top 25 inefficient query plans (in XML format) sorted by CPU, IO, recompiles, execution counts etc.

Download: http://gallery.technet.microsoft.com/Find-inefficient-query-88f4611f

TSQL Gotcha – Putting a Condition in WHERE or ON clause: Does it Matter in TSQL?

May 12, 2014Aalam RangiLeave a comment

A filter condition can be syntactically put in the WHERE clause of the query or the ON clause too. But does it matter?

No, it does not matter for an INNER join. I found this one example of an exception in Martin Smith’s comment on Stack Overflow where using the GROUP BY ALL makes a difference in results. GROUP BY ALL is now a deprecated syntax (Feature ID 169) so probably we can ignore this exception. But it is always good to know.

On the other hand, Yes, it could affect the output in any of the OUTER joins.

This happens because when a filter criteria is made a part of the JOIN condition, it is applied at the time of reading the tables to identify the rows for the JOIN. The rows that do not satisfy the filter criteria are not included for the purpose of the JOIN and that causes NULLs in the OUTER JOINs.

However, with the filter criteria a part of the WHERE clause, it is applied after the query has been processed and the adhoc dataset has been created internally. The final dataset is reduced to just those rows which meet the filter criteria.

I will use a criteria of c.isactiveYN = ‘Y’ for demonstration below. Rows with a value of ‘Y’ are used for the join. The rows with ‘N’ or NULLs don’t match the criteria so they are left out.

As the examples show below, this seemingly minor difference can cause substantial difference in the query output of OUTER JOINs. This effect could cascade on to other subsequent tables in bigger queries that are joined to the original tables.

-- Create demo tables and insert some data
declare @customer table 
(id int, 
cname varchar(20), 
isactiveYN char(1))

declare @order table 
(id int, 
customerid int, 
orderdate date)

insert into @customer 
values
(1, 'Bob',   'Y'),
(2, 'Harry', 'N'),
(3, 'Max',   'Y')

insert into @order
values
(1, 1, '2014-1-1'),
(2, 1, '2014-2-2'),
(3, 2, '2014-3-3'),
(4, 2, '2014-4-4'),
(5, 3, '2014-5-5'),
(6, 3, '2014-6-6')

select * from @customer
select * from @order

Results :

Demonstration: The location of criteria has no effect on the results in INNER JOIN.

-- A simple INNER JOIN without any 
-- conditions to display all data:

-- INNER JOIN with out any conditions
select * 
from 
  @customer as c
inner join 
  @order as o
  on c.id = o.customerid

-- The following two queries differ
-- in the location of the criteria
-- but still show similar results

-- INNER JOIN with filter in WHERE clause
select * 
from 
  @customer as c
inner join 
  @order as o
  on c.id = o.customerid
where
  c.isactiveYN = 'Y'

-- INNER JOIN with filter in ON clause
select * 
from
  @customer as c
inner join 
  @order as o
  on c.id = o.customerid
  and c.isactiveYN = 'Y'

Results : The second and third results are similar.

Demonstration: LEFT OUTER joins with the condition in the WHERE clause and the ON clause

-- LEFT JOIN: Filter in WHERE clause
select * 
from 
  @customer as c
left outer join 
  @order as o
  on c.id = o.customerid
where 
  c.isactiveYN = 'Y'

-- LEFT JOIN: Filter in ON clause
select * 
from 
  @customer as c
left outer join
  @order as o
  on c.id = o.customerid
  and c.isactiveYN = 'Y'

Results : “Harry” is not in the first result-set because the row was filtered out by the WHERE clause. “Harry” is in the second result-set because it comes from the LEFT OUTER table but there is no matching row because it was not used for the JOIN.

Demonstration: RIGHT OUTER joins with the condition in the WHERE clause and the ON clause

-- RIGHT JOIN: Filter in WHERE clause
select * 
from 
  @customer as c
right outer join 
  @order as o
  on c.id = o.customerid
where 
  c.isactiveYN = 'Y'

-- RIGHT JOIN: Filter in ON clause
select * 
from 
  @customer as c
right outer join 
  @order as o
  on c.id = o.customerid
  and c.isactiveYN = 'Y'

Results : “Harry” is not in the first result-set because the row was filtered out by the WHERE clause. “Harry” is not in the second result-set because it has been filtered out and it is also not in the OUTER table.

Summary: When the criteria is in the WHERE clause, The results of the INNER, LEFT OUTER and the RIGHT OUTER queries are the same. Differences show up in the results of LEFT and RIGHT OUTER queries when the criteria is in the ON clause.

TIMESTAMP and ROWVERSION datatypes and the Behavior of the SQL Server @@DBTS Counter

April 9, 2014Aalam RangiLeave a comment

The TIMESTAMP datatype in SQL Server is in no way related to any Date/Time datatype or any operation related to dates or time. It is a simple monotonously incrementing counter that increases by one every time there is an INSERT or UPDATE operation in the database. It is stored as BINARY but can be converted to BIGINT or INT type to make the values seem more user-friendly. The IDENTITY property of a table is quite similar when we think about a monotonously incrementing value. While the IDENTITY counter is tracked at a table level, the TIMESTAMP counter is tracked at a database level. The current value of the TIMESTAMP counter can be returned by using the Configration Function @@DBTS. We can assume a TIMESTAMP to be a GUID in the smaller context of a database. So theoretically no two tables in a database can have the same TIMESTAMP value in any of their rows. But practically it is possible to go around this via SELECT..INTO syntax to create a new table, as I’ll demostrate later in this post.

As calling it a TIMESTAMP has caused confusion among SQL developers, Microsoft introduced ROWVERSION as a new name for it. Calling it ROWVERSION also emphasizes its utility for implementing optimistic locking in the code. TIMESTAMP has been deprecated and will be discontinued in a future SQL Server release so use ROWVERSION in all new development work.

It is optional to give the column a name in the DDL with the TIMESTAMP datatype. If a column name is not specified then SQL Server uses the default name of “TIMESTAMP” for the column. Helpful, eh?

On the other hand, if using the ROWVERSION as datatype, a column name must be given just like creating a column any other datatype.

A table can have only one column of TIMESTAMP (or ROWVERSION) type.

You cannot enter/update a value explicitly into a column of TIMESTAMP or ROWVERSION datatype. The database manages it internally. If you do try then an error is returned –

Msg 273, Level 16, State 1, Line 1

Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

The DBTS value starts from 2000 in a brand new database without any table containing a TIMESTAMP/ROWVERSION column.

CREATE DATABASE [TestDB] 
GO

-- Find the default value of 
-- the TIMESTAMP/ROWVERSION counter 
SELECT @@DBTS AsBinary, 
       CAST(@@DBTS AS BIGINT) AsBigint 

/* Result
AsBinary                AsBigint 
0x00000000000007D0      2000 
*/

Does the value of the counter change after new table is created with a TIMESTAMP/ROWVERSION?

No. Just creating a table does not change the counter.

CREATE TABLE a (
       id INT IDENTITY, 
       TIMESTAMP)
GO
SELECT @@DBTS AsBinary, 
       CAST(@@DBTS AS BIGINT) AsBigint 
/* Result
AsBinary               AsBigint 
0x00000000000007D0     2000 
*/

Insert a row in a table and check counter value. It increases by one.

INSERT INTO a DEFAULT VALUES
GO
SELECT *, 
       CAST(timestamp AS BIGINT) 
FROM a 

SELECT @@DBTS AsBinary, 
       CAST(@@DBTS  AS BIGINT) AsBigint 
/* Result
AsBinary               AsBigint 
0x00000000000007D1     2001 
*/

Create a new table, this time with ROWVERSION and insert a row. The counter still increases by one.

CREATE TABLE b (
       id INT IDENTITY, 
       col2  ROWVERSION) 
GO
INSERT INTO b DEFAULT VALUES
GO
SELECT *, 
       CAST(col2  AS BIGINT) 
FROM b 

-- Find the value of the counter after 
-- a row is inserted 
SELECT @@DBTS AsBinary, 
       CAST(@@DBTS  AS BIGINT) AsBigint 
/* Result
AsBinary               AsBigint 
0x00000000000007D2     2002 
*/

Do the INSERTS in the same transaction have the same counter value? Let us try.

BEGIN TRAN 
INSERT INTO a DEFAULT VALUES
INSERT INTO b DEFAULT VALUES
COMMIT

SELECT *, 
       CAST(timestamp AS BIGINT) 
FROM a 

SELECT *, 
       CAST(col2 AS BIGINT) 
FROM b 

-- Find the value of the counter after rows 
-- are inserted in multiple tables in a transaction. 
-- Each row still gets a unique counter value.
SELECT @@DBTS AsBinary, 
       CAST(@@DBTS  AS BIGINT) AsBigint 
/* Result
AsBinary               AsBigint 
0x00000000000007D4     2004 
*/

Does the counter increase in case of a failed transaction?

BEGIN TRAN 
INSERT INTO a DEFAULT VALUES
INSERT INTO b DEFAULT VALUES
ROLLBACK

SELECT *, 
       CAST(timestamp AS BIGINT) 
FROM a 

SELECT *, 
       CAST(col2 AS BIGINT) 
FROM b 

-- Find the counter value after a failed transaction
-- Yes. The counter value still increases.
SELECT @@DBTS AsBinary, 
CAST(@@DBTS AS BIGINT) AsBigint 
/* Result
AsBinary               AsBigint 
0x00000000000007D6     2006 
*/

Is is possible to have duplicate TIMESTAMP values across tables of a database?
Yes. Although values in TIMESTAMP/ROWVERSION columns cannot be explicitly inserted or modified they can be copied from one table to another using the SELECT..INTO syntax, which in turn could result in duplicate counter values across tables

SELECT *
INTO c -- New table
FROM a

SELECT *, 
       CAST(timestamp AS BIGINT) 
FROM a -- Source table 

SELECT *, 
       CAST(timestamp AS BIGINT) 
FROM c -- New table 

-- The counter value does not change.
SELECT @@DBTS AsBinary, 
       CAST(@@DBTS AS BIGINT) AsBigint 
/*
AsBinary               AsBigint 
0x00000000000007D6     2006 
*/

Does the counter continue with insertions into new table that was created with SELECT…INTO? Yes, the counter changes because the new table gets the column definition of the source table including the ROWVERSION column.

INSERT INTO c DEFAULT VALUES
SELECT *, 
       CAST(timestamp AS BIGINT) 
FROM c -- New table 

-- Find the value of the counter after a row 
-- is inserted into the new table.
-- The counter value does change.
SELECT @@DBTS AsBinary, 
       CAST(@@DBTS AS BIGINT) AsBigint 
/*
AsBinary               AsBigint 
0x00000000000007D7     2007 
*/

Final review of the counter values in tables

SELECT *, CAST(timestamp AS BIGINT) 
FROM a 

SELECT *, CAST(col2 AS BIGINT) 
FROM b 

SELECT *, CAST(timestamp AS BIGINT) 
FROM c

TSQL Gotcha – Quotes are optional for a Stored Procedure VARCHAR parameter without spaces

March 27, 2013Aalam RangiLeave a comment

If you want to assign some default value to a parameter of type VARCHAR in a stored procedure and the parameter value does not have any spaces then the quotes around the value are optional.

create procedure TestProc
(
@a varchar(20) = 'spaces in string', --must use quotes for string with spaces
@b varchar(20) = 'nospacesinstring',
@c varchar(20) = nospacesinstring --quotes are optional if no spaces in string
)
as
select @a, @b, @c;
;
exec TestProc;

TSQL Gotcha – ISNUMERIC Function Results

March 24, 2013Aalam RangiLeave a comment

The ISNUMERIC function (MSDN documentation) is used to validate if a string is a valid number. In the demonstration that follows, you would expect the results from 1 to 5 but look closely at the results 6, 7 and 8. There are multiple commas in the string and the last one is not even a number!

Well, a note at the MSDN documentation explains the True result for the ‘$’ sign at Id 8 –

ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols, see Using Monetary Data.

For the strings at Id 6 and 7 with commas, the function behaves that if the string is convertible to a number then the result will be True. Something to remember while writing those data validation routines.

DECLARE @t TABLE
( id int, col1 VARCHAR(10) )

INSERT INTO @t (id, col1)
VALUES (1, '-1001'),
(2, '0'),
(3, '1001.50'),
(4, '1,500'),
(5, 'abc'),
(6, '15,0,1'),
(7, '15,0,1.75'),
(8, '$')

SELECT id,
col1,
Isnumeric(col1) AS [IsNumeric]
FROM @t