Feeds:
Posts
Comments

Archive for the ‘Sql Server 2008 R2’ Category

Interesting one today:

XML DML is one powerful tool in Sql Server arsenal. In that value() function allows us to parse the XMl file and retrieve the values of attributes, elements, text, etc from the XML.

Using the sample XML below, we’ll explore various versions of using value() function.

Examples:

This is the sample XML file

XML_DML_Structure.PNG

DECLARE @X XML = '

....
'

SELECT @X.value('(/StoreConfigurations/ProcessingOptions/PasswordExpirationDays)<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;">&#65279;</span>[1]', 'VARCHAR(2)') AS [Returns30]
	, @X.value('(/StoreConfigurations/ProcessingOptions/PasswordExpirationDays)[2]', 'VARCHAR(2)') AS [ReturnsNULL]

	, @X.value('(/StoreConfigurations/ReceiptTexts/ReceiptText/ReceiptHeaderLine1)[1]', 'VARCHAR(100)') AS [Returns YourStore]
	, @X.value('(/StoreConfigurations/ReceiptTexts/ReceiptText[@StoreNumber="6"]/ReceiptHeaderLine2)[1]', 'VARCHAR(100)') AS [Returns Your Street]

	, @X.value('(/StoreConfigurations/Hosts/Host/RetryCounter)[1]', 'VARCHAR(2)') AS [Returns 01]
	, @X.value('(/StoreConfigurations/Hosts/Host[2]/RetryCounter)[2]', 'VARCHAR(2)') AS [Returns 04]
	, @X.value('(/StoreConfigurations/Hosts/Host[3]/RetryCounter)[1]', 'VARCHAR(2)') AS [Returns 03]

	, @X.value('(/StoreConfigurations/Hosts/Host[@Prefix="X"]/RetryCounter)[1]', 'VARCHAR(2)') AS [Returns 01]
	, @X.value('(/StoreConfigurations/Hosts/Host[@Prefix="Y"]/RetryCounter)[2]', 'VARCHAR(2)') AS [Returns 04]
	, @X.value('(/StoreConfigurations/Hosts/Host[@Prefix="Z"]/RetryCounter)[1]', 'VARCHAR(2)') AS [Returns 03]

	, @X.value('(/StoreConfigurations/Hosts/Host[@Prefix="X"]/HostERCUse)[1]', 'VARCHAR(2)') AS [Returns X]
	, @X.value('(/StoreConfigurations/Hosts/Host[@Prefix="Y"]/HostERCUse)[1]', 'VARCHAR(2)') AS [Returns Y]
	, @X.value('(/StoreConfigurations/Hosts/Host[@Prefix="Z"]/HostERCUse)[1]', 'VARCHAR(2)') AS [Returns Z]

--/ReceiptHeaderLine1
IF (@X.value('(/StoreConfigurations/Hosts/Host[@Prefix="X"]/HostERCUse)[1]', 'VARCHAR(2)') = 'X')
	PRINT 'Yes, its X'
IF (@X.value('(/StoreConfigurations/Hosts/Host[@Prefix="Y"]/HostERCUse)[1]', 'VARCHAR(2)') = 'Y')
	PRINT 'Yes, its Y'
IF (@X.value('(/StoreConfigurations/Hosts/Host[@Prefix="Z"]/HostERCUse)[1]', 'VARCHAR(2)') = 'Z')
	PRINT 'Yes, its Z'
GO

Output:

The result looks like this:

XML_DML_Value

Also we could PRINT from the value function too:

XML_DML_Value_Print.PNG

Hope this helps,
_Sqltimes
Advertisements

Read Full Post »

Quick one today:

Frequently, we run into situations, where we need to put place holders, in a SQL script, that take different values based on some conditions or  settings. Essentially having variables about the script itself (and not the code)

We are taking about variables at the meta-level (script file level, and not the variables in the code itself)

SQLCMD mode within SSMS allows us to set placeholders / variables that will take new values (as you provide) right before executing the script.

First enable SQLCMD mode in SSMS:

SSMS_SQLCMD

In the query window, write some code as below:

--
--  Set variables in a script
--
:SETVAR DatabaseName "master"
:SETVAR TableName "MSreplication_options"

USE $(DatabaseName)
SELECT TOP 10 *
FROM syscolumns

USE $(DatabaseName)
SELECT *
FROM sys.objects
WHERE name LIKE '$(TableName)'
GO

SSMS_SQLCMD_Result

Observations:

Variable replacement occurs both in

USE $(DatabaseName)

and

WHERE name LIKE ‘$(TableName)

  • Having single quotes does not affect variable replacement, as this replacement occurs at the script file level (and node code level — if that makes sense).
  • Since this is a universal replacement capability, it allows you to use variable replacement anywhere in the code without need for special escape characters.
Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Sequence numbers is a valuable feature in Sql Server. Ir replaces the use of IDENTITY column property in some scenarios.

In the past we covered similar topic on how to query the current value of IDENTITY column. With SEQUENCEs it is slightly different:

--
--  Query SEQUENCE number details
--
SELECT name, start_value, maximum_value, current_value
FROM sys.sequences
WHERE name = 'TransactionIdSequence' ;
GO

 

Hope this helps,
_Sqltimes

 

Read Full Post »

Quick one today:

Recently, we capture deadlock server side traces and needed a quick way to retrieve all SPIDs and procedures involved in deadlocks.

The deadlock trace graph is an XML file. If there was one or two deadlocks, we could amanyze it manually. But when you have several deadlocks (100’s or more) and need to analyze all the instances of deadlock entries in the trace, we need a query to parse the XML tree.

Enter XML DML!!

Sample Deadlock XML file looks like this:

We need to retrieve the procedure name involved in the deadlock. Use value() function to parse through the XML path and retrieve the attribute or element value.

Attribute value using @AttributeName

--
--	Return Attribute value in an XML element
--
SELECT	  CONVERT(XML, TextData).value('(/deadlock-list/deadlock/process-list/process/executionStack/frame/@procname)[1]', 'VARCHAR(100)')
		, CONVERT(XML, TextData).value('(/deadlock-list/deadlock/process-list/process/executionStack/frame/@procname)[2]', 'VARCHAR(100)')
		, CONVERT(XML, TextData).value('(/deadlock-list/deadlock/process-list/process/executionStack/frame/@procname)[3]', 'VARCHAR(100)')
		, CONVERT(XML, TextData).value('(/deadlock-list/deadlock/process-list/process/executionStack/frame/@procname)[4]', 'VARCHAR(100)')
		, CONVERT(XML, TextData)
FROM [After]
WHERE EventClass = 148

Pay attention the path mentioned — that shows what value to retrieve:

/deadlock-list/deadlock/process-list/process/executionStack/frame/@procname)[1]

  • ‘/deadlock-list/deadlock/process-list/process/executionStack/frame/’ is the location of the attribute
  • ‘@procname’ is the attribute to retrieve
  • [1] says to retrieve the vale from the first instance of such element — as XML could have multiple repeating similar elements. So we could also say [2], [3]… for respective instances.
  • We mention datatype VARCHAR(30) as a second parameter to value function indicating the type of value we are returning.

Attribute text:

Similarly, we could retrieve text value using XML value function:

--
--	Return XML element value
--
SELECT	  CONVERT(XML, TextData).value('(/deadlock-list/deadlock/process-list/process/executionStack/frame)[1]', 'VARCHAR(100)')
		, CONVERT(XML, TextData).value('(/deadlock-list/deadlock/process-list/process/executionStack/frame)[2]', 'VARCHAR(100)')
		, CONVERT(XML, TextData).value('(/deadlock-list/deadlock/process-list/process/executionStack/frame)[3]', 'VARCHAR(100)')
		, CONVERT(XML, TextData)
FROM [After]
WHERE EventClass = 148

Similarly, pay attention the path mentioned — that shows text to retrieve:

‘/deadlock-list/deadlock/process-list/process/executionStack/frame)[1]’

  • /deadlock-list/deadlock/process-list/process/executionStack/frame/ is the location of the text
  • [1] says to retrieve the vale from the first instance of such element — as XML could have multiple repeating similar elements. So we could also say [2], [3]… for respective instances.

For more information, please review MSDN article.

 

Hope this helps,
_Sqltimes

 

Read Full Post »

Interesting one today:

We have a bunch of lab Sql Server boxes machines and sometimes after a fresh Sql Server install, when we try to open Activity Monitor, we run into this problem.

Error:

 

TITLE: Microsoft SQL Server Management Studio
 ------------------------------

The Activity Monitor is unable to execute queries against server DC2POLTPS02.
 Activity Monitor for this instance will be placed into a paused state.
 Use the context menu in the overview pane to resume the Activity Monitor.

Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (mscorlib)

------------------------------

Since these are lab machines, we are remotely logged into the machines and looks like there is some setting that prevents Activity Monitor from opening successfully. Activity Monitor provides great detail on what is going on with Sql Server at any given point-in-time and such activity needs “high level insight” into the Operating System and Sql Server; Such “high level” permissions are not enabled by default for user accounts.

Following steps show a way to enable elevated permissions when logged in remotely.  From what I could gather from Microsoft Connect this seems like elevated permissions on remote operating system’s DCOM. So we need to enable Remote Launch & Remote Activation permissions on remote Operating System (lab machine)

Resolution:

