View with linked server table: varying results!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

View with linked server table: varying results!

Author

Topic

im1dermike
Posting Yak Master

222 Posts

Posted - 2010-03-02 : 12:23:57

I have a view that is made up of 5 different tables, one of which is in another server's database. I connect to it via a linked server. That table has less than 50 rows and the data is, of course, static. When I run the view in either design mode or in a query, the results are often different between runs! There is a different row count, off by usually 1-2 rows, but sometimes up to 10. When I copy the table to the same server as the view itself and remove the linked server reference, the result is constant.

What could possibly explain this phenomenon?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 12:31:26

do you have any comparisons involving date using getdate() or current timestamp functions?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

im1dermike
Posting Yak Master

222 Posts

Posted - 2010-03-02 : 12:40:33

That is what I first suspected. I did have a date stamp column with GETDATE() as the value. I changed that from a datetime type to varchar(10) and changed the value to CONVERT(VARCHAR(10), GETDATE(), 120). That was still giving me varying results even though the value is '2010-03-02'. So I removed the column entirely and still get varying results. The only way I can get static results is when I remove the linked server reference. Sometimes I get the same result when I run it like 9 consecutive times, but then the row count will be off by 1 on the 10th time.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 12:48:45

is your table data highly volatile? are users constantly doing DML operations in it? are there any other time varying functions used? are you using NOLOCK hint?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

im1dermike
Posting Yak Master

222 Posts

Posted - 2010-03-02 : 12:50:17

The table linked via the linked server? The only people who could access it are myself and 2 other programmers who I sit next to. That table is as static as can be.

In fact, I just ran a query that returned the total row count of all 5 tables used by the view and then the row count for the view. In one run, I got a row count for the view of 22,515 and on another - seconds later - a count of 22,512. The total row counts for the 5 tables when I ran the query both times were identical.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 12:57:17

are you using any top x records or something inside view?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

im1dermike
Posting Yak Master

222 Posts

Posted - 2010-03-02 : 12:58:45

Yes. It looks like it starts with "SELECT DISTINCT TOP (100) PERCENT". I just removed that and the problem remains.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 12:59:52

whats the order by clause used?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

im1dermike
Posting Yak Master

222 Posts

Posted - 2010-03-02 : 13:00:27

There is no ORDER BY clause.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 13:05:00

whats the significance of TOP without order by?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-03-02 : 13:05:21

What if you use "SELECT DISTINCT TOP (100) PERCENT WITH TIES"?

PBUH

im1dermike
Posting Yak Master

222 Posts

Posted - 2010-03-02 : 13:17:05

Not sure. I think it automatically put that for me because I would never enter that myself. I removed the PERCENT so now my view starts "SELECT DISTINCT". Still the same issue.

What does the "WITH TIES" do?

Sachin.Nand

2937 Posts

Posted - 2010-03-02 : 13:21:00

The problem with TOP 100 PERCENT in the View is that when value has two or more records with the same value,the View includes only one.So WITH TIES all the records are returned.
But in any case it is not of any use for your problem.

PBUH

im1dermike
Posting Yak Master

222 Posts

Posted - 2010-03-02 : 13:55:22

I'm checking now if installing SP3 for SQL Server 2005 will resolve the issue...

im1dermike
Posting Yak Master

222 Posts

Posted - 2010-03-02 : 14:01:58

That didn't change anything.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-02 : 15:22:46

post the view

im1dermike
Posting Yak Master

222 Posts

Posted - 2010-03-03 : 08:30:44

Here is the view. I worked around the issue by creating a UDF, but this is still an unnerving issue.

SELECT DISTINCT dbo.DD_Collecting_Providers_All_temp.PRTCPNT_ID, dbo.DD_Collecting_Providers_All_temp.NBR_SSN_RCPT, dbo.CWDSPrtcpntDemoInfo.NAM_FIRST_RCPT, dbo.CWDSPrtcpntDemoInfo.NAM_LAST_RCPT, dbo.CWDSPrtcpntProjectInfo.CDE_PROJ, CASE WHEN cde_proj LIKE 'NC%' OR cde_proj LIKE 'WS%0' THEN Program ELSE CASE WHEN cde_proj LIKE 'PF0017' THEN 'GPUAC' ELSE CASE WHEN Provider NOT IN
(SELECT DISTINCT Agency_Name
FROM LinkToTWDSYS.DATABASE_SETTINGS.dbo.AIMS_AGENCY_SUB_PROGRAMS
WHERE (Agency_Status = 'True')) THEN 'Unknown' ELSE Provider END END END AS Provider, dbo.CWDSPrtcpntProjectInfo.DTE_PROJ_REFL,
dbo.DD_Collecting_Providers_All_temp.DTE_PROJ_BEGIN, dbo.CWDSPrtcpntProjectInfo.DTE_PROJ_END,
dbo.CWDSPrtcpntProjectInfo.CDE_PROJ_TERM, dbo.MGS_FY_COLLECTING_PROJ_STATUS_temp.PROJ_STATUS,
CASE WHEN dbo.DD_Collecting_Providers_All_temp.DTE_PROJ_END = '' THEN 'Active' ELSE CASE WHEN cde_proj_term = 'S' OR
cde_proj_term = 'W' OR cde_proj_term = 'X' OR cde_proj_term = 'P' THEN 'Positive' ELSE 'Negative' END END AS PROJ_STATUS_TERM, GETDATE() AS DATE_STAMP,
CASE WHEN FS_FLAG = 'FS' THEN 'YES' ELSE 'NO' END AS FS_ONLY, dbo.DD_Collecting_Providers_All_temp.JobsiteCode
FROM dbo.CWDSPrtcpntProjectInfo INNER JOIN
LinkToTWDSYS.DATABASE_SETTINGS.dbo.AIMS_AGENCY_temp AS AIMS_AGENCY_temp_1 ON
dbo.CWDSPrtcpntProjectInfo.CDE_PROJ = RTRIM(LEFT(AIMS_AGENCY_temp_1.AGENCY_ID, 6)) INNER JOIN
dbo.DD_Collecting_Providers_All_temp ON
dbo.CWDSPrtcpntProjectInfo.DTE_PROJ_BEGIN = dbo.DD_Collecting_Providers_All_temp.DTE_PROJ_BEGIN AND
dbo.CWDSPrtcpntProjectInfo.PRTCPNT_ID = dbo.DD_Collecting_Providers_All_temp.PRTCPNT_ID AND
dbo.CWDSPrtcpntProjectInfo.DTE_PROJ_END = dbo.DD_Collecting_Providers_All_temp.DTE_PROJ_END INNER JOIN
dbo.MGS_FY_COLLECTING_PROJ_STATUS_temp ON
dbo.DD_Collecting_Providers_All_temp.PRTCPNT_ID = dbo.MGS_FY_COLLECTING_PROJ_STATUS_temp.PRTCPNT_ID AND
dbo.DD_Collecting_Providers_All_temp.DTE_PROJ_BEGIN = dbo.MGS_FY_COLLECTING_PROJ_STATUS_temp.DTE_PROJ_BEGIN INNER JOIN
dbo.CWDSPrtcpntDemoInfo ON dbo.CWDSPrtcpntProjectInfo.PRTCPNT_ID = dbo.CWDSPrtcpntDemoInfo.PRTCPNT_ID
WHERE (dbo.CWDSPrtcpntProjectInfo.CDE_PROJ LIKE 'NC%' OR
dbo.CWDSPrtcpntProjectInfo.CDE_PROJ LIKE 'WS%0') AND (dbo.DD_Collecting_Providers_All_temp.JobsiteCode LIKE 'EC%' OR
dbo.DD_Collecting_Providers_All_temp.JobsiteCode LIKE '%-NC-%' OR
dbo.DD_Collecting_Providers_All_temp.JobsiteCode = 'Unknown') OR
(dbo.CWDSPrtcpntProjectInfo.CDE_PROJ NOT LIKE 'NC%') AND (dbo.DD_Collecting_Providers_All_temp.JobsiteCode NOT LIKE 'EC%') AND
(dbo.CWDSPrtcpntProjectInfo.CDE_PROJ NOT LIKE 'WS%0') AND (dbo.DD_Collecting_Providers_All_temp.JobsiteCode NOT LIKE '%-NC-%')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 09:12:03

is the view developed by you or is it an inherited one? whats it basically doing?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

im1dermike
Posting Yak Master

222 Posts

Posted - 2010-03-03 : 09:18:29

Developed by me. It just maps data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources