BULK INSERT silent failure on one SQL7 server - Help!

On one of 3 tested SQL7 servers, a plain vanilla BULK INSERT query that's
importing a simple tab-tab-return text file says that the query completed
successfully, but doesn't import any rows. The failure happens when running
the query via Query Analyzer running on the same machine, on and also
through ColdFusion, itself running with full privileges.
An sp_dboption query and Enterprise Mgr both say the 'select into/bulkcopy'
option is on, and this behavior happens even when logged in as the user sa,
with full privileges. SQL Server has been completely uninstalled and
reinstalled, and all known service packs have been installed, up to SP4. OS
is Windows 2000 Pro.
I don't know if this is related or not, but the 'Text file' data source
option is missing from the DTS import wizard, as it appears on that machine,
and also on another client machine on that network.
Does anyone have any idea what can be done to fix the BULK INSERT problem?
It's a serious blocking issue that needs to be resolved.
At this point, my only ideas are to upgrade to SQL 2000, or to wipe and
rebuild the whole machine from scratch. Both those would consume admin time
and take the server down far more than would be good.
Help!
DaveDave,
If you post the ddl+sample data+your bulk insert query, we might be able to
help. If this is really critical, a call to MS PSS might prove the best
option.
--
-oj
http://www.rac4sql.net
"Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
news:eqzkjZF3DHA.3468@.TK2MSFTNGP11.phx.gbl...
> On one of 3 tested SQL7 servers, a plain vanilla BULK INSERT query that's
> importing a simple tab-tab-return text file says that the query completed
> successfully, but doesn't import any rows. The failure happens when
running
> the query via Query Analyzer running on the same machine, on and also
> through ColdFusion, itself running with full privileges.
> An sp_dboption query and Enterprise Mgr both say the 'select
into/bulkcopy'
> option is on, and this behavior happens even when logged in as the user
sa,
> with full privileges. SQL Server has been completely uninstalled and
> reinstalled, and all known service packs have been installed, up to SP4.
OS
> is Windows 2000 Pro.
> I don't know if this is related or not, but the 'Text file' data source
> option is missing from the DTS import wizard, as it appears on that
machine,
> and also on another client machine on that network.
> Does anyone have any idea what can be done to fix the BULK INSERT problem?
> It's a serious blocking issue that needs to be resolved.
> At this point, my only ideas are to upgrade to SQL 2000, or to wipe and
> rebuild the whole machine from scratch. Both those would consume admin
time
> and take the server down far more than would be good.
> Help!
> Dave
>|||Thanks oj, here you go, assuming attachments are allowed here.
Put the file tab_rtn_test.txt on the root of C, then run the .sql code in
query analyzer in grid mode. It will create a table called bulk_insert_test,
dropping it first if it exists, BULK INSERT the data from the file into it,
and show the resulting data. In QA, result tab 1 should show the number of
rows reported by BULK INSERT (10), tab 2 should show the data in the file,
and Messages should show '(10 row(s) affected)'.
Note that the test table doesn't get dropped, so you can examine it any
other way you want, but you'll want to kill it when you're done.
Any ideas would be hugely appreciated.
Thanks again,
Dave
"oj" <nospam_ojngo@.home.com> wrote in message
news:Oqit0fG3DHA.3936@.TK2MSFTNGP11.phx.gbl...
> Dave,
> If you post the ddl+sample data+your bulk insert query, we might be able
to
> help. If this is really critical, a call to MS PSS might prove the best
> option.
> --
> -oj
> http://www.rac4sql.net
>
> "Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
> news:eqzkjZF3DHA.3468@.TK2MSFTNGP11.phx.gbl...
> > On one of 3 tested SQL7 servers, a plain vanilla BULK INSERT query
that's
> > importing a simple tab-tab-return text file says that the query
completed
> > successfully, but doesn't import any rows. The failure happens when
> running
> > the query via Query Analyzer running on the same machine, on and also
> > through ColdFusion, itself running with full privileges.
> >
> > An sp_dboption query and Enterprise Mgr both say the 'select
> into/bulkcopy'
> > option is on, and this behavior happens even when logged in as the user
> sa,
> > with full privileges. SQL Server has been completely uninstalled and
> > reinstalled, and all known service packs have been installed, up to SP4.
> OS
> > is Windows 2000 Pro.
> >
> > I don't know if this is related or not, but the 'Text file' data source
> > option is missing from the DTS import wizard, as it appears on that
> machine,
> > and also on another client machine on that network.
> >
> > Does anyone have any idea what can be done to fix the BULK INSERT
problem?
> > It's a serious blocking issue that needs to be resolved.
> >
> > At this point, my only ideas are to upgrade to SQL 2000, or to wipe and
> > rebuild the whole machine from scratch. Both those would consume admin
> time
> > and take the server down far more than would be good.
> >
> > Help!
> >
> > Dave
> >
> >
>
begin 666 tab_rtn_test.txt
M0VET>0E3=&%T90E::7 -"E=E;&QE<VQE>2!(:6QL<PE-00DP,C0X,0T*0VQA
M<F5M;VYT"4Y("3 S-S0S#0I096%B;V1Y"4U!"3 Q.38P#0I1=6EN8WD)34$)
M,#(Q-CD-"E-C:71U871E"4U!"3 R,#8V#0I-96QR;W-E"4U!"3 R,3<V#0I7
M97-T(%=A<F5H86T)34$),#(U-S8-"DQE>&EN9W1O;@.E-00DP,C0R,0T*3&5X
G:6YG=&]N"4U!"3 R-#(P#0I3<')I;F=F:65L9 E600DR,C$U,@.T*
`
end
begin 666 bulk insert test.sql
M:68@.97AI<W1S("AS96QE8W0@.*B!F<F]M('-Y<V]B:F5C=',@.=VAE<F4@.:60@.
M/2!O8FIE8W1?:60H3B=B=6QK7VEN<V5R=%]T97-T)RD@.86YD($]"2D5#5%!2
M3U!%4E19*&ED+"!.)TES57-E<E1A8FQE)RD@./2 Q*0T*"61R;W @.=&%B;&4@.
M8G5L:U]I;G-E<G1?=&5S= T*#0I#4D5!5$4@.5$%"3$4@.8G5L:U]I;G-E<G1?
M=&5S=" H#0H)8VET>2!V87)C:&%R*#(P,# I+ T*"7-T871E('9A<F-H87(H
M,C P,"DL#0H)>FEP('9A<F-H87(H,C P,"D-"BD-"E-%5"!.3T-/54Y4($].
M#0H-"D)53$L@.24Y315)4(&)U;&M?:6YS97)T7W1E<W0-"D923TT@.)T,Z7'1A
M8E]R=&Y?=&5S="YT>'0G#0I7251(("@.-"@.E&25)35%)/5R ](#(L#0H)1DE%
M3$1415)-24Y!5$]2(#T@.)UQT)RP-"@.E23U=415)-24Y!5$]2(#T@.)UQN)RP-
M"@.E-05A%4E)/4E,@./2 P#0HI#0IS96QE8W0@.0$!23U=#3U5.5"!A<R!R;W=?
M8V]U;G0-"E-%5"!.3T-/54Y4($]&1@.T*#0IS96QE8W0@.*B!F<F]M(&)U;&M?
-:6YS97)T7W1E<W0-"@.``
`
end|||Dave,
I have successfully run the bulk insert. This is what returned from the
select *.
Wellesley Hills MA 02481
Claremont NH 03743
Peabody MA 01960
Quincy MA 02169
Scituate MA 02066
Melrose MA 02176
West Wareham MA 02576
Lexington MA 02421
Lexington MA 02420
Springfield VA 22152
The only time I got (0 row(s) affected) is when I rollback the transaction.
So, check to see if you have a trigger that rollback the transaction.
e.g.
if exists (select * from sysobjects where id =object_id(N'bulk_insert_test') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table bulk_insert_test
CREATE TABLE bulk_insert_test (
city varchar(2000),
state varchar(2000),
zip varchar(2000)
)
go
SET NOCOUNT ON
go
begin tran
BULK INSERT bulk_insert_test
FROM 'C:\tab_rtn_test.txt'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
MAXERRORS = 0
)
select @.@.ROWCOUNT as row_count
rollback tran
go
select * from bulk_insert_test
go
--
-oj
http://www.rac4sql.net
"Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
news:%23RZHozG3DHA.2308@.TK2MSFTNGP11.phx.gbl...
> Thanks oj, here you go, assuming attachments are allowed here.
> Put the file tab_rtn_test.txt on the root of C, then run the .sql code in
> query analyzer in grid mode. It will create a table called
bulk_insert_test,
> dropping it first if it exists, BULK INSERT the data from the file into
it,
> and show the resulting data. In QA, result tab 1 should show the number of
> rows reported by BULK INSERT (10), tab 2 should show the data in the file,
> and Messages should show '(10 row(s) affected)'.
> Note that the test table doesn't get dropped, so you can examine it any
> other way you want, but you'll want to kill it when you're done.
> Any ideas would be hugely appreciated.
> Thanks again,
> Dave
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:Oqit0fG3DHA.3936@.TK2MSFTNGP11.phx.gbl...
> > Dave,
> >
> > If you post the ddl+sample data+your bulk insert query, we might be able
> to
> > help. If this is really critical, a call to MS PSS might prove the best
> > option.
> >
> > --
> > -oj
> > http://www.rac4sql.net
> >
> >
> > "Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
> > news:eqzkjZF3DHA.3468@.TK2MSFTNGP11.phx.gbl...
> > > On one of 3 tested SQL7 servers, a plain vanilla BULK INSERT query
> that's
> > > importing a simple tab-tab-return text file says that the query
> completed
> > > successfully, but doesn't import any rows. The failure happens when
> > running
> > > the query via Query Analyzer running on the same machine, on and also
> > > through ColdFusion, itself running with full privileges.
> > >
> > > An sp_dboption query and Enterprise Mgr both say the 'select
> > into/bulkcopy'
> > > option is on, and this behavior happens even when logged in as the
user
> > sa,
> > > with full privileges. SQL Server has been completely uninstalled and
> > > reinstalled, and all known service packs have been installed, up to
SP4.
> > OS
> > > is Windows 2000 Pro.
> > >
> > > I don't know if this is related or not, but the 'Text file' data
source
> > > option is missing from the DTS import wizard, as it appears on that
> > machine,
> > > and also on another client machine on that network.
> > >
> > > Does anyone have any idea what can be done to fix the BULK INSERT
> problem?
> > > It's a serious blocking issue that needs to be resolved.
> > >
> > > At this point, my only ideas are to upgrade to SQL 2000, or to wipe
and
> > > rebuild the whole machine from scratch. Both those would consume admin
> > time
> > > and take the server down far more than would be good.
> > >
> > > Help!
> > >
> > > Dave
> > >
> > >
> >
> >
>
>|||Thanks for working with me on this oj.
I don't get what you're after by checking if there's a trigger to roll it
back. The table is was created today, specifically for this test, and no
such trigger was ever designed. In fact, the table is dropped and recreated
on the fly by the test code, so there couldn't be a trigger referring to it,
right?
What I don't get is that this works fine on two other servers, but does this
weird silent failure on just this one. No separate development on this test
table has ever been done, just the code I sent, so it's very unlikely that
there's any specific trigger or other code-level difference.
Seems like it must be some kind of configuration thing I haven't thought of,
or a haunted SQL install. Fear of haunting is why we completely reinstalled,
but it made no difference.
Help!
Dave
"oj" <nospam_ojngo@.home.com> wrote in message
news:eDWHKbI3DHA.2136@.TK2MSFTNGP12.phx.gbl...
> Dave,
> I have successfully run the bulk insert. This is what returned from the
> select *.
> Wellesley Hills MA 02481
> Claremont NH 03743
> Peabody MA 01960
> Quincy MA 02169
> Scituate MA 02066
> Melrose MA 02176
> West Wareham MA 02576
> Lexington MA 02421
> Lexington MA 02420
> Springfield VA 22152
> The only time I got (0 row(s) affected) is when I rollback the
transaction.
> So, check to see if you have a trigger that rollback the transaction.
> e.g.
> if exists (select * from sysobjects where id => object_id(N'bulk_insert_test') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table bulk_insert_test
> CREATE TABLE bulk_insert_test (
> city varchar(2000),
> state varchar(2000),
> zip varchar(2000)
> )
> go
> SET NOCOUNT ON
> go
> begin tran
> BULK INSERT bulk_insert_test
> FROM 'C:\tab_rtn_test.txt'
> WITH (
> FIRSTROW = 2,
> FIELDTERMINATOR = '\t',
> ROWTERMINATOR = '\n',
> MAXERRORS = 0
> )
> select @.@.ROWCOUNT as row_count
> rollback tran
> go
> select * from bulk_insert_test
> go
> --
> -oj
> http://www.rac4sql.net
>
> "Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
> news:%23RZHozG3DHA.2308@.TK2MSFTNGP11.phx.gbl...
> > Thanks oj, here you go, assuming attachments are allowed here.
> >
> > Put the file tab_rtn_test.txt on the root of C, then run the .sql code
in
> > query analyzer in grid mode. It will create a table called
> bulk_insert_test,
> > dropping it first if it exists, BULK INSERT the data from the file into
> it,
> > and show the resulting data. In QA, result tab 1 should show the number
of
> > rows reported by BULK INSERT (10), tab 2 should show the data in the
file,
> > and Messages should show '(10 row(s) affected)'.
> >
> > Note that the test table doesn't get dropped, so you can examine it any
> > other way you want, but you'll want to kill it when you're done.
> >
> > Any ideas would be hugely appreciated.
> >
> > Thanks again,
> >
> > Dave
> >
> >
> > "oj" <nospam_ojngo@.home.com> wrote in message
> > news:Oqit0fG3DHA.3936@.TK2MSFTNGP11.phx.gbl...
> > > Dave,
> > >
> > > If you post the ddl+sample data+your bulk insert query, we might be
able
> > to
> > > help. If this is really critical, a call to MS PSS might prove the
best
> > > option.
> > >
> > > --
> > > -oj
> > > http://www.rac4sql.net
> > >
> > >
> > > "Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
> > > news:eqzkjZF3DHA.3468@.TK2MSFTNGP11.phx.gbl...
> > > > On one of 3 tested SQL7 servers, a plain vanilla BULK INSERT query
> > that's
> > > > importing a simple tab-tab-return text file says that the query
> > completed
> > > > successfully, but doesn't import any rows. The failure happens when
> > > running
> > > > the query via Query Analyzer running on the same machine, on and
also
> > > > through ColdFusion, itself running with full privileges.
> > > >
> > > > An sp_dboption query and Enterprise Mgr both say the 'select
> > > into/bulkcopy'
> > > > option is on, and this behavior happens even when logged in as the
> user
> > > sa,
> > > > with full privileges. SQL Server has been completely uninstalled and
> > > > reinstalled, and all known service packs have been installed, up to
> SP4.
> > > OS
> > > > is Windows 2000 Pro.
> > > >
> > > > I don't know if this is related or not, but the 'Text file' data
> source
> > > > option is missing from the DTS import wizard, as it appears on that
> > > machine,
> > > > and also on another client machine on that network.
> > > >
> > > > Does anyone have any idea what can be done to fix the BULK INSERT
> > problem?
> > > > It's a serious blocking issue that needs to be resolved.
> > > >
> > > > At this point, my only ideas are to upgrade to SQL 2000, or to wipe
> and
> > > > rebuild the whole machine from scratch. Both those would consume
admin
> > > time
> > > > and take the server down far more than would be good.
> > > >
> > > > Help!
> > > >
> > > > Dave
> > > >
> > > >
> > >
> > >
> >
> >
> >
>|||Dave,
Try it with bcp and see if the data is committed. Also, try specifying the
object owner in the test script. Perhaps, there are multiple objects with
the same name.
--
-oj
http://www.rac4sql.net
"Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
news:%23TvF%230I3DHA.2460@.TK2MSFTNGP10.phx.gbl...
> Thanks for working with me on this oj.
> I don't get what you're after by checking if there's a trigger to roll it
> back. The table is was created today, specifically for this test, and no
> such trigger was ever designed. In fact, the table is dropped and
recreated
> on the fly by the test code, so there couldn't be a trigger referring to
it,
> right?
> What I don't get is that this works fine on two other servers, but does
this
> weird silent failure on just this one. No separate development on this
test
> table has ever been done, just the code I sent, so it's very unlikely that
> there's any specific trigger or other code-level difference.
> Seems like it must be some kind of configuration thing I haven't thought
of,
> or a haunted SQL install. Fear of haunting is why we completely
reinstalled,
> but it made no difference.
> Help!
> Dave
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:eDWHKbI3DHA.2136@.TK2MSFTNGP12.phx.gbl...
> > Dave,
> >
> > I have successfully run the bulk insert. This is what returned from the
> > select *.
> >
> > Wellesley Hills MA 02481
> > Claremont NH 03743
> > Peabody MA 01960
> > Quincy MA 02169
> > Scituate MA 02066
> > Melrose MA 02176
> > West Wareham MA 02576
> > Lexington MA 02421
> > Lexington MA 02420
> > Springfield VA 22152
> >
> > The only time I got (0 row(s) affected) is when I rollback the
> transaction.
> > So, check to see if you have a trigger that rollback the transaction.
> >
> > e.g.
> > if exists (select * from sysobjects where id => > object_id(N'bulk_insert_test') and OBJECTPROPERTY(id, N'IsUserTable') =1)
> > drop table bulk_insert_test
> >
> > CREATE TABLE bulk_insert_test (
> > city varchar(2000),
> > state varchar(2000),
> > zip varchar(2000)
> > )
> > go
> > SET NOCOUNT ON
> > go
> > begin tran
> >
> > BULK INSERT bulk_insert_test
> > FROM 'C:\tab_rtn_test.txt'
> > WITH (
> > FIRSTROW = 2,
> > FIELDTERMINATOR = '\t',
> > ROWTERMINATOR = '\n',
> > MAXERRORS = 0
> > )
> > select @.@.ROWCOUNT as row_count
> >
> > rollback tran
> > go
> > select * from bulk_insert_test
> > go
> > --
> > -oj
> > http://www.rac4sql.net
> >
> >
> > "Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
> > news:%23RZHozG3DHA.2308@.TK2MSFTNGP11.phx.gbl...
> > > Thanks oj, here you go, assuming attachments are allowed here.
> > >
> > > Put the file tab_rtn_test.txt on the root of C, then run the .sql code
> in
> > > query analyzer in grid mode. It will create a table called
> > bulk_insert_test,
> > > dropping it first if it exists, BULK INSERT the data from the file
into
> > it,
> > > and show the resulting data. In QA, result tab 1 should show the
number
> of
> > > rows reported by BULK INSERT (10), tab 2 should show the data in the
> file,
> > > and Messages should show '(10 row(s) affected)'.
> > >
> > > Note that the test table doesn't get dropped, so you can examine it
any
> > > other way you want, but you'll want to kill it when you're done.
> > >
> > > Any ideas would be hugely appreciated.
> > >
> > > Thanks again,
> > >
> > > Dave
> > >
> > >
> > > "oj" <nospam_ojngo@.home.com> wrote in message
> > > news:Oqit0fG3DHA.3936@.TK2MSFTNGP11.phx.gbl...
> > > > Dave,
> > > >
> > > > If you post the ddl+sample data+your bulk insert query, we might be
> able
> > > to
> > > > help. If this is really critical, a call to MS PSS might prove the
> best
> > > > option.
> > > >
> > > > --
> > > > -oj
> > > > http://www.rac4sql.net
> > > >
> > > >
> > > > "Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
> > > > news:eqzkjZF3DHA.3468@.TK2MSFTNGP11.phx.gbl...
> > > > > On one of 3 tested SQL7 servers, a plain vanilla BULK INSERT query
> > > that's
> > > > > importing a simple tab-tab-return text file says that the query
> > > completed
> > > > > successfully, but doesn't import any rows. The failure happens
when
> > > > running
> > > > > the query via Query Analyzer running on the same machine, on and
> also
> > > > > through ColdFusion, itself running with full privileges.
> > > > >
> > > > > An sp_dboption query and Enterprise Mgr both say the 'select
> > > > into/bulkcopy'
> > > > > option is on, and this behavior happens even when logged in as the
> > user
> > > > sa,
> > > > > with full privileges. SQL Server has been completely uninstalled
and
> > > > > reinstalled, and all known service packs have been installed, up
to
> > SP4.
> > > > OS
> > > > > is Windows 2000 Pro.
> > > > >
> > > > > I don't know if this is related or not, but the 'Text file' data
> > source
> > > > > option is missing from the DTS import wizard, as it appears on
that
> > > > machine,
> > > > > and also on another client machine on that network.
> > > > >
> > > > > Does anyone have any idea what can be done to fix the BULK INSERT
> > > problem?
> > > > > It's a serious blocking issue that needs to be resolved.
> > > > >
> > > > > At this point, my only ideas are to upgrade to SQL 2000, or to
wipe
> > and
> > > > > rebuild the whole machine from scratch. Both those would consume
> admin
> > > > time
> > > > > and take the server down far more than would be good.
> > > > >
> > > > > Help!
> > > > >
> > > > > Dave
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
>