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 restrict editing of SQL Server 2005 data via ODBC link?

I have a sql server 2005 database with Delphi 2006 in the front end and for querrying and reporting we use MS Access 2003 by connecting to this database via ODBC connection. I recently found out that the SQL Server 2005 data connected thus can be edited (updated) from MS Access. I do not want end users to modify/update the SQL Server 2005 data from MS Access while I also want them to have the ability to insert/update/delete rights using the appropriate application interface. For now, I am handling this by creating a user id that is not permitted to update, insert and delete and using the same account in the ODBC. Is there a way in SQL Server 2005 you can control insert/update/delete rights for all users that will be applicable only in the ODBC mode?

Any help will be greatly appreciated.

thulo

Hi Thulo,

If I understand your question correctly, you want to be able to grant permissions to database objects based on the type of the client (ODBC, OLEDB, SqlClient, etc.), is this correct? Unfortunately, this isn't possible by design - the main goal is to provide the same functionality no matter what client is used. Instead, the SQL Server security model recommends what you are actually already doing - grant permissions "per database user". The user is the main permissions-related abstract here (having in mind the schemas, too). The user is related to the corresponding login object, which controls the connectivity part (having in mind endpoints permissions, too).

I understand that the user-based permissions concept might seem like more work, but it would pay off long term when the requirements to your application change.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Jivko,

You got my question right and that answers my question. Thanks so much for your help.

thulo