SSIS ErrorCode and ErrorColumn

SSIS is a total jerk.  It provides an ErrorCode and ErrorColumn on an error path, but then gives you no way to automatically translate the meaning of those codes.  For example, I’m getting ErrorCode -2146233033 and ErrorColumn 743.

When I use the code for the script component suggested by Microsoft, (this.ComponentMetaData.GetErrorDescription(Row.ErrorCode)), then it gives me the error “CATASTROPHIC ERROR“.    So we struck out on that solution.

There’s a few incredibly complicated solutions out there, and some third party plug-ins.  but I’m not sure I want to go down that road.

I’ll be better off just to dump the results of the error into a table, then do my own validation, and manually construct the error messages.

The other annoying thing is that in the XML Source component, it resets itself every time you look at the columns or error output tabs.

Leave a comment

Filed under Uncategorized

Crowd deadlock

There is a 3rd party tool at my company that is causing a lot of deadlocks after an upgrade.

The main problem for me is that it fills the SQL error logs with error messages.   This was happening 20 times a day.

It happens when two of these SQL update statements run simultaneously, and one of them gets killed.

update cwd_user_attribute
set user_id=@P0, directory_id=@P1, attribute_name=@P2, attribute_value=@P3, attribute_lower_value=@P4
where id=@P5

Here is the problem.  Since this statement updates the user_id (maybe unnecessarily), SQL has to make sure the foreign key constraint is still valid, by checking the table dbo.cwd_user.   But this causes deadlocks in some situations.   Although I rarely recommend this, removing this foreign key would remove the deadlocks.

I’m guessing since it’s a third party tool, the SQL can’t be changed.  But if it can, I would recommend not updating user_id, since that is the logical key of the table, and is not changing anyway.  I see this kind of thing a lot with the ORMs.

Here is the drop foreign key command:

ALTER TABLE dbo.cwd_user_attribute
DROP CONSTRAINT fk_user_attribute_id_user_id

Here is the deadlock graph:

Image

Leave a comment

March 11, 2014 · 3:15 pm

SQL Join syntax delayed ON operator

I learned something new today. In JOIN T-SQL syntax, the ON is not necessary immediately.   I’m not sure what benefit this would give, but is a nice piece of trivia.

For example, the following query WORKS in the AdventureWorks2012 database:

SELECT *
FROM HumanResources.EmployeePayHistory
INNER JOIN HumanResources.Employee
INNER JOIN HumanResources.EmployeeDepartmentHistory
INNER JOIN HumanResources.Department

ON Department.DepartmentID =
   EmployeeDepartmentHistory.DepartmentID

ON EmployeeDepartmentHistory.BusinessEntityID =
   Employee.BusinessEntityID

ON EmployeePayHistory.BusinessEntityID =
   Employee.BusinessEntityID

The order of the ONs matters.   The last two tables joined need to have the first ON, continuing in reverse order.

Leave a comment

Filed under Uncategorized

ssrs report stuck on loading

For me, it just had trouble rendering a simple line that was drawn inside the header.

Leave a comment

Filed under Uncategorized

SSAS reserved word error

Errors in the metadata manager. 

The ‘Customers’ dimension contains a member property with invalid name: ‘Name’ is one 
of the reserved words.

Despite renaming everything in sight that had the word Name, it was still giving me this error.

I just had to delete the Customer dimension, then build it again.

Leave a comment

Filed under Uncategorized

Profiler on Prod

My whole career I’ve heard that running SQL Server Profiler is a low cost operation, and has little effect on the server.  And I believed this within reason, of course, because I always added a lot of filters.

But recently when trying to get understand a process that runs in the middle of the night, I came across a problem.  In the morning, my Profiler trace was filled with the message “Trace Skipped Records”.

This eventually led me to a useful blog post by Grant Fritchey:  Snags With Profiler GUI, which led to another post, Profiler Research, which outlines why a client-side trace is inefficient.

Finally, since now I need to re-learn how to do server-side traces, I am following a Stairway Series on SQL Server Central.  Stairway to Server-Side Tracing

So far it seems a lot easier, because I can query the trace directly using SQL, to further filter out results. And I bet I can schedule it to kick off in the middle of the night.

Leave a comment

Filed under Uncategorized

Red Gate SQL Prompt DBCC TRACEON

Red Gate SQL Compare generates hundreds of these messages in the SQL Server error logs:

DBCC TRACEON 3604, server process ID (SPID). This is an informational message only; no user action is required.
DBCC TRACEOFF 3604, server process ID (SPID). This is an informational message only; no user action is required.

Using a web search, it seems like the solution is to turn off the check box for decrypt encrypted objects. However, it doesn’t remove these completely.

I’m disappointed that RedGate doesn’t have an article explaining why these messages are necessary. Instead, they have people search through the forums, which don’t seem incredibly helpful.

It’s a really good tool though, so it’s not a huge price to pay.

The way to find the computers that are generating these messages is to use SQL Profiler.  Use a standard trace, then uncheck all the events other than “SQL:BatchCompleted”.

In the Column Filters, put these in the following:

  • ApplicationName -> like -> %Red Gate%
  • TextData -> like -> %DBCC%

Even better, use a server-side trace. Here is a script to create that trace:

/****************************************************/
/* Created by: SQL Server 2012 Profiler */
/* Date: 02/12/2014 02:32:38 PM */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 2, N'B:\Traces\DBCCTraceON', @maxfilesize, NULL, 3
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 1, 0, 6, N'%DBCC TRACE%'
exec sp_trace_setfilter @TraceID, 10, 0, 6, N'%Red Gate%'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

Leave a comment

Filed under Uncategorized