Help! SQLExpress2005 date formats

I installed SQLExpress2005 and restored some old MSSQL7 databases. This
seemed to work at first glance, but there is at least one problem. These
databases contained datetime values.
And we are in Germany where dates are written like this: 25.12.2005
(christmas, for example).
My ColdFusion application contains queries like this:
SELECT Datum, Kurztext, Feiertag, Brauchtumstag, Tagestyp, Freianteil,
Notiz
FROM tblBetriebskalender
WHEREdatum between '01.12.2005' and '31.12.2005'
ORDER BYDatum
this used to work without problems.
Now the same queries don't work because for a date such as '31.12.2005',
SQLServer thinks 12 is the day and 31 is the month.
What can I do? Is there a central switch somewhere that changes the data
formats? Unfortunately I don't know what it was set to in the old system.
(The database properties option tab shows that collation is now set to
SQL_Latin1_General_CP1_CI_AS, and the compatibility level is SQLServer 7.0.)
-Michael
hi Michael,
Michael Peters wrote:
> I installed SQLExpress2005 and restored some old MSSQL7 databases.
> This seemed to work at first glance, but there is at least one
> problem. These databases contained datetime values.
> And we are in Germany where dates are written like this: 25.12.2005
> (christmas, for example).
> My ColdFusion application contains queries like this:
> SELECT Datum, Kurztext, Feiertag, Brauchtumstag, Tagestyp, Freianteil,
> Notiz
> FROM tblBetriebskalender
> WHERE datum between '01.12.2005' and '31.12.2005'
> ORDER BY Datum
> this used to work without problems.
> Now the same queries don't work because for a date such as
> '31.12.2005', SQLServer thinks 12 is the day and 31 is the month.
> What can I do? Is there a central switch somewhere that changes the
> data formats? Unfortunately I don't know what it was set to in the
> old system.
> (The database properties option tab shows that collation is now set to
> SQL_Latin1_General_CP1_CI_AS, and the compatibility level is
> SQLServer 7.0.)
> -Michael
Dates should be handled in ISO format YYYY-MM-DD or YYYYMMDD in order to
avoid this kind of problem as the dateformat is language specific and each
login has na associated language default.. probably you had german setting
for your login on the old server and englis setting on the new one...
you can change the login's language
ALTER LOGIN login_name
DEFAULT_LANGUAGE = new_language
http://msdn2.microsoft.com/en-us/library/ms189828.aspx
or, at connection level, using the SET DATEFORMAT
http://msdn.microsoft.com/library/de...-set_052s.asp,
but I strongly urge you to modify your language management to standard
ISO/ODBC format..
an unvaluable article by Notre Dame SQL Server Kalen Delaney about "dates"
is available at
http://www.windowsitpro.com/SQLServe...147/9147.html, but
only for subscriber...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||thanks very much Andrea.
-Michael

Help! How to install Sql Svr 2k to Win2k3 Least Privilege Acct?

We are trying to install SQLSever2000 on a Windows 2003 Standard Server to an
account other than Administrator.
We used this guide (which works fine for Win2k Svr)
http://support.microsoft.com/default...;en-us;Q283811
We get an Error 5: Access Denied message when starting SQL Server Agent, but
we don't get enough information to determine what has been denied.
It is obvious that there is some other setting that must be applied in
WinSvr 2k3 that we are not doing - but we didn't see it in the guide. We have
attempted to give the "SQLAdmin" account access to all files and directories
that are related to MS SQL Server.
Unfortunately, our fall back is to install SQL Server to run as
Administrator - because that works.
Any ideas/info is greatly appreciated.
email: hal.hayes@.REMOVE_CAPS_AND_INVALIDacritech.com
I think something else must be wrong or you would not be able to start SQL
Server. Check your event logs, SQL errorlogs and SQL Agent logs for
additional information.
Use SQL Enterprise Manager whenever possible to change the start-up account
for SQL Server and SQL Agent services. SEM will set file and registry
permissions whereas Service Control Manager won't.
Most importantly, ensure that non-admin start-up accounts have following
user privileges:
. Act as Part of the Operating System = SeTcbPrivilege
. Bypass Traverse Checking = SeChangeNotify
. Lock Pages In Memory = SeLockMemory
. Log on as a Batch Job = SeBatchLogonRight
. Log on as a Service = SeServiceLogonRight
. Replace a Process Level Token = SeAssignPrimaryTokenPrivilege
If this is a cluster follow the instructions in
http://support.microsoft.com/kb/239885.
Adrian
"Hal" <Hal@.discussions.microsoft.com> wrote in message
news:D2E25E17-DE30-4AD9-AD55-96BBB5D78ABE@.microsoft.com...
> We are trying to install SQLSever2000 on a Windows 2003 Standard Server to
> an
> account other than Administrator.
> We used this guide (which works fine for Win2k Svr)
> http://support.microsoft.com/default...;en-us;Q283811
> We get an Error 5: Access Denied message when starting SQL Server Agent,
> but
> we don't get enough information to determine what has been denied.
> It is obvious that there is some other setting that must be applied in
> WinSvr 2k3 that we are not doing - but we didn't see it in the guide. We
> have
> attempted to give the "SQLAdmin" account access to all files and
> directories
> that are related to MS SQL Server.
> Unfortunately, our fall back is to install SQL Server to run as
> Administrator - because that works.
> Any ideas/info is greatly appreciated.
> email: hal.hayes@.REMOVE_CAPS_AND_INVALIDacritech.com

Help! How to install Sql Svr 2k to Win2k3 Least Privilege Acct?

We are trying to install SQLSever2000 on a Windows 2003 Standard Server to an
account other than Administrator.
We used this guide (which works fine for Win2k Svr)
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q283811
We get an Error 5: Access Denied message when starting SQL Server Agent, but
we don't get enough information to determine what has been denied.
It is obvious that there is some other setting that must be applied in
WinSvr 2k3 that we are not doing - but we didn't see it in the guide. We have
attempted to give the "SQLAdmin" account access to all files and directories
that are related to MS SQL Server.
Unfortunately, our fall back is to install SQL Server to run as
Administrator - because that works.
Any ideas/info is greatly appreciated.
email: hal.hayes@.REMOVE_CAPS_AND_INVALIDacritech.comI think something else must be wrong or you would not be able to start SQL
Server. Check your event logs, SQL errorlogs and SQL Agent logs for
additional information.
Use SQL Enterprise Manager whenever possible to change the start-up account
for SQL Server and SQL Agent services. SEM will set file and registry
permissions whereas Service Control Manager won't.
Most importantly, ensure that non-admin start-up accounts have following
user privileges:
. Act as Part of the Operating System = SeTcbPrivilege
. Bypass Traverse Checking = SeChangeNotify
. Lock Pages In Memory = SeLockMemory
. Log on as a Batch Job = SeBatchLogonRight
. Log on as a Service = SeServiceLogonRight
. Replace a Process Level Token = SeAssignPrimaryTokenPrivilege
If this is a cluster follow the instructions in
http://support.microsoft.com/kb/239885.
Adrian
"Hal" <Hal@.discussions.microsoft.com> wrote in message
news:D2E25E17-DE30-4AD9-AD55-96BBB5D78ABE@.microsoft.com...
> We are trying to install SQLSever2000 on a Windows 2003 Standard Server to
> an
> account other than Administrator.
> We used this guide (which works fine for Win2k Svr)
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q283811
> We get an Error 5: Access Denied message when starting SQL Server Agent,
> but
> we don't get enough information to determine what has been denied.
> It is obvious that there is some other setting that must be applied in
> WinSvr 2k3 that we are not doing - but we didn't see it in the guide. We
> have
> attempted to give the "SQLAdmin" account access to all files and
> directories
> that are related to MS SQL Server.
> Unfortunately, our fall back is to install SQL Server to run as
> Administrator - because that works.
> Any ideas/info is greatly appreciated.
> email: hal.hayes@.REMOVE_CAPS_AND_INVALIDacritech.com

Help! How to install Sql Svr 2k to Win2k3 Least Privilege Acct?

We are trying to install SQLSever2000 on a Windows 2003 Standard Server to a
n
account other than Administrator.
We used this guide (which works fine for Win2k Svr)
http://support.microsoft.com/defaul...b;en-us;Q283811
We get an Error 5: Access Denied message when starting SQL Server Agent, but
we don't get enough information to determine what has been denied.
It is obvious that there is some other setting that must be applied in
WinSvr 2k3 that we are not doing - but we didn't see it in the guide. We hav
e
attempted to give the "SQLAdmin" account access to all files and directories
that are related to MS SQL Server.
Unfortunately, our fall back is to install SQL Server to run as
Administrator - because that works.
Any ideas/info is greatly appreciated.
email: hal.hayes@.REMOVE_CAPS_AND_INVALIDacritech.comI think something else must be wrong or you would not be able to start SQL
Server. Check your event logs, SQL errorlogs and SQL Agent logs for
additional information.
Use SQL Enterprise Manager whenever possible to change the start-up account
for SQL Server and SQL Agent services. SEM will set file and registry
permissions whereas Service Control Manager won't.
Most importantly, ensure that non-admin start-up accounts have following
user privileges:
. Act as Part of the Operating System = SeTcbPrivilege
. Bypass Traverse Checking = SeChangeNotify
. Lock Pages In Memory = SeLockMemory
. Log on as a Batch Job = SeBatchLogonRight
. Log on as a Service = SeServiceLogonRight
. Replace a Process Level Token = SeAssignPrimaryTokenPrivilege
If this is a cluster follow the instructions in
http://support.microsoft.com/kb/239885.
Adrian
"Hal" <Hal@.discussions.microsoft.com> wrote in message
news:D2E25E17-DE30-4AD9-AD55-96BBB5D78ABE@.microsoft.com...
> We are trying to install SQLSever2000 on a Windows 2003 Standard Server to
> an
> account other than Administrator.
> We used this guide (which works fine for Win2k Svr)
> http://support.microsoft.com/defaul...b;en-us;Q283811
> We get an Error 5: Access Denied message when starting SQL Server Agent,
> but
> we don't get enough information to determine what has been denied.
> It is obvious that there is some other setting that must be applied in
> WinSvr 2k3 that we are not doing - but we didn't see it in the guide. We
> have
> attempted to give the "SQLAdmin" account access to all files and
> directories
> that are related to MS SQL Server.
> Unfortunately, our fall back is to install SQL Server to run as
> Administrator - because that works.
> Any ideas/info is greatly appreciated.
> email: hal.hayes@.REMOVE_CAPS_AND_INVALIDacritech.com