Hi, I have a table T with 3 int columns, I need to update it every X
hours with a query that returns the same 3 int columns. Currently I'm
doing it using a cursor iterating over each row and checking whether I
need to DELETE/UPDATE/INSERT it, this however isn't very efficient.
So I tested 2 additional methods, one involving deleting all the rows
from the target table and then inserting everything and the other by
dropping the table, recreating and inseting.
The results are not very surprising:
PROC,CPU,READS,WRITES,DURATION
DROP,2720,177757,218,4000
DELE,3150,183060,230,5300
CURS,8200,504818,247,8240
Which indicates that DROPing and reCREATing is the way to go, any
suggestions?
I don't like the DROP/CREATE scheme since it might involve the TABLEs
downtime (even when I CREATE a T1 then DROP T and sp_rename T1->T). If
there is no better way, will there be problems due to sp_rename
changing the name but not the ID of an object?
P.S. it's SQL2005, and the query itself executes in (2300,11596,0,2730)
[same column names as above].
Thanks.Can you give some more information so we can provide a more realistic
answer? The DDL for the existing table would help along with how or where
you are getting the data to update the table with. If the new data is a
complete up to date set then you have several options. I am assuming it is a
flat file but since you didn't provide that information it is only a guess.
I would look at using BULK INSERT to load the new data into a table that you
create. Lets call it X. Then you can prep this table to get it exactly like
you want it to be, drop the original table and rename X to what the old one
was. You may find that renaming the old one first, renaming X and then
dropping might yield the least time that the original table is off-line to
the users. But either way it should be a matter of a second or less.
Andrew J. Kelly SQL MVP
<johnsolver@.gmail.com> wrote in message
news:1137942204.960433.107600@.g49g2000cwa.googlegroups.com...
> Hi, I have a table T with 3 int columns, I need to update it every X
> hours with a query that returns the same 3 int columns. Currently I'm
> doing it using a cursor iterating over each row and checking whether I
> need to DELETE/UPDATE/INSERT it, this however isn't very efficient.
> So I tested 2 additional methods, one involving deleting all the rows
> from the target table and then inserting everything and the other by
> dropping the table, recreating and inseting.
> The results are not very surprising:
> PROC,CPU,READS,WRITES,DURATION
> DROP,2720,177757,218,4000
> DELE,3150,183060,230,5300
> CURS,8200,504818,247,8240
> Which indicates that DROPing and reCREATing is the way to go, any
> suggestions?
> I don't like the DROP/CREATE scheme since it might involve the TABLEs
> downtime (even when I CREATE a T1 then DROP T and sp_rename T1->T). If
> there is no better way, will there be problems due to sp_rename
> changing the name but not the ID of an object?
> P.S. it's SQL2005, and the query itself executes in (2300,11596,0,2730)
> [same column names as above].
> Thanks.
>|||Instead of deleting all rows from the table you can truncate it. Its not
a good idea to drop and recreate table.
Or
you can first store the data in one temporary and then update related
data from temporary table in permanent table using TSQL.
Please post data and how you want to update it.
Regards
Amish Shah
*** Sent via Developersdex http://www.examnotes.net ***|||Hi, sorry for not providing enough info:
the table:
CREATE TABLE [dbo].[T](
[tid] [int] NOT NULL,
[pid] [int] NOT NULL DEFAULT (0),
[r] [int] NOT NULL,
CONSTRAINT [PK_T] PRIMARY KEY CLUSTERED
(
[t] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
The query returns the exact 3 columns, simplistically: SELECT tid,pid,r
FROM A (it returns ~90k rows) I don't think that the exact query
matters... I'll be happy to provide additional info if needed.
Thanks.|||As to updating data, assuming T contains the following:
tid,pid,r
1,3,6
2,3,40
5,4,60
and the query (Q) returns
tid,pid,r
1,3,50
8,1,1000
Then I would like T to contain exactly what Q returned, namely:
DELETE FROM T WHERE tid=2
DELETE FROM T WHERE tid=5
UPDATE T SET r=50 WHERE tid=1
INSERT INTO T (tid,pid,r) VALUES(8,1,1000)
etc.|||If you are updating the value for r in table T, based on a match of both tid
and pid from the source table, then
--create a temporary table that has the data to be imported.
SELECT tid, pid, r INTO #importtable FROM (...the rest of your import query
...)
--Delete records from T that aren't in the new import batch
DELETE FROM T WHERE NOT EXISTS
(SELECT i.tid FROM #importtable i WHERE i.tid = T.tid)
--update the records that have matching tid and pid values
UPDATE T SET r = i.r FROM #importtable i
INNER JOIN T ON T.tid=i.tid AND T.pid = i.pid
--INSERT new records into T that didn't exist before
INSERT INTO T
SELECT tid,pid,r FROM #importtable i
WHERE NOT EXISTS
(SELECT tid FROM T WHERE T.tid = i.tid)
This gets more complicated if the PK on T is composite; I couldn't really
tell from the DDL you posted earlier.
Of course, it may be faster to just truncate the table and import in the new
data, as suggested earlier by Amish.
Another suggestion is to encapsulate the above into an INSTEAD OF trigger,
and then you could simply run
INSERT INTO T SELECT ...rest of your import query here ...
The INSTEAD OF trigger would fire before any primary key constraints would
be checked, so it could acheive what you want.
"johnsolver@.gmail.com" wrote:
> As to updating data, assuming T contains the following:
> tid,pid,r
> 1,3,6
> 2,3,40
> 5,4,60
> and the query (Q) returns
> tid,pid,r
> 1,3,50
> 8,1,1000
> Then I would like T to contain exactly what Q returned, namely:
> DELETE FROM T WHERE tid=2
> DELETE FROM T WHERE tid=5
> UPDATE T SET r=50 WHERE tid=1
> INSERT INTO T (tid,pid,r) VALUES(8,1,1000)
> etc.
>|||Thanks for the reply Mark,
I've tried your suggestion, performance-wise it's better than the
CURSOR solution (not surprising) but it's not as good as DROP or
TRUNCATE approaches. The key isn't a composite it's only one column as
you guessed, tid.
Amish: Why isn't it a good idea to DROP/CREATE the table? (except for
the minimal downtime due to sp_rename)?
Thanks.|||Johnsol
I also suggested you
you can first store the data in one temporary and then update related
data from temporary table in permanent table using TSQL.
But I was unable to give solution untill go post DDL.
Second
when you change the table name causes the sp, view and references
invalid which uses this table until you recreate new table with old
name.
If you drop and recreate table you have to recreate all relations ,
indexes again. You can not guarantee referential integrity of data
of othe tables if this table is part of any foregin key relationship
with them, since this table drops many times.
In some cases we have seen that updating data in the table was not
possible using TSQL easily and you have to create large number of temp
tables and check number of conditions and check data from number of
tables.
So I have gave you all the options but untill I get the data I was not
able to give you some solution.
Regards
Amish Shah|||Thanks for the reply Amish,
in the end I'll probably go with CREATE and DROP simply because it's
performance is about 1.5 times faster than the temp table solution, I
don't have any foreign keys/views built on the table.
So I've opted for the following (DDL follows), btw. should I wrap the
whole sequence in BEGIN TRANS ... COMIT TRANS?
CREATE TABLE [dbo].[T_1](
[tid] [int] NOT NULL,
[pid] [int] NOT NULL DEFAULT ((0)),
[r] [int] NOT NULL,
CONSTRAINT [PK_T_1] PRIMARY KEY CLUSTERED
(
[tid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO T_1 SELECT tid,pid,r FROM A;
DROP TABLE T;
EXEC sp_rename 'T_1','T';
EXEC sp_rename 'T.PK_T_1','PK_T','INDEX';|||Yes at least you should do all in transaction.
Regards
Amish Shah
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Thursday, March 29, 2012
Thursday, March 22, 2012
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...
running
into/bulkcopy'
sa,
OS
machine,
time
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...
bulk_insert_test,
it,
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...
transaction.
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...
recreated
it,
this
test
of,
reinstalled,
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
>
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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
>
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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
>
Tuesday, March 20, 2012
BULK INSERT Query
Hey i am planning to use the BULK INSERT to copy the data into my Server. Th
e
data is being written to the file by external application.
But i want the data that is being written should be deleted after the
insert. Can BULK INSERT statement support such option to be specified. other
wise my file will grow infintly over time.
Any suggestion or pointer will be helpful.
MCAD
Vineet BattaBULK INSERT itself will not manage the files for you. You'll have to do
that yourself via another script. You might want to look into using a DTS
package to manage the BULK INSERT and if successful, execute a script task
to move/delete the file.
--Brian
(Please reply to the newsgroups only.)
"vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
news:816DC10F-5FAA-413D-9632-AEA438311802@.microsoft.com...
> Hey i am planning to use the BULK INSERT to copy the data into my Server.
> The
> data is being written to the file by external application.
>
> But i want the data that is being written should be deleted after the
> insert. Can BULK INSERT statement support such option to be specified.
> other
> wise my file will grow infintly over time.
> Any suggestion or pointer will be helpful.
> --
> MCAD
> Vineet Batta|||But does BULK Insert can keep track of what data has been pushed to server
from last time , so that when it runs the second time it just picks up delta
only.
--
MCAD
Vineet Batta
"Brian Lawton" wrote:
> BULK INSERT itself will not manage the files for you. You'll have to do
> that yourself via another script. You might want to look into using a DTS
> package to manage the BULK INSERT and if successful, execute a script task
> to move/delete the file.
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
> news:816DC10F-5FAA-413D-9632-AEA438311802@.microsoft.com...
>
>|||No, but you can insert into a staging table and then run queries to
unset/update data into the main table as desired.
Hope this helps.
Dan Guzman
SQL Server MVP
"vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
news:08DC46E9-ED63-413D-83E5-B7FA3EC47762@.microsoft.com...
> But does BULK Insert can keep track of what data has been pushed to server
> from last time , so that when it runs the second time it just picks up
> delta
> only.
> --
> MCAD
> Vineet Batta
>
> "Brian Lawton" wrote:
>
e
data is being written to the file by external application.
But i want the data that is being written should be deleted after the
insert. Can BULK INSERT statement support such option to be specified. other
wise my file will grow infintly over time.
Any suggestion or pointer will be helpful.
MCAD
Vineet BattaBULK INSERT itself will not manage the files for you. You'll have to do
that yourself via another script. You might want to look into using a DTS
package to manage the BULK INSERT and if successful, execute a script task
to move/delete the file.
--Brian
(Please reply to the newsgroups only.)
"vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
news:816DC10F-5FAA-413D-9632-AEA438311802@.microsoft.com...
> Hey i am planning to use the BULK INSERT to copy the data into my Server.
> The
> data is being written to the file by external application.
>
> But i want the data that is being written should be deleted after the
> insert. Can BULK INSERT statement support such option to be specified.
> other
> wise my file will grow infintly over time.
> Any suggestion or pointer will be helpful.
> --
> MCAD
> Vineet Batta|||But does BULK Insert can keep track of what data has been pushed to server
from last time , so that when it runs the second time it just picks up delta
only.
--
MCAD
Vineet Batta
"Brian Lawton" wrote:
> BULK INSERT itself will not manage the files for you. You'll have to do
> that yourself via another script. You might want to look into using a DTS
> package to manage the BULK INSERT and if successful, execute a script task
> to move/delete the file.
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
> news:816DC10F-5FAA-413D-9632-AEA438311802@.microsoft.com...
>
>|||No, but you can insert into a staging table and then run queries to
unset/update data into the main table as desired.
Hope this helps.
Dan Guzman
SQL Server MVP
"vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
news:08DC46E9-ED63-413D-83E5-B7FA3EC47762@.microsoft.com...
> But does BULK Insert can keep track of what data has been pushed to server
> from last time , so that when it runs the second time it just picks up
> delta
> only.
> --
> MCAD
> Vineet Batta
>
> "Brian Lawton" wrote:
>
Bulk insert Problem(Bug) in SQL Server 2005
I can not bulk insert a network file onto a remote SQL server 2005 machine
I get this error when run from Query analyser in remote machine
Cannot bulk load because the file <File Name> could not be opened. Operating
system error code 5(Access is denied.)
It used to work in SQL 2000 We just upgradted to SQL 2005.
The same command works fine when run from the local machine hosting the SQL
server it can access the remote file and can bulk isert but when run from
remote machine it does not work. All accounts are admin accounts and we use
Windows authentication and are using valid UNC paths.
Thanks
Kiran
I have this same exact problem, and have been fighting it for three
days. Even when I remove the lines that it says are causing the
problem, when I run it again, it just picks some new lines to fail on.
Check out this thread, where someone posts how they proved that the
problem only exists when using SQL Server 2005 to import the data:
http://forums.microsoft.com/MSDN/Sho...41512&SiteID=1
I'm convinced there is some kind of bug here, and I'm about to give up
on trying to use bulk insert altogether and just write a program to
import the file myself. SQL 2000 DTS can import the same file with no
problem. It's pretty clear there's nothing wrong with the file.
FYI, in an SSIS context, this same exact thing happens when using the
Bulk Insert task and when using the Import Column in a data flow task.
I can reproduce the problem by executing a BULK insert command in a
query window, taking SSIS out of the picture altogether.
If anyone from Microsoft is reading this and is aware of this issue,
please advise. There are clearly multiple people out here fighting it.
Dan
sql
I get this error when run from Query analyser in remote machine
Cannot bulk load because the file <File Name> could not be opened. Operating
system error code 5(Access is denied.)
It used to work in SQL 2000 We just upgradted to SQL 2005.
The same command works fine when run from the local machine hosting the SQL
server it can access the remote file and can bulk isert but when run from
remote machine it does not work. All accounts are admin accounts and we use
Windows authentication and are using valid UNC paths.
Thanks
Kiran
I have this same exact problem, and have been fighting it for three
days. Even when I remove the lines that it says are causing the
problem, when I run it again, it just picks some new lines to fail on.
Check out this thread, where someone posts how they proved that the
problem only exists when using SQL Server 2005 to import the data:
http://forums.microsoft.com/MSDN/Sho...41512&SiteID=1
I'm convinced there is some kind of bug here, and I'm about to give up
on trying to use bulk insert altogether and just write a program to
import the file myself. SQL 2000 DTS can import the same file with no
problem. It's pretty clear there's nothing wrong with the file.
FYI, in an SSIS context, this same exact thing happens when using the
Bulk Insert task and when using the Import Column in a data flow task.
I can reproduce the problem by executing a BULK insert command in a
query window, taking SSIS out of the picture altogether.
If anyone from Microsoft is reading this and is aware of this issue,
please advise. There are clearly multiple people out here fighting it.
Dan
sql
Bulk insert Problem(Bug) in SQL Server 2005
I can not bulk insert a network file onto a remote SQL server 2005 machine
I get this error when run from Query analyser in remote machine
Cannot bulk load because the file <File Name> could not be opened. Operating
system error code 5(Access is denied.)
It used to work in SQL 2000 We just upgradted to SQL 2005.
The same command works fine when run from the local machine hosting the SQL
server it can access the remote file and can bulk isert but when run from
remote machine it does not work. All accounts are admin accounts and we use
Windows authentication and are using valid UNC paths.
Thanks
KiranI have this same exact problem, and have been fighting it for three
days. Even when I remove the lines that it says are causing the
problem, when I run it again, it just picks some new lines to fail on.
Check out this thread, where someone posts how they proved that the
problem only exists when using SQL Server 2005 to import the data:
http://forums.microsoft.com/MSDN/Sh...141512&SiteID=1
I'm convinced there is some kind of bug here, and I'm about to give up
on trying to use bulk insert altogether and just write a program to
import the file myself. SQL 2000 DTS can import the same file with no
problem. It's pretty clear there's nothing wrong with the file.
FYI, in an SSIS context, this same exact thing happens when using the
Bulk Insert task and when using the Import Column in a data flow task.
I can reproduce the problem by executing a BULK insert command in a
query window, taking SSIS out of the picture altogether.
If anyone from Microsoft is reading this and is aware of this issue,
please advise. There are clearly multiple people out here fighting it.
Dan
I get this error when run from Query analyser in remote machine
Cannot bulk load because the file <File Name> could not be opened. Operating
system error code 5(Access is denied.)
It used to work in SQL 2000 We just upgradted to SQL 2005.
The same command works fine when run from the local machine hosting the SQL
server it can access the remote file and can bulk isert but when run from
remote machine it does not work. All accounts are admin accounts and we use
Windows authentication and are using valid UNC paths.
Thanks
KiranI have this same exact problem, and have been fighting it for three
days. Even when I remove the lines that it says are causing the
problem, when I run it again, it just picks some new lines to fail on.
Check out this thread, where someone posts how they proved that the
problem only exists when using SQL Server 2005 to import the data:
http://forums.microsoft.com/MSDN/Sh...141512&SiteID=1
I'm convinced there is some kind of bug here, and I'm about to give up
on trying to use bulk insert altogether and just write a program to
import the file myself. SQL 2000 DTS can import the same file with no
problem. It's pretty clear there's nothing wrong with the file.
FYI, in an SSIS context, this same exact thing happens when using the
Bulk Insert task and when using the Import Column in a data flow task.
I can reproduce the problem by executing a BULK insert command in a
query window, taking SSIS out of the picture altogether.
If anyone from Microsoft is reading this and is aware of this issue,
please advise. There are clearly multiple people out here fighting it.
Dan
Bulk insert Problem(Bug) in SQL Server 2005
I can not bulk insert a network file onto a remote SQL server 2005 machine
I get this error when run from Query analyser in remote machine
Cannot bulk load because the file <File Name> could not be opened. Operating
system error code 5(Access is denied.)
It used to work in SQL 2000 We just upgradted to SQL 2005.
The same command works fine when run from the local machine hosting the SQL
server it can access the remote file and can bulk isert but when run from
remote machine it does not work. All accounts are admin accounts and we use
Windows authentication and are using valid UNC paths.
Thanks
KiranI have this same exact problem, and have been fighting it for three
days. Even when I remove the lines that it says are causing the
problem, when I run it again, it just picks some new lines to fail on.
Check out this thread, where someone posts how they proved that the
problem only exists when using SQL Server 2005 to import the data:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=141512&SiteID=1
I'm convinced there is some kind of bug here, and I'm about to give up
on trying to use bulk insert altogether and just write a program to
import the file myself. SQL 2000 DTS can import the same file with no
problem. It's pretty clear there's nothing wrong with the file.
FYI, in an SSIS context, this same exact thing happens when using the
Bulk Insert task and when using the Import Column in a data flow task.
I can reproduce the problem by executing a BULK insert command in a
query window, taking SSIS out of the picture altogether.
If anyone from Microsoft is reading this and is aware of this issue,
please advise. There are clearly multiple people out here fighting it.
Dan
I get this error when run from Query analyser in remote machine
Cannot bulk load because the file <File Name> could not be opened. Operating
system error code 5(Access is denied.)
It used to work in SQL 2000 We just upgradted to SQL 2005.
The same command works fine when run from the local machine hosting the SQL
server it can access the remote file and can bulk isert but when run from
remote machine it does not work. All accounts are admin accounts and we use
Windows authentication and are using valid UNC paths.
Thanks
KiranI have this same exact problem, and have been fighting it for three
days. Even when I remove the lines that it says are causing the
problem, when I run it again, it just picks some new lines to fail on.
Check out this thread, where someone posts how they proved that the
problem only exists when using SQL Server 2005 to import the data:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=141512&SiteID=1
I'm convinced there is some kind of bug here, and I'm about to give up
on trying to use bulk insert altogether and just write a program to
import the file myself. SQL 2000 DTS can import the same file with no
problem. It's pretty clear there's nothing wrong with the file.
FYI, in an SSIS context, this same exact thing happens when using the
Bulk Insert task and when using the Import Column in a data flow task.
I can reproduce the problem by executing a BULK insert command in a
query window, taking SSIS out of the picture altogether.
If anyone from Microsoft is reading this and is aware of this issue,
please advise. There are clearly multiple people out here fighting it.
Dan
BULK Insert problem with DateTime field
I am trying to perform a bulk insert in SQL Query analyzer. This is the Schema for my table:
Taxonomic_Units Table:INT 4 tsn
CHAR 1 unit_ind1
CHAR 35 unit_name1
CHAR 1 unit_ind2
CHAR 34 unit_name2
CHAR 7 unit_ind3
CHAR 35 unit_name3
CHAR 7 unit_ind4
CHAR 35 unit_name4
CHAR 1 unnamed_taxon_ind
CHAR 12 usage
CHAR 50 unaccept_reason
CHAR 40 credibility_rtng
CHAR 10 completeness_rtng
CHAR 7 currency_rating
SMALLINT 2 phylo_sort_seq
DATETIME 8 initial_time_stamp
INT 4 parent_tsn
INT 4 taxon_author_id
INT 4 hybrid_author_id
SMALLINT 2 kingdom_id
SMALLINT 2 rank_id
DATETIME 4 update_date
CHAR 3 uncertain_prnt_ind
I use the following SQL Statement to BULK INSERT:
BULK INSERT itis.taxonomic_units
FROM '<dir path to input file>/taxonomic_units.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n',
KEEPIDENTITY,
KEEPNULLS
)
Here is a sample of a row that I get an error when it is processed through the above BULK INSERT statement:
50||Bacteria||||||||invalid||No review; untreated NODC data|unknown|unknown||1996-06-13 14:51:08.0||||1|10|07/29/1996||
The error is:Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 17 (initial_time_stamp).
Just to make things easier on anyone who tries to help me solve this problem, the field that causes my Bulk insert statement to choke contains the data: "1996-06-13 14:51:08.0". Why is this happening? Any thoughts on how to solve it? I have been scouring help articles all day with no resolution to this problem.
For me, that works just fine...llzamboni wrote:
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 17 (initial_time_stamp).Just to make things easier on anyone who tries to help me solve this problem, the field that causes my Bulk insert statement to choke contains the data: "1996-06-13 14:51:08.0". Why is this happening? Any thoughts on how to solve it? I have been scouring help articles all day with no resolution to this problem.
SELECT CAST('1996-06-13 14:51:08.0' AS DATETIME)
Are you *sure* that row is the problem?
|||It seems like BULK INSERT is having difficulties with the (sort of) malformed date 1996-06-13 14:51:08.0
If you remove the last .0 or add two zeroes so it becomes .000 then BULK INSERT will insert the row.
However, if you instead use BCP, no changes are needed.
Apparently, BCP isn't as cranky as BULK INSERT in this case.
/Kenneth
bulk insert problem - invalid collation name
Hi,
I keep getting an error when trying to bulk insert a flat file from
query analyzer using a format file.
Here is a sample of the flat file:
"N435",2004-08-31,"BHX","Palma","PMI","Mediterranean","xxxxxxxxxxxxxxxx",14,"KK","Inside
Twin, Shower","","","",993,"009"
"N435",2004-08-31,"BHX","Palma","PMI","Mediterranean","xxxxxxxxxxxxxxxxxx",14,"LL","Inside
Twin, Shower","","","",993,"004"
"N435",2004-08-31,"BHX","Palma","PMI","Mediterranean","xxxxxxxxxxxxxxxxxx",14,"MM","Inside
Twin, Shower","","","",993,"001"
Here is my format file:
8.0
16
1 SQLINT 0 4 "" 1 ID ""
2 SQLCHAR 0 255 "\"," 2
Cruise ""
3 SQLCHAR 0 255 ",\"" 3
Departure_Date ""
4 SQLCHAR 0 255 "\",\"" 4
Airport ""
5 SQLCHAR 0 255 "\",\"" 5
Resort ""
6 SQLCHAR 0 255 "\",\"" 6
Resort_Code ""
7 SQLCHAR 0 255 "\",\"" 7
Region ""
8 SQLCHAR 0 255 "\"," 8
Voyage_name ""
9 SQLCHAR 0 255 ",\"" 9 Duration ""
10 SQLCHAR 0 255 "\",\"" 10 Cabin_Grade
""
11 SQLCHAR 0 255 "\",\"" 11 Cabin_Type ""
12 SQLCHAR 0 255 "\",\"" 12 Hotel_Name ""
13 SQLCHAR 0 255 "\",\"" 13 Tour_definition
""
14 SQLCHAR 0 255 "\"," 14 Hotel_Grade ""
15 SQLCHAR 0 255 ",\"" 15 Price ""
16 SQLCHAR 0 255 "\"\n" 16 Available_Quantity
""
The first column id is not present in the flat file but even if i dont
include this column i still get errors. Any help appreciated.
AllyAlison,
I believe that it is saying that it does not like the collation of "".
Try replacing the "" collation with nothing at all if you want to try that
approach. Or include a collation such as SQL_Latin1_General_Cp1_CI_AS.
Note that the collation is not placed inside of quotes.
Russell Fields
> Here is my format file:
> 8.0
> 16
> 1 SQLINT 0 4 "" 1 ID
> 2 SQLCHAR 0 255 "\"," 2
> Cruise SQL_Latin1_General_Cp1_CI_ASsql
I keep getting an error when trying to bulk insert a flat file from
query analyzer using a format file.
Here is a sample of the flat file:
"N435",2004-08-31,"BHX","Palma","PMI","Mediterranean","xxxxxxxxxxxxxxxx",14,"KK","Inside
Twin, Shower","","","",993,"009"
"N435",2004-08-31,"BHX","Palma","PMI","Mediterranean","xxxxxxxxxxxxxxxxxx",14,"LL","Inside
Twin, Shower","","","",993,"004"
"N435",2004-08-31,"BHX","Palma","PMI","Mediterranean","xxxxxxxxxxxxxxxxxx",14,"MM","Inside
Twin, Shower","","","",993,"001"
Here is my format file:
8.0
16
1 SQLINT 0 4 "" 1 ID ""
2 SQLCHAR 0 255 "\"," 2
Cruise ""
3 SQLCHAR 0 255 ",\"" 3
Departure_Date ""
4 SQLCHAR 0 255 "\",\"" 4
Airport ""
5 SQLCHAR 0 255 "\",\"" 5
Resort ""
6 SQLCHAR 0 255 "\",\"" 6
Resort_Code ""
7 SQLCHAR 0 255 "\",\"" 7
Region ""
8 SQLCHAR 0 255 "\"," 8
Voyage_name ""
9 SQLCHAR 0 255 ",\"" 9 Duration ""
10 SQLCHAR 0 255 "\",\"" 10 Cabin_Grade
""
11 SQLCHAR 0 255 "\",\"" 11 Cabin_Type ""
12 SQLCHAR 0 255 "\",\"" 12 Hotel_Name ""
13 SQLCHAR 0 255 "\",\"" 13 Tour_definition
""
14 SQLCHAR 0 255 "\"," 14 Hotel_Grade ""
15 SQLCHAR 0 255 ",\"" 15 Price ""
16 SQLCHAR 0 255 "\"\n" 16 Available_Quantity
""
The first column id is not present in the flat file but even if i dont
include this column i still get errors. Any help appreciated.
AllyAlison,
I believe that it is saying that it does not like the collation of "".
Try replacing the "" collation with nothing at all if you want to try that
approach. Or include a collation such as SQL_Latin1_General_Cp1_CI_AS.
Note that the collation is not placed inside of quotes.
Russell Fields
> Here is my format file:
> 8.0
> 16
> 1 SQLINT 0 4 "" 1 ID
> 2 SQLCHAR 0 255 "\"," 2
> Cruise SQL_Latin1_General_Cp1_CI_ASsql
Bulk insert problem
Hey,
I am trying to do a bulk insert from a txt file. I am trying to allow the query to read the txt file off the users local computer. This is the code I am using:
BULK INSERT TbleTestBulk
FROM 'C:\westportela.dat'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
I also tried this
BULK INSERT TbleTestBulk
FROM '\\host_name\C$\westportela.dat'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
and this
BULK INSERT macomber
FROM '\\wcsserver\SSP\txt frm mms\WES.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
none work.
I basically get this error for each:
Server: Msg 4861, Level 16, State 1, Line 1
Could not bulk insert because file '\\wcsserver\SSP\txt frm mms\WES.txt' could not be opened. Operating system error code 53(The network path was not found.).
Anyone got any suggestions. I am really new to this so all help would be welcomed.
Thanks
MikeDid the client share that location?
Why not copy the file to the server...I would suggest that that is the preferred method...you would want to take the network out of the equation when loading or dumping data...
My own opinion (MOO) *
* Actually not really...it's in a few books I've read
I am trying to do a bulk insert from a txt file. I am trying to allow the query to read the txt file off the users local computer. This is the code I am using:
BULK INSERT TbleTestBulk
FROM 'C:\westportela.dat'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
I also tried this
BULK INSERT TbleTestBulk
FROM '\\host_name\C$\westportela.dat'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
and this
BULK INSERT macomber
FROM '\\wcsserver\SSP\txt frm mms\WES.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
none work.
I basically get this error for each:
Server: Msg 4861, Level 16, State 1, Line 1
Could not bulk insert because file '\\wcsserver\SSP\txt frm mms\WES.txt' could not be opened. Operating system error code 53(The network path was not found.).
Anyone got any suggestions. I am really new to this so all help would be welcomed.
Thanks
MikeDid the client share that location?
Why not copy the file to the server...I would suggest that that is the preferred method...you would want to take the network out of the equation when loading or dumping data...
My own opinion (MOO) *
* Actually not really...it's in a few books I've read
Bulk Insert Problem
Hi
I am trying to do a bulk insert using Query Analyzer for SQL 2000.
I have a Comma delimited file and a format file that i genrated using bcp.exe
the CSV file looks like this
08009700000,23,01,2007,23012007,16:28:09,01413412066,01,FreeConnect,00:00:05,0:05,0.083,0.002,2006,X
08009700000,24,01,2007,24012007,12:51:55,01413412066,01,FreeConnect,00:00:14,0:14,0.233,0.006,2006,X
08009700000,24,01,2007,24012007,12:52:28,01413412066,01,FreeConnect,00:00:10,0:10,0.167,0.004,2006,X
the format file look like this
8.0
15
1 SQLNCHAR 2 510 "," 1 Field1 Latin1_General_CI_AS
2 SQLNCHAR 2 510 "," 2 Field2 Latin1_General_CI_AS
3 SQLNCHAR 2 510 "," 3 Field3 Latin1_General_CI_AS
4 SQLNCHAR 2 510 "," 4 Field4 Latin1_General_CI_AS
5 SQLNCHAR 2 510 "," 5 Field5 Latin1_General_CI_AS
6 SQLNCHAR 2 510 "," 6 Field6 Latin1_General_CI_AS
7 SQLNCHAR 2 510 "," 7 Field7 Latin1_General_CI_AS
8 SQLNCHAR 2 510 "," 8 Field8 Latin1_General_CI_AS
9 SQLNCHAR 2 510 "," 9 Field9 Latin1_General_CI_AS
10 SQLNCHAR 2 510 "," 10 Field10 Latin1_General_CI_AS
11 SQLNCHAR 2 510 "," 11 Field11 Latin1_General_CI_AS
12 SQLNCHAR 2 510 "," 12 Field12 Latin1_General_CI_AS
13 SQLNCHAR 2 510 "," 13 Field13 Latin1_General_CI_AS
14 SQLNCHAR 2 510 "," 14 Field14 Latin1_General_CI_AS
15 SQLNCHAR 2 510 "," 15 Field15 Latin1_General_CI_AS
and the T-SQL code that i am using is
bulk insert RawCallData from 'C:\Temp\85005313_870.csv'
with
(FORMATFILE ='c:\Temp\yourcomms.fmt')
i have also tried
bulk insert RawCallData from 'C:\Temp\85005313_870.csv'
with
(FORMATFILE ='c:\Temp\yourcomms.fmt',ROWTERMINATOR = '\r\n')
and
bulk insert RawCallData from 'C:\Temp\85005313_870.csv'
with
(FORMATFILE ='c:\Temp\yourcomms.fmt',ROWTERMINATOR = '\r')
what ever i do i get the get the follow error message
Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].
The statement has been terminated.
can any one help ?
I haven't read through your post completely, because I'm too busy today. But...In Enterprise Manager (or Management Studio), use the 'Import Data' menu entry to go through a wizard to import your data. Use the option to save the package, or in MgtStudio, script it out. Then you can re-use the package/script when you need to. But hopefully you will find the answer to your problem when you step through the wizard and it tells you useful information like "I can't find the end of the line 10 lines in...".
Actually - having skimmed through, it sounds like it can't find the commas properly, or else your columns are too short. Probably the first though, because I imagine you would've noticed if your columns were short. The wizard should really help you here.
Hope this helps,
Rob|||
hi and thanks for the reply.
I have been playing around with this and i have now got round the error that i was getting.
The problem seems to be in the format file that i am using, which is odd cause it was genarated using the bcp.
I found 2 problems in this file
the first which caused the error about the first column being two long was the column prefix was set to 2, when i changed this to 0 for each column the error went away.
then then got a new error about unexpected end of line, I solved this buy removing the comma on the last line of the format file and replaceing it with \r\n
so my format file now looks like this
8.0
15
1 SQLNCHAR 0 510 "," 1 Field1 Latin1_General_CI_AS
2 SQLNCHAR 0 510 "," 2 Field2 Latin1_General_CI_AS
...
15 SQLNCHAR 0 510 "\r\n" 15 Field15 Latin1_General_CI_AS
it now imports all the data, however it also seems to corrupt the data, if i do a select statement in QA the data looks like it is in the wrong font as it is all white empty boxes.
any one know why this happens ?
|||Dagz,You got two of three issues answered, it looks like (changing the 2 to 0 and changing the final field terminator). If your file is Unicode, though, which I'm guessing it is from the SQLNCHAR in the format file, I think you need to specify the field terminators differently. Try this as the format file (or try SQLCHAR instead of SQLNCHAR, if your file is not a Unicode file):
8.0
15
1 SQLNCHAR 0 510 ",\x00" 1 Field1 Latin1_General_CI_AS
2 SQLNCHAR 0 510 ",\x00" 2 Field2 Latin1_General_CI_AS
3 SQLNCHAR 0 510 ",\x00" 3 Field3 Latin1_General_CI_AS
4 SQLNCHAR 0 510 ",\x00" 4 Field4 Latin1_General_CI_AS
5 SQLNCHAR 0 510 ",\x00" 5 Field5 Latin1_General_CI_AS
6 SQLNCHAR 0 510 ",\x00" 6 Field6 Latin1_General_CI_AS
7 SQLNCHAR 0 510 ",\x00" 7 Field7 Latin1_General_CI_AS
8 SQLNCHAR 0 510 ",\x00" 8 Field8 Latin1_General_CI_AS
9 SQLNCHAR 0 510 ",\x00" 9 Field9 Latin1_General_CI_AS
10 SQLNCHAR 0 510 ",\x00" 10 Field10 Latin1_General_CI_AS
11 SQLNCHAR 0 510 ",\x00" 11 Field11 Latin1_General_CI_AS
12 SQLNCHAR 0 510 ",\x00" 12 Field12 Latin1_General_CI_AS
13 SQLNCHAR 0 510 ",\x00" 13 Field13 Latin1_General_CI_AS
14 SQLNCHAR 0 510 ",\x00" 14 Field14 Latin1_General_CI_AS
15 SQLNCHAR 0 510 "\x0D\x00\x0A\x00" 15 Field15 Latin1_General_CI_AS
Steve Kass
http://www.stevekass.com
sql
Bulk Insert Problem
Hi
I am trying to do a bulk insert using Query Analyzer for SQL 2000.
I have a Comma delimited file and a format file that i genrated using bcp.exe
the CSV file looks like this
08009700000,23,01,2007,23012007,16:28:09,01413412066,01,FreeConnect,00:00:05,0:05,0.083,0.002,2006,X
08009700000,24,01,2007,24012007,12:51:55,01413412066,01,FreeConnect,00:00:14,0:14,0.233,0.006,2006,X
08009700000,24,01,2007,24012007,12:52:28,01413412066,01,FreeConnect,00:00:10,0:10,0.167,0.004,2006,X
the format file look like this
8.0
15
1 SQLNCHAR 2 510 "," 1 Field1 Latin1_General_CI_AS
2 SQLNCHAR 2 510 "," 2 Field2 Latin1_General_CI_AS
3 SQLNCHAR 2 510 "," 3 Field3 Latin1_General_CI_AS
4 SQLNCHAR 2 510 "," 4 Field4 Latin1_General_CI_AS
5 SQLNCHAR 2 510 "," 5 Field5 Latin1_General_CI_AS
6 SQLNCHAR 2 510 "," 6 Field6 Latin1_General_CI_AS
7 SQLNCHAR 2 510 "," 7 Field7 Latin1_General_CI_AS
8 SQLNCHAR 2 510 "," 8 Field8 Latin1_General_CI_AS
9 SQLNCHAR 2 510 "," 9 Field9 Latin1_General_CI_AS
10 SQLNCHAR 2 510 "," 10 Field10 Latin1_General_CI_AS
11 SQLNCHAR 2 510 "," 11 Field11 Latin1_General_CI_AS
12 SQLNCHAR 2 510 "," 12 Field12 Latin1_General_CI_AS
13 SQLNCHAR 2 510 "," 13 Field13 Latin1_General_CI_AS
14 SQLNCHAR 2 510 "," 14 Field14 Latin1_General_CI_AS
15 SQLNCHAR 2 510 "," 15 Field15 Latin1_General_CI_AS
and the T-SQL code that i am using is
bulk insert RawCallData from 'C:\Temp\85005313_870.csv'
with
(FORMATFILE ='c:\Temp\yourcomms.fmt')
i have also tried
bulk insert RawCallData from 'C:\Temp\85005313_870.csv'
with
(FORMATFILE ='c:\Temp\yourcomms.fmt',ROWTERMINATOR = '\r\n')
and
bulk insert RawCallData from 'C:\Temp\85005313_870.csv'
with
(FORMATFILE ='c:\Temp\yourcomms.fmt',ROWTERMINATOR = '\r')
what ever i do i get the get the follow error message
Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].
The statement has been terminated.
can any one help ?
I haven't read through your post completely, because I'm too busy today. But...
In Enterprise Manager (or Management Studio), use the 'Import Data' menu entry to go through a wizard to import your data. Use the option to save the package, or in MgtStudio, script it out. Then you can re-use the package/script when you need to. But hopefully you will find the answer to your problem when you step through the wizard and it tells you useful information like "I can't find the end of the line 10 lines in...".
Actually - having skimmed through, it sounds like it can't find the commas properly, or else your columns are too short. Probably the first though, because I imagine you would've noticed if your columns were short. The wizard should really help you here.
Hope this helps,
Rob|||
hi and thanks for the reply.
I have been playing around with this and i have now got round the error that i was getting.
The problem seems to be in the format file that i am using, which is odd cause it was genarated using the bcp.
I found 2 problems in this file
the first which caused the error about the first column being two long was the column prefix was set to 2, when i changed this to 0 for each column the error went away.
then then got a new error about unexpected end of line, I solved this buy removing the comma on the last line of the format file and replaceing it with \r\n
so my format file now looks like this
8.0
15
1 SQLNCHAR 0 510 "," 1 Field1 Latin1_General_CI_AS
2 SQLNCHAR 0 510 "," 2 Field2 Latin1_General_CI_AS
...
15 SQLNCHAR 0 510 "\r\n" 15 Field15 Latin1_General_CI_AS
it now imports all the data, however it also seems to corrupt the data, if i do a select statement in QA the data looks like it is in the wrong font as it is all white empty boxes.
any one know why this happens ?
|||Dagz,
You got two of three issues answered, it looks like (changing the 2 to 0 and changing the final field terminator). If your file is Unicode, though, which I'm guessing it is from the SQLNCHAR in the format file, I think you need to specify the field terminators differently. Try this as the format file (or try SQLCHAR instead of SQLNCHAR, if your file is not a Unicode file):
8.0
15
1 SQLNCHAR 0 510 ",\x00" 1 Field1 Latin1_General_CI_AS
2 SQLNCHAR 0 510 ",\x00" 2 Field2 Latin1_General_CI_AS
3 SQLNCHAR 0 510 ",\x00" 3 Field3 Latin1_General_CI_AS
4 SQLNCHAR 0 510 ",\x00" 4 Field4 Latin1_General_CI_AS
5 SQLNCHAR 0 510 ",\x00" 5 Field5 Latin1_General_CI_AS
6 SQLNCHAR 0 510 ",\x00" 6 Field6 Latin1_General_CI_AS
7 SQLNCHAR 0 510 ",\x00" 7 Field7 Latin1_General_CI_AS
8 SQLNCHAR 0 510 ",\x00" 8 Field8 Latin1_General_CI_AS
9 SQLNCHAR 0 510 ",\x00" 9 Field9 Latin1_General_CI_AS
10 SQLNCHAR 0 510 ",\x00" 10 Field10 Latin1_General_CI_AS
11 SQLNCHAR 0 510 ",\x00" 11 Field11 Latin1_General_CI_AS
12 SQLNCHAR 0 510 ",\x00" 12 Field12 Latin1_General_CI_AS
13 SQLNCHAR 0 510 ",\x00" 13 Field13 Latin1_General_CI_AS
14 SQLNCHAR 0 510 ",\x00" 14 Field14 Latin1_General_CI_AS
15 SQLNCHAR 0 510 "\x0D\x00\x0A\x00" 15 Field15 Latin1_General_CI_AS
Steve Kass
http://www.stevekass.com
Monday, March 19, 2012
Bulk insert is all or nothing?
Hi, I ran query 'Bulk insert mytable from myFile with (FIRE_TRIGGERS)'.
The problems are,
1.It does nothing if exist duplication key records. There is no option to skip the duplications. What else is possible to skip duplicated insertion and go next record when bulk insert?
2. The triggers are not fired even if bulk insert success.
My goal is that very large data should be loaded at once and use trigger.
The trigger is fired after bulk insert executed.
But, still remained some problems.
The result of executing trigger is affected only the last record of source data file. It seems that the trigger definition has logical or symantical problems. Any one help is very appreciated.
Here is my definition of trigger.
Can I control all of the inserted recorcd in trigger definition?
create table myTable
(
id int,
value1 int,
value2 int,
primary key(id)
);
create table myTempTable
(
id int,
value1 int,
value2 int
);
CREATE TRIGGER mytrigger ON myTempTable
INSTEAD OF INSERT
AS
BEGIN
PRINT 'trigger mytrigger'
DECLARE @.id AS int, @.value1 AS int, @.value2 AS int
SELECT @.id = INSERTED.id,
@.value1 = INSERTED.value1,
@.value2 = INSERTED.value2
FROM INSERTED
PRINT @.id
PRINT @.value1
PRINT @.value2
IF EXISTS(SELECT * FROM myTable WHERE id = @.id)
BEGIN
PRINT 'trigger EXISTS'
PRINT 'UPDATE'
UPDATE myTable SET value = value + @.value,outbKbps =outbKbps + @.outbKbps WHERE id=@.id
END
ELSE
BEGIN
PRINT 'trigger NOT EXISTS'
PRINT 'trigger INSERT'
INSERT INTO myTableVALUES(@.id,@.value1,@.value2)
END
END
GO
bulk insert myTempTable from 'c:\bulk_myTable.dat' with (FIRE_TRIGGERS);
-- bulk_myTable.dat file's contents --
1 100 200
1 100 200
1 100 200
1 100 200
1 100 200
From Books Online, topic: "Using bcp and BULK INSERT"
Triggers are fired once for each batch in the bulk copy operation. The inserted table passed to the trigger contains all of the rows inserted by the batch. Specify FIRE_TRIGGERS only when bulk copying into a table with INSERT and INSTEAD OF triggers that support multiple row inserts.
Your trigger fires only one time for the entire batch -not for each row in the batch.
Your trigger 'assumes' there is, and is designed to handle, only one row in the inserted table, and therefore will NOT do as you desire. It couldn't possibly work for a bulk insert of more than one row.
You should 're-design' the trigger to handle multiple rows at a time. If you provide, in a new posting, the table DDL, some sample data in the form of insert statements, and a statement of what you are attempting to accomplish, perhaps some here can help you create a trigger that will work for you.
Hopefully, this answered your question.
|||I solved the number of 2 from my question in this thread.
I use the 'AFTER' instead of 'INSTEAD OF' trigger for getting the bulk insert result record.
And, in trigger definition, using the cursor, get the recordset that I want to get.
In the end, insert or update to the target table record by record.
I got this as hint from your commant. Thank you very much.
Here is the sample.
CREATE TRIGGER TRG_MY_TABLE ON MyTempTable
AFTER INSERT
AS
BEGIN
DECLARE @.id AS INT,
@.value1 AS INT,
@.value2 AS INT
DECLARE CursorMyTable CURSOR FOR
SELECT id AS cur_id, value1 AS cur_value1, value2 AS cur_value2
FROM MyTempTable
OPEN CursorMyTable
FETCH NEXT FROM CursorMyTable
INTO @.id,@.value1,@.value2
-
IF EXISTS(SELECT * FROM MyTable WHERE id=@.id)
BEGIN
UPDATE MyTable
SET value1= value1+ @.value1,
value2= value2+ @.value2
WHERE id=@.id
END
ELSE
BEGIN
INSERT INTO MyTable VALUES(@.id,@.value1,@.value2)
END
-
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM CursorMyTable
INTO @.id,@.value1,@.value2
IF EXISTS(SELECT * FROM MyTable WHERE id=@.id)
BEGIN
UPDATE MyTable
SET value1= value1+ @.value1,
value2= value2+ @.value2
WHERE id=@.id
END
ELSE
BEGIN
INSERT INTO MyTable VALUES(@.id,@.value1,@.value2)
END
-
END
CLOSE CursorMyTable
DEALLOCATE CursorMyTable
END
I would highly recommend NOT using a CURSOR in a TRIGGER.
It would be far better to load the data into a 'Staging' table, and then process the data as needed. Stepping through a CURSOR in the context of a TRIGGER (meaning during a TRANSACTION) will hold a lot of locks and resources -most likely, unnecessarily.
Just not a good idea.
|||Thank you, but could you more explain about the 'Staging' table?|||A 'Staging' table most likely would have the same columns as the final production table -but datatypes may be different. Data is loaded into the Staging table, and then cleansed, conformed, and moved to the production table.
This is a very common ETL operation.
Friday, February 24, 2012
Bulk Insert
Hi,
I'm trying to use bulk insert to import a unformated text file into a table
with 6 columns using the following code in query analyzer:
BULK INSERT tblLocalizerTemp from 'c:\temp\textfile.txt'
WITH (CODEPAGE='RAW', ROWTERMINATOR='\n')
Since I don't have column delimiters, I left out FIELDTERMINATOR but I'm
getting an error saying that the column from the text file is too long. How
can I specify how this gets imported. The docs are not very helpful. Also,
is there a way to just import specific bytes of the file into the table
without having to import the whole record.
ThanksI got this to work with the following but now I'm having trouble using a
variable to use may my path information. I want to eventually call a stored
proc from a VB6 app
declare @.cLocalizerPath varchar(100)
declare @.cFmtPath varchar(100)
select @.cLocalizerPath = 'c:\temp\temp.txt'
select @.cFmtPath = 'c:\temp\temp.fmt'
BULK INSERT tblLocalizerTemp from @.cLocalizerPath
WITH (CODEPAGE='RAW', FORMATFILE=@.cFmtPath)
"Ellie" <nospam@.nospam.net> wrote in message
news:OsWLJZjPGHA.420@.tk2msftngp13.phx.gbl...
> Hi,
> I'm trying to use bulk insert to import a unformated text file into a
> table with 6 columns using the following code in query analyzer:
> BULK INSERT tblLocalizerTemp from 'c:\temp\textfile.txt'
> WITH (CODEPAGE='RAW', ROWTERMINATOR='\n')
> Since I don't have column delimiters, I left out FIELDTERMINATOR but I'm
> getting an error saying that the column from the text file is too long.
> How can I specify how this gets imported. The docs are not very helpful.
> Also, is there a way to just import specific bytes of the file into the
> table without having to import the whole record.
> Thanks
>
I'm trying to use bulk insert to import a unformated text file into a table
with 6 columns using the following code in query analyzer:
BULK INSERT tblLocalizerTemp from 'c:\temp\textfile.txt'
WITH (CODEPAGE='RAW', ROWTERMINATOR='\n')
Since I don't have column delimiters, I left out FIELDTERMINATOR but I'm
getting an error saying that the column from the text file is too long. How
can I specify how this gets imported. The docs are not very helpful. Also,
is there a way to just import specific bytes of the file into the table
without having to import the whole record.
ThanksI got this to work with the following but now I'm having trouble using a
variable to use may my path information. I want to eventually call a stored
proc from a VB6 app
declare @.cLocalizerPath varchar(100)
declare @.cFmtPath varchar(100)
select @.cLocalizerPath = 'c:\temp\temp.txt'
select @.cFmtPath = 'c:\temp\temp.fmt'
BULK INSERT tblLocalizerTemp from @.cLocalizerPath
WITH (CODEPAGE='RAW', FORMATFILE=@.cFmtPath)
"Ellie" <nospam@.nospam.net> wrote in message
news:OsWLJZjPGHA.420@.tk2msftngp13.phx.gbl...
> Hi,
> I'm trying to use bulk insert to import a unformated text file into a
> table with 6 columns using the following code in query analyzer:
> BULK INSERT tblLocalizerTemp from 'c:\temp\textfile.txt'
> WITH (CODEPAGE='RAW', ROWTERMINATOR='\n')
> Since I don't have column delimiters, I left out FIELDTERMINATOR but I'm
> getting an error saying that the column from the text file is too long.
> How can I specify how this gets imported. The docs are not very helpful.
> Also, is there a way to just import specific bytes of the file into the
> table without having to import the whole record.
> Thanks
>
Thursday, February 16, 2012
builtin\admin
Our servers are in mixed mode.
I have about 10 Window NT accounts.
if i log in domain\myacocunt into windows NT then i bring up SQL Query and do connect with Windows NT i can do what ever i like inside of sql i can delete add etc just like being the sa.
We have the builtin\admin enabled.
The question is i thought i had to have domain\myaccount in sql server logins regardless so that it goes windows nt authentication then sql authentication but it looks like i don't.
In order to take control....i need to have domain\myaccount only access DatabaseA
So to get this working.........do i just remove builtin\admin from security, server roles, system adminsitration......
Also i would like to know why not having a nt login inside can allow user to do what they like.
If you are a local administrator, you will get SQL Server access by virtue of being a member of Builtin\Administrators. It is sufficient for a group you are a member of to be granted access to SQL Server, for you to get access as well. This allows administrators to grant access to a Windows group instead of individually granting access to each group member.
Removing the Builtin\Administrators login will prevent local administrators from getting in, unless a login was created for them or some group they belonged to was given access. But note that a local administrator can always connect, if he can start the server in single-user mode - this is allowed to prevent an administrator from locking himself out.
Thanks
Laurentiu
Thanks for replying.
So if i put the new login domain/startupsql as the administrator on the SQL server.
In sql server i create a new login domain/startupsql add server role of system administrator and then
remove builtin/admin from security server roles system administrator...do i have to delete from security logins or ok to leave here if done remove above.
So the start up will be domain/startupsql.
Do i change the properties on EM or can i stop and start by chainging connections in windows ....services.
Is that about all i have to do.........do you know what this NT Authority server account is and why i need that and what are the server roles.
When i view details of builtin/adimin it have evey server roles selected and every database do i need to do that with the new domain/startupsql
|||I'm not sure I understand what you are trying to do.
You can remove builtin\administrators if you don't want other local administrators to connect.
Do you want to change the service account as well? I don't understand what you mean by "So the start up will be domain/startupsql."
The NT Authority account is most likely the entry for your current service account. Server roles are explained here: http://msdn2.microsoft.com/en-US/library/ms188659.aspx.
Thanks
Laurentiu
Right now all our server administrators can go into SQL and do everything...so i need to remove builtin/administrators from security, server roles, system administrators and remove builtin
From what i understand is that...when you click on SERVER properites in EM under, Security you have start up service account and it should not be the system account but be a account that is a domain/newaccount to start up SQL.
I am wondering what else i need to be aware of before turning this off...i read it may not start SQl and such..
Thanks
|||If you don't want to have administrators accessing your server, just remove the Builtin\Administrators builtin. This will prevent direct access to the server. You don't need to worry about server roles, just removing the group with "DROP LOGIN [Builtin\Administrators]" is sufficient.
The service account is a different thing - it's the account under which the SQL Server service is running. Configuring this is external to SQL Server and is unrelated to whether you have or not the Builtin\Administrators login present.
Of course, you should try all these ideas in a test environment until you're happy with the result, before attempting to do configuration changes on your main system.
I assume you're using SQL Server 2000 - if that's the case, note that if you remove Builtin\Administrators and you have no other sysadmin access, you may lock yourself out of the server.
Thanks
Laurentiu
drop...u mean just go to security, server roles, system administrations and do remove buitlin/administrators.....does sql need this for anything else...do i need to replace it with a new login ? what does this do except allow the administrators of windows to do use sql like sa.
Yes the part u mention re to sysadmin access....- i have set up the domain/sql account as system administrators and myself aswell...do i need a just a regular sql account that has security, server roles, system administrators flag set aswell.
I was under the impression in the server properties the start up settings that it should not be a system account (not sure what system account the default sql uses) but i need to change that to domain/sql.
how is your server configured.
Tuesday, February 14, 2012
Building/Issueing a query with a field with a quote in it?
Using a language that using A4GL to connect to the database. From within the language(COBOL) we have the ability to add a simple query. All is fine with one exception. Some of the fields might have a quote mark in it.
we have a field in particular that a lot of our clients like to put an apostrophe in. such as 0001A'06, 2'11" and so on. Now from inside our programs and using the simple query we add something like 'where ap_id = '0001A'06' which i know is an error but is there a way around this or a way to make it work?You can use REPLACE on the string to change each single quote to two single quotes (NOT the "double-quote" character). But is looks like you are using direct dynamic SQL, and that is an invitation to SQL injection security attacks.|||That works all we have to do now is fix all of programs, (possible 800 or so).
Thanks again...|||COBOL and SQL Server?
It's an abomination I tells ya.....
we have a field in particular that a lot of our clients like to put an apostrophe in. such as 0001A'06, 2'11" and so on. Now from inside our programs and using the simple query we add something like 'where ap_id = '0001A'06' which i know is an error but is there a way around this or a way to make it work?You can use REPLACE on the string to change each single quote to two single quotes (NOT the "double-quote" character). But is looks like you are using direct dynamic SQL, and that is an invitation to SQL injection security attacks.|||That works all we have to do now is fix all of programs, (possible 800 or so).
Thanks again...|||COBOL and SQL Server?
It's an abomination I tells ya.....
Building SQL Query to fill a treeview control with Year->Month->Day nodes
Hello,
I have load dates for each row in my database table. I'm trying to build a
query that will give me all of the unique years, then all of the unique
months for that year, then finally all of the unique days for that month.
I'm trying to load a treeview control with the results like so.
Year
|_
Month
|_
Day
Day
Etc.
Year
|_
Month
|_
Day
Etc.
If anyone has any ideas or experience on how to do this I would appreciate
it.
Thanks
You could simply write a query that does a join with a calendar table.
http://www.aspfaq.com/2519
http://www.aspfaq.com/
(Reverse address to reply.)
"MJB" <mb2@.email.com> wrote in message
news:euAyUyLcEHA.2476@.TK2MSFTNGP09.phx.gbl...
> Hello,
>
> I have load dates for each row in my database table. I'm trying to build
a
> query that will give me all of the unique years, then all of the unique
> months for that year, then finally all of the unique days for that month.
>
> I'm trying to load a treeview control with the results like so.
>
> Year
> |_
> Month
> |_
> Day
> Day
> Etc.
> Year
> |_
> Month
> |_
> Day
> Etc.
>
> If anyone has any ideas or experience on how to do this I would appreciate
> it.
>
> Thanks
>
|||Hmm, looks like it could help. Unfortunately, I only want my treeview to
show the dates that are actually in the database, where as this approach
seems to just show all dates between a range.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eSYmS0LcEHA.3480@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> You could simply write a query that does a join with a calendar table.
> http://www.aspfaq.com/2519
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "MJB" <mb2@.email.com> wrote in message
> news:euAyUyLcEHA.2476@.TK2MSFTNGP09.phx.gbl...
build[vbcol=seagreen]
> a
month.[vbcol=seagreen]
appreciate
>
|||Can you show your table structure (see http://www.aspfaq.com/5006)?
http://www.aspfaq.com/
(Reverse address to reply.)
"MJB" <mb2@.email.com> wrote in message
news:#jGPjPMcEHA.3476@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hmm, looks like it could help. Unfortunately, I only want my treeview to
> show the dates that are actually in the database, where as this approach
> seems to just show all dates between a range.
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:eSYmS0LcEHA.3480@.TK2MSFTNGP11.phx.gbl...
> build
unique
> month.
> appreciate
>
|||Well, it's nothing special. It's just a table with a Primary Key with a few
other columns and a LoadDT column that is of type datetime.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u5DN4RMcEHA.2840@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Can you show your table structure (see http://www.aspfaq.com/5006)?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "MJB" <mb2@.email.com> wrote in message
> news:#jGPjPMcEHA.3476@.tk2msftngp13.phx.gbl...
to
> unique
>
I have load dates for each row in my database table. I'm trying to build a
query that will give me all of the unique years, then all of the unique
months for that year, then finally all of the unique days for that month.
I'm trying to load a treeview control with the results like so.
Year
|_
Month
|_
Day
Day
Etc.
Year
|_
Month
|_
Day
Etc.
If anyone has any ideas or experience on how to do this I would appreciate
it.
Thanks
You could simply write a query that does a join with a calendar table.
http://www.aspfaq.com/2519
http://www.aspfaq.com/
(Reverse address to reply.)
"MJB" <mb2@.email.com> wrote in message
news:euAyUyLcEHA.2476@.TK2MSFTNGP09.phx.gbl...
> Hello,
>
> I have load dates for each row in my database table. I'm trying to build
a
> query that will give me all of the unique years, then all of the unique
> months for that year, then finally all of the unique days for that month.
>
> I'm trying to load a treeview control with the results like so.
>
> Year
> |_
> Month
> |_
> Day
> Day
> Etc.
> Year
> |_
> Month
> |_
> Day
> Etc.
>
> If anyone has any ideas or experience on how to do this I would appreciate
> it.
>
> Thanks
>
|||Hmm, looks like it could help. Unfortunately, I only want my treeview to
show the dates that are actually in the database, where as this approach
seems to just show all dates between a range.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eSYmS0LcEHA.3480@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> You could simply write a query that does a join with a calendar table.
> http://www.aspfaq.com/2519
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "MJB" <mb2@.email.com> wrote in message
> news:euAyUyLcEHA.2476@.TK2MSFTNGP09.phx.gbl...
build[vbcol=seagreen]
> a
month.[vbcol=seagreen]
appreciate
>
|||Can you show your table structure (see http://www.aspfaq.com/5006)?
http://www.aspfaq.com/
(Reverse address to reply.)
"MJB" <mb2@.email.com> wrote in message
news:#jGPjPMcEHA.3476@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hmm, looks like it could help. Unfortunately, I only want my treeview to
> show the dates that are actually in the database, where as this approach
> seems to just show all dates between a range.
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:eSYmS0LcEHA.3480@.TK2MSFTNGP11.phx.gbl...
> build
unique
> month.
> appreciate
>
|||Well, it's nothing special. It's just a table with a Primary Key with a few
other columns and a LoadDT column that is of type datetime.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u5DN4RMcEHA.2840@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Can you show your table structure (see http://www.aspfaq.com/5006)?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "MJB" <mb2@.email.com> wrote in message
> news:#jGPjPMcEHA.3476@.tk2msftngp13.phx.gbl...
to
> unique
>
Building SQL Query to fill a treeview control with Year->Month->Day nodes
Hello,
I have load dates for each row in my database table. I'm trying to build a
query that will give me all of the unique years, then all of the unique
months for that year, then finally all of the unique days for that month.
I'm trying to load a treeview control with the results like so.
Year
|_
Month
|_
Day
Day
Etc.
Year
|_
Month
|_
Day
Etc.
If anyone has any ideas or experience on how to do this I would appreciate
it.
ThanksYou could simply write a query that does a join with a calendar table.
http://www.aspfaq.com/2519
http://www.aspfaq.com/
(Reverse address to reply.)
"MJB" <mb2@.email.com> wrote in message
news:euAyUyLcEHA.2476@.TK2MSFTNGP09.phx.gbl...
> Hello,
>
> I have load dates for each row in my database table. I'm trying to build
a
> query that will give me all of the unique years, then all of the unique
> months for that year, then finally all of the unique days for that month.
>
> I'm trying to load a treeview control with the results like so.
>
> Year
> |_
> Month
> |_
> Day
> Day
> Etc.
> Year
> |_
> Month
> |_
> Day
> Etc.
>
> If anyone has any ideas or experience on how to do this I would appreciate
> it.
>
> Thanks
>|||Hmm, looks like it could help. Unfortunately, I only want my treeview to
show the dates that are actually in the database, where as this approach
seems to just show all dates between a range.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eSYmS0LcEHA.3480@.TK2MSFTNGP11.phx.gbl...
> You could simply write a query that does a join with a calendar table.
> http://www.aspfaq.com/2519
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "MJB" <mb2@.email.com> wrote in message
> news:euAyUyLcEHA.2476@.TK2MSFTNGP09.phx.gbl...
build[vbcol=seagreen]
> a
month.[vbcol=seagreen]
appreciate[vbcol=seagreen]
>|||Can you show your table structure (see http://www.aspfaq.com/5006)?
http://www.aspfaq.com/
(Reverse address to reply.)
"MJB" <mb2@.email.com> wrote in message
news:#jGPjPMcEHA.3476@.tk2msftngp13.phx.gbl...
> Hmm, looks like it could help. Unfortunately, I only want my treeview to
> show the dates that are actually in the database, where as this approach
> seems to just show all dates between a range.
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:eSYmS0LcEHA.3480@.TK2MSFTNGP11.phx.gbl...
> build
unique[vbcol=seagreen]
> month.
> appreciate
>|||Well, it's nothing special. It's just a table with a Primary Key with a few
other columns and a LoadDT column that is of type datetime.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u5DN4RMcEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Can you show your table structure (see http://www.aspfaq.com/5006)?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "MJB" <mb2@.email.com> wrote in message
> news:#jGPjPMcEHA.3476@.tk2msftngp13.phx.gbl...
to[vbcol=seagreen]
> unique
>
I have load dates for each row in my database table. I'm trying to build a
query that will give me all of the unique years, then all of the unique
months for that year, then finally all of the unique days for that month.
I'm trying to load a treeview control with the results like so.
Year
|_
Month
|_
Day
Day
Etc.
Year
|_
Month
|_
Day
Etc.
If anyone has any ideas or experience on how to do this I would appreciate
it.
ThanksYou could simply write a query that does a join with a calendar table.
http://www.aspfaq.com/2519
http://www.aspfaq.com/
(Reverse address to reply.)
"MJB" <mb2@.email.com> wrote in message
news:euAyUyLcEHA.2476@.TK2MSFTNGP09.phx.gbl...
> Hello,
>
> I have load dates for each row in my database table. I'm trying to build
a
> query that will give me all of the unique years, then all of the unique
> months for that year, then finally all of the unique days for that month.
>
> I'm trying to load a treeview control with the results like so.
>
> Year
> |_
> Month
> |_
> Day
> Day
> Etc.
> Year
> |_
> Month
> |_
> Day
> Etc.
>
> If anyone has any ideas or experience on how to do this I would appreciate
> it.
>
> Thanks
>|||Hmm, looks like it could help. Unfortunately, I only want my treeview to
show the dates that are actually in the database, where as this approach
seems to just show all dates between a range.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eSYmS0LcEHA.3480@.TK2MSFTNGP11.phx.gbl...
> You could simply write a query that does a join with a calendar table.
> http://www.aspfaq.com/2519
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "MJB" <mb2@.email.com> wrote in message
> news:euAyUyLcEHA.2476@.TK2MSFTNGP09.phx.gbl...
build[vbcol=seagreen]
> a
month.[vbcol=seagreen]
appreciate[vbcol=seagreen]
>|||Can you show your table structure (see http://www.aspfaq.com/5006)?
http://www.aspfaq.com/
(Reverse address to reply.)
"MJB" <mb2@.email.com> wrote in message
news:#jGPjPMcEHA.3476@.tk2msftngp13.phx.gbl...
> Hmm, looks like it could help. Unfortunately, I only want my treeview to
> show the dates that are actually in the database, where as this approach
> seems to just show all dates between a range.
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:eSYmS0LcEHA.3480@.TK2MSFTNGP11.phx.gbl...
> build
unique[vbcol=seagreen]
> month.
> appreciate
>|||Well, it's nothing special. It's just a table with a Primary Key with a few
other columns and a LoadDT column that is of type datetime.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u5DN4RMcEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Can you show your table structure (see http://www.aspfaq.com/5006)?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "MJB" <mb2@.email.com> wrote in message
> news:#jGPjPMcEHA.3476@.tk2msftngp13.phx.gbl...
to[vbcol=seagreen]
> unique
>
Building SQL Query to fill a treeview control with Year->Month->Day nodes
Hello,
I have load dates for each row in my database table. I'm trying to build a
query that will give me all of the unique years, then all of the unique
months for that year, then finally all of the unique days for that month.
I'm trying to load a treeview control with the results like so.
Year
|_
Month
|_
Day
Day
Etc.
Year
|_
Month
|_
Day
Etc.
If anyone has any ideas or experience on how to do this I would appreciate
it.
Thanks
You could simply write a query that does a join with a calendar table.
http://www.aspfaq.com/2519
http://www.aspfaq.com/
(Reverse address to reply.)
"MJB" <mb2@.email.com> wrote in message
news:euAyUyLcEHA.2476@.TK2MSFTNGP09.phx.gbl...
> Hello,
>
> I have load dates for each row in my database table. I'm trying to build
a
> query that will give me all of the unique years, then all of the unique
> months for that year, then finally all of the unique days for that month.
>
> I'm trying to load a treeview control with the results like so.
>
> Year
> |_
> Month
> |_
> Day
> Day
> Etc.
> Year
> |_
> Month
> |_
> Day
> Etc.
>
> If anyone has any ideas or experience on how to do this I would appreciate
> it.
>
> Thanks
>
|||Hmm, looks like it could help. Unfortunately, I only want my treeview to
show the dates that are actually in the database, where as this approach
seems to just show all dates between a range.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eSYmS0LcEHA.3480@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> You could simply write a query that does a join with a calendar table.
> http://www.aspfaq.com/2519
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "MJB" <mb2@.email.com> wrote in message
> news:euAyUyLcEHA.2476@.TK2MSFTNGP09.phx.gbl...
build[vbcol=seagreen]
> a
month.[vbcol=seagreen]
appreciate
>
|||Can you show your table structure (see http://www.aspfaq.com/5006)?
http://www.aspfaq.com/
(Reverse address to reply.)
"MJB" <mb2@.email.com> wrote in message
news:#jGPjPMcEHA.3476@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hmm, looks like it could help. Unfortunately, I only want my treeview to
> show the dates that are actually in the database, where as this approach
> seems to just show all dates between a range.
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:eSYmS0LcEHA.3480@.TK2MSFTNGP11.phx.gbl...
> build
unique
> month.
> appreciate
>
|||Well, it's nothing special. It's just a table with a Primary Key with a few
other columns and a LoadDT column that is of type datetime.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u5DN4RMcEHA.2840@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Can you show your table structure (see http://www.aspfaq.com/5006)?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "MJB" <mb2@.email.com> wrote in message
> news:#jGPjPMcEHA.3476@.tk2msftngp13.phx.gbl...
to
> unique
>
I have load dates for each row in my database table. I'm trying to build a
query that will give me all of the unique years, then all of the unique
months for that year, then finally all of the unique days for that month.
I'm trying to load a treeview control with the results like so.
Year
|_
Month
|_
Day
Day
Etc.
Year
|_
Month
|_
Day
Etc.
If anyone has any ideas or experience on how to do this I would appreciate
it.
Thanks
You could simply write a query that does a join with a calendar table.
http://www.aspfaq.com/2519
http://www.aspfaq.com/
(Reverse address to reply.)
"MJB" <mb2@.email.com> wrote in message
news:euAyUyLcEHA.2476@.TK2MSFTNGP09.phx.gbl...
> Hello,
>
> I have load dates for each row in my database table. I'm trying to build
a
> query that will give me all of the unique years, then all of the unique
> months for that year, then finally all of the unique days for that month.
>
> I'm trying to load a treeview control with the results like so.
>
> Year
> |_
> Month
> |_
> Day
> Day
> Etc.
> Year
> |_
> Month
> |_
> Day
> Etc.
>
> If anyone has any ideas or experience on how to do this I would appreciate
> it.
>
> Thanks
>
|||Hmm, looks like it could help. Unfortunately, I only want my treeview to
show the dates that are actually in the database, where as this approach
seems to just show all dates between a range.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eSYmS0LcEHA.3480@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> You could simply write a query that does a join with a calendar table.
> http://www.aspfaq.com/2519
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "MJB" <mb2@.email.com> wrote in message
> news:euAyUyLcEHA.2476@.TK2MSFTNGP09.phx.gbl...
build[vbcol=seagreen]
> a
month.[vbcol=seagreen]
appreciate
>
|||Can you show your table structure (see http://www.aspfaq.com/5006)?
http://www.aspfaq.com/
(Reverse address to reply.)
"MJB" <mb2@.email.com> wrote in message
news:#jGPjPMcEHA.3476@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hmm, looks like it could help. Unfortunately, I only want my treeview to
> show the dates that are actually in the database, where as this approach
> seems to just show all dates between a range.
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:eSYmS0LcEHA.3480@.TK2MSFTNGP11.phx.gbl...
> build
unique
> month.
> appreciate
>
|||Well, it's nothing special. It's just a table with a Primary Key with a few
other columns and a LoadDT column that is of type datetime.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u5DN4RMcEHA.2840@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Can you show your table structure (see http://www.aspfaq.com/5006)?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "MJB" <mb2@.email.com> wrote in message
> news:#jGPjPMcEHA.3476@.tk2msftngp13.phx.gbl...
to
> unique
>
Building SQL Query to fill a treeview control with Year->Month->Day nodes
Hello,
I have load dates for each row in my database table. I'm trying to build a
query that will give me all of the unique years, then all of the unique
months for that year, then finally all of the unique days for that month.
I'm trying to load a treeview control with the results like so.
Year
|_
Month
|_
Day
Day
Etc.
Year
|_
Month
|_
Day
Etc.
If anyone has any ideas or experience on how to do this I would appreciate
it.
Thanks
You could simply write a query that does a join with a calendar table.
http://www.aspfaq.com/2519
http://www.aspfaq.com/
(Reverse address to reply.)
"MJB" <mb2@.email.com> wrote in message
news:euAyUyLcEHA.2476@.TK2MSFTNGP09.phx.gbl...
> Hello,
>
> I have load dates for each row in my database table. I'm trying to build
a
> query that will give me all of the unique years, then all of the unique
> months for that year, then finally all of the unique days for that month.
>
> I'm trying to load a treeview control with the results like so.
>
> Year
> |_
> Month
> |_
> Day
> Day
> Etc.
> Year
> |_
> Month
> |_
> Day
> Etc.
>
> If anyone has any ideas or experience on how to do this I would appreciate
> it.
>
> Thanks
>
|||Hmm, looks like it could help. Unfortunately, I only want my treeview to
show the dates that are actually in the database, where as this approach
seems to just show all dates between a range.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eSYmS0LcEHA.3480@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> You could simply write a query that does a join with a calendar table.
> http://www.aspfaq.com/2519
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "MJB" <mb2@.email.com> wrote in message
> news:euAyUyLcEHA.2476@.TK2MSFTNGP09.phx.gbl...
build[vbcol=seagreen]
> a
month.[vbcol=seagreen]
appreciate
>
|||Can you show your table structure (see http://www.aspfaq.com/5006)?
http://www.aspfaq.com/
(Reverse address to reply.)
"MJB" <mb2@.email.com> wrote in message
news:#jGPjPMcEHA.3476@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hmm, looks like it could help. Unfortunately, I only want my treeview to
> show the dates that are actually in the database, where as this approach
> seems to just show all dates between a range.
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:eSYmS0LcEHA.3480@.TK2MSFTNGP11.phx.gbl...
> build
unique
> month.
> appreciate
>
|||Well, it's nothing special. It's just a table with a Primary Key with a few
other columns and a LoadDT column that is of type datetime.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u5DN4RMcEHA.2840@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Can you show your table structure (see http://www.aspfaq.com/5006)?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "MJB" <mb2@.email.com> wrote in message
> news:#jGPjPMcEHA.3476@.tk2msftngp13.phx.gbl...
to
> unique
>
I have load dates for each row in my database table. I'm trying to build a
query that will give me all of the unique years, then all of the unique
months for that year, then finally all of the unique days for that month.
I'm trying to load a treeview control with the results like so.
Year
|_
Month
|_
Day
Day
Etc.
Year
|_
Month
|_
Day
Etc.
If anyone has any ideas or experience on how to do this I would appreciate
it.
Thanks
You could simply write a query that does a join with a calendar table.
http://www.aspfaq.com/2519
http://www.aspfaq.com/
(Reverse address to reply.)
"MJB" <mb2@.email.com> wrote in message
news:euAyUyLcEHA.2476@.TK2MSFTNGP09.phx.gbl...
> Hello,
>
> I have load dates for each row in my database table. I'm trying to build
a
> query that will give me all of the unique years, then all of the unique
> months for that year, then finally all of the unique days for that month.
>
> I'm trying to load a treeview control with the results like so.
>
> Year
> |_
> Month
> |_
> Day
> Day
> Etc.
> Year
> |_
> Month
> |_
> Day
> Etc.
>
> If anyone has any ideas or experience on how to do this I would appreciate
> it.
>
> Thanks
>
|||Hmm, looks like it could help. Unfortunately, I only want my treeview to
show the dates that are actually in the database, where as this approach
seems to just show all dates between a range.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eSYmS0LcEHA.3480@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> You could simply write a query that does a join with a calendar table.
> http://www.aspfaq.com/2519
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "MJB" <mb2@.email.com> wrote in message
> news:euAyUyLcEHA.2476@.TK2MSFTNGP09.phx.gbl...
build[vbcol=seagreen]
> a
month.[vbcol=seagreen]
appreciate
>
|||Can you show your table structure (see http://www.aspfaq.com/5006)?
http://www.aspfaq.com/
(Reverse address to reply.)
"MJB" <mb2@.email.com> wrote in message
news:#jGPjPMcEHA.3476@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hmm, looks like it could help. Unfortunately, I only want my treeview to
> show the dates that are actually in the database, where as this approach
> seems to just show all dates between a range.
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:eSYmS0LcEHA.3480@.TK2MSFTNGP11.phx.gbl...
> build
unique
> month.
> appreciate
>
|||Well, it's nothing special. It's just a table with a Primary Key with a few
other columns and a LoadDT column that is of type datetime.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u5DN4RMcEHA.2840@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Can you show your table structure (see http://www.aspfaq.com/5006)?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "MJB" <mb2@.email.com> wrote in message
> news:#jGPjPMcEHA.3476@.tk2msftngp13.phx.gbl...
to
> unique
>
Subscribe to:
Posts (Atom)