sql server (alternate): 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!!!

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

(danceli@gmail.com) writes:
[quoted text, click to view]

What numbers are these?

[quoted text, click to view]

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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at