SEO

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!!!

(danceli@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, esquel@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

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