nvarchar(max) doesn’t work on Azure SQL with Classic ASP

stress

This is one of those completely obtuse issues that one hits from time to time – nvarchar(max) fields behave oddly on Microsoft Azure SQL – yet it all works fine on SQL Server 2008 on my local machine.

I battled this for an hour before finally simultaneously figuring out that trying to return data from an nvarchar(max) column via Classic ASP on Azure yields a blank value (or weird binary garbage); and falling over someone else who’s had exactly the same problem recently.

It’s fine to have a table with nvarchar(max) in Azure. I can push data into it, I can see the data via SSMS, but returning via ASP – nada.

I’m returning the data via a UDF, so I altered the table definition returned from the function so the column is nvarchar(4000). My function grabs the data from the (max) column and sticks it into the output column without trouble, and the function returns values fine, don’t even need a CAST.

It’s frustrating because I’m anticipating these columns to hold large blocks of text, and now I’m size restricted.

I haven’t had time to explore whether messing with the connection strings might do the trick.

Clean up double line spacing in Dreamweaver code

Adobe_Dreamweaver_CS6_Icon

I work across Mac and Windows, with Dreamweaver running on my Mac, and code that sometimes will be hosted on Windows Servers and sometimes not. So I often run into the problem of erratic line spacing. If I have code saved on my Mac HDD (as opposed to my Windows VM), push it up to a Windows Server, then pull it down again, I sometimes wind up with the code double spaced when I open the file again in Dreamweaver.

I finally tracked down a quick solution to clean up these pages, with grateful thanks to this page: Permanently Remove Double Space in Dreamweaver Code View.

Do a search and replace:

  • Search for: [\r\n]{2,}
  • Replace with: \n

Make sure you have the Use Regular Expressions option checked.

Of course, it’s also good to make sure you have the Line Beak Type set correctly under Dreamweaver’s Preferences, but I’ve found that doesn’t really help when you are working cross-platform.

Create Twitter-like REST API endpoints with IIS URL rewrite

Twitter’s REST API uses endpoints that look like this:

http://www.domain.com/[object]/method.json

In addition for GET request you can append parameters like this:

http://www.domain.com/[object]/[method].json?param1=a&param2=b

So for example a call to the Twitter REST might look like this:

https://api.twitter.com/1.1/statuses/user_timeline.json?screen_name=davideedle&count=2

I wanted to try and reproduce this format with a REST API I’ve been playing with, so I spent some time figuring out how to configure IIS URL rewrite to handle these URLs and deliver the parameters to an ASP page.

You’ll need IIS rewrite installed, in IIS Manager you should see the ULR rewrite icon if you click on a website, for example:

Screen Shot 2013-09-23 at 11.30.31 AM

If the icon is not there download and install URL rewrite extension from the Microsoft website, then open URL Rewrite and add a new Inbound Rule.

Here’s the regular expression pattern I came up with:

^rest/v1/([^/]*)/([^/]*)\.json$

With the Rewrite URL of:

rest/v1/?objectTypeName={R:1}&objectMethod={R:2}

This means that a URL like this:

http://www.domain.com/myObject/myMethod.json?param1=a&param2=b

Will pass the parameters into my ASP page like this:

objectTypeName = myObject
objectMethod = myMethod
param1 = a
param2 = b

You need to make sure you have the “Append query string” option checked for the Rule so the additional parameters (eg param1 and param2) are passed through the rewrite.

Source Control Window disappears in Visual Studio 2012

2844.VS_tm_Purp526_rgb

A little weirdness with Visual Studio 2012 – the Source Control Window disappeared and nothing seemed to bring it back. I could open a solution from the Recent list, and open a file from the Solution, but still no Source Control Window would launch or display. And View -> Other Windows -> Source Control Window did nothing.

All until a colleague pointed out that, if you watched closely, it seemed like the window was loading, just not visible. So we tried Window > Reset Window Layout and all was well.

Drove me crazy for 15 minutes – and the simplest solution turned out the right one.

Select a random record with SQL server

Easy little trick, if you need to select a random record with SQL server as part of a query:

SELECT TOP 10 column,column FROM table ORDER BY newId()

Or could do:

SELECT TOP 10 percent column,column FROM table ORDER BY newId()

This is not brilliant for very large data sets, you can use TABLESAMPLE, for example:

SELECT column, column FROM table TABLESAMPLE (10 PERCENT)

Or:

SELECT column, column FROM table TABLESAMPLE (100 ROWS)

There is  caveat with TABLESAMPLE, it’s not fabulously random – and the number of rows returned may not match what you asked for, especially with small values, so if you ask for 10 records you might get back 20. And if you ask for 5 you might not see anything returned at all. I’m not entirely sure I’ve completely wrapped my head around TABLESAMPLE, you can read more on MSDN. But newId() is something I use all the time, and I’d stick with that unless you find your data set is large and the query is slow.

Convert decimal/dotted IP number to hex string

I had need today to pull a bunch of IP numbers from a database and return them as hex strings from a query to my code. It took a little messing, but finally figured out a solution.

DECLARE @b varchar(30) SET @b = '64.233.160.0'

SELECT 
RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME(@b, 4) AS int)), 2),2)
+
RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME(@b, 3) AS int)), 2),2)
+
RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME(@b, 2) AS int)), 2),2)
+
RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME(@b, 1) AS int)), 2),2)

The above returns ’40E9A000′. Breaking it down:

— gives me the first octet as an integer

CAST(PARSENAME(@b, 4) AS int)

— converts the integer to a hex literal

CONVERT(VARBINARY(4), CAST(PARSENAME(@b, 4) AS int))

–converts the hex literal to a string

CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME(@b, 4) AS int)), 2)

–grabs the right hand 2 characters of the hex string

RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME(@b, 4) AS int)), 2),2)

 

Classic ASP – maintaining sessions between secure and non-secure pages

If you use secure (SSL) and non-secure pages on your IIS-powered web site, and are using session variables to hold information about your users, you most likely will find those session variable values disappearing as your user switches from non-secure to secure pages – for example, they are in your online shop, and then click to Checkout and Pay.

The problem is IIS – by default it creates a new sessionId for users when they hit the SSL pages – so any session values you already created against the non-secure page sessionId are lost.

It’s easy to fix, in IIS7 anyhow. In IIS go to the properties for your web site and open the ‘ASP’ properties page. There is an option down the bottom entitled ‘New ID on Secure Connection’. By default this is set to True. Change it to False and click the Apply link.

https_session

Switching between SSL and non-SSL can also be a reason users experience a time out, or seem to be automagically logged out in the middle of something.