RDP to the remote machine and

  1. Open Component Services (DCOMCNFG) from start menu
  2. In the left hand tree, under Console Root, expand Component Services, expand Computers, right-click on My Computer and go to Properties
  3. In My computer Properties window, go to COM Security tab.
  4. In the Launch and Activation Permissions section, click on Edit Limits button.
    1. In the Security Limits tab, see if your user/group name exists. If not add to the list by clicking on Add button.
    2. Once user is added, highlight the user and make sure it has both Remote Launch & Remote Activation permissions checked.
  5. In the Access Permissions section, click on Edit Limits button
    1. In the Security Limits tab, see if your user/group name exists. If not add to the list by clicking on Add button.
    2. Once user is added, highlight the user and make sure it has Remote Access permissions checked.
  6. Hit Okay to save changes.
  7. Now expand the My Computer in the left-hand tree and go to DCOM Config.
    1. Find Windows Management and Instrumentation and go to Properties.
    2. Go to Security tab and under Launch and Activation Permissions section, click on Edit button
    3. In the Security tab, see if your user/group name exists. If not add to the list by clicking on Add button.
    4. Once user is added, highlight the user and make sure it has both Remote Launch & Remote Activation permissions checked.
    5. (See the image below)
  8. Save all changes and re-open Activity Monitor
Activity Monitor Error

Activity Monitor Error

 

 

 

Hope this helps,
_Sqltimes

Read Full Post »

Interesting one today:

On a production box, the backup jobs have been failing with an interesting and perplexing error. Its says “Not enough disk space“; As you can guess, this is one of those confusing or misleading error messages that’s not what it seems on the surface — Making it worthwhile for a post of its own.

Detailed error message is below:

BACKUP DATABASE DummyDB
TO        DISK = N''
	, DISK = N''
	, DISK = N''
	, DISK = ''
WITH STATS = 1
GO
...
...
...
68 percent processed. 
69 percent processed. 
70 percent processed. 
Msg 3202, Level 16, State 1, Line 1 

Write on "F:\MSSQL\Backup\DummyDB.BAK" failed: 
112(There is not enough space on the disk.) 

Msg 3013, Level 16, State 1, Line 1 
BACKUP DATABASE is terminating abnormally.

This error occurs in both backups with & without compression; And in FULL & Differential backups.

This is a fairly large database, ranging up to 18 TB. So, backups are an ordeal to perform. So, when DIFF backups started failing, it was a bit concerning too.

After attempting several backups on local  & remote storage with plenty of space, a pattern still did not emerge. The only constant is that it fails around 70% completion progress.

At that point, one of  my colleagues (Thanks Michael) pointed out that, as part of backup operation, Sql Server will first run some algorithm that calculates the amount of space needed for the backup file. If the backup drive has enough free space well  and good, if not, it throws this error.

But, as you can guess, we had plenty of free space i.e. peta bytes of free space.

Occasionally, manual backups are successful. So, I’m not sure what is going on, but here is my theory:

At different points, Sql  Server  runs the algorithm (“pre-allocation algorithm”) to determine if there is enough space. Initially it comes back saying “yes” — and the backup proceeds with writing to the backup file; Again a little later, it checks, and it comes back with “Yes”; But at someone on subsequent checks (in our case between 70% to 72% complete), the algorithm decides there is  not enough disk space.

So, turns out there is a TRACE FLAG called 3042 that could disable this algorithm from making any assessments — that way backups could progress to completion.

From  MSDN:

Bypasses the default backup compression pre-allocation algorithm to allow the backup file to grow only as needed to reach its final size. This trace flag is useful if you need to save on space by allocating only the actual size required for the compressed backup. Using this trace flag might cause a slight performance penalty (a possible increase in the duration of the backup operation).

Using this trace flag might cause a slight performance penalty (a possible increase in the duration of the backup operation).

Caution: Manually make sure there is plenty of space for backup to complete — since we are disabling the algorithm.

--
-- Disable pre-allocation algorithm
--
DBCC TRACEON (3042)
GO

BACKUP DATABASE DummyDB
TO        DISK = N''
    , DISK = N''
    , DISK = N''
    , DISK = ''
WITH STATS = 1
GO
DBCC TRACEOFF (3042)
GO

Make sure you test this in a non-production environment, before enabling it in production.

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

In production environments, its a common practice to perform full VERIFICATION of backup files, as part of backup it self. Following script shows verification of backup file:

--
-- Verify backup file
--
RESTORE VERIFYONLY
FROM       DISK = N'H:\MSSQL\Backup\DummyDB_01.BAK'
	,  DISK = N'H:\MSSQL\Backup\DummyDB_02.BAK'
	,  DISK = N'H:\MSSQL\Backup\DummyDB_03.BAK'
	,  DISK = N'H:\MSSQL\Backup\DummyDB_04.BAK'
	,  DISK = N'H:\MSSQL\Backup\DummyDB_05.BAK'
WITH STATS = 1
GO

RestoreVerify

 

Hope this helps,
_Sqltimes

 

Read Full Post »

Older Posts »