SQLThreads

SQL Server 2014 Free eBook

2015-02-21T11:15:00.000+05:30

Microsoft is offering a Free eBook on SQL Server 2014. Here is the link to download PDF file

Download Book: http://aka.ms/684751pdf

Happy Learning !!!

Harsh Shah

Get the list of tables which does not have Indexes

2015-02-17T21:45:00.000+05:30

Here is the quickest way to know the list tables in SQL Server which doesn't have Indexes.

This will be very useful when we have large number of tables in our SQL Server database.

SELECT DISTINCT so.object_id,
                so.NAME
FROM   sys.indexes si
       INNER JOIN sys.objects SO
               ON SO.object_id = si.object_id
WHERE  SO.type = 'U'
       AND si.index_id = 0

I am sure there are many other ways to get the same list.. this is one of them.

Try Microsoft Products for Free in Virtual Lab Environment

2015-02-01T23:00:00.000+05:30

Everthought of trying Microsoft Product for Free before installing it ? Or just wanted to learn about a new feature Microsoft recently introduced i.e. even before installing it?

Like this there are various reasons we wanted to try or to just learn something new.

If your answer is yes to above questions, then Microsoft is offering a complete Virtual Environment - with Hands-On-Lab for testing various product features which you can't do it considering various limitations like - Can't install on a production Environment, Don't have enough infrastructure ready to setup the data etc.. etc..

Microsoft is offering HOL (Hands On Lab) for various products and you can check it out here

https://technet.microsoft.com/en-us/virtuallabs?id=J+avMPtWlXk

There are system requirements needs to be followed to run the Hands on Lab which you can find it here

Check System Requirement :

https://vlabs.holsystems.com/vlabs/SystemRequirements.aspx

Hands on Lab

How to set Auto Commit OFF in SQL Server or Disable Auto Commit in SQL Server

2014-09-25T00:18:00.004+05:30

Developers moving from Oracle to SQL Server often face issues with commit transactions. Because Oracle doesn't do AUTO COMMIT but SQL Server does and here developer faces the issues. Sometimes during testing we delete rows which we shouldn't be and can't ROLLBACK as these transactions in SQL Server are auto committed by default.

So the question is can we set this off ? Yes, we can and here is how you can do that.

There are two ways to achieve this.

You can disable AUTO COMMIT at database level as shown below.

Option 1

Open SQL Server Management Studio
Right Click on Server and Select Properties
You will now be in Server Properties
Go to Connections page and check the options as shown below.

Option 2

Connect to Database Instance where you want to set Auto Commit off.
Go to SQLQuery window
Execute below command

SET IMPLICIT_TRANSACTIONS ON;

Once this is executed , you can use ROLLBACK to undo the changes or COMMIT to save the changes.

How to Resolve - O2SS0346: SQL Server Migration Assistant for Oracle Error message: Referencing columns not nullable ?

2014-09-22T21:55:00.001+05:30

During SSMA migration from Oracle to SQL Server we generally face the below error

O2SS0346: SQL Server Migration Assistant for Oracle Error message: Referencing columns not nullable

The meaning of above error is : In oracle we have a column which is marked for accepting Null (can accept Null) and also a part of Referential integrity.

SQL Sever raise this as an error because, if the column is part of Referential Integrity then how can it be a Null ?

You can still force SQL Server to accept this during migration using below settings

How enable this option

Go to SSMA Tools Menu
Select Project Settings, you will be shown with the below screen
As per the below screen shot enable the option
Save it and re-run the report.

SQL Server Migration Assesment 6.0 (SSMA) is available now for download

2014-09-10T10:01:00.001+05:30

The new and latest version of SSMA is available now for download. The new version brings array of new enhancements.

For beginners , SQL Server Migration Assistant known as SSMA is a tool which helps us to move variety of database to SQL Server Database. It assists us in schema migration and data migration from other database to SQL Server Database.

In my future post (coming up soon) I will be showing how we can migrate from other database to SQL Server.

Following are the enhancements SSMA 6.0 gives us.

Materialized View support for Oracle
Memory Optimized tables for Oracle
Improved Azure SQL DB code conversion
Extension pack functionality moved to schema to support Azure SQL DB
Performance improvements tested for databases with over 10k objects
UI improvements for dealing with large number of objects
Highlighting of “well known” LOB schemas (so they can be ignored in conversion)
Conversion speed improvements
Show object counts in UI
Report size reduction by more than 25%
Improved error messages for unparsed constructs.

Download it from here

SSMA for Access: http://www.microsoft.com/en-us/download/details.aspx?id=43690

SSMA for MySQL: http://www.microsoft.com/en-us/download/details.aspx?id=43688

SSMA for Oracle: http://www.microsoft.com/en-us/download/details.aspx?id=43689

SSMA for Sybase: http://www.microsoft.com/en-us/download/details.aspx?id=43691

You can also post your question to SSMA team at : ssmahelp@microsoft.com

So get on to SQL now :)

Happy Learning

Microsoft SQL Server: The Data and BI Platform for Today and Tomorrow

2011-08-07T23:43:00.000+05:30

Download AdventureWorks sample database for SQL Server–Denali CTP1

2011-04-13T00:29:00.003+05:30

AdventureWorks for Denali is available now and can be downloaded from the below link.

http://msftdbprodsamples.codeplex.com/releases/view/55330

Note:

1. These databases cannot be installed on any version of SQL Server other than SQL Server code-named "Denali" CTP1.
Microsoft SQL Server "Denali" CTP1 must be installed.
Full-Text Search must be enabled

Virtual Lab for Data Tier Applications in SQL Server 2008 R2 and Visual Studio 2010

2011-04-13T00:29:00.001+05:30

Today I found the virtual lab for Data Tier Applications in SQL Server 2008 R2 and Visual Studio 2010 on the Teched 2011 – Pre Event website. So thought to share with you all.

Following is the link to Virtual Lab: https://cmg.vlabcenter.com/default.aspx?moduleid=4961fec7-46a4-42d3-b37d-f1f3b1112ee1

Note: Before you proceed with the virtual Lab , be sure to check the system requirement link available on the same link.

Metro Early Adoption Program for SQL Server Code Name "Denali"

2011-04-10T12:02:00.000+05:30

This video introduces the Metro Early Adoption Program for SQL Server Code Name "Denali", a program you can self-nominate a project or solution that you think is a good fit for the next release of SQL Server.

If you are unable to view video in my blog then you can visit the below link

Visit Channel 9 Video page here

SSIS improvements in SQL Server code name-Denali - Channel 9 Video

2011-04-10T11:28:00.002+05:30

Get current Identity value, Seed value and Increment value in SQL Server

2010-12-05T10:58:00.001+05:30

While working with IDENTITY we might want to know the current value of the IDENTITY, the SEED value and the Increment value. So here is the way by which we can quickly get this information.

SELECT IDENT_CURRENT ('customer') AS CurrentIdentity,
IDENT_INCR('customer') AS Incremented_By,IDENT_SEED('customer') AS SeedValue;

--create customer table
CREATE TABLE customer (custid INT IDENTITY NOT NULL,custname VARCHAR(10));

--insert value
INSERT INTO customer(custname) values ('cust1');

--get the Identity values
SELECT IDENT_CURRENT ('customer') AS CurrentIdentity,
IDENT_INCR('customer') AS Incremented_By,IDENT_SEED('customer') AS SeedValue;

Using the above example, I have inserted two records into the customer table. Here is the result of the current Identity values.

Happy Learning !!!

SQL Server Migration Tools - for Oracle/MySQL/Sybase

2010-08-01T18:03:00.001+05:30

Are you planning to migrate to SQL Server....? Don't forget to checkout the Migration Assessment and Planning Toolkit from Microsoft for SQL Server 2008.

http://www.microsoft.com/sqlserver/2008/en/us/assessment-tools.aspx

Also check out the migration related more information/resources at

http://www.microsoft.com/sqlserver/2008/en/us/migration.aspx

Happy Learning !!!

Download SQL Server 2008 R2 Developers Training Kit (June 2010 Update)

2010-07-31T23:01:00.001+05:30

An update for SQL Server 2008 R2 Developers Training Kit (June 2010 Update) has been released.

The SQL Server 2008 R2 Update for Developers Training Kit is ideal for developers who want to understand how to take advantage of the key improvements introduced in SQL Server 2008 and SQL Server 2008 R2 in their applications, as well as for developers who are new to SQL Server. The training kit is brought to you by Microsoft Developer and Platform Evangelism.

Please go through the below link as it contains lot of information about contents in this Training Kit update.

Download link : http://www.microsoft.com/downloads/details.aspx?familyid=FFFAAD6A-0153-4D41-B289-A3ED1D637C0D&displaylang=en

Happy Learning !!!

SQL Server Driver for PHP 2.0 CTP2 – June 2010

2010-07-31T22:57:00.001+05:30

As of June 2010, the SQL Server Driver for PHP V2.0 CTP2 is available for download.

The SQL Server Driver for PHP is a PHP 5 extension that allows for the reading and writing of SQL Server data from within PHP scripts. The extension provides a native procedural and PDO interfaces for accessing data in all editions of SQL Server 2005 and later including SQL Azure.

Please don’t forget to check the System Requirements. One thing I noticed here is, this driver is for Any edition of SQL Server 2005 or SQL Server 2008.

Click here to get more information : http://www.microsoft.com/downloads/details.aspx?FamilyID=DF4D9CC9-459C-4D75-A503-AE3FCEB85860&displaylang=en

Follow SQLPHP blog here

Happy Learning !!!

Deprecated Database Engine Features in SQL Server 2005

2010-07-20T12:34:00.001+05:30

Often while upgrading to SQL Server 2005 from the earlier version of SQL Server, you might come across a situation where some of the features are deprecated or not supported. So here is the list of deprecated Database Engine Features in SQL Server 2005

http://msdn.microsoft.com/en-us/library/ms143729(SQL.90).aspx

Happy Learning !!!

Download SQL Server 2008 Service Pack 2 CTP

2010-07-18T08:46:00.001+05:30

The CTP version of SQL Server 2008 Service Pack 2 (SP2) is now available. You can use these packages to upgrade any of the following SQL Server 2008 editions:

Enterprise
Standard
Evaluation
Developer
Workgroup

More Info from MSDN Website : click here

What's New:

SQL Server Utility The SQL Server 2008 R2 Utility supports SQL Server 2008 instances that have SP2 installed. The SQL Server Utility models SQL Server-related entities in a unified view. Utility Explorer and SQL Server Utility viewpoints in SQL Server Management Studio provide administrators a holistic view of resource health through an instance of SQL Server that serves as a utility control point (UCP). For more information about the SQL Server Utility, see Managing the SQL Server Utility.
Data-tier Applications SQL Server Management Studio in SQL Server 2008 R2 supports all data-tier application (DAC) operations for SQL Server 2008 instances that have SP2 installed. DAC is an entity that contains all databases and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects instead of having to manage them separately. A DAC allows tighter integration of data-tier development with the development of the associated application code. It also gives administrators an application level view of resource usage in their systems through the utility control point. For more information about data-tier applications, see Understanding Data-tier Applications.
Reporting Services in SharePoint Integrated Mode (Feature Pack) The Microsoft SQL Server 2008 Service Pack 2 (SP2) Reporting Services Add-in for Microsoft SharePoint Products 2007 allows you to connect to SQL Server 2008 R2 Report Servers. The new features include:
- Improved handling of credentials and shared data sources.
- Utilization of the existing instances of Report Builder configured on the Report Server.
- Support for Report Part Gallery, shared datasets, and shared report items.
- Report Builder 3.0
- ATOM feed renderer. For more information, see the ‘Rendering Reports to Data Feeds’ section in "What's New in SQL Server 2008 R2".

Note: While this version of the add-in supports SQL Server 2008 Report Servers, the new features are only supported when connecting to SQL Server 2008 R2 report servers. Please note that installation of the 2008 SP2 add-in is not supported on SharePoint 2010 Products.

Components of an Integration Services Package

2010-07-03T15:01:00.001+05:30

Integration Services provides various objects to build our SSIS package.Each of these objects has their own characteristics and play an important role in the package building and execution. So let’s understand what they are and what they do.

What is package?

A package is a collection of control flow elements, data flow elements, connection managers,event handlers, variables and various configurations. We use these objects and assemble them into a single package.
It’s a unit of work that is retrieved, executed and saved.

following is the quick summary of the objects that builds a package.

Object	Description
Control Flow elements	Implements workflow in a package. Process oriented. Interacts with other processes Executes in a linear fashion.
Data Flow elements	Contains sources and destinations. Information oriented Extracts,Transform and loads the data from various/multiple sources.
Connection Manager	Connects to the different types of data sources (for e.g. Flat File, Oracle, SQL Server,Excel files, csv files and many other data sources) Extracts and loads the data.
Variables	provides temporary storage during the package execution It can be used in expressions to update values dynamically during the transformations. Used to update values for the property expressions Used to define conditions for the precedence constraints
Event Handlers	Runs in response to the events raised by the packages, tasks,containers.
Log Providers	Provides logging mechanism to packages, tasks, containers. Can provide/log various important details about the package execution.

Happy Learning !!!

Check current Isolation level in SQL Server

2010-05-30T10:58:00.002+05:30

Often we wanted to know the current isolation level set for the SQL Server database. This can be easily checked with the following DBCC command.

DBCC USEROPTIONS;

This command returns the SET options active/configured for the current connection.

Result:

How to get the list of all the databases in SQL Server

2010-05-26T00:41:00.001+05:30

To get the list of all the databases in SQL Server use the following system stored procedure

exec sp_databases;

Result:

