Oracle Distributed Transactions
Nov 5, 2001
I am attempting to update an oracle table from a insert trigger on my SQL table. When using a trigger to run a sql command against the ole db oracle client (linked server) a distributed transaction is initiated and i get a message stating. "The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction."
I can update the oracle table through a query however just not throught a distributed transaction.
However according to all the documentation i can find it states that I should be able to perform this.
Can anyone shed any insight?
View 4 Replies
ADVERTISEMENT
May 6, 2004
Hello,
does the .NET framework support distributed transactions somehow?
The SqlConnection/SqlTransaction classes doesn't seem to support them...
TIA. -julio
View 2 Replies
View Related
May 24, 2001
Hi to everyone!
Any experience handling a Store Procedure that has distribution transactions?
Here is a story.
We have a store procedure #1 that inserts or updates data in two tables A and B on SQL Server 7.0 then it calls another store procedure #2 that:
1. updates C table in the same database on SQL Server from Oracle Server
2. deletes that record on Oracle Server
3. inserts a record on Oracle from table A
4. inserts the same record into table D on SQL Server from Oracle Server.
People who wrote store procedure #1 put Commit Tran before executing #2.
The questions are:
1. Is it correct that we don't have Commit Tran for #2?
2. What do you think about Nested Begin - Commit Tran?
Any help is highly appreciated.
Sima
View 2 Replies
View Related
Aug 3, 1998
Does SQL Server actually support distributed transactions over, say, two tables in different databases but that reside on the same server? When I try to execute such a transaction in which one part of the transaction violates referential integrity and hence should not be executed, causing the rest of the transaction to, supposedly, rollback, the transaction does not roll back but instead produces an error message and executes the second, valid half of the transaction anyway.
Any help or suggestions gratefully accepted.
View 1 Replies
View Related
Mar 27, 2006
Hi There
I realize this is probably a basic question or at least i hope so but i cannot get distributed transactions working.
BOL is just driving me in circles.
I am running SS2000, MSDTC is up and running on the server.
I have added a trigger to a table where on insert or update the triggers fires and inserts a row to a remote server.
When i execute the update or insert i get the error:
Cannot start a distributed transaction.
I have tried stating BEGIN DISTRIBUTED TRANSACTION before the update but i get the same error, i have also read up on SET REMOTE_PROC_TRANSACTIONS, but this is not a remote sp.
Can anyone please reccomend a good link as to how to get distributed transactions to work.
Thanx
View 3 Replies
View Related
Dec 12, 2001
I am using SQL server 7.0 and i have created oracle8i linked server(Using MSDAORA as provider) in it.
When i run distributed queries between SQl server and Oracle server it works fine.But when i try to run distributed transaction between two servers ( BEGIN DISTRIBUTED TRANSACTION..)it reports an error saying Distributed transactions are not supported by MSDAORA.
My question is; is it possible to run distributed transaction between SQL server and oracle server (where oracle server is a linked server in my SQL server)?
Actually i want to run this transaction in DTS package which updates and transfers data amongst various servers.
Thanx
Regards,
Rahul
View 1 Replies
View Related
Nov 10, 2004
We just upgraded from SQL 7 to 2000 and for some reason I am unable to perform distributed transactions. I keep getting the following error:
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransaction Join:: JoinTransaction returned 0x8004d00a]. Operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction
I tried a few of the fixes recommended on the microsoft website but they did not solve the problem. We are still using the same O/S (Windows 2000 Pro), so the only thing that has changed is the server.
Any help would be appreciated.
View 6 Replies
View Related
Jan 22, 2008
Hello,
I've been redirected here from the Transaction Programming forum becuase I have e peculiar issue with SQL 2005 running INSERT stored procs from multiple WCF services all withing a TransactionScope.
The original post is http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2720665&SiteID=1&mode=1
The story goes, I have SRVC A with starts a TransactionScope which in turn calls SRVC B & C in sequence based on processing rules.
SRVC A is a Sequential Workflow which Starts and Completes the TransactionScope
SRVC B Creates a new Customer into the database
SRVC C Creates new Accounts for that Customer and Initialises the accounts with funds
The DB Tables underneath are Customer, Account and AccountLog
DDL
Code Block
CREATE TABLE [Member].[Customers](
[CustomerId] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](32) NOT NULL,
[CreatedUtc] [datetime] NOT NULL ,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[CustomerId] ASC
)
CREATE TABLE [Bank].[Accounts](
[AccountId] [int] IDENTITY(1,1) NOT NULL,
[CustomerId] [int] NOT NULL,
[CurrentBalance] [money] NOT NULL,
[LastUpdateDate] [datetime] NULL,
[CreatedDate] [datetime] NOT NULL,
[timestamp] [timestamp] NOT NULL,
CONSTRAINT [PK_Bank_Account] PRIMARY KEY CLUSTERED
(
[AccountId] ASC
)
) ON [PRIMARY]
GO
ALTER TABLE [Bank].[Accounts] WITH CHECK ADD CONSTRAINT [FK_Account_Customer] FOREIGN KEY([CustomerId])
REFERENCES [Member].[Customers] ([CustomerId])
CREATE TABLE [Bank].[AccountLog](
[AccountLogId] [int] IDENTITY(1,1) NOT NULL,
[AccountId] [int] NOT NULL,
[Amount] [money] NOT NULL,
[UtcDate] [datetime] NOT NULL,
CONSTRAINT [PK_Bank_AccountLog] PRIMARY KEY CLUSTERED
(
[AccountLogId] ASC
)
) ON [PRIMARY]
GO
ALTER TABLE [Bank].[AccountLog] WITH CHECK ADD CONSTRAINT [FK_AccountLog_Account] FOREIGN KEY([AccountId])
REFERENCES [Bank].[Accounts] ([AccountId])
NB. I've removed most fields not essential for this example.
So from SRVC A I invoke SRVC B and the Customer is created, however when I get to SRVC C and the accounts are to be created I get a lock. Only when the Transaction aborts due to timeout, do I see in SQL Profiler that the call to the SP that created the Account is executed but eventually rolls back as it is part of the distributed transaction.
Now, If I set the Isolation level in the TransactionScope to ReadUncommitted (urgh) the problem remains. When I set the IsolationLevel to Read Uncommitted in the SP that creates the account the problem remains but when I remove the FK constraint the problem disappers. The other curious thing is that with the Customer -> Account FK removed and when SRVC C calls to insert funds into the AccountLog which also updates an aggregated total in the Account from within the same transaction scope and with Account -> AccountLog FK constraints in place there is no locking even with Isolation Serializable.
I'm quite at a loss as to what could be causing these issues. If anyone has any suggestions I would greatly appreciate any help.
Thanks
Andy
View 1 Replies
View Related
Oct 18, 2006
Hi there,
I have two SQL servers, SERVERA and SERVERB. They are on two different VLAN's within our corporate network. They are physically only about 5 feet apart. SERVERA is SQL2005 while SERVERB is SQL2000. I am running a distruted transaction as a result of an insert on SERVERA which then causes a Stored procedure stored on SERVERA to insert a record on a table on SERVERB. I know my syntax is correct, 'cause I can get the Process to work between two servers (one SQL2000 and the other SQL2005) on the same VLAN. But when I run it in the problem environment, I get the following error message:
SQL Execution Error
Executed SQL Statement: INSERT INTO Tablea(recordid, recordtext) values(3, 'Testagain')
Error source: .Net SQLClient Data Provider
Error Message: The operation could not be performed because OLE DB provider "SQLNCL1" for linked server "SERVERB" was unable to being a distributed transaction.
I have checked to make sure that MSDTC is running and it is on both servers.
Any help would be greatly appreciated. Thanks! - Eric-
View 5 Replies
View Related
Oct 17, 2006
Hi there,
We have two servers, one (we'll call 'SERVERA') has SQL2005 running on it. The second (we'll call 'YELLOWSTEONE') is running both SQL2000 and SQL2005 on it. The SQL instances on YELLOWSTONE are 'YELLOWSTONESQL2000' and 'YELLOWSTONESQL2005'. As a linked server, I have an entry for YELLOWSTONE which then links to the SQL Server of YELLOWSTONESQL2000 on the server network name of YELLOWSTONE. By them selves they seem to run fine. However, if I have trigger that Runs on SERVERA to do a distributed transaction on 'YELLOWSTONESQL2000', I get the following error:
OLE DB provider "SQLNCLI" for linked server "YELLOWSTONE" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "YELLOWSTONE" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 2, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [2].
If You can provide me any assistance, I would greatly appreciate it. Thanks! - Eric -
View 1 Replies
View Related
Nov 14, 2007
Hi, I am working on vs2005 with sql server 2000. I have used TransactionScope class. Example Reference: http://www.c-sharpcorner.com/UploadFile/mosessaur/TransactionScope04142006103850AM/TransactionScope.aspx The code is given below. using System.Transactions; protected void Page_Load(object sender, EventArgs e) { System.Transactions.TransactionOptions transOption = new System.Transactions.TransactionOptions(); transOption.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted; transOption.Timeout = new TimeSpan(0, 2, 0); using (System.Transactions.TransactionScope tranScope = new System.Transactions.TransactionScope(TransactionScopeOption.Required,transOption)) { using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["nwConnString"].ConnectionString)) { int i; con.Open(); SqlCommand cmd = new SqlCommand("update products set unitsinstock=100 where productid=1", con); i = cmd.ExecuteNonQuery(); if (i > 0) { using (SqlConnection conInner = new SqlConnection(ConfigurationManager.ConnectionStrings["pubsConnString"].ConnectionString)) { conInner.Open(); SqlCommand cmdInner = new SqlCommand("update Salary set sal=5000 where eno=1", conInner); i = cmdInner.ExecuteNonQuery(); if (i > 0) { tranScope.Complete(); // this statement commits the executed query. } } } } // Dispose TransactionScope object, to commit or rollback transaction. } } It gives error like
"The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025)" The database I have used is northwind database and pubs database which is by default in sql server 2000. So, Kindly let me know how to proceed further. Thanks in advance,Arun.
View 1 Replies
View Related
Mar 5, 2015
I vaguely remember reading somewhere that all distributed transactions are executed at Serializable Isolation Level "under the covers."
1. Is this true?
2. What does "under the covers" mean in this case; i.e. will I not see the isolation level represented accurately in requests?
View 9 Replies
View Related
Jul 23, 2005
Hi,I'm having a problem running a distributed transaction between twolinked servers that both have multiple instances of SQL Serverinstalled on them. This is the error message that I receive:"The operation could not be performed because the OLE DB provider'SQLOLEDB' was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in thespecified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB'ITransactionJoin::JoinTransaction returned 0x8004d00a]."The query follows the format:"BEGIN DISTRIBUTED TRANUPDATE [LINKEDSERVER1INSTANCE_NAME].DB.OWNER.TABLENAMESET fieldname = alias2.fieldnameFROM tablename alias2JOIN [LINKEDSERVER1INSTANCE_NAME].DB.OWNER.TABLENAME alias1on alias2.urn=alias1,urn"[color=blue]>From what I can gather from various sources the SQL Server must be[/color]named the same as the computer which it is installed on. However, if Ihave two instances of SQL Server, they cannot both be named the same asthe computer. Does anyone know of a way around this or whether I'mbarking up the wrong tree completely?Many thanks.
View 2 Replies
View Related
Jul 20, 2005
Hi,I am seeking the help of volunteers to test some software that I'vedeveloped which facilitates distributed two-phase commit transactions,encompassing any resource manager (e.g. SQL/Server or Oracle) controlled byMicrosoft's Distributed Transaction Coordinator in a Windows2000environment, with any resource manager under the control of DECdtm (e.g. Rdb(or Oracle via the XA Veneer)) in a VMS environment.[Yes, at some stage, I hope to sell this software and make money out of it,so unless you have a large philanthropic streak or are simply a techie wholikes to stay on top of Windows<->VMS connectivity issues, then you may wishto look away now. But if you do choose to participate, then rest assuredthat I have no interest in your personal or company details. (Just yourwork-rate :-)]What differentiates my Transaction Manager software from existingTransaction Monitor packages that are already in the marketplace (and whyyou should be interested) is that it is based on the Transaction InternetProtocol TIP standard. (RFC 2372) For those of you who don't know, thebeauty of TIP's "Two-Pipe" strategy is it's application-pipe (or middleware)neutrality. Whereas most XA implementations mandate homogenous TransactionMonitor deployments (such as Tuxedo everywhere, Encina everywhere, MQSerieseverywhere, ACMSxp everywhere and so on . . .), hotTIP from TIER3 Softwaregives you complete freedom to choose the middleware product(s) that bestsuite your particular application and heterogeneous network needs.Would you like to talk to VMS with TIER3 Sockets, COM or DCE/RPC? BEAMessageQ, IBM MQSeries or HTML? The choice is yours and yours alone. Butonce you realize that you need to encase your critical transactions withinthe ACID properties of a true Heterogeneous Two-Phase Commit then you willcome to the conclusion that you need a Transaction Manager that looks a lotlike this.Another drawback of traditional "One-Pipe" strategies is that they precludethe run-time determination of transaction participants. (Functionalitywhich may be advantageous in a wide-area or Internet based application.)Anyway, this is what I have: -On the Windows side, you need absolutely *NO* additional software! I'llreply to this note with a brief description of the COM+ and DTC functionsthat you would need to invoke in order to successfully push a MTS/DTCtransaction to VMS. NB: These are standard Windows APIs that are fullydocumented on MSDN.On the VMS side, I have a VMSINSTAL saveset that (all zipped up) is some150KB that I'm happy to e-mail to you along similar lines to the VMShobbyists (non-commercial use) license. I'll reply to this note with anInternet Daemon (INETd) example of code that uses my software to cedetransactional control, over an SQL insert into a Rdb database, to MTS/DTC.It's under 500 lines long and contains all of the DCL, 3GL, SQL required toproduce a working example of a TIP-2PC capable TCP/IP auxiliary server. Thisexample will insert a row into the MF_PERSONNEL.Employees table on the VMSside in co-operation with Windows2000 MTS/DTC client that is inserting a rowinto the NORTHWIND.Employee table. Commit them all or roll them all back.So, in summary, If you'd like to volunteer to put hotTIP through it's pacesthen simply reply to this mail.Regards Richard MaherPS. The following are a few functionality restrictions with the currentversion of my software that may effect your decision to participate: -1) Transaction has to be started/mastered/coordinated by W2K MTS/DTC2) Transactions cannot be PULLed from VMS and must be PUSHed from W2K3) No cluster-wide recovery.(If a txn falls over after being prepared then you have to wait for thatspecific node to become contactable again even though that lovely RDMrecovery job is sitting on another node protecting the database until myhotTIP TM tells it to commit or abort.)4) There is currently no Alpha or Itanium version available. The Alpha portis currently in progress but, for the time being, you'll either need a VAXor a VAX emulator on your PC.
View 7 Replies
View Related
Feb 12, 2009
We get the below error while performing a distributed transaction on linked server. We have several linked servers configured in the source server and all of them succeed with the distributed transaction except on one.
Â
We did all the basic troubleshooting and moreover the distributed transactions work fine if we use a remote server instead.
Error:
OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" was unable to begin a distributed transaction.
Â
 Test code:
begin distributed transaction
select top 10 * from [SERVERNAME.REDMOND.CORP.MICROSOFT.COM].master.sys.objects
 ROLLBACK
Â
Source server : Â Â
Microsoft SQL Server 2008 (RTM) - 10.0.1779.0 (X64)
      Nov 12 2008 12:10:04
      Copyright (c) 1988-2008 Microsoft Corporation
      Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1) (VM)
Â
Target server : Â Â
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
      Jul 9 2008 14:43:34
      Copyright (c) 1988-2008 Microsoft Corporation
      Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
View 30 Replies
View Related
Jun 4, 2007
Is it possible to use DTC (or cross database queries) with mirroring on SQL 2005 Service Pack 2?
Thnx,
GoranP
View 2 Replies
View Related
May 14, 2015
It is my understanding that when having LinkedServers, the option "enable promotion of Distributed Transactions for RPC" should be set to TRUE, so we can rollback , if needed, remote transactions. At least, that's my understanding of that setting.
Having said that, the TRUE setting is affecting this particular TSQL code, inside an sproc, which I would prefer not to alter:
Insert into #TempTable
EXEC ServerB.MyDatabase.MyStoreProcedure
@param1= '',
@param2= ''
When set is set to TRUE (current setting) I get this error:
OLE DB provider "SQLNCLI11" for linked server "ServerB" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 28
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "ServerB" was unable to begin a distributed transaction.
... when set to off, the error goes away.
View 8 Replies
View Related
Aug 14, 2007
Hi,
I am using distributed transactions where in I start a TransactionScope in BLL and receive data from service broker queue in DAL, perform various actions in BLL and DAL and if everything is ok call TransactionScope.Commit().
I have a problem where in if i run multiple instances of the same app ( each app creates one thread ), the threads pop out the same message and I get a deadlock upon commit.
My dequeue SP is as follows:
CREATE PROC [dbo].[queue_dequeue]
@entryId int OUTPUT
AS
BEGIN
DECLARE @conversationHandle UNIQUEIDENTIFIER;
DECLARE @messageTypeName SYSNAME;
DECLARE @conversationGroupId UNIQUEIDENTIFIER;
GET CONVERSATION GROUP @conversationGroupId FROM ProcessingQueue;
if (@conversationGroupId is not null)
BEGIN
RECEIVE TOP(1) @entryId = CONVERT(INT, [message_body]), @conversationHandle = [conversation_handle], @messageTypeName = [message_type_name] FROM ProcessingQueue WHERE conversation_group_id=@conversationGroupId
END
if @messageTypeName in
(
'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog',
'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
)
begin
end conversation @conversationHandle;
end
END
Can anyone explain to me why the threads are able to pop the same message ? I thought service broker made sure this cannot happen?
View 11 Replies
View Related
Jan 17, 2007
Hi all,
I found an article which described about Distributed query to Oracle table with four-part name.
http://support.microsoft.com/kb/294459/en-us
However, we still got the same error with MSSQL2000 sp4 and SQL2005. Any fix to this?
View 1 Replies
View Related
May 12, 2006
Hello!I have an Oracle linked server connected through MSDAORA. Linked serverqueries work perfectly - the "openquery" ones as well as the4-part-named ones.The problem I have is with embedding the queries within SQL Servertriggers.Trigger:CREATE TRIGGER tgTest ON [dbo].[test]FOR INSERT, UPDATE, DELETEASselect * from openquery(LS, 'select * from ORACLE_TEST')executing "delete from test" in SQL Query Analyzer raises this error:Server: Msg 7391, Level 16, State 1, Procedure tgTest, Line 5The operation could not be performed because the OLE DB provider'MSDAORA' was unable to begin a distributed transaction.OLE DB error trace [OLE/DB Provider 'MSDAORA'ITransactionJoin::JoinTransaction returned 0x8004d01b].I've tried almost every solution I found online, but nothing helped:(This looked promissing: http://tinyurl.com/nk2wd , but it didn't get meany futher.Maybe someone can get me through the troubleshoot mentioned in thatlink:- check if DTC running properlyHow do I check that? If I open the "Support services" in EnterpriseManager and right-click the "Distributed Transaction Coordinatior" Ican stop the service, what indicates the service is running, but isthere anything else I should check? I have 0 items in the right windowpane of the DTC item, is it OK?- registry setting as discussed earlierThe following Registry Keys should be entered:[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTCMTxOCI ]"OracleXaLib"="oraclient8.dll""OracleSqlLib"="orasql8.dll""OracleOciLib"="oci.dll"My entries are:"OracleOciLib"="ociw32.dll""OracleSqlLib"="SQLLib18.dll""OracleXaLib"="xa73.dll"Are they OK?- check if Mtxoci.dll is loadedThere is a Mtxoci.dll in my system32 dir, but how do I tell if it'sloaded? Should I regsvr32 it?- SET XACT_ABORT ON should be use in your SQL statement, for example:SET XACT_ABORT ONBEGIN DISTRIBUTED TRANSELECT statementCOMMIT TRANI've tried that, both in trigger but also surrounding the query thatfires the trigger.Am getting deseperate - please help. Will send candies!TIA
View 1 Replies
View Related
Apr 14, 2007
I lost few days to solve a problem regarding the connection to an Oracle database server (10) with goal to execute a distributed transaction.
The environment was:
Windows Server 2003 Standard
MSSQL Server 2000 Standard
Oracle 10g Production Edition
The connection was quite easy to make with the Microsoft driver, but the error was:
Server: Msg 7391, Level 16, State 1, Line 3
The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].
I tried to use the oracle odbc driver, but all kinds of errors were raised, regarding the TNS name and stuff like this. The real cause was that when you create the linked server you must to check the €œAllow inbound process€? in the provider options.
The Oracle ODBC driver allow distributed transactions.
View 1 Replies
View Related
Feb 7, 2008
I have a SQL Server 2005 with a linked server to an Oracle RDB 6.0, using the Microsoft OLE DB for ODBC provider. I would like to select some rows from the Oracle RDB and then Update those specific rows afterwards. Whenever I try to do this in a transaction (BEGIN TRAN) I get an error "Driver not capable". Is there any way to do this in a single transaction?
View 1 Replies
View Related
May 22, 2005
Hi there,
I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just like myTransaction.Rollback() in asp.net. But what about exceptions? In asp.net, I am used to doing the following:
<code>Try 'execute commands myTransaction.Commit()Catch ex As Exception Response.Write(ex.Message) myTransaction.Rollback()End Try</code>Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other than rollback transaction?
Any help is greatly appreciated
View 3 Replies
View Related
Feb 29, 2008
We have a test db, a staging db and a live db. I have a stored procedure that runs fine on test and staging, but throws the following error on live.
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
The stored procedure uses linked servers and a transaction.
We're using the following transaction code in the stored procedure
BEGIN
BEGIN TRANSACTION
BEGIN TRY
---
procedure stuff here
---
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @ErrorSeverity INT, @ErrorNumber INT, @ErrorMessage NVARCHAR(4000), @ErrorState INT
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
IF @ErrorState = 0
SET @ErrorState = 1
RAISERROR ('ERROR OCCURED:%d', @ErrorSeverity, @ErrorState, @ErrorNumber)
IF XACT_STATE() < 0
ROLLBACK TRANSACTION
END CATCH
END
I found the following link which seems to be the problem we're experiencinghttp://support.microsoft.com/kb/937517
The link includes a workaround which is the following:
"To prevent the SQLNCLI provider from sending an attention signal to the server, use the SQLNCLI provider to consume fully any rowsets that the OLE DB consumer creates. "
How do I use the SQLNCLI provider to fully consume any rowsets?
View 11 Replies
View Related
Sep 12, 2007
I have an issue using parameterised reports connecting to Oracle using "ODBC" and "Microsoft OLE DB Provider for Oracle" using parameteried reports. The following error is generated "ORA-01008 not all variables bound (Microsoft OLE DB Provider for Oracle)" and a similiar one for ODBC. It works fine for simple reports. Do these 2 drivers have issues passing parameters for a remote Oracle query?
Thanks.
View 4 Replies
View Related
Apr 2, 2007
Hi!
I'm loading from Oracle using the OraOLEDB.Oracle.1 provider since I need unicode support and I get the following error:
TITLE: Microsoft Visual Studio
------------------------------
Error at myTask [DTS.Pipeline]: The "output column "myColumn" (9134)" has a precision that is not valid. The precision must be between 1 and 38.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
For most of my queries to Oracle I can cast the columns to get rid of the error (CAST x AS DECIMAL(10) etc), but this does not work for:
1) Union
I have a select like "SELECT NVL(myColumn, 0) .... FROM myTable UNION SELECT 0 AS myColumn, .... FROM DUAL"
Even if I cast the columns in both selects (SELECT CAST(NVL(myColumn, 0) AS DECIMAL(10, 0) .... UNION SELECT CAST(0 AS DECIMAL(10, 0)) AS myColumn, .... FROM DUAL) I still get the error above.
2) SQL command from variable
The select basically looks like this:
"SELECT Column1, Column2, ... FROM myTable WHERE Updated BETWEEN User::LastLoad AND User::CurrentLoad"
Again, even if I cast all columns (like in the union), I still get the same error.
Any help would be greatly appreciated. Thanks!
View 10 Replies
View Related
Oct 26, 2006
Is there any step by step help sites for setting up SQL 2005 linked (oracle 10) server?
I find MSDN articles but they referance winNT and 2000, I'm not getting very far and I'm not a DBA but need to get this working asap.
View 1 Replies
View Related
May 8, 2015
we recently got a scenario that we need to get the data from oracle tables which is installed on third party servers. we have sqlserver installed on ourservers. so they have created a DBLINK in oracle server to our sqlserver and published the DBLINK name.
what are the next steps that i need to follow on my sqlserver in order to access the oracle tables ?
View 2 Replies
View Related
Feb 22, 2006
Hello,
On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider.
I use one or the other according to my needs.
In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev.
Almost everything works except Microsoft OLEDB provider for Oracle.
ssis packages on the test machine will return an error
Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009.
Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
[Connection manager "one.oledb"]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.".
I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine.
Does anyone could point me to the right direction to solve this issue?
Thanks,
Philippe
View 17 Replies
View Related
Jan 12, 2006
Hi,
I am trying to make an oracle publiching from sql server 2005 enterprise final release, i installed the oracle client 10.2 (10g) on the same server where sql server already installed, i made different connection to oracle database instance and it was ok.
from sql server : right click on publication -New oracle publication-Next-Add Oracle Publisher-Add button-Add Oracle Publisher-i entered server insttance test1 and their users and passwords--connect --->
the oracle publisher is displayed in the list of publisher but when press ok i got the following error :
TITLE: Distributor Properties
------------------------------
An error occurred applying the changes to the Distributor.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.DistributorPropertiesErrorSR&EvtID=ErrorApplyingDistributor&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
SQL Server could not enable 'test1' as a Publisher. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The permissions associated with the administrator login for Oracle publisher 'test1' are not sufficient.
Changed database context to 'master'. (Microsoft SQL Server, Error: 21684)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21684&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Any idea about this error ?
Thanks
Tarek Ghazali
SQL Server MVP.
View 2 Replies
View Related
May 11, 2007
Hi Everyone,
I've been searching for a solution for this for a week-ish, so I thought I would post my quesiton directly. Here is my scenario..
Source: MS SQL Server
Destination: Oracle 10g
The destination table has a partition set on a column called "DATE_HIGH". How do I populate this date high column in my package? Currently I just have a source object, and a destination object, but I'm unclear how to populate this field in the destination. I've read one blog that states "use OLE DB Command" - but that isn't enough information for me to implement - Can someone be more specific in these steps? Here is an example of what my newb-ness needs to understand
OLE DB Source (Select * from Table) ---> OLE DB Command (What query goes here?) --> OLE DB Destination.
Second part of my question: There is a second column called "ROW_NUM" and there is an Oracle Sequence provided to me... What objects do I need (Source, Destination, OLE DB Command etc...) and how do I call this sequence to populate on the fly as I'm loading data from my source?
If these are simple questions - my appologies, I am new to the product.
Best Regards,
Steve Collins
View 1 Replies
View Related
Aug 23, 2007
Hi everybody,
I have designed a DTS package which will migrate a view from Sqlserver 2000 to Oracle.My package is using Microsoft OLEDB provider for Oracle driver for connecting to oracle.Im able to execute this package on the my system ie on the system where sqlclient is installed(Oracle client is also installed on my machine) .But when im doing it on the server im not able to do it.The Connection to Oracle Fails.
I wanted to know in order to connect to Oracle from the server,is it necessary that Oracle has to be installed on the server?.If yes, is it enough if i install oracle client on the server or Oracle Server version has to be installed on Server.
Please suggest me wht should i do know?
Thanks in advance
Regards
Arvind L
View 3 Replies
View Related
Jan 11, 2007
Hi--
I am running SQL Server 2005 on Win2k3:
Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790
I have the OraOLEDB.Oracle provider installed to the (C:oraclexe) directory.
I am having problems querying from linked oracle server. When i setup oracle as a linked server and purposely enter an incorrect password the query i run tells me i have an incorrect password. So it at least knows that. when i set the correct password and run a query I get this error:
(i replaced the real server name with "someServer".)
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer".
This is how I set up my Linked server:
Provider: "Oracle Provider for OLE DB"
Product Name: SomeServer
Data Source: SomeServer
Provider String: "Provider=OraOLEDB.Oracle;Data Source=SomeServer;User Id=MyLogin;Password=MyPassword"
The query I run is:
Select * from [Someserver].[schema or database]..[tbl_name]
Any help??? What am i missing?
View 3 Replies
View Related
