can xp_cmdshell start from a different location than c:\windows\system32?

[bmacbmac]

Hi,

As part of a SQL scrip I have the following:

set @merge = (select 'for %a in (' + serverandpath + originalname + '.*) do "c:\program files (x86)\irfanview\i_view32.exe" %a /append=c:\TiffMerge\' + newname + ' from ImageDelivery where counter = @file)

exec master..xp_cmdshell @merge

The results show NULL after each command. Each row in the results shows c:\WINDOWS\system32> in front of the the command each time. For example,

C:\WINDOWS\system32>"c:\program files (x86)\irfanview\i_view32.exe" \\server\images\county\county\D0088009.001 /append=c:\TiffMerge\234388

If I try running this row in command line, I get an 'Access denied' error. If I remove the c:\WINDOWS\system32 and just run: "c:\program files (x86)\irfanview\i_view32.exe" \\server\images\county\county\D0088009.001 /append=c:\TiffMerge\234388

it runs just fine.

Is there a way to eliminate c:\WINDOWS\system32 from the resulting command line scripts?

 

[bmacbmac]

Nevermind on this. I was connecting from a remote server. While at the actual server desktop it worked just fine.

 

[gmmastros]

I suspect you have a permissions problem because of this part:

" \\server\images\county\county\D0088009.001 "

If you installed SQL Server using the defaults, then the SQL Server service is probably configured to use the "local system" account. This basically means SQL Server has full access to any file on the local drive, and no access to files on a server share.

To check if this is the problem....

Click Start -> Run
Type: services.msc (click ok)
Scroll down to: "SQL Server"
Right click -> Properties
click on "Log On" tab.
If the "Log on as" is set to Local System, you likely found your problem.

I usually suggest that people create an active directory account specifically for SQL server. This account should have a very strong password. It should also have full permissions to the SQL Server computer, and as limited access to network drives as possible.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom