Post reply

Value not recognized although data is present

  • February 22, 2010 at 9:03 am

    #98546

    I'm currently getting and assigning an error description from msdb.dbo.sysdtssteplog to a local variable like so:

    declare @ErrorDesc varchar(500)

    select @ErrorDesc = b.ErrorDescription from

    (select top 1 LineageFull from msdb.dbo.sysdtspackagelog

    Order By LogDate Desc) a

    JOIN (select ErrorDescription,LineageFull from msdb.dbo.sysdtssteplog) b on a.LineageFull = b.LineageFull

    Where isnull(b.ErrorDescription,'') <> ''

    The error description from the table looks something like this (notice the white space in the begining):

    '

    Step Error Source: Microsoft Data Transformation Services (DTS) Package Step Error Description:The task reported failure on execution. (Microsoft OLE DB Provider for SQL Server (80004005): The statement has been terminated.) (Microsoft OLE DB Provider for SQL Server (80004005): Cannot insert duplicate key row in ......'

    I've applied the below logic immediately after the select statment shown above.

    IF isnull(@ErrorDesc,'') > '' BEGIN SET @ImportStatus = 'Failed' END

    When executed this does not return true so the @ImportStatus will not be set to "Failed". I copied the begining values of the actual column error description and used the ASCII command and it returned char(32) (Space). However when I copy/paste the string (see below) and set it to the variable the "If" statement will work.

    SET @ErrorDesc = '

    Step Error Source: Microsoft Data Transformation Services (DTS) Package...'

    Are the characters in the begining actually Char(32) and if so why when assigned to a variable thru the select command does it not recognize the string as > '' ? My workaround is to use LEN but it seems like it's a quark. Any ideas? TIA.

  • February 22, 2010 at 2:14 pm

    #1123277

    Hi

    Firstly, try NULLIF instead of ISNULL to eliminate empty strings and NULLs of ErrorDescription from the output.

    Secondly, rewrite the query to eliminate the possibility of more than one row returning to the variable assignment, something like this:

    SELECT TOP 1 @ErrorDesc = b.ErrorDescription

    FROM msdb.dbo.sysdtssteplog b

    INNER JOIN msdb.dbo.sysdtspackagelog a ON a.LineageFull = b.LineageFull

    WHERE NULLIF(b.ErrorDescription,'') IS NOT NULL

    ORDER BY a.LogDate DESC

    Thirdly, I'd check this query, and the original, in a query window - without the variable assignment:

    SELECT TOP 1 '['+b.ErrorDescription+']'

    FROM msdb.dbo.sysdtssteplog b

    INNER JOIN msdb.dbo.sysdtspackagelog a ON a.LineageFull = b.LineageFull

    WHERE NULLIF(b.ErrorDescription,'') IS NOT NULL

    ORDER BY a.LogDate DESC

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply