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 retrieve current date and time from SQL2005 server.

well i'm using java to connect to SQL 2005 server. i need to retrieve it's current time and date on the sql 2005 server and use it on my remote desktop

thanks.

Hi,

You can use the following query :

SELECT getdate() as myDate

wich will give you somthing like "2006-10-27 11:29:15.373"

Regards

how to retrieve a password saved by SQL server

Hi,

I am able to connect to a SQL server and have checked the "remember my password" box in the connection diaglog window. Unfortunately, I forget what the password is, and now have to use it elsewhere. How can I get it? (I tried copy and paste, but it won't work).

Thanks,

Hi,

Unfortunatly I am beginer in this but it happens for me to have a corrupted master database and I couldn't login to SQL. The solution it was that I restore the master database using the command: rebuildm.exe from the SQL CD.

If I would have a problem like you, I would make a screen shots of all the settings which I have, I will detache the database I will stop the SQL service and I will restore the master database and I will put a new password and redo all the settings.

Good luck,

Diana

|||

Diana, thanks a lot for reply. I am still hoping there might a way to extract the password from my computer (since i have no trouble connecting to the server). Does anybody have any idea?

Thanks,

June

|||MSSQL stores not passwords but password hashes. There is no way to retrieve clear password from its hash: the hash operation is irreversible.