Error 8114 Date data

I am trying to pull over data in for a specified date from an ODBC connected
AS/400 table, and I get the following error.
Server: Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.
Here's an example of what the field looks like in the AS/400 table:
1999-08-24 00:00:00.000
I thought that I could just as "where date = '2005-07-01 00:00:00.00'" but
it doesn't work.
Any thoughts'
Thanks in advance.This could be the limitation of the provider you use. Try a different or
newer provider.
Adrian
"aschossler" <aschossler@.discussions.microsoft.com> wrote in message
news:6C6CD9AF-F37E-459E-9A42-64CE221D2E94@.microsoft.com...
>I am trying to pull over data in for a specified date from an ODBC
>connected
> AS/400 table, and I get the following error.
> Server: Msg 8114, Level 16, State 8, Line 1
> Error converting data type DBTYPE_DBTIMESTAMP to datetime.
> Here's an example of what the field looks like in the AS/400 table:
> 1999-08-24 00:00:00.000
> I thought that I could just as "where date = '2005-07-01 00:00:00.00'"
> but
> it doesn't work.
> Any thoughts'
> Thanks in advance.sql

Error 8114 Date data

I am trying to pull over data in for a specified date from an ODBC connected
AS/400 table, and I get the following error.
Server: Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.
Here's an example of what the field looks like in the AS/400 table:
1999-08-24 00:00:00.000
I thought that I could just as "where date = '2005-07-01 00:00:00.00'" but
it doesn't work.
Any thoughts?
Thanks in advance.
This could be the limitation of the provider you use. Try a different or
newer provider.
Adrian
"aschossler" <aschossler@.discussions.microsoft.com> wrote in message
news:6C6CD9AF-F37E-459E-9A42-64CE221D2E94@.microsoft.com...
>I am trying to pull over data in for a specified date from an ODBC
>connected
> AS/400 table, and I get the following error.
> Server: Msg 8114, Level 16, State 8, Line 1
> Error converting data type DBTYPE_DBTIMESTAMP to datetime.
> Here's an example of what the field looks like in the AS/400 table:
> 1999-08-24 00:00:00.000
> I thought that I could just as "where date = '2005-07-01 00:00:00.00'"
> but
> it doesn't work.
> Any thoughts?
> Thanks in advance.

Error 8114 Date data

I am trying to pull over data in for a specified date from an ODBC connected
AS/400 table, and I get the following error.
Server: Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.
Here's an example of what the field looks like in the AS/400 table:
1999-08-24 00:00:00.000
I thought that I could just as "where date = '2005-07-01 00:00:00.00'" but
it doesn't work.
Any thoughts'
Thanks in advance.This could be the limitation of the provider you use. Try a different or
newer provider.
Adrian
"aschossler" <aschossler@.discussions.microsoft.com> wrote in message
news:6C6CD9AF-F37E-459E-9A42-64CE221D2E94@.microsoft.com...
>I am trying to pull over data in for a specified date from an ODBC
>connected
> AS/400 table, and I get the following error.
> Server: Msg 8114, Level 16, State 8, Line 1
> Error converting data type DBTYPE_DBTIMESTAMP to datetime.
> Here's an example of what the field looks like in the AS/400 table:
> 1999-08-24 00:00:00.000
> I thought that I could just as "where date = '2005-07-01 00:00:00.00'"
> but
> it doesn't work.
> Any thoughts'
> Thanks in advance.

Error 644 Severity level 21

Hi,
I got the following error message for one of our databases:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 3/25/2005
Time: 9:18:53 AM
User: XXXXXXXX\Administrator
Computer: XXXXXX
Description:
Error: 644, Severity: 21, State: 3
Could not find the index entry for RID '16c42dc515100' in index page
(1:1725), index ID 0, database 'XXXXXXXXX_XXXXX_XXX_MSCRM'.
I looked up the error in Books Online and it told me to run DBCC CHECKDB to
see the extent of the damage. It gave me the following result:
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'XXXXXXXXX_XXXXX_XXX_MSCRM', index
'sysobjects.ncsysobjects2' (ID 1) (index ID 3). Extra or invalid key for the
keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:155:91) with values (parent_obj = 1237579447 and id = 365243844) points to the data row identified by ().
DBCC results for 'XXXXXXXXX_XXXXX_XXX_MSCRM'.
DBCC results for 'sysobjects'.
There are 2315 rows in 49 pages for object 'sysobjects'.
CHECKDB found 0 allocation errors and 1 consistency errors in table
'sysobjects' (object ID 1).
These don't seem to match. Should they?
The next thing that Books Online says to do is to run DBCC CHECKDB with the
REPAIR_REBUILD clause to fix the damage. Is this still an appropriate
course of action given that the error reported by checkdb doesn't seem to be
the one from the event log?
One of the other dba's here said that there may be a better way to rebuild
the index than to run the repair_rebuild on the entire database. I just
wanted to double check and get some other opinions/feedback before I
continued. Any help would really be appreciated. Thank you
-JohnHi
You have corruption.
Do a full backup of the database, then run the DBCC. There is probably more
than one problem so the DBCC finds the other one first.
At the same time, look for your backups because if the corruption is bad
enough, you need to restore to you last good backup.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John Rosenlof" <greyseal96@.hotmail.com> wrote in message
news:O#0LBPYMFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I got the following error message for one of our databases:
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> Date: 3/25/2005
> Time: 9:18:53 AM
> User: XXXXXXXX\Administrator
> Computer: XXXXXX
> Description:
> Error: 644, Severity: 21, State: 3
> Could not find the index entry for RID '16c42dc515100' in index page
> (1:1725), index ID 0, database 'XXXXXXXXX_XXXXX_XXX_MSCRM'.
> I looked up the error in Books Online and it told me to run DBCC CHECKDB
to
> see the extent of the damage. It gave me the following result:
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'XXXXXXXXX_XXXXX_XXX_MSCRM', index
> 'sysobjects.ncsysobjects2' (ID 1) (index ID 3). Extra or invalid key for
the
> keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:155:91) with values (parent_obj = 1237579447 and id => 365243844) points to the data row identified by ().
> DBCC results for 'XXXXXXXXX_XXXXX_XXX_MSCRM'.
> DBCC results for 'sysobjects'.
> There are 2315 rows in 49 pages for object 'sysobjects'.
> CHECKDB found 0 allocation errors and 1 consistency errors in table
> 'sysobjects' (object ID 1).
> These don't seem to match. Should they?
> The next thing that Books Online says to do is to run DBCC CHECKDB with
the
> REPAIR_REBUILD clause to fix the damage. Is this still an appropriate
> course of action given that the error reported by checkdb doesn't seem to
be
> the one from the event log?
> One of the other dba's here said that there may be a better way to rebuild
> the index than to run the repair_rebuild on the entire database. I just
> wanted to double check and get some other opinions/feedback before I
> continued. Any help would really be appreciated. Thank you
> -John
>

error 30311: Value of type 'System.DBNull' cannot be converted to 'Date'

I'm creating an ISP for extractig data from a text file and put it in a database.

One of the fields in my textfile contains the value '0' or a date. If it's '0' it should be converted to the Null Value. The column in which it has to be saved is of type smalldatetime.

This is the code of my script where I want to check the value of the field in my textfile and convert it to a date or to Null.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If Row.cdDateIn = "" Or Row.cdDateIn= "0" Then

Row.cdDateInCorr = CDate(DBNull.Value)

ElseIf Row.cdDateIn_IsNull Then

Row.cdDateInCorr = CDate(DBNull.Value)

Else

Row.cdDateInCorr = CDate(Row.cdDateIn)

End If

End Sub

The error that I get is:

Validation error. Extract FinCD: Conversion of cdDateIn [753]: Eroor 30311: Value of type 'System.DBNull' cannot ben converted to 'Date'.

How do I resolve this problem ?

Why not use a derived column? Expression follows: (Typing from memory)

cdDateIn == "" || cdDateIn == "0" || ISNULL(cdDateIn) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)cdDateIn

|||You can just set Row.cdDateInCorr_IsNull = true.
|||

JayH wrote:

You can just set Row.cdDateInCorr_IsNull = true.

Correct, though if this is all you're doing in the script component, I would stay away from that and use a derived column. There's no reason for the overhead of the script in this case.

|||

I've used the code JayH suggested and this works.

Phil, your code is a bit like chinese to me. This ISP I'm working on is my first project in .NET. But I will check this derived column thing.

Thanks four your help.

|||

Phil Brammer wrote:

JayH wrote:

You can just set Row.cdDateInCorr_IsNull = true.

Correct, though if this is all you're doing in the script component, I would stay away from that and use a derived column. There's no reason for the overhead of the script in this case.

Are you sure about this - the IsNull property is for evaluation and not for assigning true or false to it - I believe that it will reset itself when you leave the script task - or am I wrong?
|||

Simon Givoni wrote:

Phil Brammer wrote:

JayH wrote:

You can just set Row.cdDateInCorr_IsNull = true.

