Post reply

xp_cmdshell is not working in my pc

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

  • September 24, 2010 at 10:49 am

    #1227125

    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.

  • September 24, 2010 at 10:54 am

    #1227126

    for that Right click database enginee

    select facets

    on facet select surfaceareaconfiguration at last

    set xpcmdshellenabled to true

  • September 24, 2010 at 1:50 pm

    #1227213

    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

  • September 25, 2010 at 2:18 am

    #1227339

    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

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

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