Incorrect Temp table definition

PawJ

In the latest version (i dont know if it always was like this) 10.11.2.26629 of SQL Promt, i get incorrect temp table definition when i use this code:

USE [distribution];
DROP TABLE IF EXISTS #test_table
SELECT
	 CONVERT(NVARCHAR(128), SERVERPROPERTY('MachineName')) AS [server_name]
	,pub.[publication_id]
	,pub.[publication_type]
	,CASE pub.[publication_type] WHEN 2 THEN 'Merge' WHEN 1 THEN 'Snapshot' ELSE 'Transactional' END AS [publication_type_desc]
	,pub.[thirdparty_flag] AS [publication_thirdparty_flag]
	,CASE sub.[sync_type] WHEN 1 THEN 'Non SQL Server' ELSE 'SQL Server' END AS [publication_thirdparty_flag_desc]
	,pub.[sync_method] AS [publication_sync_method] 
	,CASE sub.[sync_type] WHEN 4 THEN 'Concurrent_c' WHEN 3 THEN 'Concurrent' WHEN 2 THEN 'Character' WHEN 1 THEN 'Native' ELSE 'ERROR' END AS [sync_method_desc]
	
	,sub.[subscriber_id]
	,sub.[subscription_type]																		AS [subscription_type]
	,CASE sub.[subscription_type] WHEN 2 THEN 'Anonymous' WHEN 1 THEN 'Pull' ELSE 'Push' END		AS [subscription_type_desc]
	,sub.[sync_type]																				AS [subscription_sync_type]
	,CASE sub.[sync_type] WHEN 2 THEN 'No synchronization' WHEN 1 THEN 'Automatic' ELSE 'ERROR' END	AS [subscription_sync_type_desc]
	,sub.[status]																					AS [subscription_status]
	,CASE sub.[status] WHEN 2 THEN 'Active' WHEN 1 THEN 'Subscribed' ELSE 'Inactive' END			AS [subscription_status_desc]
	,sub.[update_mode]																				AS [subscription_update_mode]
	,sub.subscription_time

	,distagt.[subscriber_security_mode] AS [distribution_agen_subscriber_security_mode]
	,CASE distagt.[subscriber_security_mode] WHEN 0 THEN 'SQL Server Authentication' ELSE 'Windows Authentication' END AS [distribution_agen_subscriber_security_mode_desc]
	,distagt.[job_id] AS [distribution_agent_job_id]
	,distagt.[name] AS [distribution_agent_job_name]
	,distagt.[creation_date] AS [distribution_agent_creation_date]

	,pub.publication	AS publication_name
	,pubser.srvname		AS publication_server
	,art.publisher_db	AS publication_database
	,art.source_owner	AS publication_schema
	,art.article		AS publication_table_name
	
	,subser.srvname										AS subscription_server_name
	,sub.subscriber_db									AS subscription_database_name
	,ISNULL(art.destination_owner, art.source_owner)	AS subscription_schema_name
	,art.destination_object								AS subscription_table_name
INTO #test_table
FROM
		 [distribution].[dbo].[MSarticles] art					WITH(NOLOCK) 
	JOIN [distribution].[dbo].[MSpublications] pub				WITH(NOLOCK) ON art.publication_id = pub.publication_id
	JOIN [distribution].[dbo].[MSsubscriptions] sub				WITH(NOLOCK) ON pub.publication_id = sub.publication_id
	JOIN [distribution].[dbo].[MSreplservers] subser			WITH(NOLOCK) ON sub.subscriber_id = subser.srvid
	JOIN [distribution].[dbo].[MSreplservers] pubser			WITH(NOLOCK) ON pubser.srvid = pub.publisher_id
	JOIN [distribution].[dbo].[MSdistribution_agents] distagt 	WITH(NOLOCK) ON distagt.publisher_id = pub.publisher_id AND distagt.subscriber_id = sub.subscriber_id

When I [TAB] the star, this is all i get:

SELECT [server_name]
	  ,[publication_id]
	  ,[publication_type_desc]
	  ,[publication_thirdparty_flag_desc]
	  ,[sync_method_desc]
	  ,[subscription_type_desc]
	  ,[subscription_sync_type_desc]
	  ,[subscription_status_desc]
	  ,[distribution_agen_subscriber_security_mode_desc]
	  ,[publication_database]
	  ,[publication_schema]
	  ,[publication_table_name]
	  ,[subscription_schema_name]
	  ,[subscription_table_name] 
FROM #test_table

As you can see we are missing a lot of columns.

Jon_Kirkwood

Hi @PawJ

Thank you for reaching out on the Redgate forums regarding your SQL Prompt auto-suggestion concern.

I replicated your code and was able to see the same results - only getting a few suggestions in the code-behind view & wild card expansion.

I tried two setting changes which appears to have resolved it in my testing:

1 - Assign aliases

SQL Prompt Options > Aliases > Assign Aliases & Include AS in alias definition

​

2 - Refresh suggestions

SQL Prompt > Refresh Suggestions

Once I had done both of these I saw extra columns in my #test_table script and when expanding the wild card

​ 

Are you able to check and verify if either of these options work for you too?

PawJ

Hey Jon

Thanks for the reply, sadly im not able to fix it with your'e suggestion, this is what i get



I do the refresh (also tried to restart SSMS)

But still I only get this

Jon_Kirkwood

Sorry for the delay in response, I have been trying to trigger this in other ways to see if I can isolate a precise cause. Sadly the only time I was getting the test_table to display the partial fields was when creating new aliases and not refreshing my SMSS session.

This issue was escalated to our development team and the behaviour around this code may change in a future release, sadly I don't have a timeframe that I can provide around this. 

Sorry that we were not able to hit a direct solution in this instance and hopefully there is a solution incorporated into a future release that displays the code as expected.