bcp and trigger: missing data in bcp out file

[danceli]

I have made trigger on table 'FER' that would be fired if data is
inserted, updated to the table. And also, I made batch file using bcp
to extract the newly updated / inserted records.

But I got missing data in bcp out file like this:

Missing 1200 records, blocked at:
/*
777946 296188 2007-01-29 21:25:45.063

778145 296494 2007-01-29 21:25:47.063
*/

1. trigger.sql
CREATE TABLE [FERUpdate] (
[id] [int] NOT NULL ,
[fid] [int] NOT NULL ,
[sid] [int] NOT NULL ,
[UpdatePass] [int] NULL
) ON [PRIMARY]
GO
create trigger trgFERUpdate on FER For Insert,Update as
insert into FERUpdate(id,fid,sid) select ins.id, ins.fid,ins.sid from
inserted ins

2. bcp.bat
----
isql -U <user-P <pw-S server -Q "update AA..FERUpdate set
UpdatePass=1 where UpdatePass is null"

bcp "select a.* from AA..FER a, AA..FERUpdate b where a.fid=b.fid and
a.sid=b.sid and b.fid<>-1 and b.sid<>-1 and b.updatepass=1" queryout
%TFN_NOW%.wrk -U <user-P <pw-S server -f FER.fmt

isql -U <user-P <pw-S server -Q "delete from AA..FERUpdate where
UpdatePass=1"
---
--

I have been struggling with this for these two days. Your any helps
are appreciated, Please help me out!! Thanks!!!

[Erland Sommarskog]

(da*****@gmail.com) writes:

I have made trigger on table 'FER' that would be fired if data is
inserted, updated to the table. And also, I made batch file using bcp
to extract the newly updated / inserted records.

But I got missing data in bcp out file like this:

Missing 1200 records, blocked at:
/*
777946 296188 2007-01-29 21:25:45.063

778145 296494 2007-01-29 21:25:47.063
*/

What numbers are these?

2. bcp.bat
----
isql -U <user-P <pw-S server -Q "update AA..FERUpdate set
UpdatePass=1 where UpdatePass is null"

bcp "select a.* from AA..FER a, AA..FERUpdate b where a.fid=b.fid and
a.sid=b.sid and b.fid<>-1 and b.sid<>-1 and b.updatepass=1" queryout
%TFN_NOW%.wrk -U <user-P <pw-S server -f FER.fmt

isql -U <user-P <pw-S server -Q "delete from AA..FERUpdate where
UpdatePass=1"
---

How often do you run this?
What is the meaning if the <-1 things?

And how do you conclude that the data is missing? There is no
ORDER BY clause in your SELECT, so the missing rows may be elsewhere
in the file.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Thyagu]

Either you query is wrong or you did the DELETE operation before the
bcp out command.