BCP Troubles

[dataguy]

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!

[Erland Sommarskog]

Re: BCP Troubles

dataguy (barry_noble@pr ogressive.com) writes:[color=blue]
> 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![/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[dataguy]

Re: BCP Troubles

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_CodeTableRepo sitorydb1.dbo.G BLECO in GBLECO.txt -fGBLECO.fmt
-SS65aa372 -T >>c:\output\GBL ECO.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.

[Erland Sommarskog]

Re: BCP Troubles

dataguy (barry_noble@pr ogressive.com) writes:[color=blue]
> Yes it is repeatable. It has happened more than once.[/color]

OK, but does it happen every time, or is it intermittently?
[color=blue]
> 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.[/color]

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.
[color=blue]
> This also is not a command file. That comes later once we get the
> process refined. I am issuing the following command:
>
> bcp d_CodeTableRepo sitorydb1.dbo.G BLECO in GBLECO.txt -fGBLECO.fmt
> -SS65aa372 -T >>c:\output\GBL ECO.OUT[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[dataguy]

Re: BCP Troubles

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..

[Erland Sommarskog]

Re: BCP Troubles

dataguy (barry_noble@pr ogressive.com) writes:[color=blue]
> It is happening everytime.[/color]

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.
[color=blue]
> 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?[/color]

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

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Maurits]

Re: BCP Troubles

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@pr ogressive.com> schreef in bericht
news:1123617714 .497837.183870@ g44g2000cwa.goo glegroups.com.. .[color=blue]
> 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!
>[/color]