Category: SQL Server

How to identify what features has been installed in your SQL Server ?


One of my friend recently asked me a question, How to view the list of features installed in the SQL Server easily without going through “Programs and Features” or “Services”.

Yes, there is an easy way

SQL Server 2008 / 2008 R2 / Denali provides “Installation Center” using which we can view features of all locally installed SQL Server versions 2000 / 2005 / 2008 / 2008 R2 / Denali including the SQL Express editions

Please follow the below steps to do that

1. Click on “Start” –> “All Programs” –> “Microsoft SQL Server 2008 R2” –> “Configuration Tools” –> “SQL Server Installation Center

image

2. Click on “Tools”

image

3. Click on “Installed SQL Server features discovery report”

image

The following report will be generated and opened in your browser.

image

The report displays the SQL Server version Name, Instance Name and its ID, Feature Name, Installed Language ID, SQL Server Edition, Version Number and whether its clustered or not.

Output from SQL Server “Denali”

image

I hope you all find this useful…

Advertisement

Catch ‘n Throw Errors in SQL Server “Denali”


SQL Server “Denali” adds a new feature in Error Handling called “THROW”

Current version already has RAISERROR, which you can already use to raise Error from your procedures, then why do we need this new “THROW” option,

Let us see the differences

/* RAISERROR */

BEGIN TRY 
    SELECT 1/0 
END TRY 
BEGIN CATCH 
    
    DECLARE @ERRORNUMBER INT = ERROR_NUMBER() 
    DECLARE @ERRORMESSAGE VARCHAR(500) = ERROR_MESSAGE() 
    
    SELECT @ERRORNUMBER, @ERRORMESSAGE

    RAISERROR(@ERRORNUMBER, 16, -1) 
END CATCH

In the above code, we are creating Divide by Zero error in the Try Block and then Catching that Error in Catch Block and Display the Error details and then need to return the same error to calling application

Output from SELECT:

image

Msg 2732, Level 16, State 1, Line 11 Error number 8134 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000.

RAISERROR(@ERRORNUMBER, 16, –1) causes the above error instead of returning that error to application

See the same implementation using THROW and how it works

/* THROW Demo */

BEGIN TRY 
    SELECT 1/0 
END TRY 
BEGIN CATCH 
    PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR);
    PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR);

    THROW;
END CATCH 
(0 row(s) affected)
Error Number: 8134
Error Line: 5
Msg 8134, Level 16, State 1, Line 5
Divide by zero error encountered.

 

The Error number and Error Line was printed and then the same error was thrown back to calling application

You can use THROW to log errors in CATCH block and return the same error back to calling application easily.

Server Side Paging simplified in SQL Server “Denali”


SQL Server “Denali” is the new upcoming version of SQL Server, it has numerous new features

Let us review the new built-in Paging feature “FETCH FIRST”, “FETCH NEXT”

–How to Fetch 1st Page of 10 Rows

USE [AdventureWorks2008R2]
GO

DECLARE @PageNumber INT = 1
DECLARE @PageSize INT = 10

SELECT [ProductID],[Name],[ProductNumber],[MakeFlag],[FinishedGoodsFlag],[Color],[StandardCost],[ListPrice]
  FROM [Production].[Product]
  ORDER BY [Name]
  OFFSET (@PageNumber * @PageSize) – @PageSize ROWS
  FETCH FIRST @PageSize ROWS ONLY

–How to Fetch 2nd Page of 10 Rows

SET @PageNumber = 2

SELECT [ProductID],[Name],[ProductNumber],[MakeFlag],[FinishedGoodsFlag],[Color],[StandardCost],[ListPrice]
  FROM [Production].[Product]
  ORDER BY [Name]
  OFFSET (@PageNumber * @PageSize) – @PageSize ROWS
  FETCH NEXT @PageSize ROWS ONLY;

— How to Fetch all records skipping first 100 Records

SELECT [ProductID],[Name],[ProductNumber],[MakeFlag],[FinishedGoodsFlag],[Color],[StandardCost],[ListPrice]
  FROM [Production].[Product]
  ORDER BY [Name]
  OFFSET 100 ROWs

— How to implement Customizable Paging support using the new feature

–Create a Personalization table to store PageSettings

CREATE TABLE PageSettings
(
    LoggedInUserName    VARCHAR(128),
    PageSize        Int
)

–Add some Page setting Records

INSERT INTO PageSettings (LoggedInUserName, PageSize) Values (‘bill’,10)
INSERT INTO PageSettings (LoggedInUserName, PageSize) Values (‘gates’,20)

— Create a Sample Stored Procedure to implement customized paging

CREATE PROCEDURE sp_FetchProducts
(
    @LoginName VARCHAR(128)
)
As
BEGIN

SELECT [ProductID],[Name],[ProductNumber],[MakeFlag],[FinishedGoodsFlag],[Color],[StandardCost],[ListPrice]
  FROM [Production].[Product]
  ORDER BY [Name]
  OFFSET 10 ROWS
  FETCH NEXT (SELECT PageSize From PageSettings WHERE LoggedInUserName=@LoginName) ROWS ONLY 

END

–Execute the Stored Procedure under login “Bill”

EXEC sp_FetchProducts @LoginName = ‘bill’

Output: Stored Procedure returns 10 Records

image

–Execute the Stored Procedure under login “Gates”

EXEC sp_FetchProducts @LoginName = ‘gates’

Output: Stored Procedure now returns 20 Records

image

You can even customize this solution further Table wise as well.

I hope all the SQL developers will enjoy this feature, since it reduces lot of coding for implementing paging using Table Variables or Temp Table or CTEs.

SQL Diag Configuration Tool


SQL Diag Configuration Tools is an easy to use tool which will help you in configuring SQLDiag.xml to collect performance data from SQL Server 2005/2008/2008 R2

SQL Diag Configuration Tool is a free tool available on Codeplex. You can download it from the following link

http://sdct.codeplex.com/ 

Let us walkthrough the steps of using SQL Diag Configuration Tool

After installation, run the tool

image

Select your desired SQL Server version and then click on “Authentication Method” option available on left side

image

Specify the Server name and SQL Server Instance Name on which you are planning to use the SQLDiag.xml and then click “Machine Wide Diagnostics” option

image

Select the Events Logs you want collect as part of performance data and then click on “Instance Specific Diagnostics”

image

If you want to collect Blocking information, select “Blocking” and the specify the Maximum File Size of log and the maximum number of files and the polling interval on which the Blocking information should be collected from SQL Server

Next Click on “Profiler" Options

image

Click “Enable SQL Tracing” to collect Profiler information and select a Trace Template.

Available Trace Templates are

image

After selecting, you can also add further Events you want to collect and then specify the maximum file size, maximum number of files and polling interval on which profiler information should be collected from SQL Server

Next click “Perfmon Options” to proceed

image

Select the Performance Counters which you want to collect or select the predefined template available and then specify Maximum File Size and the polling interval on which performance counter data should be collected

Next Click on “Producers” to enable the Producers options

image

Next Click on the “Analyzers” to enable the required Analyzers

image

Click “Save” to SQLDiag1050.xml and use it as input for SQLDiag to collect SQL Server Performance Data

You can use the  “Open” option reload the saved SQLDiag xml file and modify the profiler or perfmon or other above specified options.

I hope you find this useful…

Migrating Access Databases to SQL Server “Denali”


SQL Server Migration Assistant for Access 5.0 has been released and available for download here

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=4b37df74-7522-41cf-9c1a-01b6415d9608

New version now supports SQL Server “Denali”

image

%d bloggers like this: