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_idWhen 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.
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_idWhen I [TAB] the star, this is all i get:
As you can see we are missing a lot of columns.