Correct, though if this is all you're doing in the script component, I would stay away from that and use a derived column. There's no reason for the overhead of the script in this case.

Are you sure about this - the IsNull property is for evaluation and not for assigning true or false to it - I believe that it will reset itself when you leave the script task - or am I wrong?

Yes, I'm sure. You can set the property to True and the null will stick. You cannot set it to False, but it will automatically become False if you set a value to the column. SP2 will raise an exception if you set it to False.

|||Ah yes you are right - here are the full explanation:

The IsNull property of columns in the Script Component now raises a warning when it is used incorrectly.
The code generated by the Script component contains a <columnname> and a <columnname>_IsNull property for each input and output column. The intention of the <columnname>_IsNull property is to allow the user to specify a Null value for the column by setting the value of the property to True. To specify a non-null value, the user should assign a value to the <columnname> property. In earlier versions, if you set <columnname>_IsNull to False, the property was silently reset to True. In SP2, the Script component raises a warning message about the incorrect usage.

Source: http://msdn2.microsoft.com/en-us/library/bb283536.aspx#BKMK_IntegrationServices

Error 242 select with date conditions now failing

We have reinstalled SQL Server 2000 on a new server but select statements on
datetime fields are now failing where they didn't before. e.g.
SELECT * FROM CONTHEAD WHERE start_dt<='21/02/2005' and (expiry_dt is null
or expiry_dt>='21/02/2005')
gives the error
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
It works fine without the quotes around the dates but we have tons of code
that assumes quotes round dates is needed. There must be a global setting
somewhere that needs set. Does anyone know which setting it is?
Thanks for any help.
BernieBernie
What is a datatype of your start_dt and expiry_dt columns ?
What does it give you?
SELECT * FROM CONTHEAD WHERE start_dt<='20050221' and (expiry_dt is null
or expiry_dt>='20050221')
"Bernie Beattie" <BernieBeattie@.discussions.microsoft.com> wrote in message
news:066DA44E-AAAE-4134-9AEB-BC6C6E761A64@.microsoft.com...
> We have reinstalled SQL Server 2000 on a new server but select statements
on
> datetime fields are now failing where they didn't before. e.g.
> SELECT * FROM CONTHEAD WHERE start_dt<='21/02/2005' and (expiry_dt is null
> or expiry_dt>='21/02/2005')
> gives the error
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> It works fine without the quotes around the dates but we have tons of code
> that assumes quotes round dates is needed. There must be a global setting
> somewhere that needs set. Does anyone know which setting it is?
> Thanks for any help.
> Bernie|||This is why it pays to use the standard ISO format dates in code. Never rely
on implicit covnversions from regional date formats - the behaviour of those
conversions is too dependent on factors that you don't have complete control
over.
The following are the ISO standard forms for dates. These will work
regardless of server and connection settings:
'20050221' -- Just the date
'2005-02-21T17:59:00' -- Date/hours/minutes/seconds
'2005-02-21T17:59:00.000' -- Date/hours/minutes/seconds/milliseconds
The workaround for your situation is to set the default settings for
DATEFORMAT and LANGUAGE on your server. This will probably solve your
immediate problem but may not fix it permanently. The server settings are
only *default* settings so they can be overridden at the client. That's a
good thing because maybe not everyone will want the same settings but it
means that to make your code reliable you have to use the ISO format for dat
e
literals.
David Portas
SQL Server MVP
--|||Thanks for pointing me in the right direction David.
Our application that sends the select statements to SQL server can simply
send a set dateformat command when the ODBC connection is opened. That
should make all the application statements work just fine regardless of the
actual default setting on the server.
Bernie
"David Portas" wrote:

> This is why it pays to use the standard ISO format dates in code. Never re
ly
> on implicit covnversions from regional date formats - the behaviour of tho
se
> conversions is too dependent on factors that you don't have complete contr
ol
> over.
> The following are the ISO standard forms for dates. These will work
> regardless of server and connection settings:
> '20050221' -- Just the date
> '2005-02-21T17:59:00' -- Date/hours/minutes/seconds
> '2005-02-21T17:59:00.000' -- Date/hours/minutes/seconds/milliseconds
> The workaround for your situation is to set the default settings for
> DATEFORMAT and LANGUAGE on your server. This will probably solve your
> immediate problem but may not fix it permanently. The server settings are
> only *default* settings so they can be overridden at the client. That's a
> good thing because maybe not everyone will want the same settings but it
> means that to make your code reliable you have to use the ISO format for d
ate
> literals.
> --
> David Portas
> SQL Server MVP
> --