Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Unanswered: A Mystery : No Error returned

    Ok, here's the scenario ....

    There is an extract which has been failing for the past few days in the sense that it does not return all the rows but only a small amount out of the source. This extract does not throw any error and is running from an OPENROWSET query.

    I am suspecting something with SQL Server , (
    Microsoft SQL Server 2005 - 9.00.3042.00 (X64) -
    Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1))

    Anybody else experienced the same or Have any idea what could be causing it ..
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK.
    So, not a syntax issue.
    Not a permissions issue.

    That leaves either an SQL Logic problem, or a connection issue that is limiting the amount of data returned.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    what's the data source? SQL Server or something else? If it is SQL Server, Have you tried a linked server with a NO LOCK hint maybe? Is the data locked or uncommitted at the other end?

    I love blind chess.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Source is another SQL Server : The queries are through an Stored Proc API that automatically uses a NOLOCK hint and check for few other permissions. No SQL logic problem either, problem has occured for only about 3 times till now in the last month. The extract worked fine the rest of the times

    Any connection issue would leave an error, but in this case no error is there.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Blind Chess ??? I am feeling like I am playing soccer blindfolded. The playing field is much much larger.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, you could remove the TOP (n) clause...just kidding. Welcome back, Enigma.

    As to the problem, though, do you know of any process that deletes a lot of data about the same time as the extract runs? The nolock hint will play royal havoc with such a process.

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Was i gone ??? ... I was just lurking around ...

    Well the problem is that since the extract process returns fewer rows ... a lot of data is deleted. ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You may not need to delete millions, or even hundreds of rows to get this kind of effect, depending on how the joins fall out. By the way, how many rows out of how many expected rows is "fewer"? Are we talking about less than 1% of the rows required being returned, or one or two less?

  9. #9
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    fewer = less than 1% of the rows ....

    something like 100 -1000 records out of 20-30 million
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  10. #10
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    btw .. havent seen brett around ... where has he been lately ?
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I think he got his feelings hurt one day and ran off, but I do not recall how. From what I hear he does not post at SQLTeam anymore either.

    Something has to be jacking your data on the other end. Some scheduled refresh perhaps.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •