I use Google’s Chrome browser a lot. Its fast, websites display correctly and there’s usually less problems. But this week I started getting problems with webpages hanging in Google Chrome. I tested this on a couple of different PC’s and found that it affected both Windows and Mac OS X. After further testing I discovered […]
Microsoft revokes trust in 28 of is own security certificates. Remember to run Windows Update today!
Microsoft has revoked 28 of its security certificates via Windows Update today as part of their security advisory 278973. The certificates have been revoked following the Flame malware attack that involved the use of fraudulent Microsoft digital certificates to by pass some security measures in windows to install the malware. This patch should be available […]
How to fix: User, group, or role ‘*’ already exists in the current database. (Microsoft SQL Server, Error: 15023)
If you’ve restored a database onto another server, you may get the following error message when adding user logins to the database: Create failed for User ‘TMS’. (Microsoft.SqlServer.Smo) User, group, or role ‘TMS’ already exists in the current database. (Microsoft SQL Server, Error: 15023) To fix this there are two different methods depending on […]
SQL Server Table Function Performance
Rob Farley has a great post on when is a SQL function not a function? Read the two below functions – they return the same thing, but one is more effecient than the other. If you can’t see why, head over to Rob’s post that shows the execution plans for a great explaination. CREATE FUNCTION dbo.FetchSales_inline(@salespersonid […]
ANSWER: UPDATE failed because the following SET options have incorrect settings: ‘ANSI_NULLS, QUOTED_IDENTIFIER’
I came across an interesting problem last night – a stored procedure that had been created years ago was suddenly coming up with this error message: UPDATE failed because the following SET options have incorrect settings: ‘ANSI_NULLS, QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or […]
Finding changed rows in SQL Server using INTERSECT, ISNULL or COALESCE and performance
If you are trying to determine which rows are different between two sets, the usual method is to create a query comparing table1.Column1 != table2.Column1 but this doesn’t check for rows where one of these columns is null. isnull(table1.Column1,-1) != isnull(table2.Column1,-1) might seem like a good way to compare nullable columns – but the performance […]
Microsoft SQL Server Books
Why are my Covering Indexes not working in SQL Server?
Rob Farely has posted a great article on why your covering indexes in SQL Server may not be getting the perfomance you are expecting. Covering, schmuvvering – when a covering index is actually rubbish If you use covering indexes but SQL Server keeps ignoring them – read Rob’s article for some reasons why.
Security � How To Assign Rights To SQL Server Database Objects
Piotr Rodak has a great article on Security – How To Assign Rights To SQL Server Database Objects and why you should not assign rights to the public role and why assigning rights to roles instead of users gives you much more flexibility. He also covers why you should only grant permissions to your stored […]
Searching SQL Stored Procedure Source Code in Information_Schema.Routines
— Here’s a little line of code I use a lot to find out what SQL stored procedures / functions use a particular table SELECT * FROM information_schema.routines WHERE routine_definition LIKE ‘%GetThemeColours%’ –&nnbsp;INFORMATION_SCHEMA.ROUTINES is similar to sys.routines but has all the text names instead of objectid’s with the added bonus the full stored procedure text is searchable (its in ROUTINE_DEFINITION column) – its like a super powered sp_helptext — Also check out these helpful views: — View all the columns in tables + views with full table/view name SELECT * FROM information_schema.columns — stored procedure / function parameters SELECT * FROM information_schema.parameters — output columns for table valued functions (NOT stored procedures) SELECT * FROM information_schema.routine_columns — list of tables SELECT * FROM information_schema.tables — list of views SELECT * FROM information_schema.tables — 195cac785d844883be7a61f2b54189c6