Wednesday, April 30, 2008

Case insensitive search in mssql

Recently I needed to make a case insensitive search on a table in Microsoft SQL Server.


Because I wanted to overcome common incorrect type errors on the names of people, I needed that this particular search be accent insensitive.



After implementing another alternative I found I could do it in a better way:



select * from names


WHERE (name COLLATE Latin1_General_CI_AI) LIKE '%antonio%'



The list of collations can be obtained by consulting:



SELECT * FROM fn_helpcollations()



Pareto law and Powerpoint presentation viewer

One of the tools that clearly shows the Pareto Principle - in which 80% of the funcionalities take 20% of the implementation time, and the other 20% take 80% of the time (and are rarelly used) - is Presentation Viewer in Microsoft PowerPoint.


Most people don't know this functionality of powerpoint , which is extreamly usefull. It has been available since 2001 and for sure took a considerable time to implement.


Presentation viewer allows the person presenting to view on his screen more information then the one that is shown to the rest of the audience. This way the presenter can see the slide notes, the next slides, the elapsed time, etc, in order to better manage his presentation.

Monday, April 07, 2008

Agile and Scrum events on the week of 21st of April

Following the success of Scrum events that Fullsix organized last January, Mitch Lacy will be back in Portugal on the week of the 21st of April 2008.


The list of events for the week is


21st and 22nd : Certified Scrum Master


23rd : Agile Estimation and Planning


24th: Scrum for Managers (morning) - at Microsoft


Business Value of Scrum (afternoon) at Microsoft.

Wednesday, April 02, 2008

Assign permissions to multiple Stored Procedures in mssql 2005

Enterprise Manager from SQL 2005 allowed us to easily manage the permissions of multiple stored procedures from one place. I don't know why but SQL Server Management Studio for Microsoft SQL server 2005 only allows to manage the permissions of one stored procedure at a time.


Here is a script that Pedro Cruz shared with me that will ease this task:

SELECT 'GRANT EXECUTE ON ' + name + ' TO [UTILIZADOR_RW] AS [dbo]'

FROM sysobjects

WHERE type = 'P'


running this script will generate the sql scripts that will assign permissions to all stored procedures. just need to take out the ones you don't want and run them.


The result is something like:


GRANT EXECUTE ON FirstInsertUser TO [UTILIZADOR_RW] AS [dbo]

GRANT EXECUTE ON SecondInsertUser TO [UTILIZADOR_RW] AS [dbo]

GRANT EXECUTE ON InsertSendToAFriend TO [UTILIZADOR_RW] AS [dbo]

GRANT EXECUTE ON sp_upgraddiagrams TO [UTILIZADOR_RW] AS [dbo]

GRANT EXECUTE ON sp_helpdiagrams TO [UTILIZADOR_RW] AS [dbo]

GRANT EXECUTE ON sp_helpdiagramdefinition TO [UTILIZADOR_RW] AS [dbo]

...etc