What are the challenges you face when working across database platforms? Take the survey
Options

Find Invalid Objects not detecting ambiguous column in stored procedure

Using the latest SQLPrompt I have a SPROC with a query joining two tables. The SPROC is not valid in SSMS when trying to alter it with an error message

Msg 209, Level 16, State 1, Procedure GetData, Line 313 [Batch Start Line 7]
Ambiguous column name 'Guid'.

When trying to find this SPROC using "Find Invalid Objects" it shows all SPROCs are valid

Any ideas?
Dan Avni

Answers

  • Options

    Hi @DanAvni

    Thanks for reaching out over the Redgate forums regarding your SQL Prompt query.

    I have hit this myself with the Invalid Object finder not returning some expected results. 

    From my understanding, the check will look at code that won't compile and also code that compiles but doesn't execute.

    Ambiguous columns can sometimes fall through the cracks as the system makes assumptions about columns and that at the point of checking they do not fail.

    It may be possible to tweak a couple of settings in Prompt to format code in a way to potentially pick up on these ambiguous fields


    Options > Format > Styles > Qualify Object Names

    Options > Inserted Code > Qualification > Qualify column names with table name

    If these are enabled when you apply a code format it can add extra details to the columns which may help reduce ambiguity in stored processes, triggers etc



    Jon Kirkwood | Technical Support Engineer | Redgate Software
  • Options
    DanAvniDanAvni Posts: 72 Bronze 2
    @Jon_Kirkwood, Many thanks for your answer but in my case the SPROC is not compilable when opening it for alter. It is true that fully qualified names might solve it but the purpose is to find invalid existing objects and not re-write everyting only to be able to use the tool.

    I am not sure how it was implemented but if the tool would have scripted the object for create and try to create it under a temp name, it would fail and this could be detectable from code.
    Dan Avni
Sign In or Register to comment.