2015-01-13

Replace XML data in SQL Server

Using the XML modify method is the most efficient way to modify XML data stored in xml type variable or column. This method takes an XML DML statement to insert, replace, or delete nodes from XML data. Here is an example of using XML DML to replace the value of an attribute in XML documents stored in a xml type column.

DECLARE @old_tkr nvarchar(35), @new_tkr nvarchar(35)
-- SET @old_tkr = 'ABC'
-- SET @new_tkr = 'XYZ'
;WITH XMLNAMESPACES(DEFAULT 'http://schemas.guosen.com.hk/sqlserver/2014/04/guosen-archive/AnalystReportMetadata')
UPDATE AnalystReportMetaData SET
metadataXml.modify('replace value of (/analystreport/stocks/stock[@code=sql:variable("@old_tkr")]/@code)[1] with sql:variable("@new_tkr")')
WHERE metadataXml.exist('/analystreport/stocks/stock[@code=sql:variable("@old_tkr")]') = 1

Sample XML:
<analystreport xmlns="http://schemas.guosen.com.hk/sqlserver/2014/04/guosen-archive/AnalystReportMetadata">
  <stocks>
    <stock guosenNum="1617" code="1302 HK" countryCode="CN" marketCode="HK"></stock>
  </stocks>
</analystreport>

2015-01-05

Linked Server options: RPC and RPC Out

A linked server is a mechanism that allows a query to be submitted on one server and then have all or part of the query redirected and processed on another SQL Server instance, and eventually have the results set sent back to the original server to be returned to the client. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed. In order to enable remote procedure call, you must enable the relevant linked server option.
By the way, there're two options with similar names, RPC, and RPC Out. Do you need both of them or only one of them set to True?
The first RPC setting is mainly for a legacy feature called Remote Server. You probably will NOT be using remote servers in SQL Server 2005 -SQL Server 2014 versions. Its default is false.
The second RPC Out setting is very relevant to linked servers on SQL Server 2005 -SQL Server 2014 versions. It enables remote procedure call to the linked server instance. Its default is false.
You can try to create a linked server, then run the following remote procedure call:
EXEC [linkedserver].master.dbo.sp_helpdb
These kind of remote stored procedure calls will be blocked unless RPC OUT is set to True.
Msg 7411, Level 16, State 1, Line 1
Server 'linkedserver' is not configured for RPC.

You can set it to True in the linked server's properties (a right click menu in from the Linked Server Name in SQL Server Management Studio).

Then you should able to run the remote procedure call now.
Summary:
1. The RPC option is NOT relevant, so just keep it set to false.
2. The RPC Out option is required for enabling remote procedure call, so set it to true if required.