Can we handle ALL errors within a stored proceudre?

Hello, friends,
We call stored procedures from our app (asp.net), and use Try...Catch to
handle any possible DB error. But, can we handle all errors in a stored
proceudre? In another word, all DB errors should be caught within a stored
procedure, and return back to callers gracefully, as if nothing wrong.
We tried IF @.@.ERROR > 0 in sp, but errors such as Unique Constraint
Violation were still caught by our app, not sp.
Any ideas, reference papers, sample source code?
Thanks a lot.http://www.sommarskog.se/error-handling-II.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:59F7E8E6-1477-4A42-9B42-BB34D0136102@.microsoft.com...
> Hello, friends,
> We call stored procedures from our app (asp.net), and use Try...Catch to
> handle any possible DB error. But, can we handle all errors in a stored
> proceudre? In another word, all DB errors should be caught within a stored
> procedure, and return back to callers gracefully, as if nothing wrong.
> We tried IF @.@.ERROR > 0 in sp, but errors such as Unique Constraint
> Violation were still caught by our app, not sp.
> Any ideas, reference papers, sample source code?
> Thanks a lot.

Can we handle all errors within a stored proceudre.

Hello, friends,
We are calling stored procedures (sp) from our web app (asp.net). Since
using Try...Catch... in asp.net with C# or VB.net is very expensive, we are
considering to handle all database error in sp so that web app won't see any
DB error.
Do we have a way to catch all erros in sp and return back to callers
gracefully, as if nothing wrong in DB?
We tried to check IF @.@.ERROR > 0, but web app still get erros, such as
Unique contraint violation, etc.
Any ideas, reference papers, sample source code?
Thanks a lotexamnotes <Andrew@.discussions.microsoft.com> wrote in
news:0F041009-08FF-4FF1-8AC7-D74968D16C05@.microsoft.com:

> We are calling stored procedures (sp) from our web app (asp.net).
> Since using Try...Catch... in asp.net with C# or VB.net is very
> expensive, we are considering to handle all database error in sp so
> that web app won't see any DB error.
> Do we have a way to catch all erros in sp and return back to callers
> gracefully, as if nothing wrong in DB?
> We tried to check IF @.@.ERROR > 0, but web app still get erros, such as
> Unique contraint violation, etc.
> Any ideas, reference papers, sample source code?
In SQL Server 2005 you can, I've used the following code sample to
demonstrate it in my classes (Using the pubs database):
set XACT_ABORT on
begin try
begin transaction
declare @.id int;
insert into jobs values ('Testjobb',10,10)
set @.id = @.@.identity;
insert into employee values ('123456789','Gates',null,'Bill',@.id+
1,10,'0877',GetDate());
commit transaction
end try
begin catch
select error_message();
rollback transaction
end catch
I'll leave it up to you to implement this in a stored procedure.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||What about SQL Server 2000? That is the one we are using.
"Ole Kristian Bang?s" wrote:

> examnotes <Andrew@.discussions.microsoft.com> wrote in
> news:0F041009-08FF-4FF1-8AC7-D74968D16C05@.microsoft.com:
>
> In SQL Server 2005 you can, I've used the following code sample to
> demonstrate it in my classes (Using the pubs database):
> set XACT_ABORT on
> begin try
> begin transaction
> declare @.id int;
> insert into jobs values ('Testjobb',10,10)
> set @.id = @.@.identity;
> insert into employee values ('123456789','Gates',null,'Bill',@.id+
> 1,10,'0877',GetDate());
> commit transaction
> end try
> begin catch
> select error_message();
> rollback transaction
> end catch
> I'll leave it up to you to implement this in a stored procedure.
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>|||examnotes <Andrew@.discussions.microsoft.com> wrote in
news:C25DD758-622D-4CB5-A870-F112B1C337C6@.microsoft.com:

> What about SQL Server 2000? That is the one we are using.
Sorry. This feature was introduced in SQL Server 2005. In SQL Server 2000
you have to check for errors after each and every command that may cause an
error (which may happen to be almost everyone)
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messagingsql

