Hello I have a issue of performace
declare @basecode
Select @basegcode = 'ABCDEFGH'
declare @parm as int
Select @parm = count(column) from LINKSERVER.DATABASE.schemma.table
where left(column, 8) = @basegcode
it take more than 3 minutes
if i use
EXEC ( 'SELECT count(*) as column
FROM DATABASE.schemma
where left(column, 8) = ''ABCDEFGH''') AT LINKSERVER;
it take less than a second
but i can't safe the returning value into a variable
What can i do? I try
CREATE TABLE #msver
(internal_value INT)
Insert #msver
EXEC ( 'SELECT count(*) as column
FROM DATABASE.schemma
where left(column, 8) = ''ABCDEFGH''') AT LINKSERVER;
and i receive the following error
OLE DB provider "PGNP" for linked server "LINKSERVER" returned message "MSDTC XARMCreate error".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "PGNP" for linked server "LINKSERVER" was unable to begin a distributed transaction.
FYI: the table have about 1,000,000 records
