Maximize the power of SQL Prompt with SQL Toolbelt Essentials. Watch now.

Incorrect Temp table definition

PawJPawJ Posts: 5 New member
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.
Sign In or Register to comment.