BCP command using TSQL without xp_cmdshell

Question

BCP command using TSQL without xp_cmdshell

fasttrack

SSCertifiable

Points: 6071
More actions
July 3, 2007 at 1:23 am

#177323

Hello,
in sql server 2000 I used bcp to be run it in Tsql with xp_cmshell
EXEC master..xp_cmdshell @SQLCmd.
@SQLCmd is the bcp cmd.

In SQL server 2005 xp_cmdshell is disabled (I have to enable with sp_configure), but there is another way to run bcp using TSQL to avoid enabling xp_cmdshell.

Thank

Viewing 15 posts - 1 through 14 (of 14 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You could schedule it as a OS task in sql agent. Other than that, I don't think there's a way to run an OS command without xp_cmdshell (unless you create an EXTERNAL_ACCESS CLR procedure to call it, but that strikes me as wrong)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 2

There is no another way to run bcp while xp_cmdshell is disabled.

But you may consider Bulk Load TSQL command or CLR (DLLs written in VB or C# and stored in SQL Server) if you want just to load data from file system.

noeld SSC Guru Points: 96590 More actions · Answer 3

OPENROWSET with the 'BULK' provider or BULK INSERT are your other choices.

* Noel

jezemine SSCrazy Eights Points: 8059 More actions · Answer 4

that works for importing data.

for exporting data, you have to use BCP or SSIS.

---------------------------------------
elsasoft.org

fasttrack SSCertifiable Points: 6071 More actions · Answer 5

thank to everybody,

bcp is used to export data, so to run bcp in TSQL I have to enable XP_cmdshell.

SSIS in this project is not used.

If anyone find another way let me know

jezemine SSCrazy Eights Points: 8059 More actions · Answer 6

there's one other option here but I've never tried it - apparently you can export directly from SQL to Excel using OPENROWSET:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

---------------------------------------
elsasoft.org

enigma4u247 SSC Enthusiast Points: 179 More actions · Answer 7

In order to export data to Excel using OPENROWSET you would need to have Excel installed in order to use the Microsoft Jet OLEDB 4.0 driver

george sibbald SSC Guru Points: 104210 More actions · Answer 8

as part of the same job enable xp_cmdshell just before the bcp and disable it again immediately afterwards

---------------------------------------------------------------------

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

george sibbald (5/2/2009)
as part of the same job enable xp_cmdshell just before the bcp and disable it again immediately afterwards

That would certainly work... I guess my question would be...

If the job being run can do that, what's to prevent other users from doing the same? The answer is security. xp_CmdShell requires SA privs to be executed. Disabling xp_CmdShell will not prevent a user with SA privs from enabling it and non SA users can do neither.

Enable xp_CmdShell, leave it enabled, and restrict all users but a "batch" user from having SA privs. There is no need to ever expose the password for that batch user either. It can all be done through callable jobs.

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

george sibbald SSC Guru Points: 104210 More actions · Answer 10

Jeff Moden (5/2/2009)
george sibbald (5/2/2009)
as part of the same job enable xp_cmdshell just before the bcp and disable it again immediately afterwards
Enable xp_CmdShell, leave it enabled, and restrict all users but a "batch" user from having SA privs. There is no need to ever expose the password for that batch user either. It can all be done through callable jobs.

that would upset the heck out of most auditors unfortunately. Which is why I take advantage of my rights as a DBA with sysadmin to just enable it when I really need it, got to be some perks to being a DBA, which leads me onto........

I would expect my windows account to have sysadmin rights, preferably as part of a windows group, so rather than the 'batch' id having sa rights I would link it to the xp_cmdshell proxy account, although again that mans xp_cmdshell being enabled. Not that I have ever had to actually do this

---------------------------------------------------------------------

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

george sibbald (5/2/2009)
that would upset the heck out of most auditors unfortunately.

Why? I would think that the lack of security setup by the DBA that allowed it's misuse would be more important because if you can misuse it, then you can misuse virtually anything because xp_CmdShell requires SA privs. If someone can misuse SA privs, then that's the real problem... not xp_CmdShell itself.

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

george sibbald SSC Guru Points: 104210 More actions · Answer 12

Why? Because I have found (security) auditors require xp_cmdshell be disabled, and they also question any application user id with elevated privileges and may not allow it.

I find it most secure that xp_cmdshell be disabled by default but It is still an option to enable it temporarily for the DBA (only) to use for legitimate means. If its me it will be legitimate purposes and not misuse, I can't speak for anyone else.

---------------------------------------------------------------------

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

Heh... it's a requirement on their part to make up for short comings in security by many DBA's. Instead, I'd rather challenge them to use it in an unauthorized manner. When they fail, they're forced to allow the exception.

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

Josh887 SSC Enthusiast Points: 170 More actions · Answer 14

You can take your bcp command and put it in a .cmd file then use a sql agent job to call that .cmd file.