bcp help

[gcole]

I am trying to import comma delimited data into a table for processing and then export back to a comma delimited file. I can import with bulk insert, but I want to use bcp so those columns without data in my file can be ignored. I can’t seem to get past the easiest command. I am using query analyzer to run this. Can someone help me with this code?

bcp in_ Split in 'd:\transfer\test.txt' -c -t, -T

in_Split is my table and d:\transfer\test.txt resides on my server.

Thanks!

 

[arrowhouse]

What are the errors that you are getting?

You are doing

exec master..xp_cmdshell "bcp in_ Split in 'd:\transfer\test.txt' -c -t, -T"

OR

bcp needs to be called from the cmd line I think.

 

[gcole]

I did not include an exec. Ultimatley, I will execute as a query from vba.

 

[gcole]

The error is:
Line 1: Incorrect syntax near '.'.

It seems so simple, but I am definatley missing something.

 

[gcole]

Got it, I wasn't using execute master..xp_cmdshell .
Here is the correct code:

execute master..xp_cmdshell "bcp xxxx.dbo.in_data out d:\data\test.txt -c -q -t, "
execute master..xp_cmdshell

 

[tlbroadbent]

BCP is a command line utility that is usually run from a command prompt rather than a SQL Query. The use of xp_cmdshell should be very limited to to security issues involving this XP. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[gcole]

Waht is the syntax to run it from the command line? I am also having problems with SQL statements and bpc. My code is at work so I can't share it, but can you give me the syntax for it?

 

[bperry]

I believe it's what tbroadbent has already shown you, just without the xp_cmdshell.

for example:
bcp xxxx.dbo.in_data out d:\data\test.txt -c -q -t

Doesn't that work?

 

[arrowhouse]

Thanks for pointing out the security issues

 

[gcole]

tlbroadbent
I have an sp set up to run this bcp command. I pass it the table name and file path and name. Do you see any security (or other) issues with this approach?

 

[tlbroadbent]

The issue with XP_cmdshell is that anyone with execute permissions can run almost any command on the NT Server that hosts the SQL Service. xp_cmdshell executes in the same user security context of the NT account running the SQL Server service. This account is usually a local or domain administrator. Thus, users who have the right to execute xp_cmdshell also have the right to perform administrative tasks on the Server.

Example: full recursive deletion of the root directory.

xp_cmdshell "erase c:\*.* -F -S -Q"

A user could even give his own login full administrative rights on the server.

See Bernadette's Tip in thread183-73387 - "XP_CmdShell - Security Risks !!!"

There is an excellent security checklist at SQLSecurity.com.

http://www.sqlsecurity.com/checklist.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[gcole]

I guess I need to add that this command is run by an Excel script where the user doesn't have access to the code. It usually runs on a job scheduler.

I have another question though. How can I run this so I create a format file that allows the server to populate the dataid field?

 

[tlbroadbent]

You can create the format file by executing bcp at the command prompt. See the following links for directions. These topics are also available in SQL BOL.

http://msdn.microsoft.com/library/en-us/adminsql/ad_impt_bcp_6sog.asp

http://msdn.microsoft.com/library/en-us/adminsql/ad_impt_bcp_9yat.asp

http://msdn.microsoft.com/library/en-us/adminsql/ad_impt_bcp_57qd.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Guest_imported]

How do you bcp out a file with a vertical bar delimiter?

 

[tlbroadbent]

SQL BOL explains how to use the BCP Utility. The online reference is...

http://msdn.microsoft.com/library/en-us/coprompt/cp_bcp_61et.asp

You should use the -t option to indicate the field delimiter. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.