Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


bcp a pipe delimited file


bcp a pipe delimited file

Author
Message
sanjuv999
sanjuv999
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 8
hi all,

i want to import a flat file of type

Employee ID|Employee Name;
134543543|asdfasdfdsfdf;

inside a sql proc

As bcp requires the destination table to be already present, i created a table having a single column 'id' as primary key and then executed


EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE

Next i have used

exec master.dbo.xp_cmdshell 'bcp foo.dbo.employee in c:\test.txt -T -S .\sqlexpress';

but in the output of sql management studio i get

Enter the file storage type of field id [bigint]:

please guide me
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
sanjuv999 (9/25/2012)
please guide me

Do not enable xp_cmdshell! You do not need it for this scenario and it introduces risk in your environment.

If you want to do everyting with T-SQL use for this task BULK INSERT.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
sanjuv999
sanjuv999
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 8
thanks for the reply.

This operation of bulk copy will be taking place once per day in my usecase . i.e. importing 70000 records so that is why i had thought of using bcp in the stored proc. But as you have said that it is better to do bulk insert, i will go forward with the same.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45147 Visits: 39923
opc.three (9/25/2012)
sanjuv999 (9/25/2012)
please guide me

Do not enable xp_cmdshell! You do not need it for this scenario and it introduces risk in your environment.

If you want to do everyting with T-SQL use for this task BULK INSERT.


It doesn't introduce any risks that aren't already there. Only an attacker who can get in as "SA" would be able to use it and if (s)he did so, they could also turn it on. It wouldn't even slow them down because they're expecting it to be off.

I do agree, however, that it's absolutely not required for this scenario.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
Jeff Moden (9/26/2012)
opc.three (9/25/2012)
sanjuv999 (9/25/2012)
please guide me

Do not enable xp_cmdshell! You do not need it for this scenario and it introduces risk in your environment.

If you want to do everyting with T-SQL use for this task BULK INSERT.


It doesn't introduce any risks that aren't already there. Only an attacker who can get in as "SA" would be able to use it and if (s)he did so, they could also turn it on. It wouldn't even slow them down because they're expecting it to be off.

Enabling xp_cmdshell does introduce risk into an environment despite how slight or irrelevant you think those risks may be.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45147 Visits: 39923
opc.three (9/26/2012)
Jeff Moden (9/26/2012)
opc.three (9/25/2012)
sanjuv999 (9/25/2012)
please guide me

Do not enable xp_cmdshell! You do not need it for this scenario and it introduces risk in your environment.

If you want to do everyting with T-SQL use for this task BULK INSERT.


It doesn't introduce any risks that aren't already there. Only an attacker who can get in as "SA" would be able to use it and if (s)he did so, they could also turn it on. It wouldn't even slow them down because they're expecting it to be off.

Enabling xp_cmdshell does introduce risk into an environment despite how slight or irrelevant you think those risks may be.


If you have control over who has "SA" privs, it simply does not. If you don't have that kind of control over your database (and you absolutely should), disabling it is nothing more than a warm fuzzy that won't actually work because anyone with "SA" privs can simply turn it on.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
Jeff Moden (9/27/2012)
opc.three (9/26/2012)
Jeff Moden (9/26/2012)
opc.three (9/25/2012)
sanjuv999 (9/25/2012)
please guide me

Do not enable xp_cmdshell! You do not need it for this scenario and it introduces risk in your environment.

If you want to do everyting with T-SQL use for this task BULK INSERT.


It doesn't introduce any risks that aren't already there. Only an attacker who can get in as "SA" would be able to use it and if (s)he did so, they could also turn it on. It wouldn't even slow them down because they're expecting it to be off.

Enabling xp_cmdshell does introduce risk into an environment despite how slight or irrelevant you think those risks may be.


If you have control over who has "SA" privs, it simply does not. If you don't have that kind of control over your database (and you absolutely should), disabling it is nothing more than a warm fuzzy that won't actually work because anyone with "SA" privs can simply turn it on.

Risk mitigation is all about the "what if" Jeff. I won't argue this point with you anymore. The fact is that enabling it introduces risk.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45147 Visits: 39923
opc.three (9/27/2012)
Jeff Moden (9/27/2012)
opc.three (9/26/2012)
Jeff Moden (9/26/2012)
opc.three (9/25/2012)
sanjuv999 (9/25/2012)
please guide me

Do not enable xp_cmdshell! You do not need it for this scenario and it introduces risk in your environment.

If you want to do everyting with T-SQL use for this task BULK INSERT.


It doesn't introduce any risks that aren't already there. Only an attacker who can get in as "SA" would be able to use it and if (s)he did so, they could also turn it on. It wouldn't even slow them down because they're expecting it to be off.

Enabling xp_cmdshell does introduce risk into an environment despite how slight or irrelevant you think those risks may be.


If you have control over who has "SA" privs, it simply does not. If you don't have that kind of control over your database (and you absolutely should), disabling it is nothing more than a warm fuzzy that won't actually work because anyone with "SA" privs can simply turn it on.

Risk mitigation is all about the "what if" Jeff. I won't argue this point with you anymore. The fact is that enabling it introduces risk.

You HAVE to continue to talk about it because you keep talking about it in a negative fashion! ;-)

What risk does having xp_CmdShell being turned on bring to a system? None because it can only be used by "SA"s (unless you were foolish enough to proxy it to individuals) and ANY SA can turn it on even if it's off. There is absolutely no risk to mitigate here.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
Jeff Moden (9/27/2012)
opc.three (9/27/2012)
Jeff Moden (9/27/2012)
opc.three (9/26/2012)
Jeff Moden (9/26/2012)
opc.three (9/25/2012)
sanjuv999 (9/25/2012)
please guide me

Do not enable xp_cmdshell! You do not need it for this scenario and it introduces risk in your environment.

If you want to do everyting with T-SQL use for this task BULK INSERT.


It doesn't introduce any risks that aren't already there. Only an attacker who can get in as "SA" would be able to use it and if (s)he did so, they could also turn it on. It wouldn't even slow them down because they're expecting it to be off.

Enabling xp_cmdshell does introduce risk into an environment despite how slight or irrelevant you think those risks may be.


If you have control over who has "SA" privs, it simply does not. If you don't have that kind of control over your database (and you absolutely should), disabling it is nothing more than a warm fuzzy that won't actually work because anyone with "SA" privs can simply turn it on.

Risk mitigation is all about the "what if" Jeff. I won't argue this point with you anymore. The fact is that enabling it introduces risk.

You HAVE to continue to talk about it because you keep talking about it in a negative fashion! ;-)

And I will continue to steer people towards more-secure and more-auditable (and more robust I might add) solutions as long as I have breath :-)

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45147 Visits: 39923
opc.three (9/27/2012)
Jeff Moden (9/27/2012)
opc.three (9/27/2012)
Jeff Moden (9/27/2012)
opc.three (9/26/2012)
Jeff Moden (9/26/2012)
opc.three (9/25/2012)
sanjuv999 (9/25/2012)
please guide me

Do not enable xp_cmdshell! You do not need it for this scenario and it introduces risk in your environment.

If you want to do everyting with T-SQL use for this task BULK INSERT.


It doesn't introduce any risks that aren't already there. Only an attacker who can get in as "SA" would be able to use it and if (s)he did so, they could also turn it on. It wouldn't even slow them down because they're expecting it to be off.

Enabling xp_cmdshell does introduce risk into an environment despite how slight or irrelevant you think those risks may be.


If you have control over who has "SA" privs, it simply does not. If you don't have that kind of control over your database (and you absolutely should), disabling it is nothing more than a warm fuzzy that won't actually work because anyone with "SA" privs can simply turn it on.

Risk mitigation is all about the "what if" Jeff. I won't argue this point with you anymore. The fact is that enabling it introduces risk.

You HAVE to continue to talk about it because you keep talking about it in a negative fashion! ;-)

And I will continue to steer people towards more-secure and more-auditable (and more robust I might add) solutions as long as I have breath :-)


All you have to do now, my old friend, is find something more secure to steer them to. :-D For starters, how about how to control who gets SA privs and how to determine who already has them. How about how to run any stored procedure with only PUBLIC privs and EXECUTE privs on the stored proc instead of wasting time on something that doesn't actually increase security at all?

Heh... when you and I finally meet up to drink a beer or two together, let's promise each other now that neither of us will bring this particular subject up. It appears to be the only subject that we have a true disagreement on. w00t

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search