Download SQL Server 2012 Developer Training Kit

The SQL Server 2012 Developer Training Kit includes technical content designed to help you learn how to develop SQL Server 2012 database and BI solutions. The SQL Server 2012 Developer Training Kit is a great resource for developers, trainers, consultants and evangelists who need to understand the key improvements introduced in SQL Server 2012 from a developer perspective…. download and read more on…http://www.microsoft.com/en-us/download/details.aspx?id=27721&WT.mc_id=rss_alldownloads_all

Microsoft Training Courses on SQL Server 2012

Microsoft has launched new courses to sharpen your knife in SQL Server. These courses will help experienced professionals to upgrade their skills with course Updating your Database Skills to Microsoft SQL Server 2012 or course Updating your Business Intelligence Skills to Microsoft SQL Server 2012.

For SQL Server these are categorized in below steps:

  • Step 1 – Course 10774 Querying Microsoft SQL Server 2012 (exam code – 461)
  • Step 2 – Course 10775 Administering Microsoft SQL Server 2012 Databases (exam code – 462)
  • Step 3 – Course 10777 Implementing a Data Warehouse with Microsoft SQL Server 2012 (exam code – 463)
  • Step 4 – MCSA: SQL Server

For more details log on to – http://www.microsoft.com/learning/en/us/training/sql-server.aspx

SQL Server Long Drive!

Every one of us must have started their career path in SQL server from some version. But very few of us hold the knowledge of when Microsoft gave birth to the SQL Server. Even I was not the exception for this, but long back I attended one session in Ahmadabad by Pinal Dave who shared the very great information on SQL Server birth and its childhood. Today I am thankful to him and also feeling honour to share such history with all of you. It not a big deal if you don’t know the history (“How many of us know the history of our great leaders?). But I am telling you guys Microsoft has taken a very long drive with SQL server. Rather than saying anything I would prefer go through this link this speaks more than my words…

http://en.wikipedia.org/wiki/Microsoft_SQL_Server

How to execute SQL files using T-SQL?

Sometimes ago I read an article on how to execute the sql file using powershell by Martin Bell. After reading it the question came into my mind is “Why not just use T-SQL? Would that be simpler? “. Indeed, powershell gives you a wide approach of doing the database activities, the same can be achieved using T-SQL as well. The below code snippet gives a overview of “How to execute sql using T-SQL”

DECLARE @fPath VARCHAR(4000) = 'C:\SQL\Script1.sql'
DECLARE @outPath VARCHAR(4000) = 'C:\SQL\Script_exec_log.txt'
DECLARE @serverPath VARCHAR(20) = 'localhost'
DECLARE @sqlString VARCHAR(4000)
DECLARE @dbList VARCHAR(1000), @dbName VARCHAR(100), @rowCount INT
SELECT @dbList=COALESCE(@dbList + ',', '')+ name FROM sys.databases WHERE name like '%Test'
SET @rowCount = @@rowcount WHILE 1= 1
BEGIN
SET @dbName = CASE WHEN (@rowCount = 1 OR @dbList != @dbName) THEN @dbList ELSE SUBSTRING(@dbList, 1, CHARINDEX(',', @dbList)-1) END
SET @sqlString = 'SQLCMD -E -S'+@serverPath+' -d'+@dbName+' -i'+@fPath +' -o'+ @outPath
PRINT @sqlString
EXEC master..xp_cmdshell @sqlString
IF @dbList= @dbName BREAK
SET @dbList = SUBSTRING(@dbList, CHARINDEX(',', @dbList)+1, LEN(@dbList))
END

Microsoft Codename “Atlanta” is on the way….

Today, database administrators (DBAs) have to spend a great deal of time and effort dealing with problems related to misconfiguration of Microsoft SQL Server. Microsoft Codename “Atlanta” is a configuration-monitoring cloud service that will help customers to reduce downtime and improve the performance of Microsoft SQL server deployments. This secure cloud service will help DBAs to:

  • Proactively avoid problems with Microsoft SQL Server through ongoing monitoring and alerting of configuration from the cloud
  • Reduce downtime and improve performance of Microsoft SQL Server through proactive scanning for known configuration issues and comparison with best practices.
  • Resolve issues faster by providing Microsoft, or your own support staff, the ability to get a current view of your configuration, changes that have been made, and use the most up to date solutions to solve them

