An item with the same key has already been added (SQL Report Services)

Screen Shot 2014-02-26 at 7.42.34 AM

 

I was creating a new report in SQL Reporting Services, I pasted in the SQL query for the report and up popped the message “An item with the same key has already been added”.

Turns out I had two columns with the same name – which as always with these error messages, begs the question, why not just say so!

Copy Azure SQL Database to local machine (SQL 2008 R2)

You would have thought in these days of point and click a simple task like copying a database from one place to another would be a walk in the park. So when I wanted to take a copy of a SQL database from Azure and move it to my local machine, I thought it surely would be straightforward. It’s not. Here’s what I came up with:

In Azure make sure you have a Storage account set up, with a container inside. My container is called ‘files’.

export1

In Azure go to your database. Click the Export button down the bottom.

export2

A new window will pop up. The file name will probably be pre-filled. Select your blob storage account (if you only have one then it’s already selected). Select a container (mine is ‘files’). Enter your SQL Server log in name and password – these are the credentials you set up when you added the database. Click OK and the export should start – you’ll see the status down the bottom.

export3

Once the export is done, you can click to view your Storage container, and you should see the export file there.

export4

The trick now is to get the file from Azure to your local machine. I use Azure Storage Explorer, it’s a free download. Download the file from your container.

export5

If you have SQL Server 2012, then you are home free, because you can import the BACPAC file in SQL Management Studio. Right click on the Databases folder under a server in the Object Explorer and choosing “Import Data-tier Application.

If like me you are on SQL Server 2008 R2, you need some help.

Create a new blank database on your local machine.

Go to  http://msdn.microsoft.com/en-us/jj650014 and install the SQL Server Data Tools for Visual Studio 2010. After install go and find SqlPackage.exe, I located it in C:\Program Files\Microsoft SQL Server\110\DAC\bin. Open up a DOS prompt and use the following command:

SqlPackage.exe /a:Import /sf:c:\Desktop\FILENAME.bacpac /tdn:DATABASENAME /tsn:SQLSERVERNAME
  • FILENAME.bapac is the file you downloaded from Azure, whereever it is located on your machine.
  • DATABASENAME is the name of the new blank database you created a minute ago.
  • SQLSERVERNAME is the name of the SQL server on your local machine.

I followed these steps, albeit for a fairly small file, and it worked perfectly.

Puzzle for the day, why one particular Glyphicon Halflings icon is acting weird

calendar-icon

Today’s conundrum, why does one particular Glyphicon Halflings icon display differently between browsers? I have several other icons and they are all correctly displaying black and white. Yet this one icon – calendar – on the same page, same code, displays differently across my browsers.

On FireFox and Safari it displays in colour:

Screen Shot 2014-01-29 at 9.03.15 AM

On IE it displays in black and white:

Screen Shot 2014-01-29 at 9.04.37 AM

On Chrome it doesn’t display at all:

Screen Shot 2014-01-29 at 9.04.12 AM

The markup is straightforward:

Screen Shot 2014-01-29 at 9.11.55 AM

Finally tracked down this answer on Stackoverflow:

There is most likely an issue with inclusion of Glyphicons on BootstrapCDN 3.0.0 version. When 3.0 was released the glyphicons were in a separate repo, and then combined into the Bootstrap baseline again later around 3.0.1.

I updated the CSS CDN include from:

<link href="//netdna.bootstrapcdn.com/bootstrap/3.0.0/css/bootstrap.min.css" rel="stylesheet">

to:

<link href="//netdna.bootstrapcdn.com/bootstrap/3.0.3/css/bootstrap.min.css" rel="stylesheet">

and my calendar icon is now correctly displaying as black and white across all browsers.

Now Internet Explorer won’t even confess to being Internet Explorer

liar

The bizarre world of Internet Explorer never ceases to amaze me. Today’s revelation – Internet Explorer 11 doesn’t even like to admit that it’s Internet Explorer.

When a web browser visits a web site, it identifies itself to the web site with a string of information that contains various elements that say to the web site “hey, I’m here, this is the type of web browser I am, my version, operating system”. It’s known as the user-agent string.

In the past the user-agent string for Internet Explorer would look something like this:

Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)

The ‘MSIE’ stands for Microsoft Internet Explorer, so pretty easy to work out which browser is visiting your web site.

Today I was wondering why I could not teach some web site code to recognise it was being visited by IE, did a little research and came up with this page of explanation from Microsoft.

Mozilla/5.0 (Windows NT 6.1; Trident/7.0; rv:11.0) like Gecko

What’s missing? The ‘MSIE’ of course!

If I was using Firefox, the string would say FIREFOX, like this:

MOZILLA/5.0 (MACINTOSH; INTEL MAC OS X 10.8; RV:26.0) GECKO/20100101 FIREFOX/26.0

Safari would say ‘SAFARI’, and Chrome would, well now there’s the rub, because Chrome can say CHROME as well as SAFARI:

MOZILLA/5.0 (MACINTOSH; INTEL MAC OS X 10_8_5) APPLEWEBKIT/537.36 (KHTML, LIKE GECKO) CHROME/32.0.1700.77 SAFARI/537.36

Remind me again why I got into this web development malarky?

word-wrap: break-word CSS for long text options in drop down select lists

CSS+HTML

A quirk I ran across yesterday with CSS on a drop down list. The list is disabled and has a width fixed with CSS and is contained in a table cell. The selected option text is wider than the fixed width of the list.

<table class="grid_table_new">
  <tbody>
    <tr>
      <td><select style="width:130px;" disabled="disabled" >
          <option value="47" selected="selected">Traditur Preascep irruit. Sed now possum Non adamare te. Maneam</option>
        </select></td>
    </tr>
</table>

When viewed in the browser the list looks like as you would expect:

Screen Shot 2013-12-21 at 8.55.42 AM

Except someone showed me their screen, and it looked different:

Screen Shot 2013-12-21 at 9.00.08 AM

As you can see, the drop down has expanded vertically to display the entirety of the option text. I tested this page in a bunch of browsers and found this  was only happening in a couple of browsers:

  • Chrome (Win) – YES
  • FF (Win) – no
  • Safari (Win) – YES
  • IE11 – no
  • Chrome (Mac) – no
  • FF (Mac) – no
  • Safari (Mac) – no

After a significant amount of poking around in a large amount of CSS I finally tracked it down to this piece of CSS:

table.grid_table_new td, table.grid_table_new th {
	word-wrap: break-word;
}

word-wrap has been around for a while but from what I can tell is not officially applicable to options in a select list, it’s designed to help when formatting blocks of text on a page, and has now been renamed to overflow-wrap, which now seems to be supported by current browsers. I tried swapping the existing property to overflow-wrap – same outcomes though.

It seems this is a quirk of Chrome and Safari on Windows (note, NOT on Mac). And given it’s impossible to specific that my customers should use one of those browsers clearly we need an alternative solution to cope with these long text options. Thankfully JQuery gives us choices, such as this plugin.

puTTY SSH connections dropping when on wifi instead of ethernet

04082-fart-putty

I use puTTY to create SSH tunnel connections to servers, and usually have the connection up all day. Recently I’ve been plagued with puTTY losing the connection every 15 or 20 minutes – all because I moved to wifi instead of a wired connection. It’s because I bought a new MacBook Pro, which doesn’t have a standard ethernet port, a small fact I didn’t realise so I didn’t buy an adaptor. Plus my family was probably becoming a little bored with the blue network cable hanging down the stairs from our cable router to my study – makes the place look untidy.

I already had keep alives configured in puTTY:

Screen Shot 2013-11-06 at 9.19.46 AM

After some research I came across this post ‘Why Windows 7 / PuTTY drop TCP connections even on very brief outages?‘ which seemed to mirror my own experience to some extent.

I took a look at my Windows registry, and did not see the TcpMaxDataRetransmissions parameter, so added it in with a value of 15 (the superuser post suggests 15, the linked FAQ on the puTTY web site says 10).

2013-11-06_09-22-55

With this change my connection remained up for a couple of hours, and then died, a darn sight better than the previous habit of dropping a couple of times every hour – except this time puTTY doesn’t report the connection as dropped, it looks fine, I just can’t reach anything at the other end of the tunnel.

I decided to fiddle with the puTTY keepalive, and changed it from 30 seconds to 15 seconds, over the past day or two I’m seeing 3 or 4 hours uptime. Interestingly, I only notice when I try to connect to a server and receive a timeout message, previously puTTY would say it was disconnected, now it seems to think the session is still current.

All in all good progress. When I have some time I’ll play with the configuration some more. I haven’t even got to looking at whether there is a setting on my  wifi router that might be to blame.

Send file data via JSON from REST API and open as download file in browser (with Classic ASP/JQuery)

Pet project challenge of the day. I have a REST API that delivers a JSON response, and I want it to include file data, so the browser at the client end can download the file. In real world what I’m doing is offering a link to the user to export some data to an XLS and download to their desktop.

I messed around with this for a while. Carriage of the file data seemed simple – at the REST end encode it to base64 and stick that into the JSON. The problem lies at the client end. I have JQuery calling the REST, and dealing with the response. How then to get the base64 out of the JSON and contrive to offer it as a File Save As situation?

Finally here’s what I came up with, it was sparked by something I saw on StackOverflow, although I cannot lay my hands on the post right now:

At the REST, encode the XLS file text to base64, and return the base64 in the JSON as a text element

On the client, I have an ASP script that acts as the controller, it receives requests from the pages (eg JQuery calls this page), then it calls the remote REST, and returns the response back to JQuery. Obviously cross-browser rules means my JQuery can only call URLs on the same domain, and my REST is not. So this script acts as the transporter for the calls. It’s even called Frank.

On the page I have a form with a hidden field, JQuery puts the base64 file data into that hidden field, then submits the form to another page.

<form id="base64Form" action="openFileToBrowser.asp" method="post" name="base64Form">
<input id="base64data" type="hidden" name="base64data" value="" />
<input id="fileName" type="hidden" name="fileName" value="responses.xls" />
</form>

This new page grabs the file data, decodes it from base64 back to text, sets the content headers to XLS etc. And hey presto, the user gets a File Save As dialog. Here’s the code on that page:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<% Option Explicit%>
<% response.expires = 0 %>
<!--#include virtual="/includes/hex_sha1_base64.asp" -->
<%
	dim base64data
	base64data=request.form("base64data")
	if base64data<>"" then
	Response.ContentType = "application/vnd.ms-excel"
	Response.AddHeader "Content-Disposition", "attachment; filename="&request.form("fileName")
	response.write Base64_Decode(base64data)
	response.Flush()
	end if
%>

The base64 encode/decode routine I’m using is part of the very handy hex_sha1_base64.asp file which I also use for other encryption and encoding tasks (for example I use it for encryption as part of talking to OAUTH based APIs).

I haven’t tested this in all browsers yet, be interesting to see if kicks up problems with IE etc.