SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Juan Sanchez-352749
Juan Sanchez-352749
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 99
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
Matt Miller (4)
Matt Miller (4)
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15711 Visits: 18766
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
Jeff Moden
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114340 Visits: 41371
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... Wink

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Juan Sanchez-352749
Juan Sanchez-352749
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 99
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
Juan Sanchez-352749
Juan Sanchez-352749
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 99
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
Jeff Moden
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114340 Visits: 41371
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Matt Miller (4)
Matt Miller (4)
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15711 Visits: 18766
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
Juan Sanchez-352749
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 99
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
Jeff Moden
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114340 Visits: 41371
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
CynthiaCols
CynthiaCols
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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".

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search