Archive

Posts Tagged ‘SQL Server Denali’

SQL Server “Denali” CTP1 Expired…

May 23, 2011 Leave a comment

Today when I started using SQL Server “Denali” CTP1 Management Studio, I wasn’t able to connect to the SQL Server, When I checked the SQL Server Service it wasn’t running.

I opened Event Viewer and checked the System Logs

image

This error message wasn’t much helpful in identifying the issue, so I opened

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log folder and opened ERRORLOG file in NotePad++ to find out the error caused in the startup and found this error information

 

2011-05-23 20:50:54.33 Server      Microsoft SQL Server "Denali" (CTP1) – 11.0.1103.9 (X64)
    Sep 24 2010 21:52:29
    Copyright (c) Microsoft Corporation
    Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

2011-05-23 20:50:54.33 Server      (c) Microsoft Corporation.
2011-05-23 20:50:54.33 Server      All rights reserved.
2011-05-23 20:50:54.33 Server      Server process ID is 1360.
2011-05-23 20:50:54.33 Server      System Manufacturer: ‘VMware, Inc.’, System Model: ‘VMware Virtual Platform’.
2011-05-23 20:50:54.33 Server      Authentication mode is MIXED.
2011-05-23 20:50:54.33 Server      Logging SQL Server messages in file ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG’.
2011-05-23 20:50:54.33 Server      Registry startup parameters:
     -d C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf
     -e C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG
     -l C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2011-05-23 20:50:54.69 Server      Error: 17051, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

I searched for this Error but couldn’t find anything useful to fix the issue

Then I went back to look for older ErrorLog from last week and found this

2011-05-12 22:31:20.77 Server      The evaluation period for your edition of SQL Server expires in 5 day(s).

I then checked “Application Logs” under Event Viewer and found this

image

Now I understood why the SQL Server service is not starting, but still couldn’t understand why this information is not logged in Today’ ErrorLog

I think it will be better to have separate Event Log for SQL Server so that we can easily view SQL Server related events.

Please post your feedback and comments !!!

Advertisements

SQL Server “Denali” – SEQUENCEs–Continued…

May 13, 2011 Leave a comment

After reading my previous article about SEQUENCE one of my friend asked a question about the performance of using Sequence for quick and continuous generation and whether there is a way to create and manage this using Management Studio ?

Yes, SEQUENCE support CACHE option

  1. /* Create Sequence with Cache */
  2.  
  3. CREATE SEQUENCE RunningNumbers
  4. As INT
  5. MINVALUE 1
  6. NO MAXVALUE
  7. START WITH 1
  8. CACHE 500
  9. NO CYCLE;

 

Using CACHE option, you can specify how many values should be pre generated and available ready, you can set the cache size based on your application’ requirement.

Default cache size is 20

To use in Loop for quick processing

To get a range of numbers to use in a loop, you can use sp_sequence_get_range System stored procedure, so that you can get a range and not have to use NEXT VALUE option for each loop call

  1. /* Get Range of Numbers using Sequence */
  2. DECLARE
  3. @RANGE_FIRST_VALUE SQL_VARIANT,
  4. @RANGE_LAST_VALUE SQL_VARIANT,
  5. @RANGE_CYCLE_COUNT INT,
  6. @SEQUENCE_INCREMENT SQL_VARIANT,
  7. @SEQUENCE_MIN_VALUE SQL_VARIANT,
  8. @SEQUENCE_MAX_VALUE SQL_VARIANT;

 

  1. EXEC sp_sequence_get_range
  2. @sequence_name = 'RunningNumbers',
  3. @range_size = 15,
  4. @range_first_value = @range_first_value OUTPUT,
  5. @range_last_value = @range_last_value OUTPUT,
  6. @range_cycle_count = @range_cycle_count OUTPUT,
  7. @sequence_increment = @sequence_increment OUTPUT,
  8. @sequence_min_value = @sequence_min_value OUTPUT,
  9. @sequence_max_value = @sequence_max_value OUTPUT;

  1. SELECT
  2. 'RunningNumbers' AS [Sequence Name],
  3. @range_first_value AS [Sequence First Value],
  4. @range_last_value AS [Sequence Last Value],
  5. @range_cycle_count AS [Range Cycle Count],
  6. @sequence_increment AS [Sequence Increment],
  7. @sequence_min_value AS [Sequence Min Value],
  8. @sequence_max_value AS [Sequence Max Value];

image

You can use [Sequence First Value] and [Sequence Last Value] and use it in your loop to insert data.

How to create and manage SEQUENCE from Management Studio ?

Step 1 : Open SQL Server “Denali” Management Studio, and connect to SQL Server “Denali” instance and go to your database in Object Explorer

Step 2: Expand “Programmability” and then expand "Sequences"  to currently available ones

image

Step 3: To create a new sequence, right click on Sequence and click on “New Sequence”

image

Step 4: Specify the name and other required options such as Start Value, Increment By, etc.. and click “Ok” to create Sequence

image

Step 5: To modify the Sequence, right click on the selected sequence and click “Properties”

image

If you see the above screenshot, you might notice that Data Type, Precision or name of sequence can not be modified. You can change the other values, similarly you will see an option to restart the sequence as well.

Note: To rename you can use the “Rename” option in Context menu.

Step 6: To drop the sequence, right click on the selected sequence and click “Delete

image

Let me know your experiences and feedback about SEQUENCEs

SEQUENCE–How to create Identity Key across tables in SQL Server “Denali” ?

May 12, 2011 Leave a comment

SQL Server “Denali” introduces a new feature called “SEQUENCE”. SEQUENCE is a user-defined object that generates a sequence of numeric values according to specified options.

Its not bound to a table like IDENTITY, You can use SEQUENCE across tables

Supported Data Types for creating SEQUENCE are as follows:

  • TinyInt
  • SmallInt
  • Int
  • BigInt
  • Decimal
  • Numeric
  1. /* Create Sequence With Start Values */
  2. CREATE SEQUENCE RunningNumbers
  3.     START WITH 1
  4.     INCREMENT BY 1;
  5. GO

 

The above code creates a simple SEQUENCE with starting number as 1 and increments by 1

To get the next number in SEQUENCE, you need to use “NEXT VALUE” option

  1. SELECT (NEXT VALUE FOR RunningNumbers) AS [RunningNumbers];

image

The below code might syntactically look same as the SEQUENCE "- RunningNumbers, but if you look at the output, its entirely different

  1. /* Create Sequence Without Start Values */
  2. CREATE SEQUENCE RunningNumbers2
  3.     INCREMENT BY 1;
  4. GO

  1. SELECT (NEXT VALUE FOR RunningNumbers2) AS [RunningNumbers2];

If you see the output from RunningNumbers2, you will be getting a Negative value, its due to not specifying the Start number, so make sure you specify a Positive Start Number if you don’t want your table keys to Negative

image

Other options available in Creating Sequence are as follows:

  1.  
  2. CREATE SEQUENCE RunningNumbers3
  3.    AS tinyint
  4.     START WITH 1
  5.     INCREMENT BY 1
  6.     MINVALUE 1
  7.     MAXVALUE 5
  8.     CYCLE ;
  9. GO

 

You can specify Minimum Value and Maximum Value and whether to Cycle the numbers when reaching maximum value or not

 

  1. SELECT NEXT VALUE FOR RunningNumbers3 AS ID, Name FROM sys.objects ;
  2. GO

 

If you see the below output, for every 5 records, the IDs are recycling and starts from 1 again

image

Restarting the Sequence

To restart the Sequence, you need to ALTER the sequence with RESTART option and start value

  1. /* Restart the Sequence */
  2.  
  3. ALTER SEQUENCE [dbo].[RunningNumbers2]
  4. RESTART WITH 1

  1.  
  2. SELECT (NEXT VALUE FOR RunningNumbers2) AS [RunningNumbers2];

Since we restarted the Sequence with 1, Now we are getting output as 1 for RunningNumber2

image

Find the available Sequences in a database

  1. SELECT
  2.     object_id, name, type_desc, start_value, current_value, increment,
  3.     minimum_value, maximum_value, is_cycling, is_cached, cache_size
  4. FROM sys.sequences

You can Sys.Sequence system view to query the list of available Sequences in a database

image

How to use it as Identity across tables ?

Create tables for each reqion

  1. — Create tables
  2. CREATE TABLE Orders_West
  3.     (OrderID int PRIMARY KEY,
  4.     Name varchar(20) NOT NULL,
  5.     Qty int NOT NULL);
  6. GO
  7.  
  8.  
  9. CREATE TABLE Orders_East
  10.     (OrderID int PRIMARY KEY,
  11.     Name varchar(20) NOT NULL,
  12.     Qty int NOT NULL);
  13. GO
  14.  
  15. CREATE TABLE Orders_South
  16.     (OrderID int PRIMARY KEY,
  17.     Name varchar(20) NOT NULL,
  18.     Qty int NOT NULL);
  19. GO
  20.  
  21. CREATE TABLE Orders_North
  22.     (OrderID int PRIMARY KEY,
  23.     Name varchar(20) NOT NULL,
  24.     Qty int NOT NULL);
  25. GO

 

Insert data to table using Sequences

  1. — Insert Four records
  2. INSERT Orders_West (OrderID, Name, Qty)
  3.     VALUES (NEXT VALUE FOR RunningNumbers2, 'Apples', 2) ;
  4. INSERT Orders_East (OrderID, Name, Qty)
  5.     VALUES (NEXT VALUE FOR RunningNumbers2, 'Oranges', 1) ;
  6. INSERT Orders_South (OrderID, Name, Qty)
  7.     VALUES (NEXT VALUE FOR RunningNumbers2, 'Grapes', 1) ;
  8. INSERT Orders_North (OrderID, Name, Qty)
  9.     VALUES (NEXT VALUE FOR RunningNumbers2, 'Banana', 1) ;
  10.  
  11. GO

 

Query the inserted data

  1. SELECT * FROM Orders_West
  2. UNION
  3. SELECT * FROM Orders_East
  4. UNION
  5. SELECT * FROM Orders_North
  6. UNION
  7. SELECT * FROM Orders_South

 

If you see the Union output from 4 different tables, we are able to create a identity column across 4 tables, This will be very helpful when you horizontally partition data across tables

image

Delete the Sequences

  1. DROP SEQUENCE RunningNumbers;
  2. DROP SEQUENCE RunningNumbers2;

I hope you all find this information about Sequences useful and informative !!!

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

May 7, 2011 Leave a comment

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…

Catch ‘n Throw Errors in SQL Server “Denali”

May 6, 2011 Leave a comment

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”

May 5, 2011 Leave a comment

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.

%d bloggers like this: