Handling error level 16

Hello there
I have some store procedure that run on many views by cursor. One of the
views is failed on error level 16. And therefore the batch is being
terminated.
Is there a way not to terminate the procedure and continue?Roy Goldhammer (roy@.hotmail.com) writes:
>Date: Thu, 20 Apr 2006 10:04:29 +0200
Hey, I am in that time zone, and my watch is only 9:30!

> I have some store procedure that run on many views by cursor. One of the
> views is failed on error level 16. And therefore the batch is being
> terminated.
> Is there a way not to terminate the procedure and continue?
If you are on SQL 2000, you are basically out of luck. Some errors in SQL
Server terminates the batch, and there is no easy way to handle it.
If you are on SQL 2005, you can use the new TRY-CATCH construct.
If your aim is to run sp_refreshview on these views, search on Google
for FMTONLY + sp_refreshview. I saw a tip on that recently, but I don't
recall exactly in which newsgroup.
For more information about error handling in SQL Server, see
http://www.sommarskog.se/error-handling-I.html
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Whell Erland.
I'm using sql server 2000.
The main error that comes is error converting.
and it is level 16 and therefore the statement is being terminated
is there something i can do to solve it?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97AB61A39F364Yazorman@.127.0.0.1...
> Roy Goldhammer (roy@.hotmail.com) writes:
> Hey, I am in that time zone, and my watch is only 9:30!
>
> If you are on SQL 2000, you are basically out of luck. Some errors in SQL
> Server terminates the batch, and there is no easy way to handle it.
> If you are on SQL 2005, you can use the new TRY-CATCH construct.
> If your aim is to run sp_refreshview on these views, search on Google
> for FMTONLY + sp_refreshview. I saw a tip on that recently, but I don't
> recall exactly in which newsgroup.
> For more information about error handling in SQL Server, see
> http://www.sommarskog.se/error-handling-I.html
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Roy
What does the script do? Do you convert dates?
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OI5duAFZGHA.4248@.TK2MSFTNGP05.phx.gbl...
> Whell Erland.
> I'm using sql server 2000.
> The main error that comes is error converting.
> and it is level 16 and therefore the statement is being terminated
> is there something i can do to solve it?
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns97AB61A39F364Yazorman@.127.0.0.1...
>|||Roy Goldhammer (roy@.hotmail.com) writes:
> I'm using sql server 2000.
> The main error that comes is error converting.
> and it is level 16 and therefore the statement is being terminated
> is there something i can do to solve it?
The level does not matter. Error handling in SQL Server is inconsistent.
In my previous post I suggested a search, and gave a link to that article.
Rather than asking again, without telling what you are doing, why not try
the references you got?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Handling Dataset in Stored Proc.

Hi,

I want to create and populate a dataset from store procedure with following to querires & return the dataset as a result.

Select * from billmain where billno = 12

Select * from billdetails where billno = 12

I am currently performing this task aa a resultset. Now I want to use Dataset. Anybody can send me sample sp which returns dataset as a execution of the sp.

Nilkanth Desai

A simple select statement would do. Call the stored procedure from your ADO.NET code and store the results of this stored procedure in a DataSet object.

CREATE PROCEDURE SelectTable @.billNo int

AS

SELECT columnName FROM Table WHERE billno=@.billNo

|||

Hi,

Thanks fpr your Reply. This is working fine when I call it from my ADO.NET Code. But If I want to trf. Dataset as a return result of the said stored proc. where I will be doing multiple select in more then one table. So if I call it only once I can complete all task in a single call. In addition I want to marshal the job to server. So, In this case now tell me what should I do.I don't want to use CLR-Integrated stored procedure. In this case can How can I handle Dataset in above mentioned manner in a standard T-SQL stored procedure?

Nilkanth Desai

|||

You can return more than one SQL Server resultsets from a single stored procedure, see the example below.

Chris

CREATE PROCEDURE SelectTable @.billNo int

AS

SELECT columnName

FROM BillMain WHERE billno=@.billNo

SELECT columnName

FROM BillDetails WHERE billno=@.billNo

GO

handle store procedure return 2 table

I have a sp that will do two select from two table. now, can datareader read both table or only dataset can? if datareader can? how to handle it?In .NET 2.0 you can return multiple datasets with a datareader. You use the NextResult() method to get the next set. Seehere.