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 SQL error code from the Error object?

Hello!
I'm using MS SQL 2000 as a server and Delphi 7 over ADO as a client. On the
server side i have created stored procedure that can raise an error with
some SQL error code like 547 or 550 and i want to catch this code on the
client side to log and to call other procedure, but Error object does not
pass this code, there are only OLE DB error code (same for different errors)
and Description, but NativeError and SQLState fields is empty. How can i
recieve SQL error code over Error object? Thanks!
Best regards, Konstantin KnyazevKostya
There are some errors in SQL Server that terminate batch immediately after
raising like Violation of PRIMARY KEY constraint. In your case I'd try to do
that on the server side
CREATE PROC myProc
AS
--Do something
IF @.@.ERROR <>0
RETURN -1
ELSE
RETURN 0
DECLARE @.err INT
EXEC @.err=myProc
IF @.err =-1
PRINT 'I have got an error'
"Konstantin Knyazev" <kknyazev_no_spam_@.mail.ru> wrote in message
news:O7pl8EvLFHA.1144@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I'm using MS SQL 2000 as a server and Delphi 7 over ADO as a client. On
the
> server side i have created stored procedure that can raise an error with
> some SQL error code like 547 or 550 and i want to catch this code on the
> client side to log and to call other procedure, but Error object does not
> pass this code, there are only OLE DB error code (same for different
errors)
> and Description, but NativeError and SQLState fields is empty. How can i
> recieve SQL error code over Error object? Thanks!
> Best regards, Konstantin Knyazev
>|||Hi
You can cycle throught the errors in the connection object such as:
procedure TForm1.Button1Click(Sender: TObject);
var
i:integer;
begin
try
form2.show;
sp.ExecProc;
except
on e: Eaccessviolation do
showmessage(e.message)
else
if cnn.errors.Count <> 0 then
begin
for i:=0 to cnn.errors.count-1 do
showmessage(cnn.errors[i].desc_ription);
end;
end;
end;
from the thread http://tinyurl.com/4trhq
John
"Konstantin Knyazev" wrote:

> Hello!
> I'm using MS SQL 2000 as a server and Delphi 7 over ADO as a client. On th
e
> server side i have created stored procedure that can raise an error with
> some SQL error code like 547 or 550 and i want to catch this code on the
> client side to log and to call other procedure, but Error object does not
> pass this code, there are only OLE DB error code (same for different error
s)
> and Description, but NativeError and SQLState fields is empty. How can i
> recieve SQL error code over Error object? Thanks!
> Best regards, Konstantin Knyazev
>
>|||Hi!
In my case, Connection.Errors collection is empty, probably due a
asynchronious call, there are only Error object passed in the parameters of
the ExecuteComplete method.
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:87F6042E-5AFD-4118-A7F9-E7FBB0C8EB4C@.microsoft.com...
> Hi
> You can cycle throught the errors in the connection object such as:
> procedure TForm1.Button1Click(Sender: TObject);
> var
> i:integer;
> begin
> try
> form2.show;
> sp.ExecProc;
> except
> on e: Eaccessviolation do
> showmessage(e.message)
> else
> if cnn.errors.Count <> 0 then
> begin
> for i:=0 to cnn.errors.count-1 do
> showmessage(cnn.errors[i].desc-ription);
> end;
>
> end;
> end;
> from the thread http://tinyurl.com/4trhq
> John
> "Konstantin Knyazev" wrote:
>
the
not
errors)|||It was a while since I did something similar, but I can recall having to use
error values of at least a certain number, otherwise SQL Server supresses
them, you would need to check the documentation to confirm this..
"Konstantin Knyazev" wrote:

> Hello!
> I'm using MS SQL 2000 as a server and Delphi 7 over ADO as a client. On th
e
> server side i have created stored procedure that can raise an error with
> some SQL error code like 547 or 550 and i want to catch this code on the
> client side to log and to call other procedure, but Error object does not
> pass this code, there are only OLE DB error code (same for different error
s)
> and Description, but NativeError and SQLState fields is empty. How can i
> recieve SQL error code over Error object? Thanks!
> Best regards, Konstantin Knyazev
>
>|||Hello!
Yes, i did it, but further i have changed system to use linked servers and
distributed query that requires XACT_ABORT ON, but your method requires
XACT_ABORT OFF.
Best regards, Konstantin Knyazev
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23BQSdu3LFHA.2988@.TK2MSFTNGP14.phx.gbl...
> Kostya
> There are some errors in SQL Server that terminate batch immediately
after
> raising like Violation of PRIMARY KEY constraint. In your case I'd try to
do
> that on the server side
> CREATE PROC myProc
> AS
> --Do something
> IF @.@.ERROR <>0
> RETURN -1
> ELSE
> RETURN 0
> --
> DECLARE @.err INT
> EXEC @.err=myProc
> IF @.err =-1
> PRINT 'I have got an error'
>
>
>
>
>
>
> "Konstantin Knyazev" <kknyazev_no_spam_@.mail.ru> wrote in message
> news:O7pl8EvLFHA.1144@.TK2MSFTNGP09.phx.gbl...
> the
not
> errors)
>|||Errors that i'm trying to catch is 547 and 550. Sorry, can you give me a
link to article? I can't find anything :(
Best regards, Konstantin Knyazev
"Mike (adoanywhere)" <Mikeadoanywhere@.discussions.microsoft.com> wrote in
message news:B2304F57-46C6-4033-AB7D-639E89994413@.microsoft.com...
> It was a while since I did something similar, but I can recall having to
use
> error values of at least a certain number, otherwise SQL Server supresses
> them, you would need to check the documentation to confirm this..
> "Konstantin Knyazev" wrote:
>
the
not
errors)|||For my problem I was using RAISERROR in SQL Server Stored Proc
[http://msdn.microsoft.com/library/d...r />
_5ooi.asp]
Try modifying the paramters until you get a reply back to the client. Since
I dont know exactly what you are doing I cant be more specific. But in my
case I wrote a small app to test these paramters until I got a result.
"Konstantin Knyazev" wrote:

> Errors that i'm trying to catch is 547 and 550. Sorry, can you give me a
> link to article? I can't find anything :(
> Best regards, Konstantin Knyazev
> "Mike (adoanywhere)" <Mikeadoanywhere@.discussions.microsoft.com> wrote in
> message news:B2304F57-46C6-4033-AB7D-639E89994413@.microsoft.com...
> use
> the
> not
> errors)
>
>

How to restore Sql Server database?

Hi，
I have a backup file in the database server,and I want to restore database using the backup in client.What should I do?

Thanks for any word.

Search Books Online for "RESTORE DATABASE" You can do the restore by sending a SQL Command in ADO.NET.
RESTORE DATABASE {database_name |@.database_name_var}
[ FROM < backup_device > [,...n] ]
[ WITH
[ RESTRICTED_USER ]
[ [,] FILE= {file_number |@.file_number} ]
[ [,] PASSWORD ={password|@.password_variable} ]
[ [,] MEDIANAME ={media_name|@.media_name_variable} ]
[ [,] MEDIAPASSWORD ={mediapassword|@.mediapassword_variable} ]
[ [,] MOVE'logical_file_name' TO'operating_system_file_name']
[,...n]
[ [,] KEEP_REPLICATION ]
[ [,] { NORECOVERY | RECOVERY | STANDBY=undo_file_name} ]
[ [,] { NOREWIND | REWIND } ]
[ [,] { NOUNLOAD | UNLOAD } ]
[ [,] REPLACE ]
[ [,] RESTART ]
[ [,] STATS [=percentage] ]
]