Monthly Archives: March 2010

Absolute MUST READ: Plagiarism, Inspiration, and John Dunleavy

I’ve been out of the twitter loop for a while, running around trying to meet deadlines. Sadly I missed this whole saga, but thanks to Brent’s post, I’m caught up.

Read. And learn.

Plagiarism, Inspiration, and John Dunleavy – http://www.brentozar.com/archive/2010/03/plagiarism-inspiration-and-john-dunleavy/

My students can attest that the one single thing they can do on their assignments that will guarantee huge mark deductions is – NO PROPER CITATIONS.

We usually have online discussions in class, and students are asked to post a way to solve a problem, or to post about a new SQL Server feature etc. And I emphasize to them that it doesn’t matter what their answers are, it must have come somewhere. If it’s an article, a blog, a white paper, I need the author, the title, and the link back to that article. If it’s a book, good times but use the traditional citation. If it came from my lecture, or something I said in my lecture, I ask them to put explicitly “Heard from Donabel’s lecture”. If they absolutely thought about it on their own, or just did their own experimentation and never consulted a book, they should still explicitly say the source is them.

It sounds very tedious for a student, but I just want to plant in their minds that in this day and age where information is easily obtained anywhere, I still want them to pause and take the time to acknowledge where they got that information from. (Students usually learn after the first time they get deductions).

Deja Vu

Which reminds me .. I used to mark papers when I was still in school. Caught one of the students plagiarizing really bad – that s/he has copied everything (verbatim) including all the spelling and grammar mistakes. C’mon – that’s just called EMBARRASSING.

Exploring SSRS 2008 R2 Report Manager

SQL Server Reporting Services 2008 R2 (based on November CTP) has received a makeover! It looks more SharePoint-ish, especially with its new blue theme, contextual dropdowns, and ajaxified operations. I quite like the changes, this definitely feels more “user friendly” to me.

Download document/screenshots: Exploring SQL Server 2008 Reporting Services R2 Report Manager

Report Manager – Home Page

The new theme is easy on the eyes. When you hover over the items, whether it’s a folder or a file, there will be a contextual dropdown. Feels very much like SharePoint (2010).
SSRS 2008 R2 Report Manager Home Page

Report Manager – Site Settings

On the Site Settings page, you have General Properties, Security and Schedule.
SSRS 2008 R2 Report Manager - Site Settings

Report Manager – Data Source Properties

Data Source Properties page looks familiar, with the exception of Dependent Items. Dependent Items page will list all items (reports, data sets) that use this particular Data Source.
SSRS 2008 R2 Report Manager - Data Source Properties

Report Manager – Search

Yes, you can now search in SSRS Report Manager! Type in your keyword, and away you go! It will find your report, your data set, your folder, or even your report part.
SSRS 2008 R2 Report Manager - Search

Report Manager – Report Contextual Dropdown

If you have worked with SSRS in Integrated Mode in SharePoint, the contextual dropdown in the revamped Report Manager will look familiar. When you hover over a report item and click on the down arrow, you will be able to move, delete, edit in Report Builder 3.0, and explore other properties, to name a few.
SSRS 2008 R2 Report Manager - Report Contextual Dropdown

Report Manager – Report Properties

If you clicked on “Manage” in the contextual dropdown of a report item, you will be taken to this page. Note that on the left column, you have links for : properties, data sources, shared data sets, subscriptions, processing options, cache refresh options, report history, snapshot, and security.
SSRS 2008 R2 Report Manager - Report Properties

Report Manager – Shared Data Set Properties

Finally there is a way to share data sets among reports!
SSRS 2008 R2 Report Manager - Shared Data Set Properties

Report Manager – Shared Data Set Caching Properties

And yes, you can also cache these shared data sets.
SSRS 2008 R2 Report Manager - Shared Data Set Caching Properties

To infinity and beyond

(You can tell I’m waiting for Toy Story 3) .. I’m excited to work with SSRS 2008 R2. Besides changes in the Report Manager, there’s quite a few more exciting additions. In my next few blogs I will post some mini tutorials on creating spark lines, data bars, indicators and map integration.

Download document/screenshots: Exploring SQL Server 2008 Reporting Services R2 Report Manager

SQL Server Spatial Data for Canada

I needed to load spatial data for Canadian places into a SQL Server 2008 R2 database. Good thing there is geonames.org, which is a geographic database that contains millions of placenames and their corresponding geolocations (latitude and longitude).

Here are the steps I took to create this spatial database.

Download the geospatial data

So I first downloaded the Canadian geolocation database from http://download.geonames.org/export/dump/ called CA.zip (according to the geonames readme).

This export file contains the following fields (excerpt from the readme file)

geonameid integer id of record in geonames database
name name of geographical point (utf8) varchar(200)
asciiname name of geographical point in plain ascii characters, varchar(200)
alternatenames alternatenames, comma separated varchar(5000)
latitude latitude in decimal degrees (wgs84)
longitude longitude in decimal degrees (wgs84)
feature class see http://www.geonames.org/export/codes.html, char(1)
feature code see http://www.geonames.org/export/codes.html, varchar(10)
country code ISO-3166 2-letter country code, 2 characters
cc2 alternate country codes, comma separated, ISO-3166 2-letter country code, 60 characters
admin1 code fipscode (subject to change to iso code), see exceptions below, see file admin1Codes.txt for display names of this code; varchar(20)
admin2 code code for the second administrative division, a county in the US, see file admin2Codes.txt; varchar(80)
admin3 code code for third level administrative division, varchar(20)
admin4 code code for fourth level administrative division, varchar(20)
population bigint (4 byte int)
elevation in meters, integer
gtopo30 average elevation of 30’x30′ (ca 900mx900m) area in meters, integer
timezone the timezone id (see file timeZone.txt)
modification date date of last modification in yyyy-MM-dd format

Create a table in your SQL Server database that maps to all these columns

I then created the following table
[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

CREATE TABLE [dbo].[CAGeoNames]
(
[geonameid] INT PRIMARY KEY,
[name] VARCHAR(200),
[asciiname] VARCHAR(200),
[alternatenames] VARCHAR(5000),
[latitude] DECIMAL(38,10),
[longitude] DECIMAL(38,10),
[featureclass] VARCHAR(10),
[featurecode] VARCHAR(10),
[countrycode] VARCHAR(2),
[cc2] VARCHAR(60),
[admin1code] VARCHAR(20),
[admin2code] VARCHAR(80),
[admin3code] VARCHAR(20),
[admin4code] VARCHAR(20),
[population] bigINT,
[elevation] INT,
[gtopo30] INT,
[timezone] VARCHAR(100),
[modificationdate] DATETIME
)
GO
[/sql]

Import the data

I initially used BULK IMPORT with a ROWTERMINATOR of n because I know each line is terminated by a linefeed. But for some reason, n didn’t work. Neither did rn.

Baffled, I know that n should have worked, but I conceded I should consult Books Online. Turns out Books Online has an exact sample for importing a file that was produced by UNIX .. and for some reason it uses dynamic SQL. Not wanting to waste any more time with the import, I just used the Books Online sample as a template, and true enough, it worked.

One of these days though I will come back to this issue and figure out why a bare n ROWTERMINATOR doesn’t work.

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = ‘BULK INSERT CAGeoNames
FROM ”C:CACA.txt”
WITH (ROWTERMINATOR = ”’+CHAR(10)+”’)’
EXEC(@bulk_cmd)
GO

[/sql]

Add a GEOGRAPHY type column

Next up, we’ll use SQL Server 2008’s new GEOGRAPHY data type.
[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
ALTER TABLE [dbo].[CAGeoNames]
ADD [GeoLocation] GEOGRAPHY
GO

[/sql]

Create GEOGRAPHY data based on the existing longitude and latitude values

And last but not least, we are going to derive the GEOGRAPHY value based on the longitude and latitude values that we just imported. We are going to use the Parse function that comes with the GEOGRAPHY data type.
[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

UPDATE [dbo].[CAGeoNames]
SET [GeoLocation] = Geography::Parse(‘POINT(‘ +
CAST([longitude] AS VARCHAR(20)) + ‘ ‘ +
CAST([latitude] AS VARCHAR(20)) + ‘)’)
GO
[/sql]

Ta-Daa!

That’s it. Now I have my own Canadian geolocation database. I can start using this database now for my SSRS R2/Bing Maps presentations!

WMIProviderException when installing SSRS 2005 on Windows 2008

Got this error when I was trying to configure SQL Server Reporting Services 2005 on Windows Server 2008:

ReportServicesConfigUI.WMIProvider.WMIProviderException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError (ManagementBaseObject mo)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ListReportServersInDatabase (RSReportServerInfo[]& serverInfos)

Turns out I really just needed to restart the Windows Management Instrumentation Service:
1. Start > Run > services.msc
2. Right click on Windows Management Instrumentation, and restart.

SSRS Error: configuration file contains an element that is not valid


The configuration file contains an element that is not valid. The ReportServerUrl element is not a configuration file element.

I was configuring SSRS 2005 on Windows Server 2008, and on the last stretch (after several errors because of permissions on Windows Server 2008 side) .. I was getting this issue. It seems SSRS does not bode well if the server has more than one website.

What I had to do was to go to the RSWebApplication.config and comment out


<ReportServerUrl>http:/MyDomain/ReportServer</ReportServerUrl>
<!-- <ReportServerVirtualDirectory></ReportServerVirtualDirectory> -->
<ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel>