August 17, 2009 at 1:47 am
i have a stored proc which will be joining tables from a remote server with tables in local server using a linked server in SQL Server 2005. Here is my problem, whn the stored proc was executed and results returned should be 265 rows but it only returned 2 rows. Any ideas why is this happening?
DECLARE @Scheme varchar(10)
declare @DateTo datetime
declare @DateFrom datetime
declare @DateToInt varchar(10)
declare @DateFromInt varchar(10)
declare @StfType varchar(10)
select @Scheme = 'EMS'
select @DateFrom = '01-July-2009'
select @DateTo = '31-july-2009'
select @DateToInt = '20090731'
select @DateFromInt = '20090701'
SELECT @Scheme XScheme, acq.productType, acq.ProdCode, prdt.ProdName, prdt.DateExpired,
prdt.ProductClass, prdt.ProductSubClass, acq.facilityCode AppId, acq.AppStatus,
acq.cpNo cpNoRefNo, acq.FinalDate, acq.AcquisitionCount, acq.AcquisitionRevenue,
acq.AcquisitionGross, dimAgt.EmployeeNo, acq.TeamCode, acq.costCenterAbbr BizUnit,
acq.ChannelCode, NTCFlag, 'SISTEM' COMMTYPE,
acq.i_AgentCode, @DateTo DATETO, acq.campaign CategoryCode, acq.promoCode,
'' additionalInfo, getdate() SDATE, 0 acquisitionKRA, 0 INCMULTP, 0 cc_incAmt,
dimAgt.staffType, 'Y',
NULL locationCode, dimAgt.DateResign,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, dimAgt.DesignationCode,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL
FROM v_acq2 acq
INNER JOIN Products_Mapping prdt on acq.ProdCode = prdt.ProdCode
AND prdt.ProductType IN ('MORTGAGE')
LEFT JOIN Server2.ISTS.dbo.dimstaffagent dimAgt on dimAgt.employeeNo = acq.AgentCode
WHERE acq.FinalDate BETWEEN @DateFromInt AND @DateToInt
AND acq.agtNRICnew IN
(
SELECT DISTINCT dimAgt.NRICnew NewIC
FROM Server2.ISTS.dbo.dimstaffagent dimAgt INNER JOIN i_library lib ON
dimAgt.DesignationCode = lib.Code
AND dimAgt.StaffType = lib.Doc2
AND lib.Module = 'SYS'
AND lib.Type = 'Designation'
AND lib.Stat1 = 'ACT'
AND (lib.Doc1 = 'EMS' OR lib.Doc1 = 'AMMS')
AND @DateTo BETWEEN lib.Date1 AND lib.Date2
WHERE dimAgt.dateResign IS NULL
UNION
SELECT DISTINCT dimAgt.NRICnew NewIC
FROM Server2.ISTS.dbo.dimstaffagent dimAgt INNER JOIN i_library lib ON
dimAgt.DesignationCode = lib.Code
AND dimAgt.StaffType = lib.Doc2
AND lib.Module = 'SYS'
AND lib.Type = 'Designation'
AND lib.Stat1 = 'ACT'
AND (lib.Doc1 = 'EMS' OR lib.Doc1 = 'AMMS')
AND @DateTo BETWEEN lib.Date1 AND lib.Date2
WHERE dimAgt.dateResign >= @DateFrom
AND (
dimAgt.NRICnew NOT IN ('') OR
dimAgt.NRICnew NOT IN (
SELECT DISTINCT dimAgt.NRICnew NewIC
FROM Server2.ISTS.dbo.dimstaffagent dimAgt INNER JOIN i_library lib ON
dimAgt.DesignationCode = lib.Code
AND dimAgt.StaffType = lib.Doc2
AND lib.Module = 'SYS'
AND lib.Type = 'Designation'
AND lib.Stat1 = 'ACT'
AND (lib.Doc1 = 'EMS' OR lib.Doc1 = 'AMMS')
AND @DateTo BETWEEN lib.Date1 AND lib.Date2
WHERE dimAgt.dateResign IS NULL
)
)
UNION
SELECT DISTINCT dimAgt.NRICnew NewIC
FROM Server2.ISTS.dbo.dimstaffagent dimAgt
INNER JOIN i_library lib ON dimAgt.EmployeeNo = lib.Doc1
AND lib.Module = 'SYS'
AND lib.Type = 'specialstaff'
AND lib.Code = @Scheme
AND lib.Stat1 = 'ACT'
AND @DateTo BETWEEN lib.Date1 AND lib.Date2
WHERE dimAgt.dateResign IS NULL
UNION
SELECT DISTINCT dimAgt.NRICnew NewIC
FROM Server2.ISTS.dbo.dimstaffagent dimAgt
INNER JOIN i_library lib ON dimAgt.EmployeeNo = lib.Doc1
AND lib.Module = 'SYS'
AND lib.Type = 'specialstaff'
AND lib.Code = @Scheme
AND lib.Stat1 = 'ACT'
AND @DateTo BETWEEN lib.Date1 AND lib.Date2
WHERE dimAgt.dateResign >= @DateFrom
AND
(
dimAgt.NRICnew NOT IN ('') OR
dimAgt.NRICnew NOT IN
(
SELECT DISTINCT dimAgt.NRICnew NewIC
FROM Server2.ISTS.dbo.dimstaffagent dimAgt
INNER JOIN i_library lib ON dimAgt.EmployeeNo = lib.Doc1
AND lib.Module = 'SYS'
AND lib.Type = 'specialstaff'
AND lib.Code = @Scheme
AND lib.Stat1 = 'ACT'
AND @DateTo BETWEEN lib.Date1 AND lib.Date2
WHERE dimAgt.dateResign IS NULL
)
)
)
AND
(
(
acq.productType = 'MORTGAGE'
AND appStatus = 'DE'
)
)
August 17, 2009 at 6:11 am
it's pretty unlikely that the cause is due to the linked servers.
the procedure you've pasted is pretty huge, and without knowing any table structure or data details, it'd be hard to really help you out, but one thing I could recommend if you really think the problem is the linked servers, is to make a copy of all the tables in question, in one common location, like a backup server, and then run the query with everything local.
or, you could make temp tables, and select all the records from the linked server tables, put them in the temp tables, and then do everything locally on the local server.
August 17, 2009 at 7:46 pm
Actually, I have tried both of the methods you mentioned to determine if the cause is the linked server. I tried to copy the database from the remote server and restore in the local server and run it locally, results returned are 265 rows which is correct but once i change to linked server, it returns only 2 rows. For the temp table, i have that as well but it is still giving me 2 rows.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply