Bulk insert causing timeout

Hi,
I'm using Bulk Insert to load a file about 1.5GB in size. When I do
this, another process which is continually reading and writing to other
tables in the same database gets a connection time out error. Why might
that be? Is the bulk insert just hogging the i/o?
Also, if I were to load the file into a different database on the same
server am I likely to still get the same error?
Thanks,
Nick
Hi Nick
If you look at the performance counters you may see what the issue with
system is!
You may also want to look at the output from sp_lock just to make sure that
each process is not locking itself out, there may be a trigger that you are
forgetting! If the latter is the case then loading into a staging table may
help, but you could still get blocking when you transfer the information to
the main table, in which case you may have to do the transfer in phases.
John
"nicholastoze@.gmail.com" wrote:

> Hi,
> I'm using Bulk Insert to load a file about 1.5GB in size. When I do
> this, another process which is continually reading and writing to other
> tables in the same database gets a connection time out error. Why might
> that be? Is the bulk insert just hogging the i/o?
> Also, if I were to load the file into a different database on the same
> server am I likely to still get the same error?
>
> Thanks,
> Nick
>

Bulk insert causing timeout

Hi,
I'm using Bulk Insert to load a file about 1.5GB in size. When I do
this, another process which is continually reading and writing to other
tables in the same database gets a connection time out error. Why might
that be? Is the bulk insert just hogging the i/o?
Also, if I were to load the file into a different database on the same
server am I likely to still get the same error?
Thanks,
NickHi Nick
If you look at the performance counters you may see what the issue with
system is!
You may also want to look at the output from sp_lock just to make sure that
each process is not locking itself out, there may be a trigger that you are
forgetting! If the latter is the case then loading into a staging table may
help, but you could still get blocking when you transfer the information to
the main table, in which case you may have to do the transfer in phases.
John
"nicholastoze@.gmail.com" wrote:

> Hi,
> I'm using Bulk Insert to load a file about 1.5GB in size. When I do
> this, another process which is continually reading and writing to other
> tables in the same database gets a connection time out error. Why might
> that be? Is the bulk insert just hogging the i/o?
> Also, if I were to load the file into a different database on the same
> server am I likely to still get the same error?
>
> Thanks,
> Nick
>

Bulk insert causing timeout

Hi,
I'm using Bulk Insert to load a file about 1.5GB in size. When I do
this, another process which is continually reading and writing to other
tables in the same database gets a connection time out error. Why might
that be? Is the bulk insert just hogging the i/o?
Also, if I were to load the file into a different database on the same
server am I likely to still get the same error?
Thanks,
NickHi Nick
If you look at the performance counters you may see what the issue with
system is!
You may also want to look at the output from sp_lock just to make sure that
each process is not locking itself out, there may be a trigger that you are
forgetting! If the latter is the case then loading into a staging table may
help, but you could still get blocking when you transfer the information to
the main table, in which case you may have to do the transfer in phases.
John
"nicholastoze@.gmail.com" wrote:
> Hi,
> I'm using Bulk Insert to load a file about 1.5GB in size. When I do
> this, another process which is continually reading and writing to other
> tables in the same database gets a connection time out error. Why might
> that be? Is the bulk insert just hogging the i/o?
> Also, if I were to load the file into a different database on the same
> server am I likely to still get the same error?
>
> Thanks,
> Nick
>

BULK INSERT - does it lock the file while reading?

Howdy, all. We have a (log-type) file that's constantly being written
to. We need to grab the latest rows out of this file, insert them into
a table, and then process them. We've found out the hard way that BCP
locks the file while it's inserting, so rows can't be written to the
file while the BCP is running.
Our current workaround is to make a copy of the file (using ftp), but
we're running into other problems.
I'm trying to find a better way to do this. We've narrowed down a
couple.
1) use TAIL or something similar to grab the records since we last ran.
2) use BULK INSERT, provided it doesn't lock the file.
3) get suggestions off of USENET.
Any suggestions or comments?
Thanks,
MichaelWHy not BCP into a "Different" table and then run a Stored Proc to Process
the records into the main table from the One you BCP'd into?
1) Step 2, where any locks would be created on the main table, will be
faster.
2) If there's still a concurrency issues, you can rewrite the Step 2 code to
process the records from one table into the main table in smaller batches...
reducint the batch size until the concurrency issue goes away.
"M Bourgon" wrote:

> Howdy, all. We have a (log-type) file that's constantly being written
> to. We need to grab the latest rows out of this file, insert them into
> a table, and then process them. We've found out the hard way that BCP
> locks the file while it's inserting, so rows can't be written to the
> file while the BCP is running.
> Our current workaround is to make a copy of the file (using ftp), but
> we're running into other problems.
> I'm trying to find a better way to do this. We've narrowed down a
> couple.
> 1) use TAIL or something similar to grab the records since we last ran.
> 2) use BULK INSERT, provided it doesn't lock the file.
> 3) get suggestions off of USENET.
>
> Any suggestions or comments?
> Thanks,
> Michael
>|||"M Bourgon" <bourgon@.gmail.com> wrote in message
news:1110928274.048339.317490@.f14g2000cwb.googlegroups.com...
> Howdy, all. We have a (log-type) file that's constantly being written
> to. We need to grab the latest rows out of this file, insert them into
> a table, and then process them. We've found out the hard way that BCP
> locks the file while it's inserting, so rows can't be written to the
> file while the BCP is running.
> Our current workaround is to make a copy of the file (using ftp), but
> we're running into other problems.
> I'm trying to find a better way to do this. We've narrowed down a
> couple.
> 1) use TAIL or something similar to grab the records since we last ran.
> 2) use BULK INSERT, provided it doesn't lock the file.
> 3) get suggestions off of USENET.
>
> Any suggestions or comments?
> Thanks,
> Michael
>
Would a half baked suggestion do?
Write an ActiveX Script to open the file shared read only ,
find your starting point and loop to the end doing either
1. a write out of the lines as is to a scratch file that you can then
BulkCopy in
2. a parse of the log file lines and then update the DB directly.|||The problem is that the file's still locked during that time, and I
can't afford to lose any rows.|||Not a bad idea, though I may just use tail for simplicity, assuming it
doesn't lock the file as well. Thanks|||Answering my own question - yes, bulk insert DOES lock the file -
couldn't append to the file, got "The process cannot access the file
because it is being used by another process.". Looks like I'll
tail/etc the file. Thanks, guys.

BULK INSERT - does it lock the file while reading?

Howdy, all. We have a (log-type) file that's constantly being written
to. We need to grab the latest rows out of this file, insert them into
a table, and then process them. We've found out the hard way that BCP
locks the file while it's inserting, so rows can't be written to the
file while the BCP is running.

Our current workaround is to make a copy of the file (using ftp), but
we're running into other problems.

I'm trying to find a better way to do this. We've narrowed down a
couple.
1) use TAIL or something similar to grab the records since we last ran.
2) use BULK INSERT, provided it doesn't lock the file.
3) get suggestions off of USENET. :)

Any suggestions or comments?
Thanks,
Michael"M Bourgon" <bourgon@.gmail.com> wrote in message
news:1110928274.048339.317490@.f14g2000cwb.googlegr oups.com...
> Howdy, all. We have a (log-type) file that's constantly being written
> to. We need to grab the latest rows out of this file, insert them into
> a table, and then process them. We've found out the hard way that BCP
> locks the file while it's inserting, so rows can't be written to the
> file while the BCP is running.
> Our current workaround is to make a copy of the file (using ftp), but
> we're running into other problems.
> I'm trying to find a better way to do this. We've narrowed down a
> couple.
> 1) use TAIL or something similar to grab the records since we last ran.
> 2) use BULK INSERT, provided it doesn't lock the file.
> 3) get suggestions off of USENET. :)
>
> Any suggestions or comments?
> Thanks,
> Michael

Would a half baked suggestion do?

Write an ActiveX Script to open the file shared read only ,
find your starting point and loop to the end doing either
1. a write out of the lines as is to a scratch file that you can then
BulkCopy in
2. a parse of the log file lines and then update the DB directly.|||Not a bad idea, though I may just use tail for simplicity, assuming it
doesn't lock the file as well. Thanks|||Answering my own question - yes, bulk insert DOES lock the file -
couldn't append to the file, got "The process cannot access the file
because it is being used by another process.". Looks like I'll
tail/etc the file. Thanks, guys.

BULK INSERT - does it lock the file while reading?

Howdy, all. We have a (log-type) file that's constantly being written
to. We need to grab the latest rows out of this file, insert them into
a table, and then process them. We've found out the hard way that BCP
locks the file while it's inserting, so rows can't be written to the
file while the BCP is running.
Our current workaround is to make a copy of the file (using ftp), but
we're running into other problems.
I'm trying to find a better way to do this. We've narrowed down a
couple.
1) use TAIL or something similar to grab the records since we last ran.
2) use BULK INSERT, provided it doesn't lock the file.
3) get suggestions off of USENET. :)
Any suggestions or comments?
Thanks,
MichaelWHy not BCP into a "Different" table and then run a Stored Proc to Process
the records into the main table from the One you BCP'd into?
1) Step 2, where any locks would be created on the main table, will be
faster.
2) If there's still a concurrency issues, you can rewrite the Step 2 code to
process the records from one table into the main table in smaller batches...
reducint the batch size until the concurrency issue goes away.
"M Bourgon" wrote:
> Howdy, all. We have a (log-type) file that's constantly being written
> to. We need to grab the latest rows out of this file, insert them into
> a table, and then process them. We've found out the hard way that BCP
> locks the file while it's inserting, so rows can't be written to the
> file while the BCP is running.
> Our current workaround is to make a copy of the file (using ftp), but
> we're running into other problems.
> I'm trying to find a better way to do this. We've narrowed down a
> couple.
> 1) use TAIL or something similar to grab the records since we last ran.
> 2) use BULK INSERT, provided it doesn't lock the file.
> 3) get suggestions off of USENET. :)
>
> Any suggestions or comments?
> Thanks,
> Michael
>|||"M Bourgon" <bourgon@.gmail.com> wrote in message
news:1110928274.048339.317490@.f14g2000cwb.googlegroups.com...
> Howdy, all. We have a (log-type) file that's constantly being written
> to. We need to grab the latest rows out of this file, insert them into
> a table, and then process them. We've found out the hard way that BCP
> locks the file while it's inserting, so rows can't be written to the
> file while the BCP is running.
> Our current workaround is to make a copy of the file (using ftp), but
> we're running into other problems.
> I'm trying to find a better way to do this. We've narrowed down a
> couple.
> 1) use TAIL or something similar to grab the records since we last ran.
> 2) use BULK INSERT, provided it doesn't lock the file.
> 3) get suggestions off of USENET. :)
>
> Any suggestions or comments?
> Thanks,
> Michael
>
Would a half baked suggestion do?
Write an ActiveX Script to open the file shared read only ,
find your starting point and loop to the end doing either
1. a write out of the lines as is to a scratch file that you can then
BulkCopy in
2. a parse of the log file lines and then update the DB directly.|||The problem is that the file's still locked during that time, and I
can't afford to lose any rows.|||Not a bad idea, though I may just use tail for simplicity, assuming it
doesn't lock the file as well. Thanks|||Answering my own question - yes, bulk insert DOES lock the file -
couldn't append to the file, got "The process cannot access the file
because it is being used by another process.". Looks like I'll
tail/etc the file. Thanks, guys.

BULK INSERT - does it lock the file while reading?

Howdy, all. We have a (log-type) file that's constantly being written
to. We need to grab the latest rows out of this file, insert them into
a table, and then process them. We've found out the hard way that BCP
locks the file while it's inserting, so rows can't be written to the
file while the BCP is running.
Our current workaround is to make a copy of the file (using ftp), but
we're running into other problems.
I'm trying to find a better way to do this. We've narrowed down a
couple.
1) use TAIL or something similar to grab the records since we last ran.
2) use BULK INSERT, provided it doesn't lock the file.
3) get suggestions off of USENET. :)
Any suggestions or comments?
Thanks,
MichaelWHy not BCP into a "Different" table and then run a Stored Proc to Process
the records into the main table from the One you BCP'd into?
1) Step 2, where any locks would be created on the main table, will be
faster.
2) If there's still a concurrency issues, you can rewrite the Step 2 code to
process the records from one table into the main table in smaller batches...
reducint the batch size until the concurrency issue goes away.
"M Bourgon" wrote:

> Howdy, all. We have a (log-type) file that's constantly being written
> to. We need to grab the latest rows out of this file, insert them into
> a table, and then process them. We've found out the hard way that BCP
> locks the file while it's inserting, so rows can't be written to the
> file while the BCP is running.
> Our current workaround is to make a copy of the file (using ftp), but
> we're running into other problems.
> I'm trying to find a better way to do this. We've narrowed down a
> couple.
> 1) use TAIL or something similar to grab the records since we last ran.
> 2) use BULK INSERT, provided it doesn't lock the file.
> 3) get suggestions off of USENET. :)
>
> Any suggestions or comments?
> Thanks,
> Michael
>|||"M Bourgon" <bourgon@.gmail.com> wrote in message
news:1110928274.048339.317490@.f14g2000cwb.googlegroups.com...
> Howdy, all. We have a (log-type) file that's constantly being written
> to. We need to grab the latest rows out of this file, insert them into
> a table, and then process them. We've found out the hard way that BCP
> locks the file while it's inserting, so rows can't be written to the
> file while the BCP is running.
> Our current workaround is to make a copy of the file (using ftp), but
> we're running into other problems.
> I'm trying to find a better way to do this. We've narrowed down a
> couple.
> 1) use TAIL or something similar to grab the records since we last ran.
> 2) use BULK INSERT, provided it doesn't lock the file.
> 3) get suggestions off of USENET. :)
>
> Any suggestions or comments?
> Thanks,
> Michael
>
Would a half baked suggestion do?
Write an ActiveX Script to open the file shared read only ,
find your starting point and loop to the end doing either
1. a write out of the lines as is to a scratch file that you can then
BulkCopy in
2. a parse of the log file lines and then update the DB directly.|||The problem is that the file's still locked during that time, and I
can't afford to lose any rows.|||Not a bad idea, though I may just use tail for simplicity, assuming it
doesn't lock the file as well. Thanks|||Answering my own question - yes, bulk insert DOES lock the file -
couldn't append to the file, got "The process cannot access the file
because it is being used by another process.". Looks like I'll
tail/etc the file. Thanks, guys.

BULK INSERT - does it lock the file while reading?

Howdy, all. We have a (log-type) file that's constantly being written
to. We need to grab the latest rows out of this file, insert them into
a table, and then process them. We've found out the hard way that BCP
locks the file while it's inserting, so rows can't be written to the
file while the BCP is running.
Our current workaround is to make a copy of the file (using ftp), but
we're running into other problems.
I'm trying to find a better way to do this. We've narrowed down a
couple.
1) use TAIL or something similar to grab the records since we last ran.
2) use BULK INSERT, provided it doesn't lock the file.
3) get suggestions off of USENET.
Any suggestions or comments?
Thanks,
Michael
WHy not BCP into a "Different" table and then run a Stored Proc to Process
the records into the main table from the One you BCP'd into?
1) Step 2, where any locks would be created on the main table, will be
faster.
2) If there's still a concurrency issues, you can rewrite the Step 2 code to
process the records from one table into the main table in smaller batches...
reducint the batch size until the concurrency issue goes away.
"M Bourgon" wrote:

> Howdy, all. We have a (log-type) file that's constantly being written
> to. We need to grab the latest rows out of this file, insert them into
> a table, and then process them. We've found out the hard way that BCP
> locks the file while it's inserting, so rows can't be written to the
> file while the BCP is running.
> Our current workaround is to make a copy of the file (using ftp), but
> we're running into other problems.
> I'm trying to find a better way to do this. We've narrowed down a
> couple.
> 1) use TAIL or something similar to grab the records since we last ran.
> 2) use BULK INSERT, provided it doesn't lock the file.
> 3) get suggestions off of USENET.
>
> Any suggestions or comments?
> Thanks,
> Michael
>
|||"M Bourgon" <bourgon@.gmail.com> wrote in message
news:1110928274.048339.317490@.f14g2000cwb.googlegr oups.com...
> Howdy, all. We have a (log-type) file that's constantly being written
> to. We need to grab the latest rows out of this file, insert them into
> a table, and then process them. We've found out the hard way that BCP
> locks the file while it's inserting, so rows can't be written to the
> file while the BCP is running.
> Our current workaround is to make a copy of the file (using ftp), but
> we're running into other problems.
> I'm trying to find a better way to do this. We've narrowed down a
> couple.
> 1) use TAIL or something similar to grab the records since we last ran.
> 2) use BULK INSERT, provided it doesn't lock the file.
> 3) get suggestions off of USENET.
>
> Any suggestions or comments?
> Thanks,
> Michael
>
Would a half baked suggestion do?
Write an ActiveX Script to open the file shared read only ,
find your starting point and loop to the end doing either
1. a write out of the lines as is to a scratch file that you can then
BulkCopy in
2. a parse of the log file lines and then update the DB directly.
|||The problem is that the file's still locked during that time, and I
can't afford to lose any rows.
|||Not a bad idea, though I may just use tail for simplicity, assuming it
doesn't lock the file as well. Thanks
|||Answering my own question - yes, bulk insert DOES lock the file -
couldn't append to the file, got "The process cannot access the file
because it is being used by another process.". Looks like I'll
tail/etc the file. Thanks, guys.

Build select statement by reading info from a text file??

Hi, excuse my ignorance on the subject, i'm new to sql. I'm trying to
build a select statement by reading info from a text file which contains a
list of names. See below:
SELECT EMail
FROM Employees
WHERE Name = <-- this is where the loop would start to read the names
from the text file.
Help!
Thanks,
Message posted via http://www.webservertalk.comSee thread:
Open a text file in a stored procedure
http://tinyurl.com/65sdn
Bryce|||You may want to import that text file into a table using DTS, then read the
values that way
Select Email
from Employees
Inner Join NewTable
ON Employees.Name = NewTable.Name
Or
Select Email
from Employees
where Name in (SELECT DISTINCT Name from NewTable)
--
Simon Worth
"LS via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:c4322cbb3d3a4109b041a08c6e1e1142@.SQ
webservertalk.com...
> Hi, excuse my ignorance on the subject, i'm new to sql. I'm trying to
> build a select statement by reading info from a text file which contains a
> list of names. See below:
> SELECT EMail
> FROM Employees
> WHERE Name = <-- this is where the loop would start to read the names
> from the text file.
> Help!
> Thanks,
> --
> Message posted via http://www.webservertalk.com|||Thanks ALL... This is what I was looking for!
Message posted via http://www.webservertalk.com