Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

bcp a pipe delimited file Expand / Collapse
Author
Message
Posted Tuesday, September 25, 2012 10:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 26, 2012 10:00 AM
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
Post #1364158
Posted Tuesday, September 25, 2012 4:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:57 AM
Points: 7,084, Visits: 12,577
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
Post #1364346
Posted Wednesday, September 26, 2012 10:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 26, 2012 10:00 AM
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.
Post #1364812
Posted Wednesday, September 26, 2012 3:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 36,781, Visits: 31,238
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1364955
Posted Wednesday, September 26, 2012 3:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:57 AM
Points: 7,084, Visits: 12,577
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
Post #1364961
Posted Thursday, September 27, 2012 10:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 36,781, Visits: 31,238
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1365399
Posted Thursday, September 27, 2012 11:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:57 AM
Points: 7,084, Visits: 12,577
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
Post #1365402
Posted Thursday, September 27, 2012 1:01 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 36,781, Visits: 31,238
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1365462
Posted Thursday, September 27, 2012 1:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:57 AM
Points: 7,084, Visits: 12,577
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
Post #1365470
Posted Thursday, September 27, 2012 9:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 36,781, Visits: 31,238
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. 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.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1365586
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse