xp_cmdshell

[fmt]

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

 

[jnavarro]

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

 

[JamesLean]

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

 

[fmt]

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.

 

[fmt]

By the way, this statement works when I execute it through command prompt
Thanks

 

[DrSql]

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.

 

[JamesLean]

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

--James

 

[fmt]

Thanks James...Thats exactly what worked. I asked our security team to check permissions for sql account.They gave full permissions and it worked.