Xp_cmdshell With Xcopy Returns 2, Why?
Apr 8, 2008
According to the documentation, xp_cmdshell returns 0 if success or 1 if it fails, so I was curious as to why I was getting a return value of 2 when using it with xcopy. So in my t-sql code, if I have something like
@result = master..xp_cmdshell 'xcopy c: est.txt c:Program Files'
The result would return 2. Then if I check if @Result is not equal to 0, it would say their is an error when I am not sure if there was one. I did read on a faq that xcopy expects input from the user, which is normally provided at the command prompt through a return after the command and this behavior can me mimicked by adding "< NUL:" after the command text, which will effectively signal the XCOPY input stream, so the above command becomes:
@result = master..xp_cmdshell 'xcopy c: est.txt c:Program Files < NUL: '
I am still unsure why the @result would return a 2 though?
Also, if I am not going into any subdirectories, is their an advantage of copy over xcopy and vice versa?
If anyone has any info, please let me know, I am using Sql Server 2000.
Thanks in advance,
XaiSoft
View 4 Replies
ADVERTISEMENT
Mar 7, 2008
Hi All,
I am writing a stored procedure which accepts 2 parameters -
Current version(.....for example V53)
UpgradeTo version(.....for example V61)
Using these 2 inputs --I need to create a directory V61 by copying directory V53
for this specific example it would go like this-------------
exec master.dbo.xp_cmdshell N'Xcopy c:V53 c:V61 /i'
Now if i want to make a dynamic query for this ...
how do i go about it?
declare @folder1 varchar(255)
declare @folder2 varchar(255)
declare @xcopytext varchar(255)
SET @folder1 = 'V53'
set @folder2 = 'V61'
set @xcopytext = N'Xcopy "c:@folder1" "c:@folder2" /i'
exec master.dbo.xp_cmdshell @xcopytext
This gives the error :
File Not Foundfolder1
How do I integrate dynamic query with XCopy functionality ?
View 5 Replies
View Related
Mar 2, 2004
hi,
I have the following statement:
exec xp_cmdshell 'xcopy f:dataackup est1.bak \server2ackup /c'
it works in command prompt but not in QA. i tried dir on both the machines, it works fine.
harshal.
View 8 Replies
View Related
Jan 31, 2008
I was hoping to upgrade my SQL CE from 3 to 3.5 for my PPC and desktop applications. However, when I went to XCopy to another desktop I got errors. First, error was that the System.Data.SqlServerCe.dll is not enough, so I copied the other dlls too. Then I got provider not installed error. I do not want to install, I just want to XCopy.
So I searched the newsgroups and online help. I tested using ClickOnce and that worked only because it installed SQL CE for me... but I want to just XCopy.
Next I commented out my code that uses LINQ and just used the old data adapter way to get data and it worked.
I use the same connection string:
"Data Source=C:=test.sdf;Password=sa2008;Persist Security Info=True;Encryption Mode=PPC2003 Compatibility;Max Database Size=4000"
1. Using old way to get data works with XCopy
2. Using LINQ way to get data DOES NOT work with XCopy
Seems that LINQ requires that SQL CE be installed on the desktop to work.
Is this true?
(I know LINQ is not support with SQL CE at this time, but it's sure nice to use)
One other problem I noticed is that I can compile and run my LINQ to SQL CE solution just fine in VS.Net 2008 express, but the same solution gives me a compile error without details as to why when compiled in VS.Net 2008 Pro.
,,,Bydia
View 11 Replies
View Related
Oct 18, 2007
sql server 2000
when I run under SQL in sql analyser
exec master.dbo.xp_cmdshell 'c:mssqldata est_data_mdf \server1data /y'
return : invalid drive specification
but I can run 'c:mssqldata est_data_mdf \server1data /y' from CMD
any solution?
Thx
View 1 Replies
View Related
Nov 28, 2006
We are in the process of converting our MSDE-enabled products to using SQL Express for compatability with Vista. With help from the msdn article at http://msdn2.microsoft.com/en-us/library/ms165716.aspx I've been able to create a test install that works fine under XP, but fails under Vista. We have our application installing under the "Program Files" directory, and are copying the database's MDF file into the same directory. We are using AttachDbFileName to connect to the database and under XP the LDF file is created fine. Under Vista we receive an error that the database cannot be attached and I've noticed that the LDF file is not being created. I was receiving the exact same error under XP until I removed the LDF file from the install and allowed SQL Express to create it. I'm guessing that it is an issue in the permissions necessary to write the LDF file out to the applications directory, but that's just a guess. So, I was wondering what the Microsoft recommended method for doing a SQL Express XCopy deployment under Vista was?
View 2 Replies
View Related
Jun 9, 2006
When we take backups, we xcopy them from a folder (on a remote server) containing all of the backups for the databases on that server accross a network onto a central server, into a folder for that server/database. However, should one of the backups we have moved accross the network and onto the central server get deleted or moved, the XCopy does not attempt to move the copy from the remote server back onto the central server the next time we run the procedure...so is there a setting that essentially states, "sync up the source and destination, in order to make the destination match the host"? and what causes XCopy to ignore the files it previously moved into the destination? Is it a property on the file? Any advice?
thanks in advance!
SQL Server 2000
View 4 Replies
View Related
Apr 8, 2008
I am executing xcopy with xp_cmdshell in a stored procedure and it is not copying all the files. There are about 600 files in the source directory and only around 190 are copied to the destination.
The command in the stored procedure is this N'xcopy c:source*.* c:dest'
Help please!!!!
Thanks,
Saied
View 14 Replies
View Related
Jul 1, 2006
I originally developed my application using SQL 2005 Developer Edition, but want to switch to using an XCOPY deployed DB on SQL Express for deployement.
I have successfully copied the database.mdf/ldf files over to me project, and can connect using Data Source=.SQLExpress and AttachDbFilename=|DataDirectory|[database].mdf attributes.
The question is, how do I enable CLR integration for my C# SP's?
I've tried executing the following in various places:
sp_configure 'clr enabled', 1
go
reconfigure
go
But I obviously haven't hit the spot because I'm getting the following error when VS deploys my SP library:
Error: starting database upload transaction failed.
Error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
Any ideas?
View 1 Replies
View Related
Jul 20, 2005
After setting up the linked server connection at the standby server, Itried to xcopy a file through the sql server 2000 schedule task to thestandby server's shared directory. But it keeps giving me the errormessage with'Invalid Drive Specification'.My whole process includes1) set up linked server connection on the standby server2) set up job to xcopy file as operating system commend in sql serverfrom the production box(xcopy c:directoryfile.bak\standby_servere$directory /c)3) test, but not successful - I am already running the whole scheduletask as a Windows user with Admin authority.)What did I do wrong or did I miss something?Thanks in advance
View 6 Replies
View Related
Jan 9, 2007
Hi all,
I have a stand-alone Windows XP Pro PC that has SQL Server Express (SSE) and Visual Web Developer Express (VWDE) programs. The Microsoft "pubs" Database is installed in the SQL Server Management Studio Express (SSMSE). I created a website in my VWDE program. I need the .mdf file of the pubs Database in the App_Data folder of website of my VWDE project. I think that User Instance in my SSE is established. I have studied Xcopy Deployment (SQL Server Express) and User Instance for a quite a while and I still do not know where and how to use Xcopy to get the mdf file of the pubs database into the App_Data folder of the website of my VWDE project. Please help and give me the detailed key steps/instructions about where and how to get the .mdf file of the pubs database into the App_Data folder of the website of my VWDE project via Xcopy.
Thanks in advance,
Scott Chang
View 6 Replies
View Related
Jul 23, 2005
Grrr!I'm trying to run a script:print 'Declaring cursor'declare cInv cursor forward_only static forselectdistinctinv.company,inv.contact,inv.address1,inv.city,inv.state,inv.postalcode,inv.cmcompanyidfromdedupe.dbo.ln_invoice as invleft joindedupe.dbo.customerid as cidondbo.fnCleanString(inv.company) = cid.searchcowhere((inv.customerid is nulland cid.searchco is null)and (inv.date >= '01/01/2003' or (inv.date < '01/01/2003' andinv.outstanding > 0.01))and not inv.company is null)print 'Cursor declared'declare@contact varchar(75),@company varchar(50),@address1 varchar(75),@city varchar(30),@state varchar(20),@zip varchar(10),@cmcompanyid varchar(32),@iCount int,@FetchString varchar(512)open cInvprint 'cursor opened'fetch cInv into@company,@contact,@address1,@city,@state,@zip,@cmc ompanyidprint 'Cursor fetched @@Cursor_rows = ' + cast(@@cursor_rows asvarchar(5))All the prints are there to help me figure out what's going on!When I get to the Print 'Cursor fetched @@cursor_rows....the value is 0 and the script skips down to the close and deallocate.BUT, if I just highlight the Select...When section, I get over 2,000rows. What am I missing?Thanks.
View 6 Replies
View Related
Jan 6, 2007
Hi all,
I have read/studied (i) Working with Databases in Visual Web Developer 2005 Express in http://quickstarts.asp.net/QuickStartv20/aspnet/doc/data/vwd.aspx, (ii) Xcopy Deployment (SQL Server Express) in http://msdn2.microsoft.com/en-us/library/ms165716.aspx, (iii) User Instances for Non-Administrators in http://msdn2.microsoft.com/en-us/library/ms143684.aspx, and (iv) Embedding SQL Server Server Express in Applications in http://msdn2.microsoft.com/en-us/library/ms165660.aspx. I do not understand the concepts and procedures to do Xcopy and User Instances for non-administrators completely-I do not know how to connect to databases and create database diagrams or schemas using the Database Explorer. I have a stand-alone Windows XP Pro PC. I have created a ChemDatabase with 3 dbo tables in the SQL Server Management Studio of my SQL Server Express and a website of my VWD Express application with an App_Data folder. I am not able to proceed to use Xcopy and user instance to bring the 3 dbo tables of ChemDatabase to my App_Data folder. Please help and give me some detailed procedures/instructions to bring the 3 dbo tables of ChemDatabase (or ChemDatabase itself) from the SQL Server Management Studio Express to the App_Data folder of the website of my VWD Express project?
Thanks in advance,
Scott Chang
View 3 Replies
View Related
May 15, 2001
Hello,
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
View 1 Replies
View Related
Jul 9, 2001
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
View 2 Replies
View Related
Aug 10, 2001
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
View 1 Replies
View Related
Aug 17, 2001
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
View 1 Replies
View Related
Sep 7, 2001
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
View 1 Replies
View Related
Apr 14, 2000
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
View 1 Replies
View Related
Nov 1, 2000
Hi,
while using XP_cmdshell for renaming a file , can I concatenate the current date to the file?
Ramam
View 1 Replies
View Related
Nov 28, 2000
Hi,
What is best way to compare two files and get the most recent one using xp_cmdshell?
Thanks
TT
View 1 Replies
View Related
Nov 28, 2000
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.
View 1 Replies
View Related
Mar 8, 2000
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
View 1 Replies
View Related
Mar 1, 2000
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
View 2 Replies
View Related
Jul 6, 2000
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
View 1 Replies
View Related
Jun 6, 2003
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.
View 5 Replies
View Related
Sep 24, 2003
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.
View 2 Replies
View Related
Aug 22, 2001
How to create directory and file using xp_cmdshell..?
Thanks,
Harish
View 1 Replies
View Related
May 12, 1999
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
View 1 Replies
View Related
Nov 2, 1998
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
View 6 Replies
View Related
Apr 13, 2005
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" '
View 3 Replies
View Related
Jun 15, 2004
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
View 9 Replies
View Related
Apr 27, 2007
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
View 13 Replies
View Related