use T-SQL call BCP to load txt into table

Question

Post reply

use T-SQL call BCP to load txt into table

Jasper_visiter

SSC-Addicted

Points: 439
More actions
December 2, 2009 at 7:36 am

#140431

hi evryone,
i met a problem,
i want to use T-SQL to call BCP.exe to load local txt file into a Table.
in DOS windows it runs ok.
but when i use T-SQL to call it,it donesn't work.
T-SQL command are:
cmdexec and
exec master..xp_cmdshell ' .......'
the local user is sa ,what's wrong with it?
thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

First, not really sure. Can you show us the entire command and the full error message you are receiving?

Second, have you looked at using BULK INSERT to accomplish the import?

Jasper_visiter SSC-Addicted Points: 439 More actions · Answer 2

exec master..xp_cmdshell 'bcp.exe L_Staging.dbo.DID_Data in "c:test_20091118.txt" -f "bcp_DID.fmt" -S "qa-01" -U "sa" -P "Ssssssss"'

SQLState = 08001, NativeError = 2

Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [2].

SQLState = 08001, NativeError = 2

Error = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is config

ured to allow remote connections. For more information see SQL Server Books Online.

SQLState = S1T00, NativeError = 0

Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired

NULL

Jasper_visiter SSC-Addicted Points: 439 More actions · Answer 3

hi there,

I have solved this problem.

1, create a bat file in DOS root,inside content the sentence to run BCP script.

2, create a task Schedule to run the bat file in some special time.

it passed.

thanks everyone.

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

On the other hand, you could do this with BULK INSERT through T-SQL without ever going near xp_CmdShell.

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

Jasper_visiter SSC-Addicted Points: 439 More actions · Answer 5

thanks Jeff,

I must use that, because it is a part of promotion of OLD system to new system in our company.

i have to use those old code, the change less the better.

or that mean big project waiting.

thanks.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

The other neat part BULK INSERT is that you can use a "trusted connection"... no need for a user name or password in clear code. Post back if you run into problems.

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