Why Unresolved alias is not issue?

kknyazevkknyazev Posts: 10 New member
Hello! I have trial of SQL Prompt and investigating it about Find invalid Objects behavior. I have big database with many procedures. After finding invalid objects i have discovered that "Unresolved alias" error is not displayed in results, although its showed in hint on mouse hover.

Answers

  • Jessica RJessica R Posts: 1,178 Rose Gold 4

    Hi and thanks for your post!

    The "unresolved alias" tooltip comes from SQL Prompt and indicates that our parser wasn't able to find the definition of the alias in question. 

    I suspect the definition does exist but our parser just isn't seeing it for some reason.

    You mentioned that the query is quite large. Can you kindly first double check if the alias is indeed defined elsewhere in the query? If so, is the definition quite far from the point where you are seeing the "Unresolved alias" tool tip? 

    There is a SQL Prompt setting that configures how many characters away to parse and the issue may be that the definition is more characters further than the number set.

    Could you try editing the following file in notepad:

    %localappdata%\Red Gate\SQL Prompt 10\RedGate_SqlPrompt_Engine_EngineOptions.xml

    And see if increasing the <ParserLookAheadDistance> variable to a larger value helps?

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • kknyazevkknyazev Posts: 10 New member
    Thank you for answer!
    During investigations, i found that reason is temporary table. So, SQL Prompt can not check about invalid aliases in query with temporary table :(
    select T.ID from Table1 T1 join #Table2 T2 on T2.ID = T1.ID

  • Jessica RJessica R Posts: 1,178 Rose Gold 4
    Hi @kknyazev,

    Ah I believe it won't be able to resolve the alias for a "local" temporary table like that unless #Table2 is created in the same query. Can I just double check if the #Table2 creation is in the same script?

    (If it is in the same script, then it could be an issue with  <ParserLookAheadDistance> )

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • kknyazevkknyazev Posts: 10 New member
    Yes, #Table2 is created locally in stored procedure by "select into #Table2" command
  • Jessica RJessica R Posts: 1,178 Rose Gold 4

    Thanks! In that case, is the "select into #Table2" command far from that statement,

    select T.ID from Table1 T1 join #Table2 T2 on T2.ID = T1.ID

    Have you already tried increasing the <ParserLookAheadDistance> value?

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • kknyazevkknyazev Posts: 10 New member
    I have checked - distance is 140 rows, ParserLookAheadDistance = 3000.
    Thank you for your attention!
  • Jessica RJessica R Posts: 1,178 Rose Gold 4
    No worries, @kknyazev !

    The ParserLookAheadDistance value is actually for number of characters, is it 3000 characters or less away?

    If you temporarily move the "select T.ID from Table1 T1 join #Table2 T2 on T2.ID = T1.ID" statement just under the "select into #Table2" command  statement, does it still say "unresolved alias"?

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • kknyazevkknyazev Posts: 10 New member
    Now error is no showed but alias not recognized at all (there are no any hint message), only standard SSMS warning displayed, and procedure not recognized as invalid object, although execution returns error

  • Jessica RJessica R Posts: 1,178 Rose Gold 4
    Hi @kknyazev !

    The SQL Server error takes precedent but you should still get the SQL Prompt "unresolved alias" message if SQL Server errors are disabled (Tools>Options>Text Editor>Transact SQL>Intellisense>Underline Errors).

    I'm afraid though that the Find Invalid Objects feature only works on existing objects but doesn't apply to query files. Since the InvalidProc stored procedure in your example doesn't actually get created due to the error, that's the reason why SQL Prompt doesn't show it as an invalid object.

    I hope that info helps!

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • kknyazevkknyazev Posts: 10 New member
    Of course it was created.
  • Jessica RJessica R Posts: 1,178 Rose Gold 4
    Ah apologies. if I'm understanding correctly though, I believe the object exists, but it doesn't look like the alter to include the invalid code succeeded, as it failed with the error below:



    Can you please share the current CREATE statement for InvalidProc?


    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • kknyazevkknyazev Posts: 10 New member
    It's same but have CREATE. Very strange... I don't  have error on compile on SQL Server 2017 Enterprise Edition.
  • Jessica RJessica R Posts: 1,178 Rose Gold 4
    Hi @kknyazev!

    Can I just check if the #tmp table exists in the SQL Server 2017 Enterprise server?

    You can create stored procedures that reference objects that don't exist yet (e.g. tables and functions). However, you cannot create stored procedures that reference columns that don't exist yet in objects that do already exist. (described here: https://dba.stackexchange.com/questions/24806/disabling-checking-of-schema-on-function-stored-procedure-creation)

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • kknyazevkknyazev Posts: 10 New member
    Hello! No, #tmp does not exist.
  • Jessica RJessica R Posts: 1,178 Rose Gold 4
    Ah, thanks @kknyazev! That explains why it was able to compile on that SQL Server. But just as SQL Server allows the code when the table doesn't exist, SQL Prompt doesn't flag it as invalid.

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • kknyazevkknyazev Posts: 10 New member
    It is not correct, i am talking about invalid alias for temporary table. And i just suggest to think about implementing this functionality. Recently, i have found same bug and only one tool can help with it - unit tests (tSQLt ). Thanks for your attention!
  • Jessica RJessica R Posts: 1,178 Rose Gold 4
    Hi @kknyazev!

    I'm so sorry for the delay in reply!

    Re-reading this I'm not sure I am understanding correctly, so I just want to double check if my understanding below is correct.

    You create the proc:
    CREATE OR ALTER PROC [dbo].[myinvalidproc]

    Although  t1.Name is invalid, SQL Server still allows the proc to be created because none of the referenced objects have actually been created yet.

    Then if you run the statement below to actually create the temp table:
    SELECT 1 AS ID, 'Name' AS NameINTO #tmp
    The CREATE OR ALTER PROC [dbo].[myinvalidproc] would fail to execute because SQL Server recognizes that t1.Name is invalid. 

    If I'm understanding correctly, you would want SQL Prompt to be able to determine that as well, and then list myinvalidproc as an invalid object?

    (I unfortunately don't think this would be possible though because the temp table #tmp only exists within the scope of the session - but please let me know if I'm still misunderstanding!)

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • kknyazevkknyazev Posts: 10 New member
    Yes. The most surprising is that in sql 
    select	T.ID, T2.Name from #Tmp2 T
    
    Invalid only alias, not table name or even column name! 
Sign In or Register to comment.