Change xp_cmdshell working directory?  
Author Message
HumanResources





PostPosted: Fri Feb 16 16:18:36 CST 2007 Top

SQL Server >> Change xp_cmdshell working directory?

Hello,

xp_cmdshell seems to be using c:\windows\system32 as some sort of "working"
directory. When we excute commands that create temporary files (such as FTP
for example), they write their temporary files to c:\windows\system32. If
we're ftping a large file, this causes problems.

Can this be changed? When FTP.exe is run from a normal command window, it
seems to respect the TEMP/TMP environment variable settings. When run from
xp_cmdshell, it doesn't seem to respect those settings.

Does anyone have any ideas?

SQL Server259  
 
 
Aaron





PostPosted: Fri Feb 16 16:18:36 CST 2007 Top

SQL Server >> Change xp_cmdshell working directory? You need to set the TEMP/TMP environment variable settings for the user that
SQL Server runs as. SQL Server does not run as *your* account, typically,
so changing your environment variables does absolutely nothing for the SQL
Server process.

A





> Hello,
>
> xp_cmdshell seems to be using c:\windows\system32 as some sort of
> "working"
> directory. When we excute commands that create temporary files (such as
> FTP
> for example), they write their temporary files to c:\windows\system32. If
> we're ftping a large file, this causes problems.
>
> Can this be changed? When FTP.exe is run from a normal command window, it
> seems to respect the TEMP/TMP environment variable settings. When run
> from
> xp_cmdshell, it doesn't seem to respect those settings.
>
> Does anyone have any ideas?


 
 
pythas





PostPosted: Fri Feb 16 16:40:18 CST 2007 Top

SQL Server >> Change xp_cmdshell working directory? Hello,

The environment variables are set properly for the service account.

It looks like this FTP procedure is part of a defined job that's scheduled
to run a couple of times a week. That step that uses xp_cmdshell to call FTP
is set to run as "Self".

Any other ideas?



> You need to set the TEMP/TMP environment variable settings for the user that
> SQL Server runs as. SQL Server does not run as *your* account, typically,
> so changing your environment variables does absolutely nothing for the SQL
> Server process.
>
> A
>
>
>


> > Hello,
> >
> > xp_cmdshell seems to be using c:\windows\system32 as some sort of
> > "working"
> > directory. When we excute commands that create temporary files (such as
> > FTP
> > for example), they write their temporary files to c:\windows\system32. If
> > we're ftping a large file, this causes problems.
> >
> > Can this be changed? When FTP.exe is run from a normal command window, it
> > seems to respect the TEMP/TMP environment variable settings. When run
> > from
> > xp_cmdshell, it doesn't seem to respect those settings.
> >
> > Does anyone have any ideas?
>
>
>
 
 
Aaron





PostPosted: Fri Feb 16 16:53:38 CST 2007 Top

SQL Server >> Change xp_cmdshell working directory? > It looks like this FTP procedure is part of a defined job that's scheduled
> to run a couple of times a week. That step that uses xp_cmdshell to call
> FTP
> is set to run as "Self".
>
> Any other ideas?

Oh, you didn't mention this was a scheduled job. What is the proxy/account
in use for the SQL Server Agent service (not SQL Server itself)? Who is the
owner of the job?


 
 
pythas





PostPosted: Fri Feb 16 17:07:03 CST 2007 Top

SQL Server >> Change xp_cmdshell working directory? SQL Service Account is running as the same service account as the SQL Server
itself. If I login as that service account user, environment variables look
like they get set properly. Looks like the server isn't configured with a
proxy account.

Didn't realize it was a scheduled job either, our developers left that out
of the email they sent me, and I just noticed it when I was pawing through
the job definition more.

Looks like the owner of the job is sa.



> > It looks like this FTP procedure is part of a defined job that's scheduled
> > to run a couple of times a week. That step that uses xp_cmdshell to call
> > FTP
> > is set to run as "Self".
> >
> > Any other ideas?
>
> Oh, you didn't mention this was a scheduled job. What is the proxy/account
> in use for the SQL Server Agent service (not SQL Server itself)? Who is the
> owner of the job?
>
>
>
 
 
Sue





PostPosted: Mon Feb 19 21:43:43 CST 2007 Top

SQL Server >> Change xp_cmdshell working directory? You can verify the account and the environment variables by
executing a job with the same job owner to execute
xp_cmdshell 'SET' . Have the job step output to a file and
then check that file.

Check the system variables for the temporary directory as
well. Control Panel -> System. Select the Advanced tab - you
can check environment variables there. Not everything
related to a profile is loaded when the account is running a
service.

-Sue

On Fri, 16 Feb 2007 15:07:03 -0800, Daniel Peterson


>SQL Service Account is running as the same service account as the SQL Server
>itself. If I login as that service account user, environment variables look
>like they get set properly. Looks like the server isn't configured with a
>proxy account.
>
>Didn't realize it was a scheduled job either, our developers left that out
>of the email they sent me, and I just noticed it when I was pawing through
>the job definition more.
>
>Looks like the owner of the job is sa.
>

>
>> > It looks like this FTP procedure is part of a defined job that's scheduled
>> > to run a couple of times a week. That step that uses xp_cmdshell to call
>> > FTP
>> > is set to run as "Self".
>> >
>> > Any other ideas?
>>
>> Oh, you didn't mention this was a scheduled job. What is the proxy/account
>> in use for the SQL Server Agent service (not SQL Server itself)? Who is the
>> owner of the job?
>>
>>
>>