Excel Import: handling

Showing posts with label handling. Show all posts

Monday, March 26, 2012

EXEC and Error Handling

I am building a Bulk Insert statement dynamically passing in filename location, etc.

I have coded error handling via the @.@.ERROR. To test the error handling, I am forcing the file that is bulk inserted to be missing.

The statement is created in a declared variable, IE @.SQL:

EXEC(@.SQL)

IF @.@.ERROR <> 0

BEGIN
GOTO ErrorHandler
END

Since the file is missing it causes an error, and the Stored Procedure gives the

Server: Msg 4860, Level 16, State 1, Line 1 Could not find the file etc.
and aborts aborts the whole stored proc at that point. In other words the error handling IF @.@.ERROR doesn't trap the error and send the process to the error handler routine.

Is there anyway to get the error back from the EXEC, or maybe something has to be set to trap the error because it is fatal? Because of the way the Bulk Insert statement is created dynamically, it appears you have to EXEC the statement once it is built (EXEC(@.SQL)).

Any help would be appreciated.

Barryyou can do something like :

begin Transaction exec1
-- your sql stmts

If @.@.ERROR > 0
begin
RAISERROR('Error in sp',16,1)
ROLLBACK TRANSACTION exec1
RETURN 99
END
COMMIT TRANSACTION exec1

sql

Friday, March 9, 2012

ExceptionHandling in T-SQL?

Hi all,

Is there any concept of exception handling in T-SQL while writing Stored Procedures, plz. help me with this issue. I'm using SQL Server 2000

Hello Trid,

No, there is no structural error handling in 2000 so to speak. There are several ways to perform error handling in 2000, with the most common being to test the value of @.@.ERROR for a non-zero value, then send control to a handler lable for rollback etc.

if (@.@.error <> 0)

goto error_handler

return (0)

error_handler:

if (@.@.trancount > 0)

rollback tran

return (-1)

Cheers

Rob

|||

To back up what Robert said, T-SQL did not have a rich set of error handling capabilities in 2000, whatsoever. You have to deal with the error message on the client end. So be careful to check for and close any transactions you might have started when you get error messages back and want to stop the batch.

|||

Thank you Robert :)

Wednesday, March 7, 2012

Exception handling with full text search

Hello,
I am dynamically generating and SQL query, which could contain illegal
words. I receive an exception and dialog box,
"System.Data.SqlClient.SqlException: A clause of the query contained only
ignored words".
Is there a way to catch that exception and resume processing (of the rest of
the application)?
My C# code looks like:
try {
...
myReader = myCommand.ExecuteReader(CommandBehavior.CloseConne ction);
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
...
}
How can I handle the exception?
Many thanks,
Urban
No, there is no way to do an error resume next.
You have to avoid this error by one of the three methods
1) use a FreeText search which solves this error almost all the time, but
you may get too many results returned
2) remove the noise words from the search phrase by client side parsing.
This is not always the best solution as your search may not always return
what you expect, but in general works well.
3) remove all entries from your noise word list and replace it with a blank
space. Rebuild your catalogs. The problem with this approach is that your
catalogs can get much larger and performance will consequently drop
somewhat.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Urban Bettag" <urban@.bettag.com> wrote in message
news:1092323291.76604.0@.demeter.uk.clara.net...
> Hello,
> I am dynamically generating and SQL query, which could contain illegal
> words. I receive an exception and dialog box,
> "System.Data.SqlClient.SqlException: A clause of the query contained only
> ignored words".
> Is there a way to catch that exception and resume processing (of the rest
of
> the application)?
> My C# code looks like:
> try {
> ...
> myReader = myCommand.ExecuteReader(CommandBehavior.CloseConne ction);
> }
> catch (Exception e)
> {
> Console.WriteLine(e.ToString());
> ...
> }
> How can I handle the exception?
> Many thanks,
> Urban
>
|||> No, there is no way to do an error resume next.
> You have to avoid this error by one of the three methods
> 1) use a FreeText search which solves this error almost all the time, but
> you may get too many results returned
> 2) remove the noise words from the search phrase by client side parsing.
> This is not always the best solution as your search may not always return
> what you expect, but in general works well.
> 3) remove all entries from your noise word list and replace it with a
blank
> space. Rebuild your catalogs. The problem with this approach is that your
> catalogs can get much larger and performance will consequently drop
> somewhat.
Thanks Hilary,
I am using SQL Server 2000 and within a hosted environment I possibly have
not
full control of the noise word list. Or is there a way to programmatically
access
that list?
It looks like I will try to go for 2), filtering out what is in the default
noise
word list.
Many thanks,
Urban
|||There is no way to programmatically access this list other than by using
trial and error methods to determine exactly what this list contains.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Urban Bettag" <urban@.bettag.com> wrote in message
news:1092324646.15314.0@.iris.uk.clara.net...[vbcol=seagreen]
but[vbcol=seagreen]
return[vbcol=seagreen]
> blank
your
> Thanks Hilary,
> I am using SQL Server 2000 and within a hosted environment I possibly have
> not
> full control of the noise word list. Or is there a way to programmatically
> access
> that list?
> It looks like I will try to go for 2), filtering out what is in the
default
> noise
> word list.
> Many thanks,
> Urban
>
>
|||Urban and Hilary,
Well you could do something like this... (from a previous post on this
subject):
Create Table noise_words ( Noiseword varchar(50) Not Null )
Go
Alter Table noise_words Add Constraint PK_noise_words Primary Key Clustered
( Noiseword )
Go
Then you can use BULK INSERT, BCP or DTS task to copy the contents of the
file into the database either at server startup or via a SQLServerAgent job
when the you change the noise word file. Before you copy in the
language-specific noise word file, you will need to make some changes to the
initial file from the end of the file as the noise word files contain a list
of "white space" single letters and characters at the end of the file, for
example, from noise.enu:
a b c d e f g h i j k l m n o p q r s t u v w x y z
BULK INSERT or BCP will fail or think this is one big string (no CR/LF), so
you will need to separate out the row above such that each letter takes up
its own row in the file. Open the language specific noise word file in a
text editor (notepad.exe) and change the above list to:
a
b
c
d
e
f
and so on. Be sure to eliminate any leading or trailing spaces for each
character. Once that's done, you can use BULK INSERT, BCP or DTS to copy the
data from the noise file to the noise_words table. Once the data is imported
correctly, you can use a standard SQL statement such as:
select Noiseword from noise_words where Noiseword = "between"
to use in a string parser function in your C# code to remove the noise words
in your users input string and then pass this edited string to a SQL Server
FTS query.
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:elwYwTIgEHA.704@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> There is no way to programmatically access this list other than by using
> trial and error methods to determine exactly what this list contains.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Urban Bettag" <urban@.bettag.com> wrote in message
> news:1092324646.15314.0@.iris.uk.clara.net...
> but
parsing.[vbcol=seagreen]
> return
> your
have[vbcol=seagreen]
programmatically
> default
>

Exception handling in Triggers.

If we can get away from the trigger for a second.
One of the least documented aspects on how transactions
work are to do with the process id that its running from.
So if you open up QA do transaction mock statement, open
up another window in QA and do rollback you will get an
error, why, becasue the process in the second window is
different to the first.
The reason why this is important is that a rollback as you
found out will effect EVERY transaction in that process
even though it maybe nested. Try this yourself, you will
get some very interesting answers.
So to get back to your question.
SQL Server will automatically rollback transactions if the
error is serious enough.
If there is no transaction then there will be no
transaction count, there is a handy little global variable
called @.@.TRANCOUNT you may want to look at. So if that is
set to anything but a 0 it will automatically rollback the
transaction irrespective on whether it is done in trigger
or something else.
From application programs then it depends on the error. If
you had a connection object and you application (not SQL)
failed then it would be up to your application to repair
the DB by sending it up a rollback.
If you application using the same connection as the begin
transaction sent some SQL that caused an error then yes it
would automatically roll back.
Anyway that it. I sugest you have a play on QA.
If you have any questions then don't hesitate to email me
on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
busy tonight)
Peter
"You can always count on Americans to do the right thing -
after they've tried everything else."
Winston Churchill

>--Original Message--
>Can anybody point me to a good text about
>exception handling in triggers (errors and rollbacks) ?
>From 'Inside' :
>ROLLBACK (because of a fatal error or an
>explicit ROLLBACK command), the entire batch is aborted.
>
>As I read this :
>From within a trigger any FK or relational constraint
>violation results in a fatal error aborting the complete
>batch (and complete transaction).
>Is this correct ?
>What if there was no transaction start ?
>In the QA, does the QA supply a transaction
>(increase the transaction count) if no transaction
>was started ?
>When sending several statements, it appears
>that all statements are rolled back. (Are handled
>as one transaction).
>Does something similar happen when doing
>similar statements from application programs ?
>Thanks for your attention,
>ben brugman.
>
>.
>
My email is
stbraslenscap@.lenscaphiscom.nl
(Both lenscaps should be removed).
I tried to reply to your mail, but that bounced,
so now in the thread.
thanks in advance,
ben brugman
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:4ff601c49104$6e437d70$a301280a@.phx.gbl...[vbcol=seagreen]
> If we can get away from the trigger for a second.
> One of the least documented aspects on how transactions
> work are to do with the process id that its running from.
> So if you open up QA do transaction mock statement, open
> up another window in QA and do rollback you will get an
> error, why, becasue the process in the second window is
> different to the first.
> The reason why this is important is that a rollback as you
> found out will effect EVERY transaction in that process
> even though it maybe nested. Try this yourself, you will
> get some very interesting answers.
> So to get back to your question.
> SQL Server will automatically rollback transactions if the
> error is serious enough.
> If there is no transaction then there will be no
> transaction count, there is a handy little global variable
> called @.@.TRANCOUNT you may want to look at. So if that is
> set to anything but a 0 it will automatically rollback the
> transaction irrespective on whether it is done in trigger
> or something else.
> From application programs then it depends on the error. If
> you had a connection object and you application (not SQL)
> failed then it would be up to your application to repair
> the DB by sending it up a rollback.
> If you application using the same connection as the begin
> transaction sent some SQL that caused an error then yes it
> would automatically roll back.
> Anyway that it. I sugest you have a play on QA.
> If you have any questions then don't hesitate to email me
> on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
> busy tonight)
> Peter
> "You can always count on Americans to do the right thing -
> after they've tried everything else."
> Winston Churchill
>
>

Sunday, February 26, 2012

Exception handling in Triggers.

Can anybody point me to a good text about
exception handling in triggers (errors and rollbacks) ?
From 'Inside' :
ROLLBACK (because of a fatal error or an
explicit ROLLBACK command), the entire batch is aborted.
As I read this :
From within a trigger any FK or relational constraint
violation results in a fatal error aborting the complete
batch (and complete transaction).
Is this correct ?
What if there was no transaction start ?
In the QA, does the QA supply a transaction
(increase the transaction count) if no transaction
was started ?
When sending several statements, it appears
that all statements are rolled back. (Are handled
as one transaction).
Does something similar happen when doing
similar statements from application programs ?
Thanks for your attention,
ben brugman."ben brugman" <ben@.niethier.nl> wrote in message
news:%23VPXOAQkEHA.3724@.TK2MSFTNGP11.phx.gbl...
> As I read this :
> From within a trigger any FK or relational constraint
> violation results in a fatal error aborting the complete
> batch (and complete transaction).
> Is this correct ?
Not just a constraint violation; ANY error will abort (and consequently
rollback) the entire batch.
> What if there was no transaction start ?
Every DML operation in SQL Server uses a transaction, even if you don't
explicitly request it.
So:
UPDATE Tbl
SET Val = 0
WHERE SomeCol = 1
will use a transaction. That way, if there are two rows where SomeCol =1, and the first was successful but the second had an error, the entire
batch will roll back to the initial state.
> When sending several statements, it appears
> that all statements are rolled back. (Are handled
> as one transaction).
Do you have IMPLICIT_TRANSACTIONS turned on for your session?
This option causes SQL Server to treat multistatement batches as if they
were wrapped in a transaction. But it's not turned on by default in Query
Analyzer or any connection library that I know of...|||If we can get away from the trigger for a second.
One of the least documented aspects on how transactions
work are to do with the process id that its running from.
So if you open up QA do transaction mock statement, open
up another window in QA and do rollback you will get an
error, why, becasue the process in the second window is
different to the first.
The reason why this is important is that a rollback as you
found out will effect EVERY transaction in that process
even though it maybe nested. Try this yourself, you will
get some very interesting answers.
So to get back to your question.
SQL Server will automatically rollback transactions if the
error is serious enough.
If there is no transaction then there will be no
transaction count, there is a handy little global variable
called @.@.TRANCOUNT you may want to look at. So if that is
set to anything but a 0 it will automatically rollback the
transaction irrespective on whether it is done in trigger
or something else.
From application programs then it depends on the error. If
you had a connection object and you application (not SQL)
failed then it would be up to your application to repair
the DB by sending it up a rollback.
If you application using the same connection as the begin
transaction sent some SQL that caused an error then yes it
would automatically roll back.
Anyway that it. I sugest you have a play on QA.
If you have any questions then don't hesitate to email me
on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
busy tonight)
Peter
"You can always count on Americans to do the right thing -
after they've tried everything else."
Winston Churchill
>--Original Message--
>Can anybody point me to a good text about
>exception handling in triggers (errors and rollbacks) ?
>From 'Inside' :
>ROLLBACK (because of a fatal error or an
>explicit ROLLBACK command), the entire batch is aborted.
>
>As I read this :
>From within a trigger any FK or relational constraint
>violation results in a fatal error aborting the complete
>batch (and complete transaction).
>Is this correct ?
>What if there was no transaction start ?
>In the QA, does the QA supply a transaction
>(increase the transaction count) if no transaction
>was started ?
>When sending several statements, it appears
>that all statements are rolled back. (Are handled
>as one transaction).
>Does something similar happen when doing
>similar statements from application programs ?
>Thanks for your attention,
>ben brugman.
>
>.
>|||My email is
stbraslenscap@.lenscaphiscom.nl
(Both lenscaps should be removed).
I tried to reply to your mail, but that bounced,
so now in the thread.
thanks in advance,
ben brugman
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:4ff601c49104$6e437d70$a301280a@.phx.gbl...
> If we can get away from the trigger for a second.
> One of the least documented aspects on how transactions
> work are to do with the process id that its running from.
> So if you open up QA do transaction mock statement, open
> up another window in QA and do rollback you will get an
> error, why, becasue the process in the second window is
> different to the first.
> The reason why this is important is that a rollback as you
> found out will effect EVERY transaction in that process
> even though it maybe nested. Try this yourself, you will
> get some very interesting answers.
> So to get back to your question.
> SQL Server will automatically rollback transactions if the
> error is serious enough.
> If there is no transaction then there will be no
> transaction count, there is a handy little global variable
> called @.@.TRANCOUNT you may want to look at. So if that is
> set to anything but a 0 it will automatically rollback the
> transaction irrespective on whether it is done in trigger
> or something else.
> From application programs then it depends on the error. If
> you had a connection object and you application (not SQL)
> failed then it would be up to your application to repair
> the DB by sending it up a rollback.
> If you application using the same connection as the begin
> transaction sent some SQL that caused an error then yes it
> would automatically roll back.
> Anyway that it. I sugest you have a play on QA.
> If you have any questions then don't hesitate to email me
> on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
> busy tonight)
> Peter
> "You can always count on Americans to do the right thing -
> after they've tried everything else."
> Winston Churchill
>
>
> >--Original Message--
> >Can anybody point me to a good text about
> >exception handling in triggers (errors and rollbacks) ?
> >
> >From 'Inside' :
> >ROLLBACK (because of a fatal error or an
> >explicit ROLLBACK command), the entire batch is aborted.
> >
> >
> >As I read this :
> >From within a trigger any FK or relational constraint
> >violation results in a fatal error aborting the complete
> >batch (and complete transaction).
> >Is this correct ?
> >
> >What if there was no transaction start ?
> >In the QA, does the QA supply a transaction
> >(increase the transaction count) if no transaction
> >was started ?
> >When sending several statements, it appears
> >that all statements are rolled back. (Are handled
> >as one transaction).
> >Does something similar happen when doing
> >similar statements from application programs ?
> >
> >Thanks for your attention,
> >
> >ben brugman.
> >
> >
> >.
> >

Exception handling in Triggers.

If we can get away from the trigger for a second.
One of the least documented aspects on how transactions
work are to do with the process id that its running from.
So if you open up QA do transaction mock statement, open
up another window in QA and do rollback you will get an
error, why, becasue the process in the second window is
different to the first.
The reason why this is important is that a rollback as you
found out will effect EVERY transaction in that process
even though it maybe nested. Try this yourself, you will
get some very interesting answers.
So to get back to your question.
SQL Server will automatically rollback transactions if the
error is serious enough.
If there is no transaction then there will be no
transaction count, there is a handy little global variable
called @.@.TRANCOUNT you may want to look at. So if that is
set to anything but a 0 it will automatically rollback the
transaction irrespective on whether it is done in trigger
or something else.
From application programs then it depends on the error. If
you had a connection object and you application (not SQL)
failed then it would be up to your application to repair
the DB by sending it up a rollback.
If you application using the same connection as the begin
transaction sent some SQL that caused an error then yes it
would automatically roll back.
Anyway that it. I sugest you have a play on QA.
If you have any questions then don't hesitate to email me
on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
busy tonight)
Peter
"You can always count on Americans to do the right thing -
after they've tried everything else."
Winston Churchill

>--Original Message--
>Can anybody point me to a good text about
>exception handling in triggers (errors and rollbacks) ?
>From 'Inside' :
>ROLLBACK (because of a fatal error or an
>explicit ROLLBACK command), the entire batch is aborted.
>
>As I read this :
>From within a trigger any FK or relational constraint
>violation results in a fatal error aborting the complete
>batch (and complete transaction).
>Is this correct ?
>What if there was no transaction start ?
>In the QA, does the QA supply a transaction
>(increase the transaction count) if no transaction
>was started ?
>When sending several statements, it appears
>that all statements are rolled back. (Are handled
>as one transaction).
>Does something similar happen when doing
>similar statements from application programs ?
>Thanks for your attention,
>ben brugman.
>
>.
>My email is
stbraslenscap@.lenscaphiscom.nl
(Both lenscaps should be removed).
I tried to reply to your mail, but that bounced,
so now in the thread.
thanks in advance,
ben brugman
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:4ff601c49104$6e437d70$a301280a@.phx.gbl...[vbcol=seagreen]
> If we can get away from the trigger for a second.
> One of the least documented aspects on how transactions
> work are to do with the process id that its running from.
> So if you open up QA do transaction mock statement, open
> up another window in QA and do rollback you will get an
> error, why, becasue the process in the second window is
> different to the first.
> The reason why this is important is that a rollback as you
> found out will effect EVERY transaction in that process
> even though it maybe nested. Try this yourself, you will
> get some very interesting answers.
> So to get back to your question.
> SQL Server will automatically rollback transactions if the
> error is serious enough.
> If there is no transaction then there will be no
> transaction count, there is a handy little global variable
> called @.@.TRANCOUNT you may want to look at. So if that is
> set to anything but a 0 it will automatically rollback the
> transaction irrespective on whether it is done in trigger
> or something else.
> From application programs then it depends on the error. If
> you had a connection object and you application (not SQL)
> failed then it would be up to your application to repair
> the DB by sending it up a rollback.
> If you application using the same connection as the begin
> transaction sent some SQL that caused an error then yes it
> would automatically roll back.
> Anyway that it. I sugest you have a play on QA.
> If you have any questions then don't hesitate to email me
> on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
> busy tonight)
> Peter
> "You can always count on Americans to do the right thing -
> after they've tried everything else."
> Winston Churchill
>
>
>

Exception handling in Triggers.

Exception handling in stored procedure in sql server 2000.

Hi all,
I am writing one stored procedure wherein i want to do something like
try-catch.
I am inserting data into one table. So what i want to do is to catch
exception if error occured during insertion.
I want to display generic message if insert query fails.
Can someone tell me how to do this.
Any help will be truely appreciated.
Thanks in advance.Hi
CREATE TABLE Employees
(
empid INT NOT NULL,
empname VARCHAR(25) NOT NULL,
mgrid INT NULL
.....
)
BEGIN TRY
INSERT INTO Employees(empid, empname, mgrid)
VALUES(1, 'Emp1', NULL)
PRINT 'After INSERT.'
END TRY
BEGIN CATCH
PRINT 'INSERT failed.'
END CATCH
<trialproduct2004@.yahoo.com> wrote in message
news:1159425651.145892.88090@.e3g2000cwe.googlegroups.com...
> Hi all,
> I am writing one stored procedure wherein i want to do something like
> try-catch.
> I am inserting data into one table. So what i want to do is to catch
> exception if error occured during insertion.
> I want to display generic message if insert query fails.
> Can someone tell me how to do this.
> Any help will be truely appreciated.
> Thanks in advance.
>|||Uri,
Try..Catch block was introduced in SQL 2005.
--
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message|||Hi!
Start with this excellent article by Erland Sommarskog:
http://www.sommarskog.se/error-handling-I.html.
--
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
<trialproduct2004@.yahoo.com> wrote in message
news:1159425651.145892.88090@.e3g2000cwe.googlegroups.com...
> Hi all,
> I am writing one stored procedure wherein i want to do something like
> try-catch.
> I am inserting data into one table. So what i want to do is to catch
> exception if error occured during insertion.
> I want to display generic message if insert query fails.
> Can someone tell me how to do this.
> Any help will be truely appreciated.
> Thanks in advance.
>|||Dejan
This is a classic example how to important to mention what is the vesrion of
SQL Server
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:uWW2xss4GHA.1200@.TK2MSFTNGP02.phx.gbl...
> Uri,
> Try..Catch block was introduced in SQL 2005.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
>|||An even more classic example of how easy it is to overlook said information.
;-)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23NRJNws4GHA.668@.TK2MSFTNGP02.phx.gbl...
> Dejan
> This is a classic example how to important to mention what is the vesrion
> of SQL Server
>
>
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:uWW2xss4GHA.1200@.TK2MSFTNGP02.phx.gbl...
>> Uri,
>> Try..Catch block was introduced in SQL 2005.
>> --
>> Dejan Sarka, SQL Server MVP
>> Mentor, www.SolidQualityLearning.com
>> Anything written in this message represents solely the point of view of
>> the sender.
>> This message does not imply endorsement from Solid Quality Learning, and
>> it does not represent the point of view of Solid Quality Learning or any
>> other person, company or institution mentioned in this message
>>
>|||Arnie
You are right . :-))) Probably it is because I get in my office on 4AM
today
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O9Uco1s4GHA.1012@.TK2MSFTNGP05.phx.gbl...
> An even more classic example of how easy it is to overlook said
> information.
> ;-)
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23NRJNws4GHA.668@.TK2MSFTNGP02.phx.gbl...
>> Dejan
>> This is a classic example how to important to mention what is the vesrion
>> of SQL Server
>>
>>
>>
>> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
>> in message news:uWW2xss4GHA.1200@.TK2MSFTNGP02.phx.gbl...
>> Uri,
>> Try..Catch block was introduced in SQL 2005.
>> --
>> Dejan Sarka, SQL Server MVP
>> Mentor, www.SolidQualityLearning.com
>> Anything written in this message represents solely the point of view of
>> the sender.
>> This message does not imply endorsement from Solid Quality Learning, and
>> it does not represent the point of view of Solid Quality Learning or any
>> other person, company or institution mentioned in this message
>>
>>
>|||:-)
Hope the rest of the day goes better:-)
--
Dejan Sarka
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:udc9z6s4GHA.3840@.TK2MSFTNGP03.phx.gbl...
> Arnie
> You are right . :-))) Probably it is because I get in my office on 4AM
> today
>
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:O9Uco1s4GHA.1012@.TK2MSFTNGP05.phx.gbl...
>> An even more classic example of how easy it is to overlook said
>> information.
>> ;-)
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23NRJNws4GHA.668@.TK2MSFTNGP02.phx.gbl...
>> Dejan
>> This is a classic example how to important to mention what is the
>> vesrion of SQL Server
>>
>>
>>
>> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
>> in message news:uWW2xss4GHA.1200@.TK2MSFTNGP02.phx.gbl...
>> Uri,
>> Try..Catch block was introduced in SQL 2005.
>> --
>> Dejan Sarka, SQL Server MVP
>> Mentor, www.SolidQualityLearning.com
>> Anything written in this message represents solely the point of view of
>> the sender.
>> This message does not imply endorsement from Solid Quality Learning,
>> and it does not represent the point of view of Solid Quality Learning
>> or any other person, company or institution mentioned in this message
>>
>>
>>
>|||Hi,
thanks for your example.
But what to do if i have sql server 2000. Is is like that i can't
handle exception in stored procedure itself.
Or is there any alternative?
Any help will be truely appreciated.
thanks in advacne.|||Hi,,
Thanks to all for reply.
I have one more query regarding exception handling.
I have stored procedure in which i am opening one cursor and after
getting values from cursor i am inserting data into table. So my
question is if insert fails then sp will not execute next part then how
will i deallocate cursor.
Can someone provide me way to do this.
Any help will be truely appreciated.
Thanks in advance.|||> I have stored procedure in which i am opening one cursor and after
> getting values from cursor i am inserting data into table. So my
> question is if insert fails then sp will not execute next part then how
> will i deallocate cursor.
You can check the @.@.error after the Insetr, and use GOTO statement, e.g.:
INSERT ...
IF @.@.error > 0
BEGIN
ROLLBACK TRAN
GO TO ON_ERROR
END
...
ON_ERROR:
DEALLOCATE ...
--
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message

Exception Handling

Hi All

I m new to the sqlserver.In Oracle we can handle exceptions like this

declare
name varchar(20);
begin
select ename into name from emp where eno=&eno;
dbms_output.put_line(name);
exception
when no_data_found then
dbms_output.put_line('No Entry');
end;
/

We will get the message No Entry When corrsponding employee number dosent exists.

In Sqlserver how to handle these things.

Specifically my requirement is with select statement(Like above program)
Waiting for valuable suggestions

Baba

You can do in sqlserver using @.@.Rowcount

select ename into name from emp where eno=&eno

if((select @.@.Rowcount)<= 0)

print 'No data'

The following explains the errorhandling in simple terms

http://www.sqlteam.com/article/handling-errors-in-stored-procedures

|||

In SQL Server this situation never treated as exception, There is a workaround available - here it is,

Code Snippet

Declare @.Sno as int;

Declare @.name as varchar(100)

Set @.Sno=4

Select @.name=name from sysobjects Where id=@.Sno

If @.@.RowCount = 0

Print 'No Entry'

Else

Print @.Name

|||Hi manivannan

I tried like this before only . I got the output

Thank u for u r valuable reply.

Baba

Exception handling

it gives error while calling a sql stored procedure as "INPUT STRING WAS NOT IN A CORRECT FORMAT". I am providing the code here.

publicvoid get_issid(string cse_email,string tech_email,string subject,string issue_details,string response,string solv_date,outint issid)

{

// Establish Connection

SqlConnection oConnection = GetConnection();

// build the command

SqlCommand oCommand =newSqlCommand("get_issid", oConnection);

oCommand.CommandType =CommandType.StoredProcedure;

// Parameters

SqlParameter paracse_email =newSqlParameter("@.cse_email",SqlDbType.VarChar, 50);

paracse_email.Value =cse_email;

oCommand.Parameters.Add(paracse_email);

SqlParameter paratech_email =newSqlParameter("@.tech_email",SqlDbType.VarChar,50);

paratech_email.Value = cse_email;

oCommand.Parameters.Add(paratech_email);

SqlParameter parasubject =newSqlParameter("@.subject",SqlDbType.VarChar, 50);

parasubject.Value = subject;

oCommand.Parameters.Add(parasubject);

SqlParameter paraissue_details =newSqlParameter("@.issue_details",SqlDbType.VarChar, 500);

paraissue_details.Value = issue_details;

oCommand.Parameters.Add(paraissue_details);

SqlParameter pararesponse =newSqlParameter("@.response",SqlDbType.VarChar, 500);

pararesponse.Value = response;

oCommand.Parameters.Add(pararesponse);

SqlParameter parasolv_date =newSqlParameter("@.solv_date",SqlDbType.DateTime);

parasolv_date.Value = solv_date;

oCommand.Parameters.Add(parasolv_date);

SqlParameter paraissid =newSqlParameter("@.issid",SqlDbType.Int);paraissid.Direction =ParameterDirection.Output;

oCommand.Parameters.Add(paraissid);

try

{

oConnection.Open();

oCommand.ExecuteNonQuery();

issid =int.Parse(paraissid.Value.ToString());

}

catch (Exception oException)

{

throw oException;

}

finally

{

oConnection.Close();

}

the stored procedure is:

create proc [dbo].[get_issid](@.tech_emailvarchar(50), @.cse_emailvarchar(50),@.subjectvarchar(50),@.issue_detailsvarchar(500),@.responsevarchar(500),@.solv_datedatetime, @.issidintoutput)

select @.issid=tech_response.issue_idfrom tech_response,issue_detailswhere tech_response.tech_email=@.tech_emailand tech_response.cse_email=@.cse_emailand tech_response.subject=@.subjectand tech_response.issue_details=@.issue_detailsand response=@.responseand solv_date=@.solv_dateand tech_response.issue_id=issue_details.issue_id

requested to help in this

Use ExecuteScalar or ExecuteReader .

ExecuteNonQuery can be used only on DDL statements such as Insert and Delete statements.

|||

Hi,

Thank u for your reply. but the error is coming again same as earlier.

pls help me in this regard.

your amibly,

nagireddy

Exception Handling

Hello,

I am trying to catch an error, but not able to. I am storing the value of @.@.Error soon after the statement which I believe would generate error. In order to produce the error I have deliberately used a wrong table name. So, the statement breaks but never comes to my error handling code snippet. Instead, it just throws the SQL server error message and quits. What am I doing wrong here? Here is the code snippet. The actual name of the temporary table is #TEMPO_TABLE, but in order to generate the error I have used #TEMPO_. Since this would surely error out, it should go to the label ROLLITBACK. But is not going to that label. Neither is it printing the error number as per the PRINT statement there. It just throws the SQL error and quits the execution. (Or should it NOT?). The Error that it throws it this:

Server: Msg 208, Level 16, State 1, Procedure FEED_PULL_XX, Line 157
Invalid object name '#TEMPO_'.

Can you please let me know whats going wrong here? Thanks a lot in advance.

INSERT INTO Table1
(
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
)
SELECT
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
FROM
#TEMPO_

SET @.ErrNo = @.@.Error

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

IF @.ErrNo <> 0
Begin
PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'
GOTO ROLLITBACK
End

Hi Mannubhai,

Which version of SQL server you are using.. if you are using SQL05, try using Try..catch block.

If you are using SQL2k.. then in above script there is no variable declared and there is no rollitback lable..

And YOU SHOULD GET THE ERROR SAYING THAT THE TABLE DOES NOT EXISTS.. AND THIS ERROR HAS THE SEVERITY LEVEL OF 16.. which says that the error can be fixed by the user and terminates the batch.. so you are not able to catch it.

Have a look on the severity levels for more details.

Regards,

|||

There are errors that cannot be caught.

Erland has written up some good articles on the subject. Please take a look.

http://www.sommarskog.se/error-handling-I.html

http://www.sommarskog.se/error-handling-II.html

|||

Hey Thanks Sajid. I am using SQL 2000. The Code that I have given is just a snippet. It is not the complete one. The Complete SP has the variables declared and yes, the ROLLITBACK label is also present. The error as I said is, INVALID OBJECT #TEMPO_. But can this error NOT be caught in the way I have attempted? I think it does not even execute this statement --> SET @.ErrNo = @.@.Error. Why does this happen? Please let me know. Appreciate the response.

Mannu.

|||

You can refer to Erland's documents on Error handline.. spcially the first link provided by OJ. .that will surely help you out to understand the Error handling in details..

Regards,

|||

Hi All,

I seem to have solved this. The below insert which I was using, I put that into a variable and executed it using sp_executesql. A Dynamic SQL.

I put this like this:

SET @.SQL = '

EXEC sp_Executesql @.SQL

SET @.ErrNo = @.@.Error.

IF ErrNo <> 0

....

This gave the proper results. Let me know if more information is required.

Mannu.

|||

I partially agree with your solution.

But there is few risks you might face if you given the access permissions on SP level (readonly at Table)..

The alternate possible solution might be,

Option 1#:

Code Snippet

If Exists (Select * from tempdb..sysobjects where id=object_id('tempdb..#tempo_'))

Insert into Table1

(

[itam_asset_eup_bus_line],

[itam_machine_status],

[itam_owner_id],

[itam_asset_admin],

[itam_model],

[itam_asset_name],

[itammachine_purpose]

)

Select

[itam_asset_eup_bus_line],

[itam_machine_status],

[itam_owner_id],

[itam_asset_admin],

[itam_model],

[itam_asset_name],

[itammachine_purpose]

From

#tempo_

Else

Goto RollITBack

SET @.ErrNo = @.@.Error

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

IF @.ErrNo <> 0

Begin

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

Goto RollItback

End

Option 2#:

Wrapp the insert statement in new proc & call it from your main proc.

Code Snippet

Create Proc InsertFromTemp

Insert into Table1

(

[itam_asset_eup_bus_line],

[itam_machine_status],

[itam_owner_id],

[itam_asset_admin],

[itam_model],

[itam_asset_name],

[itammachine_purpose]

)

Select

[itam_asset_eup_bus_line],

[itam_machine_status],

[itam_owner_id],

[itam_asset_admin],

[itam_model],

[itam_asset_name],

[itammachine_purpose]

From

#tempo_

Exec InsertFromTemp

SET @.ErrNo = @.@.Error

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

IF @.ErrNo <> 0

Begin

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

Goto RollItback

End

Monday, March 26, 2012

EXEC and Error Handling

Friday, March 9, 2012

ExceptionHandling in T-SQL?

if (@.@.error <> 0)

goto error_handler

return (0)

error_handler:

if (@.@.trancount > 0)

rollback tran

return (-1)

Wednesday, March 7, 2012

Exception handling with full text search

Exception handling in Triggers.

Sunday, February 26, 2012

Exception handling in Triggers.

Exception handling in Triggers.

Exception handling in Triggers.

Exception handling in Triggers.

Exception handling in stored procedure in sql server 2000.

Exception handling in stored procedure in sql server 2000.

Exception handling in stored procedure in sql server 2000.

Exception Handling

Exception handling

Exception Handling

Excel Import

Blog Archive

About Me