Sunday 31 July 2011

Atlassian JIRA Startup Failed: Configuring JIRA to access SQL Server 2008 Express R2

I recently upgraded my SQL Server 2008 Express Edition with Advanced Services to R2, and found that my JIRA installation stopped working (received Tomcat 404 errors).

I tried everything, but could not get it to work - I kept getting 'connection refused' errors in the JIRA logs. I hadn't modified my server.xml configuration since the upgrade, and I checked that the protocols in SQL Server Configuration Manager were enabled on Port 1433.

Something wasn't right - even with the same configuration as before I just could not get JIRA to start.

Eventually, I found the answer which worked for me:

  • Run config.bat in the /bin folder of the JIRA installation folder.
  • Click the Database Tab:
  • Database type: SQL Server
  • Hostname: localhost (Or your server name. Note: NOT "localhost\SQLExpress")
  • Port: 49374 (Find this value in your registry, in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQLEXPRESS\MSSQLServer\SuperSocketNetLib\Tcp for your SQL instance name. i.e. SQLEXPRESS. You can edit your registry by typing 'regedit' at the command line.)
  • Database: jiradb
  • Username: jirauser
  • Password: *****
  • Schema: jiraschema
  • Connection Pool: 20
  • Click Test Connection.
Hopefully the utility will return 'Connection successful.'. Restart the Atlassian JIRA service and you should find that you are able to access your JIRA installation.

Worked for me - I'm not sure why I needed to do this for R2.

More info in the comments attached to this forum post.

Thursday 21 July 2011

SQL Server: Columns truncated to 255 characters when using OPENROWSET or OPENDATASOURCE to import from Excel

I was using the SQL command OPENROWSET to directly import a load of text from an Excel spreadsheet to my database. The columns were of varying widths, some up to 3,000 characters.

The problem I experienced was that for some columns the output of the statement was being truncated to 255 characters - but not for all of them. I couldn't understand why this would happen and tried various combinations of OPENROWSET and OPENDATASOURCE, but to no avail.


sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

INSERT INTO tblRichTextFields (Requester,feet,approval)
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source="C:\RichtextFields.xls";Extended Properties=''Excel 12.0''')...[Sheet1$]

GO

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 0
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO


The only thing I could think of was that it was something to do with the Excel cell formats. I compared the columns which were being truncated to the column which was not, but they were the same. I tried changing them all to Text to see if that made any difference. Still truncated.

I couldn't find much more on Google, nor in my brain - but eventually I did find a fix which proved me right - it was some underhanded 'guesswork' on Microsoft's part - surprise, surprise. See this quote:

"Your data may be truncated to 255 characters if the first 8 records for the field(s) being truncated contain 255 or fewer characters. The Microsoft Excel ODBC driver will, by default, scan the first 8 rows of your data to determine the type of data in each column."

I changed the Registry Setting which controls the 'Excel format guessing' behaviour from 'on' to 'off', logged out and back in - and Hey Presto! it worked.

Setting:
HKEY_LOCAL_MACHINE\SOFTWARE\Mi­­crosoft\Jet\4.0\Engines\Excel­\­TypeGuessRows

Change the value from 8 hex to 0

I don't think this fix will work in every situation and I haven't considered every possible implication of changing this registry setting, but if your text is being truncated like this - this is worth a try. Take a backup of your registry before changing it and it's not my fault if you Michael Berk it up!

Here's some detailed info on the Microsoft website.

Tuesday 19 July 2011

97 Things Every Programmer Should Know

Opinions are often divided on the best approach to programming and software development, but there's a really nice book with 97 points which are pretty tough to argue with.

It's by a bloke called Kevlin Henney, and it's called 97 Things Every Programmer Should Know: Collective Wisdom from the Experts. There are some really excellent points in there, and you can see the content of the book on O'Reilly's commons wiki.

It covers topics such as techinical debt, the importance of good quality user stories (test plans) and good approaches to test driven development.