SQL Server Data Type Conversion Chart

As part of the SQL Server Developer or DBA job, you may come across many instances where you are required to convert from one data types to another in order to complete the task given to you and the conversions can be implicit or explicit.
Microsoft has released a SQL Server Data Type Conversion Chart which helps in determining the conversions between data types and the type of conversion, whether it is a explicit or implicit conversion.


This poster can be downloaded form here.

How to rename a column in SQL Server

Simple way of renaming a column in SQL Server is

1. Go to the Table Designer 
2. Modify the column name 
3. Save the changes

Other way is using the Stored Procedure "sp_rename"

Syntax:

Exec sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name' 
    [ , [ @objtype = ] 'object_type' ]

Example:

EXEC sp_rename N'dbo.tbl_Details.MailID', N'e-MailID', 'COLUMN'

When the above command is executed, the column named "MailID" in the table "tbl_Details" will be renamed to "e-MailID"

By using the stored procedure sp_rename, the following object types can be renamed

1. Column
2. Database
3. Index
4. Object
5. UserDataType 

DBCC checkprimaryfile - An Useful Undocumented DBCC Command

Consider you are given with a data file and asked to tell the details about the associated database and the files without attaching the database. What would be your answer?
I would have said "No, it is not possible" if I was asked this few days ago. But now, I say "Yes, It is possible to some extent" using the undocumented DBCC command "DBCC checkprimaryfile"

Before using this DBCC command, you have to note that

1. This is not recommend to use in Production environment
2. The database file should be detached from the SQL Server
3. This works with MDF files

Syntax:

DBCC checkprimaryfile ({'FileName'} [,opt={0|1|2|3}])

FileName is the full path for the primary database file.
opt=0 - checks if the file a primary database file.
opt=1 - returns name, size, maxsize, status and path of all files associated with the database.
opt=2 - returns the database name, version and collation.
opt=3 - returns name, status and path of all files associated with the database.

Usage:

DBCC checkprimaryfile ('C:\Users\SANDESH\Desktop\MSDBData.mdf', 0)

DBCC checkprimaryfile ('C:\Users\SANDESH\Desktop\MSDBData.mdf', 1)

DBCC checkprimaryfile ('C:\Users\SANDESH\Desktop\MSDBData.mdf', 2)

DBCC checkprimaryfile ('C:\Users\SANDESH\Desktop\MSDBData.mdf', 3)

Microsoft License Advisor

How often we look around to find information on licensing and pricing be it any product? Yes, we do. Microsoft has a dedicated link to find the relevant information on volume licensing on all of its products. Please click Microsoft License Advisor to explore more.

For more reading on Volume Licensing - we can refer to the following
Microsoft Volume Licensing

Hope you find this useful as always!

Problem with SQL Server 2005 Maintenance Plan

Few days ago, one of my friend was facing an issue while creating Maintenance Plan in SQL Server 2005. When he was trying to create a Maintenance Plan, he was getting a message which says "Invalid column name 'from_msx'" and "Invalid column name 'has_targets'"

When we were trying to find out what might have caused this issues, we came to know that SP3 was recently applied to this instance.
This kind of errors usually pop up when the upgrades might have not completed properly.
Also in every service pack or hot fixes there will be a lot of changes like addition of tables and columns will be included for supporting the bug fixes or enhancements and if these are not applied properly to the database then you will get many similar errors.

To fix this particular issue, you have to execute the script "sysdbupg.sql" which will be present in the installation location  <Drive>:\Program Files\Microsoft SQL Server\<InstanceID>\MSSQL\Install
Example: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install

Once this has been executed successfully, you will now be able to work with Maintenance Plans.