Why Unresolved alias is not issue?

kknyazev

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.

Jessica R

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?

kknyazev

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 R

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> )

kknyazev

Yes, #Table2 is created locally in stored procedure by "select into #Table2" command

Jessica R

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?

kknyazev

I have checked - distance is 140 rows, ParserLookAheadDistance = 3000.
Thank you for your attention!

Jessica R

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"?

kknyazev

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 R

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!

kknyazev

Of course it was created.

Jessica R

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?

kknyazev

It's same but have CREATE. Very strange... I don't  have error on compile on SQL Server 2017 Enterprise Edition.

Jessica R

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)

kknyazev

Hello! No, #tmp does not exist.

Jessica R

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.

kknyazev

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 R

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:

<div>CREATE OR ALTER PROC [dbo].[myinvalidproc]</div><div><br>AS BEGIN</div><div><br>SELECT 1 AS ID</div><div>, 'Name' AS Name</div><div>INTO #tmp</div><div><br></div><div>SELECT t.ID, t1.Name FROM #tmp t</div><div><br></div><div>END</div>

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:

<div>SELECT 1 AS ID</div><div>, 'Name' AS Name</div><div>INTO #tmp</div>

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!)

kknyazev

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! 

Tianjiao_Li

@kknyazev

Thanks for your patience with us!

The development team has confirmed this is not a bug in the product, and we have no plan to change this behavior in the foreseeable future.

We apologies for any inconvenience this is causing you.