How to check if MS SQL Server, Linked Server object is in use

Linked server objects allow one SQL server to connect to another at the database engine level. A connection is defined when creating the linked server, with credentials that should be used for that connection. As time passes, the challenge is knowing if that old linked server object is still being used by a report or script or import somewhere, as the original reason for its existence and person responsible may be long gone.

The quickest (not easiest) method to find dependencies on a linked server is to delete the linked server and wait for the phone to ring sometime over the next year. The person ranting on the other end will help you identify what it was used for. If the linked server was involved in a complex integration orchestration, or year/month end reporting, then the fall out from this maverick approach may not be pleasant to resolve nor may the call be in social hours... Instead carefully identify, then remove or correctly document the dependencies on the linked server object.

Here a few tips to help you find those dependencies:

Look for dependencies using SQL inbuilt dependency tracking

Using script from here List All Objects Using Linked Serverinvestigate any objects it returns.

SELECT 
Distinct
referenced_Server_name As LinkedServerName,
referenced_schema_name AS LinkedServerSchema,
referenced_database_name AS LinkedServerDB,
referenced_entity_name As LinkedServerTable,
OBJECT_NAME (referencing_id) AS ObjectUsingLinkedServer
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL
And referenced_Server_name = 'Enter LinkedServerName here'

Script out objects

In built dependency checking does not work if the dependency is “hidden” in a dynamically generated (sp_executesql) SQL statement or SQL jobs or is used by SQL replication subscriptions etc. If you don’t trust the dependency script above or have older SQL server version, script out all your stored procedures, views, user defined functions, SQL jobs and any other SQL objects that might reference the linked server. Then perform a text search over all the resulting scripts for the linked server name. If you find the text, in turn identify if that object is still in use. If you use notepad++ or similar advanced text editor, they provide multiple file search with regular expression support. The regular expression support is handy if the linked server name is used in other parts of the database, allowing the search to be narrowed down to text patterns likely to be a linked server.  Also do a search on any source code for applications that may be developed against that database.

Use Sql Profiler

Run SQL server Profiler for a month against the target server (on a spare machine) to see if the linked server login name appears, this is the login name of the connection set up in the linked server configuration. I recommend a month as month end scripts or monthly maintenance scripts will be caught, however this won’t catch scripts or reports that are only ran at year end or once in a while. The trick here is to make certain you change the linked server connection to use a uniquely identifiable login name, that way it is possible to use the filter option in SQL profiler to only log events from that LoginName. Changing the login name has to be a considered move as it will change the security context and may thus affect rights to the destination server objects. These approaches all have down sides, but on less complex scenarios this technique works well.

Before deleing the linked server object

In SSMS, right click the object and script out the linked server. Put the resulting create script somewhere easy to find before deleting it, this way it will be quick to put it back again should an urgent need for it occur, say at year end months later. Having the create script helps get things running and buys you time to fix the offending referencing scripts, or document them correctly if it is decided they should exist after all.

Update all XML element nodes in an XML field of a SQL table

I wrote an electronic invoicing solution for Dynamics GP a long time ago. The XML invoices of various formats are stored against the invoice number in the database. The column was upgraded to XML type when SQL sever started supporting it in SQL 2005. Although it is possible to cast a varchar into XML data type, it makes it simpler to work with as a native XML data type.

Recently an issue surfaced where the tax amount on the invoice lines needed to be zeroed for a set of invoices, as the XML values had been incorrectly generated. Once the invoices had been corrected (only a dozen), they could be marked for retransmission. But how to quickly and easily update the invoices?

We have been using XML functionality in SQL server for another project recently, so it seemed natural to address the issue with with the inbuilt XML functions of SQL server.

Showing an snippet of the "modify replace value of" SQL

Documents

The nodes in the document all look like this:

<core:TotalTaxAmount>
<core:MonetaryAmount>7.680</core:MonetaryAmount>
</core:TotalTaxAmount>

Whatever the value is, it needs to be zero after the update.

<core:TotalTaxAmount>
<core:MonetaryAmount>0</core:MonetaryAmount>
</core:TotalTaxAmount>
 

Modify() function

The following is a good guide to using the XML modify() statement in TSQL

 
Using this as a guide I end up with the xPath looking like this
UPDATE 
[dbo].[CA_SOP_EINVOICE_Output]
SET
XMLToSend.modify('
replace value of
(//core:TotalTaxAmount/core:MonetaryAmount[text()!="0"])[1]
with ("0")'
)
WHERE
SOPNUMBE=@SOPNUMBE AND
SOPTYPE=@SOPTYPE

This gives us a name space issue error due to the :core namespaces
XQuery [dbo.CA_SOP_EINVOICE_Output.XMLToSend.modify()]: 
The name "core" does not denote a namespace.

This is easily resolved by specifying the namespace with
;WITH XMLNAMESPACES('rrn:org.xcbl:schemas/xcbl/v4_0/core/core.xsd' AS core)

Next we get an error of
The target of 'replace value of' must be a non-metadata attribute or an 
element with simple typed content
 
This is due to the schema not defining a type for our node, so just update it as text, by adding text()
(//core:TotalTaxAmount/core:MonetaryAmount[text()!="0"]/text())[1]

Only one replace is allowed at a time, this is a real shame as it makes an otherwise trivial statement into a script. To make it work for this problem, a loop is created that will keep updating the first node it finds until all the nodes have been set to zero. This then gives us the final script to do the task as shown below:
 
DECLARE @SOPTYPE smallint
DECLARE @SOPNUMBE CHAR(21)

DECLARE @KeepLooping bit

SELECT @SOPTYPE=3, @SOPNUMBE='[Enter Doc Number]'

BEGIN TRANSACTION

-- Declare the namespace we are using before each select/update
;WITH XMLNAMESPACES('rrn:org.xcbl:schemas/xcbl/v4_0/core/core.xsd' AS core)
SELECT @KeepLooping= XMLToSend.exist('//core:TotalTaxAmount/core:MonetaryAmount!=0')
FROM [dbo].[CA_SOP_EINVOICE_Output]
WHERE
SOPNUMBE=@SOPNUMBE
AND SOPTYPE=@SOPTYPE

WHILE
(@KeepLooping=1)
BEGIN

-- Do the update on first non-matching node each time until they are all done
;WITH XMLNAMESPACES('rrn:org.xcbl:schemas/xcbl/v4_0/core/core.xsd' AS core)
UPDATE
[dbo].[CA_SOP_EINVOICE_Output]
SET
XMLToSend.modify('
replace value of
(//core:TotalTaxAmount/core:MonetaryAmount[text()!="0"]/text())[1]
with ("0")'
)
WHERE
SOPNUMBE=@SOPNUMBE AND
SOPTYPE=@SOPTYPE

;WITH XMLNAMESPACES('rrn:org.xcbl:schemas/xcbl/v4_0/core/core.xsd' AS core)
SELECT @KeepLooping=
XMLToSend.exist('//core:TotalTaxAmount/core:MonetaryAmount[text()!="0"]')
FROM [dbo].[CA_SOP_EINVOICE_Output]
WHERE
SOPNUMBE=@SOPNUMBE AND
SOPTYPE=@SOPTYPE

END
ROLLBACK TRANSACTION
 
That was another interesting dip into SQL Server support for XML!

SQL Replication duplicate log reader in SQL replication monitor

It is upgrade time, Dynamics GP does not like schema objects bound by replication during upgrades. During version updates some tables are copied dropped and recreated, depending on what changes have been made to the schema. If replication is left running on a Dynamics GP company during the upgrade, then the upgrade will fail, if the object being replicated is change during the upgrade.

As a result, SQL replication has to be removed during the upgrade, after first scripting it out to SQL as a create so that it can be rebuilt post upgrade. During a recent upgrade, after rebuilding replication there were two log readers running for each publication. Oddly looking at the properties of the SQL log readers, they both reference the same source SQL jobs. This was causing issues trying to start and stop them or trying to change credentials as it all got a bit confusing. 

This has happened to me a few times now. The following command removes the extra log reader registration.

Selecting all the readers, from the replication distribution database where they are registered, simply choose the higher numbered duplicates for deletion, then remove them with the delete SQL command, using the ID from the table. 

SELECT  
*
FROM
[{replicationdistributiondbname}]..[MSlogreader_agents]


DELETE
FROM
[{replicationdistributiondbname}]..[MSlogreader_agents]
WHERE id={duplicate ID #}
Now the table should be left with one reader per publication (unless you have a more complicated replication arrangement going on). 
 
replication monitor showing reader agents

SSMS locked / frozen after network disconnect or computer sleep

image
When both the following are true:

  • the machine is woken from sleep or the network connection has been interrupted to SQL server
  • SQL Server Managment Studio Activity Monitor was open on a tab somewhere in the editor deck in SSMS.

-then It will be no longer be possible to interact with SSMS. It will feel like the SQL Server Management Studio has crashed or has frozen.

The cause

SSMS crashing as a result of connection lost or sleep  is caused by the modal dialog indicating the loss of connection for the activity monitor popping up in a modal mode behind the application surface somewhere. You cannot see this window by right clicking on the application in the bar, but it IS there. Modal mode prevents interaction with any other windows in the applicaiton until that modal window is closed.

image

The Fix

To get up and running again, click ALT+Tab until you find the pesky dialog window, then click OK on that window to dismiss it. SSMS will spring back to life. This is a regular occurrance for me after computer sleeps and I’ve been monitoring the SQL server.