Update Table Joined with other on the basis of CASE statement

Please check the below example;

Declare @vTableA Table (CINAME varchar(100),  SerialNo varchar(100),   Status varchar(100),    UniquekeyCiname_serialno varchar(100))
Declare @vTableB Table (CINAME varchar(100),  SerialNo varchar(100),   Status varchar(100),    UniquekeyCiname_serialno varchar(100))

Insert into @vTableA
Select 'ASDAS','23SSD',NULL,'ASDAS23SSD'
Union All
Select 'WER34','',NULL,'WER34'
Union All
Select 'DFDFS','3239',NULL,'DFDFS3239'
Union All
Select 'ERTER',NULL,NULL,'ERTER'


Insert into @vTableB
Select 'ASDAS','23SSD','DEPLOYED','ASDAS23SSD'
Union All
Select 'WER34','5335','IN TRANSIT','WER345335'
Union All
Select 'DFDFS','3239','LOST','DFDFS3239'
Union All
Select 'ERTER','678W','DEAD','ERTER678W'


Select * from @vTableA
Select * from @vTableB

Update a
Set a.[Status] = b.[Status] 
From @vTableA a
Inner Join @vTableB b on Case when IsNull(a.SerialNo,'')<>'' then a.UniquekeyCiname_serialno else a.CINAME end = Case when IsNull(a.SerialNo,'')<> '' then b.UniquekeyCiname_serialno else b.CINAME end

Select * from @vTableA
Select * from @vTableB

Implement Log Shipping with AlwaysOn Availability Group

Audience

This article is for those who understands bot AlwaysOn Availability Group and Log Shipping. I will not go into the details explaining the both technologies. The article will only cover the tricky part that is involved to implement Log Shipping with AlwaysOn Availability Group.

Scenario

Let’s suppose you have AlwaysOn Availability Group implemented. Assume you have more than one databases part of the availability group but due to some technical requirements, you need to provide a read only access of only one database. Here, Log Shipping can help you to replicate data on the another machine to give access to the data.

Environment

Let’s say we have one node as SQL1 and one node as SQL2. You need to apply Log Shipping on node SQL3 which is not the part of the availability group replicas. You need to first apply Availability group with Listener (IP and port). Make sure you can connect with the Primary node (say, its SQL1 is Primary and SQL2 is secondary at this time) using Listener IP and port that you specified at the time of the creation of Availability Group on Listener Tab.

Implement Log Shipping

Connect the primary Replica using Listener IP and port and implement Log Shipping using SQL3 as your Log Shipping node. Make sure that the shared backup path is accessible by all nodes SQL1, SQL2 and SQL3.

Tricky Part

Right click the availability group and go to Backup Preferences page. Here select, “Primary” as you backup preference.

Since the log shipping is applied to the Primary replica SQL1 and not on secondary replica SQL2, so when the failover will occur from SQL1 to SQL2, the log shipping will break. It is because the log shipping jobs are not applied on the secondary SQL2 node and in case of any log backup on SQL1, the LSN chain will break even when you will go back to SQL1 from SQL2. So the trick here is to create the script of the Log Shipping that you implemented on Primary node SQL1. The script is divided into 2 parts. Copy the “Primary” part of the script. Failover to the SQL2. Connect to SQL Server using the same Listener IP and port. This will connect you to SQL2. Open the query window and paste the copied script. Run the script. The script will create and configure the Log Shipping on SQL2 for you. Now you have log shipping is configured on both nodes SQL1 an SQL2.

Result

Now, in case of failover, your log shipping will never break as the backups will be taken on Primary node only and the LSN will be maintained.

Testing, Adding Nodes to Clusters and Adding Replicas to Availability Groups - Part 3

You can read this article here.

Enabling AlwaysOn and Creating Availability Groups - Part 2

You can read this article here.

Introduction and Creating Windows Cluster - Part 1

You can read this article here.

Introducing Tabular Model - Self-Service Business Intelligence

Tabular Model is a new feature in SQL Server 2012. Tabular models are in-memory databases in Analysis Services. They use state-of-the-art compression algorithm and multi-threaded query processors named as VertpPaq (xVelocity in-memory analytics engine). This enable fast access to tabular objects and data by reporting applications such as MS Excel.

Tabular Models are created in SQL Server Data Tools (SSDT). It is the upgraded version of BI developer. SSDT is also used to develop Reports, Integration Services and Multidimensional projects. Tabular Model project is deployed as Analysis Services database just like Multidimensional projects. In Tabular Model, we can add partitions for optimization purposes. Row-level security can also be implemented in Tabular Model. We can also import external data to make it a part of the model. We can add relationships, measures and calculated columns on the tabular data in the model. Hierarchies are also supported in Tabular model as in multidimensional model. This makes it more do-able and easy to go with project type in SSAS. As Tabular Model is deployed as Analysis Services database, client applications can connect to the deployed model for reporting purposes.

Analysis services provides three approaches to develop business intelligence semantic models;

1. Tabular

2. Multidimensional

3. PowerPivot

Tabular Model is based on relational structure such as tables and relationships for modeling of data. Multidimensional and data mining are based on OLAP (cubes and dimensions). PowerPivot is a self-service BI tool.

Tabular Model support data access through two modes;

• Cached Mode In cached mode, you can integrate data from multiple sources including relational databases, data feeds, and flat text files. 

• Direct-Query Mode In this mode, you can by-pass the In-Memory model and allow cklient applications to query directly from the relational database. In short, Tabular model is designed to query huge data with much lesser response time in different client application, especially in MS Excel. 

Comparing With Multidimensional Model
With the release of Tabular Model, there are few decisions by Managers that has to be made. Some common questions are;

Question 1: When to use Tabular Model and Multidimensional Model?
Answer: Both Tabular and Multidimensional Models are the part of Analysis Services. Bot are developed in SSDT. The answer lies within the data to be used in the model. If you have huge data, like in TBs, to process in the model, then Multidimensional Model is the best suite for you. Tabular Model are more compressed than Multidimensional but it also requires more memory to load and process the data in memory. PowerPivot Models are for even lesser data size.

Question 2: What do we mean by 'External Data' or 'External Source'?
Answer: We can import data in Tabular Model and PowerPivot from External sources. These can be text files, excel files, data feeds and even some document formats. In multidimensional, you can only load data from relational sources using OLEDB and managed providers.

Question 3: What is the core benefit of Tabular Model?
Answer:  The core benefit is that they are easy to implement as PowerPivot. You can develop more efficient and effective solution. The data processing is very fast and it is easy to understand, It uses DAX as programming language. DAX is quite similar to Excel formula language.  

Question 4: Should we convert the current Multidimensional Models to Tabular?
Answer: NO. The project types are not interchangeable. And it will not be an effective decission to convert the multidimensional model into Tabular model. It is best to use the model for the new requirements.

Analysis Services 2012 Two in One Product
Most important aspect of Analysis Services 2012 is that it is two products in one. In SQL Server 2008 R2 and before, we only had Multidimensional Model option in Analysis services. But in SQL Server 2012, you can choose between Multidimensional and Tabular Model while installing SSAS instance. You need to have separate instance for both if you want to use both, multidimensional and tabular model. In the figure below, you can see that on Feature selection page, there is only one selection for Analysis Services;

But when you pass on to the Analysis configuration page, there you will see a radio button for Server Mode selection. The options are; a. Multidimensional and Data Mining Mode b. Tabular Mode

Licensing
Analysis services 2012 is available in the following editions of SQL Server;

• SQL Server Standard Edition 
• SQL Server BI Edition 
• SQL Server Enterprise Edition 

In SQL Server Standard edition, you can only have Multidimensional Mode. SQL Server BI edition and SQL Server Enterprise editions have both Multidimensional and Tabular Mode. In terms of Analysis Services functionality, these two editions are the same; the only difference between them is that SQL Server Business Intelligence edition licensing is based on buying a server license plus Client Access Licenses (CALs), whereas SQL Server Enterprise edition is licensed on a per-CPU core basis. (You can no longer license SQL Server Enterprise edition on a server-plus-CALs basis as was possible in the past.) In SQL Server Business Intelligence and SQL Server Enterprise editions, both Tabular and Multidimensional models contain all available features and can use as many cores as the operating system makes available. The upshot of this is that it could be more expensive in some situations to use Tabular than Multidimensional because Multidimensional is available in SQL Server Standard edition and Tabular is not. If you have a limited budget, already have existing Multidimensional skills, or are willing to learn them, and your data volumes mean that you do not need to use Multidimensional features such as partitioning, it might make sense to use Multidimensional and SQL Server Standard edition to save money. If you are willing to pay slightly more for SQL Server Business Intelligence edition or SQL Server Enterprise edition, however, then licensing costs should not be a consideration in your choice of model. 

A hint to Self-Service BI and Corporate BI
Over the last few years, trends and demand of BI have significantly changed. Now, users are more interested in having the hold on their data and analyze it according to their own run-time requirement on the spot. Prior to that, they had to involve an IT professional to develop complex reports for them. This is Corporate BI. This scenario still exists but for analysis work, it becomes more cost and time consuming for engaging IT professional for the analysis work. This resulted in a new Self-Service BI. There are tools available in the market like QlikView and Tableu that enables users to query their own data in a user friendly interface. Analysis Services is a corporate-BI tool by MS as you need an IT professional to design and build an Analysis Services database. There are disadvantages of self-service BI like poor-quality data, lack of integration between multiple source systems, etc. Still, self-service BI is high on demand and the reason is that most of the data users think that in most cases, the time consuming step of creating a data warehouse is un-necessary in their initial scenario. MS, as a software company could not ignore this market demand, so in 2010, it released its own serf service BI tool, PowerPivot. Tabular Model resembles a lot to PowerPivot and a refined version of PowerPivot as you get the scalability and manageability in the Tabular Model. PowerPivot is more Agile and Self-Service BI tool.

Comparing File Names in a directory with the list in Excel File

Here is the Script...

---- Get File Names rom you directory

Declare @vOSFiles Table (MyFileName nvarchar(1000),MyDepth int, IsFile bit)

Insert into @vOSFiles

       EXEC xp_dirtree 'G:\Atif\', 1, 1

--Select * from @vOSFiles where IsFile = 1

---- Get List of Files from Excel

----  Insert Excel File list to another Temp Table.

---- The structure o fthis table depends upon you excel file and requirement

Declare @vExcelFileList Table (MyFileName nvarchar(1000))

---- This is for xlsx files

 Insert into @vExcelFileList

       SELECT [MyFileName] FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

       'Excel 12.0 Xml;Database=G:\Budjet.xlsx;HDR=Yes;IMEX=1',

       'SELECT * FROM [Sheet2$]')

--Select *

--from @vOSFiles

---- Join the two tables on the File Names

Select a.*, case when b.MyFileName is not Null then 'Exists' Else 'Not Exists' end as FileExistance

from @vOSFiles a

Left Outer Join @vExcelFileList b on b.MyFileName = a.MyFileName

Where a.IsFile = 1

You might want to enable ad hoc query options;

---- Enable AdHoc Queries

--USE MSDB

--GO

--sp_configure 'show advanced options', 1;

--GO

--RECONFIGURE;

--GO

--sp_configure 'Ad Hoc Distributed Queries', 1;

--GO

--RECONFIGURE;

--GO

---- Add OLEDB referance for xlsx

--EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0'

--    , N'AllowInProcess', 1

--GO

--EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0'

--    , N'DynamicParameters', 1

--GO

For XLS files (earlier 2007 versions of Excel), you can run the OpenRowset query as;

SELECT * FROM  OPENROWSET('Microsoft.Jet.OLEDB.4.0'

,'Excel 4.0;Database=G:\Budjet.xls','SELECT * FROM [sheet2$]')

5% OFF All Lenovo ThinkPad's. Coupon Code:CJDEC5UK Lenovo UK Homepage

Error Handling in SQL Server 2012

As we all know that from SQL Server 2005 and onward, we had TRY CATCH to handle the exceptions / errors in t-sql code along with @@ERROR methodology.

@@ERROR

Returns an error number if the previous statement encountered an error. If the error was one of the errors in the sys.messages catalog view, then @@ERROR contains the value from the sys.messages.message_id column for that error. You can view the text associated with an @@ERROR error number in sys.messages.

Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement being verified, or save it to a local variable that can be checked later.

Simple Example;

CREATE TABLE [dbo].[tblTest](

       [NAME] [varchar](100) NOT NULL,

 CONSTRAINT [PK_tblTest_1] PRIMARY KEY CLUSTERED

(

       [NAME] ASC

)

)

GO

INSERT INTO tblTest 

       Select 'Atif'

       Union

       Select 'Sheikh'

Update tblTest

Set Name = 'Atif'

Where Name = 'Sheikh'

if @@ERROR <> 0

       print 'Error Generated'

else

       Print 'No Error.'

TRY CATCH

We can also make use of TRY CATCH block in order to catch the error;

BEGIN TRY

       Update tblTest

       Set Name = 'Atif'

       Where Name = 'Sheikh'

END TRY

BEGIN CATCH

       print 'Error Generated.'

END CATCH

We can make use of transactions and roll back transactipon in the CATCH block. The TRY...CATCH construct also supports additional system functions (ERROR_LINE, ERROR_MESSAGE, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE) that return more error information than @@ERROR. TRY...CATCH also supports an ERROR_NUMBER function that is not limited to returning the error number in the statement immediately after the statement that generated an error.

BEGIN TRY

       Update tblTest

       Set Name = 'Atif'

       Where Name = 'Sheikh'

END TRY

BEGIN CATCH

       print 'Error Generated.'

      

       SELECT

    ERROR_NUMBER() AS ErrorNumber

    ,ERROR_SEVERITY() AS ErrorSeverity

    ,ERROR_STATE() AS ErrorState

    ,ERROR_PROCEDURE() AS ErrorProcedure

    ,ERROR_LINE() AS ErrorLine

    ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

THROW

Now, in SQL Server 2012, we can also use THROW to throw the exception to the application. You can say it works like RAISERROR;

BEGIN TRY

       Update tblTest

       Set Name = 'Atif'

       Where Name = 'Sheikh'

END TRY

BEGIN CATCH

    print 'Error Generated.';     

       THROW;

END CATCH

You can use THROW statement like RAISERROR as well;

BEGIN TRY

       Update tblTest

       Set Name = 'Atif'

       Where Name = 'Sheikh'

END TRY

BEGIN CATCH

    print 'Error Generated.';     

       THROW 51000, 'Primary Key Violates.', 1;

END CATCH

Limitatoin of Sequence Objects in SQL Server 2012

Sequence Object introduced in SQL Server 2012 is a good features added by MS SQL Server team. I have already discussed this in my post "Sequence Object in SQL Server 2012" but there are many limitations. I will try to explain the prominent limitations.

Let us create a sequence object in our test database.

CREATE SEQUENCE LimitSequence AS INT
 START WITH 1
 INCREMENT BY 1
GO

Also, create a test table with ID generated by using the sequence object;

CREATE TABLE dbo.TestLimit(ID INT,Name VARCHAR(100))
GO
INSERT INTO dbo.TestLimit VALUES
 (NEXT VALUE FOR LimitSequence,'Atif'),
 (NEXT VALUE FOR LimitSequence,'Sheikh'),
 (NEXT VALUE FOR LimitSequence,'Asif')
GO

Cannot Use DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT 

Now, if you try to run the query using sequence object with any of these clauses, you will get an error message;

Select Distinct NEXT VALUE FOR LimitSequence,* from dbo.TestLimit

On executing the above query, you get an eror message as ;

Msg 11721, Level 15, State 1, Line 1
NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.

This applies to all operators.

Using simple ORDER BY clause

If you try the query using the sequence object with simple ORDER by clause, it will generate an error;

Select NEXT VALUE FOR LimitSequence,* from dbo.TestLimit

Order by ID

Error message is;

Msg 11723, Level 15, State 1, Line 1

NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified.

Good part for this is to user OVER (ORDER BY ). 

Select NEXT VALUE FOR LimitSequence,*, ROW_NUMBER() Over (Order by ID) as RNO from dbo.TestLimit

The above query will execute without any error.

TOP 

Cannot use with TOP.

Select Top(10) NEXT VALUE FOR LimitSequence,* from dbo.TestLimit

This query will generate error message as;

Msg 11739, Level 15, State 1, Line 1

NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET. 

As stated in the error message, if ROWCOUNT is set or TOP and OFFSET is used, the query will generate an error message.

CASE, CHOOSE, COALESCE, IIF, ISNULL, or NULLIF

All these are not allowed. I will give the example if ISNULL function;

Select Isnull( NEXT VALUE FOR LimitSequence,0),* from dbo.TestLimit

Erro rmessage is;

Msg 11741, Level 15, State 1, Line 1

NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.

You can try the rest of the functions yourself.

WHERE Clause

You cannot use it in WHERE clasuse;

Select * from dbo.TestLimit

where NEXT VALUE FOR LimitSequence = 6

Error message will be;

Msg 11720, Level 15, State 1, Line 2

NEXT VALUE FOR function is not allowed in the TOP, OVER, OUTPUT, ON, WHERE, GROUP BY, HAVING, or ORDER BY clauses.

So, these are few prominent limitation for the sequence objects.