Hi ,
I want to execute xp_cmdshell from normal sql server account not sysadmin member in SQL Server 2000. I had give execute privileges on xp_cmdshell to normal account... but its not working...
Is it possible to execute xp_cmdshell from non sysadmin member account in SQL Server2000...
Thanks in advance...
-Mohit.
Here is a post to the MSDN forums that I have not been able to resolve:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2663783&SiteID=1
Anyone here have any ideas?
I'm trying execute DTSX-package
Code Block
exec master..xp_cmdshell '"C:Program Files (x86)Microsoft SQL Server90DTSBinndtexec.exe" /FILE ...'
But have got error
Code Block
'C:Program' is not recognized as an internal or external command,
for some reason it doesn't work!
Or xp_cmdshell doesn't work with long filenames?
How can I execute an SSIS package from TSQL without using xp_cmdshell?
I have a web-app which calls some SQL which executes my SSIS package (a DTSX file, but stored in the server). But the security policy for my application won't permit me use to xp_cmdshell.
I want to do this:-
DECLARE @returncode int
EXEC @returncode = xp_cmdshell 'dtexec /sq pkgOne"'
Is there another way for executing a Package without going to the command line (e.g. is there some other system stored proc)?
Thanks
HelloI am trying to execute ‘xp_cmdshell' from within a DTS package thatwas created by another person. When I try to execute that ‘SQL Task'selectively from within the package, I get the following errormessage:Error Title: Package ErrorError Details:Error Source: Microsoft OLE DB Provider for SQL ServerError Description: xpsql.cpp: Error 87 from GetProxyAccount on line604Why is this error message popping up? When I create a new package(myself) and create an exact same SQL task as above and run it, theSQL task runs fine.Appreciate any help / feedback.Thanks in AdvanceJagannathan Santhanam
View 1 Replies View RelatedHow can I execute an SSIS package from TSQL without using xp_cmdshell?
I have a web-app which calls some SQL which executes my SSIS package (a DTSX file, but stored in the server). But the security policy for my application won't permit me use to xp_cmdshell.
I want to do this:-
DECLARE @returncode int
EXEC @returncode = xp_cmdshell 'dtexec /sq pkgOne"'
Is there another way for executing a Package without going to the command line (e.g. is there some other system stored proc)?
Thanks
I have a package that executes fine in BIDS. However, when calling it using xp_cmdshell it cannot find the path to the source flat file. Anyone have any suggestions?
Here is the statement:
xp_cmdshell 'dtexec /SQ NatlAcctsImport /SER CR1-SQL-01 /CONN SourceConnectionFlatFile;Q:BINATNLACCTS'
Here is the result:
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 9:50:56 AM
Progress: 2006-02-10 09:50:57.40
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-02-10 09:50:57.40
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2006-02-10 09:50:57.42
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Prepare for Execute: 0% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Prepare for Execute: 50% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Prepare for Execute: 100% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Pre-Execute: 0% complete
End Progress
Warning: 2006-02-10 09:50:57.43
Code: 0x80070003
Source: Data Flow Task Source - NATNLACCTS [1]
Description: The system cannot find the path specified.
End Warning
Error: 2006-02-10 09:50:57.43
Code: 0xC020200E
Source: Data Flow Task Source - NATNLACCTS [1]
Description: Cannot open the datafile "Q:BINATNLACCTS".
End Error
Error: 2006-02-10 09:50:57.43
Code: 0xC004701A
Source: Data Flow Task DTS.Pipeline
Description: component "Source - NATNLACCTS" (1) failed the pre-execute phase and returned error code 0xC020200E.
End Error
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Pre-Execute: 50% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Cleanup: 0% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Cleanup: 50% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Cleanup: 100% complete
End Progress
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 9:50:56 AM
Finished: 9:50:57 AM
Elapsed: 0.985 seconds
NULL
Hi,
I have a least privileged SQL Login €œClient€? and have granted execute rights on XP_Cmdshell SP at master db. When I execute master.. XP_Cmdshell €˜dir€™ I€™m getting the below error.
Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
Please note it is SQL Login account and not windows account. I have checked everywhere for similar problem and no luck.
Thanks for you help in advance
With regards
GK
Hi,
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Please see the image through this url http://kyxao.net/127/ExecutionProblem.png
Any ideas for this issue?
Thanks a lot
Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack
View 2 Replies View RelatedHi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys
View 2 Replies View RelatedHello,
I am trying to create a directory containing the date and then copy all the files in the current directory to it.
If I run the T-SQL script found below within Query Analyser it works fine (directory is created and files are copied in to it).
But if I run it as SQL task within DTS, only the directory is created. The files are not copied in to it!?!
I thought it maybe a permissions problem, but the SQL Server/Agent account is a local administrator and has sysadmin role with SQL Server.
I would be grateful of any assistance in this problem.
Thanks in advance,
Chris.
SQL:
declare @directoryname varchar(200)
declare @doscmd varchar(255)
select @directoryname = 'e:Audit_table_archive' + rtrim(cast(day(getdate())as char)) + rtrim(cast(month(getdate())as char)) + rtrim(cast(year(getdate())as char))
select @doscmd = 'mkdir ' + @directoryname
exec master..xp_cmdshell @doscmd
select @doscmd = 'copy e:Audit_table_archive*.* e:Audit_table_archive' + rtrim(cast(day(getdate())as char)) + rtrim(cast(month(getdate())as char)) + rtrim(cast(year(getdate())as char)) + '*.*'
exec master..xp_cmdshell @doscmd
does anyone know how to execute a FTP command through sql server 7? I am creation a table and need to ftp the result set. I have it currently creation the table, turning it into a CSV txt file and placing it in a directory. I have to then manually ftp the txt file. I want to automate this process but I cant get the FTP command to execute throught the xp_cmdshell. It will work at the DOS prompt though so I know the syntax is correct. Any sugetsions would be appreciated.
-Nathan
hi everybody
My requirement is, I have to transfer database backup files from one server to another server to take tape backup. Generally in my backup folder there will be 4days backups. I want to schedule a job so that after database backed up, today’s backup file only copied to the other server. I wrote following code, upto xp_cmdshell every thing is working fine.
But xp_cmdshell statement is giving error. I have to use variable value with sp_cmdshell. Please give me the solution for this.
declare @year1 as varchar(4),@month1 as varchar(2),@day1 varchar(2),@filename varchar(40)
set @year1=ltrim(str(year(getdate())))
if month(getdate())<10
set @month1='0'+ltrim(str(month(getdate())))
else
set @month1=ltrim(str(month(getdate())))
if day(getdate())-1 <10
set @day1='0'+ltrim(str(day(getdate())-1))
else
set @day1=ltrim(str(day(getdate())-1))
set @filename='EMPTest_db_'+ @year1+@month1+@day1+'*.bak'
xp_cmdshell @a
thanks
Keerthi
I am trying to run a Visual Basic Script using the xp_cmdshell stored procedure. When I try to run the file, the MS Script Debugger application is started.
I run the script like this: "xp_cmdshell 'D:ScriptFilesSpaceMail.vbs'"
I can run it from the DOS prompt successfully, but not from Query Analyzer. Does anyone have any suggestions on how to prevent the MS Script Debugger from running?
Chris
Hi,
I am trying to run bcp with xp_cmdshell inside a trigger. Whenver I update table the server is hanging. It creates the file in specified location but of ZERO size and I cannot delete it unless I stop SQL server service.
The smae code runs from a stored procedure without any problem.
Can you pl tell me if there are any LIMITATIONS with xp_cmdshell and Trigger.
Thanks
sekhar
Hi!!!
Can someone help me?
How can I get the result of following execution in some stored procedure, and work with it:
exec xp_cmdshell 'dir c:'
Thanx in advance
Laert
Hi,
while using XP_cmdshell for renaming a file , can I concatenate the current date to the file?
Ramam
Hi,
What is best way to compare two files and get the most recent one using xp_cmdshell?
Thanks
TT
Hi,
I am trying to execute this command.
use master
exec xp_cmdshell..'dir estserverest_dataInventory_Files*.txt /b'
I am getting the following error..
output
------------------------------------------------------------------------------Logon failure: unknown user name or bad password.
I am logged on as 'sa' for that server.
Any suggestion on how to resolve this?
Thanks in advance.
Hai ,
I beleive this is simple question but I have problem using this command
I was trying to delete contents of a temp folder thru TSQL.
Correct me if wrong
For example
xp_cmdshell " del c:emp*.*"
This is leading me to prompt 'Are you sure to delete Y/N'.
How do I write a command to say Y.
I want to schedule this every week. Is there any other way to do this task
like using AT command ,etc.
Thank you in advance
Surya
Hi !
I am trying to use xp_cmdshell
---
xp_cmdshell 'dir abcc$'
---
where abc is host name
and C$ is shared name
I get the following error message
"Logon failure: unknown user name or bad password."
I execute the same command at the dos prompt , I get the directory listing
Any suggestions??
Thanks.
KMM
Hi Guys,
i am trying to run an .exe file from queryanalyzer for my testing.I am using the following command
exec master..xp_Cmdshell 'C:extEXEproject1.exe -SCHEETAH -Uvijay -Pbell'
CHEETAH is my server name
User :vijay
pwd:bell
the problem is its running forever in QueryAnalyzer,when i tried to run the
same thing from dosprompt its executing fine.
Please help me in this
thanks for any help
SQL 7.0
I have given a non sa user permissions to run xp_cmdshell via his NT logon. When he runs it and does a "dir" of the server it works. When he trys to do a "dir" of his own machine it comes up with "Logon failure unknown user name or bad password".
SQL Agent is running under a domain admin account. I have placed SQLAgentCmdExec in the servers local administrators group. I have de-selected the option from SQLAgent propertiesJob System which restricts non sa users executing CmdExec stuff. I did attempt to Reset Proxy Account and Reset Proxy Password. When I clicked on these it just gave me message that the account and password had successfully been reset without asking me for username, password or domain.
Hello, friends,
This is probably not a question for DBA forum, but may be someone knows the answer.
In the stored procedure I'm using xp_cmdshell command to copy file from one dir to another.
How I can receive any indication what the process succeded?
xp_cmdshell return 1 or 0 if the stored procedure was invoked,
I would like to receive an indication that the file was copied.
How to create directory and file using xp_cmdshell..?
Thanks,
Harish
I tried to use the external procddure - xp_cmdshell - to copy a file from a local drive to a network drive, but failed. Yet the attempt succeeded if I tried to copy a file from one place to another in local drive.
It seems that SQL Server 6.5/7.0 did not recognize any network drive.
I greatly appreciate your help/assistance and/or any hint(s). Thanks a lot
Hi there!
Is it possible to supply variable for the xp_cmdshell parameter?
here's an example:
declare @@okay char(20)
select @@okay = "dir c:mssqlinn"
exec master..xp_cmdshell @@okay
but this doesn't work. is there any workaround on this? instead
of writing a new extended sp to cater for this.
thanks in advance.
dion
I was able to run DTS using xp_cmdshell. In my dts I have 3 global variables which I would like to pass. Can I pass a variable values using xp_cmdshell?
xp_cmdshell 'DTSRUN /S ServerName /U user/P pass /N "DTS_Name" '
Hi,
I need to do FTP using a FTP script...here is the syntax that i am using
ftp -s:c: empftpcmd.txt > c: emplog.txt
when i run it thru the command line, log.txt file has the following
==================================================
ftp> Connected to 9.999.99.999.
open 9.999.99.999
220-YYYY1 XXX FTP CS V1R2 at XXXXXX.XXXXXX.XXX.COM, 16:55:07 on 2004-06-15.
220 Connection will close if idle for more than 5 minutes.
User (9.999.99.999:(none)):
331 Send password please.
230 USERNAME is logged on. Working directory is "USERNAME.".
ftp> Invalid command.
ftp>
ascii
200 Representation type is Ascii NonPrint
ftp> get 'XXX.YYY.ZZZ.ACCOUNT' c: empXXX.YYY.ZZZ.ACCOUNT.txt
200 Port request OK.
125 Sending data set XXX.YYY.ZZZ.ACCOUNT FIXrecfm 22
250 Transfer completed successfully.
ftp: 1172544 bytes received in 3.31Seconds 353.82Kbytes/sec.
ftp> quit
221 Quit command received. Goodbye.
=================================================
but when i run it from inside a query...using
declare @cmd varchar(1000)
select @cmd = 'ftp -s:c: empftpcmd.txt > c: emplog.txt'
exec master..xp_cmdshell @cmd
the log.txt file has
=================================================
User (3.172.28.153:(none)): open 3.172.28.153
Invalid command.
ascii
get XXX.YYY.ZZZ.ACCOUNT' c: empXXX.YYY.ZZZ.ACCOUNT.txt
quit
=================================================
why is this differnece? what can i do so that i get full details in the log file.
please help.
thanks
rohit
Where should i read up on using
dos commands in conjunction with the xp_cmdshell extended stored procedure..
I need to manipulate file names....
move copy take off ... parts of the filename and replace... them...
etc... any good sites or topics... so that I can learn how to do this...
thanks...
jonathan
Hi All,
I've recently installed SQL Server 2005 and now trying to enable xp_cmdshell through SQL Server 2005 Surface Area Configuration. However, I am getting the following error:
User does not have permission to perform this action.
You do not have permission to run the RECONFIGURE statement.
I am logged in as "sa".
How can I make it work?
Thanks.
how to enable xp_cmdshell
View 2 Replies View Related