bcp is giving SQL State = 1000 Native error =0 SQL Server

Question

Post reply

bcp is giving SQL State = 1000 Native error =0 SQL Server

surafeb7

SSC Enthusiast

Points: 185
More actions
February 7, 2023 at 12:21 pm

#4146489

Hi experts,
Seeking your help.
I am using a bcp command in a tool called IBM Datastage for Windows with SQL Server 2008 R2 which is working fine many years.
bcp [DBO].[VW_EDW_7777_1] out filepath_7777_1.txt -S server,1534 -U username -P *** -d db_name -c -t , -b 10000
The same command is giving error in when I use it in IBM Datastage 11x for Redhad Linux with SQL Server 2017 in AWS and the file path is Fsx.
Starting copy...
SQL State = 1000 Native error =0 SQL Server
Error=[Microsoft][ODBC Driver 17 for SQL Server]Warning:BCP import with a format file will convert empty string in delimited columns to NULL
When I remove the comma from the "-t" the command is working fine. Need to specify the comma in a quote or any other form?
Seeking your help and advice.
Thanks in advance.
Sura

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

surafeb7 SSC Enthusiast Points: 185 More actions · Answer 1

surafeb7

SSC Enthusiast

Points: 185

February 7, 2023 at 1:21 pm

#4146509

tried with -t ',' as well, but same error

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

did you also try using double quotes ? "," ( it's command line, not tsql)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

mandavli SSCrazy Points: 2122 More actions · Answer 3

does SQL server account has access to the path, where the file reside?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Of much larger concern, why on this good Green Earth would you even consider writing a command, any command, that has a user name and password in clear text? You seriously need to learn about how to properly use "Trusted Connections".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

surafeb7 SSC Enthusiast Points: 185 More actions · Answer 5

Thanks for your reply. Yes, tried both ',' as well as ",". Getting same error.

surafeb7 SSC Enthusiast Points: 185 More actions · Answer 6

Thanks for the reply. Yes, the same command works well without comma.

surafeb7 SSC Enthusiast Points: 185 More actions · Answer 7

Thanks, Jeff, for your reply. The value will pass as a parameter, but the password will register as a **** in the log.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

surafeb7 wrote:

Thanks, Jeff, for your reply. The value will pass as a parameter, but the password will register as a **** in the log.

You don't understand that it's not the log I'm worried about. Why not do it right so that there's no chance of someone sniffing your wire to get it and so you don't read about yourself or the company your doing this for on the latest "who got hacked" lists?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

surafeb7 SSC Enthusiast Points: 185 More actions · Answer 9

Thanks, Jeff, for your reply and I am happy that you are paying more attention in security matters. Yes, this command is being embedded in a toll, contains security measures. I any case I will advise my security team to look into this.

Thanks

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

surafeb7 wrote:

Thanks, Jeff, for your reply and I am happy that you are paying more attention in security matters. Yes, this command is being embedded in a toll, contains security measures. I any case I will advise my security team to look into this.
Thanks

Thanks for the feedback. And, yeah... it's pretty easy to avoid doing this with a "Service Login" from Active Directory and then using the "Trusted Connection" feature of BCP.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

surafeb7 SSC Enthusiast Points: 185 More actions · Answer 11

Thanks all.

one more interesting findings. If I take out the value for the switch -t it’s working fine. If I give any value to -t getting the error

surafeb7 SSC Enthusiast Points: 185 More actions · Answer 12

One more question. Do we need to consider any patching hence the BCP is running in the Red hat Linux ?