Skip to main content

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)

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 […]

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