Thursday, March 29, 2012

QlikView Expression for Converting Unix int Epoch Dates to date

I wrote an earlier post on ways to do this in SSIS, T-SQL, and PL-SQL, but today needed to do it in a QlikView expression (or load script), since QlikView has AddMonths and AddYears functions, it wasn't immediately obvious that adding days is just a datetime or timestamp plus a numeric and cast back.

To get long datetime

=Timestamp(Timestamp('1970-01-01 00:00:00.000') + YOURUNIXDATEVALUE/1000/60/60/24)

To get date only

=Date(Date('1970-01-01 00:00:00.000') + YOURUNIXDATEVALUE/1000/60/60/24)

The only tricky thing is that in both cases, you're converting milliseconds to days, which makes sense with a date value, but slightly less with a value that's granular to the millisecond.

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

If you just need to add days rather than convert the milliseconds since 1970 data type, you can just add whole days.  For example tomorrow's date:  =(date(today() + 1)).

Disabling or Enabling VMware Change Block Tracking (CBT) using PowerShell and PowerCLI

Recently I experimented with using the VMware specific backup technologies with a number of vendor products.  None of those technologies compared favorably to the simplicity of NetApp SnapMirror + NDMP when using NFS, but the solutions were vastly better than the old VCB based ones.  I could see using them in an environment that leveraged iSCSI or local storage.

After the POC I wanted to disable VMware Change Block Tracking on my non-production VMs.  I’ve run into one bug around CBT and would rather not leave it turned on without a reason.  It is simple to set the value using PowerShell, but to get it to take effect you need to power off the VM or take a snapshot.  Here is my solution:

Get the VMs with CBT enabled:

$vms=get-vm | ?{$_.ExtensionData.Config.ChangeTrackingEnabled -eq $true}

Create a VM Specification to apply with the desired setting:

$spec = New-Object VMware.Vim.VirtualMachineConfigSpec
$spec.ChangeTrackingEnabled = $false

Apply the specification to each VM, then create and remove a snapshot:

foreach($vm in $vms){
$vm.ExtensionData.ReconfigVM($spec)
$snap=$vm | New-Snapshot -Name 'Disable CBT'
$snap | Remove-Snapshot -confirm:$false}

Check for success:

get-vm | ?{$_.ExtensionData.Config.ChangeTrackingEnabled -eq $true}

Wednesday, March 21, 2012

Why does my AJAX Toolkit Miss Stephen so Much?

Quick Description:  After adding an AJAX toolkit control to a c# web application in VS 2010, a debug run blows up with error

Locating source for 'C:\Users\Stephen\Documents\Repos\Superexpert\AjaxControlToolkit\Server\AjaxControlToolkit\ExtenderBase\ExtenderControlBase.cs'

There’s a good reason for that: no-one named Stephen has ever used my sandbox machine.

Embarrassing to admit, but rule this out:

In searching on this problem, I found lots of questions, but I didn’t find a solution…which is probably a fitting punishment for skipping a step in the very clear instructions on the Control Toolkit CodePlex page.  In my case (there may be other causes for the error), it was resolved after verifying I’d followed the instructions to add the ToolkitScriptManager to the page (spoiler alert: I hadn’t).

Error Examples:

image

Call Stack Location:

AjaxControlToolkit.DLL!AjaxControlToolkit.ExtenderControlBase.OnLoad(System.EventArgs e = {System.EventArgs}) Line 306

Source File Information:

Locating source for 'C:\Users\Stephen\Documents\Repos\Superexpert\AjaxControlToolkit\Server\AjaxControlToolkit\ExtenderBase\ExtenderControlBase.cs'. Checksum: MD5 {56 64 14 a8 4c 29 25 94 27 e0 35 10 f1 b4 60 f2}
<rest of stack trace snipped>

And:

Could not find any resources appropriate for the specified culture or the neutral culture.  Make sure "AjaxControlToolkit.Properties.Resources.NET4.resources" was correctly embedded or linked into assembly "AjaxControlToolkit" at compile time, or that all the satellite assemblies required are loadable and fully signed.

Exception Details: System.Resources.MissingManifestResourceException: Could not find any resources appropriate for the specified culture or the neutral culture.  Make sure "AjaxControlToolkit.Properties.Resources.NET4.resources" was correctly embedded or linked into assembly "AjaxControlToolkit" at compile time, or that all the satellite assemblies required are loadable and fully signed.

Friday, March 16, 2012

Enabling NLA for RDP connections using PowerShell

The critical nature of Microsoft bulletin MS12-020 may have you searching for a method to easily enable Network Layer Authentication on your Windows 2008 SP2/R2 servers to avoid an unscheduled patch installation and restart. It is possible to enable it with group policy, but that may not be the best method for every environment. If you have PowerShell remoting setup on all of your servers, you can easily enable NLA:

Enable/Disable NLA for Terminal Services or Remote Desktop Protocol RDP (0=Off, 1=On)

(Get-WmiObject -class "Win32_TSGeneralSetting" -Namespace root\cimv2\terminalservices -Filter "TerminalName='RDP-tcp'").UserAuthenticationRequired
(Get-WmiObject -class "Win32_TSGeneralSetting" -Namespace root\cimv2\terminalservices -Filter "TerminalName='RDP-tcp'").SetUserAuthenticationRequired(1)

For a single server you can enable the setting with invoke-command:

invoke-command –computername MyServer –scriptblock {(Get-WmiObject -class "Win32_TSGeneralSetting" -Namespace root\cimv2\terminalservices -Filter "TerminalName='RDP-tcp'").SetUserAuthenticationRequired(1)}

You can use VMware PowerShell commands to generate the list of servers and execute the command on each server:

$vmnames=get-vm –location "Windows-NonProduction" | select-object -expandproperty name | sort
$sessions = New-PSSession -computername $vmnames
invoke-command –session $sessions –scriptblock {(Get-WmiObject -class "Win32_TSGeneralSetting" -Namespace root\cimv2\terminalservices -Filter "TerminalName='RDP-tcp'").SetUserAuthenticationRequired(1)}

If you have text file with the server names you can use:

$sessions = cat (.\names.txt)
invoke-command –session $sessions –scriptblock {(Get-WmiObject -class "Win32_TSGeneralSetting" -Namespace root\cimv2\terminalservices -Filter "TerminalName='RDP-tcp'").SetUserAuthenticationRequired(1)}

Friday, March 2, 2012

SSIS Timeout to a Web Service with Web Service Task

Quick Description:  The WebService task in SSIS throws an error similar to the one below, but the return code from the actual Web Service is 200.

The error:

[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: The Web Service threw an error during method execution. The error is: The operation has timed out.
   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection)
   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser)
   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".

If you have access to the WebService being called (or have written a proxy), the logs show that the service itself returned a successful status code 200.

This is misleading (or at least I thought it could be worded better).  It can mean that the WebService task  itself, rather than the WebService being called timed out.    This is made even more confusing by the fact the timeout is set not on the WebService task, but on the httpConnectionManager.  Default is 30 seconds.  Large amounts of data can take longer than 30 seconds to return without being a problem.  As far as I understand 300 seconds is the limit, which doesn’t seem long enough to me.

Timeout can be increased (up to 300 seconds) in Connection Managers,  yourHttpConnectionManager, properties, timeout.