Performing Queries With T-SQL DBs With Illegal Characters In Their Name

I've been trying to connect to a DB on a completely different server, using Microsoft SQL Server Management Studio, and discovering a big problem. The SQL Server I'm trying to query has a '-' character in it's name.

This is overcome using two methods:

1. Add the SQL Server to the local (on my development machine) SQL Server linked servers list.
   
   This is done by:
   
   Using the Object Explorer and opening "Server Objects -> Linked Servers", right clicking and selecting "New Linked Server".
   
   The important next step is that the "Linked server" field is filled out with the SQL Server name only, eg: SOMELIVEBOX-SQL5 and then the "SQL Server" radio button is selected.
   
   Next, on the left, select "Security" and choose "Be made using this security context:" and fill out the SQL Server login details.
   
   
2. Then the query, and this is only relevant if the SQL Server name has an illegal character (like '-') in it.
   
   Lets say the table is called "MyTable", is in a database called "MyDB", is in the server mentioned in point 1 and we want all the records from it...
   
   Open a new query window from inside the local database and type:
   
   SELECT * FROM [SOMELIVEBOX-SQL5].MyDB.dbo.MyTable

My Favourite Chrome Extensions

Yep, cos I keep forgetting, each time set up a new PC....

• Speed Dial
  https://chrome.google.com/extensions/detail/dgpdioedihjhncjafcpgbbjdpbbkikmi?hl=en-gb
  https://chrome.google.com/webstore/detail/speed-dial-3web/lceefillmbhhileboicaeakgcikdocmm?hl=en-US
• InstaPaper
  https://chrome.google.com/extensions/detail/acgdjjilmhiofacmdnmmlndeokamkkcl?hl=en-gb
• Tube Service Status Updates
  https://chrome.google.com/extensions/detail/dcbnpmjhmjhpaegikhpjlaemjiilnkgb?hl=en-gb
• CoolIris
  https://chrome.google.com/extensions/detail/noocneohefmdhonidldnlhaainpiomkp?hl=en-gb
• Fastest Chrome
  https://chrome.google.com/extensions/detail/mmffncokckfccddfenhkhnllmlobdahm?hl=en-gb
• Downloads
  https://chrome.google.com/extensions/detail/jfchnphgogjhineanplmfkofljiagjfb?hl=en-gb
• Quick QR
  https://chrome.google.com/extensions/detail/bcfddoencoiedfjgepnlhcpfikgaogdg?hl=en-gb
• GMail Checker Plus
  https://chrome.google.com/extensions/detail/gffjhibehnempbkeheiccaincokdjbfe?hl=en-gb
• Join Tabs
  https://chrome.google.com/webstore/detail/binjiceocgbfooocmheaenmmcominbpe
• XMarks Bookmark Sync
  https://chrome.google.com/webstore/detail/ajpgkpeckebdhofmmjfgcjjiiejpodla
• Page Snooze
  https://chrome.google.com/webstore/detail/bmhnhpfchoeofnjoobbhgihdkhllnfbc
• Print Friendly
  https://chrome.google.com/webstore/detail/jmplllfmgpkogegnjnkecmkdbeaeheop
• youR Here
  https://chrome.google.com/webstore/detail/fkandkgadbpobooanbmiiinlelnhbabm
• Edit This Cookie
  https://chrome.google.com/webstore/detail/edit-this-cookie/fngmhnnpilhplaeedifhccceomclgfbg

Can't Connect To Local SQLEXPRESS

This started occurring the other day and I was confused as to why the local database would not let me connect via standard Windows Authentication.

The answer turned out to be simply that the database had not been started as a Windows Service. This was because the Log On account it was trying to use did not have appropriate permissions. Not having another machine to check the correct settings, I decided that in the Services snap-in (Start -> Run -> services.msc -> SQL Server (MSSQLSERVER) & SQL Server (SQLEXPRESS)) I went to the Log On tab, selected "Local System account", hit "Apply" and then "Start" under the General tab.

For those two services, on my machine at least, this got everything working again. I was only able to discover this solution after googling and ending up here:

• http://social.msdn.microsoft.com/forums/en-US/sqlsetupandupgrade/thread/4d2ed764-e196-404c-a610-e10322b002cb/

