Know if a stored proc has failed 1

[micang]

SQL 2008

Hi All,

I am calling a SQL stored procedure (from a Access db), is there a way in the SQL database to know if the stored procedure has executed successfully?

Any info appreciated.

Michael

 

[markros]

Can you show your code? Basically, you can add return - 1 (or other negative value) inside the SP for error cases.

Also, what version of SQL Server you're using? In SQL Server 2005 and up you can use TRY/CATCH inside the SP.

See

http://kevin_s_goff.typepad.com/kevin_s_goff_weblog/2008/03/3172008-touchpe.html

 

[micang]

Hi markos,

Thank you for the reply.

I am using SQL 2008 and Access 2007

For now I have a simple testing procedure that I am calling from Access:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[zzz_test]

@test1 VARCHAR(255), 
@test2 VARCHAR(255)
as


update dbo.Test_customer
set first_name = 'Jim'
where city = @test1 and country = @test2

What I am trying to figure out is when I call it from Access, is there a way know if the procedure has executed successfully?

Many thanks

Michael

 

[markros]

I'm not sure how to call SQL SPs from Access - haven't worked with Access for long time.

Anyway, you may add to your SP

declare @ErrorCode int, @RowCount int

Update  ...

select @RowCount = @@ROWCOUNT, @ErrorCode = @@ERROR

if @ErrorCode <> 0
  Raserror('Error %d has occurred', 16, 1, @ErrorCode)

else
  if @RowCount = 0
    Raiserror('No records were updated for %s',16,1,@Test1)

return @ErrorCode

See also

http://msdn.microsoft.com/en-us/library/aa933181(SQL.80).aspx

 

[micang]

Excellent, thanks marko, I can make use of that.

Many thanks for your time.

Michael