How to ROLLBACK TRANSACTION on client level

Suppose i connect to db (SQL Server) from the client (.NET) and call to
some store procedure, in this sp i start transaction (BEGIN TRANSACTION)
and before COMMIT or RALLBACK an error happen that imidietly stop the
execution of stored procedure, In the client i cacth this error but what
about an open transaction ?
HOW to rollback in the client?
Message posted via http://www.webservertalk.comYou can wrap your sp inside another one, get @.@.trancount before calling
second sp and compare after the call.
create procedure dbo.proc1
@.p1 int,
@.p2 datetime
as
set nocount on
declare @.error int
begin transaction
insert into t1 values(@.p1, @.p2)
set @.error = @.@.error
if @.error != 0
begin
rollback transaction
raiserror('whatever 1.', 16, 1)
return 1
end
insert into t2 values(@.p1)
set @.error = @.@.error
if @.error != 0
begin
rollback transaction
raiserror('whatever 2.', 16, 1)
return 1
end
else
commit transaction
return @.@.error
go
create procedure dbo.proc2
@.p1 int,
@.p2 datetime
as
set nocount on
declare @.tc int
declare @.rv int
declare @.error int
set @.tc = @.@.transcount
exec @.rv = dbo.proc1 @.p1, @.p2
set @.error = coalesce(nullif(@.rv, 0), @.@.error)
if @.tc != @.@.trancount
rollback transaction
return @.error
go
Call proc2 from your client app, instead calling proc1.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"E B via webservertalk.com" wrote:

> Suppose i connect to db (SQL Server) from the client (.NET) and call to
> some store procedure, in this sp i start transaction (BEGIN TRANSACTION)
> and before COMMIT or RALLBACK an error happen that imidietly stop the
> execution of stored procedure, In the client i cacth this error but what
> about an open transaction ?
> HOW to rollback in the client?
> --
> Message posted via http://www.webservertalk.com
>|||thanks. However i find somthing more intresting, in my app i'm using
ADO.NET so when i close a connection (conection to db) with ADO.NET method
close() it rolls back any pending transactions.
Message posted via http://www.webservertalk.com

How to return a varchar from sproc on remote Linked Server?

The following T-SQL 2005 snippet executes a remote sproc on an Oracle database.

DECLARE@.OutParamVARCHAR(5)

EXEC('{Call getnextpin(1)}')ATALTTEST

GetNextPin takes a single input parameter.

I need to modify the above code snippet to return the next PIN number into @.OutParam.

Any ideas?

Any help appreciated.

Regards,

Paul.

Hi wadep,

Try the following code and see if it solves your problem ( i suppose your table has a primary key named "number_index" and your table is ordered well. If not, you need to use cursor which will be a little complicated)

create procedure getnextpin@.indexint,@.numberint outputasselect top 1 @.number=pin_number from your_table_namewhere number_index>@.indexreturn

-------- call proceduredeclare @.indexint, @.numberintset @.index=whatever_you_wantexecute getnextpin @.index,@.number outputprint @.number -- see if your number can be printed here

Hope my suggestion helps

How to return a value from SP

Hi all,

How to return a value from a store procedure?

I use a VBA to call a store procedure, but I would like to be able to return the result back to a variable.

Here is an VBA example:

Dim GetDestinationID As Long

Dim conConnection As ADODB.Connection
Dim StrSQL As String

Set conConnection = CurrentProject.Connection

StrSQL = "usp_GetDestinationID " & 2 & ", " & _
GetDestinationID

conConnection.Execute StrSQL, iAffected, adExecuteNoRecords

I would like to return GetDestinationID.

Here is the SP:

CREATE PROCEDURE [dbo].[usp_GetDestinationID]

(
@.intOrderID int,
@.intDestinationID int=0 OUTPUT

)

AS

BEGIN

set @.intDestinationID=(SELECT lv.DestinationID
FROM [Land Voyages] AS lv INNER JOIN [Pickup Booking List] AS pbl
ON lv.LandVoyageID=pbl.LandVoyageID
WHERE pbl.OrderID = @.intOrderID)

END
GO

What is wrong? Can I return a value to a Visual Basic Application from a Store Procedure?

Regardshttp://dbforums.com/t916861.html|||HI all

I found a solution to my question about VBA calling a Stored Procedure and returning a value

VBA:
'-------------------
Private Function GetDestinationID(OrderID As Long) As Long
On Error GoTo GetDestinationID_Err

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd

.ActiveConnection = CurrentProject.Connection
.CommandText = "usp_GetDestinationID"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@.intOrderID", adInteger, adParamInput, , OrderID)
.Parameters.Append .CreateParameter("@.intDestinationID", adInteger, adParamOutput)
.Execute
GetDestinationID = .Parameters("@.intDestinationID").Value
End With

WrapUp:


Exit_GetDestinationID:
Set cmd = Nothing
Exit Function

GetDestinationID_Err:
Call LogMsgError(Err.Description, Err.Number, ModuleName$, "GetDestinationID")
Resume Exit_GetDestinationID
End Function

'----------------
T-SQL:

CREATE PROCEDURE dbo.usp_GetDestinationID

(
@.intOrderID int,
@.intDestinationID int=0 OUTPUT
)

AS

SET NOCOUNT ON

BEGIN

SELECT @.intDestinationID=lv.DestinationID
FROM dbo.[Land Voyages] AS lv INNER JOIN dbo.[Pickup Booking List] AS pbl
ON lv.LandVoyageID=pbl.LandVoyageID
WHERE pbl.OrderID = @.intOrderID

END
GO
'-----------

Thanks to Igor for suggestions.

Dani