In case the post with the answer disappears, here it is in full:

I encountered the exact same error. My work around to get the server back up and running is as follows. This applies to both the engine and the agent. The bug in this patch didn't affect the other SQL related services.

I changed the service account from the normal one to one that is local administrator to the server (me).

I was then able to start the services.

I went into the SQL Server 2005 Surface Area Configuration tool and changed Database Engine -> Remote Connections to use Local connections only.

I restarted the engine service.

I then changed it back to using Local and remote connections Using TCP/IP only.

I restarted the engine service.

I then changed both services back to their normal service account and all is well.

I could not get the patch to apply under any circumstances or configuration that I tried and have given up on it in hopes that MS releases a new one for this real quick.

Google Proxy Setting

If you are having trouble accessing the internet via Google's Chrome, try the solution suggested here:

• http://www.google.co.uk/support/forum/p/Chrome/thread?tid=68af55240f3a69e7&hl=en

Basically, (for Windows) make your Chrome shortcut look like this:

• Target: "C:\Documents and Settings\\Local Settings\Application Data\Google\Chrome\Application\chrome.exe" --proxy-server=

Load Balanced Session State Partitioning

Recently doing some research into getting session state management faster and more stable in an enterprise, load balanced environment. Not claiming to be any more knowledgeable than I was before, but definately more read...

Products:

• http://code.msdn.microsoft.com/velocity/
  http://en.wikipedia.org/wiki/Velocity_(memory_cache)
  http://www.microsoft.com/downloads/details.aspx?FamilyId=B24C3708-EEFF-4055-A867-19B5851E7CD2&displaylang=en
• http://memcached.org/
• http://www.alachisoft.com/ncache/index.html
• http://www.scaleoutsoftware.com/products/state_server.php
• http://sharedcache.codeplex.com/
  http://www.sharedcache.com/

Tutorials:

• http://blog.maartenballiauw.be/post/2007/11/ASPNET-load-balancing-and-ASPNET-state-server-(aspnet_state).aspx
• http://blog.maartenballiauw.be/post/2008/01/ASPNET-Session-State-Partitioning.aspx
• http://blog.maartenballiauw.be/post/2008/01/24/ASPNET-Session-State-Partitioning-using-State-Server-Load-Balancing.aspx
• http://en.aspnet-bhs.info/post/State-Server-Partitioning.aspx
• http://www.theserverside.net/news/thread.tss?thread_id=47330
• http://www.codeproject.com/KB/session/ASPNETSession.aspx
• http://blogs.msdn.com/velocity/archive/2008/06/05/how-to-use-session-store-provider-microsoft-project-code-named-velocity.aspx

Discussions:

• http://stackoverflow.com/questions/2063421/scaleout-vs-ncache
• http://forums.asp.net/t/1030680.aspx
• http://forums.asp.net/t/1210004.aspx
• http://stackoverflow.com/questions/489270/caching-and-session-state-management-using-microsoft-velocity-and-memcached
• http://stackoverflow.com/questions/856959/which-distributed-cache

Of course, once you have your caching up and running you'll want to test it before sticking it in the wild:

• http://predicatet.blogspot.com/2009/09/download-microsoft-web-application.html
• http://support.microsoft.com/kb/231282
• http://www.west-wind.com/presentations/webstress/webstress.htm
• http://ask-leo.com/can_i_monitor_or_control_the_bandwidth_used_on_my_home_network.html
• http://www.softwareqatest.com/qatweb1.html#LOAD

And some further reading, which may get bolstered the longer this post stays alive:

• What a 'Cache' is: http://en.wikipedia.org/wiki/Cache
• "Caching in the distributed environment": http://msdn.microsoft.com/en-us/library/dd129907.aspx
• Memcached Vs Velocity Showdown: http://www.briandrought.com/blog/?p=29

And for those who want to know, my own comparison (please be aware that I have not exhaustively tested all options, this is based on research and some usage):

NCache

• Distributed
• Partitioaned
• Replicated
• Remote clients available
• Expensive
• Failover
• Clustered
• Free developer edition not appropriate for live environments

Memcached

