Feeds:
Posts
Comments

Archive for the ‘Tips and Techniques’ Category

Interesting error today:

When we query over linked server, we run into some restrictions. One such restrictions is this, where we could not query XML columns over LinkedServer connection.

For example, if the remote server table has a XML column in its structure, when we query the remote server for this table, we run this error:

--
-- Old query that throws this error
--
SELECT ID, XMLColumn
FROM LinkedServer01.DatabaseName.dbo.TableName
GO
Error : Msg 9514, Level 16, State 1, Line 1 
Xml data type is not supported in distributed queries. 
Remote object has xml column(s)

Resolution:

Not sure why this limitation exists, but there is a workaround: OPENQUERY

In the past, we’ve covered some key benefits of OPENQUERY and how it allows us to circumvent some limitations of remote-table-value function calls. Today, we’ll see another benefit of OPENQUERY in circumventing the XML limitation of linked servers.

Rather than querying XML directly, we convert XML into NVARCHAR(MAX) and revert it back to XML once the data is on local server.

--
-- Modified query to circumvent the limitation
--
SELECT ID, CONVERT(XML, XMLColumn) AS XMLcolumn
FROM OPENQUERY(LinkedServer01,
	'SELECT ID
		 , CONVERT(NVARCHAR(MAX), XMLColumn) AS XMLColumn
	FROM DatabaseName.dbo.TableName') AS RemoteQuery
GO

The XML data is retrieved as NVARCHAR(MAX) to the local machine; And immediately converted to XML before displaying.

 

Hope this helps,
_Sqltimes
Advertisements

Read Full Post »

Quick one today:

Every time we we build a new cluster in our lab environment, we start with running cluster validation report before starting Sql Server installation. Once in a while, we need to go back to refer older validation reports.

All cluster validation reports are saved, by default, at the location below on the active node where the validation was executed from:

C:\Windows\Cluster\Reports

ValidationReport

 

Hope this helps,
_Sqltimes

 

Read Full Post »

Interesting Error today:

While playing with XML DML, ran into this cryptic error.

Msg 2207, Level 16, State 1, Line 89
XQuery [dbo.Table.XMLColumn.modify()]: Only non-document nodes 
can be inserted. Found "xs:string ?".

 

While INSERTing a new XML element into an existing XML column, using the below code, this error pops up:

 

DECLARE @NewXMLElement VARCHAR(50) = 'Test'

UPDATE dbo.TableName
SET XMLColumn.modify('
		insert sql:variable("@NewXMLElement")
		into (/Parent/Child)[1]
	')
WHERE XMLColumn.exist('(/Parent/Child/NewXMLElement/text())') = 0
GO

Resolution:

Here, the goal is to INSERT a XML snippet into an existing XML column value. So, it is important that in the modify() function, the new XML element variable is defined as XML datatype

DECLARE @NewXMLElement XML = 'Test'

UPDATE dbo.TableName
SET XMLColumn.modify('
		insert sql:variable("@NewXMLElement")
		into (/Parent/Child)[1]
	')
WHERE XMLColumn.exist('(/Parent/Child/NewXMLElement/text())') = 0
GO

 

Hope this helps,
_Sqltimes

 

Read Full Post »

Interesting one today:

In our lab environemnt, one of the SQL cluster environment ran into this error.

 

 

Error Message:

Clustered role 'Cluster Group' has exceeded its failover threshold.  
It has exhausted the configured number of failover attempts within 
the failover period of time allotted to it and will be left in a 
failed state.  
No additional attempts will be made to bring the role online or fail 
it over to another node in the cluster.  Please check the events 
associated with the failure.  After the issues causing the failure are 
resolved the role can be brought online manually or the cluster may 
attempt to bring it online again after the restart delay period.

The Cluster service failed to bring clustered role 'Cluster Group' 
completely online or offline. One or more resources may be in a 
failed state. This may impact the availability of the clustered role.

Resolution:

Errors like this are more common in Lab environments than in production environment. In any case, if you encounter the same error in production environment, then take extra caution before you follow these steps.

Possible Root Cause:

In lab sometimes, as part of some other effort, we inadvertently end up failing over the cluster several times within a short period of time. There is a setting in Cluster that measures the failover count.

  • If that count hits a particular threshold, it flags the Resource Group as ‘Failed’ state
  • And creates an entry in the the Cluster Events, that Cluster Resource Group failed after reaching the threshold (see the error message : Clustered role ‘Cluster Group’ has exceeded its failover threshold)

Resolution Steps:

According to this MSDN post, we could alter that failover count threshold to allow the resource group to come back up in a healthy state.

 

Step 1:

Go to Failover Cluster Manager >> Roles >> right click on the Resource Group and to go Properties:

  • Change the Maximum failures in a specified period to a larger number to account for the repeated failovers in recent hour.

ClusterFailoverThreshold_1

 

 

Step 2:

Go to Failover Cluster Manager >> Roles >>

In the bottom portion of the window where we the individual resources are listed, right click on the Resource that is in failed state and go Properties:

  • Increase the Maximum restarts in the specified period setting to a larger number to account for recent restarts.

 

ClusterFailoverThreshold_2

 

NOTE: This is not a standard solution for production environments.

Hope this helps,
_Sqltimes

Read Full Post »

Interesting one today:

In the past, we’ve seen multiple posts on XML DML covering several XML manipulation capabilities. Today, we’ll cover another aspect of XQuery : Ability to add multiple conditions within the same XQuery path.

--
-- Create new sample table
--
CREATE TABLE dbo.SampleXML (
ID INT NOT NULL IDENTITY(1,1)
, XML_Col XML
)
GO

--
-- Create XML value
--
DECLARE   @X XML = '

				Marco
				Polo

				Hello 16
				World 16

				Hello 17
				World 17

				Hello

'
--
-- INSERT XML value into table column
--
INSERT INTO dbo.SampleXML (XML_Col) VALUES (@X)
GO

--
-- See the result
--
SELECT *
FROM dbo.SampleXML
GO

The sample XMl looks like this:

XMLDML_MultipleConditions

And in it we’ll be querying the following things:

  1. Using Single Condition
    1. Query the number of ParentElement under Root
    2. Query the number of ParentElement with @Att1 = “Color”
      1. Answer: 1
    3. Query the number of ParentElement with @Att1 = “Brand”
      1. Answer: 2
    4. Retrieve element text of ParentElement with @Att1 = “Brand” for Child1
      1. Answer: Hello 16
  2. Using Two conditions
    1. Retrieve element text of ParentElement with @Att1 = “Brand” and @Att2=”17″ for Child1
      1. Answer: Hello 17

XMLDML_MultipleConditions_Detailed.png

--
-- XML Query to retrieve with multiple conditions in XQuery<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>
--
SELECT	  XML_Col.value('count(/Root/ParentElement)', 'INT')													AS [NumberOf_ParentElements]
		, XML_Col.value('count(/Root/ParentElement[@Att1="Color"])', 'INT')										AS [NumberOf_ParentElements_Color_Attribute]
		, XML_Col.value('count(/Root/ParentElement[@Att1="Brand"])', 'INT')										AS [NumberOf_ParentElements_Brand_Attribute]

		, XML_Col.value('(/Root/ParentElement[@Att1="Brand"]/Child1/text())[1]', 'VARCHAR(30)')					AS [Child1_Text_in_Brand]
		, XML_Col.value('(/Root/ParentElement[@Att1="Brand" and @Att2="17"]/Child1/text())[1]', 'VARCHAR(30)')	AS [Child1_Text_in_Brand_and_17]

FROM dbo.SampleXML
GO

Output looks like this:

XMLDML_MultipleConditions_Output

 

Hope this helps,
_Sqltimes

 

Read Full Post »

Interesting one today:

Occasionally we need to connect to Sql Server over Windows Authentication using different Windows user. This is a good step to manually check a few things about the validity or privilege level for that other Windows Account (there are other ways too).

One of the quickest way is to Connect to Sql Server over Windows Authentication using the other Windows account.

By default, when you open SSMS (Sql Server Management Studio) under Windows Authentication, it pre-populates our current windows account. There are a couple of options to workaround that.

Solution 1: Use runas

From a windows command prompt or a Run window, run the following runas command with the other Windows user

runas /user:domain\user Ssms.exe

New SSMS window opens up with the above mentioned Windows User account pre-populated.

Solution 2: Use RunAs from windows menu

Same steps as above, but we use windows user interface to perform the same steps.

Right click on SSMS and go to pop-up menu:

SSMS_DifferentUser_1

On “Microsoft Sql Server Management Studio 17“, press Shift + Right click

SSMS_DifferentUser_2

On the new SSMS, you could specify the new Windows Account (or Domain Account) to use for login.

Hope this helps,
_Sqltimes

 

Read Full Post »

Quick one today

Once in a while, we need to process XML files (a.k.a. deadlock XML files) to retrieve some pertinent information to uncover the deadlock patterns.

In the past, we’ve seen some XML DML queries to parse XML files.

Here we’ll focus on filtering capability based on attribute values in elements:

Take an deadlock XML for example:

Deadlock_Filter_Attribute

The goal is to search for the action element with attribute value as “collect_system_time“.

--
-- Parse & Filter XMl file data
--
SELECT
	  ROW_NUMBER() OVER(ORDER BY DeadLockXML.value('(/event/@timestamp)[1]', 'DATETIME2'))											AS [RowN]
	, DeadLockXML.value('(/event/action[@name="collect_system_time"]/value)[1]', 'DATETIME') AS SystemTime
FROM  dbo.Deadlocks_07262018_Step2
GO

The [] allow us to provide the value to filter the XMl elements:

(/event/action[@name="collect_system_time"]/value)[1]'

 

Hope this helps,
_Sqltimes

 

Read Full Post »

Older Posts »