May 31, 2006 at 9:25 am
I am trying to zip and move backups using 7Zip command line.
------------------------------------------------------------
declare @cmd nvarchar(4000)
set @cmd =
'"D:7ZIP\7z.exe" a \\192.168.1.25\backups\DB_backup_200605200300.zip "D:\Microsoft SQL Server\MSSQL\BACKUP\DB_backup_200605200300.bak"'
exec xp_cmdshell @cmd
print @cmd
-------------------------------------------------------------
The code above returns this error
"The filename, directory name, or volume label syntax is incorrect."
However if i paste what is displayed by the print command into a dos window it runs fine??
Could it be to do with double quotes?
if it is a quotes things perhaps someone could show me how to create and execute a .bat file with T-SQL.
Any Ideas?
Thanks,
May 31, 2006 at 9:44 am
May 31, 2006 at 9:58 am
Aren't you missing a backward slash ?
set @cmd = '"D:\7ZIP\7z.exe"
Also, what user is this running under when you execute from query analyser ? Perhaps you don't have the user permissions to see this share: \\192.168.1.25\backups
May 31, 2006 at 10:16 am
have tried qutoed identifier but its not that.
permissions should be the same in both cases have tried as admin.
The dos command produced by print runs with no problem SO it must be some odd behavior to do with how the syntax of the string is altered upon entry to the xp_cmdshell sp.
Any more ideas
How do i create a .bat file with t-sql?
May 31, 2006 at 10:20 am
You still may have permission issues related to the user accounts being used by SQL Server. From BoL:
When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, **xp_cmdshell will be executed under the security context in which the SQL Server service is running.** When the user is not a member of the sysadmin group, >>>xp_cmdshell will impersonate the SQL Server Agent proxy account<<<, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail.
May 31, 2006 at 2:25 pm
>How do i create a .bat file with t-sql?
What do you mean? How to create the actual OS file, or how to generate windows shell script?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 1, 2006 at 1:29 am
To verify if it's a quote issue or not, try first a test on the local machine only and with paths without spaces. Something like
set @cmd = 'D:\7ZIP\7z.exe a D:\myTestFile.zip D:\myTesfile.txt'
/Kenneth
June 1, 2006 at 2:58 am
There was an issue with the format of the string which i fixed yesterday along the lines you have but now getting an access denied error in the folder to which the zips are placed.
how do i put a dos window in the same context as sql runs so i can try to reproduce the error returned by sql.
June 1, 2006 at 5:06 am
To reproduce, you can do one of two things.
Log on to your workstation with the same account that your SQL Server service is using, or go tho the server and use the prompt at the console.
In any case, the access denied message is a clear indication of that said account that your SQL Server is running under does not have permissions on the remote share. Grant perms, and all is well. It may be that group membership isn't enough, in past times there have been cases where you had to grant explicit permissions to the service account.
If, by any chance, your SQL Server is running under the Local System account, then this is a no-can-do.
The server must use a 'normal' windows account for any remote access of any kind, along with the appropriate permissions, just as any other 'nomal' windows user.
/Kenneth
June 1, 2006 at 9:22 am
As I noted above - from BoL:
When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role,
xp_cmdshell will be executed under the security context in which the SQL Server service is running.
When the user is not a member of the sysadmin group,
xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail.
So ask yourself:
Is the login invoking xp_cmdshell a member of the sysadmin role?
If not, what permissions does the SQL Server Agent Proxy account have to the filesystem?
If so, what permissions does the account used to run the SQL Server service have to the filesystem?
June 1, 2006 at 9:44 am
June 5, 2006 at 8:31 am
>How do i create a .bat file with t-sql?
In a xp_cmdshell using ECHO and redirecting output with > and >> you can create a .bat file that you can call later.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply