.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Probleme mit xp_cmdShell

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :


I have a problem concerning the usage of xp_cmdshell in one of our SQL Server. The server is 2005 (9.0.4035).
The SQL Server and the agent is running with an domain account
I have activated xp_cmdshell with the following commands...

EXEC sp_configure 'show advanced options',1

EXEC sp_configure xp_cmdshell,1

-- Result:
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'xp_cmdshell' changed from 1 to 1. Run the RECONFIGURE statement to install.

If I run sp_configure I see that it is enabled!

Next I want to check some existing files

View Complete Post

More Related Resource Links

run a bunch of sql script files using a stored procedure (without using xp_cmdshell)

Is this possible? Let me elaborate. I need to automate the execution of a bunch of sql script files (*.sql) placed by my users in a certain folder. One way I can do this is by using some kind of a script to loop thru the files in that folder and for each *.sql file found, launch a 'sqlcmd' command to execute the current script file using the -i option, ie, sqlcmd -S <server> -i <the current sql file in the loop> I tried doing this, but ran into a liitle bit of difficulty with the particular scripting language (JAVA). I was going to research this a bit more, but I also wanted to consider something that doesn't involve another script. So I was thinking about doing this using a stored procedure, but then I was wondering, how would I run a sql script file from sql server? The only way I can think of is still using sqlcmd, but then to use that within a stored procedure, I would need to turn on xp_cmdshell. Although I can do that, that invloves getting other people involved so I was wondering if there is way to do this without turning on xp_cmdshell.

Msg 15121, Level 16, State 10, XP_Cmdshell - A call to 'CreateProcessAsUser' failed with error cod

I have the XP_cmdshell issues for the past two days in SQL2005 SP2 environment. There is a domain account with sysadmin privilege. I have created a proxy account and gave the execute privilege for xp_cmdshell. If i try to run the following commmand EXEC master..xp_cmdshell 'dir *.txt' Msg 15121, Level 16, State 10, Procedure xp_cmdshell, Line 1 An error occurred during the execution of xp_cmdshell. A call to 'CreateProcessAsUser' failed with error code: '1314'. But if i give sysadmin priv and then i try to execute. It worked. I cant give sysadmin priv so i m using a proxy account. Whether any one have solution for it.

Probleme localization usercontrol

Hi,   I try that but and the file is generated : CartSummary.ascx.resx and I've created CartSummary.ascx.en-US.resx my viewCartLinkResource1.Text is détails for fr-FR and viewCartLinkResource1.Text is details for en-US But when I change it, it seems to dont work ...??? even the label price or my basket .... is it because my folder App_LocalResources is in the folder usercontrol ...? There is my website http://onceuponatimejohanna.com/   My user control looks like this : <%@ Control Language="C#" AutoEventWireup="true" CodeFile="CartSummary.ascx.cs" Inherits="UserControls_CartSummary" %><table border="0" cellpadding="0" cellspacing="1" style="width: 174px"> <tr> <td style="width: 93px"> <b><asp:Label ID="cartSummaryLabel" runat="server" meta:resourcekey="cartSummaryLabelResource1" /></b> <asp:HyperLink ID="viewCartLink" runat="server" NavigateUrl="../ShoppingCart.aspx" CssClass="CartLink" Text="(details...)" meta:resourcekey="viewCartLinkResource1" /> <asp:DataList ID="list" runat="server" meta:resourcekey="listResource1"> <ItemTemplate>

xp_cmdshell not executing for fixed server role.

Hi all, i hope you can help me. i am logon into sql server 2005 using my windows account. i am trying to execute xp_cmdshell from my account as i am member of fixed server role (sysadmin acouunt). when i am executing xp_cmdshell , its running under ##xp_cmdshell_proxy_account## "Proxy acoount".  please help me to run under sysadmin account .

Access Denied to backup but xp_cmdshell no Acess Denied

Hi, I'm trying to backup to a network drive, but I keep getting the message below: Cannot open backup device '\\Server\Projects\Exodus\Backup_Banco'. Operating system error 5(Access is denied.). The weird thing is that if I use xp_cmdshell on the same folder, I can dir, copy files, etc...  Why is that? Below is by backup command code that I get the Access Denied: BACKUP DATABASE Exodus TO DISK = '\\Server\Projects\Exodus\Backup_Banco' WITH COPY_ONLY, COMPRESSION, NOFORMAT, INIT And both xp_cmdshell commands below works without a glitch: exec xp_cmdshell 'dir \\Server\Projects\Exodus\Backup_Banco' exec xp_cmdshell 'copy \\Server\Projects\Exodus\Backup_Banco\text.txt \\Server\Projects\Exodus\Backup_Banco\text4.txt' Both SQL Server and SQL Agent are running on domain accounts, and I gave Modify rights on the \\Server\Projects\Exodus\Backup_Banco folder to the Domain Local Group that holds both accounts, as the copy commands shows. Both commands use SQL Server's service account rights, so I should get the same treatment for both commands. But that's not what is happening. Any ideas why? Thanks

Administrative Commands via XP_CmdShell in SQL Server 2005

SQL Server 2005: Microsoft SQL Server Enterprise Edition (64-bit) Microsoft Windows NT 5.2 (3790) SQL Version 9.00.3175.00   I have 2 identical servers... one for DEV and one for TEST. On the DEV server, I can use xp_cmsdhell to execute "administrative commands" such as NET SHARE and AT. On the identically configured TEST server, I can not. Those command return the "System error 5 has occurred. Access is denied" error. I CAN run other commands via xp_cmdshell (DIR, NET USE, etc.). This is true regardless of the account I connect as.   The SQL Server service accounts in both instances are domain accounts belonging to the local admin group.   I "know" that I ran across this when setting up the DEV server and am "fairly sure" I found a reference to a known problem with 2005's xp_cmdshell and administrative commands. But for the life of me I can not now find any such reference, nor can I find any differences between the DEV and TEST servers in the area of users, groups, policies, or anything else security-related. (99.9% of the internet searches I do end up being about either enabling xp_cmdshell -- done -- or setting up an xp_cmdshell proxy account -- not needed).   Any input is greatly appreciated.   Thanks! -dave

copy folder from one server to the next using xp_cmdshell

Hello, I have to copy a folder from one server to another. I would like to use xp_cmdshell to accomplish this, but I am not sure if I have to create a batch file, or if I can just embed the commands right in xp_cmdshell. My DOS knowledge is limited, so I always dread these types of tasks. Any help would be appreciated and that you all in advance :)   DaveDave SQL Developer

Probleme custom result page



I have create a custom result page to show my search result into my theme.

I have configure into the site collection, the search settings to show result in my page and not in the Search Center.

My problem is when i start a search i have no results.

But if i delete the '&u' parameter in my URL, it will show the good results.

How can i disable this parameter without using javascript ?




Catching Error Message from XP_CMDSHELL


I am running the following command: 

 EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline

On the Results tab I get 2 lines
 Could not find a part of the path '\\server\directory\filename'.

How do I capture the first line in an error message?  I tried using a Try Catch block with "SELECT @ErrorMessage = ERROR_MESSAGE()" and it doesn't grab it.


The message is not coming from sys.messages.  Where is this error message coming from then? 



SQL 2008 and above - xp_cmdshell replacement ?


Hello All !

"xp_cmdshell" is not a recommanded SQL command (security and so on) ... in SQL 2005 or >, but is there a simple command to do the same things with security with SQL 2008 or R2 or > ?

do I need to use SMO objects or include Powershell command in my queries ?


Thanks for advance for your ideas / help - Regards - Have a nice day ! RHUM2

xp_cmdshell is not working in my pc



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


any body has idea to resolve this.


xp_cmdshell is not working in my pc



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 erroe


SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file


any body has idea to resolve this.



xp_cmdshell output xml


Hi I am using below statement to run in management studio


EXEC master..xp_cmdshell 'bcp "select * from AdventureWorks.HumanResources.Employee for xml auto,elements" queryout "C:\bcptest.xml" -T -c -SKRISHNAK'

output file that i received from this is having xml error

i want xml  to be separated by each column of select statement

Is there any way to achieve this.

Error while exeucting XP_CmdShell (SQL 2005)



I am trying to execute the following lines of code in Query and i am getting an error

Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connection

 DECLARE @AlertResultWordRepositoryPath AS NVARCHAR (1000)                    
 DECLARE @LetterFileNameAlert AS NVARCHAR (255)                      
 DECLARE @LetterFileNameResult AS NVARCHAR (255)
DECLARE @bcpcommandWord AS NVARCHAR (500)

SET @AlertResultWordRepositoryPath = dbo.EY_fnGetLookupValue (251)                       
 SET @LetterFileNameAlert           = dbo.EY_fnGetLookupValue (106)                       
 SET @LetterFileNameR

xp_cmdShell in Stored Procedures



Iam trying to run SSIS package from C# code using Stored Procedures(which in turn uses xp_cmdshell) Due to administrative issues, I want to bypass xp_cmdshell completely. Is there any other way to do this? I would also like to explore an alternative to SSIS if there is no solution without xp_cmdshell.




Syntax error adding a parameter to xp_cmdshell command



I am trying to add a parameter to a bcp query that will be run in xp_cmdshell but am getting a syntax error at the '+' character.  Been staring at it for two hours and have concluded that  some outside help is needed. 

The command is:

EXEC xp_cmdshell 'bcp "SELECT * FROM [CensusData].[dbo].[Main] where zBarCode = ' + @zBarCode + '"  queryout "C:\temp_housing_data.txt" -T -c -t -S "MyServerName"'

The syntax is on the first '+' character and it simply says "Incorrect syntax near '+'"

Please help.

How to enable xp_cmdshell?


Dear esteemed DBAs,

I've tried to enable xp_cmdshell by executing below command in SSMS.



sp_configurexp_cmdshell, 1




ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend