Archive for April, 2011

SQLXML: Fun with SQLXML XQuery – Miscellaneous Functions – Part 1

Just going through some old notes; here are some samples I compiled before that might still be of interest to those of you who are venturing into SQLXML.

The samples below are using miscellaneous numeric and string functions.

Have fun!

Sample XML

DECLARE    @xml XML
SELECT @xml = '
<Employees Dept="IT">
  <Employee Number="1001" Hourly="11.35" FullName="Suzy Jacobs">
	<FName>Suzy</FName>
	<LName>Jacobs</LName>
	<Phone>111-111-1111</Phone>
	<Phone>222-222-2222</Phone>
	<Phone>111-222-1111</Phone>
  </Employee>
  <Employee Number="10" FullName="Bob Willow" ReportsTo="Steve Wong"/>
  <Employee Number="1003" FullName="Tony Carpenter" FName="Tony" LName="Carpenter"/>
  <Employee Number="1004" FullName="Suzy Jacobs" FName="Suzy" LName="Jacobs"/>
  <Staff Number="1003" FName="John"  FullName="John Smith"  LName="Smith" Department="ABC" />
  <MoreStaff Number="1020"  FullName="Ronnie Bates"  FName="Ronnie" LName="Bates" Address="XYZ" Department="ABC" />
</Employees>'

Miscellaneous Functions 1

Using string-length, concat, contains, substring, ceiling, floor, round

<br />

-- miscellaneous functions 1
SELECT
	-- length of "Suzy" 
	-- 4
	@xml.value('string-length((//Employee/FName/text())[1])', 'int') AS 'string-length()',
	
	-- Suzy===
	@xml.value('concat((//Employee/FName/text())[1], "===")', 'varchar(20)') AS 'concat()',
	
	-- note that concat is case sensitive
	-- 1
	@xml.value('contains((//Employee/FName/text())[1], "z")', 'bit') AS 'contains()',	
	
	-- no capital Z in Suzy
	-- 0
	@xml.value('contains((//Employee/FName/text())[1], "Z")', 'bit') AS 'contains()',
	
	-- 0
	@xml.value('contains((//Employee/FName/text())[1], "x")', 'bit') AS 'contains()',
	
	-- uz
	@xml.value('substring((//Employee/FName/text())[1], 2,2)', 'varchar(20)') AS 'substring()'

Results


Miscellaneous Functions 2

Using min, max, avg, sum, lower-case, upper-case

-- miscellaneous functions 2
SELECT	

	-- 10
	@xml.value('min(//Employee/@Number)', 'int') AS 'min()',

	-- 1003
	@xml.value('max(//Employee/@Number)', 'int') AS 'max()',

	-- 671.33
	@xml.value('avg(//Employee/@Number)', 'decimal(10,2)') AS 'avg()',

	-- 2014.00
	@xml.value('sum(//Employee/@Number)', 'decimal(10,2)') AS 'sum()',

	-- min/max dont work on strings
	-- NULL
	@xml.value('min(//Employee/@FullName)', 'varchar(30)') AS 'min()',
	
	-- suzy jacobs
	@xml.value('lower-case((//Employee/@FullName)[1])', 'varchar(30)') AS 'lower-case()',
	
	-- SUZE JACOBS
	@xml.value('upper-case((//Employee/@FullName)[1])', 'varchar(30)') AS 'upper-case()',
	
	-- value is 11.35
	-- 12
	@xml.value('ceiling((//Employee/@Hourly)[1])', 'int') AS 'ceiling()',
	
	-- value is 11.35
	-- 11
	@xml.value('floor((//Employee/@Hourly)[1])', 'int') AS 'floor()',
	
	-- value is 11.35
	-- 11
	@xml.value('round((//Employee/@Hourly)[1])', 'int') AS 'round()'

Results


Getting distinct values

Using distinct-values

SELECT
	@xml.query(' 
				distinct-values( data(//Employee/@FullName) )
			   ') as 'distinct-values()'	

Results

VN:F [1.9.22_1171]
Rating: 10.0/10 (4 votes cast)
VN:F [1.9.22_1171]
Rating: +2 (from 2 votes)

Troubleshooting Replication Error: A required privilege is not held by the client

If you ever you encounter this replication issue, the resolution might be fairly simple, however it will require that you toggle your SQL Service accounts.

Have a look at this KB:
http://support.microsoft.com/kb/911305/en-us

The KB suggests the service account might have changed using a tool other than the Configuration Manager (technically a no-no because the SSCM, in addition to changing the service accounts, also performs updates to associated settings like windows registry entries). Try toggling the service accounts in Configuration Manager (for ex, from Administrator, to something Local, to administrator again) and that should flush the old credentials.

Quote from KB:


To resolve the problem, follow these steps:

1. Set the SQL Server Agent service account in SQL Server Configuration Manager to the LocalSystem account.
2. Stop and then start the SQL Server Agent service.
3. Reset the SQL Server Agent service account in SQL Server Configuration Manager back to the original account.
4. Stop and then start the SQL Server Agent service.


Good luck!

VN:F [1.9.22_1171]
Rating: 8.5/10 (2 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

@msshushu’s and @sqlbelle’s take on “Why BCIT”?

Shereen (blog | @msshushu) and I are going to participate tonight at BCIT’s Info Session for the School of Computing.

Shereen and I are both true blue BCIT alumni. We both have our CST Diplomas from BCIT, and both pursued our degrees at BCIT.

There are a lot of reasons why we went to BCIT, and why going to BCIT worked for us.
Here’s our take on “Why BCIT?”

VN:F [1.9.22_1171]
Rating: 5.5/10 (6 votes cast)
VN:F [1.9.22_1171]
Rating: -2 (from 4 votes)

Invalid Credentials Error During SQL Server 2008/R2 Install

In case you get the following errors:

The credentials you provided for the SQL Server Agent service are invalid. To continue, provide a valid account and password for the SQL Server Agent service.

The specified credentials for the SQL Server service are not valid. To continue, provide a valid account and password for the SQL Server service.

Here’s the simple solution:
http://www.jadota.com/2009/01/the-credentials-you-provided-during-sql-server-2008-install-are-invalid/

VN:F [1.9.22_1171]
Rating: 6.2/10 (10 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Meme Monday – SQL Server Story in 11 Words or Less

I’ve been tagged by the awesome tweep/blogger, Janice C Lee (blog | twitter) for Tom LaRock’s (blog | twitter) Meme Monday (Write a SQL blog post that tells a story in 11 words or less).

Here’s my 11-words-or-less-sql-story:

It’s fun working with SQL Server. Sometimes stressful, but always fun.

Now I want to tag another 3 awesome tweeps/bloggers:

Wendy Pastrick (blog | twitter)
Michelle Ufford (blog | twitter)
Kendra Litte (blog | twitter)

Happy Monday everyone!

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
`