Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

xp_cmdshell

Status
Not open for further replies.

fmt

Programmer
Aug 5, 2005
53
US
Hello,
I have this statement to copy file from location a to location b. I ran this in query analyser, it says file copied but i dont see file in destination folder.Please advice.
Here is code.

DECLARE @fn char(6),
@cmd varchar(100)

SET @fn = REPLACE(CONVERT(char(8), getdate(), 1), '/', '')

SET @cmd = 'copy /Y \\fns01\fncommon\apps\doctracking\sample.xls \\fns01\fncommon\apps\doctracking\test\sample' + @fn + '.xls'
print @cmd
EXEC master..xp_cmdshell @cmd
 
I had the same problem. If I am correct it has something to do with the owner of the file that limit what can be done using xp_cmdshell.

I still have not figure this out
 
You should see what's wrong if you look at the output when you print @cmd. The @cmd variable is too short so the filename is being truncated. Declare @cmd as varchar(150) and it should be fine.

--James
 
Thanks JameaLean That worked...
Now I am having problem with folder where its going (different location), it says access denied.I think I need to check with our network team to see what kind of access do i have to this folder.
 
By the way, this statement works when I execute it through command prompt
Thanks
 
If it works through Dos it should work through cmdshell script.
Try to declare @cmd varchar(300) to avoid any complications in future.
Xp_Cmdshell is so convinent way to execute the dos scripts.



Dr.Sql
Good Luck.
 
You need to check the permissions for the SQL Server service account as this is the context that xp_cmdshell runs under.

--James
 
Thanks James...Thats exactly what worked. I asked our security team to check permissions for sql account.They gave full permissions and it worked.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top