sys.sysservers table

Question

sys.sysservers table

control

SSCommitted

Points: 1630
More actions
March 10, 2009 at 7:23 am

#97357

When I query the system table sys.sysservers, two result is displayed in srvstatus
column for 2 different servers. One is 1217 and 1089.
Can somebody tell me different between srvstatus 1217 and 1089?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

http://msdn.microsoft.com/en-us/library/ms187997(SQL.90).aspx

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Better use sys.servers

Please post 2005 questions in SQL 2005 forum.

MJ

control SSCommitted Points: 1630 More actions · Answer 2

control

SSCommitted

Points: 1630

March 10, 2009 at 8:34 am

#956828

Thanks. What is status 1217?

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

It seems to be used for internal use only.

http://msdn.microsoft.com/en-us/library/aa260585(SQL.80).aspx

srvstatus smallint For internal use only.

MJ

Lowell SSC Guru Points: 323518 More actions · Answer 4

i mapped this out for fun; i reversed engineered by comparing the values to all my serves(8 total in my db) for the first 10 columns, the values seem to map to the bits:

[font="Courier New"]srvstatus value: 1217

RPC True

PUBLISHER False

SUBSCRIBER False

DIST False

DPUB False

RPCOUT True

DATAACCESS True

COLLATIONCOMPATIBLE False

SYSTEM False

USE REMOTE COLLATION True

LAZYSCHEMAVALIDATION False[/font]

here's the code:

[font="Courier New"]

SELECT 'name: ' + [srvname] + CHAR(13) +

'RPC '+ MIN(CASE srvstatus & 1 WHEN 1 THEN 'True' ELSE 'False' END) + CHAR(13) +

'PUBLISHER '+ MIN(CASE srvstatus & 4 WHEN 4 THEN 'True' ELSE 'False' END) + CHAR(13) +

'SUBSCRIBER '+ MIN(CASE srvstatus & 8 WHEN 8 THEN 'True' ELSE 'False' END) + CHAR(13) +

'DIST '+ MIN(CASE srvstatus & 16 WHEN 16 THEN 'True' ELSE 'False' END) + CHAR(13) +

'DPUB '+ MIN(CASE srvstatus & 32 WHEN 32 THEN 'True' ELSE 'False' END) + CHAR(13) +

'RPCOUT '+ MIN(CASE srvstatus & 64 WHEN 64 THEN 'True' ELSE 'False' END) + CHAR(13) +

'DATAACCESS '+ MIN(CASE srvstatus & 128 WHEN 128 THEN 'True' ELSE 'False' END) + CHAR(13) +

'COLLATIONCOMPATIBLE '+ MIN(CASE srvstatus & 256 WHEN 256 THEN 'True' ELSE 'False' END) + CHAR(13) +

'SYSTEM '+ MIN(CASE srvstatus & 512 WHEN 512 THEN 'True' ELSE 'False' END) + CHAR(13) +

'USE REMOTE COLLATION '+ MIN(CASE srvstatus & 1024 WHEN 1024 THEN 'True' ELSE 'False' END) + CHAR(13) +

'LAZYSCHEMAVALIDATION '+ MIN(CASE srvstatus & 2048 WHEN 2048 THEN 'True' ELSE 'False' END) + CHAR(13) +

'bit 12 '+ MIN(CASE srvstatus & 4096 WHEN 4096 THEN 'True' ELSE 'False' END) + CHAR(13) +

'bit 13 '+ MIN(CASE srvstatus & 32768 WHEN 32768 THEN 'True' ELSE 'False' END) + CHAR(13) +

'bit 14 '+ MIN(CASE srvstatus & 65536 WHEN 65536 THEN 'True' ELSE 'False' END) + CHAR(13) + CHAR(13)

FROM sys.sysservers

GROUP BY [srvname][/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Michael-401546 Mr or Mrs. 500 Points: 557 More actions · Answer 5

I hope this is not to late. This code gives me an error

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near ';'.

I know I'm missing something. Can you please correct me?

SELECT 'name: ' + [srvname] + CHAR(13) +

'RPC '+ MIN(CASE srvstatus & 1 WHEN 1 THEN 'True' ELSE 'False' END) + CHAR(13) +

'PUBLISHER '+ MIN(CASE srvstatus & 4 WHEN 4 THEN 'True' ELSE 'False' END) + CHAR(13) +

'SUBSCRIBER '+ MIN(CASE srvstatus & 8 WHEN 8 THEN 'True' ELSE 'False' END) + CHAR(13) +

'DIST '+ MIN(CASE srvstatus & 16 WHEN 16 THEN 'True' ELSE 'False' END) + CHAR(13) +

'DPUB '+ MIN(CASE srvstatus & 32 WHEN 32 THEN 'True' ELSE 'False' END) + CHAR(13) +

'RPCOUT '+ MIN(CASE srvstatus & 64 WHEN 64 THEN 'True' ELSE 'False' END) + CHAR(13) +

'DATAACCESS '+ MIN(CASE srvstatus & 128 WHEN 128 THEN 'True' ELSE 'False' END) + CHAR(13) +

'COLLATIONCOMPATIBLE '+ MIN(CASE srvstatus & 256 WHEN 256 THEN 'True' ELSE 'False' END) + CHAR(13) +

'SYSTEM '+ MIN(CASE srvstatus & 512 WHEN 512 THEN 'True' ELSE 'False' END) + CHAR(13) +

'USE REMOTE COLLATION '+ MIN(CASE srvstatus & 1024 WHEN 1024 THEN 'True' ELSE 'False' END) + CHAR(13) +

'LAZYSCHEMAVALIDATION '+ MIN(CASE srvstatus & 2048 WHEN 2048 THEN 'True' ELSE 'False' END) + CHAR(13) +

'bit 12 '+ MIN(CASE srvstatus & 4096 WHEN 4096 THEN 'True' ELSE 'False' END) + CHAR(13) +

'bit 13 '+ MIN(CASE srvstatus & 32768 WHEN 32768 THEN 'True' ELSE 'False' END) + CHAR(13) +

'bit 14 '+ MIN(CASE srvstatus & 65536 WHEN 65536 THEN 'True' ELSE 'False' END) + CHAR(13) + CHAR(13)

FROM sys.sysservers

GROUP BY [srvname]

Michael-401546 Mr or Mrs. 500 Points: 557 More actions · Answer 6