BCP Troubles

We are trying to move data from a product called TABLEBase that runs on
the mainframe and we are experiencing problems trying to BCP the data
into SQL Server. The SQL server we are tying to BCP into is SQL 2000.
We are using BCP from a command prompt.

Here is what happens.

the first time it doesn't load anything and we get no messages.The only
way I got it to work was to run BCP and have it create a ForMaT file.
I compared the ForMat file I created by hand,to the ForMat file I
created from BCP they are identical. (I used a product called beyond
compare to check it). After I created the ForMaT file and loaded the
data, I then deleted the data and ran it with the original ForMaT file
(no changes) and the data loaded fine this time.

Has anyone else run into this before? It is driving us nuts!dataguy (barry_noble@.progressive.com) writes:
> We are trying to move data from a product called TABLEBase that runs on
> the mainframe and we are experiencing problems trying to BCP the data
> into SQL Server. The SQL server we are tying to BCP into is SQL 2000.
> We are using BCP from a command prompt.
> Here is what happens.
> the first time it doesn't load anything and we get no messages.The only
> way I got it to work was to run BCP and have it create a ForMaT file.
> I compared the ForMat file I created by hand,to the ForMat file I
> created from BCP they are identical. (I used a product called beyond
> compare to check it). After I created the ForMaT file and loaded the
> data, I then deleted the data and ran it with the original ForMaT file
> (no changes) and the data loaded fine this time.
> Has anyone else run into this before? It is driving us nuts!

Is this repeatable? From your description, it sounds as it happened
once.

Or does the first attempt of every file fail silently? Do you really
have to use your own format file in between for the load to work?

Normally, I would ask for CREATE TABLE, format file and sample data file,
but I'm not sure that would be useful this time.

What I can say, that I cannot recall ever having run into that BCP
fails without message. As a matter of fact, BCP never completes
silently. So it sounds like the first time BCP is not executed at
all. Is it in a command file?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,

Yes it is repeatable. It has happened more than once. The reason we
want to use our own Format file is that we are putting a process
together that will automate this. In other words if the table format
changes we want the format file to be automaticially updated.

This also is not a command file. That comes later once we get the
process refined. I am issuing the following command:

bcp d_CodeTableRepositorydb1.dbo.GBLECO in GBLECO.txt -fGBLECO.fmt
-SS65aa372 -T >>c:\output\GBLECO.OUT

At this time the format file and the input file are in the directory
where we are running the BCP command. We eventually will change this
to be fully qualified.|||dataguy (barry_noble@.progressive.com) writes:
> Yes it is repeatable. It has happened more than once.

OK, but does it happen every time, or is it intermittently?

> The reason we want to use our own Format file is that we are putting a
> process together that will automate this. In other words if the table
> format changes we want the format file to be automaticially updated.

Using your own format file is a very normal thing to do.

Since you compare the files in Beyond Compare, the only thing I can
think of is that your file has trailing spaces or different line
terminators. But in such case it should not work the second time
round - unless you save the line from some tool on the way.

> This also is not a command file. That comes later once we get the
> process refined. I am issuing the following command:
> bcp d_CodeTableRepositorydb1.dbo.GBLECO in GBLECO.txt -fGBLECO.fmt
> -SS65aa372 -T >>c:\output\GBLECO.OUT

Shouldn't you have a 2>&1 to also get errors into the output file?
You can also use -e to errors from the bulk-copying itself, but I don't
think this would matter here.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||It is happening everytime.

I got a brainstorm and figured I could run a BCP and create the format
file first prior to doing the BCP in.. When I tried running BCP with a
format as the output I keep getting the following error:

SQLState = S1090, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid string or buffer
length

Any suggestions?

I checked MSDN and couldn't see anything regarding this error and DTS..|||dataguy (barry_noble@.progressive.com) writes:
> It is happening everytime.

That's a good thing. :-) I mean this makes it easier to repeat. Could
you post the CREATE TABLE statement for table, sample data and the
format file. But please put the sample data and format file in a zip
file to attach that.

> I got a brainstorm and figured I could run a BCP and create the format
> file first prior to doing the BCP in.. When I tried running BCP with a
> format as the output I keep getting the following error:
> SQLState = S1090, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid string or buffer
> length
> Any suggestions?

BCP's error messages are sometimes very obscure. What was the command
you used to achieve this?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Couldn't it be a security issue?
In your command line I don't see any user parameters (account plus password)
that will be used by bcp. Is that correct and does the default user has
sufficient rights to import the data?

Maurits

"dataguy" <barry_noble@.progressive.com> schreef in bericht
news:1123617714.497837.183870@.g44g2000cwa.googlegr oups.com...
> We are trying to move data from a product called TABLEBase that runs on
> the mainframe and we are experiencing problems trying to BCP the data
> into SQL Server. The SQL server we are tying to BCP into is SQL 2000.
> We are using BCP from a command prompt.
> Here is what happens.
> the first time it doesn't load anything and we get no messages.The only
> way I got it to work was to run BCP and have it create a ForMaT file.
> I compared the ForMat file I created by hand,to the ForMat file I
> created from BCP they are identical. (I used a product called beyond
> compare to check it). After I created the ForMaT file and loaded the
> data, I then deleted the data and ran it with the original ForMaT file
> (no changes) and the data loaded fine this time.
> Has anyone else run into this before? It is driving us nuts!

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 problem

this command:

bcp MyDatabase..MyTable in Data\2005\MyFile.bcp.dat /f bcp.fmt /S MyServer /U sa

runs fine when run from its original location on a network drive.

When we copy the files referenced down to the C: drive and execute the command (with the path adjusted appropriately) and run the command I get an error:

Unable to open BCP host data-file

both MyFile.bcp.dat and bcp.fmt exist in the locations specified in the new command line. MyTable exists in MyDatabase. Etc. etc.

Why do we get this error? Are there other files secretly needed?

Thanks,

Jon

hi i 'm also facing the same problem could anybody reply if u have the solution...

i have tried bcp out command to a network drive which give the error

sql state = s1000

could not open host data file

please do help me.

jollyguyjolly@.hotmail.com

bcp only runs on master database

Hello,
I can't get bcp to run on any database but 'master'. I get a 'failed to login' error. i'm logged in as 'sa' with full permissions.
Please help
ThanksOriginally posted by markfraser
Hello,

I can't get bcp to run on any database but 'master'. I get a 'failed to login' error. i'm logged in as 'sa' with full permissions.

Please help
Thanks
Here is an example how to do it with VB (youll find this example at the help of MS SQL Server and will help you to do it from within Query analizer too I guess.

Peter

The following Microsoft Visual Basic code uses the BulkInsertTask object to insert data from file D:\DTS_UE\BCPData\Payroll.txt into table Payroll of database DTS_UE.

Public Sub Main()
'initialize Payroll table in DTS_UE db with bulk data
Dim objPackage As DTS.Package2
Dim objConnect As DTS.Connection2
Dim objStep As DTS.Step
Dim objTask As DTS.Task
Dim objBulkCopy As DTS.BulkInsertTask

Set objPackage = New DTS.Package

'create database connection
Set objConnect = objPackage.Connections.New("SQLOLEDB.1")
With objConnect
.ID = 1
.DataSource = "(local)"
.UseTrustedConnection = True
End With
objPackage.Connections.Add objConnect
'create step and task, specify data file and format
Set objStep = objPackage.Steps.New
Set objTask = objPackage.Tasks.New("DTSBulkInsertTask")
Set objBulkCopy = objTask.CustomTask
With objBulkCopy
.Name = "BulkInsTask"
.DataFile = "D:\DTS_UE\BCPData\Payroll.txt"
.ConnectionID = 1
.DestinationTableName = "DTS_UE..Payroll"
.FieldTerminator = "|"
.RowTerminator = "\r\n"
End With

'link step to task to package, run package
objStep.TaskName = objBulkCopy.Name
objStep.Name = "BulkInsStep"
With objPackage
.Steps.Add objStep
.Tasks.Add objTask
.FailOnError = True

.Execute
End With
End Sub|||Originally posted by markfraser
Hello,

I can't get bcp to run on any database but 'master'. I get a 'failed to login' error. i'm logged in as 'sa' with full permissions.

Please help
Thanks

do you have an admin on your team other than yourself? post your bcp.|||True, post the code used and BCP is a command link utility and not pertained to default to any database.

BCP in Stored Procedure

I am trying to set up a stored procedure to run BCP. My bcp statement runs fine if I run it from Query Analyzer or from a command prompt. However, when I try to run it from a stored procedure, it will not run. The statement is as follows:

exec master..xp_cmdshell 'bcp FeeScheduleValidation..FS_PhysicianCOSMOSSystemFSFile in C:\FeeScheduleValidationTool\Data\testfile.txt -c -t~ -r\n -S(local) -Ufeescheduleuser -Pfeescheduleuser'

Is there something special that needs to be done to allow bcp to run from a stored procedure?Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users.

Important If you choose to use a Windows NT account that is not a member of the local administrator's group for the MSSQLServer service, users who are not members of the sysadmin fixed server role cannot execute xp_cmdshell.

I think you should check the permissions of that stored procedure to make sure it allows "execute".|||Have you looked at using the Bulk Insert command in a stored proc rather than using the command shell...?

BCP in stored procedure

I have a stored procedure, which loops through a database and runs a BCP
statement (with changing criteria), as follows:
exec master..xp_cmdshell 'bcp "SELECT myfields FROM Database..Viewname WHERE
criteria ORDER BY criteria" queryout "C:\File.txt" -S ServerName -T -c'
When I run this, I get the following error:
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server
'MY DATABASE NAME' in sysservers. Execute sp_addlinkedserver to add the
server to sysservers.
I'm telling it which server to use, but it's reading the database name as a
server, for some reason. The server, however DOES show in sysservers. I've
also tried running this with -U sa -P ... with the same results. What would
be causing this? Thanks for advance for any help you can offer.
BariYou have to register that Server on the server you are running the query on.
You register the Server by executing
sp_addlinkedserver YOURSERVERNAME
Otherwise it won't be in systables.
Pain if you wanna distribute this code.

BCP Error

CP Activation Error
-------

The following command runs on several PCs without any problems,
but on one of the computers + user the bcp resulting with error

Env:
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
SQL Server: win 2000 server
PCs: win 2000 professional

Command:
"bcp" "select a, b from db_name.dbo.tbl_name WHERE (c = 0) ORDER BY a" "queryout" "\\pc-name\dir1\dir2\file-name.p" "-Ssrv" "-T" "-c"

Result:
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Code page 862 is not supported by SQL Server
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to resolve column level collations

BCP copy out failed

Please, help me to solve this problem

HelenaInteresting...you mean different servers, right...

Collation not supported? Hmmmm...

Is this SQL Server or sybase?|||Is there a difference between the computer with the problem and other machines where it's working fine in Windows Regional Settings? mojza|||Hi, Its runs ok on other PCs & for other users. The connection to MSSql 200 it with windows id.
Thanks
H|||Hi,
There is only MsSql server 2000, no Sybase involved.
I Don't know the setting of the computers - whot chell i check ?
Thanks
Helena|||Hi,
Thanks for the hint.
I changed the Windows Regional Settings on the problemathic PC and now BCP works ok.

Thanks again
Helena

BCP

HI ALL,
I have a question. If some one in our group (who are the admins, we use
trusted connection to SQL Server 2000) runs BCP and pull the data, is that
can be logged. i have never seen any events in the logs (like bcp ran, DTS
ran).
advise me any good plans.
Thanks,
Jay.John wrote:
> HI ALL,
> I have a question. If some one in our group (who are the admins, we
> use trusted connection to SQL Server 2000) runs BCP and pull the
> data, is that can be logged. i have never seen any events in the logs
> (like bcp ran, DTS ran).
> advise me any good plans.
> Thanks,
> Jay.
The log does not contain select access to data, just data that has
changed in some way. What are you looking see here? Maybe you need to
use Profiler to capture specific types of access to the database.
--
David Gugick
Imceda Software
www.imceda.com

BCP

HI ALL,
I have a question. If some one in our group (who are the admins, we use
trusted connection to SQL Server 2000) runs BCP and pull the data, is that
can be logged. i have never seen any events in the logs (like bcp ran, DTS
ran).
advise me any good plans.
Thanks,
Jay.
John wrote:
> HI ALL,
> I have a question. If some one in our group (who are the admins, we
> use trusted connection to SQL Server 2000) runs BCP and pull the
> data, is that can be logged. i have never seen any events in the logs
> (like bcp ran, DTS ran).
> advise me any good plans.
> Thanks,
> Jay.
The log does not contain select access to data, just data that has
changed in some way. What are you looking see here? Maybe you need to
use Profiler to capture specific types of access to the database.
David Gugick
Imceda Software
www.imceda.com

Batch update of a SQL table

Can anyone help a beginner with some T-SQL which runs as a scheduled stored procedure to update a table with is then accessed via an ASP web application.

I have a table called Loans which contains a calculated column which will indicate in days if a loan item is late and also each row has a charges column to reflect a charge for late returns.

In a seperate table I have a charge per day for late returns. I read this into a variable @.LateCharges

I'd like to consutruct some T-SQL to scan through the Loans table and for every row where Status is not 'Returned' I woule like it to update the charges column based on the DaysLatecolumn*@.Latecharges

Any help much appreciated.

Regards

Clive

UPDATE Loans
SET CHARGES = CHARGES + @.LateCharges
WHERE Status <> 'Returned'

|||

Hi

That would almost do it I think - however it would need to read the value of DaysLate in each case too - can I just use that name in my set statement and it woudl automatically be the one applicable to the current row?

ie:

UPDATE Loans
SET CHARGES = DaysLate * @.LateCharges
WHERE Status <> 'Returned'

|||

It sounds like you need to do a join. rather than read just one value into a variable. Please post the table definitions.