Click to Download

Download SQL Server Interview Question

Download how to be Database Administrator PDF file

SQL Server Central Management Servers System Tables

ProblemI have SQL Server Central Management Servers setup in my environment. How can I get a list of the registered servers and their associated properties? Are there any queries I can issue? Check out this tip to learn more.

Solutions
http://www.mssqltips.com/tip.asp?tip=2397

Bug Fix:The I/O operation has been aborted because of either a thread exit or an application request

Error

Read on "VDI_FCA0B3E9" failed: 995(The I/O operation has been aborted because of either a thread exit or an application request.)

The media family on device LiteSpeed for SQL Server backup file is incorrectly formed. SQL Server cannot process this media family.

Solution
In recent times I came across the above error, while restoring the database. We are using the Quest LiteSpeed for the backup/restore. As per the request from the user we have started the restore of the database from the production box to development box.

Restore Query is failing with the above error. I have follow the below checklist to fix the error.

1. Check for the LiteSpeed version on both the boxes


2. Verified the backup file


3. Verified the backup file path

I got the positive result on all the above tests but still the restore is failing. At last I checked the one day old emails and found that, production box was migrated from SQL Server 2005 to SQL Server 2008. As you may know that we can't restore the database from higher version to lower version because of that restore was failed.

Finally I have restore the database on the different SQL Server 2008 development box and up-grade the SQL Server 2005 box with SQL Server 2008

If you come across such kind of error, make sure you are checking the SQL Server version and LiteSpeed version on both source and destination machine.

Customize SSMS query window for standard comment block and frequently used commands

Problem

As a best practice, most companies follow a standard comment format at the beginning of stored procedures and functions. In addition, it is also a good idea to include comments in all scripts you write even if they are just save as .sql or .txt files.  Manually we often add a standard comment block to the header of our scripts using copy and paste, but in this tip we look at how to automate adding a standard comment block for every set of code we write using SSMS.

Solution

http://www.mssqltips.com/tip.asp?tip=2372

Bug Fix:Cannot find template file for the new query

Error
Cannot find template file for the new query ('C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\SQL\SQLFile.sql')

Solution:
Step 1: Go to C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\SQL\

Step 2: Create .SQL File named SQLFile.SQL