Can we directly execute a text file that contains all sql commands

Hi, friends,
We have our stored procedures (sp) saved in a .sql file, one for each. When
we want to create an sp, we open the corresponding .sql file using notepad o
r
sql query analyzer, and then execute it.
Do we have a way that we just need to pass those .sql files' name, including
full path, and run all those sp creation sql code in a batch, without
openning each .sql file one by one?
Thanks a lot for your help.Yes, use osql.
exec master..xp_cmdshell 'osql ...'
You can look up the syntax for osql in Books Online...
A
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:917F3B91-8A8E-454D-8782-A76CF5D59B4A@.microsoft.com...
> Hi, friends,
> We have our stored procedures (sp) saved in a .sql file, one for each.
> When
> we want to create an sp, we open the corresponding .sql file using notepad
> or
> sql query analyzer, and then execute it.
> Do we have a way that we just need to pass those .sql files' name,
> including
> full path, and run all those sp creation sql code in a batch, without
> openning each .sql file one by one?
> Thanks a lot for your help.|||You can use the command line tools for that. Look up 'osql' in BOL.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:917F3B91-8A8E-454D-8782-A76CF5D59B4A@.microsoft.com...
> Hi, friends,
> We have our stored procedures (sp) saved in a .sql file, one for each.
> When
> we want to create an sp, we open the corresponding .sql file using notepad
> or
> sql query analyzer, and then execute it.
> Do we have a way that we just need to pass those .sql files' name,
> including
> full path, and run all those sp creation sql code in a batch, without
> openning each .sql file one by one?
> Thanks a lot for your help.

Can We assign DataSet object to Object Type

Hi Friends,

I am having a Package scope variable DS of type system.object .In the Script Component which accept this variable as ReadWrite type I am Making the following assignment.

Public Overrides Sub PostExecute()

Variables.DsReport = DsReport // DsReport is a Data Set Object Created and Populated inside the script Component

End Sub

I am getting error while doing this sort of assignment.

Please help me to solve this problem

Regards,

Mahe

Which error do you get?

-Tom

Can u please check this queary?

Dear Friends
Hi!!!
I have written this query having a few join
its showing "syntax error in FROM clause" when i am trying to execute
it
If u can help me i would be very Glad
Here is my code
SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
dbo_Test_Components.Test_Name
FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
(LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID = Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID = Test_Comp_Prop_Map.Property_ID
LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID = dbo_Test_Components.Test_Component_ID
WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
Thanks in advance
TakeCare
Love
Amitdev.amit
At first glance (utested)
SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
dbo_Test_Components.Test_Name
FROM
(
SELECT * FROM LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID
) AS LV_CBAY_TEST_PROPERTY
LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =Test_Comp_Prop_Map.Property_ID
LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =dbo_Test_Components.Test_Component_ID
WHERE LV_CBAY_TEST_PROPERTY.TEMPLATEID = txt_template_code. value
"dev.amit" <agrawal.solutions@.gmail.com> wrote in message
news:1143113410.483444.240350@.i40g2000cwc.googlegroups.com...
> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID => Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID => Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID => dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>|||Are you sure about this namee : dbo_Test_Components ?
must be perhaps : dbo.Test_Components
and where does " txt_template_code.valuedev.amit " come from ?
SELECT TST.TESTID,
TST.PROPERTYID,
TCP.Test_Name
FROM LV_CBAY_TEMPLATE_PROPERTY AS TMP
INNER JOIN LV_CBAY_TEST_PROPERTY AS TST
ON TST.PROPERTYID = TMPLATE_PROPERTY.PROPERTYID
AND TST.TESTID = TMP.TESTID
LEFT OUTER JOIN Test_Comp_Prop_Map AS CPM
ON TST.TESTID = CPM.Test_ID
AND TST.PROPERTYID = CPM.Property_ID
LEFT OUTER JOIN dbo_Test_Components AS TCP
ON CPM.TestComp_ID = TCP.Test_Component_ID
WHERE TMP.TEMPLATEID = txt_template_code.valuedev.amit
A +
> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID => Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID => Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID => dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||There is an open ( bracket at this line.
(LEFT JOIN Test_Comp_Prop_Map.
Either the query is incomplete or it needs restructing.
"dev.amit" wrote:
> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID => Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID => Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID => dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>sql

can ommiting log file speed up me and how

Dear friends,
I have a database with simple stucture and tasks but big data sizes (23M
rec, 35G data file).
Most of my data are static and I dn't need transaction facilities. so I set
it to simple mode.
The log file is created and has good size. How can I get ride of it to have
more simplified and
more speed operations.
Thanks
You cannot do as you ask. Every SQL Server database has a transaction log
and DML statements get written there FIRST before being written to the
actual data file and you CANNOT disable this functionality.
If you have a large amount of read-only data, consider putting said data on
read-only filegroups (or make the entire database read only as a database
option). This will definitely help performance, especially the
database-level setting.
TheSQLGuru
President
Indicium Resources, Inc.
"Tarvirdi" <mail@.tarvirdi.com> wrote in message
news:eXabaVnfHHA.5044@.TK2MSFTNGP05.phx.gbl...
> Dear friends,
> I have a database with simple stucture and tasks but big data sizes (23M
> rec, 35G data file).
> Most of my data are static and I dn't need transaction facilities. so I
> set it to simple mode.
> The log file is created and has good size. How can I get ride of it to
> have more simplified and
> more speed operations.
> Thanks
>
|||Tarvirdi,
Set the database logging to simple and shrink it. I would consider 35GB to
be a medium size database. 500GB drives are in the $100 range according to
pricewatch.com.
Luke
"Tarvirdi" <mail@.tarvirdi.com> wrote in message
news:eXabaVnfHHA.5044@.TK2MSFTNGP05.phx.gbl...
> Dear friends,
> I have a database with simple stucture and tasks but big data sizes (23M
> rec, 35G data file).
> Most of my data are static and I dn't need transaction facilities. so I
> set it to simple mode.
> The log file is created and has good size. How can I get ride of it to
> have more simplified and
> more speed operations.
> Thanks
>

can ommiting log file speed up me and how

Dear friends,
I have a database with simple stucture and tasks but big data sizes (23M
rec, 35G data file).
Most of my data are static and I dn't need transaction facilities. so I set
it to simple mode.
The log file is created and has good size. How can I get ride of it to have
more simplified and
more speed operations.
ThanksYou cannot do as you ask. Every SQL Server database has a transaction log
and DML statements get written there FIRST before being written to the
actual data file and you CANNOT disable this functionality.
If you have a large amount of read-only data, consider putting said data on
read-only filegroups (or make the entire database read only as a database
option). This will definitely help performance, especially the
database-level setting.
TheSQLGuru
President
Indicium Resources, Inc.
"Tarvirdi" <mail@.tarvirdi.com> wrote in message
news:eXabaVnfHHA.5044@.TK2MSFTNGP05.phx.gbl...
> Dear friends,
> I have a database with simple stucture and tasks but big data sizes (23M
> rec, 35G data file).
> Most of my data are static and I dn't need transaction facilities. so I
> set it to simple mode.
> The log file is created and has good size. How can I get ride of it to
> have more simplified and
> more speed operations.
> Thanks
>|||Tarvirdi,
Set the database logging to simple and shrink it. I would consider 35GB to
be a medium size database. 500GB drives are in the $100 range according to
pricewatch.com.
Luke
"Tarvirdi" <mail@.tarvirdi.com> wrote in message
news:eXabaVnfHHA.5044@.TK2MSFTNGP05.phx.gbl...
> Dear friends,
> I have a database with simple stucture and tasks but big data sizes (23M
> rec, 35G data file).
> Most of my data are static and I dn't need transaction facilities. so I
> set it to simple mode.
> The log file is created and has good size. How can I get ride of it to
> have more simplified and
> more speed operations.
> Thanks
>