Sql Basic Security: missing

显示标签为“missing”的博文。显示所有博文

2012年3月25日星期日

BCP uploads of data with missing values

I have two problems with the BCP utility:
1. I have a data file that sometimes does not have a value for some of the
fields. I am looking at uploading that data into SQL Server 2000 using BCP.
If it happens to be the last column in the row that doesn't have any data,
BCP complains about that line with the following error:
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
If I add spaces at the end of the line to match the line length with the
definition file that I use to import the data, the error goes away. What ca
n
I do to keep this error from being recorded? BCP quits after a preset numbe
r
of errors, unless I was to add the -m maxerrors parameter. I'd rather not d
o
that as I could miss "real" errors.
2. I want nulls to be inserted into the columns where there is nothing
specified in the data file. Because the data file is in a fixed width
format, I suspect BCP thinks that the value for that type of column is a
bunch of spaces. How can I force BCP to insert nulls instead?
Thanks,
FrancoisFrancois,
I'm don't know if this can be done with BCP, however the last
time I checked, the Jet driver handled ragged-right source data
without raising errors. Jet may not have the best reputation around,
but it may help you out here.
See http://groups.google.co.uk/groups?q...+kass+sqlserver
for details.
Steve Kass
Drew University
Francois wrote:

>I have two problems with the BCP utility:
>1. I have a data file that sometimes does not have a value for some of the
>fields. I am looking at uploading that data into SQL Server 2000 using BCP
.
>If it happens to be the last column in the row that doesn't have any data,
>BCP complains about that line with the following error:
>SQLState = 22001, NativeError = 0
>Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
>If I add spaces at the end of the line to match the line length with the
>definition file that I use to import the data, the error goes away. What c
an
>I do to keep this error from being recorded? BCP quits after a preset numb
er
>of errors, unless I was to add the -m maxerrors parameter. I'd rather not
do
>that as I could miss "real" errors.
>2. I want nulls to be inserted into the columns where there is nothing
>specified in the data file. Because the data file is in a fixed width
>format, I suspect BCP thinks that the value for that type of column is a
>bunch of spaces. How can I force BCP to insert nulls instead?
>Thanks,
>Francois
>

2012年3月6日星期二

BCP error - missing data

CREATE TABLE [EventUpdate] (
[id] [int] NOT NULL ,
[eventid] [int] NOT NULL ,
[sequenceid] [int] NOT NULL ,
[UpdatePass] [int] NULL
) ON [PRIMARY]
GO
create trigger trgEventUpdate on EventLog For Insert,Update as
insert into EventUpdate (id,eventid,sequenceid) select ins.id,
ins.eventid,ins.sequenceid from inserted ins

bcp script:

bcp "select a.* from w..eventlog a, w..eventupdate b where
a.eventid=b.eventid and a.sequenceid=b.sequenceid and b.eventid<>-1
and b.sequenceid<>-1 and b.updatepass=1" queryout 30sec-%TFN_NOW%.wrk -
U <userwithaccess-P <password-S doserver -f EventLog.fmt

Thanks in advance for your reply!(danceli@.gmail.com) writes:

Quote:

Originally Posted by

Please, could you take that again, and more slowly this time? Keep in
mind that people reading this newsgroup does not know about your system.

You are missing data but where? You appear to extract data with BCP, do
you load it anywhere else?

One thing I can note:

Quote:

Originally Posted by

create trigger trgEventUpdate on EventLog For Insert,Update as
insert into EventUpdate (id,eventid,sequenceid) select ins.id,
ins.eventid,ins.sequenceid from inserted ins
>
>bcp "select a.* from w..eventlog a, w..eventupdate b where
>a.eventid=b.eventid and a.sequenceid=b.sequenceid and b.eventid<>-1
>and b.sequenceid<>-1 and b.updatepass=1" queryout 30sec-%TFN_NOW%.wrk -
>U <userwithaccess-P <password-S doserver -f EventLog.fmt

The BCP query has a condition on the column UpdatePass which is not
set in the trigger. So from what you have posted, I would not expect
anything at all to go in the BCP file.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||1.

Quote:

Originally Posted by

You are missing data but where? You appear to extract data withBCP, do
you load it anywhere else?

On table Eventlog, there are missing data.

Quote:

Originally Posted by

Quote:

Originally Posted by

create trigger trgEventUpdate on EventLog For Insert,Update as
insert into EventUpdate (id,eventid,sequenceid) select ins.id,
ins.eventid,ins.sequenceid from inserted ins

Quote:

Originally Posted by

bcp"select a.* from w..eventlog a, w..eventupdate b where
a.eventid=b.eventid and a.sequenceid=b.sequenceid and b.eventid<>-1
and b.sequenceid<>-1 and b.updatepass=1" queryout 30sec-%TFN_NOW%.wrk -
U <userwithaccess-P <password-S doserver -f EventLog.fmt

>
TheBCPquery has a condition on the column UpdatePass which is not
set in the trigger. So from what you have posted, I would not expect
anything at all to go in theBCPfile.

sorry, i didn't clearly explain it. i did set the UpdataPass:

CREATE TABLE [EventLogUpdate] (
[id] [int] NOT NULL ,
[eventid] [int] NOT NULL ,
[sequenceid] [int] NOT NULL ,
[UpdatePass] [int] NULL
) ON [PRIMARY]
GO
create trigger trgEventUpdate on EventLog For Insert,Update as
insert into EventLogUpdate (id,eventid,sequenceid) select ins.id,
ins.eventid,ins.sequenceid from inserted ins
GO|||(danceli@.gmail.com) writes:

Quote:

Originally Posted by

On table Eventlog, there are missing data.

But it was the EventLog table you had the trigger on? If data is
missing in EventLog what has the trigger or the BCP stuff to do with it?

Quote:

Originally Posted by

sorry, i didn't clearly explain it. i did set the UpdataPass:
>
CREATE TABLE [EventLogUpdate] (
[id] [int] NOT NULL ,
[eventid] [int] NOT NULL ,
[sequenceid] [int] NOT NULL ,
[UpdatePass] [int] NULL
) ON [PRIMARY]
GO
create trigger trgEventUpdate on EventLog For Insert,Update as
insert into EventLogUpdate (id,eventid,sequenceid) select ins.id,
ins.eventid,ins.sequenceid from inserted ins
GO

I still can't see any update of UpdatePass?

It's your call. If you don't want to explain what you are doing, that
is alright. But unfortunately it is difficult to answer your questions
in this case.

BCP error - missing data

bcp script:

Thanks in advance for your reply!Danceli,

Are your triggers set-based?

-- Bill

<danceli@.gmail.comwrote in message
news:1170212352.130762.295120@.m58g2000cwm.googlegr oups.com...

Quote:

Originally Posted by

After loading the BCP files that are created during the trigger/
reporting events I've noticed that the data in the table is missing
records. I've also noticed that the missing records (records in the
table but not in the BCP out files) seem to occur in contiguous
blocks. Since the complete set of records exists in the table, I
assume this points to an issue in the way the TableUpdate script/
Triggers interact with the system. But i tried to take out the bcp
procedure, do test on trigger, then no data missing, So, I think the
problem is still on bcp part. Could you help me with that?
>
CREATE TABLE [EventUpdate] (
[id] [int] NOT NULL ,
[eventid] [int] NOT NULL ,
[sequenceid] [int] NOT NULL ,
[UpdatePass] [int] NULL
) ON [PRIMARY]
GO
create trigger trgEventUpdate on EventLog For Insert,Update as
insert into EventUpdate (id,eventid,sequenceid) select ins.id,
ins.eventid,ins.sequenceid from inserted ins
>
>
>
bcp script:
>
bcp "select a.* from w..eventlog a, w..eventupdate b where
a.eventid=b.eventid and a.sequenceid=b.sequenceid and b.eventid<>-1
and b.sequenceid<>-1 and b.updatepass=1" queryout 30sec-%TFN_NOW%.wrk -
U <userwithaccess-P <password-S doserver -f EventLog.fmt
>
Thanks in advance for your reply!
>

2012年2月23日星期四

bcp and trigger: missing data in bcp out file

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:

Quote:

Originally Posted by

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?

Quote:

Originally Posted by

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.

订阅：博文 (Atom)

2012年3月25日星期日

BCP uploads of data with missing values

2012年3月6日星期二

BCP error - missing data

BCP error - missing data

2012年2月23日星期四

bcp and trigger: missing data in bcp out file

Sql Basic Security

Blog Archive

About Me