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! Oracle linked server not working

we upgraded servers and now i can't link to an oracle server
old config:
windows2000
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
new config:
windows2003
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
the oracle db is 9.2.
i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005:]
i've scoured the internet and found numerous solutions to this, none of
which work.
i found an article in oracle technet that said to redo all permissions
on the oraclehome
directory. that didn't work.
i made sure the registry entries pointed to the correct oracle dll's.
no help.
i tried the 10g client with latest patch. no help.
i deleted and readded the linked server. no help.
i can use sqlplus or netmanager and connect to the oracle db just fine
from the sql server.
i don't know what else to try.
i'm wondering if it's windows2003 that's screwing things up.
has anybody been able to link to an oracle server from a win2003 server
running sql server sp4?Some additional questions:
What platform is the Oracle database?
Is connection pooling being used?
What language is doing the call or are you doing linked servers (four level
table qualifier)?
That particular error has a lot of possibles, as you found. Don't want to
re-hash what you have already ruled out.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"ch" wrote:
> we upgraded servers and now i can't link to an oracle server
> old config:
> windows2000
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> new config:
> windows2003
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> the oracle db is 9.2.
> i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005:]
> i've scoured the internet and found numerous solutions to this, none of
> which work.
> i found an article in oracle technet that said to redo all permissions
> on the oraclehome
> directory. that didn't work.
> i made sure the registry entries pointed to the correct oracle dll's.
> no help.
> i tried the 10g client with latest patch. no help.
> i deleted and readded the linked server. no help.
> i can use sqlplus or netmanager and connect to the oracle db just fine
> from the sql server.
> i don't know what else to try.
> i'm wondering if it's windows2003 that's screwing things up.
> has anybody been able to link to an oracle server from a win2003 server
> running sql server sp4?
>

Help! Index corrupting? DBCC CheckTable failing?

We are having quite a time since moving a large database to a new
server (actually built new server, renamed as old to make seamless for
users, etc.)

Import 104 million row database (5 column) into table (CD_Assets_bad2)
from existing (CD_Assets):
Account(varchar(8))
TransactionDate(datetime(8)
Flow(varchar(1))
Category(varchar(7))
TotalValue(decimal(8))

Run DBCC CheckTable - no issues.
Create 4 non clustered indexes (3 single column, 1 two-column). All
indexes create fine.

Run DBCC CheckTable again and receive the following:
Server: Msg 8951, Level 16, State 1, Line 1

Table error: Table 'CD_Assets_bad2' (ID 244195920). Missing or invalid
key in index 'idx_totalvalue' (ID 7) for the row:

Server: Msg 8955, Level 16, State 1, Line 1

Data row (1:11154499:98) identified by (RID = (1:11154499:98) ) has
index values (TotalValue = -10).

Server: Msg 8952, Level 16, State 1, Line 1

Table error: Database 'CD', index 'CD_Assets_bad2.idx_totalvalue' (ID
244195920) (index ID 7). Extra or invalid key for the keys:

Server: Msg 8956, Level 16, State 1, Line 1

Index row (1:20855652:338) with values (TotalValue = -0

4) points to the data row identified by (RID = (1:11154499:98)).

DBCC results for 'CD_Assets_bad2'.

There are 104397173 rows in 677904 pages for object 'CD_Assets_bad2'.

CHECKTABLE found 0 allocation errors and 2 consistency errors in table
'CD_Assets_bad2' (object ID 244195920).

repair_fast is the minimum repair level for the errors found by DBCC
CHECKTABLE (CD.dbo.CD_Assets_bad2 ).

Any ideas? It seems like some sort of corruption, but the index
creates fine. If anyone can help please let me know. If I can provide
any addtional information that might help, please let me know.

Thanks,
David
david.schwartz@.schwab.com[posted and mailed, please reply in news]

David Schwartz (david.schwartz@.schwab.com) writes:
> Any ideas? It seems like some sort of corruption, but the index
> creates fine. If anyone can help please let me know. If I can provide
> any addtional information that might help, please let me know.

Both errors 8252 and 8956 have articles in Books Online. (Just search
for the numbers.)

Both articles says that the index must be reparied or dropped.

Since your indexes appears to be corrupted when you build them, it smells
a hardware problem to me. I would probably open a case with Microsoft
to investigate this.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Try Creating clusterd index and import the data after dropping all the
existing indexes in the destination table.

david.schwartz@.schwab.com (David Schwartz) wrote in message news:<32d60003.0312021706.10bee3d6@.posting.google.com>...
> We are having quite a time since moving a large database to a new
> server (actually built new server, renamed as old to make seamless for
> users, etc.)
> Import 104 million row database (5 column) into table (CD_Assets_bad2)
> from existing (CD_Assets):
> Account(varchar(8))
> TransactionDate(datetime(8)
> Flow(varchar(1))
> Category(varchar(7))
> TotalValue(decimal(8))
> Run DBCC CheckTable - no issues.
> Create 4 non clustered indexes (3 single column, 1 two-column). All
> indexes create fine.
> Run DBCC CheckTable again and receive the following:
> Server: Msg 8951, Level 16, State 1, Line 1
> Table error: Table 'CD_Assets_bad2' (ID 244195920). Missing or invalid
> key in index 'idx_totalvalue' (ID 7) for the row:
> Server: Msg 8955, Level 16, State 1, Line 1
> Data row (1:11154499:98) identified by (RID = (1:11154499:98) ) has
> index values (TotalValue = -10).
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'CD', index 'CD_Assets_bad2.idx_totalvalue' (ID
> 244195920) (index ID 7). Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:20855652:338) with values (TotalValue = -0
> 4) points to the data row identified by (RID = (1:11154499:98)).
> DBCC results for 'CD_Assets_bad2'.
> There are 104397173 rows in 677904 pages for object 'CD_Assets_bad2'.
> CHECKTABLE found 0 allocation errors and 2 consistency errors in table
> 'CD_Assets_bad2' (object ID 244195920).
> repair_fast is the minimum repair level for the errors found by DBCC
> CHECKTABLE (CD.dbo.CD_Assets_bad2 ).
> Any ideas? It seems like some sort of corruption, but the index
> creates fine. If anyone can help please let me know. If I can provide
> any addtional information that might help, please let me know.
> Thanks,
> David
> david.schwartz@.schwab.com

Help! How do you remove a sql group when...

Hi all,
I am attempting to remove a sql server group from my list in enterprise
manager, none of the old registered sql servers in my group exist and the
connections to them times out. How does one get around this problem.
Cheers, LerpCan you not right click on the group in Enterprise Manager and choose
delete to delete the group? You will have to delete the server
registrations first.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Help! How do you remove a sql group when...

Hi all,
I am attempting to remove a sql server group from my list in enterprise
manager, none of the old registered sql servers in my group exist and the
connections to them times out. How does one get around this problem.
Cheers, LerpCan you not right click on the group in Enterprise Manager and choose
delete to delete the group? You will have to delete the server
registrations first.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Help! How do you remove a sql group when...

Hi all,
I am attempting to remove a sql server group from my list in enterprise
manager, none of the old registered sql servers in my group exist and the
connections to them times out. How does one get around this problem.
Cheers, Lerp
Can you not right click on the group in Enterprise Manager and choose
delete to delete the group? You will have to delete the server
registrations first.
Rand
This posting is provided "as is" with no warranties and confers no rights.