SQL Server Archives - Page 254 of 393 - SQL Authority with Pinal Dave

SQL SERVER – Fix: Error: 262 : SHOWPLAN permission denied in database

January 5, 2010

Execution Plan, SQL Error Messages, SQL Scripts, SQL Server, SQL Server Security

During one of my recent training class when I asked students to check the execution plan using (can be enabled using CTRL+M), they received error as following.

Msg 262, Level 14, State 4, Line 1
SHOWPLAN permission denied in database ‘AdventureWorks’.

SQL SERVER – Unique Nonclustered Index Creation with IGNORE_DUP_KEY = ON

January 4, 2010

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Error Messages, SQL Index, SQL Scripts

In one of my recent training course, I was asked question regarding what is the importance of setting IGNORE_DUP_KEY = ON when creating unique nonclustered index. Here is the short answer: When nonclustered index is created without any option the default option is IGNORE_DUP_KEY = OFF, which means when duplicate…

SQLAuthority News – TechDays Session at Infosys Mysore 2009 – Change Data Capture and PowerPivot

January 3, 2010

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks, SQLAuthority News

Business Intelligence, MVP, PowerPivot, SQLAuthority Author Visit

It has been a great pleasure to visit Infosys Mysore for an MSDN session. I had previously visited Infosys Bangalore for Technical session. Please read the details of earlier visit SQLAuthority News – Notes from TechDays 2009 at Infosys, Bangalore. This event was held on Dec 10, 2009. I have…

SQL SERVER – FIX: Error: 1807 Could not obtain exclusive lock on database ‘model’. Retry the operation later.

January 1, 2010

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Error Messages, SQL Scripts

While working on query optimization project, I encountered following error. Msg 1807, Level 16, State 3, Line 1 Could not obtain exclusive lock on database ‘model’. Retry the operation later. Msg 1802, Level 16, State 4, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check…

SQLAuthority News – 1200th Post – An Important Milestone

December 31, 2009

Pinal Dave

Personal, SQL, SQL Server, SQL Tips and Tricks, SQLAuthority News

Database, DBA, MVP

Today is the last day of 2009 and this is my 1200th post! This year had been a wonderful year for me. I was actively involved with the community, and there were a lot of occasions where I could work along with IT professionals to resolve their issues in projects.…

SQL SERVER – Get Date of All Weekdays or Weekends of the Year

December 29, 2009

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL DateTime, SQL Function, SQL Scripts

Today’s article is created based on wonderful contribution from Tejas Shah. Tejas is very prominent SQL Expert and .NET wizard. He has answered the query of a reader on this blog who raised the following question: how to generate the date for all the Sundays in the upcoming year. Tejas…

SQL SERVER – Difference Temp Table and Table Variable – Effect of Transaction

December 28, 2009

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

Best Practices, Database, SQL Scripts, SQL Transactions

Few days ago I wrote an article on the myth of table variable stored in the memory—it was very well received by the community. Read complete article here: SQL SERVER – Difference TempTable and Table Variable – TempTable in Memory a Myth. Today, I am going to write an article…