DATABASE_NAME	DATABASE_SIZE	REMARKS
master	5120	NULL
model	2816	NULL
msdb	11264	NULL
ReportServer$SQL2008DEV	10240	NULL
ReportServer$SQL2008DEVTempDB	3072	NULL
SQLThreads	4096	NULL
tempdb	8704	NULL

Alternatively you can also use SQL Query as given below.

SELECT * FROM sys.databases;

Download Free E-Book - Introducing Microsoft SQL Server 2008 R2

2010-05-25T00:21:00.001+05:30

Dear Readers, today I came across a very good Free eBook on SQL Server 2008 R2. This a free ebook from Microsoft and you can download it from here – Introducing Microsoft SQL Server 2008 R2

The book contains following chapters

1. SQL Server 2008 R2 Editions and Enhancements
2. Multi-Server Administration
3. Data-Tier Applications
4. High Availability and Virtualization Enhancements
5. Consolidation and Monitoring
6. Scalable Data Warehousing
7. Master Data Services
8. Complex Event Processing with StreamInsight
9. Reporting Services Enhancements
10. Self-Service Analysis with PowerPivot

Happy Learning !!!

Execute SSIS Package based on File Existence

2010-05-23T10:35:00.001+05:30

Often we have a requirement to check the file existence before we execute our package and it’s a best practice as well. So let’s understand how to achieve this in SSIS.

Quick Answer : The File.Exists () method will check the file existence on the specified path. The File.Exists() method will be available once you include System.IO; namespace to your script editor.

Complete Demo.

Open the SSIS Package/Create a new SSIS Package
Go to Control Flow
Create three new variables with the Package Level Scope. – See the image 1 below
Create one file (Excel or Flat File) as per your requirement and store it on a C:\
Variables declaration
1. FileExists – type is Boolean
2. FileName – type your file name in a value column.
3. FilePath – type your file path in a value column
Go to Control Flow and drag the Script Task to the container.
Double click on the Script Task and go to Script Tab and assign the ReadOnly and ReadWriteVariables as shown in the below Image 2
Click Edit Script
Now, you will be in the script Editor.
Declare the namespace as using System.IO;
Go to Main() method and copy the below code.
File.Exists will check the file existence for the specified path

public void Main()
{
string fileNameWithPath=string.Empty ;

//prepare the complete file path with name

fileNameWithPath =Dts.Variables["FilePath"].Value.ToString () + Dts.Variables["FileName"].Value.ToString ();

            //check for the file existence
            if (File.Exists(fileNameWithPath))
                Dts.Variables["FileExists"].Value = true;

//display the result
MessageBox.Show("File Exists " + Dts.Variables["FileExists"].Value.ToString());

// TODO: Add your code here
Dts.TaskResult = (int)ScriptResults.Success;

}

Now you are ready with the code. The above code will return true or false based on the file existence and you can use this value in the later part of the package to make conditional execution of the SSIS Package.

Conditional Execution of the package using Precedence Constraints

First connect your Script Task with the other Task Container i.e. Data Flow Task or any other task which you want to execute based on the file existence.
Now, double click on the green line (pipe line) which connects script task to another task.
This will open a Precedence Constraint Editor .
Under the Constraint Options group Select --> Evaluation Operation as Expression as show in Image 3
In the Expression text box type the expression you want. In our case it' is : @FileExists=true. This means if the variable is true the the next task will execute.
See the Image 3 for more clarity

Images

Image 1: Variables Declaration

Image 2 – Script Task Editor

Image 3 – conditional Execution using SSIS Precedence Constraints.

Happy Learning !!!

Download SQL Server 2008 R2 Guide

2010-05-23T00:42:00.001+05:30

The SQL Server 2008 R2 Guide provides key information and resources on evaluating the product,installation instructions and a guided tour. This seems to be very useful document as it gives quick information SQL Server 2008 R2 capabilities.

Download the document here

What is Task Host Container in SSIS?

2010-05-23T00:28:00.000+05:30

What is Task Host Container in SSIS?

It’s a default container where every single tasks fall into.
If we don’t specify a container then the task will fall into Task Host container.
The Task Host is not configured separately, instead, it is configured when we set the properties of the task it encapsulates.
SSIS extends variables and event handlers to the task through task host container.

Happy Learning !!!

SQL Server 2008 R2 Overview resources

2010-05-23T00:17:00.000+05:30

Get a very good high level overview on SQL Server 2008 R2 with the videos/documents/Hands on labs available on Channel 9.

The resources available on Channel 9 contains information about the new features and improvements in SQL Server 2008 R2. Some of the interesting features on which information available is:

Creating Data Tier Application with VS 2010 and SQL Server 2008 R2.
Lesson on Introduction to StreamInsight
PowerPivot feature
Reporting Services features and many other new features.

You can view and download the resources from here.

Happy Learning !!!