SQL Server 2000 DTS package issue

[stainless]

We are replacing a development server and this has involved re-testing DTS packages on the new server.

We have hit an anomaly within a VB script within packages. Our package is called directly from a SQL job and, when we run this on the new server, it is building a filename with a suffix of the current date in M/dd/YYYY format. This is behaving differently to our previous server which was using dd-MM-YYYY format.

This is causing failures. Rather than change any such scripts, I need to find and fix the date setting that is defining the default format. Our SQL support team state they do not think there is an issue with SQL Server 2000 default date.

Interestingly, if I run the DTS package in Design pakage mode, it picks up the default server setting for my username (Uk date format) and works correctly i.e. uses dd-MM-YYYY format. It is simply when run from the job that US date is used.

[Erland Sommarskog]

Post by stainless
We are replacing a development server and this has involved re-testing
DTS packages on the new server.
We have hit an anomaly within a VB script within packages. Our package
is called directly from a SQL job and, when we run this on the new
server, it is building a filename with a suffix of the current date in
M/dd/YYYY format.. This is behaving differently to our previous server
which was using dd-MM-YYYY format.
This is causing failures. Rather than change any such scripts, I need to
find and fix the date setting that is defining the default format. Our
SQL support team state they do not think there is an issue with SQL
Server 2000 default date.
Interestingly, if I run the DTS package in Design pakage mode, it picks
up the default server setting for my username (Uk date format) and works
correctly i.e. uses dd-MM-YYYY format. It is simply when run from the
job that US date is used.

You can't accuse me for knowing too much about DTS (or SSIS for that
matter), but I would guess that the system locale for the server is
wrong. Also check the settings for the service account under which
SQL Server Agent runs.

[Bob Barrows]

Post by stainless
We are replacing a development server and this has involved
re-testing DTS packages on the new server.
We have hit an anomaly within a VB script within packages. Our
package is called directly from a SQL job and, when we run this on
the new server, it is building a filename with a suffix of the
current date in M/dd/YYYY format. This is behaving differently to our
previous server which was using dd-MM-YYYY format.
This is causing failures. Rather than change any such scripts, I need
to find and fix the date setting that is defining the default format.
Our SQL support team state they do not think there is an issue with
SQL Server 2000 default date.
Interestingly, if I run the DTS package in Design pakage mode, it
picks up the default server setting for my username (Uk date format)
and works correctly i.e. uses dd-MM-YYYY format. It is simply when
run from the job that US date is used.

When you run the job in Design, it uses YOUR regional settings, because
yours is the logged in account that is running the package.
When the job runs it, it uses the regional settings of the account under
which the job runs. By default, accounts created automatically are created
with US regional settings. You either have to configure the job to run under
an account with UK regional settings, or modify the regional settings for
the Job's account, probably by directly editng them in the Registry since
you will likely not know the password of the automatically-created account.
Wait ... this is SQL 2000, before the days of creating credentials and proxy
accounts in SSMS. You will need to configure SQL Agent to start up using an
account with the correct regional settings - a domain account is
recommended.

[Erland Sommarskog]

Post by Bob Barrows
By default, accounts created automatically are created
with US regional settings.

No, the default for accounts is the system locale. You set this up in
Regional Settings applet in the Control Panel, under the Advanced tab.
(Well, exactly where it is depends on the version of Windows.)

[Bob Barrows]

Post by Erland Sommarskog

Post by Bob Barrows
By default, accounts created automatically are created
with US regional settings.

No, the default for accounts is the system locale. You set this up in
Regional Settings applet in the Control Panel, under the Advanced tab.
(Well, exactly where it is depends on the version of Windows.)

That's not been my experience. New accounts that are automatically set up by
SQL Server installation seem to default to US settings, regardless of what's
been set in the Advanced tab you describe. We've had to deal with this
several times in our organization. I've not been involved personally,
residing in the US, but I've had to walk our UK users through the process of
fixing this several times. Not lately, though. Maybe this is something that
has been fixed in later versions of SQL. Don't forget that the OP is dealing
with SQL 2000 ...

[Erland Sommarskog]

Post by Bob Barrows
That's not been my experience. New accounts that are automatically set
up by SQL Server installation seem to default to US settings, regardless
of what's been set in the Advanced tab you describe. We've had to deal
with this several times in our organization. I've not been involved
personally, residing in the US, but I've had to walk our UK users
through the process of fixing this several times. Not lately, though.
Maybe this is something that has been fixed in later versions of SQL.
Don't forget that the OP is dealing with SQL 2000 ...

I did a quick test on a VM running Windows 2000. Added a new user and logged
in as that user. Dates etc was displayed according to Swedish regional
settings.

[Bob Barrows]

Post by Erland Sommarskog

Post by Bob Barrows
That's not been my experience. New accounts that are automatically
set up by SQL Server installation seem to default to US settings,
regardless of what's been set in the Advanced tab you describe.
We've had to deal with this several times in our organization. I've
not been involved personally, residing in the US, but I've had to
walk our UK users through the process of fixing this several times.
Not lately, though. Maybe this is something that has been fixed in
later versions of SQL. Don't forget that the OP is dealing with SQL
2000 ...

I did a quick test on a VM running Windows 2000. Added a new user and
logged in as that user. Dates etc was displayed according to Swedish
regional settings.

It's coming back now - the issue was when system accounts were used by the
servers - the system accounts seemed to be created with US settings. Again,
the advice to not use system accounts was relevant.

[stainless]

Thanks everyone

The situation was sorted by setting the UK default at server level, then it was all OK