bcp timeout

We use bcp a lot in production and redistributable software.
We'd like to get bcp to terminate early if it runs for over a certain amount
of time due to locking/deadlock/...
Thus, I'm trying something like and it's not working - ideas?
bcp "SET LOCK_TIMEOUT 2; SELECT * FROM Northwind.dbo.Suppliers2" queryout
"s24.txt" -c -U abc -P ewe
Thanks.
Tom
Thomas Brown wrote:
> We use bcp a lot in production and redistributable software.
> We'd like to get bcp to terminate early if it runs for over a certain
> amount of time due to locking/deadlock/...
> Thus, I'm trying something like and it's not working - ideas?
> bcp "SET LOCK_TIMEOUT 2; SELECT * FROM Northwind.dbo.Suppliers2"
> queryout "s24.txt" -c -U abc -P ewe
A lock timeout causes an automatic attention of the query if it runs
into a situation where the query is waiting on a locked resource, in
this case, for more than 2 seconds. It is not the same thing as a query
timeout. That is, it does not terminate the query if the query runs for
more than 2 seconds even if it encounters locks along the way less than
2 seconds each.
OSQL has the -t<timeout> parameter to tell OSQL that max time for the
query. But BCP has no such parameter as far as I can see, probably
because you wouldn't want to stop a BCP operation in the middle.
If BCP encounters a deadlock situation (probably unlikely given that it
is accessing a single table in most cases), the transaction is
terminated. In a locking situation, the BCP operation could slow down
assuming none of the locks exceed the lock timeout. I'm not even sure a
lock timeout like that would work in BCP.
You can use the TABLOCK hint for BCP which gives you a lock on the table
during the operation. That should eliminate any blocking
For data extraction, you can use try using OSQL which supports what you
want.
David Gugick
Imceda Software
www.imceda.com
|||Thomas Brown wrote:
> We use bcp a lot in production and redistributable software.
> We'd like to get bcp to terminate early if it runs for over a certain
> amount of time due to locking/deadlock/...
> Thus, I'm trying something like and it's not working - ideas?
> bcp "SET LOCK_TIMEOUT 2; SELECT * FROM Northwind.dbo.Suppliers2"
> queryout "s24.txt" -c -U abc -P ewe
A lock timeout causes an automatic attention of the query if it runs
into a situation where the query is waiting on a locked resource, in
this case, for more than 2 seconds. It is not the same thing as a query
timeout. That is, it does not terminate the query if the query runs for
more than 2 seconds even if it encounters locks along the way less than
2 seconds each.
OSQL has the -t<timeout> parameter to tell OSQL that max time for the
query. But BCP has no such parameter as far as I can see, probably
because you wouldn't want to stop a BCP operation in the middle.
If BCP encounters a deadlock situation (probably unlikely given that it
is accessing a single table in most cases), the transaction is
terminated. In a locking situation, the BCP operation could slow down
assuming none of the locks exceed the lock timeout. I'm not even sure a
lock timeout like that would work in BCP.
You can use the TABLOCK hint for BCP which gives you a lock on the table
during the operation. That should eliminate any blocking.
For data extraction, you can use try using OSQL which supports what you
want.
David Gugick
Imceda Software
www.imceda.com

bcp timeout

We use bcp a lot in production and redistributable software.
We'd like to get bcp to terminate early if it runs for over a certain amount
of time due to locking/deadlock/...
Thus, I'm trying something like and it's not working - ideas?
bcp "SET LOCK_TIMEOUT 2; SELECT * FROM Northwind.dbo.Suppliers2" queryout
"s24.txt" -c -U abc -P ewe
Thanks.
Tom
Why don't you try the otherway?
Use TABLOCK hint in the BCP statement (Check BOL). BOL says "Using TABLOCK
can improve performance of the bulk copy operation due to reduced lock
contention on the table".
Since this may ensure faster bcp, there will be less locking due to users
transaction.
Thanks
GYK
|||We do use TABLOCK in our production code, and we do see perf. gains and a
reduction in locking contention. However, on our larger tables(10 GB) we see
MS SQL fall over into a state of blocking and deadlock.
We have partitioned our larger tables several times over and can't really do
that anymore to meet business requirements.
"GYK" wrote:

> Why don't you try the otherway?
> Use TABLOCK hint in the BCP statement (Check BOL). BOL says "Using TABLOCK
> can improve performance of the bulk copy operation due to reduced lock
> contention on the table".
> Since this may ensure faster bcp, there will be less locking due to users
> transaction.
> Thanks
> GYK
sql

bcp timeout

We use bcp a lot in production and redistributable software.
We'd like to get bcp to terminate early if it runs for over a certain amount
of time due to locking/deadlock/...
Thus, I'm trying something like and it's not working - ideas?
bcp "SET LOCK_TIMEOUT 2; SELECT * FROM Northwind.dbo.Suppliers2" queryout
"s24.txt" -c -U abc -P ewe
--
Thanks.
TomWhy don't you try the otherway?
Use TABLOCK hint in the BCP statement (Check BOL). BOL says "Using TABLOCK
can improve performance of the bulk copy operation due to reduced lock
contention on the table".
Since this may ensure faster bcp, there will be less locking due to users
transaction.
Thanks
GYK|||We do use TABLOCK in our production code, and we do see perf. gains and a
reduction in locking contention. However, on our larger tables(10 GB) we see
MS SQL fall over into a state of blocking and deadlock.
We have partitioned our larger tables several times over and can't really do
that anymore to meet business requirements.
"GYK" wrote:
> Why don't you try the otherway?
> Use TABLOCK hint in the BCP statement (Check BOL). BOL says "Using TABLOCK
> can improve performance of the bulk copy operation due to reduced lock
> contention on the table".
> Since this may ensure faster bcp, there will be less locking due to users
> transaction.
> Thanks
> GYK

BCP Import Errors

I'm using a credit union software/database package (Symitar/Episys) to export the data tables for use with MS SQL 7.0. After I move the files onto my SQL Server 2000 box and then run the import batch file (there are 40 some odd tables to move) which has the correct destination table, import file and format file listed. While the batch is running in a command window, I spy three specific errors during the process. they are:

String Data, Right Truncation (I added spaces/changed to larger data type to the tables to possibly mitigate this to no avail)

Invalid Character Data For Cast Specification

Invalid Date Format (all my dates in the export file are in the format xxxx-xx-xx)

I have spent several hours on researching them and trying some of the fixes to no avail

The first export was a comma delimited file and had many errors, I then switched to a tab delimited export and changed the command line in the batch file to reflect that fact. I have fewer errors with the tab version but still have errors. I have tried to view the data in the export files at the probable location but can't see anything that will cause these errors.

Here are three lines in the batch file that I'm using:

bcp ACUTest..NAME in c:\DataXfer\EXTRACT.NAME -fc:\DataXfer\FMT.NAME -t \t -Satlas -Usa -Pwhatever -V70

bcp ACUTest..COMMENT in c:\DataXfer\EXTRACT.COMMENT -fc:\DataXfer\FMT.COMMENT -t \t -Satlas -Usa -Pwhatever -V70

bcp ACUTest..SAVINGSNAME in c:\DataXfer\EXTRACT.SAVINGSNAME -fc:\DataXfer\FMT.SAVINGSNAME -t \t -Satlas -Usa -Pwhatever -V70

If I've been to vague anywhere please let me know so I can provide more info.

Thank you all for your insight and valuable time.

Seems that you have the same problem like this poster here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=908084&SiteID=1

Did you checked the data types of the destination table ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Basic SQL Cluster Questions

Ok my job is done. I have built a fully tested Windows and SQL active/passive
cluster. Not the software guys take over but as an inquiring IT guy I can't
call it done withoout some more education.
Please feel free to answer these questions or send me to more information
please.
1. I understand that the virtual clusters "CompanyCluster IP address
x.x.x.x and SqlCluster IP Address x.x.x.x" are used to map databases and
point to as the reference for websites, databases and apps. Do you perform
any tasks on these virtual machines llike servicee packs or database installs
and maintenance?
1a. If you do not use the Virtual machines then I assume you use the active
node. Would that install the database to the other node automatically?
2. Where are the Virtual machines stored and should you back them up or are
they just basically cached on the active node?
3. How about backup software any recomendations. We currently use Acronis
and is seems to work on all of our non-clustered servers.
I have not built a cluster since Server 2000 and I can tell you that this
time out with Server 2003 R2 and Sql 2005 it was much better.
Regards, emagidson
Comments Inline
"EMagidson" <EMagidson@.discussions.microsoft.com> wrote in message
news:991EA41E-F915-4A6C-9379-7BB85BE8CC44@.microsoft.com...
> Ok my job is done. I have built a fully tested Windows and SQL
> active/passive
> cluster. Not the software guys take over but as an inquiring IT guy I
> can't
> call it done withoout some more education.
> Please feel free to answer these questions or send me to more information
> please.
> 1. I understand that the virtual clusters "CompanyCluster IP address
> x.x.x.x and SqlCluster IP Address x.x.x.x" are used to map databases and
> point to as the reference for websites, databases and apps. Do you perform
> any tasks on these virtual machines llike servicee packs or database
> installs
> and maintenance?
OS service packs are applied to physical machines. SQL Service packs and
hotfixes are applied to individual instances. The installer is
cluster-aware and updates all nodes at once. From the point of view of a
client connection, there is no difference between a clustered installation
and a non-clustered system.

> 1a. If you do not use the Virtual machines then I assume you use the
> active
> node. Would that install the database to the other node automatically?
Nodes are the physical layer. Instances are the virtual abstraction. They
are independent entities. An instance can move from one node to another in
order to stay online. An instance can exist on only a single node at a
time.

> 2. Where are the Virtual machines stored and should you back them up or
> are
> they just basically cached on the active node?
SQL Data is stored on the cluster disks. The cluster configuration is
stored in teh cluster registry, an abstraction that is managed by the
cluster service on each node.

> 3. How about backup software any recomendations. We currently use Acronis
> and is seems to work on all of our non-clustered servers.
Personally, I backup the SQL servers just as I would a stand-alone system.
I do a compressed backup to disk and archive that to tape. I don't really
worry about backups. I worry about restores. Nobody got fired or rewarded
for a backup. Many folks have had that happen because they either could or
could not restore the system. One final note on restores. Test them. An
untested recoery plan is merely a recovery hope.

> I have not built a cluster since Server 2000 and I can tell you that this
> time out with Server 2003 R2 and Sql 2005 it was much better.
>
Yes, it is. That is one of the things the Server and SQL teams got right.

> Regards, emagidson
>
Good luck,
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP