Posts

Do you use code templates in SSMS? I am sure that at least once you happened to click “New stored procedure” in the object explorer context menu. The default template for this action is a bit disappointing and the only valuable line is “SET NOCOUNT ON”. The rest of the code has to be heavily rewritten or deleted. Even if you use the handy keyboard shortcut for “Specify values for template parameters” (CTRL+SHIFT+M), you end up entering a lot of useless values. [Read More]

Stanislav Kamaletdin (twitter) today asked on #sqlhelp how to backup all user databases with TDP for SQL Server: My first thought was to use the “*” wildcard, but this actually means all databases, not just user databases. I ended up adapting a small batch file I’ve been using for a long time to take backups of all user databases with full recovery model: @ECHO OFF SQLCMD -E -Q "SET NOCOUNT ON; SELECT name FROM sys. [Read More]

Today I noticed in my WordPress site stats that somebody landed on this blog from a search engine with the keywords “spaghetti dba”. I found it hilarious that somebody was really searching for me that way, and I performed the search on Google to see how this blog would rank. The third result from Google made me chuckle: The Spaghetti-Western Database Trailer. And here is the video showing on that page: [Read More]

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]

Some bugs in Oracle’s code are really surprising. Whenever I run into this kind of issue, I can’t help but wonder how nobody else noticed it before. Some days ago I was querying AWR data from DBA_HIST_SYSMETRIC_SUMMARY and I wanted to turn the columns AVERAGE, MAXVAL and MINVAL into rows, in order to fit this result set into a performance graphing application that expects input data formatted as {TimeStamp, SeriesName, Value}. [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]