Post reply

xp_cmdshell

  • October 9, 2011 at 8:41 pm

    #244342

    Trying to do this

    EXEC master..xp_cmdshell 'bcp "SELECT * FROM tablename" queryout "c:\testfile_20111010.txt" -c -T'

    getting this error

    Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1

    The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

    Any ideas how to resolve this?

  • October 10, 2011 at 1:26 am

    #1392274

    You might need to enable the XP_CMDSHELL option using this:

    sp_configure 'xp_cmdshell',1

    reconfigure with override

    But, be aware that enabling this might make your SQL server vulnerable and if you are using it on a Production server, you might want to disable it after your BCP is complete

    sp_configure 'xp_cmdshell',0

    reconfigure with override

  • October 10, 2011 at 3:36 am

    #1392304

    There is a very nice discussion going on SSC on use of xp_cmdshell.

    http://www.sqlservercentral.com/Forums/Topic1186236-1526-1.aspx#bm1187660

    Don’t miss it. 😉

Viewing 3 posts - 1 through 2 (of 2 total)

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