Monday, June 28, 2010

More SQL XML, Making It Easier...

Well, I've previously used stored procedures to execute SQLXML queries, pulling the XML out using a standard SqlDataAdapter and concatenate the returned rows via a StringBuilder.

There is, however, a different mechanism to use:
  • It does throw a large number of exceptions, so your code needs to be clean.
  • You will need to remove System.Data.SqlClient because the new DLL replaces the namespaces found in the original DLL.
Example code:

using Microsoft.Data.SqlXml;

string connectionString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
SqlXmlCommand queryCommand = new SqlXmlCommand(connectionString);
queryCommand.CommandText = @"SQL * FROM something FOR XML";

SqlXmlParameter queryParameter = queryCommand.CreateParameter();
queryParameter.Value = 2860;
queryCommand.RootTag = "root";
queryCommand.XslPath = Server.MapPath("~/XSLT/formatter.xslt");
queryCommand.ClientSideXml = true;
XmlDocument xmlChartData = new XmlDocument();
xmlChartData.Load(queryCommand.ExecuteXmlReader());


Friday, June 25, 2010

Listing Table Column Names On One Line

I wanted to list out all the names of a table on one line, separated by tab characters, so that I could do a simple copy-paste from the SQL Server Management Studio into an Excel sheet - having each column name place itself conveniently into the next Excel column.

The code I came up with is a slight modification of a previous post:
select STUFF(
(SELECT char(9) + name FROM
(SELECT c.name
FROM syscolumns c inner join sysobjects o on c.id = o.id
where o.name = 'your-table-name') AS Y
--ORDER BY names -- optional sorting of the column names
FOR XML PATH('')),
1, 1, N'')



Wednesday, June 23, 2010

Monday, June 21, 2010

Prince Of Persia For iPhone And iPad Is Awesomely 8 Bit

Having run across the re-release of the original Prince Of Persia on the iPhone App Store (under Featured) I found that it can be sync'd across to the iPad and then uses a whole other set of higher resolution graphics - making it perfect for full size retro gaming.

One post I found, after search for the age old "can't pick up sword!!!" problem was this:
However, click the "Controls" option on the main menu shows that the "action" button is in fact anywhere on the screen, that isn't already one of the four Up, Down, Left or Right buttons.

Here's some visual goodness for you to rest your eyes on - and remember, the one PoP app works on both iPhone and iPad, with improved graphics on the iPad!...

Prince Of Persia on iPhone...



Prince Of Persia on iPad...

Sunday, June 20, 2010

L2B 2010 - It's Over!

It was long and it was kinda cold, actually, but it warmed up once we got to Brighton - yeah, afterwards!

Pictures from the day...



Here's the route we eventually took...


View L2B 2010 in a larger map

London To Brighton 2010

This Father’s Day, Sunday 20th June, I am doing (again) the BHF London to Brighton 58 mile bike ride in a Morph Suit! It’s a grueling challenge filled with blood, sweat, tears, burgers, beer, steep hills and numb-bum-syndrome.

It really is a worthy cause – and I will look a complete prat - so please, if you can, follow this link and donate just a little towards my target for the British Heart Foundation

http://original.justgiving.com/matthewwebster

Click here to see the estimated route!




View L2B2010 in a larger map


Footnote: Yes, numb-bum-syndrome is real: http://nutritionfitnesslife.com/numb-bum-syndrome/

Friday, June 18, 2010

Calculate XML Element Depth

In XPath (from the excellent D.Pawson site):
In LINQ:
  • int depth = element.Ancestors().Count();

Thursday, June 17, 2010

Direct SQLXML Access And XSLt Using ADO.NET

If you want a quick and easy way to directly access a SQL 2005 DB, read the content in XML and render it from your website, here's how:
Please see other links on my blog for how to do more specific things; this is tech I use occasionally, but sometimes in detail.

Wednesday, June 16, 2010

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

Tuesday, June 15, 2010

My Favourite Chrome Extensions

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

Thursday, June 10, 2010

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:

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.

Tuesday, June 08, 2010

Google Proxy Setting

If you are having trouble accessing the internet via Google's Chrome, try the solution suggested here:
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=

Sunday, June 06, 2010

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...

Tutorials:
Discussions:
Of course, once you have your caching up and running you'll want to test it before sticking it in the wild:
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

Friday, June 04, 2010

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":

Thursday, June 03, 2010

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:

Wednesday, June 02, 2010

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: