Steven Wright Blog

For the Scrum for Team System project I have been focusing on the reports; I came across an issue which was due to the way the Reporting Services 2008 "automatic upgrade" feature upgraded our reports. The Scrum for Team System template contains over 20 reports all written for Reporting Services 2005. This was done because Team Foundation Server 2008 only worked on SQL Server 2005 and Reporting Services 2005. Then in August of 2008 Team Foundation Server 2008 Services Pack 1 was released which added support for the Team Foundation Server 2008 is to run on SQL Server 2008 platform. With Reporting Services 2008 there is a new reporting definition language schema. However reporting services does have backward compatibility method as describe below: The report server will validate report definition file against the included reference to the RDL namespace which specifies the version of the report definition schema that is used. The report is automatically upgraded the first...

I have just finished a project were I made a lot of use of the SQL Spatial to do some processing and loading into the database. Here are some of lesson I learnt: The first method that needs to call after instantiating a SQLGeographybuilder object is: SetSrid() then the BeginGeography(), BeginPoint(). Before using sending a SQLGeometry object to the SQL Server use the IsVaild() function to ensure that Geometry object is valid. I create a console application which loaded line, which was using OS coordinates system, data from a flat file. Some of the line data within the file wasn’t as correct as I was led to believe. The application create the SQLGeometry object .NET but the Sql Server then rasied the error when it received and try to save it into the table. The STPointN() function is 1 base which is stated in the Books On-Line If your query needs to select some of the derive data from the functions you can help performance by using persisted compute columns on t...

A couple of colleagues of mine were working on a complicated reporting services project; they had one report which had a parameter which allowed a user to select multiple values of numbers, the problem they were facing was how pass the selected values into the query. They were looking for a way within SQL Server to split a string by a delimiter. As they were using a stored procedure to access the data and multiple value parameters, regardless of parameter type, are passed in as a string of comma separated values. One thing which they weren’t aware of was that with a multiple value parameter, the behaviour can differ between how the data is being retrieved i.e.: stored procedure or embed sql statement. If the data set is using direct query to gather the data then the multi value parameter can be used in the query with a IN clause like so: Where column1 in (@pMultiValueParam) What happens is that before the query is sent to the SQL Server the query the report server substitutes...