Feeds:
Posts
Comments

Archive for the ‘New Features’ Category

Interesting Problem Today:

Ran into this issue a few times and every time its a variation of the same headache. So, here some ideas will be documented for posterity.

In general terms, the error looks like this:

Connecting to Subscriber ''
Agent message code 20084. The process could not connect to Subscriber ''
Microsoft SQL Server Native Client 11.0
SQL Server Network Interfaces: The target principal name is incorrect.
Cannot generate SSPI context

The error message seems nebulous and confusing — but for trained eyes it makes perfect sense. For me it took a while to make sense out of it.

There could be several things wrong under the hood, but essentially it says that the target SQL server that is is trying to connect to, does not have a valid SPN with Active Directory.

Meer from Microsoft has documented some information on troubleshooting this issue here. For more details, please read his article, as I’ll over simplify things and address a variation of the problem in this articles (which will be slightly different from his).

Example:

From SQLServerA, using UserA, if I’m trying to connect to SqlServerB, sometimes I get this error. Essentially, means SqlServerB does not have a valid SPN.

Resolution:

First, log into the machine that has SqlServerB. Open command prompt with Administrative privileges. Run the command below to see if there is a valid SPN.

 

SETSPN -L <SQL Server Instance Service Account>

 

If the output looks like the first image below, then the Sql Server instance does not have a valid SPN. Now its time to generate one.

Output without valid SPN:

Invalid SPN

Invalid SPN

Step 2: Download Kerberos Configuration Manager for SQL Server from here, and start generating one.

Step 3: Open Kerberos Configuration Manager for SQL Server from the same machine that has SqlServerB instance. When you run it, it shows something like this:

 

Kerberos Tool Output

Kerberos Tool Output

Notice that for Sql Server service, there is not valid SPN or misplaced SPN. So its time to generate one.

Step 4: Hit the “Fix it” button right next to it and generate one. Make sure the user account that is logged into the machine has domain controller permissions.

Step 5: Now run the same command as in Step 1, and the output looks different.

Output with valid SPN:

Valid SPN

Valid SPN

 

Voila !! Now you are able to connect to SqlServerB from SqlServerA using UserA

 

 

Hope this helps,
_Sqltimes
Advertisements

Read Full Post »

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

Read Full Post »

Quick one today:

Intellisense is a great feature. Quite often, we run into situations where we don’t see the drop-down showing new tables, columns, objects, etc names. While this is not frequent, it does happen often enough that it causes some good discussions in conference calls.

Problem Definition:

When we make schema changes in the current session, they are not always visible within intellisense immediately (similarly other people making schema changes to the the database schema are not immediately visible in your session either)

As you can imagine, Intellisense has its own cache and it needs to be refreshed to update with recent schema changes.

Resolution

Two options to fix this:

  1. Ctrl + Shift + R
  2. Edit menu >> Intellisense >> Refresh Local Cache
IntellisenseCache
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 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 »

Quick one today:

Extended Events make it easy to capture deadlock details into an XEL file, that has deadlock data in XML format.

This XEL file could be imported into a Sql table using sys.fn_xe_file_target_read_file function; It is similar to ::fn_trace_gettable function to load Sql trace file into a table.


SELECT * INTO dbo.DeadlocksXML
FROM sys.fn_xe_file_target_read_file ('I:\Deadlocks\xml_deadlock_report_0_131771103583570000.xel', null, null, null)
GO

MSDN also has an article on this function.

Hope this helps,
_Sqltimes

 

Read Full Post »

Quick one today:

Earlier, while troubleshooting deadlocks, we needed a quick way to count the number of victims & processes in each deadlock incident. Since deadlock information is available in XML, we could use COUNT function to measure the occurrences.

Deadlock_Counts

In the past, we’ve covered some techniques to uncover details from deadlock XML data.

XML provides many tools to parse XML files. Below query makes it easy to gather such counts at each level; Be it Victim count or processes count, etc

SELECT
	, DeadLockXML.value('count(/event/data/value/deadlock/victim-list/victimProcess)', 'INT') AS [Count_Victims]
FROM dbo.DeadlocksXMLTable
GO

We could send XML Path we want to count as a parameter to COUNT function to gather the number.

DeadLockXML.value(‘count(/event/data/value/deadlock/victim-list/victimProcess)’, ‘INT‘)
Below is an expanded TSQL query that includes more columns from deadlock XML file.

SELECT
	  ROW_NUMBER() OVER(ORDER BY DeadLockXML.value('(/event/@timestamp)[1]', 'DATETIME2'))											AS [RowN]
	, DeadLockXML.value('(/event/@timestamp)[1]', 'DATETIME2')																		AS [TimeStamp]
	, DeadLockXML.value('count(/event/data/value/deadlock/victim-list/victimProcess)', 'INT')										AS [Count_Victims]
	, DeadLockXML.value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[1]', 'VARCHAR(100)')							AS [Victim1]
	, DeadLockXML.value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[2]', 'VARCHAR(100)')							AS [Victim2]
	, DeadLockXML.value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[3]', 'VARCHAR(100)')							AS [Victim3]
	, DeadLockXML.value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[4]', 'VARCHAR(100)')							AS [Victim4]
FROM dbo.DeadlocksXMLTable
GO

 

Hope this helps,
_Sqltimes

Read Full Post »

Older Posts »