SQL Resource Online

Upgrading to SQL Server 2005

2008-04-22T05:56:00.000-07:00

The following are some tips for upgrading to SQL Server 2005:
• Upgrade to SQL Server 2005 from SQL Server 7.0 or SQL Server 2000.
• Run Upgrade Advisor before upgrading to determine if any product changes are expected to break existing applications.
• The SQL Server Database Engine, Analysis Services, and Reporting Services can be upgraded by the setup program.
• SQL Server Integration Services, the replacement for Data Transformation Services (DTS), is installed side by side with DTS. You can run DTS packages using the DTS runtime components.
• SQL Server 2005 Notification Services is installed side by side with Notification Services 2.0. You must migrate instances of Notification Services to SQL Server 2005 when you upgrade the SQL Server Database Engine.
• Use the Surface Area Configuration tool after upgrading to enable or disable SQL Server 2005 services, network protocols, and features.

New MVPs Announced

2006-01-03T22:59:00.000-08:00

Ankit Fadia Windows Security
Lakshmi Murthy BizTalk Server
Mahesh Chand Visual C#
Sanjay Kumar Buragute Windows Embedded
Venugopal Iyengar Windows Security

SQL Server 2005 Express

2005-11-25T02:56:00.000-08:00

SQL Server 2005 Express is available free for download. The same can be downloaded from Microsoft download site. However, you can distribute the same after registering on the redistribution license.

For more information on SQL Server 2005 check http://www.microsoft.com/sql

You can also check the e-book “SQL 2005 Express [Installation Guide].pdf” which provides step-by-step instructions on how to install SQL Server 2005 Express edition

The e-book can be downloaded by clicking HERE.

SQL 2005 Editions

2005-11-22T04:05:00.000-08:00

Business today demands a different kind of data management solution—one that offers excellent performance, scalability, and reliability, but is also easy to use and maintain. Your business now expects more from its key information technology investments. SQL Server 2005 exceeds expectations by delivering enterprise-class data management and business intelligence.The following list provides summaries and links to information about each edition of SQL Server 2005

• SQL Server 2005 Express Edition
Express Edition helps developers build robust and reliable applications by providing a free, easy to use, and robust database when protecting and managing information inside and outside of applications is critical.

• SQL Server 2005 Workgroup Edition
Workgroup Edition is the data management solution for small organizations or workgroups within larger entities. It includes all the core database features needed for data management in an affordable and simple-to-manage package.

• SQL Server 2005 Developer Edition
Developer Edition is designed to enable developers to build any type of application on top of SQL Server 2005. It includes all the functionality of Enterprise Edition (win32, x64, IA64) but with a special development and test license agreement that prohibits production deployment.

• SQL Server 2005 Standard Edition
Standard Edition is an affordable option for small- and medium-sized organizations. It includes the core functionality needed for noncritical e-commerce, data warehousing, and line-of-business solutions. Standard Edition is optimized to run on win32, x64, and Itanium-based servers.

• SQL Server 2005 Enterprise Edition
Enterprise Edition includes the complete set of enterprise data management and business intelligence features. SQL Server Enterprise offers the highest levels of scalability and availability of all the SQL Server 2005 editions. Additionally, it is optimized to run on x64- and Itanium-based servers, helping you achieve higher levels of database scalability and availability.

• SQL Server 2005 Mobile Edition
Mobile Edition enables you to rapidly develop applications that extend enterprise data management and business intelligence capabilities to mobile devices

[Courtesy: Microsoft Corporation]

Ready Launch Tour 2005

2005-11-21T02:54:00.000-08:00

Microsoft is launching its new products:
1. Visual Studio 2005
2. SQL Server 2005 &
3. BizTalk Server 2006

Starting Dec 9, 2005

Bill Gates will be in Bangalore, India on 9th Dec for the launch.

The products are scheduled to be launched in other major cities also:

Bangalore Dec. 9, 2005
Palace Grounds ,
The Bangalore Palace,
PO- 193, Vasantha Nagar.

Mumbai Dec. 13, 2005
Grand Hyatt
Off Western Express Highway
Santacruz (East).

Pune Dec. 14, 2005
Le Meridien
Raja Bahadur Mill Road.

Chennai Dec. 15, 2005
Le Royal MeridienBusiness District,
1 GST Road, St Thomas Mount.

New Delhi Dec. 16, 2005
The Grand InterContinental
Barakhamba Avenue,
Connaught Place.

Hyderabad Dec. 20, 2005
Taj Krishna
Road No. 1, Banjara Hills.

Kolkata Dec. 21, 2005
The Oberoi Grand15,
Jawaharlal Nehru Road.

If you are interested in joining for the launch, please register on the Microsoft site:
http://www.microsoft.com/india/ready2005/register.aspx

New MVPs announced for Oct' 2005

2005-10-10T00:59:00.000-07:00

Alok Gupta Visual C++
Veer Ji Wangoo SQL Server
Vipin Aravindakshan Windows – Printing/imaging
Omar Al Zabir Visual C#
Roji. P Thomas SQL Server
Balamurali Balaji Visual C#
Md. Shariqul Islam Azad Windows – Shell/User
Radhakrishna M.V. ASP/ASP.NET
Chandra Sekhar Ananthapatnayakuni SQL Server
Chakravarthy DSK Windows Server - Management Infrastructure
Deepak Kumar SQL Server
Narayana Rao Surapaneni ASP/ASP.NET

Calculating Age

2005-08-08T23:18:00.000-07:00

To display the correct age of a person:

declare
@date as DATETIME
SET @date = '09/17/1976'

SELECT CAST( datediff(d, @date, getdate()) / 364 as VARCHAR) + ' Years ' +
CAST( datediff(m, dateadd(year, datediff(d, @date, getdate()) / 364, getdate()), getdate()) as VARCHAR) + ' Months ' +
CAST( datepart(d, getdate()) as VARCHAR) + ' Days' AS Age

List Tables in a database

2005-08-04T03:16:00.000-07:00

The following query lists all the tables that are available in a current database

SELECT db_name() [Database], name [Table Name],
CASE xtype
WHEN 'S' THEN 'System Table'
WHEN 'V' THEN 'View'
ELSE 'User Table' END [Type]
FROM sysobjects
WHERE xtype IN ('S','V','U')
ORDER BY db_name(), xtype, name

User Defined Datatypes

2005-07-30T04:58:00.000-07:00

To list all the User Defined Datatypes in a given database:

SELECT
[name] UDT,
type_name(xtype) Type,
Length,
xprec [Precision],
xscale Scale,
CASE WHEN AllowNulls = 1 THEN 'Yes' ELSE 'No' END [ISNULL]
FROM systypes
WHERE xusertype > 256
ORDER BY [name]

Enable or Disable Triggers

2005-05-26T22:43:00.000-07:00

Specifies that trigger_name is enabled or disabled. When a trigger is disabled it is still defined for the table; however, when INSERT, UPDATE, or DELETE statements are executed against the table, the actions in the trigger are not performed until the trigger is re-enabled

Syntax:

Enable a specific trigger
ALTER TABLE <table_name> ENABLE TRIGGER <trigger_Name>

Disable a specific trigger
ALTER TABLE <table_name> DISABLE TRIGGER <trigger_Name>

Enable all triggers
ALTER TABLE <table_name> ENABLE TRIGGER ALL

Disable all triggers
ALTER TABLE <table_name> DISABLE TRIGGER ALL

SQL Server TechCenter

2005-05-25T22:28:00.000-07:00

The SQL Server TechNet site has been republished as a TechCenter. Just as MSDN DevCenters focus on information for developers, TechCenters focus on information for IT professionals such as DBAs. The site layout of the SQL Server TechCenter is similar to those already published for Exchange and Windows Server 2003. The TechCenters will evolve over time to incorporate customer feedback and deliver new information as it is published.

One requirement for TechCenters is to organize their content into 8 content areas, such as Getting Started and Operations. SQL Server customers often work in one technology at a time (Database Engine, Analysis Services, DTS, etc.) and want to focus on content for that technology. The SQL Server TechCenter uses a technique from the Windows Server TechCenter to support both navigation paths:

Browse by Task

Has one navigation page for each content area, with links to Web content relevant to IT professionals performing those types of tasks. To support technologies, the sets of links on each task navigation page are organized by technology. There is also a Technical Library fly-out in the left-hand column that provides links to the Browse by Task pages.

Browse by Technology

Has one navigation page for each technology, with links to Web content relevant to that technology. To support the content areas, the sets of links on each task navigation page are organized by task. There is also a Technologies fly-out in the left-hand column that provides links to the Browse by Technology pages.

The new TechCenter should improve your ability to find IT professional information for SQL Server. The new navigation pages will, for the first time, give IT professionals a set of links to all the major SQL Server content on the Web, including links to the relevant major nodes in the copy of the SQL Server Books Online in the MSDN Library.

SQL Server 2000 Licensing FAQ

2005-05-25T21:00:00.000-07:00

Review this FAQ to find answers to some common questions about licensing SQL Server 2000.
Select a question from the list to show the answer. You can also view the answers to all of the questions by selecting the following check box

Primary Key vs Unique Key

2005-05-24T03:03:00.000-07:00

Primary Key creates a clustured Index on a table when it is created
Unique Key creates a non-clustured Index on a table when it is created

Primary Key doesn't allow NULL values
Unique Key allows only one NULL value

Maximum Capacity Specifications

2005-05-19T03:52:00.000-07:00

The first table specifies maximum capacities that are the same for all editions of Microsoft® SQL Server™ 2000. The second and third tables specify capacities that vary by edition of SQL Server 2000 and the operating system.

This table specifies the maximum sizes and numbers of various objects defined in Microsoft SQL Server databases, or referenced in Transact-SQL statements. The table does not include Microsoft SQL Server 2000 Windows® CE Edition

SQL Server 2000 Resource Kit

2005-05-18T21:51:00.000-07:00

SQL Server Resource Kit is designed for database administrators and developers who already use SQL Server and want some helpful tips, advanced techniques, and useful tools or samples to enhance their work with SQL Server 2000. It is the premier guide for deploying, managing, optimizing, and troubleshooting SQL Server 2000

Change owner of the database

2005-05-17T23:21:00.000-07:00

To change the owner of the present database, use the stored procedure sp_changedbowner

Syntax:
sp_changedbowner '<login>'

The ownership for databases

Master
Model
Tempdb

cannot be changed

Only members with sysadmin role can execute the stored procedure sp_changedbowner

Error Handling in SQL Server

2005-05-11T22:48:00.000-07:00

This article focuses on how SQL Server – and to some extent ADO – behave when an error occurs. If you are relatively new to SQL Server, I recommend that you start with Implementing.... The article here gives a deeper background and may answer more advanced users' questions about error handling in SQL Server

Remove Spaces - TRIM

2005-05-10T22:02:00.000-07:00

There is no single function to remove leading and trailing spaces from the field. For this you need to:

Remove Training spaces using LTRIM(<column>)
Remove Leading spaces using RTRIM(<column>)

SELECT LTRIM(RTRIM(<column>))

Editions of SQL Server 2000

2005-05-10T08:16:00.000-07:00

Microsoft® SQL Server™ 2000 is available in these editions

Find the Version of SQL Server

2005-05-10T07:11:00.000-07:00

To find the version of the SQL Server, use the global variable @@version

SELECT @@version

Enable or Disable a Constraint

2005-05-10T06:03:00.000-07:00

-- Enable all table constraints
ALTER TABLE <table_name> CHECK CONSTRAINT ALL

-- Disable all table constraints
ALTER TABLE <table_name>NOCHECK CONSTRAINT ALL

-- Disable a specific constraint
ALTER TABLE <table_name>NOCHECK CONSTRAINT <constraint_name>

-- Enable a specific constraint
ALTER TABLE <table_name>CHECK CONSTRAINT <constraint_name>

Rename user-created objects in SQL Server

2005-05-09T21:43:00.000-07:00

Rename user-created object (for example, table, column, or user-defined data type) in the present database:

Syntax:
sp_rename oldName, newName, [Object]

Rename Table
sp_rename 'Employee', 'Employees'
or
sp_rename 'Employee', 'Employees', 'TABLE'

Rename Column
sp_rename 'EName', '[Emp Name]', 'COLUMN'

List Available Databases

2005-05-09T21:19:00.000-07:00

To List the datbases available in your SQL Server and show the path of .MDB file:

SELECT [name] [Database], [filename] [File Path]
FROM master..sysdatabases