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 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?
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.
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 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.
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.
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.
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-
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 -
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.
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.
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.
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)
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.
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?
Hi Chaps!! I am in serious problem that My production sql 2000 server with winSp 4 and sqlSP 2 with slammer hotfix is not executing four part distributed query well when I combine the query with begin tran statement it enter into hang mode... else without begin tran it is fine.
request to all of u to get rid of this situation as some modules of our applicaiton is not functioning...
can sp3a installation help or going back to winSp2/3 will be helpfull. (recently we have applied winsp4 but i don't think this is concerned with it)
I have a test environment setup consisiting of one SQL Server 2000 32 bit instance on one box and one SQL Server 2005 64 bit edtion on another box. Nearly everything works fine but have found this one snag...
Linked Server name: SQL2005
From the SQL Server 2000 instance in Query Analyzer....
-- this works fine Delete from [SQL2005].myDB.dbo.myTbl
-- this just hangs Begin Tran Delete from [SQL2005].myDB.dbo.myTbl
It doesn't make any difference what provider I use on the Linked Server.
I have tried it on a SQL Server 2000 to SQL Server 2000 configuration and, interestingly, it would only work if I used set XACT_ABORT ON. However, in the SQL Server 2000 to 2005 configuration, this doesn't help.
We have a number of DTS packages that do deletes across the linked server that are failing due to this issue so we're being stopped from continuing with the migration to SQL Server 2005.
Just curious if anyone out there has had success running a Distributed Transaction on Windows 2000 Professional SP4 lately?
I have come to the conclusion that is is virutally impossible. I am running MSDE on a Win2k Pro machine and trying to run a distributed transaction via linked server and am getting the
"The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction."
error. Yes I have been scouring the web looking for answers and have implemented pretty much everything I have run accross... and Yes the MSDTC service is started on both machines... BTW: If I run the procedure on XP it works great!!!
I am wondering if anyone even uses Win2k Pro anymore let alone trying to run a distributed tarnsaction on the darn thing.
If anyone out there has it running let me know I would love to chat with you for a minute or two.
I have been struggling with a problem for the last couple days now regarding MSTD and distributed transactions. The main issue is that 3 servers are each sitting in a different domain. Non of the domains trust each other.
As of yesterday, I could get all the SQL 2005 chatting to each other using MSDTC without a problem. However, I have a SQL 2000 box which refuses to work. A normal query across a linked server works fine. A distributed transaction will not work. This is the case from A to B and B to A.
I have tested with DTCPing and it says all is ok.
The error messages are:
SQL 2000 > SQL 2005
OLE DB provider "SQLNCLI" for linked server "T-ServerTServer" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "T-ServerTServer" was unable to begin a distributed transaction.
And the other way round:
[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].
Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
If watching the DTC monitor, it shows an active transaction then goes back to 0.
All instances are on a 4 node cluster with 3 nodes turned off for the testing.
I have run out of things to try. Most of the symptoms for the above messages are to do with DTC security, the Turn RPC Security Off reg hack. All instances and OS (W2K3) are the latest updates / patches.
As for the 3rd server, SQL 2005, it is happy to talk
Anybody got a gem which I can try?
Edit: Both SQL 2005 servers are x64 while the 2000 is x86
I am trying to write some admin only procedures which will collect information to one of my development server from other production and development servers.
I have created linked servers to access these other servers on the development server. This development server is SQL Server 2000 EE. Other servers which I want to access are 2000 and 2005 (vaious editions)
E.g I have another development server called PRODTEST which is SQL Server 2005 and on the development server I have created a linked server pointing to PRODTEST called TESTLINKSRV. I want to access new object catalog view (as I do not want to use sysobjects)
When I run the following query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys].[objects]
I get following error,
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName=' TESTLINKSRV ', TableName='" DBNAME "."sys"."objects"'].
Msg 7314, Level 16, State 1, Line 1
OLE DB provider ' TESTLINKSRV ' does not contain table '"DBNAME"."sys"."objects"'. The table either does not exist or the current user does not have permissions on that table.
So I try this query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys.objects]
and I get following error
Msg 208, Level 16, State 1, Line 1
Invalid object name TESTLINKSRV.DBNAME.sys.objects'.
So bottom line is how do I access catalog views on a 2005 server from a 2000 server using linked server?
I hope someone understands what I am trying to achieve. Please let me know what is it that I am doing wrong. Thank you
hi, i have developed an web-enabled database application using ASp.net, C# and sql server 2000. now i want to implement transaction controls over the same can anyone plz help me in implementing the same? thanks in advance
I need to live update my SQL-Server 2000 database from AS400 database. I usually use Access 2002 database between the AS400 database and the SQL-Server 2000 database to reflect any live update. I use the (Link Tables...) option when creating a new table in access and link it with the ODBC to connect with the AS400 database. After that I work with the Access link table.
Is there any tool in SQL-Server 2000 to show the live transactions in AS400 databasee so I can work on it?
if it is possible to run a distributed query against 2000 from 2005, what would the OPENDATASOURCE parameters look like? I'd like to be able to pivot without copying my older 2000 db to 2005 or using linked servers..
For reference, here's an example of a distrib query that reads excel...
ie SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C: estxltest.xls;Extended Properties=Excel 8.0')...[Customers$]
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
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?
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?
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?