As per Microsoft "Atlanta" is available by Nov 8, 2010 and the production version will be available by first half of 2011 calendar year. Currently "Atlanta" is available for US based datacenters.

Read more about Microsoft Codename "Atlanta" at… http://www.microsoft.com/systemcenter/en/us/atlanta.aspx

Using Data Profiling Task in SSIS

The Data Profiling task is a new task in the Integration Services toolbox. Data Profiling in SSIS provides profiling functionality for the data. Data Profiling helps to analyze the data more efficiently by providing the statistical information for table(s) in the database such as

· Number of rows in the table

· Number of nulls in each columns of a table

· Number of distinct values in each columns of a table etc.

Why we need Data Profiling? The statistical information generated by Data Profiling can be use to efficiently minimize the data quality issues that might occur from source data.

I have created a sample SSIS package which helps you to understand working with Data Profile Task. For this example I have used AdventureWorksLT database.

1. Open SQL Server Business Intelligence Development Studio (BIDS)

2. Create new SSIS project by selecting the “Integration Services Project”. Now you are ready to develop completely new SSIS project.

3. Selecting Data Profiling Task – Drag the “Data Profiling Task” to Component Flow from toolbox, so that your screen will looks like as below…

SSIS-DataProfileTask-1

4. Setting properties – Data Profiling task needs some inputs to run it such as Source – the input for which the statistics should be collected, Destination – where it should be collected, and for which entities should collect the statistics, This can be achieve by by providing some additional information to Data Profiling Task

  • Open the Data Profiling Task Editor (double –click on the Data profiler Task to open Editor) as  shown below.

SSIS-DataProfileTask-2 

  • Here you can set Source by clicking on Quick profile option. You can  connect to database using the ADO.net Connection option. In this case I have connected to my Local Server and AdventureWorksLT database. and select which statistics you want to calculate as well as view such as Number of rows in the table, Number of nulls in each columns of a table, Number of distinct values in each columns of a table etc. Here you can select just one or all tables with Table or View option.

SSIS-DataProfileTask-3

1. Source connection string – When you create any connection string in package, it always preserve the connection string in connection manager.

2. Statistical information calculation options.

  • You can set the destination file by clicking on General menu in Data Profiling Editor as shown in the below screen shot

SSIS-DataProfileTask-4 

5. Data Collection – Now we are ready to execute the package to collect the statistics for selected tables. Execute the package to collect the statistical information. This will store information at the Destination you have selected (step # 4)

6. Viewing the Data Profiling output – To view the Data Profiling output you need Data Profile Viewer, which can be found under Programs -> Microsoft SQL Server 2008 -> Integration Services OR (C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DataProfileViewer.exe). Refer the below screen shot.

SSIS-DataProfileTask-5 

This is just a “How to use Data Profiling Task in SSIS” article, for further information Click here.

Types of Stored Procedures & Functions

It may sound tricky question if someone ask you “What are the types of Stored procedures?”. Because you might have heard of “Type of Functions”. But some of newbie’s or sometimes some of our DB professionals got confused when they hear this question  (just like me – in my earlier days). In this short article, I’ll summarize the Types of Stored procedure. I hope it helps everyone.

Types of Stored procedure

  • System Stored Procedures – System Stored procedures are nothing but in-built stored procedures which deals which the internal metadata of the SQL Server. There is bunch of system stored procedure, for more details refer to MSDN or link : http://msdn.microsoft.com/en-us/library/ms187961.aspx

EX. sp_renamedb. This system procedure helps to rename the database name.

  • User-defined Store Procedures  – User Defined Store procedures are deals with the relational data in the database, created by the end-user.
    • Simple Stored procedure – In this type of SP, there are no In / Out parameter involve. Stored procedure with simple SQL Query with / without JOINS.

      CREATE PROCEDURE dbo.usp_GetPersons()
      BEGIN
          SET NOCOUNT ON

          SELECT * FROM dbo.Persons
      END

  • Parameterized Stored procedure – In this type of SP, there are In / Out / Both parameter involve.

    CREATE PROCEDURE dbo.usp_GetPersons( @PersonKey )
    BEGIN
        SET NOCOUNT ON

        SELECT * FROM dbo.Persons WHERE PersonKey = @PersoneID
    END