When you’re working with PowerShell and SSRS, you may occasionally come across a script that works once, then just mysteriously decides not to work anymore on a second or third invocation. Or it may just not work period, even though you think the syntax is short and straightforward, and you know you’re not misspelling any syntax.

Please note I am running this on the PowerShell ISE, and PowerGUI – and tried on both PowerShell V2 and V3.

Common Task

What was driving me crazy (at some point, I promise I’m back to my sane self now) was trying to create a folder with property. The syntax is pretty straightforward, like this:

Import-Module SQLPS -DisableNameChecking;
$ReportServerUri  = "http://localhost/ReportServer/ReportService2010.asmx"
$proxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential -Namespace SSRS ;
$proxy
$proxy.GetType().Namespace;	#gives me SSRS
 
#create a folder
$property = New-Object  "SSRS.Property" 
$property.Name = "Description"
$property.Value = "This folder is for any HR related reports"
 
#we need a property array to pass to the CreateFolder method
$properties = New-Object "SSRS.Property[]" 1;
$properties[0] = $property;
$foldername = "HR_" + (Get-Date -format "yyyy-MMM-dd-hhmmtt");
$proxy.CreateFolder($foldername, "/", $properties);

Broken??

Should be simple, right? PowerShell says, nope, not today. You get this error:

Cannot convert argument "Properties", with value: "SSRS.Property",
for "CreateFolder" to type "SSRS.Property[]":
"Cannot convert the "SSRS.Property" value of type "SSRS.Property"
to type "SSRS.Property"." 
At C:\Users\Administrator\Scripts\SSRS\SSRS.ps1:22 char:1
+ $proxy.CreateFolder($foldername, "/", $properties);
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument

Uh, what? Doesn’t that sound a little – wrong? It’s complaining about casting from SSRS.Property to SSRS.Property. It’s the same thing!
Read the rest of this entry »

I was strolling into the office liesurely one morning this week, when one of my co-workers saw me in the hallway and motioned me to hurry because I was apparently late for a meeting. I didn’t realize an invite for an early morning meeting was sent out the night before, around 10:33PM, which I was not able to see or accept.

Coming in to my director’s office, I see most of my co-workers already there, and my boss’ boss. We were just waiting for one more person who I assume didn’t check emails at 10PM either. I was thinking this must be another serious deployment issue, or a system meltdown. All I was thinking of was .. “are we in trouble .. again?“. Last year had been rough with all the work, deadlines, and flurry of activities. December was especially rough, trying to push for a deployment before everyone went away for holidays.

As my last team member came in, my director started speaking .. and I can see her face redden. I can clearly remember her saying ..

“Kevin Tu passed away yesterday morning .. ”

.. and I remember nothing else…

What did she say? I can’t have heard that right. I can feel my eyes burning up. I didn’t work with Kevin for a long time, but worked long enough to care about this person, and to feel a very deep sadness.. and denial.

Yeah I can’t have heard right. I was just bugging him about accounts we needed to create and accounts we needed to audit. He was just telling me what he did on his “staycations”. I was just at his cubicle promoting why “neighbor times” are good. We have so much work that sometimes I don’t realize time just flew by. Before I realize it it’s already 3 or 4PM. Whenever this happens, I get up and go to my neighbor cubicles, and ask how they’re doing and getting them to take a 2 minute break from staring at their computers. He was just making fun of me because I was so fast in responding to freebie emails from our local tech mailing list. An email goes around about stuff that will be thrown out – old printers, projectors etc – and I managed to snag a couple even before he saw the email.

I am feeling guilty too. When he started missing work, I should have asked around about what’s happening. But I’m the type of person who doesn’t pry. I think that if I needed to know it, or if they wanted to share the information, people will volunteer the information. If not, I don’t pry about it because it’s most likely none of my business. And I want to give people that kind of privacy. I realize maybe I have done this to a fault. I *should* have asked how he was doing and why he was missing at work. I *should* have never assumed that he was just on vacation. Had I asked, and had I known, the least I could have done was get some of my other co-workers together to visit him.

And now it’s too late. Too late to have helped him in all those account creations. Too late to bug him about how fast he goes up 5 flights of stairs. Too late to have asked why he missed days at work. Too late to have been considered a friend. But it does not erase the fact that he has touched many lives, including mine, and I will always be grateful I’ve been given the chance to know him albeit very brief.

