How can i get the details of the error

How can i get the details of the error in sql server 2000 such where it
occurs in which line. I need something like i get in sql query analyzer.

--
Message posted via http://www.sqlmonster.comE B via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> How can i get the details of the error in sql server 2000 such where it
> occurs in which line. I need something like i get in sql query analyzer.

In SQL 2000, the only item you can find out about an error is the
number of the error.

In SQL 2005, currently in beta, there is a vastly improved support in
this area.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, however what can you advice me??

--
Message posted via http://www.sqlmonster.com|||E B via SQLMonster.com (forum@.SQLMonster.com) writes:
> Thanks, however what can you advice me??

Since I don't know what you are after, it's quite difficult to give any
advice.

However, I have two articles on my web site on error handling in SQL Server.
They may give you some guidande.
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||ok thanks

--
Message posted via http://www.sqlmonster.com

How can I get the actual line number from inside flatfile sources for each record?

hi all,

maybe this is not a serious problem, but I tried for days to come toi a solution without success.

My Problem:

I have have several flatfile sources I need to import into a sql-server 2005 DB.

It is very important for me to have the original line number from inside the source file for each record. The rowcount transform doesn't fit in for this task, because it accumulates all rows until the end of the dataflow.

I tried script components and it works fine if i assume there are no errors in my source. then I simply could declare a local variable and count it up and add a custom collumn to my output. But for errors in my source this won't work, because a second script component won't know the actual value of a package level variable, which i use to store the value, because i am only allowed to access this variable in the post execujte method of the script.

How can I achieve my goal? Please help me...

Thanks in advance .. Bernd

Can I clarify something?

Do you want the number of rows in the file or the row number adding for each row? Your post above is a little ambiguous about this because you say "original line number from inside the source" but then mention the rowcount component which is nothing to do with line numbers.

-Jamie

|||

I want the line number of the Flat File, which holds the Record.

I don't have them in the source. you may think of it as an Row ID.

Thanks Bernd

|||

OK, gotcha. Well the rowcount component is completely irrelevant here.

SSIS does not give row numbers to rows in the pipeline. I've previously asked for this to be changed which you can vote for and add a comment on here if you like: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=131335

In the meantime, the alternative is to artifically create your own row numbers as explained here: http://www.sqlis.com/default.aspx?37 however this may not work for you as the row numbers cannot be added until after the rows have been extracted into the pipeline. It depends on your requirement.

If you need row numbers added as the file is parsed then you're in script source component or custom component territory I'm afraid.

-Jamie

|||

Thank you very, even if my problem isn't solved.

Personally I think it is a major flaw, because we are not able to track direcly back into the source, which sometimes is extremely important. I support your request for adding this information into the Pipeline without question.

Thank you again

Bernd

|||

berndS wrote:

Thank you very, even if my problem isn't solved.

Personally I think it is a major flaw, because we are not able to track direcly back into the source, which sometimes is extremely important. I support your request for adding this information into the Pipeline without question.

Thank you again

Bernd

Thanks Bernd,

In that case, click the first link in my last post and vote for it. I forgot to add the link in before (sorry) but I have now edited it!

I also think this is a really important feature!

-Jamie

|||

Hmm, hope it's OK to play devil's advocate here. How would this work in a data flow merge, conditional split, etc? Seems like a lot of the processing that is needed in ETL and supported in SSIS would confound the meaning of source row number?

|||

kenambrose wrote:

Hmm, hope it's OK to play devil's advocate here. How would this work in a data flow merge, conditional split, etc? Seems like a lot of the processing that is needed in ETL and supported in SSIS would confound the meaning of source row number?

Ken

Yeah of course it would, absolutely - in which case it has no meaning and you don't use it. Ideally you'd simply have a boolean property on every source adapter saying whether or not to add this "rownumber" column.

If its there and you don't want it - ignore it!

I'd still like to have this feature.

-Jamie

|||

I was just wondering if this might be one feature that sounds good on paper but has limited practical value. Since I insist on data models and domains that make use of natural keys (and always design solutions based on this paradigm), I can't see how it would be of much use in my work. If I ever need to find a row, I always can do so by using the key...

|||

kenambrose wrote:

I was just wondering if this might be one feature that sounds good on paper but has limited practical value. Since I insist on data models and domains that make use of natural keys (and always design solutions based on this paradigm), I can't see how it would be of much use in my work. If I ever need to find a row, I always can do so by using the key...

True enough. Where I think it would be useful would be when importing a file. If a row goes down the error flow it'd be nice to know which row it was.

-Jamie

|||

Jamie,

is there any way to get the error details from a source connector when you set it to ignore errors? Otherwise it would be easy to add the row number by a counter (there are some ways to do that, you know)... The only problem are the error lines. SSIS simply changes the value of a non-readable field to NULL. You don't get any error information (at least that's what I see). If there would be any way to find out that a record has an error (and probably what kind of error in which field) then we could just ignore all errors first, add the counter and do some manual error handling later...

Thanks,

|||

Thomas Pagel wrote:

Jamie,

is there any way to get the error details from a source connector when you set it to ignore errors?

I don't think so Thomas, no. Do these errors trigger the OnError eventhandler? I'm 99% sure they don't but don't have a SSIS instance to hand to check.

-Jamie

How can I get the actual line number from inside flatfile sources for each record?

hi all,

maybe this is not a serious problem, but I tried for days to come toi a solution without success.

My Problem:

I have have several flatfile sources I need to import into a sql-server 2005 DB.

It is very important for me to have the original line number from inside the source file for each record. The rowcount transform doesn't fit in for this task, because it accumulates all rows until the end of the dataflow.

