{"id":120,"date":"2012-12-29T12:51:43","date_gmt":"2012-12-29T12:51:43","guid":{"rendered":"http:\/\/www.theSQLReport.com\/?p=120"},"modified":"2012-12-29T13:01:23","modified_gmt":"2012-12-29T13:01:23","slug":"server-servername1-is-not-configured-for-rpc-for-a-linked-server","status":"publish","type":"post","link":"https:\/\/www.theSQLReport.com\/?p=120","title":{"rendered":"Server ‘SERVERNAME1’ is not configured for RPC for a Linked Server"},"content":{"rendered":"

Once in while I am asked to troubleshoot a SQL Server database where my only connection is though a linked server.\u00a0 Because this database server is on protected network, I don\u2019t have port 1433 open to connect the instance with SQL Server Management Studio.<\/p>\n

There are a couple of commands I like to run to check the health of the database.<\/p>\n

First, already knowing that the database is running, I like to look at the error log with the xp_readerrorlog extended stored procedure.<\/p>\n

When I run a simple command to view the error log from my linked server:<\/p>\n

exec SERVERNAME1.master.sys.xp_readerrorlog<\/span><\/p>\n

Note: replace SERVERNAME1 with the name of your linked server<\/p>\n

I get the following error:<\/p>\n

Msg 7411, Level 16, State 1, Line 1<\/span><\/p>\n

Server ‘SERVERNAME1’ is not configured for RPC.<\/span><\/p>\n

\"\"<\/a><\/p>\n

This means that when I set up my linked server, I disable RPC commands to and from running for security reasons.<\/p>\n

To re-enable the RCP commands for the linked server:<\/p>\n

exec sp_serveroption @server=’SERVERNAME1′, @optname=’rpc’, @optvalue=’true’<\/span><\/p>\n

exec sp_serveroption @server=’SERVERNAME1′, @optname=’rpc out’, @optvalue=’true’<\/span><\/p>\n

Note: again replace SERVERNAME1 with the name of your linked server<\/p>\n

————————————————————————————–<\/p>\n

Once RPC is enabled, there are a couple of other command I like to run to find out the health of the SQL instance & its databases.<\/p>\n

— This extended stored procedure displays operating system information regarding local attached disks:<\/p>\n

exec SERVERNAME1.master.dbo.xp_fixeddrives<\/span><\/p>\n

— This\u00a0stored procedure displays the size of the database and how that space is currently allocated.<\/p>\n

exec SERVERNAME1.mydb.dbo.sp_spaceused<\/span><\/p>\n

— You can run DBCC CHECKDB and other DBCC commands over a linked server connection:<\/p>\n

EXEC SERVERNAME1.mydb.dbo.sp_executesql N’DBCC CHECKDB’<\/span><\/p>\n

Note: Again replace SERVERNAME1 with the name of your linked server & mydb with the name of your database<\/p>\n

————————————————————————————–<\/p>\n

On a final note, remember to disable RPC when your done as a security precaution:<\/p>\n

exec sp_serveroption @server=’SERVERNAME1′, @optname=’rpc’, @optvalue=’false’<\/span><\/p>\n

exec sp_serveroption @server=’SERVERNAME1′, @optname=’rpc out’, @optvalue=’false’<\/span><\/p>\n

Note: again replace SERVERNAME1 with the name of your linked server<\/p>\n","protected":false},"excerpt":{"rendered":"

Once in while I am asked to troubleshoot a SQL Server database where my only connection is though a linked server.\u00a0 Because this database server is on protected network, I … Continue Reading →<\/a><\/p>\n","protected":false},"author":1,"featured_media":121,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-120","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server"],"_links":{"self":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/120"}],"collection":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=120"}],"version-history":[{"count":19,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/120\/revisions"}],"predecessor-version":[{"id":482,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/120\/revisions\/482"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/media\/121"}],"wp:attachment":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=120"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=120"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=120"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}