Error Reporting from Stored Procedures

How do you report errors from a stored procedure? For example, if the stored
procedure is trying to insert something into a table and the database is
full, how can the stored procedure return an error string to the caller?Hi Swami,
This article might help...
http://www.sqljunkies.com/Article/5...3B6D84BF94.scuk
"Swami" wrote:

> How do you report errors from a stored procedure? For example, if the stor
ed
> procedure is trying to insert something into a table and the database is
> full, how can the stored procedure return an error string to the caller?
>|||Thanks--great article.
"Ryan Randall" wrote:
> Hi Swami,
> This article might help...
> [url]http://www.sqljunkies.com/Article/564F5D77-2F7E-41FB-91C7-353B6D84BF94.scuk[/url
]
>
> "Swami" wrote:
>

Error reading variable

I keep receiving errors while using variables to pass values to different part of my package. For example ...

Error: 2006-05-04 10:31:59.84
Code: 0xC00470EA
Source: EdwPostProcess
Description: Reading the variable "User::GvPathRoot" failed with error code 0xC0010009.
End Error

The way my variables are constructed is :

First the global variables (Gv*) are set by the parent package; Then local variables (Lv*) are set using the EvaluateAsExpression property and giving it an expression that takes the Gv* variable and concatenate a string to it.

At execution time, while the expressions are resolved, I get the above error while it was resolved correctly in a previous task.

I tried different method including duplicating my variables but without success. I'm running out of ideas

Gilles

Sorry I don't have an answer. But just wanted to report a very similar problem I am having. I am also at my wits' end since the problem cannot be reproduced at will.

I am also passing variable from parent package to a child package. There is a loop in the parent package and in each iteration, the value of this variable changes. There are 9 iterations of the loop and so 9 different values of this variable. Most of the times all the loops are successful (particularly if the volume of data is less). If the volume of data processed by the child package is more then I sometimetimes get this error in the 5th or later iteration. I have changed the order of the values of the variable but looks like it does not matter - it fails at different values.

Ketan

|||

My variable was used in a script task and in expressions on other tasks. By re-arranging my tasks flow so the script task wouldn't be executed at the same time as another task with the same variable seem to have corrected the problem.

A variable used by a script task is lock in memory even when it is used for read only. So depending on how fast each task completes, it is possible that sometime we encounter the variable locked situation sometime not.

I found a way to eliminate the script task from my flow and everything is fine now.

Gilles

|||Another good technique to minimise locking is to reduce the scope of variables. You may end up with what seem like duplicates, by scoping variables to loops and containers or tasks, but this is more reliable than sharing variables when strictly speaking you never use the value outside of the lower scoped executable.

Error reading variable

I keep receiving errors while using variables to pass values to different part of my package. For example ...

Error: 2006-05-04 10:31:59.84
Code: 0xC00470EA
Source: EdwPostProcess
Description: Reading the variable "User::GvPathRoot" failed with error code 0xC0010009.
End Error

The way my variables are constructed is :

First the global variables (Gv*) are set by the parent package; Then local variables (Lv*) are set using the EvaluateAsExpression property and giving it an expression that takes the Gv* variable and concatenate a string to it.

At execution time, while the expressions are resolved, I get the above error while it was resolved correctly in a previous task.

I tried different method including duplicating my variables but without success. I'm running out of ideas

Gilles

Sorry I don't have an answer. But just wanted to report a very similar problem I am having. I am also at my wits' end since the problem cannot be reproduced at will.

I am also passing variable from parent package to a child package. There is a loop in the parent package and in each iteration, the value of this variable changes. There are 9 iterations of the loop and so 9 different values of this variable. Most of the times all the loops are successful (particularly if the volume of data is less). If the volume of data processed by the child package is more then I sometimetimes get this error in the 5th or later iteration. I have changed the order of the values of the variable but looks like it does not matter - it fails at different values.

Ketan

|||

My variable was used in a script task and in expressions on other tasks. By re-arranging my tasks flow so the script task wouldn't be executed at the same time as another task with the same variable seem to have corrected the problem.

A variable used by a script task is lock in memory even when it is used for read only. So depending on how fast each task completes, it is possible that sometime we encounter the variable locked situation sometime not.

I found a way to eliminate the script task from my flow and everything is fine now.

Gilles

|||Another good technique to minimise locking is to reduce the scope of variables. You may end up with what seem like duplicates, by scoping variables to loops and containers or tasks, but this is more reliable than sharing variables when strictly speaking you never use the value outside of the lower scoped executable.

Error Parsing Name String

I''m reposting this problem because I may not have explained it well enough
before. in example 1 below, i've successfully extracted the last name from a
full name field, although I've hard-coded the fullname or userName.
Example 2 gives the listed error when i run it on my table with data in the
userName field. What I can't figure is Example 2 will work if I don't
include the "-1" part, buh returns the lastname and it's trailing comma.
What am I doing wrong? My data looks like the DATA section below.
--EXAMPLE 1: works , hard-coded name
SELECT 'Miles, Michael L' as userName,
LEFT('Miles, Michael L',CHARINDEX(',','Miles, Michael L')-1) as userLast
-- EXAMPLE 2
-- Error Msg 536, Level 16, State 3, Line 1
-- Invalid length parameter passed to the substring function.
SELECT name as userName,
LEFT(name,CHARINDEX(',',name)-1) as userLast
FROM sc_employee
DATA Table***********************************
****************
USERNAME
Smith, John R
Walker, L.T.
Adams, Charles
Weathersby, D.J.On Sun, 2 Apr 2006 17:56:42 -0500, scott wrote:
(snip)
>-- EXAMPLE 2
>-- Error Msg 536, Level 16, State 3, Line 1
>-- Invalid length parameter passed to the substring function.
>SELECT name as userName,
>LEFT(name,CHARINDEX(',',name)-1) as userLast
>FROM sc_employee
Hi Scott,
You probably have at least one row where there is no comma in the Name
column. To find the offenders, use
SELECT name
FROM sc_employee
WHERE name NOT LIKE '%,%'
And to exclude the offending rows from your query, change it to
SELECT name as userName,
LEFT(name,CHARINDEX(',',name)-1) as userLast
FROM sc_employee
WHERE name LIKE '%,%'
Hugo Kornelis, SQL Server MVP|||I found a null record. thanks.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:6nm0321acbpk9am57qii9nk8p23dinp6a3@.
4ax.com...
> On Sun, 2 Apr 2006 17:56:42 -0500, scott wrote:
> (snip)
> Hi Scott,
> You probably have at least one row where there is no comma in the Name
> column. To find the offenders, use
> SELECT name
> FROM sc_employee
> WHERE name NOT LIKE '%,%'
> And to exclude the offending rows from your query, change it to
> SELECT name as userName,
> LEFT(name,CHARINDEX(',',name)-1) as userLast
> FROM sc_employee
> WHERE name LIKE '%,%'
> --
> Hugo Kornelis, SQL Server MVP

Error output question

Hello,

I realize I have a question about what constitutes an "error" for an error output.

For example, a flat file source has an error output for "bad rows", that is, when it encounters "unexpected data". What specifically is "unexpected data"? Is this documented somewhere?

Another example would be an OLE DB source that uses a query to retrieve row. This too, has an error output, but I realize I have no clue what would constitute bad data from a table. I mean, data in a table is just data, so what would constitute an error from an OLE DB source? I can't think of one thing. Where are these "rules" documented, if anywhere?

Thanks

"Handling Errors in Data" in Books Online has some great information on this.

Errors can be different for different components. Sources most often suffer from data type conversion issues. The error output adds an ErrorCode and you can call Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData90.GetErrorDescription in a script component to see the actual error message.