This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objects. You can find a list of all of the deprecated views in the introduction post.
The compatibility view sysservers provides information on linked servers, or servers an instance of SQL Server has OLE DB access to. There is one row in the output for every SQL Server instance that the instance can connect to.
The catalog view sys.servers provides a replacement to sysservers. Similar to the compatibility view, sys.servers returns a row for every linked server on an instance.
Status Column
Similar to many other compatibility views, sysservers has a status column. This column contains bit values that defined a number of properties for the linked server. One thing that is unique about the values in the status column for this view, is all of the values are already represented in columns within the view. The values, though, stored in the view are:
- 0×1:Is remote login enabled
- 0×2:Is publisher
- 0×4:Is subscriber
- 0×8:Is distributor
- 0×20:Is linked
- 0×40:Is rpc out enabled
- 0×80:Is collation compatible
- 0×100:Not recovered
- 0×200:Is system
- 0×400:Uses remote collation
- 0×800:Lazy schema validation
- 0×1000: Is nonsql subscriber
Query Via sysservers
Except for the status column, a query for sysservers just includes all of the columns in the view. For the status column, the values can be extracted using CASE statements. The resulting query is provided in Listing 1. A last item to note concerns the columns topologyx and topologyy, both of these columns now always return 0.
--Listing 1 – Query for sys.sysservers SELECT srvid , srvname , srvproduct , providername , datasource , location , providerstring , schemadate , topologyx , topologyy , catalog , srvcollation , connecttimeout , querytimeout , srvnetname , isremote rpc , pub , sub , dist , dpub , rpcout , dataaccess , collationcompatible , system , useremotecollation , lazyschemavalidation , collation , nonsqlsub , srvstatus , CONVERT(INT,srvstatus & 0x1) / 1 AS is_remote_login_enabled , CONVERT(INT,srvstatus & 0x2) / 2 AS is_publisher , CONVERT(INT,srvstatus & 0x4) / 4 AS is_subscriber , CONVERT(INT,srvstatus & 0x8) / 8 AS is_distributor , CONVERT(INT,srvstatus & 0x20) / 32 AS is_linked , CONVERT(INT,srvstatus & 0x40) / 64 AS is_rpc_out_enabled , CONVERT(INT,srvstatus & 0x80) / 128 AS is_data_access_enabled , CONVERT(INT,srvstatus & 0x100) / 256 AS is_collation_compatible , CONVERT(INT,srvstatus & 0x200) / 512 AS is_system , CONVERT(INT,srvstatus & 0x400) / 1024 AS uses_remote_collation , CONVERT(INT,srvstatus & 0x800) / 2048 AS lazy_schema_validation , CONVERT(INT,srvstatus & 0x1000) / 4096 AS is_nonsql_subscriber FROM sysservers
Query via sys.servers
When it comes to mapping sysservers to sys.servers, there are just a few things to look at. The first thing is most of the columns between sysservers and sys.servers have been renamed. The columns are there, but most have an expanded, more descriptive name. The other difference is the provider name where SQLNCLI has been swapped for SQLOLEDB. There’s also the is_linked column which is now the inverse of isremote. The last difference is the inclusion of the column is_remote_proc_transaction_promotion_enabled in the catalog view. In total, putting the differences together results in the query in Listing 2; which can be used to replace the use of sysservers.
--Listing 2 – Query for sys.servers SELECT server_id AS srvid ,name AS srvname ,product AS srvproduct ,CASE WHEN UPPER(provider)=N'SQLNCLI' THEN N'SQLOLEDB' ELSE provider END AS providername ,data_source AS datasource ,location ,provider_string AS providerstring ,modify_date AS schemadate ,0 AS topologyx ,0 AS topologyy ,catalog ,CONVERT(SYSNAME, COLLATIONPROPERTY(collation_name, 'collationid')) AS srvcollation ,connect_timeout AS connecttimeout ,query_timeout AS querytimeout ,CONVERT(CHAR(30), CASE WHEN (product=N'SQL Server') THEN data_source END) AS srvnetname ,CASE WHEN is_linked=1 THEN 0 ELSE 1 END AS isremote --inverse ,is_remote_login_enabled AS rpc ,is_publisher AS pub ,is_subscriber AS sub ,is_distributor AS dist ,CONVERT(VARBINARY, is_distributor&is_publisher) AS dpub ,is_rpc_out_enabled AS rpcout ,is_data_access_enabled AS dataaccess ,is_collation_compatible AS collationcompatible ,is_system AS system ,uses_remote_collation AS useremotecollation ,lazy_schema_validation AS lazyschemavalidation ,collation_name AS collation ,is_nonsql_subscriber AS nonsqlsub ,is_linked ,is_remote_proc_transaction_promotion_enabled FROM sys.servers
Summary
In this post, we compared the compatibility view sysservers with the catalog view sys.servers. The main need when transitioning between these views is to modify the expected column names. While there are a few other differences, that change is the primary difference between the two. After reading all of this, do you see any reason to continue using sysservers? Is there anything missing from this post that people continuing to use the compatibility view should know?
