ADO error handling when connecting to SQL Server 2000

I use Delphi to connect to SQL Server 2000 using the ADO components. I'm
executing stored procedure that makes some calls to another stored procs. In
one of the sub called stored procs foreign key is violated and error message
is generated ('INSERT statement conflicted with COLUMN FOREIGN KEY
constraint ..' i see it in the SQL Query analyser).
When i use this connection string:
Provider=SQLOLEDB.1;Password="";Persist Security Info=True;User
ID=sa;Initial Catalog=TechDB;Data Source=.
the error is not reported in my application, it seems that the driver
ignores the error message. I have no Exception generated. So i tried with
another driver for SQL Server (ODBC). The ADO connection string was:
Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data
Source=LOCA_RMP;Initial Catalog=TechDB
This one is reporting error in the above situation (i get Exception with
'INSERT statement conflicted with COLUMN FOREIGN KEY constraint ...' ). But
the problem is that it generates exception when 'print' command is executed,
so it thinks that every message is an error. Besides the connecting thru
ODBC DSN seems to be slower and i think it's not native for ADO .
I know that @.@.ERROR is used to detect errors in SQL Server 2000 but it's
messy to use "if @.@.error<>0 set @.local_error=1" after every line of code. Is
there another way to do this? Am i supposed to raiserror at the end of the
stored proc?
Any suggestions are appreciated,
Georgi PeshterskiFor a complete discussion on Error handling in Stored Procs, see
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"George Peshterski" <unseen@.mail.bg> wrote in message
news:eF%234vneDFHA.2216@.TK2MSFTNGP09.phx.gbl...
> I use Delphi to connect to SQL Server 2000 using the ADO components.
> I'm
> executing stored procedure that makes some calls to another stored procs.
> In
> one of the sub called stored procs foreign key is violated and error
> message
> is generated ('INSERT statement conflicted with COLUMN FOREIGN KEY
> constraint ..' i see it in the SQL Query analyser).
> When i use this connection string:
> Provider=SQLOLEDB.1;Password="";Persist Security Info=True;User
> ID=sa;Initial Catalog=TechDB;Data Source=.
> the error is not reported in my application, it seems that the driver
> ignores the error message. I have no Exception generated. So i tried with
> another driver for SQL Server (ODBC). The ADO connection string was:
> Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data
> Source=LOCA_RMP;Initial Catalog=TechDB
> This one is reporting error in the above situation (i get Exception
> with
> 'INSERT statement conflicted with COLUMN FOREIGN KEY constraint ...' ).
> But
> the problem is that it generates exception when 'print' command is
> executed,
> so it thinks that every message is an error. Besides the connecting thru
> ODBC DSN seems to be slower and i think it's not native for ADO .
> I know that @.@.ERROR is used to detect errors in SQL Server 2000 but it's
> messy to use "if @.@.error<>0 set @.local_error=1" after every line of code.
> Is
> there another way to do this? Am i supposed to raiserror at the end of the
> stored proc?
> Any suggestions are appreciated,
> Georgi Peshterski
>|||Thank you for the useful links, i'm reading the articles now and there are
many things i didn't knew.
As for my problem i found that the foreign key error that ADO hides happens
in INSTEAD OF trigger in the subprocedures. I read that error in trigger
cancels the batch but i'm still curious why ADO isn't reporting it to me,
i'm going to examine carefully the articles about this problem ...
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:%23B7yxteDFHA.3688@.TK2MSFTNGP14.phx.gbl...
> For a complete discussion on Error handling in Stored Procs, see
> http://www.sommarskog.se/error-handling-I.html
> http://www.sommarskog.se/error-handling-II.html
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "George Peshterski" <unseen@.mail.bg> wrote in message
> news:eF%234vneDFHA.2216@.TK2MSFTNGP09.phx.gbl...
procs.
with
it's
code.
the
>|||Make sure you have SET NOCOUNT ON at the beginning of your procs and
triggers. This will suppress DONE_IN_PROC messages that are returned as
empty closed recordsets in ADO.
Hope this helps.
Dan Guzman
SQL Server MVP
"George Peshterski" <unseen@.mail.bg> wrote in message
news:e6k39MgDFHA.392@.TK2MSFTNGP14.phx.gbl...
> Thank you for the useful links, i'm reading the articles now and there are
> many things i didn't knew.
> As for my problem i found that the foreign key error that ADO hides
> happens
> in INSTEAD OF trigger in the subprocedures. I read that error in trigger
> cancels the batch but i'm still curious why ADO isn't reporting it to me,
> i'm going to examine carefully the articles about this problem ...
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:%23B7yxteDFHA.3688@.TK2MSFTNGP14.phx.gbl...
> procs.
> with
> it's
> code.
> the
>

ADO Connection issue

Hello All,

I have made a small application on my system in delphi v7 that uses the ado connetion components and works fine on the remote sql server db. But my client gets a "cannot find server" on his system with the sql server/db local.

My connection string this...

Provider=SQLOLEDB;Password=password;Persist Security Info=True;User ID=USER;Initial Catalog=MyDB;Data Source=SERVER01;

his connect is...

Provider=SQLOLEDB;Password=password;Persist Security Info=True;User ID=USER;Initial Catalog=MyDB;Data Source=HISSERVER01\APP;

I have changed the Data Source to (local) and local, as seen in another post but this had not effect. We have check his MDAC version verse mine and they are the same, save he has sp3; I have sp1.

...any ideas?

The SQL Server of your client is a named instance, you need to make sure that the Data Source string matches the instance that you want to connect to in the format of <servername>\<instancename>. Also, local is not valid, (local)\<instancename> or localhost\<instancename> are ok. If you want to connect to a remote named instance, you also need to enable SQL Browser service if the remote SQL instance is SQL Server 2005. -HTH|||

Thank for answering :)

But I may have not been clear. we are not sharing a database. He has his own copy running on his lan, I have mine running on my lan.

Both systems are running on XP systems.

On his; he is running the client app locally to his sql server. His connection of <server>/<instance> is correct

On mine; the client is remotely accessing the sql server. I did not setup an instance, so just using <server> should be valid.

I wonder if the instance requires additional parameters to be passed, any ideas?

TY,

CJ

|||Which version of SQL Server are you using ? If you are using SQL Server 2005 you will have to enable remote connections, switch on the SQL Browser in order to connect to another port than the default one 1433, or name the portnumber after the servername with the syntax Servername,portnumber.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

ADO Connection issue

Hello All,

I have made a small application on my system in delphi v7 that uses the ado connetion components and works fine on the remote sql server db. But my client gets a "cannot find server" on his system with the sql server/db local.

My connection string this...

Provider=SQLOLEDB;Password=password;Persist Security Info=True;User ID=USER;Initial Catalog=MyDB;Data Source=SERVER01;

his connect is...

Provider=SQLOLEDB;Password=password;Persist Security Info=True;User ID=USER;Initial Catalog=MyDB;Data Source=HISSERVER01\APP;

I have changed the Data Source to (local) and local, as seen in another post but this had not effect. We have check his MDAC version verse mine and they are the same, save he has sp3; I have sp1.

...any ideas?

The SQL Server of your client is a named instance, you need to make sure that the Data Source string matches the instance that you want to connect to in the format of <servername>\<instancename>. Also, local is not valid, (local)\<instancename> or localhost\<instancename> are ok. If you want to connect to a remote named instance, you also need to enable SQL Browser service if the remote SQL instance is SQL Server 2005. -HTH|||

Thank for answering :)

But I may have not been clear. we are not sharing a database. He has his own copy running on his lan, I have mine running on my lan.

Both systems are running on XP systems.

On his; he is running the client app locally to his sql server. His connection of <server>/<instance> is correct

On mine; the client is remotely accessing the sql server. I did not setup an instance, so just using <server> should be valid.

I wonder if the instance requires additional parameters to be passed, any ideas?

TY,

CJ

|||Which version of SQL Server are you using ? If you are using SQL Server 2005 you will have to enable remote connections, switch on the SQL Browser in order to connect to another port than the default one 1433, or name the portnumber after the servername with the syntax Servername,portnumber.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de