belle’s sql musings

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 more

I just have to blog this little gem. Every now and then I would be running a PowerShell script, I’d get an error but I wouldn’t get anything useful from the default error message besides the line number.

Little Gem

cls
$error[0] | fl -force

Sample

For example, this is an error I would get from the PowerShell console or PowerShell ISE:

Exception calling "Create" with "0" argument(s): "Create failed for
JobSchedule 'Every 3rd Friday 6AM'. "
At C:\Users\Administrator\Dropbox\Packt Draft PS Scripts\Ch03\15 -
Schedule a SQL Server Job - Every 3rd Friday at 6AM.ps1:63 char:1
+ $jobsched.Create();
+ ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FailedOperationException

A sample result that I would get from this little gem, which tells me *exactly* my issue, so I don’t have to dig any further or pull up Profiler. Cool!!

Exception             : System.Management.Automation.MethodInvocationException: Exception calling "Create"
with "0" argument(s): "Create failed for JobSchedule 'Every 3rd Friday 6AM'. "
---> Microsoft.SqlServer.Management.Smo.FailedOperationException:
Create failed for JobSchedule 'Every 3rd Friday 6AM'.
---> Microsoft.SqlServer.Management.Common.ExecutionFailureException:
An exception occurred while executing a Transact-SQL statement or batch. 
---> System.Data.SqlClient.SqlException: The schedule for this job is invalid (reason: @freq_recurrence_factor must be at least 1.). 
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean
 catchException)
    at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteScalar(String sqlCommand)
    --- End of inner exception stack trace ---
    at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteScalar(String sqlCommand)
    at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteScalar(StringCollection sqlCommands)
    at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteScalar(StringCollection query)
    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext)
    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplFinish(StringCollection createQuery, ScriptingPreferences sp)
    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
    --- End of inner exception stack trace ---
    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
    at CallSite.Target(Closure , CallSite , Object )
    --- End of inner exception stack trace ---
    at System.Management.Automation.ExceptionHandlingOps.ConvertToMethodInvocationException(Exception exception, String methodName, Int32 numArgs)
    at CallSite.Target(Closure , CallSite , Object )
    at System.Dynamic.UpdateDelegates.UpdateAndExecute1[T0,TRet](CallSite site, T0 arg0)
    at System.Management.Automation.Interpreter.DynamicInstruction`2.Run(InterpretedFrame frame)
    at System.Management.Automation.Interpreter.Interpreter.Run(InterpretedFrame frame)
TargetObject          :
CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
FullyQualifiedErrorId : FailedOperationException
ErrorDetails          :
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at , C:\Users\Administrator\Dropbox\Packt Draft PS Scripts\Ch03\15 - Schedule a SQL Server Job - Every 3rd Friday at 6AM.ps1: line 63
PipelineIterationInfo : {}
PSMessageDetails      : 

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