xp_cmdshell is not working in my pc

Question

xp_cmdshell is not working in my pc

jani_shirish

SSC Veteran

Points: 220
More actions
September 24, 2010 at 10:37 am

#228738

Hi
i have query below for adventureworks database
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
SET @FileName = REPLACE('c:\authors_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
SET @bcpCommand = 'bcp "select * from AdventureWorks.HumanResources.Employee" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -T -c'
EXEC master..xp_cmdshell @bcpCommand
this gives me error
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file
NULL
any body has idea to resolve this.

Viewing 5 posts - 1 through 4 (of 4 total)

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

setiv Mr or Mrs. 500 Points: 556 More actions · Answer 1

this gives me :

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

jani_shirish SSC Veteran Points: 220 More actions · Answer 2

for that Right click database enginee

select facets

on facet select surfaceareaconfiguration at last

set xpcmdshellenabled to true

Adiga One Orange Chip Points: 27284 More actions · Answer 3

Check for misspells in @FileName by printing the output of

SET @FileName = REPLACE('c:\authors_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')

Also check if you have privileges on that file.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

setiv Mr or Mrs. 500 Points: 556 More actions · Answer 4

After running a query

-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

GO

-- To update the currently configured value for advanced options.

RECONFIGURE

GO

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

GO

-- To update the currently configured value for this feature.

RECONFIGURE

GO

and running a query

DECLARE @FileName varchar(50),

@bcpCommand varchar(2000)

SET @FileName = REPLACE('c:\authors_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')

SET @bcpCommand = 'bcp "select * from AdventureWorks.HumanResources.Employee" queryout "'

SET @bcpCommand = @bcpCommand + @FileName + '" -T -c'

EXEC master..xp_cmdshell @bcpCommand

it gives me

NULL

Starting copy...

NULL

290 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total : 81 Average : (3580.25 rows per sec.)

NULL