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?
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
"Daniel Peterson" <pythas@.hotmail.com> wrote in message
news:B2531A19-F787-4365-9F5E-3B29AE3D7956@.microsoft.com...
> 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?
|||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?
"Aaron Bertrand [SQL Server MVP]" wrote:

> 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
>
> "Daniel Peterson" <pythas@.hotmail.com> wrote in message
> news:B2531A19-F787-4365-9F5E-3B29AE3D7956@.microsoft.com...
>
>
|||> 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?
|||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.
"Aaron Bertrand [SQL Server MVP]" wrote:

> 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?
>
>

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?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
"Daniel Peterson" <pythas@.hotmail.com> wrote in message
news:B2531A19-F787-4365-9F5E-3B29AE3D7956@.microsoft.com...
> 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?|||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?
"Aaron Bertrand [SQL Server MVP]" wrote:
> 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
>
> "Daniel Peterson" <pythas@.hotmail.com> wrote in message
> news:B2531A19-F787-4365-9F5E-3B29AE3D7956@.microsoft.com...
> > 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?
>
>|||> 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?|||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.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > 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?
>
>|||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
<pythas@.hotmail.com> wrote:
>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.
>"Aaron Bertrand [SQL Server MVP]" wrote:
>> > 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?
>>

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?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
"Daniel Peterson" <pythas@.hotmail.com> wrote in message
news:B2531A19-F787-4365-9F5E-3B29AE3D7956@.microsoft.com...
> 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?|||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 FT
P
is set to run as "Self".
Any other ideas?
"Aaron Bertrand [SQL Server MVP]" wrote:

> You need to set the TEMP/TMP environment variable settings for the user th
at
> 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
>
> "Daniel Peterson" <pythas@.hotmail.com> wrote in message
> news:B2531A19-F787-4365-9F5E-3B29AE3D7956@.microsoft.com...
>
>|||> 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?|||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.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Oh, you didn't mention this was a scheduled job. What is the proxy/accoun
t
> in use for the SQL Server Agent service (not SQL Server itself)? Who is t
he
> owner of the job?
>
>|||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
<pythas@.hotmail.com> wrote:
[vbcol=seagreen]
>SQL Service Account is running as the same service account as the SQL Serve
r
>itself. If I login as that service account user, environment variables loo
k
>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.
>"Aaron Bertrand [SQL Server MVP]" wrote:
>

Change user login

A users name has changed. In active directory the account has been renamed.
How do you associate this renamed NT account with the SQL login? The orgina
l
SID still exists so I cant add the new NT user name. Can I modify the
sysxuser table? : (Doug:
You can remove a login from the server, but leave the user with rights to
each database. Then, when you add the login again with the new name and gran
t
them access to specific databases, you can map that new Domain name to the
old user in the database.
For example: MyDomain\Bob is a login to the server and a user of the
database. I remove MyDomain\Bob as a login (leaving him as a user in the db)
.
Then I add MyDomain\Robert as a login to the server. When I assign him acces
s
as a user to the database, I map him to MyDomain\Bob.
It's not elegant, but it might work (no guarantees!)
Good luck.
Todd C
"doug" wrote:

> A users name has changed. In active directory the account has been rename
d.
> How do you associate this renamed NT account with the SQL login? The orgi
nal
> SID still exists so I cant add the new NT user name. Can I modify the
> sysxuser table? : (

Change the username in Active Directory

We use extended properties of Active Directory to filter the data in reporting services throw a web services. We send the userid (User!UserId) to the webservices and retrieve some information about the user.
But if we change the name of one user (rename not recreate), he lost all this properties. When try to execute a report show an error:

Parameter validation failed. It is not possible to provide valid values for all parameters. (rsParameterError) I know this issue is in the border of reporting services, but we have spent a lot of time without results and we need any help.

Thanks in advance

How are you doing your rename? Because that's where your issue is <s>.

If you're doing it through some GUI interface and that interface doesn't transfer your extended properties on a rename, then that interface has a bug in it <sigh>.

I do some AD coding but don't have an example I can throw in here. Basically, when any consumer queries AD for a user object, it is allowed to say what properties it's interested in retrieving. If it doesn't know about yours and doesn't retrieve them, it probably doesn't clone them along with the rest of the object. (I think, under the covers, a rename likely does a clone-and-delete.)

So you have to write something that does know about your extended properties and write a better rename...

>L<

|||We rename the user in Active directory but i supposed that in some part of reporting services store the previous username, guid or something to refers to the old user. When we use the user!userID functions, reporting services get the old value instead the new value.

Our process is:
We have two parameters, one with the user and other with the information to retrieve and send to another queries. Send this two parameters to the web services and retrieve a parameter (a custom property defined in AD) to send in another query.

We use directly in web browser the reporting services. And it is the default configuration of report server web.

Thanks for your patience

|||

>>We rename the user in Active directory

I know that's what you did. I asked you *how* you did this (through what interface).

But, yes, if you're getting the old version when you look at user!userID, it doesn't matter.

I don't see why the report server would be caching the previous user name etc, unless you have put this information in as the credentials of the user to run the report in the report configuration?

Could this possibly be an AD refresh type of problem, instead? IOW, you do the rename and all the domain servers don't know about it?

>L<

change the location of dat & log files**

Hi
I'm working with SQL server 2000
and I've a db called db1 and it's dat and log
files(for example) are located in root of d: directory in a server and
there are so many users working with this db.
and now I want to change the location (for example
move it to c:\sqldata,maybe because of not empty space in current
drive),how is it possible?
my second question is ,if I want to define the
second dat file and log file for a db which are placed in
different location how is it possible? and if I do
it successfully,is there anyway when I want to restore
the backup of this db ,make dat files or log files ,each of them in one
file beacause there is enough space in one drive in destination?
any help would be greatly thanked.RM
[url]http://support.microsoft.com/directory/article.asp?ID=kb;en-us;Q224071--[/url
]
Moving SQL Server Databases to a New Location
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsactt6e0hqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I've a db called db1 and it's dat and log
> files(for example) are located in root of d: directory in a server and
> there are so many users working with this db.
> and now I want to change the location (for example
> move it to c:\sqldata,maybe because of not empty space in current
> drive),how is it possible?
> my second question is ,if I want to define the
> second dat file and log file for a db which are placed in
> different location how is it possible? and if I do
> it successfully,is there anyway when I want to restore
> the backup of this db ,make dat files or log files ,each of them in one
> file beacause there is enough space in one drive in destination?
>
> any help would be greatly thanked.|||1. To move files, read KB 224071.
2. To add a database file, use ALTER DATABASE ADD FILE...
3. No, when you RESTORE, you have to have as many files as when you took the
backup, each file need
to have the same logical name, and be at least as big as when you took the b
ackup...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RM" <m_r1824@.yahoo.co.uk> wrote in message news:opsactt6e0hqligo@.msnews.microsoft.com...[vb
col=seagreen]
> Hi
> I'm working with SQL server 2000
> and I've a db called db1 and it's dat and log
> files(for example) are located in root of d: directory in a server and
> there are so many users working with this db.
> and now I want to change the location (for example
> move it to c:\sqldata,maybe because of not empty space in current
> drive),how is it possible?
> my second question is ,if I want to define the
> second dat file and log file for a db which are placed in
> different location how is it possible? and if I do
> it successfully,is there anyway when I want to restore
> the backup of this db ,make dat files or log files ,each of them in one
> file beacause there is enough space in one drive in destination?
>
> any help would be greatly thanked.[/vbcol]

change the location of dat & log files**

Hi
I'm working with SQL server 2000
and I've a db called db1 and it's dat and log
files(for example) are located in root of d: directory in a server and
there are so many users working with this db.
and now I want to change the location (for example
move it to c:\sqldata,maybe because of not empty space in current
drive),how is it possible?
my second question is ,if I want to define the
second dat file and log file for a db which are placed in
different location how is it possible? and if I do
it successfully,is there anyway when I want to restore
the backup of this db ,make dat files or log files ,each of them in one
file beacause there is enough space in one drive in destination?
any help would be greatly thanked.
RM
http://support.microsoft.com/directo...Q224071--
Moving SQL Server Databases to a New Location
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsactt6e0hqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I've a db called db1 and it's dat and log
> files(for example) are located in root of d: directory in a server and
> there are so many users working with this db.
> and now I want to change the location (for example
> move it to c:\sqldata,maybe because of not empty space in current
> drive),how is it possible?
> my second question is ,if I want to define the
> second dat file and log file for a db which are placed in
> different location how is it possible? and if I do
> it successfully,is there anyway when I want to restore
> the backup of this db ,make dat files or log files ,each of them in one
> file beacause there is enough space in one drive in destination?
>
> any help would be greatly thanked.
|||RM
http://support.microsoft.com/directo...Q224071--
Moving SQL Server Databases to a New Location
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsactt6e0hqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I've a db called db1 and it's dat and log
> files(for example) are located in root of d: directory in a server and
> there are so many users working with this db.
> and now I want to change the location (for example
> move it to c:\sqldata,maybe because of not empty space in current
> drive),how is it possible?
> my second question is ,if I want to define the
> second dat file and log file for a db which are placed in
> different location how is it possible? and if I do
> it successfully,is there anyway when I want to restore
> the backup of this db ,make dat files or log files ,each of them in one
> file beacause there is enough space in one drive in destination?
>
> any help would be greatly thanked.
|||1. To move files, read KB 224071.
2. To add a database file, use ALTER DATABASE ADD FILE...
3. No, when you RESTORE, you have to have as many files as when you took the backup, each file need
to have the same logical name, and be at least as big as when you took the backup...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RM" <m_r1824@.yahoo.co.uk> wrote in message news:opsactt6e0hqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I've a db called db1 and it's dat and log
> files(for example) are located in root of d: directory in a server and
> there are so many users working with this db.
> and now I want to change the location (for example
> move it to c:\sqldata,maybe because of not empty space in current
> drive),how is it possible?
> my second question is ,if I want to define the
> second dat file and log file for a db which are placed in
> different location how is it possible? and if I do
> it successfully,is there anyway when I want to restore
> the backup of this db ,make dat files or log files ,each of them in one
> file beacause there is enough space in one drive in destination?
>
> any help would be greatly thanked.
|||1. To move files, read KB 224071.
2. To add a database file, use ALTER DATABASE ADD FILE...
3. No, when you RESTORE, you have to have as many files as when you took the backup, each file need
to have the same logical name, and be at least as big as when you took the backup...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RM" <m_r1824@.yahoo.co.uk> wrote in message news:opsactt6e0hqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I've a db called db1 and it's dat and log
> files(for example) are located in root of d: directory in a server and
> there are so many users working with this db.
> and now I want to change the location (for example
> move it to c:\sqldata,maybe because of not empty space in current
> drive),how is it possible?
> my second question is ,if I want to define the
> second dat file and log file for a db which are placed in
> different location how is it possible? and if I do
> it successfully,is there anyway when I want to restore
> the backup of this db ,make dat files or log files ,each of them in one
> file beacause there is enough space in one drive in destination?
>
> any help would be greatly thanked.

change save settings directory on SSMS 2005

when i exit sql server management studio i get the following error:

The automatically saved settings file '\\SERVER\DATA\Sql Server Management Studio\Settings\CurrentSettings-2006-09-21.vssettings' is not available for write. You can change this file on the 'Import and Export Settings' Tools Options page.

i'm guessing this might be because of the corporate policy of pointing everyone's "My Documents" at a read-only folder.

i'd like to follow the advice on the dialog but unfortunately my Tools, Options dialog does not have an 'Import and Export Settings' page.

any ideas?

This problem relates to SSMS being an offshoot of the Visual Studio Shell. I would raise a bug in Connect http://connect.microsoft.com/sqlserver|||

You could try changing the registry I found this key that might be worth looking at

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell

|||

Probably the folder in which the vssettings file is present is a read-only location and that's why it's not allowing you to over-write it.

But you can very well re-direct your My Documents folder to any location(for which you have write access) and save the settings file will be saved when you close the SSMS.

For redirecting the My Documents folder, right click on it and select Properties and specify the new path in the Target folder location.

Hope that Helps.

Regards,

Rajesh

|||bug raised as https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=208955

Change NT Domain Name with SQL Servers in

Hello,
the administrators want to change our NT (NT 2000) domain name (no Active Di
rectory), where we have
several SQL Servers in.
Which actions should I do / which pitfalls should I take care of to make the
SQL Severs and their
accounts, services... run after the change ?
Thank You
Joachimone caveat with Service Accounts in general is to make sure that you change
the Service Accounts using Enterprise Manager as opposed to Services.msc
etc.
Most of the time you get away with if Service Account is Local Admin - but
if the box has been hardened you can get problems
documented below
http://support.microsoft.com/defaul...kb;en-us;283811
regards,
Andy
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:40E1972B.73739A36@.freenet.de...
> Hello,
> the administrators want to change our NT (NT 2000) domain name (no Active
Directory), where we have
> several SQL Servers in.
> Which actions should I do / which pitfalls should I take care of to make
the SQL Severs and their
> accounts, services... run after the change ?
> Thank You
> Joachim