Select Statements in Stored Procedure gets Error converting data type DBTYPE_DBTIMESTAMP to datetime.

Question

Select Statements in Stored Procedure gets Error converting data type DBTYPE_DBTIMESTAMP to datetime.

Juan Sanchez-352749

SSC Veteran

Points: 273
More actions
November 9, 2007 at 6:23 pm

#69200

I have a select statement that executes successfully by itself, so I used it in an stored procedure that our developers can call and get the data needed. However, when the try to execute the procedure we got the message "Error converting data type DBTYPE_DBTIMESTAMP to datetime." The data source does not have any dates at all. The select statement includes tables in SQL 2005 and a table and a view that are in a Oracle linked database.
The stored procedure does not have any input parameters, and it only contains the select statement, which runs fine by itself outside the stored procedure.
I am puzzled, and don't know what else to do. Any input from will be greatly appreciated. We are migrating from Oracle to SQL and the road seems to be full of roadblocks.
Thank you in advance for any input.
Juan Sanchez

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

We could help a whole lot more effectively if you were to post the body of the stored procedure (the one giving you the error). That error is one I've encountered dealing with OLE DB sources, and usually because I screwed some type of sytntax up. Seeing that from a Stored proc means you're doing sometihng a bit unusual, so it would probably help to see what you're up to...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

I think you're going to find that the problem is a bad date in the Oracle view. Check for any dates there that are outside the range of '17530101' and '99991231'... betcha find one... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Juan Sanchez-352749 SSC Veteran Points: 273 More actions · Answer 3

Matt, Thank you for your feedback. Below is the body of the stored procedure, which is very simple.

CREATE PROCEDURE [Abstracts].[Reviewer_GetAbstractsTst]

AS

BEGIN

Select

abrvr.ReviewerID, abrvr.ReviewStatus as Status, abrvr.AbstractID,

abrvr.Reviewer,abrvr.ResultsID, abrvr.ReviewScore as Scored,

abrvr.ResultStatus as ResultStatusType , abrvr.Inactive,

abst.TestID

From

Abstracts.Abstracts_Reviewers_tstV as abrvr

Inner join Abstracts.AbstractsTemplate as abst on abst.AbstractType = abrvr.TYPECODE

WHERE

abrvr.Inactive = 0

END

execute [Abstracts].[Reviewer_GetAbstractsTst]

11/9/2007 9:49:30 PM0:00:01.203SQL Server Database Error: Error converting data type DBTYPE_DBTIMESTAMP to datetime.execute [Abstracts].[Reviewer_GetAbstractsTst]

But, when I execute the select statement by itself, it works fine! See below

Select

abrvr.ReviewerID, abrvr.ReviewStatus as Status, abrvr.AbstractID,

abrvr.Reviewer,abrvr.ResultsID, abrvr.ReviewScore as Scored,

abrvr.ResultStatus as ResultStatusType , abrvr.Inactive,

abst.TestID

From

Abstracts.Abstracts_Reviewers_tstV as abrvr

Inner join Abstracts.AbstractsTemplate as abst on abst.AbstractType = abrvr.TYPECODE

WHERE

abrvr.Inactive = 0

(12 row(s) affected)

Abstracts.Abstracts_Reviewers_tstV is a View

Juan Sanchez-352749 SSC Veteran Points: 273 More actions · Answer 4

Jeff,

Thanks for the input. I checked all my date fields and none of them are outside the range you listed. There are some date fields that are null, would that be the problem?

I will keek researching until a find a solution.

Thanks

Juan

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

I don't think so, but links are sometimes a bit picky. If you don't find any other problem, I think I'd set up a small test to make sure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Nothing jumps out - but have you tried casting the date field to a varchar or a char? I've had to do that on occasion to get the conversion not to screw up between Oracle and SQL Server. You can always con vert it back on the SQL server side.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Juan Sanchez-352749 SSC Veteran Points: 273 More actions · Answer 7

Got it working! It had something to do with the date values, just as Jeff mentioned. I updated all date values, analyzed the table in question, and then the stored procedure start working again. Next, I tried to replycate the error to confirm the solution and entered dates 1000, and the stored procedure still works! Anyway, I will leave the proofing for my spare time, I can have now the developers continue with their work.

Thank you guys for your guidence,

Juan Sanchez

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Thanks for the feedback on your solution, Juan...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

CynthiaCols SSC Rookie Points: 29 More actions · Answer 9

ERROR: Error converting data type DBTYPE_DBTIMESTAMP to datetime

If you get this error in SQL, while doing an OPENQUERY SELECT on PROGRESS tables (or SQL92 data). The problem is there is an offending record with an unallowed date. Just convert each datetime field in the OPENQUERY part of your select, adding date fields one at a time and testing, until you find the offending date field that has an unallowed date.

HOW:

SELECT * INTO sqlTBLname FROM OPENQUERY(LINKEDSRVRNAME, 'SELECT { fn CONVERT("start-date", SQL_CHAR)} as STARTDATE FROM PUB."tblname" ')

After you get it into a SQL table, then just sort on the year, right(datefield,4), and find the offending date. It probably is some date before 1900 (01/30/1630).

CynthiaCols: "we can all make the world a better place, by simply sharing our view of the details, and by not assuming common knowledge".