CHARINDEX and PATINDEX both return the start position of a pattern in a text field. PATINDEX allows wildcards, CHARINDEX does not. Examples use AdventureWorks.
SQL SERVER – Stored Procedure – Clean Cache and Clean Buffer
DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory. Let us learn how to clean cache.
SQL SERVER – Fix: Sqllib error: OLEDB Error encountered calling IDBInitialize::Initialize. hr = 0x80004005. SQLSTATE: 08001, Native Error: 17
A Sqllib error with hr = 0x80004005 and Native Error 17 says SQL Server does not exist or access denied. The simple fix is to enable TCP-IP in Configuration Manager.
SQL SERVER – T-SQL Script to find the CD key from Registry
Need the SQL Server CD key used at install time? The undocumented xp_regread reads it straight from the registry. Log in as SA if you lack permission.
SQL SERVER – What is New in SQL Server Agent for Microsoft SQL Server 2005
A TechNet article I liked on what’s new in SQL Server Agent for SQL Server 2005: security, proxy accounts, shared schedules, WMI alerts and Database Mail.
SQL SERVER – Restore Database Backup using SQL Script (T-SQL)
Restore Database backups with a T-SQL script: read the logical file names with RESTORE FILELISTONLY, set single user mode, then run RESTORE WITH MOVE.
SQL SERVER – Query Analyzer Shortcuts
A handy table of Query Analyzer Shortcuts with each key combination and what it does, like CTRL+E to execute a query, plus a PDF copy you can download.