Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select Statements in Stored Procedure gets Error converting data type DBTYPE_DBTIMESTAMP to datetime. Expand / Collapse
Author
Message
Posted Friday, November 09, 2007 6:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 10, 2014 3:38 PM
Points: 7, Visits: 82
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
Post #420742
Posted Friday, November 09, 2007 9:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:12 PM
Points: 7,084, Visits: 14,685
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?
Post #420754
Posted Friday, November 09, 2007 9:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:02 PM
Points: 35,978, Visits: 30,268
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #420755
Posted Friday, November 09, 2007 11:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 10, 2014 3:38 PM
Points: 7, Visits: 82
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 PM 0:00:01.203 SQL 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
Post #420765
Posted Saturday, November 10, 2007 12:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 10, 2014 3:38 PM
Points: 7, Visits: 82
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
Post #420790
Posted Sunday, November 11, 2007 5:44 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:02 PM
Points: 35,978, Visits: 30,268
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #420826
Posted Monday, November 12, 2007 9:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:12 PM
Points: 7,084, Visits: 14,685
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?
Post #421123
Posted Monday, November 12, 2007 10:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 10, 2014 3:38 PM
Points: 7, Visits: 82
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
Post #421128
Posted Monday, November 12, 2007 4:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:02 PM
Points: 35,978, Visits: 30,268
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #421271
Posted Wednesday, August 15, 2012 12:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 15, 2012 11:56 AM
Points: 1, Visits: 2
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 [color=#FF00FF]OPENQUERY[/color](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".
Post #1345435
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse