Hey,
I have a Sql 2000 w/ SP4 on Server 2003 running an sp that inserts and updates to a 2005 64 bit DB w SP 1 on Server 2003. The SP has worked forever. No code or server changes. Now all of the sudden I randomly get this error. It will work, then fail 2Xs, then work, then fail. Very bazaar.
Any help is appreciated! I have done the MSDTS, ran catalog sps, forced TCP/IP. I'm lost.
If I run the sp on the 64bit box it works fine, but on the 32bit box now, I get the frequent error below.
[OLE/DB provider returned message: Unspecified error]
Msg 8525, Level 16, State 1, Procedure sp_xxx_xxx_xxx, Line 1496
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
The line number in the error will change as well and is not always consistent.
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?
Is there anyway of running a Store Procedure in Database A that's going against database B? I have a store procedure that does a select on table A in database A and a select on table B in database B.
The problem is that the user have exec rights to the store procedure, which is an object in database A. The store procedure won't run because of permission rights to table B in database B.
Is there anyway of queryiny table B without giving the user select rights to that table? Anyone out there had the same problem?
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?
I have a ODBC data source setup for the AS400DB2 which is AS400JDE
The linked server has been created and all the tables are visible within enterprise manager.
If I had a table F0101, datasource = AS400JDE and linked server of AS400
How would I query it.
I gave tried AS400.AS400JDE.DBO.F0101, but i get the following error message. OLE DB provider 'MSDASQL' does not contain table '"as400jde"."dbo"."f0101"'.
i added a linked sql 7 production server to my sql 7 development server so i could perform some data comparisons between the two servers. after i linked in the server, i was able to see all the icons for the various tables in the production server. i went to query analyzer and executed the following openquery:
select * from openquery(itdev_s08, 'select * from dbo.tablename')
it returned all the rows in the table. however, i want to comapre several different values so i needed a better way to query. i then decided to try the distrbuted query below:
select * from linkedserver.dbname.dbo.tablename
however instead of a result set i got the error message below:
Server: Msg 7314, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' does not contain table '"dbname"."dbo"."tablename"'.
i went to BOL and it told me the only reason i would get this error is if the object did not exist or if i did not have permission to use it. however, i know the object exists and i have the appropriate permission becuase i can obtain a result set from the first query in the same query analyzer window. is my syntax incorrect? what am i doing wrong?
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.
Many posted this message and no one anwered. i am facing the same problem now.
Got the error message
Server: Msg 7391, Level 16, State 1, Line 1 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 the specified transaction coordinator. ]
My MSDTC is on. I had setup linked server using OLEDB.
Microsoft did'nt give enough info or solution for this. ANybody faced this prob and solved it?
I have a procedure where it runs a procedure on another server and returns the results to the calling procedure and dumps it into a temp table..
I get the following message: ----- Server: Msg 7391, Level 16, State 1, Procedure proc1, Line 60 The operation could not be performed because the OLE DB provider 'SQLOLEDB' does not support distributed transactions. [OLE/DB provider returned message: Distributed transaction error] ----
But both the servers are running the distributed transaction corordinator
example:
create procedure dbo.proc1 @param1 int as
create table #temp (col1 int col2 varchar(255) )
insert into #temp EXEC server.database.dbo.proc2 @param1 = @param1
Hi all, I am trying to merge data of 2 tables on different servers with an insert statement.
INSERT INTO SERVER1.db.owner.table select s2.* from SERVER2.db.owner.table as s2 LEFT JOIN SERVER1.db.owner.table as s1 ON s1.key=s2.key where s1.key is null
I got this error. 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 the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
while the select query is giving the result. I have done simmilar inserts on some other tables which worked fine
I have created sp_addlinkedserver. and DTC set on both servers.
Hi All, I am trying to use distributed transaction (using linked Server). But getting the folloing error..
Some one please help...
following is the error...
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by another session.] OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Suppose we have 15 tables distributed to 3 databases. So if we want to to load some related data from diffrent tables of diffrent databases, we have to make more connections to the other databases, and it takes some time to establish the connection. but if we use 1 database with 15 tables, then we wont have this problem and we wont make more connections. but using 1 database, will grow the size of database file (*.mdf) and sure this couses to take more time of file operation like record-seek-time, record-insertion-time and etc.
which of these solutions have better performance? Distributed databases or single one?
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.
I work in the data warehouse team of my organization. We are currently rearchitecting our server environment. One of the ideas on the table is to devote a separate server for ETL processing. The databases would reside on several other servers. The ETL server would run the SSIS packages. I'm questioning if this would be a good idea.
The database servers would continue to carry the load of the query processing for the ETL. But with the ETL process on a separate server, the resultsets would need to go over the network for the SSIS package to then work on them. Plus, they would then have to go back over the wire to the destination server.
Are there advantages to this setup? Does this setup have better scalability? Or, would it be better to run the ETL from either the source or destination database server?
I am getting the following error when with SQL Express.SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online. While I was very please to see such a verbose error and directions on where to find the answer I have yet to figure out how to turn this option on... I tried sp_configure 'Ad Hoc Distributed Queries', 1 but got the following error The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.If I execute only sp_configure it does not list Ad Hoc Distributied Queries as an option. I checked the sql Books Online and it tells me to use the Surface configuration tool which SQL Express does not seem to have.... Could someone help me out with this?Thanks - Mark
i am inserting new record in linked server and i need to get the id (which is of course autonumber) of newly added record. can't i get it using SCOPE_IDENTITY( ) ? SCOPE_IDENTITY( ) seems to be returning null. so SCOPE_IDENTITY( ) doesn't work in distributed transaction?
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.
Can anyone tell me why Query B (see below) works but Query A does not? When Query A is run the following error is received. I would love to know why using a subquery allows Query B to run.
Server: Msg 8623, Level 16, State 2, Line 1 Internal Query Processor Error: The query processor could not produce a query plan.
Query A. SELECT pt.Description, pa.Method_Order, os.LogAction, Sum(pa.Amount) Total FROM BO_PaymentAmountsApplied pa, BO_OrderStatusLog os, Members.members.dbo.MBR_PaymentTypes pt WHEREpa.LogID = os.LogID AND os.LogAction IN (1,2,3) AND pt.PmntTypeID = pa.MethodID AND pa.OrderID = 1526925 GROUP BY pt.Description, pa.Method_Order, os.LogAction
Query B. SELECT pt.Description, pa.Method_Order, os.LogAction, Sum(pa.Amount) Total FROM BO_PaymentAmountsApplied pa, BO_OrderStatusLog os, Members.members.dbo.MBR_PaymentTypes pt WHERE (pa.LogID = os.LogID) AND (os.LogAction IN (1,2,3)) AND (pt.PmntTypeID = pa.MethodID) AND (os.orderid = pa.orderid) AND (pa.OrderID in (select orderid from BO_PaymentAmountsApplied where orderid = 1526925)) GROUP BY pt.Description, pa.Method_Order, os.LogAction
Hi, I am trying to use linked servers (all SQLServer 6.5 and 7.0). When I issue a distributed query utilizing the four-part qualified table name for the distributed machine and using the LIKE operator, the execution plan shows that the remote query is returning ALL the rows from the remote distributed server, then performing the LIKE filter locally on my server! Needless to say, performance sux! Any ideas what I may be doing wrong or what settings I am unaware of? Example query:
select * from server.database.schema.table where column1 like 'A%'
I have the following distributed query. I am running it from SQL Server "CASTER" while it links databases from SQL1 and SQL2 Servers.
SELECT T1.ENTITY, T2.EMPLNAME, T1.EMPLID, T1.FISCAL_YR, T1.ACCOUNT_NBR10, T1.POSITION_NBR, JOBCLASS, FINAL_BGT_FTE, FINAL_BGT_PCT, FINAL_BGT_SAL, FINAL_BGT_FTB, TERM FROM SQL1.BUDGET.DBO.BDBPDCTB T1, SQL2.TECHRIS.DBO.TRBASCTB T2 WHERE T1.FISCAL_YR = '2003' AND T1.EMPLID > 0 AND T1.EMPLID = T2.EMPLID AND T1.ENTITY = 'H' AND T1.ORGID = 'TT' AND EXISTS (SELECT T3.EMPLID FROM SQL1.BUDGET.DBO.BDBPDCTB T3 WHERE T3.EMPLID = T1.EMPLID --------ERROR HERE AND T3.ENTITY = 'H' AND T3.FISCAL_YR = '2003' GROUP BY T3.EMPLID HAVING SUM(T3.FINAL_BGT_PCT) > 100) ORDER BY 1,2,5,6
In the error line, it says, T1 does not match with a table name or alias name used in the query.
SELECT T1.ENTITY, T2.EMPLNAME, T1.EMPLID, T1.FISCAL_YR, T1.ACCOUNT_NBR10, T1.POSITION_NBR, JOBCLASS, FINAL_BGT_FTE, FINAL_BGT_PCT, FINAL_BGT_SAL, FINAL_BGT_FTB, TERM FROM SQL1.BUDGET.DBO.BDBPDCTB T1, SQL2.TECHRIS.DBO.TRBASCTB T2 WHERE T1.FISCAL_YR = '2003' AND T1.EMPLID > 0 AND T1.EMPLID = T2.EMPLID AND T1.ENTITY = 'H' AND T1.ORGID = 'TT' AND EXISTS (SELECT T3.EMPLID FROM SQL1.BUDGET.DBO.BDBPDCTB T3,SQL1.BUDGET.DBO.BDBPDCTB T5 WHERE T3.EMPLID = T5.EMPLID --------NO ERROR AND T3.ENTITY = 'H' AND T3.FISCAL_YR = '2003' GROUP BY T3.EMPLID HAVING SUM(T3.FINAL_BGT_PCT) > 100) ORDER BY 1,2,5,6
When I am going to have the query like the above, i dont get any error.
I did not get any reply for my previous post. So i am just trying to make my doubt clear.
I have a subquery within a distributed query.
Eg:
SELECT T1.deptID FROM SERVER1.ACCOUNT.DBO.DEPT as T1 where deptid IN (SELECT T2.DEPTID FROM SERVER2.DEPARTMENT.DBO.DEPT as T2 WHERE T1.DIVISIONID = T2.DIVISIONID)
In the above query.. T1.DIVISIONID is not recognoized and it says T1 is not a table or alias name though I have declared it upfront.
You have to note that both the ACCOUNT and the DEPARTMENT database are on different servers.
If they are on the same servers, this should not be a problem at all. Also, all the security is working perfectly for running a distributed query for me.
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=W:MyExcel.xls', 'select * from [Sheet1$]') which works on my local server with Microsoft office XP Professional installed
But keep on failing on the QA server which has MDAC installed only, no Microsoft Office installed
The error msg is 7399. I thought it's because of permission issue.
But problem still resides after I move the Excel file into that QA NT server and execute the statement with a service account which has sysadmin permission on both NT Server and SQL Server
What might be te reason?Why it's working perfectly in my Local server.
-------------------------------------- This is the error message:
Server: Msg 7399, Level 16, State 1, Line 2 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. [OLE/DB provider returned message: Unspecified error] OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
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.
Hi: When I tried to run a remote proc (with one parameter of date to return a set of records (no problem in return records) and to insert to the local database table A, with following errors:
Server: Msg 7391, Level 16, State 1, Line 1 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 the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Hello, I've got stuck trying to run distributed transaction on same machine with two SQL instances. () Running transaction from Query Analizer With SET XACT_ABORT ON it works fine. (Without that - it throws an error about unable to begin nested distributed transaction). But from Application (C++, ODBC) it waits for a while and throus an error: 'unable to begin distributed transaction' even SET XACT_ABORT ON is applied before issuing transaction in Stored Procedure.
