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


Oracle and SQL Server Data Interoperability Issues - Part 1


Oracle and SQL Server Data Interoperability Issues - Part 1

Author
Message
Ian Yates
Ian Yates
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8036 Visits: 445

kkam, you are correct. Hmmm - I thought I had done so before but obviously not... Perhaps I was using a UDF of some sort as the source.

In any case, you could still use a #temp table rather than a ##temp table. The scope is limited to the DB connection rather than the current SQL block but it's still not global.





kkam
kkam
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 351

That would probably work. However, I am not sure what happens if the SP were called again before the first call could finish executing. This SP is called from a web page and my concern is that before the contents of the table could be cleared after we are done processing the request, the SP could be called again.

Is that a valid concern or am I off in my understanding of SP execution within SQL server?

Thanks again





Ian Yates
Ian Yates
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8036 Visits: 445

The temporary tables prefixed with a single # are scoped within a single database connection. Thus if you have 10 hits on your website then you might have 10 concurrent database connections (being simple here - no pooling or other voodoo ). Each connection can have its own temp table. Thus the use of temp tables within the stored proc should not cause any problem.

A global temporary table (prefix ##) IS shared - much like a standard fixed table in the database.





kkam
kkam
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 351

Thanks! That narrows down the wires that were crossed for me. I won't have a chance to test it for at least a few days but I'll keep you posted.

Thanks again!





debrucer1
debrucer1
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2394 Visits: 136
Nice article and with SQL Server and Oracle, it comes back into focus every couple of years. I am experiencing a problem today that is supposed to be with a date/timestamp; but, there isn't one in my query. This isn't an oddball case, it is happening regularly with a linked server from SS 2005 to Oracle10g.

The query is as follows:

SELECT [QUEUE]
FROM [EMREP]..[SYSMAN].[AQ$MGMT_LOADER_QTABLE]

The error message is as follows:

Msg 7354, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "EMREP" supplied invalid metadata for column "DELAY_TIMESTAMP". The data type is not supported.

Anybody have a clue?

(I will post in forum tomorrow)

Thanks!

Thanks,

David Russell
Oracle Since 1982
SQL Server Since 1998
Anipaul
Anipaul
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12623 Visits: 1407
Good article. The problems were well discussed.



Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7189 Visits: 3889
Good Article and Good Topic.
I also stumbeled accross the oracle timestamp issue and used TO_CHAR as well to get around it.

Btw, here is some information regarding the deprecated timestamp datatype and its replacement "rowversion" in SQL Server 2008.
http://msdn.microsoft.com/en-us/library/ms182776(SQL.100).aspx

Best Regards,

Chris Büttner
Craig A. Silvis
Craig A. Silvis
SSC-Addicted
SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)

Group: General Forum Members
Points: 426 Visits: 170
Your article referred to timestamps but I've had the same problem with date fields in Oracle and had to do the same thing you suggested and convert them to character data to make it work.
SuperDBA-207096
SuperDBA-207096
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3983 Visits: 711
Excellent article. I've found it's a crapshoot getting things to work w/ oracle reliably from SQL server.....
JJ B
JJ B
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1803 Visits: 2862
What a timely article for me. I recently found out that I might have to do some queries/reports off of another agency's Oracle database. I've only worked with MS database products and Sybase. I don't know anything about Oracle. Any advice or "gotchas" people have on doing queries off of Oracle would be appreciated.

Thanks,
- JJ
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