<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server Newbies
»
bcp a pipe delimited file
11 posts, Page 1 of 2
1
2
»»
bcp a pipe delimited file
Rate Topic
Display Mode
Topic Options
Author
Message
sanjuv999
sanjuv999
Posted Tuesday, September 25, 2012 10:05 AM
Forum 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
opc.three
opc.three
Posted Tuesday, September 25, 2012 4:20 PM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 11:14 PM
Points: 6,706,
Visits: 11,738
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
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1364346
sanjuv999
sanjuv999
Posted Wednesday, September 26, 2012 10:04 AM
Forum 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
Jeff Moden
Jeff Moden
Posted Wednesday, September 26, 2012 3:39 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906,
Visits: 26,790
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1364955
opc.three
opc.three
Posted Wednesday, September 26, 2012 3:48 PM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 11:14 PM
Points: 6,706,
Visits: 11,738
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
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1364961
Jeff Moden
Jeff Moden
Posted Thursday, September 27, 2012 10:59 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906,
Visits: 26,790
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1365399
opc.three
opc.three
Posted Thursday, September 27, 2012 11:03 AM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 11:14 PM
Points: 6,706,
Visits: 11,738
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
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1365402
Jeff Moden
Jeff Moden
Posted Thursday, September 27, 2012 1:01 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906,
Visits: 26,790
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1365462
opc.three
opc.three
Posted Thursday, September 27, 2012 1:12 PM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 11:14 PM
Points: 6,706,
Visits: 11,738
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
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1365470
Jeff Moden
Jeff Moden
Posted Thursday, September 27, 2012 9:26 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906,
Visits: 26,790
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1365586
« Prev Topic
|
Next Topic »
11 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
