Category Archives: Annoyances

MS SQL TSQL PadLeft

Ever wanted a PadLeft for SQL server
Here’s the next best thing, a scalar function we wrote and implemented into all our databases
Enjoy

Windows 8 to Windows 8.1 with redirected user profile folders

You’re here probably because you received this error

Sorry, it looks like this PC can’t run Windows 8.1. This might be because the Users or Program Files folder is being redirected to another partition

The Solution in a nutshell

  • Edit registry keys to re point to system drive
  • Add New Admin account to system
  • Login as new Admin account
  • edit user registry keys to re point to local system users folder
  • Run batch file to create junctions between local system user folders and redirected user folder
  • Restart, login as admin and run windows update to 8.1
  • Once complete edit registry to re point chosen user profile folders back across to redirected drive
  • The user folders that are redirected again must have the AppData hidden folder folder copied across to the redirected user folder fro the system drive

The solution in more detail
Basically we need to trick the updater that the profiles are local. We do this in a few steps

  1. Edit some registry keys
  2. Create a new admin account, restart and login to it
  3. Edit some more registry keys
  4. Create some junctions to point from local profiles to actual profiles
  5. Restart login again as the admin account and run updates

So let’s get started
You might want to save this page as a “.mht” file to the root of your C: so you can refer back to this as you progress
Edit registry
Run regedit as admin and goto
HKLM\SOFTWARE\Microsot\Windows NT\CurrentVersion\ProfileList
Mine looks like this
ProfileList HKLM
Edit the ProfilesDirectory Key back to “%SystemDrive%\Users”
ProfilesDirectory HKLM
Create a new account and mark is as administrator
Restart and login with the new account
Go back into an admin regedit and adjust the remaining keys so they look like this
RESET ProfileList keys HKLM

As you can see I have lots of local logins (I develop apps and as such lots of services login to my machine) so I built a batch file to create the junctions from local to actual user profile folders
For users with no gaps in their name
mklink /J C:\Users\Andre E:\Users\Andre
for users with gaps
mklink /J “C:\Users\.NET v4.5” “E:\Users\.NET v4.5”
My Batch file ended up here c:\MKLinks.bat and looked like this
mklink /J C:\Users\Mcx1-HOME-PC E:\Users\Mcx1-HOME-PC
mklink /J C:\Users\UpdatusUser E:\Users\UpdatusUser
mklink /J C:\Users\Andre E:\Users\Andre
mklink /J “C:\Users\Classic .NET AppPool” “E:\Users\Classic .NET AppPool”
mklink /J “C:\Users\.NET v4.5” “E:\Users\.NET v4.5”
mklink /J “C:\Users\.NET v2.0” “E:\Users\.NET v2.0”
mklink /J “C:\Users\.NET v4.5 Classic” “E:\Users\.NET v4.5 Classic”
mklink /J “C:\Users\.NET v2.0 Classic” “E:\Users\.NET v2.0 Classic”
mklink /J C:\Users\Default E:\Users\Default
mklink /J C:\Users\Public E:\Users\Public

Now we are ready to execute the batch file, so open an elevated command prompt, navigate to the location you saved the batch file and run it.

Next we must adjust the “ProfileImagePath” string value in each user profile sub key of ProfileList (the keys typically start with S-1-5-xx-xxxxxxxxxx-xxxxxxxxxx-xxxxxxxxxx-xxxx) so they point back to the local system drive (C:) like this one

Return Profiles back to system drive locations

Once you are sure you have completed these instructions restart once more, login as the admin account and run windows update.

Once completed, every profile will be put back onto the C:\Users and your folder junctions will have been replaced by these user folders.
There is a simple solution to bring things back across to your redirected drive, editing the users registry key and copying the hidden AppData folder from the C:\Users\Username folder to the target user profile folder on the redirected drive you are trying to use.

I ended up leaving all of my profiles on C and only redirected my profile, which incidentally was far too large to reside on the C:

asp.net ReportViewerControl The size of the request headers is too long

Bad Request – Request Too Long

HTTP Error 400. The size of the request headers is too long.

When you use the “Microsoft.ReportViewer.WebForms”control in asp.net

You’ve eaten too many cookies Reserved.ReportViewerWebControl.axd

If like me you use the report viewer control to display reports from SSRS to the world wide web you may have come across an ever increasing number of cookies from the control.
I don’t really have time to look into this in any detail as im working to a deadline at the moment and as such need to resolve this asap.
I think this is perhaps something to do with the fact I am using masterpages with iframes to both separate the CSS and pull out the parameters into an intermediate step.

Anyway my solution was to wipe the cookie from the request cookies collection so they dont get a chance to stack up.
Create class in you App_Code and past the following code into the file

you could adapt this to only delete if the number of cookies exceeded a certain level

SSMS SQL Server Management Studio Refresh Schema

Refreshing the intelli-sense (Intellisense) for the current connection to a given database can be a bug bare

There is nothing more frustrating than making additions, or changes in design to tables/views and having the management studio intellisense not pick-up the changes once you have made them. This is because the SSMS schema refresh doesn’t occur real-time not does it update local schema changes to those successfully committed to the db while you work, it occurs on close and open of the IDE.

The easiest way to refresh the schema on any connection (also note each new window is a separate connection and as such you need to refresh each window)

You can either

1) Go to Edit -> IntelliSense -> Refresh Local Cache and
OR
2) Hit Ctrl+Shift+R

Hope this helps those with the same issue

SQL 2008 SSRS Web Service access from .NET

When you try to access a report server web service to execute code you get an error similar to, where the scheme or header varies a tiny bit

The HTTP request is unauthorized with client authentication scheme ‘Basic’. The authentication header received from the server was ‘Negotiate,NTLM

Basically my situation is that we have a MS 2008 Server running SSRS outside of our domain in the DMZ. However we need to execute code on a domain machine that will connect and run over 100 reports on the SSRS Server, then dump them on a share in our domain in excel format.

To get around the negotiation problem you need to make sure the SSRS server is allowing connections configured using basic authentication

Find the file

rsreportserver.config

This is usually buried in the install folder

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer

Then change the authentication to support your desired connection authentication type

More info at MSDN
Once you have done that you should be good to connect.
Here is some sample code to get you started with connecting to your web service and pulling back a list of items

unable to download web platform product list

Unable to download the Web Platform product list from . Check your network connection and try again. If the problem persists, report the issue on the Web Platform Installer forum at: http://go.microsoft.com/fwlink/?LinkId=145244.

Applies to Web platform installer 3 on server 2008

Verify you can browse to http://www.microsoft.com/web/webpi/3.0/WebProductList.xml

If you can then add the following registry key

HKLM\SOFTWARE|Microsoft\WebPlatformInstaller

Add string value (reg_sz) named “ProductXmlLocation”

Value http://www.microsoft.com/web/webpi/3.0/WebProductList.xml

Re run the Web Platform Installer and all should be well

.NET Entity Layer The member with identity ‘Result’ does not exist in the metadata collection

“The member with identity ‘Result’ does not exist in the metadata collection.
Parameter name: identity”

If you have stumbled across this error when trying to perform a transaction against your DB or any other action via your entity layer. If it is indeed a Microsoft SQL Database or any other database I would highly recommend you disable triggers on the affected tables and re run your test.

This error is the entity layer running home to its default error when either the error cannot be translated or the what ever is being returned from the transaction cannot be understood.

So if you stumble across this error, its because the entity layer is performing an operation that is causing a secondary separate operation which is where the underlying error is kicking back from, either because an error was raised or because the return is in an unexpected format.

user control is ambiguous in the namespace ‘ASP’

This one was a particular pain.

Long story short try this in your web config

Set batch=”false” under compilation

This will allow you to drill further into the problem, its not a particularly desirable attribute to set in production.