Reading microseconds from Sql server into oracle 10g

Question

Reading microseconds from Sql server into oracle 10g

Viewing 3 posts - 16 through 17 (of 17 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

that was my point.

I thought you said you said sourceid exists on SQL Server., and assumed you were doing the work from SQL server, with linked servers to Oracle.

What confused me was test@dblink_test I think you are saying that is a dblink TO SQL Server?

so your commands are all originating on the Oracle side, not SQL Server, correct?

What Table does source_id exist in?

i would be lazy, and on SQL Server,create a view that formats the the source_id to CONVERT(VARCHAR(35),source_id,121)

this is actually an Oracle question, and dblink behavior, and not really a SQL question, which is causing all the confusion...you ar egetting SQL-oriented answers from us.

see this post, where they specifically say you need to cast it to a char to get timestamps:

https://forums.oracle.com/forums/thread.jspa?threadID=2359176

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

Lowell (4/11/2012)this is actually an Oracle question, and dblink behavior

Oracle DBLink connections behave like a normal client connection meaning that the server on the remote side of the DBLink perceives the connection as a client - nothing more, nothing else.

My guess is that poster might be mixing sql syntax sometimes hitting the remote SQL Server either with PL/SQL or Oracle SQL syntax which wouldn't fly.

I think that the solution pointed by Lowell, creating a view on SQL Server side casting the column as varchar() then do whatever you want with it on Oracle side.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

caramelkaps SSC-Addicted Points: 414 More actions · Answer 3

Thanks everbody for all the insight.

I have posted my issue in oracle forum as well and am yet to receive any reply.

Unfortunately we have no control over the third party sql server DB.

So might not be able to request them to either change all the columns to varchar or create additional views.

Will update here on the progress.

Thanks.