Category Archives: Tech & Gadgets
MS Reporting Services – Dynamic SQL
Over the last couple of weeks, I have started to use and get my head around Microsoft Reporting Services. As with all new products, it takes a while to start to understand the feature-set of the product and how to work-around some of the limitations.
The power of SQL Reporting Services is the ability to create and deploy reports that end-users can consume and refine if necessary, without giving them direct access to the database. It also make sense that these same end-users can refine datasets as required before exporting to MS Excel.
In my case, I wanted to provide an easy way for users to be able to modify some of the search logic for a given report once published out i.e. Ability to select a search field, basic logic to search on and required search value. In order to do this, I had to convert the SQL within the dataset into ‘Dynamic SQL’. This small tutorial will take you through the steps of how to do this using the MS AdventureWorks Database.
On a side note, I am also testing out ScreenSteps Pro, which helps in the production of documentation and publication. It looks pretty good so far!
Playstation Welcome Back–Missing Games
After over a month of waiting for the network to come back up, and days of trying to log into it just to receive error messages, I finally managed to login and checkout the Welcome Back Offer. Unfortunately, I made the mistake of downloading the welcome back offer (yes it is downloadable) just to realise that then removed all my options to download the free games!
So, whether or not this is a result of what I did or a system wide bug, I am not sure. Will do a bit of searching and find out what is going on. I did however manage to join up for the 30 day free playstation plus offer.
Update:
Thanks to DryEarth.com for posting up a workaround to this problem – http://www.dryearth.com/playstation-welcome-back-missing-free-game/
Have to access from the playstation account management section. Logical… See link above for detailed instructions.
Integration Services – Multiple Environments
Best Practice with version control of DB schemas..
So I am currently working a Microsoft data integration project (more on that later). For the first time, we have setup multiple SSIS environments to mirror that of our production database environments, i.e. Development, Test and Production. I am currently using Visual Studio 2010 to manage database changes (via a database project). All source code is version controlled via TFS.
At the moment, if I make any changes to the development SSIS database, I do so directly through SQL Server Management Studio. Within VS 2010, I then perform a schema compare against the Project schema and update the project schema with any changes.
Once packages have been deployed and tested, on Development, I build and deploy my database solution out to test and production.
This process while it works seems to be a little combersome. I was wondering if anyone out there reading this, has experience in managing multiple database instance using VS, and how they manage schema changes. Thanks!
00904. 00000 – invalid identifier
I have been writing some scripts in oracle and have just encountered and solved a niggly little problem. Syntax difference between SQL Server and Oracle threw me.
On performing this query, was receiving this error: 00904. 00000 - “%s: invalid identifier”.
WITH tempTab AS
(
SELECT a,b,c,d,e, ROW_NUMBER() OVER (partition by a ORDER BY e) AS “RowNums”
FROM tableName
ORDER BY aASC
)
SELECT * From tempTab WHERE RowNums = 1
Because the RowNums is a created column name, the referred RowNums outside of the WITH clause must also be referenced in the same manner i,e “RowNums”
Logical right? ;P