I tried script components and it works fine if i assume there are no errors in my source. then I simply could declare a local variable and count it up and add a custom collumn to my output. But for errors in my source this won't work, because a second script component won't know the actual value of a package level variable, which i use to store the value, because i am only allowed to access this variable in the post execujte method of the script.

How can I achieve my goal? Please help me...

Thanks in advance .. Bernd

Can I clarify something?

Do you want the number of rows in the file or the row number adding for each row? Your post above is a little ambiguous about this because you say "original line number from inside the source" but then mention the rowcount component which is nothing to do with line numbers.

-Jamie

|||

I want the line number of the Flat File, which holds the Record.

I don't have them in the source. you may think of it as an Row ID.

Thanks Bernd

|||

OK, gotcha. Well the rowcount component is completely irrelevant here.

SSIS does not give row numbers to rows in the pipeline. I've previously asked for this to be changed which you can vote for and add a comment on here if you like: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=131335

In the meantime, the alternative is to artifically create your own row numbers as explained here: http://www.sqlis.com/default.aspx?37 however this may not work for you as the row numbers cannot be added until after the rows have been extracted into the pipeline. It depends on your requirement.

If you need row numbers added as the file is parsed then you're in script source component or custom component territory I'm afraid.

-Jamie

|||

Thank you very, even if my problem isn't solved.

Personally I think it is a major flaw, because we are not able to track direcly back into the source, which sometimes is extremely important. I support your request for adding this information into the Pipeline without question.

Thank you again

Bernd

|||

berndS wrote:

Thank you very, even if my problem isn't solved.

Personally I think it is a major flaw, because we are not able to track direcly back into the source, which sometimes is extremely important. I support your request for adding this information into the Pipeline without question.

Thank you again

Bernd

Thanks Bernd,

In that case, click the first link in my last post and vote for it. I forgot to add the link in before (sorry) but I have now edited it!

I also think this is a really important feature!

-Jamie

|||

Hmm, hope it's OK to play devil's advocate here. How would this work in a data flow merge, conditional split, etc? Seems like a lot of the processing that is needed in ETL and supported in SSIS would confound the meaning of source row number?

|||

kenambrose wrote:

Hmm, hope it's OK to play devil's advocate here. How would this work in a data flow merge, conditional split, etc? Seems like a lot of the processing that is needed in ETL and supported in SSIS would confound the meaning of source row number?

Ken

Yeah of course it would, absolutely - in which case it has no meaning and you don't use it. Ideally you'd simply have a boolean property on every source adapter saying whether or not to add this "rownumber" column.

If its there and you don't want it - ignore it!

I'd still like to have this feature.

-Jamie

|||

I was just wondering if this might be one feature that sounds good on paper but has limited practical value. Since I insist on data models and domains that make use of natural keys (and always design solutions based on this paradigm), I can't see how it would be of much use in my work. If I ever need to find a row, I always can do so by using the key...

|||

kenambrose wrote:

I was just wondering if this might be one feature that sounds good on paper but has limited practical value. Since I insist on data models and domains that make use of natural keys (and always design solutions based on this paradigm), I can't see how it would be of much use in my work. If I ever need to find a row, I always can do so by using the key...

True enough. Where I think it would be useful would be when importing a file. If a row goes down the error flow it'd be nice to know which row it was.

-Jamie

|||

Jamie,

is there any way to get the error details from a source connector when you set it to ignore errors? Otherwise it would be easy to add the row number by a counter (there are some ways to do that, you know)... The only problem are the error lines. SSIS simply changes the value of a non-readable field to NULL. You don't get any error information (at least that's what I see). If there would be any way to find out that a record has an error (and probably what kind of error in which field) then we could just ignore all errors first, add the counter and do some manual error handling later...

Thanks,

|||

Thomas Pagel wrote:

Jamie,

is there any way to get the error details from a source connector when you set it to ignore errors?

I don't think so Thomas, no. Do these errors trigger the OnError eventhandler? I'm 99% sure they don't but don't have a SSIS instance to hand to check.

-Jamie

How can i find the exact error line ?

Hi all,

i have an insert function using stored procedure with parameters. At some point while assigning parameters i make a casting problem but all i get is:

String was not recognized as a valid Boolean.

how can i find the exact line causing the error?

thank you

-shane

can you post the code? Perhaps you are passing some incompatible value for a boolean type parameter?|||It's listed in the stack trace that follows that message you pasted here. If you stopped the program in VS 2005, tell it to continue executing, and you'll see it on the HTML page.|||I m using vs 2003 and in the stack tree there is no explanation about it. i found the problem , i was trying to compare a posted radiobutton list item value (which is 0 or 1) with 0 and 1 wihtout knowing radiobutton values were sent as string. i got teh error message because i was going like :
if request("rblcontrol') then
do this...
end if

The problem is; i dont get the line that was causing the problem. And this is what i was looking for. You find the prblem this way or that way, maybe after several hours, but is there a way to get the exact line number that is causing hte problem. Stack tree doesnt give me this, if it would i woulnt ask this question. I tried debugging but i m not getting the error while debugging either. its just happening at the end without giving me any clue of whats causing the problem.

Any ideas/
thank you
-shane|||Usually it will tell you in vs or in a webpage the error with the exact page as long as the page was run on the local computer. Did you end up casting the value to boolean?|||

Once again, my problem isnt with the page. Of course i m getting the error in the page it occurs. The problem is the line that causes the problem.

I am gettng ar error in abc.aspx but the only thing i get is generic error message. For some cases i get the line number, usually for the casting problems i never get the line number. Its taking for ever to find which line causes the problem because sometimes everthing looks so right.

-shane

|||You're not being clear. Which line number? In your .aspxpage, in your code-behind page, or in your stored procedure?

Could you possibly show the part of the code casuing trouble and the exact copy-and-paste of the error screen?