November 18, 2013 at 11:49 am
I'm in the middle of tuning a process so that it manhandles fewer rows from a linked server than when someone wrote it originally.
i'm awfully sure DTC is already set up and running, because the linked server is already in use for inserts/updates and deletes. i also know views exist on either server that join data between the two via matching linked servers to each other.
I have a query that works fine featuring EXECUTE() AT and some dynamic SQL I built to limit the results better;
EXECUTE(@cmd) AT [MIA-SERVERSQL]; --linked server
my issue is when switching to save that same EXECUTE() AT query data to a temp table, i get an unexpected error:
OLE DB provider "SQLNCLI10" for linked server "MIA-SERVERSQL" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 46
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "MIA-SERVERSQL" was unable to begin a distributed transaction.
the original query joined a local table against a pair of remote tables; as a result, the two remote tables (and their 40 million rows) got copied into tempdb for the join.
the example below returns a fraction of the data previously, and will speed up the process i'm fiddling with...if i can save the data.
the Basic Example:
--get the data related to the specific DatesOfService(s) we are currently processing.
DECLARE @cmd varchar(max) = '';
SELECT @cmd = s.Datez
FROM(SELECT
Datez = STUFF((SELECT ',' + CONVERT(varchar,CPUDateOfService, 111) --111 = converting to yyyy/mm/dd format
FROM [#ReconciliationStagingTable]
GROUP BY CPUDateOfService
ORDER BY CPUDateOfService
FOR XML PATH('')
),1,1,'')
) s;
--FOR XML trick created comma delimited list of dates
--now using EXECUTE AT to do the work on the remote server, build the command
SET @cmd = 'SELECT * FROM (
SELECT T1.ColumnList,
Count(*) AS ChargeCount,
Min(T2.DTPDAT) AS MaxPostDate
FROM MIAMIDW.dbo.DMTRANS T2
INNER JOIN MIAMIDW.dbo.DMMEMBR T1
ON T1.DMACRO = T2.DTACRO
AND T1.DMFAMNO = T2.DTFAMNO
AND T2.DTTTYP = ''C''
GROUP BY T1.ColumnList
) MyAlias
WHERE MyAlias.MaxPostDate IN (SELECT convert(datetime,fn.value) AS val FROM MIAMIDW.dbo.DelimitedSplit8K('''
+ @cmd + ''','','') fn )'; --splitting the comma delimited list of dates
INSERT INTO #RemoteResults
EXECUTE(@cmd) AT [MIA-SERVERSQL]; --linked server
so to recap, if i don't try to insert into my temp table, the query works fine.
If i try the temp table, then i get the error quoted above.
I can't seem to get my mind around the problem...it seems like saving the data is adding an implicit transaction due to teh temp table, and that is the connection doesn't like it; to me, it seems like a false DTC error.
Lowell
November 18, 2013 at 12:40 pm
answering my own question:
found a post that referenced "linked server" along with the error;
it pointed out that toggling this option on the linked server from true to false resolved the issue:
--returned an error for the insert into #temp
EXEC master.dbo.sp_serveroption @server=N'MIA-SERVERSQL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
--resolves issue
EXEC master.dbo.sp_serveroption @server=N'MIA-SERVERSQL', @optname=N'remote proc transaction promotion', @optvalue=N'false'
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply