Re: Lost ability to retrieve the schemes from MSSQL once we replaced our MSSQL 2008 R2 version with 2017 version
Hi Milosz,
Thank you for looking into my case.
Yes, query SELECT * FROM sys.databases does return 11 rows, essentially showing
all existing databases.
Here is the output in unformatted TEXT:
SELECT * FROM sys.databases
name,database_id,source_database_id,owner_sid,create_date,compatibility_level,collation_name,user_access,user_access_desc,is_read_only,is_auto_close_on,is_auto_shrink_on,state,state_desc,is_in_standby,is_cleanly_shutdown,is_supplemental_logging_enabled,snapshot_isolation_state,snapshot_isolation_state_desc,is_read_committed_snapshot_on,recovery_model,recovery_model_desc,page_verify_option,page_verify_option_desc,is_auto_create_stats_on,is_auto_create_stats_incremental_on,is_auto_update_stats_on,is_auto_update_stats_async_on,is_ansi_null_default_on,is_ansi_nulls_on,is_ansi_padding_on,is_ansi_warnings_on,is_arithabort_on,is_concat_null_yields_null_on,is_numeric_roundabort_on,is_quoted_identifier_on,is_recursive_triggers_on,is_cursor_close_on_commit_on,is_local_cursor_default,is_fulltext_enabled,is_trustworthy_on,is_db_chaining_on,is_parameterization_forced,is_master_key_encrypted_by_server,is_query_store_on,is_published,is_subscribed,is_merge_published,is_distributor,is_sync_with_backup,service_broker_guid,is_broker_enabled,log_reuse_wait,log_reuse_wait_desc,is_date_correlation_on,is_cdc_enabled,is_encrypted,is_honor_broker_priority_on,replica_id,group_database_id,resource_pool_id,default_language_lcid,default_language_name,default_fulltext_language_lcid,default_fulltext_language_name,is_nested_triggers_on,is_transform_noise_words_on,two_digit_year_cutoff,containment,containment_desc,target_recovery_time_in_seconds,delayed_durability,delayed_durability_desc,is_memory_optimized_elevate_to_snapshot_on,is_federation_member,is_remote_data_archive_enabled,is_mixed_page_allocation_on,is_temporal_history_retention_enabled
master,1,NULL,0x01,2003-04-08 09:13:36.390,140,SQL_Latin1_General_CP1_CI_AS,0,MULTI_USER,0,0,0,0,ONLINE,0,0,0,1,ON,0,3,SIMPLE,2,CHECKSUM,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,00000000-0000-0000-0000-000000000000,0,0,NOTHING,0,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NONE,0,0,DISABLED,0,0,0,1,0
tempdb,2,NULL,0x01,2018-01-18 17:05:37.607,140,SQL_Latin1_General_CP1_CI_AS,0,MULTI_USER,0,0,0,0,ONLINE,0,0,0,0,OFF,0,3,SIMPLE,2,CHECKSUM,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,C702C437-FAE8-4F99-97B9-7C05AF9A7E64,1,0,NOTHING,0,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NONE,60,0,DISABLED,0,0,0,0,0
model,3,NULL,0x01,2003-04-08 09:13:36.390,140,SQL_Latin1_General_CP1_CI_AS,0,MULTI_USER,0,0,0,0,ONLINE,0,0,0,0,OFF,1,3,SIMPLE,2,CHECKSUM,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,00000000-0000-0000-0000-000000000000,0,0,NOTHING,0,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NONE,60,0,DISABLED,0,0,0,1,0
msdb,4,NULL,0x01,2017-08-22 19:39:22.887,130,SQL_Latin1_General_CP1_CI_AS,0,MULTI_USER,0,0,0,0,ONLINE,0,0,0,1,ON,0,3,SIMPLE,2,CHECKSUM,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,0,0,0,0,0,0,0,0,FC836A18-610D-41E5-9E43-6DC25CBAEA0A,1,0,NOTHING,0,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NONE,60,0,DISABLED,0,0,0,1,1
artfactDB2,5,NULL,0x01,2018-01-03 09:38:12.300,140,SQL_Latin1_General_CP1_CI_AS,0,MULTI_USER,0,0,0,0,ONLINE,0,0,0,0,OFF,1,1,FULL,2,CHECKSUM,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,0,0,0,0,9F80895F-8982-41B5-9B87-A08B268BEA7A,0,0,NOTHING,0,0,0,0,B4B26ACA-22B8-4115-9A07-76182DA9CA13,51D64CE5-9C3C-412F-836C-698DA5AFB3F9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NONE,0,0,DISABLED,0,0,0,0,0
analysisDB,6,NULL,0x01050000000000051500000088B648436ACE948225BD2BB321160000,2018-01-03 09:41:42.597,140,SQL_Latin1_General_CP1_CI_AS,0,MULTI_USER,0,0,0,0,ONLINE,0,0,0,0,OFF,1,1,FULL,2,CHECKSUM,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,0,F258ECF6-4795-4FE6-A048-CFDD7A06EA21,0,0,NOTHING,0,0,0,0,B4B26ACA-22B8-4115-9A07-76182DA9CA13,A2EE74A6-8BCA-4560-BFB8-59762B94FF70,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NONE,0,0,DISABLED,0,0,0,0,0
DBA,7,NULL,0x01050000000000051500000088B648436ACE948225BD2BB321160000,2017-11-29 11:15:17.740,140,SQL_Latin1_General_CP1_CI_AS,0,MULTI_USER,0,0,0,0,ONLINE,0,0,0,0,OFF,1,3,SIMPLE,2,CHECKSUM,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,2B2A8D27-9662-48C1-835A-2EA0239D0271,0,0,NOTHING,0,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NONE,0,0,DISABLED,0,0,0,0,0
dataArchive,8,NULL,0x01,2018-01-03 09:44:54.083,140,SQL_Latin1_General_CP1_CI_AS,0,MULTI_USER,0,0,0,0,ONLINE,0,0,0,0,OFF,1,1,FULL,2,CHECKSUM,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,EDD39A3D-8C71-482F-826C-3E80D6664805,0,0,NOTHING,0,0,0,0,B4B26ACA-22B8-4115-9A07-76182DA9CA13,1EFF1FD0-DBA7-4E79-B91B-D8B096878677,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NONE,0,0,DISABLED,0,0,0,0,0
packageDB,9,NULL,0x01050000000000051500000088B648436ACE948225BD2BB321160000,2018-01-03 09:56:02.770,140,SQL_Latin1_General_CP1_CI_AS,0,MULTI_USER,0,0,0,0,ONLINE,0,0,0,0,OFF,1,1,FULL,2,CHECKSUM,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,0,0,0,0,46D0898A-D16F-4BE1-BD4E-32F15B50C6E3,0,0,NOTHING,0,0,0,0,B4B26ACA-22B8-4115-9A07-76182DA9CA13,58028B44-8227-4274-8395-A414D554A410,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NONE,0,0,DISABLED,0,0,0,0,0
rfcAnalysis,10,NULL,0x01,2018-01-03 09:58:19.260,140,SQL_Latin1_General_CP1_CI_AS,0,MULTI_USER,0,0,0,0,ONLINE,0,0,0,0,OFF,1,1,FULL,2,CHECKSUM,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,10BDA3CA-BDAF-49FF-9818-CA8418C7DBB9,0,0,NOTHING,0,0,0,0,B4B26ACA-22B8-4115-9A07-76182DA9CA13,1FE8C37C-ECDA-45F9-BBA3-A7D9E0256072,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NONE,0,0,DISABLED,0,0,0,0,0
CoreDB_placeholder,11,NULL,0x01,2017-12-21 11:47:57.910,140,SQL_Latin1_General_CP1_CI_AS,0,MULTI_USER,0,0,0,0,ONLINE,0,0,0,0,OFF,1,1,FULL,2,CHECKSUM,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,0,C3516CD3-2A3D-479B-87B1-539AF6919E81,0,0,NOTHING,0,0,0,0,B4B26ACA-22B8-4115-9A07-76182DA9CA13,101C5D28-586D-4309-94FF-13DCAF1676ED,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NONE,60,0,DISABLED,0,0,0,0,1
(11 rows affected)
Best regards,
Jacob Nikom