Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Oracle and SQL Server Data Interoperability Issues - Part 1 Expand / Collapse
Author
Message
Posted Wednesday, June 14, 2006 9:24 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444

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.




Post #287615
Posted Thursday, June 15, 2006 12:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 22, 2014 11:19 AM
Points: 16, Visits: 243

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




Post #287632
Posted Thursday, June 15, 2006 11:33 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444

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.




Post #287977
Posted Friday, June 16, 2006 1:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 22, 2014 11:19 AM
Points: 16, Visits: 243

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!




Post #288008
Posted Monday, March 17, 2008 6:50 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 2:23 PM
Points: 254, Visits: 131
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
Post #470590
Posted Friday, June 6, 2008 4:08 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,375, Visits: 1,391
Good article. The problems were well discussed.


Post #512775
Posted Friday, June 6, 2008 5:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 1:05 AM
Points: 2,842, Visits: 3,875
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
Post #512797
Posted Friday, June 6, 2008 6:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 12, 2014 2:58 PM
Points: 92, 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.
Post #512836
Posted Friday, June 6, 2008 7:52 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, Visits: 711
Excellent article. I've found it's a crapshoot getting things to work w/ oracle reliably from SQL server.....
Post #512903
Posted Friday, June 6, 2008 9:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 4:34 PM
Points: 266, Visits: 2,597
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
Post #513008
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse