Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    51

    Exclamation Unanswered: strange but interesting error

    I have a stored proc which works for few execution (all with same parameters), but after some time gives run time error ' Un ambigous column '. If we recompile the proc, it again starts working.. Any clue what could be happening?

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Do you have any SQL code we can look at? Are all the tables the sproc references static ie they haven't been rebuilt in the last few days?

  3. #3
    Join Date
    Jan 2004
    Posts
    51
    Unfortunately I can't share the code.. The tables are transactional tables, the structure has not changed (at least since proc was created), data in the table has changed (increased).

    Strange thing is if we recompile, the code executes. After some time, it starts throwing above error. I used sp_showplan, it does not even show the plan...

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    The optimiser will occasionally rebuild the plan for a stored proc. If the tables are transaction tables then they might well be empty at some times and full of data at other times. This might affect the plan that the optimiser chooses. Though why it's actually coming up with this error can only be solved by providing us with some code and table definitions.

    Perhaps you could snapshot the tables before calling the proc. Also store what, if any, parameters are being passed to the proc. When the proc fails then see if you can run a copy of the proc but on your snapshot tables. This might give you a better chance of reproducing the error.

    Good luck

    EDIT: you could also try forcing the optimiser to choose a certain path each time within the stored proc. I don't like this approach but it might work.
    Last edited by mike_bike_kite; 12-16-09 at 20:53.

  5. #5
    Join Date
    Jan 2004
    Posts
    51
    Thank you for suggestions.

Tags for this Thread

Posting Permissions

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