Probleme mit 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.

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.

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>

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 .

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

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

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

Hi,

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 ?

Thanks,

Nic.

 

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'.
 NULL

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? 

 

---

lcerni

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

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.

 

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 erroe

 

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.

 

 

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.

Hi

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 @Path AS VARCHAR(1000)
DECLARE @bcpcommandWord AS NVARCHAR (500)

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

Hi,

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.

 

Thanks!

---

DKoushik

Hi,

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.