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...
quote:
> 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
quote:
> 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'
quote:
> option is on, and this behavior happens even when logged in as the user
sa,
quote:
> with full privileges. SQL Server has been completely uninstalled and
> reinstalled, and all known service packs have been installed, up to SP4.
OS
quote:
> 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,
quote:
> 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
quote:|||Dave,
> and take the server down far more than would be good.
> Help!
> 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...
quote:
> 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,
quote:
> dropping it first if it exists, BULK INSERT the data from the file into
it,
quote:|||Thanks for working with me on this oj.
> 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...
> to
> that's
> completed
user[QUOTE]
SP4.[QUOTE]
source[QUOTE]
> problem?
and[QUOTE]
>
>
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...
quote:
> 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.
quote:|||Dave,
> 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...
in[QUOTE]
> bulk_insert_test,
> it,
of[QUOTE]
file,[QUOTE]
able[QUOTE]
best[QUOTE]
also[QUOTE]
> user
> SP4.
> source
> and
admin[QUOTE]
>
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...
quote:
> 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
quote:
> on the fly by the test code, so there couldn't be a trigger referring to
it,
quote:
> right?
> What I don't get is that this works fine on two other servers, but does
this
quote:
> weird silent failure on just this one. No separate development on this
test
quote:
> 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,
quote:
> or a haunted SQL install. Fear of haunting is why we completely
reinstalled,
quote:sql
> but it made no difference.
> Help!
> Dave
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:eDWHKbI3DHA.2136@.TK2MSFTNGP12.phx.gbl...
> transaction.
1)[QUOTE]
> in
into[QUOTE]
number[QUOTE]
> of
> file,
any[QUOTE]
> able
> best
when[QUOTE]
> also
and[QUOTE]
to[QUOTE]
that[QUOTE]
wipe[QUOTE]
> admin
>
No comments:
Post a Comment