An annoying limitation of the SQL Server REPLACE function is that it doesn't operate on TEXT data type columns. The CAST function does however. With SQL Server 2005 you can set the size to "MAX" otherwise 2000 and lower you'll have to specify "8000".

REPLACE(CAST(TextColumn as VARCHAR(MAX)), 'find text', 'replace text') 

I did find another solution on the SQL Team site here.

I was making some changes to a client's website recently and found static repeated HTML in quite a few places. Their internal developer had put something together than ended up going live!

I decided to create put the HTML in a SQL DB and use LINQ to SQL to retieve the content on demand. All ready to go live and website being hosted somewhere else on their network with slow access to their DB server. No problem, export the content as XML and use LINQ to XML to retrieve.

I ended up getting the content data into a DataTable with a table name of "Content" and then calling the data table's WriteXML method to output as XML. The format was:

<?xml version="1.0" standalone="yes"?>
<DocumentElement>
  <Content>
    <PageName>HOME</PageName>
    <Text>blah</Text>
  </Content>
</DocumentElement>

Then you can write a function that contains your LINQ to XML:

Very cool! And much easier that typed data sets.

A few of our clients have only 1 or 2 PCs (no server) and as our apps all use SQL Server as a backend data store I have often attempted to configure the firewall to allow SQLServer traffic. And failed. I decided to find out the correct way to do it the other day.

1. Add an exception for C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlserver.exe. Assuming the first instance and installed on the C Drive.

2. Add an exception for C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe.

3. Ensure that the sql browser service is started and set to start automatically.

4. Via Surface Area Configuration for Services and Connections, allow local and remote connections.

That's it! Pretty easy and logical in the end. 

I was working with a website the other day and I needed to know the background color of a TH row that was coming from an image. I tried unsuccessfully to use Paint and Paint.NET to get the RBG without success. 

Then I came across Instant Eyedropper. Awesome little tool that when activated allows you to move a cursor around the screen and it tells you the colour underneath the cursor. 

Got stuck into LINQ to XML the other week and I thought wouldn't it be great if this could be strongly typed somehow. Of course the MS XML Team were WAY ahead of me. You can find the article at their blog here.

It's only in preview Alpha 0.2 Refresh at this stage (here) so I'm a little hesitatent to install on my regular machine. What the heck, if you're not living on the edge, you're taking up too much room. Right?

Scott Hanselman also has some interesting thoughts on LINQ to Everything.

LINQ for us was a huge productivity improvement as we didn't have to generate stored procedures anymore for our CRUD database operations. Because we had a rule that ALL data access was via stored procedures, everytime we had a new processing operation or new search situation, we either had to adapt an exisitng stored procedure or create a new one. Then we had to create a method in our data layer to make the call to the stored procedure.

Now with LINQ we can create a new method in our data layer. Saves time and the generated SQL is not too bad. One thing going against LINQ is that it's very easy to start putting LINQ queries in your UI (aka inline SQL from the old days).

Learning LINQ is a challenge. More specifically doing things that come naturally in TSQL are challenging. I have found it quicker to create a few stored procedures to do some complex queries and call them through the data context.

Never fear LINQPad is here. Very similar to management studio you can connect to servers, run queries against databases. I have found it to be a great tool for learning LINQ.

Happy LINQing.

We're working on a project now where there is a .NET WinForms client (for power users) communicating with a Java middle tier. For occasional and public users there is a Flex client. Because .NET clients cannot directly communicate with the Java middle tier (Spring Beans), the middle tier guys have created web services for our team to use.

When the Flex team came onboard and saw the web services, they said they could talk directly with Spring Beans via OpenAMF or Hessian project so there was no need for the web services layer. They also said that the .NET client could also use the Hessian project and that EVERY .NET developer knew about the Hessian project!

Anyone used the Hessian project? At this point I'm just interested hearing from people who've heard of it!

Interestingly I was at Bunnings looking at paint over the weekend and I saw a colour called Hessian. I wonder if all painters know about this colour! :) 

I'm looking into internationalisation for a project we're about to start. There are a few articles on how to localise label text etc on windows forms. But what about error messages that are displayed to the user.

I created a new resource file calles Messages.resx and this created a "code behind" CS file that allowed me to access each of the string resouces in the file. Cool so far. Once I had a few message texts in the resource file I copied it a few times to Messages.fr-FR.resx for France French, Mesages.fr.resx for default French, Messages.nb-NO.resx for Norwegian etc. Each of these resource files also had "code behind" CS files but they were empty. 

I set my UI culture before loading my form in my static void Main method like so:

System.Threading.Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo("fr-FR"); 

And to my surprise when I called Messages.M001 (M001 was name of the message in the resource file), it would attempt to get the text for message M001 in my Mesages.fr-FR.resx file. How cool is that?

Also interesting if I set my UI culture to fr-BE it would try to receive the text from the Messages.fr.resx file as I didn't have a Belgium specific resource file.

Any advice or tips on how to handle internationalisation would be most appreciated too. 

I came across a very annoying problem recently where I was using management studio to connect to a hosted SQL Server (2005) and I wasn't connecting as a  sysadmin as I didn't have access to all DBs on the server.

Under SQL Management Studio 2005 I could connect to the server and expand the databases node to see all databases. But under Studio 2008 when I tried to expand the databases node I got the following error:

Recently I found a wrapper around the google geocode API here. Which got me thinking about map coordinates of Australian suburbs and being able to do distance from or surrounding suburbs searches for starters.

One thing I found with using this wrapper is that I had to go to google and register for access to use the API (and I was issued a key to use with the wrapper). To register for the key I had to enter the URL that would be making calls to the Google API but I found that the calls worked either from a win forms app or an ASP.NET website both running locally (and in debug mode).

I then found a site that publish a CSV file (amoungst others) with all Australia post codes and their lats and longs here.

The only thing left was to find a routine that calculated the distance between 2 lat and longs. I have attached the SQL CLR user defined function source file to the bottom of this article. 

The only thing left was to create a SQL function that returned the results. Simply pass in the postode you are starting from and the distance you want to search from the postode. I group by postcode and get the max distance because in most instances there postcodes that cover multiple suburbs. This function retrieves the "distinct" list of souurounding suburbs.

I'm sure there can be improvements in my implementation.

GeoDistance.cs (1.17 kb)