Error converting data type DBTYPE_DBTIMESTAMP to datetime

Hi

I am trying to transfer data from Access 2000 database to SQL server 2005 via SSIS. My access database table has a field with data type Date/Time and SQL server table has a field with data type datetime. When I am running the SSIS package, it is throwing the following error and stops execution.

[SQL Server Destination [12466]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E07 Description: "Error converting data type DBTYPE_DBTIMESTAMP to datetime.".

Please help.

Thanks in advance,
Ramzee

According to BOL (http://msdn2.microsoft.com/en-us/library/ms175970.aspx) the types can be converted, so that should work. Have you identified the values that fail? Using the error output is a very easy way to capture this data. My best guess for now is the values are out of range for the SQL datetime type. A derived column transformation could be used to massage these values depending on requirements.|||

Thanks Darren!

I used the script component to identify the wrong date entries. Now it works!

Thanks once again.

|||

I am having the same error. A simple select from Query Analyzer

Select * from HCPROD..HC.UNIT U

Even if I specifically reference a number column I get the same error

Select BLOCK_ID from HCPROD..HC.UNIT U

The way I see it is that there is a column, probably the DATE_MODIFIED column, somewhere in the database that has a date & time value that the driver cannot process. Why the error occurs even if I do not reference the column is a mystery.

I am using Microsoft OLE DB Provider for Oracle

Note that I have insured that every date field in the table is in 20th or 21st century

|||Some more information. We are using Oracle version 10g. I wonder if there is an updated driver from Microsoft? We searched and could not find one ourselves.|||SELECT TOP 10 * FROM [ORACLE_LOOKUP]..[SchemaName].[TableName] AlaisName

Error converting data type DBTYPE_DBTIMESTAMP to datetime

SELECT * FROM
OPENQUERY (
ORACLE_LOOKUP,
'SELECT TO_CHAR(DateColumnName) FROM TableName')

The following workaround was foud on the internet

A workaround would be to use the OPENQUERY function in SQL Server. This
function allows you to execute a pass-through query on the linked server.
By issuing a pass-through query, you can then take advantage of Oracle's
built-in functions to convert the date to a character data type or NULL.
Examples of both are as follows:

In the following example, the column "hiredate" is converted to a string in
the format of MM-DD-YYYY.

SELECT *
FROM OPENQUERY(, 'SELECT empno,
TO_CHAR(hiredate,''MM-DD-YYYY'') FROM scott.emp')

In this example we use a combination of two Oracle built-in functions,
DECODE and GREATEST to convert any hiredate that is earlier than 01/01/1753
(SQL Server's lower bound) to NULL.

SELECT *
FROM OPENQUERY(,'SELECT DECODE (hiredate,
GREATEST(hiredate, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), hiredate, NULL)
FROM scott.emp')

In case you are not familiar with Oracle built-ins, here is a description of
the two used in the query above.

DECODE is similar to a case statement in SQL Server. Its syntax is as
follows :

DECODE(, , , , ,...,
)

The is compared to each value. If there is a match,
the corresponding is returned. Otherwise, the value is
returned.

GREATEST returns the greatest value in the list of expressions.

GREATEST(, , ...)
Reply With Quote

Error converting data type DBTYPE_DBTIMESTAMP to datetime

Hi

I am trying to transfer data from Access 2000 database to SQL server 2005 via SSIS. My access database table has a field with data type Date/Time and SQL server table has a field with data type datetime. When I am running the SSIS package, it is throwing the following error and stops execution.

[SQL Server Destination [12466]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E07 Description: "Error converting data type DBTYPE_DBTIMESTAMP to datetime.".

Please help.

Thanks in advance,
Ramzee

According to BOL (http://msdn2.microsoft.com/en-us/library/ms175970.aspx) the types can be converted, so that should work. Have you identified the values that fail? Using the error output is a very easy way to capture this data. My best guess for now is the values are out of range for the SQL datetime type. A derived column transformation could be used to massage these values depending on requirements.|||

Thanks Darren!

I used the script component to identify the wrong date entries. Now it works!

Thanks once again.

|||

I am having the same error. A simple select from Query Analyzer

Select * from HCPROD..HC.UNIT U

Even if I specifically reference a number column I get the same error

Select BLOCK_ID from HCPROD..HC.UNIT U

The way I see it is that there is a column, probably the DATE_MODIFIED column, somewhere in the database that has a date & time value that the driver cannot process. Why the error occurs even if I do not reference the column is a mystery.

I am using Microsoft OLE DB Provider for Oracle

Note that I have insured that every date field in the table is in 20th or 21st century

|||Some more information. We are using Oracle version 10g. I wonder if there is an updated driver from Microsoft? We searched and could not find one ourselves.|||SELECT TOP 10 * FROM [ORACLE_LOOKUP]..[SchemaName].[TableName] AlaisName

Error converting data type DBTYPE_DBTIMESTAMP to datetime

SELECT * FROM
OPENQUERY (
ORACLE_LOOKUP,
'SELECT TO_CHAR(DateColumnName) FROM TableName')

The following workaround was foud on the internet

A workaround would be to use the OPENQUERY function in SQL Server. This
function allows you to execute a pass-through query on the linked server.
By issuing a pass-through query, you can then take advantage of Oracle's
built-in functions to convert the date to a character data type or NULL.
Examples of both are as follows:

In the following example, the column "hiredate" is converted to a string in
the format of MM-DD-YYYY.

SELECT *
FROM OPENQUERY(, 'SELECT empno,
TO_CHAR(hiredate,''MM-DD-YYYY'') FROM scott.emp')

In this example we use a combination of two Oracle built-in functions,
DECODE and GREATEST to convert any hiredate that is earlier than 01/01/1753
(SQL Server's lower bound) to NULL.

SELECT *
FROM OPENQUERY(,'SELECT DECODE (hiredate,
GREATEST(hiredate, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), hiredate, NULL)
FROM scott.emp')

In case you are not familiar with Oracle built-ins, here is a description of
the two used in the query above.

DECODE is similar to a case statement in SQL Server. Its syntax is as
follows :

DECODE(, , , , ,...,
)

The is compared to each value. If there is a match,
the corresponding is returned. Otherwise, the value is
returned.

GREATEST returns the greatest value in the list of expressions.

GREATEST(, , ...)
Reply With Quote

Error converting data type DBTYPE_DBTIMESTAMP to datetime

Hi

I am trying to transfer data from Access 2000 database to SQL server 2005 via SSIS. My access database table has a field with data type Date/Time and SQL server table has a field with data type datetime. When I am running the SSIS package, it is throwing the following error and stops execution.

[SQL Server Destination [12466]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E07 Description: "Error converting data type DBTYPE_DBTIMESTAMP to datetime.".

Please help.

Thanks in advance,
Ramzee

According to BOL (http://msdn2.microsoft.com/en-us/library/ms175970.aspx) the types can be converted, so that should work. Have you identified the values that fail? Using the error output is a very easy way to capture this data. My best guess for now is the values are out of range for the SQL datetime type. A derived column transformation could be used to massage these values depending on requirements.|||

Thanks Darren!

I used the script component to identify the wrong date entries. Now it works!

Thanks once again.

|||

I am having the same error. A simple select from Query Analyzer

Select * from HCPROD..HC.UNIT U

Even if I specifically reference a number column I get the same error

Select BLOCK_ID from HCPROD..HC.UNIT U

The way I see it is that there is a column, probably the DATE_MODIFIED column, somewhere in the database that has a date & time value that the driver cannot process. Why the error occurs even if I do not reference the column is a mystery.

I am using Microsoft OLE DB Provider for Oracle

Note that I have insured that every date field in the table is in 20th or 21st century

|||Some more information. We are using Oracle version 10g. I wonder if there is an updated driver from Microsoft? We searched and could not find one ourselves.|||SELECT TOP 10 * FROM [ORACLE_LOOKUP]..[SchemaName].[TableName] AlaisName

Error converting data type DBTYPE_DBTIMESTAMP to datetime

SELECT * FROM
OPENQUERY (
ORACLE_LOOKUP,
'SELECT TO_CHAR(DateColumnName) FROM TableName')

The following workaround was foud on the internet

A workaround would be to use the OPENQUERY function in SQL Server. This
function allows you to execute a pass-through query on the linked server.
By issuing a pass-through query, you can then take advantage of Oracle's
built-in functions to convert the date to a character data type or NULL.
Examples of both are as follows:

In the following example, the column "hiredate" is converted to a string in
the format of MM-DD-YYYY.

SELECT *
FROM OPENQUERY(, 'SELECT empno,
TO_CHAR(hiredate,''MM-DD-YYYY'') FROM scott.emp')

In this example we use a combination of two Oracle built-in functions,
DECODE and GREATEST to convert any hiredate that is earlier than 01/01/1753
(SQL Server's lower bound) to NULL.

SELECT *
FROM OPENQUERY(,'SELECT DECODE (hiredate,
GREATEST(hiredate, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), hiredate, NULL)
FROM scott.emp')

In case you are not familiar with Oracle built-ins, here is a description of
the two used in the query above.

DECODE is similar to a case statement in SQL Server. Its syntax is as
follows :

DECODE(, , , , ,...,
)

The is compared to each value. If there is a match,
the corresponding is returned. Otherwise, the value is
returned.

GREATEST returns the greatest value in the list of expressions.

GREATEST(, , ...)
Reply With Quote

Error at Transfer Objects Task: The source server can not be the same as the destination server.

SQL Server 2005 - June CTP

Error at Transfer Objects Task [Transfer Objects Task]: The source server can not be the same as the destination server.

Why not? I'm trying to transfer a set of tables from one database to another. Both databases happen to be on the same server. I understand that the source *database* cannot be the same as the destination *database*. But why can't I use the Transfer Objects task to transfer objects from one database to another on the same server?

Thanks for your help!

I can easily reproduce this. I would agree with Joseph, moving between databases on the same server is a legitamate requirement.

-Jamie|||I bumped into this problem several weeks ago and was only told "there are known problems with the transfer objects task"

My work around is to use an execute sql task that uses a fully qualified Insert statement to insert records into database 2 from database 1

Insert database2.dbo.tableA select * from database1.dbo.tableA

While it is a royal pain to have to have a line per table (250+ tables..changing on a regular basis) it does work and I get my records from the staging DB to the production

Paul Pisarek|||Paul,

Sorry about the curt response. Please reactivate the bug and follow through until you're satisfied with either the explaination or the resolution.

thanks,
ash|||

Dear Sharma,

Could you please update everyone whether the bug is resolved or yet to be resolved as we are facing a lot of issues in this task even after installing Service Pack Sp1.

Thanks,

Pradeep

+91 99451 20960

Pradeep.au@.gmail.com

|||

As far as I can see, with the production release of VS 2005, the original bug which I reported 'the source server cannot be the same as the destination server' has been corrected. I just used the Transfer SQL Server Objects task to transfer all objects (i.e. Tables, Views, Data, Indexes, etc.) from one database to another on the same server.

What 'issues' specifically are you encountering?

- Joe Geretz -

Error at Transfer Objects Task: The source server can not be the same as the destination ser

SQL Server 2005 - June CTP

Error at Transfer Objects Task [Transfer Objects Task]: The source server can not be the same as the destination server.

Why not? I'm trying to transfer a set of tables from one database to another. Both databases happen to be on the same server. I understand that the source *database* cannot be the same as the destination *database*. But why can't I use the Transfer Objects task to transfer objects from one database to another on the same server?

Thanks for your help!

I can easily reproduce this. I would agree with Joseph, moving between databases on the same server is a legitamate requirement.

-Jamie|||I bumped into this problem several weeks ago and was only told "there are known problems with the transfer objects task"

My work around is to use an execute sql task that uses a fully qualified Insert statement to insert records into database 2 from database 1

Insert database2.dbo.tableA select * from database1.dbo.tableA

While it is a royal pain to have to have a line per table (250+ tables..changing on a regular basis) it does work and I get my records from the staging DB to the production

Paul Pisarek|||Paul,

Sorry about the curt response. Please reactivate the bug and follow through until you're satisfied with either the explaination or the resolution.

thanks,
ash|||

Dear Sharma,

Could you please update everyone whether the bug is resolved or yet to be resolved as we are facing a lot of issues in this task even after installing Service Pack Sp1.

Thanks,

Pradeep

+91 99451 20960

Pradeep.au@.gmail.com

|||

As far as I can see, with the production release of VS 2005, the original bug which I reported 'the source server cannot be the same as the destination server' has been corrected. I just used the Transfer SQL Server Objects task to transfer all objects (i.e. Tables, Views, Data, Indexes, etc.) from one database to another on the same server.

What 'issues' specifically are you encountering?

- Joe Geretz -