Archive for the ‘Trainings’ Category

In the earlier days, learning a technology not only required passion but it pinched the pocket as well, due to the new hardware and software requirements. And in most of the cases, one has to setup a complete lab to learn it.
However, SQL server has resolved this problem since SQL Server 2005 and keep improving these labs namely Virtual Labs (http://www.microsoft.com/en-us/sqlserver/learning-center/virtual-labs.aspx). Basically these virtual labs are virtual machines and you do not need to do any installation or complex setup for it. You just need to connect it through internet for free & immediate use.  (Internet Explorer recommended).
Once you start the lab, it will give you the hands-on-lab material as well as lab for the particular area of SQL Server. In other words, it will guide you about what is new in the technology and how to learn it. SQL Server provides 16 virtual labs in different areas of SQL Server 2012, which are listed below:

  1. Introduction to SQL Server 2012 AlwaysOn Availability Groups
  2. Data Platform Showcase on SQL Server 2012
  3. Exploring new T-SQL features in SQL Server 2012 (SQL 202)
  4. Exploring SQL Server Data Tools (SSDT) in Visual Studio 2012
  5. Installing SQL Server 2012 on Windows 2012 Server Core
  6. Installing SQL Server 2012 on Windows Server 2012
  7. Upgrading to SQL Server 2012 from SQL 2005/2008 (SQL 208)
  8. Implementing Transparent Database Encryption
  9. Implementing and Managing AlwaysOn Availability Groups
  10. Creating a PerformancePoint Services dashboard based on a SQL Server 2012 BI Semantic Model
  11. Optimizing a SQL Server 2012 Tabular BI Semantic Model
  12. Exploring Power View in SQL Server 2012
  13. Creating a SQL Server Reporting Services Report based on a Tabular BI Semantic Model
  14. Developing a SQL Server 2012 Tabular BI Semantic Model using SQL Server Data Tools
  15. Creating a PerformancePoint Services dashboard based on a SQL Server 2012 BI Semantic Model
  16. Creating a PowerPivot workbook in Excel 2013

I found it very beneficial, specially for new bie (SQL Developers) that they can learn scripting on SQL Server without having knowledge of how to install and configure SQL Server. Let me know your feedback.

Read Full Post »

In my earlier articles, I wrote about THROW statement and its enhanced features that give us more control over the debugging.
I also implemented it with some clients and found it much more effective than raiserror. Given below are the guidelines that must be followed to avoid any error.

  1. We usually do not use (;) semicolon terminator in SQL Server, however it is recommended to use.  But make sure to use it prior to any statement before THROW statement. Given below is the example.
    SET @VALUE = 1 / 0
    PRINT 'ERROR';--Do not forget to put this semicolon
    --prior to THROW Statement

    If you fail to use the semicolon terminator prior to THROW statement, SQL Server will generate given below error message.
    Msg 102, Level 15, State 1, Line 7
    Incorrect syntax near ‘THROW’

  3. Sometimes we pass THROW statement without parameter, so make sure that THROW Statement is inside CATCH block as shown in the above example. If you fail to do it, SQL Server will generate given below error message.Msg 10704, Level 15, State 1, Line 9
    To rethrow an error, a THROW statement must be used inside a CATCH block. Insert the THROW statement inside a CATCH block, or add error parameters to the THROW statement.
  4. We usually pass the customized message_id in the THROW statement, make sure you use message_id between 50000 and 2147483647. Given below is the example.
    EXEC sys.sp_addmessage @msgnum = 80000 ,@severity = 16
    ,@msgtext = N'This is a test message.'
    ,@lang = 'us_english';
    THROW 80000 , N'This is a test message', 1;

    if you fail to do it, SQL Server will generate given below error message.
    Msg 35100, Level 16, State 10, Line 9
    Error number 49999 in the THROW statement is outside the valid range. Specify an error number in the valid range of 50000 to 2147483647.

  5. THROW does not allow the substitution parameter, so if you need to use it, always use with the FORMATMESSAGE function. Given below is the example.
    EXEC sys.sp_addmessage
    @msgnum = 90000
    ,@severity = 16
    ,@msgtext = N'This is a %s test message'
    ,@lang = 'us_english';
    DECLARE @msg NVARCHAR(50) =
    FORMATMESSAGE(90000, N'raresql');
    THROW 90000, @msg, 1;
  6. Cleaning :

    USE master;
    EXEC sp_dropmessage 80000;
    EXEC sp_dropmessage 90000;

    Reference : MSDN

Read Full Post »

Adventureworks sample database launched with SQL Server 2012 and you can download it from the codeplex.

Given below are the links :

After downloading the appropriate data files lets proceed with the installation :

Installation via T-SQL :

Step 1:

You should copy the data file to any appropriate location but it should not be in the root directory due to the security issue.

If you do so, it generates error.

Step 2 :

Once you download the file, you will notice that it has only data file and not log file. So, we need to write the script given below to create the database and  in order to build a new log file, we need to use ATTACH_REBUILD_LOG.

Create Database [AdventureWorks2012]
On (FILENAME=  N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf')

For testing purpose lets put it in the root directory :

Create Database [AdventureWorks2012]
On (FILENAME=  N'C:\AdventureWorks2012_Data.mdf')

It generates an error :
File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Log.ldf” may be incorrect.
Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘C:\AdventureWorks2012_log.ldf’.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘AdventureWorks2012’. CREATE DATABASE is aborted.

Installation via SSMS :
Step 1:

Should be same as above.

Step 2 :

Right click on the databases and click on Attach. After that click on Add button and browse the file (AdventureWorks2012_Data).

After that, if you press  OK button, it will reflect error as given below.

So, you need to select the file name (AdventureWorks2012_log.ldf) having file type “log”  and message “Not Found”  and press the Remove button.

After that press OK to finish the installation.

Now, you can see the “AdventureWorks2012” database in SSMS.

Read Full Post »