473,325 Members | 2,671 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Linked Server Time Difference

I am using SQL 2000 and/or SQL 2005 to link to an Oracle Db using
OPENQUERY.
The SQL 2000 box is SP4 on Win2000.

I can run a query thru Q.A. for SQL 2000 or I can run an Oracle query
thru SQL-Plus to see the data. But, if I run a date-query using the
linked server then SQL Server returns a TIME that is off by 4 hours.

SQL Query:
SELECT * FROM OPENQUERY( ORASERVER, 'SELECT MYDATE FROM MYTABLE')

Oracle Query:
'SELECT MYDATE FROM MYTABLE'

Oracle results:
04/16/2006 01:07:00

SQL 2000 result:
04/16/2006 05:07:00

Has anyone seen this before? Is there a fix?
I have searched the newsgroups and MS Kb but can not find an answer.

Thanks,
Marty

May 31 '06 #1
1 1652

MartyP wrote:
I am using SQL 2000 and/or SQL 2005 to link to an Oracle Db using
OPENQUERY.
The SQL 2000 box is SP4 on Win2000.

I can run a query thru Q.A. for SQL 2000 or I can run an Oracle query
thru SQL-Plus to see the data. But, if I run a date-query using the
linked server then SQL Server returns a TIME that is off by 4 hours.

SQL Query:
SELECT * FROM OPENQUERY( ORASERVER, 'SELECT MYDATE FROM MYTABLE')

Oracle Query:
'SELECT MYDATE FROM MYTABLE'

Oracle results:
04/16/2006 01:07:00

SQL 2000 result:
04/16/2006 05:07:00

Has anyone seen this before? Is there a fix?
I have searched the newsgroups and MS Kb but can not find an answer.

Thanks,
Marty


I have never seen this problem, but could you verify that the Oracle
mydate column datatype is a defined as DATE or as a TIMESTAMP? If it
is a timestamp is it a timestamp with timezone by any chance?

HTH -- Mark D Powell --

May 31 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
3
by: David | last post by:
We recently translated the backend db from Access(97) to SQL Server. We are still using Access frontends. I have an update query in the Access front end that uses a lookup table to populate...
12
by: Neil | last post by:
I previously posted re. this, but thought I'd try again with a summary of facts. I have an Access 2000 MDB with a SQL Server 7 back end. There is a view that is linked to the database via ODBC...
8
by: ZRexRider | last post by:
Hi, I am working on an Access application where the author used SQL back end via linked tables. I wrote some general functions that would execute pass through queries and was going to call...
15
by: brettclare | last post by:
I have linked a large SQL Server table to Access, however 'only' 2,195,439 records are shown and are available to query. Can I increase the size (cache??)/number of records showing in Access? ...
3
by: Zlatko Matiæ | last post by:
Hi! What happens with linked tables if they were linked using File DSN, when I copy the Access file on some other PC without File DSN ? What is the difference between DSN on linked tables and...
5
by: Neil | last post by:
I am getting time-out errors when I try to perform a simple delete on a linked server. The command is: Delete From MyTable Where PKID=12345 I have tried executing this command directly from...
0
by: aberton | last post by:
Hi all, I am having major performance issues between my linked server and an Oracle10 db. I have created a number of views in my SQL Server database which map to corresponding Oracle tables via a...
0
by: aberton | last post by:
Hi all, I am having major performance issues between my linked server (SQL Server) and an Oracle10 db. I have created a number of views in my SQL Server database which map to corresponding Oracle...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.