• Free
• Open source
• Distributed
• Not partitioned
• Not replicated
• Servers unaware of each other
• Not clustered
• Least recently used model

Velocity

• Sparsely documented

ScaleOut

• 
  

Rediscovering A Blog Entry On The Button Element

Just seen this and though it warranted some attention - the Button HTML element looks rather more powerful than the input submit element, but "requires a little love":

• http://particletree.com/features/rediscovering-the-button-element/

Get Notified About Public Holidays

So, using Outlook (in this case, 2007) you can add lots of public holidays to your calendar very easily:

• Tools -> Options -> Calendar Options -> Add Holidays

Easy...

T-SQL IsNullOrEmpty

Just needed to do the T-SQL equivalent of String.IsNullOrEmpty(), so went looking and found these:

• http://weblogs.sqlteam.com/jeffs/archive/2007/09/27/sql-nullif-function.aspx
• http://community.devpinoy.org/blogs/dotnetrandz/archive/2008/11/11/isnullorempty-with-a-twist-on-t-sql.aspx

How To List The Columns In Your Table

I wanted to inspect, in T-SQL code, the list of columns in a table I had created. This is how I did it:

select c.* from sysobjects o inner join syscolumns c on o.id = c.id

where o.xtype = 'U' and o.name = 'myTableName'

And for reference, a handy page on the T-SQL system tables:

• http://msdn.microsoft.com/en-us/library/aa260604(v=SQL.80).aspx

Concatenating String Fields In T-SQL

Lets say you've got a table called 'metaproperty' with an NVARCHAR(50) field called 'names'. How do you get all the values of the 'names' field concatenated into one string, separated by commas?

Like this:

select STUFF(

(SELECT N',' + names

FROM (SELECT * FROM metaproperty) AS Y

--ORDER BY names

FOR XML PATH('')),

1, 1, N'')

The ORDER BY is optional, as are any DISTINCTs you might want to shove in...

PIVOT

Found these pages quite interesting but not managed to make use of it yet:

• http://geekswithblogs.net/veerji/articles/45464.aspx
• http://www.tsqltutorials.com/pivot.php

It's about how to use PIVOT. Have a look round his site and look for the UNPIVOT pages, much geeky fun to be had within a T-SQL server (2005 onwards.)

[UPDATE] The key thing I've found (as highlighted in the first link) is that the PIVOT statement operates upon the fields returned in the FROM clause, NOT the SELECT clause. This is important because it means, if you have a JOIN or two, you probably have a lot more fields to quote in your clause than you think. The solution, I found, is to nest the original query inside the FROM( ) AS virtualtable thereby reducing the pivot to only the fields you're concerned with.

eg:

select *

from

(

select

distinct top 100

v.pointid, v.doublevalue, p.displayname

from [property] p

inner join pointvalue v on p.propertyid = v.propertyid

inner join point pt on v.pointid = pt.pointid

where v.pointid in (select top 5 p.pointid from point p where p.instanceid = 36132)

) virtualtable

pivot

(

sum(doublevalue)

for [displayname] in ([Low Price], [High Price])

) as alias

Virtual Machines

My foray into the Mac world continues, though this post isn't specifically a Mac oriented thing. I have been looking for virtual machine applications...

Firstly, what Wikipedia has to say on the subject:

• http://en.wikipedia.org/wiki/Virtual_machine#List_of_virtual_machine_software
• http://en.wikipedia.org/wiki/Comparison_of_platform_virtual_machines

And the virtual PC applications I have used, or rather, had closer experience of than the vast array of virtualisation software out there:

• VirtualBox
  http://www.virtualbox.org/wiki/VirtualBox
  http://en.wikipedia.org/wiki/VirtualBox
• Hyper-V
  http://www.microsoft.com/hyper-v-server/en/us/default.aspx
  http://en.wikipedia.org/wiki/Hyper-V
• VirtualPC
  http://www.microsoft.com/windows/virtual-pc/default.aspx
  http://en.wikipedia.org/wiki/VirtualPC
• BootCamp
  http://www.apple.com/macosx/compatibility/
  http://en.wikipedia.org/wiki/Boot_Camp_(software)
• Parallels
  http://www.parallels.com/uk/products/desktop/
  http://en.wikipedia.org/wiki/Parallels_Desktop_for_Mac
• Parallels Workstation
  http://www.parallels.com/uk/products/desktop/pd4wl/
  http://en.wikipedia.org/wiki/Parallels_Workstation
• VMWare
  http://www.vmware.com/
  http://en.wikipedia.org/wiki/VMware

And finally, my personal opine on the subject:

• VirtualPC - Quite good, does what it says on the tin but hogs a lot of system resources. Will run non-Windows OS's, but probably is better running Windows. Discontinued, I understand, in deference to Hyper-V.
• Parallels - Not the same kind of virtual machine as VirtualPC, in that it's expressly designed to run Windows on a Mac, but it does this beautifully. It can startup Windows which has been installed into Parallels or BootCamp and run them side by side.
• BootCamp - Runs Windows on a Mac as a stand-alone OS, in that Mac OS X is not running at the same time. Allows the Mac to operate as a Windows PC and does it pretty much better than a regular PC can IMHO.
• VMWare - Basically just like VirtualPC, but many would have it as being more stable and not Microsoft.

Web.config Element Positions

One thing that bugs me from time to time is that I cannot remember where the hell a web.config element is supposed to go. Some (ok, all) of them are extremely location sensitive. Some don't really care as long as certain elements appear before, but not necessarily immediately before, them.

Here are some which often (in the grand scheme of things, but not every day) annoy me and links to find others which might cause problems:

• assemblies http://msdn.microsoft.com/en-us/library/37e2zyhb.aspx

I Don't Know Nout About Fonts, But I Know This

This is awesome - I've seen it tweeted recently, a lot, but it really is impressive:

• http://code.google.com/webfonts/preview

Google Font Directory :)

Stuff You (I) Should Know

The following is a list of APIs and tools which I consider the current state of the art in .NET development (though not exclusively .NET) and that I really should know a lot more about. This should pretty much be complete right now, though I have a nagging feeling I missed one.

• OpenRasta
  http://trac.caffeine-it.com/openrasta/wiki/Doc
• ASP.NET MVC 2
  http://www.asp.net/learn/mvc/
  http://channel9.msdn.com/posts/matthijs/ASPNET-MVC-2-Ninja-Black-Belt-Tips-by-Scott-Hanselman/
• SparkView
  http://sparkviewengine.com/documentation/syntax
• Castle
  http://www.castleproject.org/activerecord/documentation/v1rc1/index.html
  http://gojko.net/2008/11/04/dependency-injection-with-castle-windsor-video/
  http://using.castleproject.org/display/IoC/Home
  http://using.castleproject.org/display/IoC/Fluent+Registration+API
  http://www.castleproject.org/castle/download.html
  http://api.castleproject.org/
  http://blog.coreycoogan.com/2009/11/06/castle-windsor-tutorial-in-asp-net-mvc/
• NHibernate
  http://nhforge.org/
  http://community.jboss.org/wiki/NHibernateforNET
  http://www.nhforge.org/doc/nh/en/index.html
  http://blogs.hibernatingrhinos.com/nhibernate/archive/2008/04/01/your-first-nhibernate-based-application.aspx
  http://www.ayende.com/hibernating-rhinos.aspx
  http://nhforge.org/wikis/howtonh/your-first-nhibernate-based-application.aspx
• MvcContrib
  http://mvccontrib.codeplex.com/Wikipage
  http://mvccontrib.codeplex.com/releases/view/43560
• NUnit
  http://www.nunit.org/
• Ruby On Rails
  http://ironruby.net/About
• Fluent Programming
  http://en.wikipedia.org/wiki/Fluent_interface
• NerdDinner
  http://nerddinnerbook.s3.amazonaws.com/Intro.htm
  http://www.hanselman.com/blog/ExtendingNerdDinnerExploringDifferentDatabaseOptions.aspx
  http://www.hanselman.com/blog/ExtendingNerdDinnerAddingMEFAndPluginsToASPNETMVC.aspx
  
• Managed Extensibility Framework "MEF"
  http://mef.codeplex.com/
  http://code.msdn.microsoft.com/mef
  http://blog.maartenballiauw.be/category/MEF.aspx

The following list is not complete, won't be for some time and may require time travel to keep reasonably accurate - list of best practice advice/docs:

• http://weblogs.asp.net/rashid/archive/2009/04/01/asp-net-mvc-best-practices-part-1.aspx#prg
  

And some videos which I hope will be helpful:

• MVC
  http://video.google.com/videoplay?docid=6197789949116343290#

Footnote: This needs reorganising into a list of tech, each with a list of links and FAQ/Quickstarts.

The Engineer's Code

Error 107 ERR_SSL_PROTOCOL_ERROR

Needing to apply SSL to an existing website (on my Windows Server 2003 development machine) I recently opened the properties on the website (under IIS, Web Sites), clicked Directory Security and Server Certificate and chose to apply/replace the certificate from an existing site/certificate.

This did not have the required effect, when requesting the page in the browser:

• Using HTTP://
  The page must be viewed over a secure channel
• Using HTTPS://
  Error 107 (net::ERR_SSL_PROTOCOL_ERROR): Unknown error IIS
• And occasionally
  Bad Request (Invalid Hostname)

Essentially, this is because I was trying to reuse an existing certificate on a web site that had a different name. The solution is basically to replace the certificate with a valid certificate either from a trusted Certificate Authority (CA) or to use the SelfSSL.exe.

All of this is documented here, the first link being how to use the SelfSSL to get the job done on a development box:

• http://springboardpillow.blogspot.com/2009/03/creating-certificate-in-iis.html
• Securing a web service with SSL: http://msdn.microsoft.com/en-us/library/aa302409.aspx
• Configuring your web server to use SSL: http://support.microsoft.com/kb/324069
• Securing XML web services created using ASP.NET: http://msdn.microsoft.com/en-us/library/w67h0dw7(VS.71).aspx
• Renewing a wildcard SSL certificate in IIS 6: http://stackoverflow.com/questions/1389941/renewing-a-wildcard-ssl-certificate-in-iis-6-1024-to-2048-bit
• Easily secure custom web services with .NET: http://articles.techrepublic.com.com/5100-10878_11-5779491.html
• HTTPS Web Request and Web Response: http://www.tech-archive.net/Archive/DotNet/microsoft.public.dotnet.languages.csharp/2005-06/msg00038.html

It is important to note that when applying SSL certificates, especially via SelfSSL, that you open the site's Properties dialog:

• Click Web Site tab
• Click Advanced button
• Under Multiple SSL identifies for this Web site select the site's IP row
• Click Edit button
• From the IP address drop down menu select the IP address of that site
• Click Ok, Ok, Ok

This associates the SSL certificate with the site's IP and ensures that there are no conflicts. Click the Help button in this window for more information.

It's "Creative"

Debugging SQL And The Fun Attached To That

Having come across this problem in Commerce Server 2002 recently:

Server Error in '/' Application.

Source:Microsoft OLE DB Provider for SQL ServerDescription:Incorrect syntax near '('.Source:Microsoft OLE DB Provider for SQL ServerDescription:Invalid object name 'MSCS_CatalogScratch.dbo.Catalog__Query__Results__for_spid__304'.Source:Microsoft OLE DB Provider for SQL ServerDescription:Invalid object name 'MSCS_CatalogScratch.dbo.Catalog__Query__Results__for_spid__304'.

I discovered that the stored procedure failing was called "ctlg_GetResults_for_SingleCatalog" but how to work out where?

This page came in useful: http://support.microsoft.com/kb/316549

Basically, it's stepping through SQL Stored Procedures in Visual Studio. The account you're accessing the DB requires execute permissions, etc, of course, but from there you should be able to step through the process just like code. Well, almost.

Always Check IIS

This is a lesson we all learn over and over again, I believe.

Recently trying to get the files in the MVC web app's directory '/Scripts/*' to be accessed I was constantly frustrated in not being able to load simple .js files, etc, in the browser page.

In this case, it was because Default Web Site in IIS already has a Scripts virtual directory setup. This, of course, does not behave as a normal subdirectory would and was stopping access to the files within it by the browser. This is expected, unless you've forgotten that fact because you've been working on WinSrv2k3 for too long...