SQL Server

A new article on SQLServerCentral today: Setting up linked servers with an out-of-process OLEDB provider. I had to struggle to find the appropriate security settings to make a commercial OLEDB provider work with out-of-process load and I want to share the results of my research with you. It took 50 hours of Microsoft paid support to partially solve the issue and a huge time spent on Google and MSDN to find a complete resolution. [Read More]

Yesterday I ran into this error message while installing a new SQL Server 2005 instance on a Windows 2003 cluster: Setup failed to start on the remote machine. Check the Task scheduler event log on the remote machine. SQL Server 2005 setup, in a clustered environment, relies on a remote setup process started on the passive nodes through a scheduled task: For some weird reason, the remote scheduled task refuses to start if there is an active RDP session on the passive nodes. [Read More]

In order to avoid collation conflict issues with TempDB, all user databases on a SQL Server instance should be set to the same collation. Temporary tables and table variables are stored in TempDB, that means that, unless explicitly defined, all character-based columns are created using the database collation, which is the same of the master database. If user databases have a different collation, joining physical tables to temporary tables may cause a collation conflict. [Read More]

Many setup scripts for SQL Server include the 3659 trace flag, but I could not find official documentation that explains exactly what this flag means. After a lot of research, I found a reference to this flag in a script called AddSelfToSqlSysadmin, written by Ward Beattie, a developer in the SQL Server product group at Microsoft. The script contains a line which suggests that this flag enables logging all errors to errorlog during server startup. [Read More]

Personally, I hate having a dedicated administrative account, different from the one I normally use to log on to my laptop, read my email, write code and perform all the tasks that do not involve administering a server. A dedicated account means another password to remember, renew periodically and reset whenever I insist typing it wrong (happens quite frequently). I hate it, but I know I cannot avoid having it. Each user should be granted just the bare minimum privileges he needs, without creating dangerous overlaps, which end up avoiding small annoyances at the price of huge security breaches. [Read More]

Bart Duncan (blog) found a very strange edge case for short-circuiting and commented on my article on sqlservercentral. In my opinion it should be considered a bug. BOL says it clearly: Searched CASE expression: Evaluates, in the order specified, Boolean_expression for each WHEN clause. Returns result_expression of the first Boolean_expression that evaluates to TRUE. If no Boolean_expression evaluates to TRUE, the Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified. [Read More]