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 command using TSQL without xp_cmdshell Expand / Collapse
Author
Message
Posted Tuesday, July 3, 2007 1:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 15, 2013 7:37 AM
Points: 351, Visits: 152

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




Post #378524
Posted Tuesday, July 3, 2007 1:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:28 PM
Points: 43,028, Visits: 36,193
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 2008, MVP
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

Post #378527
Posted Tuesday, July 3, 2007 7:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 9:22 AM
Points: 386, Visits: 441
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.
Post #378638
Posted Tuesday, July 3, 2007 8:52 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 5:47 AM
Points: 6,266, Visits: 2,029
OPENROWSET with the 'BULK' provider or BULK INSERT are your other choices.



* Noel
Post #378678
Posted Tuesday, July 3, 2007 10:28 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 18, 2013 4:06 PM
Points: 471, Visits: 589

that works for importing data.

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



---------------------------------------
elsasoft.org
Post #378733
Posted Tuesday, July 3, 2007 2:42 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 15, 2013 7:37 AM
Points: 351, Visits: 152

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




Post #378786
Posted Tuesday, July 3, 2007 3:53 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 18, 2013 4:06 PM
Points: 471, Visits: 589

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
Post #378792
Posted Saturday, May 2, 2009 8:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 3, 2010 10:38 AM
Points: 127, Visits: 72
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
Post #708923
Posted Saturday, May 2, 2009 9:08 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:35 AM
Points: 5,992, Visits: 12,940
as part of the same job enable xp_cmdshell just before the bcp and disable it again immediately afterwards

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

Post #708928
Posted Saturday, May 2, 2009 12:43 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 37,101, Visits: 31,653
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."

(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 #708957
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse