Importing GeoNames data into SQL Server

Problem

Import GeoNames data into SQL Server 2014.

Solution

Download GeoNames file from here. I’m using just USA data in my example (us.txt).
Create permanent table for storing data. We will only need specific columns from the original feed:

CREATE TABLE GeoNames(
	geonameid int NOT NULL PRIMARY KEY,
	name nvarchar(200) NULL,
	latitude float NULL,
	longitude float NULL,
	feature_class char(2) NULL,
	feature_code nvarchar(10) NULL,
	[state] nvarchar(20) NULL,
	geoLocation GEOGRAPHY NULL
)

Create temporary table for storing exact copy of the GeoNames data:

CREATE TABLE #GeoNames(
	geonameid int NOT NULL,
	name nvarchar(200) NULL,
	asciiname nvarchar(200) NULL,
	alternatenames nvarchar(max) NULL,
	latitude float NULL,
	longitude float NULL,
	feature_class char(2) NULL,
	feature_code nvarchar(10) NULL,
	country_code char(3) NULL,
	cc2 char(60) NULL,
	admin1_code nvarchar(20) NULL,
	admin2_code nvarchar(80) NULL,
	admin3_code nvarchar(20) NULL,
	admin4_code nvarchar(20) NULL,
	population int NULL,
	elevation int NULL,
	dem int NULL,
	timezone varchar(40) NULL,
	modification_date date NULL
)

Bulk import data into temp table:

BULK INSERT #GeoNames
FROM 'C:\temp\GeoNames\us.txt'
	WITH(
		FIELDTERMINATOR = '\t',
		ROWTERMINATOR = '0x0a'
	)
GO

In this example I’m only interested in school(SCH) and churches (CH) data for DE, PA and MD states. So drop the rest of the data:

DELETE FROM #GeoNames WHERE admin1_code NOT IN ('de','pa','md') OR admin1_code IS NULL OR feature_code IS NULL;
DELETE FROM #GeoNames WHERE feature_code NOT IN ('SCH','CH');

Now copy data from the temp table into permanent:

TRUNCATE TABLE GeoNames;
GO
INSERT 
	GeoNames SELECT geonameid, name, latitude, longitude, feature_class, feature_code, admin1_code,
	geography::Point(latitude, longitude, 4326)
FROM #GeoNames

Finally add a spatial index:

CREATE SPATIAL INDEX [inx_GeoNames_geoLocation]  ON  GeoNames(geoLocation);

View Source doesn’t work in IE8 or IE9

Problem

Nothing happens when you select View Source option in Internet Explorer.

Solution

This happend to me after upgrading from IE8 to IE9 – the View Source option just stopped working. Deleting Temporary Files via IE options menu did not help. The FIX – navigate to “C:\Users\[Your User Name]\AppData\Local\Microsoft\Windows\Temporary Internet Files\Low\Content.IE5\” folder and remove all files/folders including index.dat file. Make sure you can see hidden files/folders when you do that. Also note the [Low] section of the path.

Auto tab text form fields with jQuery

Problem

Need to auto tab to the next form field when the maximum number of entered characters is reached.

Solution

Assigned [maxlenght] attribute and [autotab] class to each field you want to auto tab. Add the following code to $(document).ready event:

$('.autotab').keydown(function(event) {
    //save previously entered value prior to keyup event
    $(this).attr('prevValue', $(this).val());
});

$('.autotab').keyup(function(event) {
    var newValue = $(this).val();
    //see if the textbox had its value changed
    if ($(this).attr('prevValue') != newValue) {
        //see if the number of entered characters is equal or greater
        //than the allowable maxlength
        if (newValue.length >= $(this).attr('maxlength')) {
            //set focus on the next field with autotab class
            $(this).next('.autotab').focus();
        }
        //save newly entered value for the next execution of this event
        $(this).attr('prevValue', newValue)
    }
});

Hopefully, my comments in the above code is enough to understand how it works.

TruncationException: Trying to convert return value or output parameter of size XXX bytes to a T-SQL type with a smaller size limit of 8000 bytes.

Problem

You receive “TruncationException: Trying to convert return value or output parameter of size XXX bytes to a T-SQL type with a smaller size limit of 8000 bytes” error while trying to execute CLR stored procedure in SQL Server 2008.

Explanation

CLR stored procedure is trying to return via output parameter string that exceeds 8000 bytes. Declaring output parameter as nvarchar(MAX) is not enough to overcome 8000 bytes limit.

Solution

In order to return strings via CLR procedures that are more than 8000 bytes you need to apply a special attribute called SqlFacet(MaxSize) to the output parameter.

Example in VB.NET:
Public Shared Sub MySP(<SqlFacet(MaxSize:=-1)> ByRef ret As SqlString)

Example in C#:
public static SqlString MySP([param: SqlFacet(MaxSize=-1)] SqlString ret)

How to Modify applicationSettings section

Problem

My.Settings.YourSettingName is a read only property for application scope configuration

Explanation

By design you can’t modify application scope configuration settings, e.g. My.Settings.Setting1 is always read-only.

Solution

You can change the scope of the setting from Application to User, but then the setting will be stored in user.config instead of app.config
Another solution is to access application configuration file directly via System.Configuration.Configuration class. Here a small sample that updates LastDownloadDate configuration setting inside applicationSettings/APP.My.MySettings section:

Dim config As System.Configuration.Configuration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)
Dim sec As ClientSettingsSection = config.GetSection("applicationSettings/APP.My.MySettings")
sec.Settings.Get("LastDownloadDate").Value.ValueXml.InnerXml = Now.ToString
sec.SectionInformation.ForceSave = True
config.Save(ConfigurationSaveMode.Modified)

“Cannot get IIS pickup directory” error

Problem

You receive “Cannot get IIS pickup directory” error while trying to send email via System.Net.Mail namespace in ASP.NET.

Explanation

User account under which the ASP.NET code is being executed doesn’t have access to the SMTP configuration path in IIS metabase.

Solution

  1. Find out under what domain/user account your ASP.NET application is running
  2. Download MetaAcl tool from here
  3. Run the following command:cscript Metaacl.vbs “IIS://localhost/SMTPsvc” your_domain\your_username RE

The above steps will give your_domain\your_username Read & Enumerate permissions in IIS metabase on SMTPsvc branch.