This is the error message I'm receiving: > Msg 7391, Level 16, State 2, Procedure spStoredProc, Line 62 [Batch Start Line 1] > The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "MyLinkedServer" was unable to begin a distributed transaction. As a test my stored procedure query is just SELECT 1 AS A and works locally on the server but doesn't work when I call it remotely on a linked server.

I am using SQL Server 2016 and recently one of the stored procedures I have been using is throwing error. I use a select statement with joins on many tables to the remote server using a linked server **SQL** BEGIN TRANSACTION SELECT a.col1, b.col1 FROM [LinkedServer].[Database].[Schema].[TableA] AS a JOIN [LinkedServer].[Database].[Schema].[TableB] AS b ON a.col2 = b.col2 COMMIT TRANSACTION **Error** New request is not allowed to start because it should come with valid transaction descriptor When I change the BEGIN TRANSACTION to BEGIN DISTRIBUTED TRANSACTION I am running into a different error OLE DB provider "SQLNCLI11" for linked server was unable to begin a distributed transaction. The remote server too is SQL Server 2016. Not sure on how to resolve the errors.

I have a query that uses the FORCESEEK hint and runs fine on the server where the database I'm querying lives. If I try to run the same exact query remotely from a different server (targeting the original server), I get the following error: > Msg 7436, Level 16, State 1, Line 4 The query processor could not produce a query plan because FORCESEEK or FORCESCAN hints on table or view 'TableBeingQueried' cannot be used with remote data sources. Remove the hints and resubmit the query. Example T-SQL: SELECT DISTINCT Table3.Field5 FROM Server1.Database1.Table1 AS T1 INNER JOIN Server1.Database1.Table2 AS T2 WITH (FORCESEEK) -- Index exists for T2.Field2 ON T1.Field1 = T2.Field2 INNER JOIN Server1.Database1.Table3 AS T3 WITH (FORCESEEK) -- Index exists for T3.Field3 ON T2.Field4 = T3.Field3

when I run the script below all at once I get this error message: > Msg 15281, Level 16, State 1, Line 58 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', search for > 'Ad Hoc Distributed Queries' in SQL Server Books Online. But when I run this first and then run the full script then it is all fine. exec sp_configure 'Ad Hoc Distributed Queries', 1 reconfigure The impression that I got is that sql-server will look at the configured values in memory, and does not seem to realise that they have changed. How can I alter this behaviour? here is the full script: SET NOCOUNT ON; declare @prevAdvancedOptions int declare @prevXpCmdshell int declare @adhocdistque int ------------------------------------------------------------------------------------------- --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. select @prevAdvancedOptions = cast(value_in_use as int) from sys.configurations where name = 'show advanced options' select @prevXpCmdshell = cast(value_in_use as int) from sys.configurations where name = 'xp_cmdshell' select @adhocdistque = cast(value_in_use as int) from sys.configurations where name = 'Ad Hoc Distributed Queries' PRINT @prevAdvancedOptions PRINT @prevXpCmdshell print @adhocdistque if (@prevAdvancedOptions = 0) begin exec sp_configure 'show advanced options', 1 reconfigure end if (@prevXpCmdshell = 0) begin exec sp_configure 'xp_cmdshell', 1 reconfigure end if (@adhocdistque = 0) begin exec sp_configure 'Ad Hoc Distributed Queries', 1 reconfigure end /* ----------------------------------------------------------------- do work - begin */ SELECT * FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes' , 'set fmtonly off exec msdb.dbo.sp_help_job') --where name in -- ( 'WebFeed UKProductOffer Offers' -- ,'WebFeed USProductOffer Offers' -- ,'WebFeed DEProductOffer Offers' -- ,'WebFeed ATProductOffer Offers' -- ,'WebFeed FRProductOffer Offers' -- ,'WebFeed EUProductOffer Offers' -- ,'WebFeed AUProductOffer Offers') /* ----------------------------------------------------------------- do work - end */ --------------------------------------------------- -- restore the settings as they were previously --------------------------------------------------- if (@prevXpCmdshell = 0) begin exec sp_configure 'xp_cmdshell', 0 reconfigure end if (@prevAdvancedOptions = 0) begin exec sp_configure 'show advanced options', 0 reconfigure end if (@adhocdistque = 0) begin exec sp_configure 'Ad Hoc Distributed Queries', 0 reconfigure end

I have two instances _ServerA_ and _ServerB_, and I have created a linked server in _ServerA_ for _ServerB_ as **Linksrv_B**. I can execute a query on _ServerA_ using the four part naming convention: SELECT * FROM Linksrv_B.master.sys.databases or OPENQUERY(): SELECT * FROM OPENQUERY(Linksrv_B, 'SELECT * FROM master.sys.databases') I know linked server works across heterogeneous database using distributed transaction as a rowset. 1. Where does the OLEDB provider gets initiated/connected? ServerA or ServerB? 2. Where does the query gets executed on ServerA or ServerB? Does optimizer comes into play if so on which server? 3. Where does the result set get cached? 4. Could anyone give us a detailed explanation how linked server works.

Since I may not come across or get a chance to use a distributed database system. I would like to understand an example of a Distributed Database Query. I know that in a distributed system, you have database stored on multiple computers connected via network. Basically, I want to know on what level the data is kept on different computer 1. Is it on table-level, database-level etc.? 2. a part of some table is kept on "Computer A" and another part of table is on "Computer B"? If so then Querying the table will take fetching the data from multiple computers.