Board index MS SQL Server

Intermittent Foreign Key error

Intermittent Foreign Key error

Post by Christopher Campbel » Wed, 28 Aug 2002 05:42:17



I have a Windows Scripting Host (VBScript) script running on my server
updating a SQL 6.5 database.  This job runs daily, generally with no
problem.  We had a case where it ran and recieved the following error:

2147217900 [Microsoft][ODBC SQL Server Driver][SQL Server]INSERT statement
conflicted with COLUMN FOREIGN KEY constraint 'tblLetterFormDetails_FK01'.
The conflict occurred in database 'Pentamark', table 'tblPackages', column
'intPackageID'

Upon checking the data, it appeared that the foreign condition had been
satisfied and the error message as incorrect.  We resubmitted the job 3
times and got the same error.  On the fourth try (with no code or data
changes) the job ran successfully.

Can anyone think of a reason something like this might happen?  Does
VBScript continue processing even if SQL Server hasn't returned control yet?
In other words, if I execute an ADO command object inserting into Table1,
followed by an ADO command object inserting into Table2, will the second
insert be executed even if the first hasn't completed yet?  My understanding
was that this wasn't supposed to happen and we haven't experienced this
before however, our SQL server has been under an increased load lately.

Any help or insight would be appreciated.

Thanks,

Chris Campbell

 
 
 
Top

Intermittent Foreign Key error

Post by Greg Linwoo » Wed, 28 Aug 2002 09:57:41


Hi Chris.

ADO can actually operate in asynchronous mode, but this is not possible from
VBScript.

You could try using the SQL Profiler to see what order the commands are
being issued in..

HTH

Cheers,
Greg Linwood


> I have a Windows Scripting Host (VBScript) script running on my server
> updating a SQL 6.5 database.  This job runs daily, generally with no
> problem.  We had a case where it ran and recieved the following error:

> 2147217900 [Microsoft][ODBC SQL Server Driver][SQL Server]INSERT statement
> conflicted with COLUMN FOREIGN KEY constraint 'tblLetterFormDetails_FK01'.
> The conflict occurred in database 'Pentamark', table 'tblPackages', column
> 'intPackageID'

> Upon checking the data, it appeared that the foreign condition had been
> satisfied and the error message as incorrect.  We resubmitted the job 3
> times and got the same error.  On the fourth try (with no code or data
> changes) the job ran successfully.

> Can anyone think of a reason something like this might happen?  Does
> VBScript continue processing even if SQL Server hasn't returned control
yet?
> In other words, if I execute an ADO command object inserting into Table1,
> followed by an ADO command object inserting into Table2, will the second
> insert be executed even if the first hasn't completed yet?  My
understanding
> was that this wasn't supposed to happen and we haven't experienced this
> before however, our SQL server has been under an increased load lately.

> Any help or insight would be appreciated.

> Thanks,

> Chris Campbell



 
 
 
Top

Intermittent Foreign Key error

Post by Dan Guzma » Wed, 28 Aug 2002 11:32:11


ADO Execute methods are synchronous by default.  However, the command
will execute asynchronously if you've specified the adAsyncExecute (256
or 0x10) execute option.

Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------


Quote:> I have a Windows Scripting Host (VBScript) script running on my server
> updating a SQL 6.5 database.  This job runs daily, generally with no
> problem.  We had a case where it ran and recieved the following error:

> 2147217900 [Microsoft][ODBC SQL Server Driver][SQL Server]INSERT
statement
> conflicted with COLUMN FOREIGN KEY constraint

'tblLetterFormDetails_FK01'.
> The conflict occurred in database 'Pentamark', table 'tblPackages',
column
> 'intPackageID'

> Upon checking the data, it appeared that the foreign condition had
been
> satisfied and the error message as incorrect.  We resubmitted the job
3
> times and got the same error.  On the fourth try (with no code or data
> changes) the job ran successfully.

> Can anyone think of a reason something like this might happen?  Does
> VBScript continue processing even if SQL Server hasn't returned
control yet?
> In other words, if I execute an ADO command object inserting into
Table1,
> followed by an ADO command object inserting into Table2, will the
second
> insert be executed even if the first hasn't completed yet?  My
understanding
> was that this wasn't supposed to happen and we haven't experienced
this
> before however, our SQL server has been under an increased load
lately.

> Any help or insight would be appreciated.

> Thanks,

> Chris Campbell


 
 
 
Top

Intermittent Foreign Key error

Post by Greg Linwoo » Wed, 28 Aug 2002 12:11:19


Actually, I should re-state this - you may be able to execute asych, but you
can't capture WM_ message / events in VBScript..

Cheers,
Greg Linwood


> Hi Chris.

> ADO can actually operate in asynchronous mode, but this is not possible
from
> VBScript.

> You could try using the SQL Profiler to see what order the commands are
> being issued in..

> HTH

> Cheers,
> Greg Linwood



> > I have a Windows Scripting Host (VBScript) script running on my server
> > updating a SQL 6.5 database.  This job runs daily, generally with no
> > problem.  We had a case where it ran and recieved the following error:

> > 2147217900 [Microsoft][ODBC SQL Server Driver][SQL Server]INSERT
statement
> > conflicted with COLUMN FOREIGN KEY constraint

'tblLetterFormDetails_FK01'.

- Show quoted text -

> > The conflict occurred in database 'Pentamark', table 'tblPackages',
column
> > 'intPackageID'

> > Upon checking the data, it appeared that the foreign condition had been
> > satisfied and the error message as incorrect.  We resubmitted the job 3
> > times and got the same error.  On the fourth try (with no code or data
> > changes) the job ran successfully.

> > Can anyone think of a reason something like this might happen?  Does
> > VBScript continue processing even if SQL Server hasn't returned control
> yet?
> > In other words, if I execute an ADO command object inserting into
Table1,
> > followed by an ADO command object inserting into Table2, will the second
> > insert be executed even if the first hasn't completed yet?  My
> understanding
> > was that this wasn't supposed to happen and we haven't experienced this
> > before however, our SQL server has been under an increased load lately.

> > Any help or insight would be appreciated.

> > Thanks,

> > Chris Campbell


 
 
 
Top

1. Trigger to check a foreign key without using foreign key

Hello,

I want to use a trigger to check a foreign key.

Let's assume :

table LIGNES_V contains field ID_ARTICLE char(25)
table STOCK contains field S_ID char(10)

In table LIGNES_V, ID_ARTICLE must contain '' or a valid value from S_ID in
STOCK
(because of the '' -> could not use a standard foreign key)

In the update trigger of LIGNES_V, i placed

if update(id_article) and exists (select id_article from inserted where
(id_article>' ') and  (left(id_article,10) not in (select s_id from stock)))
 RAISERROR ( 'ID_ARTICLE does not match S_ID !',16,1 )

it works very fine and gives the correct result.

BUT : i assume that this request containing a sub-request will result in bad
performances.

Does somebody have an idea to write this trigger on a more-performing way ?

Thanks for help,

Kind regards,

Guy

2. NEED HELP WITH "DATE" FORMAT

3. foreign key w/o foreign/primary key

4. ASP connection

5. Trigger to check a foreign key without using foreign key

6. Inserting into Access table from ADO Rset or Cmd from Oracle

7. HELP !,SQL 6.5 and IIS 2, insert duplicate keys, intermittent error, reward

8. dbase to paradox

9. foreign/primary key error

10. error in deleting a record which is foreign key

11. foreign key rule error

12. Foreign Key & 14151 Error Replication Problem

13. Newbie: Relationships and foreign keys error


All times are UTC
Board index