July 6, 2004 at 12:35 pm
| Hi All,
Does anyone have a work-around or solution to the DBTYPE_DBTIMESTAMP error "Error converting data type DBTYPE_DBTIMESTAMP to datetime" you get when trying to retrieve an Oracle datetime field from SQL server? Am just trying to write a storedProcedure to grab it. Many thanks for any suggestions. Steve. |
July 7, 2004 at 3:44 am
Hi
I had similar problems and had to use the OPENQUERY syntax to get ORACLE to make the datetime suitable for SQL Server, you can then use this result as a derived table. If you need to join between SQL Server and ORACLE then this approach may not work for you.
I had two issues, the first is that SQL Server cannot handle dates prior to 01/01/1753, the second is I had intermitent errors if the datetime contains time info.
SELECT *
FROM OPENQUERY(ORACLESERVER, '
SELECT PersonCode,
GREATEST(TRUNC(DateOfBirth), TO_DATE(''01 JAN 1753'', ''DD MON YYYY''))
FROM Demo.Person
WHERE PersonCode = 12345
')
If you use the ORACLE TRUNC() function this removes any time from the datetime, I don't need the time for my application so I have not tried to work out what causes the problem with times, this manifests as the query terminating when it finds the first row with a 'strange' time in it (maybe something to do with milliseconds), but you don't get an error.
If you use GREATEST() and TO_DATE() this converts any date prior to 01/01/1753 to be 01/01/1753, you'll mask the actual date with 01/01/1753 but you would not have a date of birth in the range < 01/01/1753 (usually). Not sure how you would get around this if your application handles historical dates, i.e. 1066.
Hope this helps
July 7, 2004 at 7:12 am
Hi Neil,
Many, many thanks for your reply. Had seen another post on openquery and have been successfull in returning all the data from oracle using SQL QUERY ANALYZER. The goal is to write a sql server stored procedure to do this. Here it is so far....4
CREATE PROCEDURE dbo.spRS_ShopPAP
(
@BEGININVOICED_DATE as datetime,
@ENDINVOICED_DATE as datetime
)
AS
SELECT *
FROM OPENQUERY (VMFG, 'SELECT
s.CUST_ORDER_ID,
o.CUSTOMER_PO_REF,
l.PART_ID,
l.COMMODITY_CODE,
l.PRODUCT_CODE,
sl.UNIT_PRICE AS AMOUNT,
s.INVOICE_ID,
l.CUSTOMER_PART_ID AS SN,
l.WORKORDER_BASE_ID,
sl.PACKLIST_ID,
sl.LINE_NO,
rl.AMOUNT AS CUSTINQAMOUNT
FROM SHIPPER s
JOIN CUSTOMER_ORDER o ON s.CUST_ORDER_ID = o.ID
JOIN SHIPPER_LINE sl ON s.PACKLIST_ID = sl.PACKLIST_ID
JOIN CUST_ORDER_LINE l ON sl.CUST_ORDER_LINE_NO = l.LINE_NO AND sl.CUST_ORDER_ID = l.CUST_ORDER_ID
JOIN RECEIVABLE_LINE rl ON s.PACKLIST_ID = rl.PACKLIST_ID AND sl.LINE_NO = rl.PACKLIST_LINE_NO
WHERE l.PRODUCT_CODE="35" AND s.INVOICED_DATE>=@BEGININVOICED_DATE And s.INVOICED_DATE<=@ENDINVOICED_DATE
Order by s.CUST_ORDER_ID')
GO
When checking syntax an error 7321 is generated, ora-00933 sql command not properly ended. Do you see anything obvious here ? Do not know if openquery can be used from a stored procedure but want to give it a try.
Thanks, Steve.
July 7, 2004 at 9:24 am
Hi Steve,
I'm not sure that you can use @BEGININVOICED_DATE and @ENDINVOINCED_DATE in the SQL that is passed to oracle, the variables are declared within SQL Server so I'm not sure if they are in 'scope'. The exact SQL string is forwarded to Oracle 'untouched', so SQL Server would not substitute the values for your variables (but I'm not sure on this).
You could try replacing the variables with literals and see if the query works? If it does then I'm not sure how you would workaround getting the values of @BEGININVOICED_DATE into the SQL string. Perhaps you could write an equivalent stored procedure in Oracle and call the Oracle stored procedure from within your SQL Server stored procedure?
I get around this issue as I am using a VB front end so I can send a dynamic SQL to SQL Server, which is then forwarded to Oracle. I substitute any variables into my dynamic SQL before I send it to SQL Server.
The only other way around this I've found is to build dynamic SQL into string variables within the stored procedure, and then use EXEC to run the dynamic SQL. You have to create a temp table and run something like the following using EXEC (the following is stored in a variable and passed to EXEC).
INSERT INTO #temp (field1, field2)
SELECT field1, field2
FROM OPENQUERY(ORACLE, 'SELECT field1, field2 FROM ... WHERE ...')
Note that the above is a workaround, and there may be a better way to solve this using the oracle driver or a setting on the oracle server - but I'm not aware of one (I've not use Oracle much).
What you basically end up with after running the EXEC is the records from Oracle stored in #temp, which you can then either return to the client or process in whatever way you like. Its not efficient, but works if you are only returning a few rows.
Neil
July 7, 2004 at 9:32 am
Ahaaa ! Thanks Neil, this is becoming clearer now. No problem, now have a nice oracle stored procedure that is expecting two data parameters. Only need to figure out how to call it from a sql server stored procedure (have not done this before). Steve.
July 7, 2004 at 10:39 am
Hi all,
We ran into the same problem and discovered that timestamp is not supported in SQL Server. We thought about creating a SP in Oracle but could not find out a way to call it from SQL Server. If you can find a way, please send to this thread.
The solution we implemented was to change the data type of the field in Oracle to type DATE. It still records the date and time upto seconds and is supported in SQL Server. See if you have this option available.
Hope that helps.
Sanjeev
July 9, 2004 at 9:08 am
Ok,
Have an update. Created an Oracle view which now contains no nulls in the date field we are interested in selecting. Query analyzer returns and converts the data from the view perfectly. Created an MS stored procedure and this also returns the data perfectly when run from query analyzer.
SELECT *
FROM VMFG..SYSADM.PAP
EXEC spRS_ShopPAP '2004-03-16', '2004-04-14'
The issue we are having is when this stored procedure is used in reporting services we get "An error occured whle reading data from the query result set. OLE DB provider MSDASQL reported an error"
The Oracle date field looks like "29-JUN-04" when queried on oracle. Query Analyzer returns the data as "2004-06-29 00:00:00.000"
Is there a conversion tool or trick I am missing here ?
Many thanks for any suggestions, Steve.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply