Archive

Archive for November, 2013

Visual Studio SQLCompare does not Compare SQLCMD Variables correctly

November 29th, 2013 6 comments

The problem

When using the Visual Studio SQL Compare to compare your database schema to your physical database it returns heaps of false positives, where SQLCompare is suggesting that your CMD variable does not match what is on the database.

The Solution

SQLCompare uses the default or local CMD variables that you have on your project when performing the compare.

You need to ensure the default variables match the target database you are comparing against.

To change your defaults open the properties of your Database project, switch to the “SQLCMD Variables” tab and edit the defaults.

When you get your variables in alignment with your target comparison database you will get a true indication of the differences.

VN:F [1.9.22_1171]
Rating: 7.3/10 (3 votes cast)

Categories: SQL Server, SSDT, Visual Studio Tags:

exec sp_executesql nvarchar(max) truncating at 4000 characters

November 28th, 2013 1 comment

The Problem

Sometimes you may need to write dynamic SQL.

One example is writing query for running over linked server connections. You might write something like this

Create Procedure prcTest
    @start datetime
as
Begin
    Declare @sql nvarchar(max)

    set @sql = 'Insert Into #tblTemp
    select * from OpenQuery(LINKSERVER, ''Select * from dbo.tblRemote r
        inner join tblRemote2 r2 on r.Id = r2.fkId 
    .... lots more sql x 4000+ characters
    Where r.DateCreated > ''''' + Convert(varchar, @start, 106) + '''''
    '')';

    exec sp_executesql @sql

    select * from #tblTemp inner join tblOther on id = otherId

End

Assuming this has over 4000 characters it will truncate at 4000 characters, your SQL will be malformed as it is missing the closing quote ‘ and will crash.

The Resolution

So what is going on!

I’ve read everywhere on the internet about nvarchar(max) having a 2GB limit etc, but it is clearly truncating at 4000 characters.

Add in a

select Len(@sql)

to confirm that it is definitely a 4000 character limit.

So what is going on?

Implicit Type Conversion!!!

The innocent looking code adding a date to the query,

 ''''' + Convert(varchar, @start, 106) + '''''

is causing SQL Server to perform an implicit type conversion and our NVarchar(MAX) is somehow becoming an NVarchar(4000).

Change the addition of the date to the following

''''' + Convert(nvarchar(max), @start, 106) + '''''

The problem is averted and the select Len(@sql) will happily count beyond 4000.

(BTW convert(varchar, @date, 106), creates a ‘1-Jan-2000’ formatted date which resolves any issues that can occur with different regional settings on your sql server causing problems)

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

Categories: SQL Server, T-SQL Tags:

Windows 8.1 Upgrade and SSD optimisation warning

November 21st, 2013 No comments

Be warned.

When you upgrade from windows 8 to windows 8.1 it is actually a complete windows update equivalent to going from windows 7 to windows 8.

In doing so it blasts away a number of your settings that you may not have thought of.

When I installed my Samsung 830 SSD in my laptop I used Samsung magician to optimise and change the settings that are not required with SSD drives.

I did things like:

  1. Disabled Pre-fetch and super-fetch
  2. Turned off scheduled defrag
  3. adjusted my pagefile down to certain limit
  4. turn off write-cache buffer flushing

After windows 8.1 is installed all of these settings are lost. I found out much to my surprise as a defrag had been executed on my machine.

Here are a few articles that talk about all the possible optimisations, the first is from microsoft;
Windows 7 & SSD: defragmentation, SuperFetch, prefetch

Can You Get More Space Or Speed From Your SSD?

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

Categories: Hardware Issues Tags:

Citrix has stopped responding Windows 8.1, wfcrun32.exe

November 15th, 2013 No comments

The problem

Upgrade your computer to windows 8.1 and upon logging in you get an application crash from Citrix, wcfrun32.exe.

The solution

You need to upgrade to the XenDesktop 7.1, Windows Receiver 4.1.

This can be downloaded from here:


http://www.citrix.com/downloads/xendesktop/receivers/receiver-for-windows-41.html

Download, install and the application will stop crashing

VN:F [1.9.22_1171]
Rating: 6.7/10 (3 votes cast)

Categories: Uncategorized Tags:

Slow Login on HyperV – Please wait for the local session manager

November 8th, 2013 6 comments

The problem

A HyperV Virtual Machine that I had been using for over 1 year suddenly started slowing down when logging in via remote desktop.

It got to the point where it was taking 4 or 5 attempts to login and costing about 15 minutes every morning.

Edit
I have now also noticed it is taking approx 10 minutes to shut down when performing a reboot.

The Solution – Version 3 (@ Jun-2014)

Ok, this problem has been driving me crazy, and each time I apply a “fix” I find a week later that I am back to having problems.

I have left the sum total of the trouble shooting that I have done, in the previous solutions further down, but this latest solution seems to be the one.
Note: this fix is done on the guest. I have changed nothing on the hyper-v host.

The issue of slow logins was also compounded recently with really slow shutdowns. I applied some patches and needed to reboot the guest and had to wait 10 minutes. I tested this a few times, by pinging the server during a reboot and (ping server.domain.com -t), and waiting for lost packets. It would take about 8 minutes before the packets were being dropped.

After one of these reboots, I opened event viewer and looked at the “System” logs.

I noted that during the shut down there was a heap of “service x has shut down” messages, but then there was a 6 minute gap followed by a Warning message from the “Windows Remote Management” service, WinRM.

Log Name:      System
Source:        Microsoft-Windows-WinRM
Date:          19/06/2014 11:43:53 AM
Event ID:      10149
Task Category: None
Level:         Warning
Keywords:      Classic
User:          N/A
Computer:      computer.dev.com
Description:
The WinRM service is not listening for WS-Management requests. 

 User Action 
 If you did not intentionally stop the service, use the following command to see the WinRM configuration: 

 winrm enumerate winrm/config/listener
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="Microsoft-Windows-WinRM" Guid="{A7975C8F-AC13-49F1-87DA-5A984A4AB417}" EventSourceName="WinRM" />
    <EventID Qualifiers="7">10149</EventID>
    <Version>0</Version>
    <Level>3</Level>
    <Task>0</Task>
    <Opcode>0</Opcode>
    <Keywords>0x80000000000000</Keywords>
    <TimeCreated SystemTime="2014-06-19T01:43:53.000000000Z" />
    <EventRecordID>86324</EventRecordID>
    <Correlation />
    <Execution ProcessID="0" ThreadID="0" />
    <Channel>System</Channel>
    <Computer>computer.dev.com</Computer>
    <Security />
  </System>
  <EventData Name="Stopped Listening">
  </EventData>
</Event>

This service is detailed here, Windows Remote Management (Windows) – MSDN

I decided I could try living without this service, so I found the “Windows Remote Management” service disabled it and stopped it.

Now, my computer shuts down (to lost ping) in less than 10 seconds and I have not had a slow login since.

I would appreciate if anyone else can confirm this works for them.

The Solution – Version 2

On the server manager I notice the following:

SlowLoginRemoteAccess

The item to note is the “Remote Access” configuration.

Somehow I had installed Remote Access? Don’t remember doing it, but there you have it in plain sight, so I must have installed it by accident.

To fix the problem uninstall remote access by:

  1. Launch Server Manager
  2. On the Manage menu, click Remove Roles and Features.
  3. In the wizard find Remote access and deselect it
  4. Click next through the wizard and confirm removal

Since doing this my logins via mstsc / rdp are normal again.

The Solution – Version 1

Goes to show you can’t trust everything you read on the internet. Everything below was my original investigation and attempt at resolving the problem, and it seemed to fix things but ultimately it was not the solution.

I have left the details here as it may help describe other problems.

Read Hearing Investigation #1

I finally looked into the event log and found that I was getting the following error every 45 seconds.

Log Name:      Application
Source:        vmicvss
Date:          8/11/2013 9:06:37 AM
Event ID:      13
Task Category: None
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      MYSERVER
Description:
Windows cannot perform an online backup of this system because scoped snapshots are enabled. To resolve this, disable scoped snapshots by creating the following registry value on this computer:
 	PATH: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\SystemRestore\
 	DWORD: ScopeSnapshots
 	Value: 0
 Incorrectly editing the registry may severely damage your system. Before making changes to the registry, you should back up any valued data on the computer.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="vmicvss" />
    <EventID Qualifiers="49154">13</EventID>
    <Level>2</Level>
    <Task>0</Task>
    <Keywords>0x80000000000000</Keywords>
    <TimeCreated SystemTime="2013-11-07T22:06:37.000000000Z" />
    <EventRecordID>81122</EventRecordID>
    <Channel>Application</Channel>
    <Computer>MYSERVER</Computer>
    <Security />
  </System>
  <EventData>
  </EventData>
</Event>

A quick scout around the internet for this error found this article, Scoped Snapshots vmicvss Error 13 on Server 2012, Windows 8

Incorrect solution #1

Edit – Nov 2013
Sorry, this has not resolved my slow login problems. It has has help improve the performance of the box sligthly and I have definitely stopped the errors in my event log, but it has not resolved the slow login screen. I will keep trying and if I get a resolution I will bump the vmicvss issue to its own post and put the definitive solution here.
———– End Edit———–

The fix is in two parts:

  1. Create the registry key that the error log is suggesting is a problem

    Save the following into a .reg file and then execute on your computer to stop the error occurring.

    Windows Registry Editor Version 5.00
    
    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\SystemRestore]
    "ScopeSnapshots"=dword:00000000
    
  2. Turn off HyperV Backup

    The registry change will stop the errors but it will not stop “Information” event log entries being written every 45 seconds.
    You need to login to the HyperV host and edit the settings of you VM.
    Turn off – Backup (Volume Snapshot)

    HyperVBackup

I also cleared my event log and things seem to be running much more smoothly .

P.S see the comments. this may be a side effect of Dell Equalogics auto-snapshot software.

VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)

Categories: Servers Tags:

CKEditor HTML view greyed out

November 3rd, 2013 No comments

The Problem

When running CKEditor (3.6.4 for ASP.NET) the editor loads but the HTML view is greyed out.
Switch to source mode and the HTML code is there but it does not render in HTML View

Diagnosis

On further investigation this error only appears to occur in Internet Explorer 10, (IE10) when the browser is running in quirks mode.

To reproduce open the following URL which takes you to a earlier demo of CKEditor 3.6.4

http://rev.ckeditor.com/ckeditor/trunk/7500/_samples/skins.html

In Internet Explorer do the following:

  1. Press F12 – to launch the developer Tools
  2. On the menubar – Look for Document Mode and change to Quirks mode

IEQuirksMode

When in quirks mode the control greys out. Switch back the Standard mode and it works.

The Solution

You can control which mode the browser runs in, by adjusting the

VN:F [1.9.22_1171]
Rating: 4.0/10 (1 vote cast)

Categories: ASP .Net Tags:

SSRS – Default Date Parameter not showing

November 3rd, 2013 No comments

The Problem

I have an SSRS Report with a default date parameter.
The default date parameter appears grayed out and does not allow me to enter the date

The problem in detail

In an SSRS report I wanted to have a Default date parameter.

The report had two parameters:

  1. Person Id – Free Text Number field, Mandatory
  2. End Date – Date Field – default to Now

The default value for the date parameter can be set by a default expression like:
=Today()

When this report is opened the cursor is focused in the “Person ID” field, and the End Date is grayed out.
Enter a PersonId and either tab out of the field or click view report and the End date field will become available.

The solution

The problem can be resolved by changing the order of the parameters.

By changing the order from:

  1. Person ID
  2. End Date

to

  1. End Date
  2. Person ID

It appears that placing a text style parameter that requires user input prior to the default date field, stops the parameter execution and leaves the field grayed out.

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

Categories: Reporting Services Tags: