none
select from a linked server fails when executed as a join to database tables

    Question

  • Hi

    I have been using the following TSQL to update a results table for several years it is currently running on SQL Express version 11.0.2100.60 (I have successfully run this on various versions and editions from 2005 onwards)

    select c.Race_id , r.Runner_id , i.Time_secs
      from CHAMPS INPUT]...Results I
      join  [dbo].[Race] c
      on rtrim(I.Race)COLLATE SQL_Latin1_General_CP1_CI_AS
      = rtrim(c.Race_name)COLLATE SQL_Latin1_General_CP1_CI_AS
      join  [dbo].[Runner] r
      on rtrim(I.[Name]) COLLATE SQL_Latin1_General_CP1_CI_AS
      = rtrim(r.First_Name)COLLATE SQL_Latin1_General_CP1_CI_AS + ' '
      + rtrim(r.Surname)COLLATE SQL_Latin1_General_CP1_CI_AS
     where i.Time_secs > 0

    This worked earlier today then with no obvious change stopped working

    No error is given but no rows are returned

    running select * from [CHAMPS INPUT]...Results where Time_secs > 0 returns the expected rows

    Any ideas where to look for the problem

     

    Sunday, July 13, 2014 6:36 PM

Answers

  • Erland thanks for your reply. I have resolved the issue, it was caused by an error in updating the joined race table. I was looking for a problem with the linked server and missing the obvious.

    Hi philpits,

    You can try to use SQL Profiler to capture some events while run your query.

    In addition, can you get expected result while run the query on remote server? Please also check your underlying tables.

    Regards,


    Elvis Long
    TechNet Community Support

    Thursday, July 17, 2014 9:35 AM
    Moderator

All replies

  • So for whatever reason there are no rows to return today. Why? How could we tell, since we don't know your tables?

    What I can say is that you posted is not syntactically correct (unbalanced right bracket on line 2), so we don't even know what your queries look like.

    I suggest that you play with the query to see what may cause the missing rows. For instance, make the inner joins into left joins (one at a time) and add the join columns to the SELECT list and study the data.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, July 13, 2014 6:51 PM
  • Erland thanks for your reply. I have resolved the issue, it was caused by an error in updating the joined race table. I was looking for a problem with the linked server and missing the obvious.

    Sunday, July 13, 2014 10:56 PM
  • Erland thanks for your reply. I have resolved the issue, it was caused by an error in updating the joined race table. I was looking for a problem with the linked server and missing the obvious.

    Hi philpits,

    You can try to use SQL Profiler to capture some events while run your query.

    In addition, can you get expected result while run the query on remote server? Please also check your underlying tables.

    Regards,


    Elvis Long
    TechNet Community Support

    Thursday, July 17, 2014 9:35 AM
    Moderator
  • Hi philpits

    Please close the tread if this issue solved :-)


    [Personal Site] [Blog] [Facebook]signature

    Thursday, July 17, 2014 9:45 AM
    Moderator