Msg 8114, Level 16, State 8, Line 1 (invalid datetime)

[henky]

Hello,

I am currently busy to build a datawarehouse and I am importing data out of a linked server (Progress 9.1E).

I am working with SQL Server 2005.

I will get an error:
Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

The problem occures when I am selecting records out of a table containing a (for SQL Server none valid) date field out of the Progress database. Some of the dates are before the minimum possible datetime in SQL Server.

At the msdn page (

http://msdn2.microsoft.com/en-US/library/aa226403(SQL.80).aspx)

about this error I found a solution but it won't help me because I need this column.

Solution:
Action
Remove the remote table column of DBTYPE_DATE data type from the query select list or predicate list.

Can someone give me a more reliable solution?

Thanks in advance,
Henky

 

[LNBruno]

You have a data issue:

Some of the dates are before the minimum possible datetime in SQL Server

Either fix the data before importing or bring it in as a char and fix it on the SQL side are about the only two options that come to mind.

< M!ke >
Acupuncture Development: a jab well done.

 

[gmmastros]

Can you identify the dates that are causing this problem? If so, can you post what those dates are.

I suspect you may have a problem with the date format. Some countries use month-day-year and others use day-month-year.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[henky]

At first thanks for the replies LNBruno and gmmastros!

I am iusing the dateformat in the right way, the problem isn't in the dmy subject. The problem is founded in a application which excepts every date value (AD) which ever was at this world.

So there are dates in that database like 01-01-1650, 31-12-1512 and even 03-02-0206 (dd-mm-yyyy).

I tried to use a Convert(..., datefield...) but SQL Server still has to read the invalid date before it tries to convert it.
The data is coming out of a linked server and I don't know if I can (and how I can) 'fool' SQL Server to let it think its a char field when it access the table.
This is my create statement for the Linked server:

/****** Object:  LinkedServer [SBHPRO_GSK2]    Script Date: 06/20/2007 15:04:39 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SBHPRO_GSK2', @srvproduct=N'DATADIRECT 4.10 32-BIT Progress SQL92 v9.1E', @provider=N'MSDASQL', @datasrc=N'GSK2DD'
GO
EXEC master.dbo.sp_serveroption @server=N'SBHPRO_GSK2', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SBHPRO_GSK2', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SBHPRO_GSK2', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SBHPRO_GSK2', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SBHPRO_GSK2', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SBHPRO_GSK2', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SBHPRO_GSK2', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SBHPRO_GSK2', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SBHPRO_GSK2', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SBHPRO_GSK2', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SBHPRO_GSK2', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SBHPRO_GSK2', @optname=N'use remote collation', @optvalue=N'true'

 

[SQLDenis]

If you need to store dates before 1753 your only option is to store them as varchar

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[henky]

Denis,

Thanks for your reply.

I will store it in a varchar, thats not a problem, but I cant 'read' the record out of the table from linked server.

I can use

SELECT TOP 1
       field1,
       field2,
       datefield_with_a_few_invalid_values_for_SQL_Server
FROM   Table

because there is a valid code in the datefield in the first record, but when I use

SELECT field1,
       field2,
       datefield_with_a_few_invalid_values_for_SQL_Server
FROM   Table

I will get the error above.

Henky

 

[SQLDenis]

don't know about Progress but in sql you would do

select convert(varchar,datefield_with_a_few_invalid_values_for_SQL_Server,101)

where 101 is the format, there are others lookup convert in bol



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[henky]

I still got the same error when I was trying the code you mentioned above.

I did try that before:

I tried to use a Convert(..., datefield...) but SQL Server still has to read the invalid date before it tries to convert it.

Do you have another idea?

 

[henky]

should be

I tried to use a Convert(..., datefield...) but SQL Server still has to read the invalid date before it tries to convert it.

 

[henky]

Does anyone else got any idea how to solve this problem?

Henky

 

[SQLDenis]

>>but SQL Server still has to read the invalid date before it tries to convert it.

you have to make it a varchar in the query that runs on progress

take as look at this

SELECT * 
FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;
SourceDB=\\Secretserver\SecretFolder\bla\tables\report;
SourceType=DBF',
'SELECT *  FROM TableName where tradate >=date()-1')

this is obviously not valid SQL server SQL (date is not a valid function) but it is for that damn product FoxPro
I am running this from SQL and have no problems

try using OPENROWSET see if that makes a difference



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[LNBruno]

The bottom line is you're trying to shove an invalid date into a SQL Server table column defined as datetime.

Could you simply change the column in that table to VARCHAR before trying to import?

< M!ke >
Acupuncture Development: a jab well done.

 

[henky]

Thanks for the replies again!

LNBruno, The table I've to access, is a 'production' table, so I cant change the columntype into varchar.

SQLDenis, The Openrowset won't work for me

I am trying to create a public view in progress, which I hope to use in my select statement. The other problem now is how to make the progress view public (but thats another section at Tek-Tips.com

If you still have some advice, please post here!

 

[ESquared]

henky, why won't openrowset or openquery work? Using those, with the native sql syntax of the source database, convert the date column to text. If you have a linked server already created, then openquery ought to at least work because it runs against the linked server.

Otherwise, if you can't change the source table's data type, then create a view which converts that column to text or copy the data from that table to a new table. Or just copy the row key and that column text converted to a new table and join them, grabbing the text value.

Or use DTS to periodically copy data, transforming the data type of that column.

Or do some scrubbing in your source database to wipe or correct bad datetime values.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[henky]

Hello ESquared,

Thanks for your response!

I got a table in progress "pub.appointement" which contains the 'wrong' date field "app_date".

SBHPRO_GSK2 is my linked server "Progress 9.1E "

SELECT  TOP 1 *
FROM OPENQUERY (SBHPRO_GSK2,
               'SELECT Store_no, 
                App_date, 
                App_code
                FROM pub.appointement')

This code works, because the first record has got a valid date value. When I remove Top 1, it will give the error.

SELECT Store_no, 
       App_date, 
       STRING(App_date),  
       App_code
       FROM pub.appointement

This code works in the Progress Procedure Editor (Query tool of Progress) and returns the field "App_date" as a character column.

SELECT  TOP 1 *
FROM OPENQUERY (SBHPRO_GSK2,
               'SELECT Store_no, 
                App_date, 
                STRING(App_date),  
                App_code
                FROM pub.appointement')

This code won't work in SQL Server, but I used the same subselect as I used in the Procedure Editor.

This is the error:
Server: Msg 7321, Level 16, State 2, Line 1 An error occurred while
preparing a query for execution against OLE DB provider 'DB2OLEDB'.
[OLE/DB provider returned message: A SQL error has occurred. Please
consult the documentation for your specific DB2 version for a
description of the associated Native Error and SQL State.
SQLSTATE: 42707, SQLCODE: -208]

Do you have any experience / idea how to solve this problem?

Henky

 

[henky]

I gave the wrong error, that one was from A Microsoft site.

This is the error message I receive in SQL Server, but the native code does work in Progress.

Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT Store_no,
App_date,
STRING(App_date),
App_code
FROM pub.appointement" for execution against OLE DB provider "MSDASQL" for linked server "SBHPRO_GSK2".