Kevin’s death can’t help but put things in perspective. I can’t help but think about my own life and how I am choosing to live it. Is it enough? Am I doing things that matter? Am I making the most of my life, am I making the impact I am hoping to make, am I spending my time with people I care about? I’ve recently come across Joe Webb‘s article “I am choosing to cheat” and I can’t help but ask myself a lot of questions. A lot of hard questions. What matters to me, and with my limited time, am I giving enough time to the people that matter most to me? Do they know they matter to me? Have I told them? Have I shown them?

I don’t think I will ever be answer all of these questions that are going through my mind right now. But maybe that’s just part of life’s wonders.

So .. I guess this is life. As I grow older, I experience and appreciate better this adventure called life. There are ups and downs. Good and bad. Achievements and failures. Joy and pain. There’s confusion, fear, regret, bliss. And there’s loss. And healing. And hope.

–

A memorial page has been set up in Kevin’s memory. Please consider donating.
http://donate.bccancerfoundation.com/site/TR/Events/Fuse?pxfid=10508&fr_id=1340&pg=fund

The PowerShell ISE is much improved in V3, needless to say I am really enjoying writing scripts with this tool! It is however, not without hiccups.

I was trying to run the Invoke-Sqlcmd cmdlet when I got the following error in the PowerShell ISE

Mixed mode assembly is built against version ‘v2.0.50727′ of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.

Basically just means PowerShell has support for side-by-side runtime, and we need to specify an activation policy in a config file. The config file name must be placed in the same directory as your exe file, and must be named the same as your exe file, plus a .config extension.

Solution

1.Create a config file for the powershell_ise.exe called powershell_ise.exe.config and place this in the PowerShell directory:
C:\Windows\System32\WindowsPowerShell\v1.0

2. Add the following to your powershell_ise.exe.config file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <startup useLegacyV2RuntimeActivationPolicy="true">
     <supportedRuntime version="v4.0" />
  </startup>

  <runtime>
    <generatePublisherEvidence enabled="false" />
  </runtime>
</configuration>

Thanks to Richard Fennel’s post “Mixed mode assembly is built against version ‘v2.0.50727′ error using .NET 4 Development Web Server”, which helped me fix this issue.

As a gist, the new SQL Server 2012 will have a new set of editions:
- Standard – will be the new basic version
- Enterprise
- Business Intelligence
- Developer
(no more DataCenter, Workgroup and Standard for Small Business)

For licensing, Microsoft is doing away with per processor licensing – but replacing this with per core licensing (licenses to be sold in 2-core packs). And for a VM, you pay for virtual cores.

Cost breakdown nicely explained and tabulated by Glenn Berry here: http://sqlserverperformance.wordpress.com/2011/11/10/sql-server-2012-licensing-and-hardware-considerations/

Find the official transcript here:
http://www.sqlpass.org/Community/PASSBlog/entryid/380/Preparing-for-SQL-Server-2012-What-You-Need-to-Know.aspx

Link for Licensing Options for SQL Server 2012:
http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-licensing.aspx

Just this week, UBC launched of a very ambitious campaign. The tagline is “Start an Evolution” – UBC is launching the biggest fundraising campaign in history. I am very proud to be part of this organization .. albeit in the shadows (I work on the databases, you see).

For the unfamiliar it probably sounds like just a “money thing”, but for those who are familiar, it is more than just raising money. It is all about building opportunities for students, inspiring them to become what they can become, and empowering them to take on challenges that they otherwise may not be able to take on by themselves. It’s about daring to dream. It sounds so cliche, but that’s what it is.

One example, there was one group of students, inspired by their professor and funded by someone who believed in the dream, went on to do an investigative journalism piece that won an Emmy. There are lots more inspiring stories like this (Read these success stories) … We have to remember that the littlest drop, the littlest change, can cause ripple effects of unfathomable lengths.

Somehow, this is what it feels, and means to be a SQL Server MVP – for me…

Just today I opened my inbox, and I saw an email saying “Congratulations! We are pleased to present you with the 2011 Microsoft® MVP Award! …”.

“huh … gasp!!!” …. I literally had to pause, and re-read my email. Because this is something I did not expect at all. I knew I was nominated, but knowing there are so many other great, expert SQL Server Professionals who are up for the award, I knew I didn’t have a chance.

But … I still dared to dream. Long before I got the email, I did dream and thought it might be nice to get the award. I’d be a hypocrite if I told you getting the award didn’t matter. For me, it’s this nice warm fuzzy feeling knowing that somehow, you’ve been noticed, and that somehow there’s this appreciation for some of the things you’ve done.

However, the MVP award is NOT the be-all end-all reason I’ve poured hours of work answering emails or writing articles or giving talks. I don’t know what it is about SQL Server, but I truly enjoy working with it. I truly enjoy writing the queries and reports, and answering the questions that can’t be easily answered. I like uncovering truths, although it’s not always a party if you’re the bearer of the not-so-great message (Sorry sales are really down). I enjoy being able to troubleshoot these sometimes really cryptic error messages that creep up to servers and seemingly harmless installs and configurations (@sqlbelle - 1, SQL Server – 0! Cha ching!).

I may not be the best at all things SQL Server, but with everything I’ve done, I’ve poured my 110%.

Some people may know I have been teaching at BCIT for a while now, for me I consider this more than just a contract job. Because if it was just that I could have gladly moved to more lucrative contracts. But I consider my teaching at BCIT a passion, and almost a mission. I hope to be able to make a difference, to inspire someone – one out of ten, one out of a hundred, one out of the 10 years I’ve been with the institute. My courses are primarily SQL Server, but I hope in some ways it’s also beyond that. I hope somehow through the courses, students realize that they should pursue what makes them happy. Of course I try to infect my SQL Server enthusiasm to my students – sometimes it works, sometimes it doesn’t. But that doesn’t mean my efforts are in vain (or so I would like to believe).

I probably almost sound like a hopeless romantic (and how unfitting is that in a supposedly technical blog) .. but I really believe you can dare to follow your dreams. You can dare to find what it is that makes you happy. (Joseph Sack just posted a link to this New York Times article – Do Happier People Work Harder? ). And as Confucius says:

Choose a job you love, and you will never have to work a day in your life.

I’m glad I discovered my passion. Yes I know this sounds cheesy, but working with SQL Server makes me happy. Everyday at “work” is not really “work”. It’s another SQL Server adventure.

I dared to dream. Now, I dare to make my ripple effects, however small or large they may be.

PS Thank you Microsoft, for awarding me the MVP. Another source of inspiration .. I still have the warm fuzzy feeling.

PPS Thank you, whoever you are who nominated me. Gesture is very very much appreciated.

Just wanted to say thank you to everyone who attended the August 2011 VANPASS meeting at the Sierra Systems building. And thank you to the August sponsors Sierra Systems and QueryWorks Solutions!

If you need to contact me with questions or comments about the presentation just send me an email at

donabel dot santos at queryworks dot ca

Here is a recap of the presentation:

SQL Server Reporting Services – Beyond 101

In this presentation we will look different ways of using and extending Reporting Services.

Topics will include using and customizing charts, using expressions for common (and not-so-common) Reporting Services tasks, creating drilldowns/drillthroughs, querying SSRS metadata using custom code, and using Reporting Services to connect to non-conventional data sources (MySQL and other data sources). We’ll also include a few tricks (like PowerShell!) you might not have known about SSRS!

This presentation will be demo-filled. You’ll be sure to go home with ready-to-use tricks up your sleeve.

I will also be posting some SSRS tips/tricks at QueryWorks blog

As promised here are the files:

VANPASS – SSRS Beyond 101 Presentation – Donabel Santos – sqlbelle

VANPASS SSRS Beyond 101 – Aug 2011 – RDL Files

VANPASS SSRS Beyond 101 – PowerShell – SSRS Viewer.PS1

VANPASS SSRS Beyond 101 – PowerShell – Download RDL.ps1

SQLSaturday65 – SSRS from the Ground Up CheatSheat

Just had an interesting error the other day.

Got this error when trying to connect one of our servers that has just been patched:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

Typically, to troubleshoot this, you go to SQL Server Configuration Manager (SSCM) and:
1. ensure Shared Memory protocol is enabled
2. ensure Named Pipes protocol is enabled
3. ensure TCP/IP is enabled, and s ahead of the Named Pipes in the settings

Check out Pinal Dave’s excellent post on this.

All of these are set in our server.

Next up, I checked the log files. Logs are typically located in a folder similar to this (note you will need to change the folder MSSQL.1 with the appropriate instance name’s folder name):
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

When I checked the logs however, I got yet another interesting error:

Could not connect because the maximum number of ’1′ user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: ]

It’s an interesting error because:
1. We just restarted the server and nobody is able to access it yet
2. DAC is disabled

Solution

After some more sleuthing and fiddling around, I went back to SSCM to check ensure the protocols are enabled properly. There’s 4 (four) options – SharedMemory, TCP/IP, Named Pipes and VIA (for Virtual Interface Adapter). Everything is turned on, except for VIA.

I enabled VIA protocol – and that did it. That was the issue.

Learn more about VIA:
http://en.wikipedia.org/wiki/Virtual_Interface_Adapter
http://msdn.microsoft.com/en-us/library/ms191229.aspx

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