Inside a stored procedure I'm executing BCP command through
exec xp_cmdshell @str
But when i put this line inside stored procedure, it generates a Output for that. like how many files copied etc. this output is actually of bcp command.
Hi allI have a stored procedure that has the lineEXEC master..xp_cmdshell 'dtsrun /Stestjob1 /N testdts /E'If I run the SP from an access front end as a trusted user or from ascheduled job it runs fine and exectues the dts.If I run the stored procedure using VB6 as a standard connection the dtsjobwont run. I get back Execute permissions denied on xp_cmd.. on databasemasterdb_connect_string = "Provider=SQLOLEDB.1;Persist Security Info=False;UserID=test_connect;PWD=pw1test;Initial Catalog=testdb;Data Source=" &database_name....Set cmd = New ADODB.Commandcmd.ActiveConnection = db_connect_stringcmd.CommandType = adCmdStoredProccmd.CommandText = "testStoredProcedure"cmd.ExecuteDo I need to give test_connect permisions to run the test stored procedure.I hoped that because the VB called a stored procedure and the connection hadpermissions to execute the SP then it would be the SP that called thexp_command....can anyone tell me the accepted way to do thismany thanksAndy
some one can tell if i can make a "where" clause inside the BCP , when i say : select * FROM tbl where flag=N , usually the where flag='N' works with the ' ' , but it gives no error when save the store procedure without it ' ' saves nicely the SP,
but like this it saves well "SELECT * FROM tbl where flag=N" and when i execute it, give a sql statement error "Error = [Microsoft][SQL Native Client][SQL Server]Invalid column name N."
i dont now , any help
PS: perhaps at the end of the BCP ---> -c -T , must have some more or less
i've tried through this SQL script in Query Analyser but. the Result is not storing in the Text file, and getting output this
--------------------------------------- Password: NULL Do you want to save this format information in a file? [Y/n] Starting copy... SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at least one column NULL BCP copy out failed NULL ---------------------------------------- Please help me, Successful Query. Thanks <b>Nosepicker</b> u r solutions is right... Which u gave the Query... Exec Master..xp_cmdshell 'osql -Uuserid -Ppassword -Sservername -ddatabase -Q"DBCC CHECKDB" > "c:Test.txt"'
i've Executed, It's work's fine in the VB6,VB.Net,ASP.Net... I've done project, i've facility to take Backup and Restore the Database...
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'
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?
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.
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.
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
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.
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
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" '
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.
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
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.
When xp_cmdshell interacts with FTP it doesn't log the FTP return codes.
History: I found Nigel Rivett's T-SQL FTP scripts http://www.nigelrivett.net/ and concatenated them together with some modifications so I could have a process that actually errors when an FTP fails.
The process FTP's a file, disconnects, and then reconnects to do a dir and then select from the returned listing to see if the file name exists.
I wanted to review the initial log from the put and check for a valid FTP return code of 250 that says "Transfer completed successfully"
Log Snippets:
A normal FTP log shows: ftp> put foo 'mainframe.node.structure.foo' 200 Port request OK. 125 Storing data set MAINFRAME.NODE.STRUCTURE.FOO 250 Transfer completed successfully. ftp: 7972888 bytes sent in 99.63Seconds 80.03Kbytes/sec. ftp> quit 221 Quit command received. Goodbye.
xp_cmdshell log shows (with FTP debug on) shows: 7 put foo 'mainframe.node.structure.foo' 8 PORT 10,99,11,16,7,30 9 ---> STOR MAINFRAME.NODE.STRUCTURE.FOO 10 ---> quit 11 QUIT 12 (null)
As you can see, the return codes and some other messages are missing.
I've messed around with various options and can't seem to get any return codes in the log at all.
I need to kill some user session that i get from below query output
xp_cmdshell 'netstat -an | find /i "est"'
output: -
TCP 0.0.0.47:1099 0.0.0.39:139 ESTABLISHED TCP 0.0.0.47:1433 0.0.0.104:2961 ESTABLISHED TCP 0.0.0.47:1433 0.0.0.104:3012 ESTABLISHED TCP 0.0.0.47:1433 0.0.0.39:2915 ESTABLISHED TCP 0.0.0.47:1433 0.0.0.39:3106 ESTABLISHED TCP 0.0.0.47:2007 0.0.0.26:1487 ESTABLISHED TCP 0.0.0.47:2007 0.0.0.79:1139 ESTABLISHED
do we have any command to kill the connected session to my sql except kill spid.
ex: i want to kill the session of IP 0.0.0.79 from my sql server.
Hi .. I want to Write in files or read from files for example i have My_File.txt . i need a syntax and i want to call this syntax in my Store procedure and this syntax write forexample " Hello Word " in My_File.txt . and i want another syntax that read from My_File.txt forexample "Word" from My_File.txt . what are those syntaxes do that ??
Is there any way better than XP_CMDSHELL for writing in or reading from MyFile.txt ??
Why am I not able to use a table created via CTE with xp_cmdshell to dump it to a text file? cPRLDetails AS ( SELECT 1,2,3 FROM [dbo].[VmyVIew] vsp ) EXEC master..xp_cmdshell 'bcp "Select * FROM cPRLDetails ORDER BY PartExt05" queryout "p: argetfile.txt" -c -T'
I get the following error Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'cPRLDetails'.