SQL Server blogs from beyondrelational.com

The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys' - SQL Server

Tue, 09 Apr 2013 16:30:00 GMT

Recently while working with one query to get schedule jobs status, i used xp_sqlagent_enum_jobs object which gives details of state values of all jobs in msdb database and this is undocumented object. But received an error while running following query for same,

EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'sa';

Error :

Msg 229, Level 14, State 5, Procedure xp_sqlagent_enum_jobs, Line 1
The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'.

As per error, says it does not have execute permission in mssqlsystemresource database for sys schema.

Solution :

For the solution just need to assign execute permission on dbo.xp_sqlagent_enum_jobs in master database to user under the query to be run. So following query needs to be run against master database,

USE master
GO
GRANT EXECUTE ON xp_sqlagent_enum_jobs TO test;

Hope this help you if you will receive such an error and you may enjoyed this post.

You can read my some earlier posts,

Stay tuned for further post!

Moving table or indexes on another filegroup - SQL Server

Thu, 04 Apr 2013 00:00:00 GMT

Because of data growth and performance issue we need to move tables or indexes to other filegroups. The purpose of moving tables or indexes to improve the database performance as it reduce I/O from single drive or single filegroup. We can create another filegroup and move the tables and indexes to new created filegroups, then it will spit the I/O to two different filegroups. This way it will improve performance.

When we need to move the tables or indexes to other filegroup? We should move those table need to move which are having more rows or heavily in size, Also need to move if those tables data are frequently updated. Here i would like to show the demo for the same. Let us see how can we move tables/indexes to other filegroup.

-- First we are creating database  
-- Main primary file on Primary filegroup 
-- Secondary files on secondary filegroup. 
-- Log files on different file group. 
CREATE DATABASE filegroupdb 
ON PRIMARY 
	( name = filegroupdb_data, 
	filename = 'C:\FileGroupDB_Data.mdf', 
	size = 4 mb, 
	maxsize = 5 mb, 
	filegrowth = 1 mb ), 
filegroup secfilegroup 
	( name = filegroupdb_data_1, 
	filename = 'C:\FileGroupDB_Data_1.ndf', 
	size = 2 mb, 
	maxsize = 5 mb, 
	filegrowth = 1 mb ) 
log ON 
	( name = filegroupdb_log, 
	filename = 'C:\FileGroupDB_Log.ldf', 
	size = 2 mb, 
	maxsize = 5 mb, 
	filegrowth = 1 mb ) 

GO

After creating database we will create a new table on Primary filegroup. If you not mentioned filegroup name then it will be created on default filegroup. Let us create it.

USE filegroupdb 
GO 

-- Creating table on Primary filegroup 
IF( Object_id('tblFileGroup', 'u') > 0 ) 
  DROP TABLE tblfilegroup 
GO 

CREATE TABLE tblfilegroup 
  ( 
     objectid   INT NOT NULL PRIMARY KEY, 
     objectname VARCHAR(100), 
     createdate DATETIME 
  ) 
ON [PRIMARY] 

GO

Table is created and now we look the property of table where it is created,

SP_HELP tblFileGroup

Now we coming on the point which is tables/indexes actually will be moved. There are two way through we can move the tables and indexes.

#1. By moving clustered index

Using this way we just need to move clustered index and tables and all indexes will be moved to different filegroup.

How can with #1 way?

-- Defining dropping constraint with move
ALTER TABLE tblFileGroup
DROP CONSTRAINT PK__tblFileG__9A619291029E5EB6 WITH (MOVE TO SecFileGroup)
GO

-- Adding constraint
ALTER TABLE tblFileGroup 
ADD CONSTRAINT PK__tblFileG__9A619291029E5EB6 PRIMARY KEY(ObjectId)
GO

Let us check again the properties of tables as successfully moved or not.

SP_HELP tblFileGroup

#2. Recreate a clustered index with drop existing

We just need to recreate a tables clustered index with drop existing clustered index on that.

How can with #2 way?

CREATE UNIQUE CLUSTERED INDEX PK__tblFileG__9A619291029E5EB6
ON tblFileGroup(ObjectId)
   WITH DROP_EXISTING
ON SecFileGroup

GO

You also worked with moving tables/indexes to other location , Share your ideas here!

Conditional aggregation - SUM Vs COUNT

Tue, 26 Mar 2013 00:00:00 GMT

You can do conditional aggregation like SUM(CASE WHEN .. THEN 1 ELSE 0 END),....etc to find a count for a particular match. This type of conditions are useful when you want to write a CROSS-TAB/PIVOT Query. You can also make use of COUNT(CASE WHEN .. THEN 1 END). One of the developers told me that he used the same type of queries but the count did not seem to be correct. I asked him to show the code. His code has the following pattern

COUNT(CASE WHEN .. THEN 1 ELSE 0 END)

I immediately pointed out to him that the above condition is equivalent to COUNT(*) because COUNT will count everything other than NULL. In the above expression both 1 and 0 are counted. So either COUNT should be replaced with SUM or 0 should be NULL.

But for these types are expressions I would like to use SUM instead of COUNT. There are atleast two reasons I know

1 SUM can be used to COUNT as well as to SUM the values. See the below examples

SUM(CASE WHEN gender='M' THEN 1 ELSE 0 END) as male_count,
SUM(CASE WHEN year(trans_date)=2012 THEN trans_amount ELSE 0 END) as total_2012

The first expression COUNTs how many males in the recordset and second expression SUMs the values of the column named trans_amout for the year 2012.

COUNT can be used only for COUNTing as below

COUNT(CASE WHEN gender='M' THEN 1 END) as male_count

2 NULL warning

Using SUM will almost avoid NULL warning. In the first expression the derived values are always 1 and 0 so you will never get any warnings on NULL. In the second example, if the column trans_amount is a NOT NULL column, you will never get a NULL warning but if it is NULLable column and if there are some NULL values for the transaction year 2012, you will get a NULL warning. The NULL warning is as below

Warning: Null value is eliminated by an aggregate or other SET operation.

But using COUNT will most likely result to NULL warning until there are no rows with gender<>'M'.

The NULL warning depends on the data. So use conditional aggregations cleverly.

Failed to acquire connection. Connection may not be configured correctly or you may not have the right permissions on this connection.-SQL Server SSIS

Thu, 21 Mar 2013 12:04:00 GMT

This week, I resolved one issue which I received continuously which is noting but exactly displayed in post title. The issue occurred when I ran ssis package with batch file or scheduled job and same error message raised.

Source: Start Execute SQL Task
Description: Failed to acquire connection "<Connection Name>". Connection may not be configured correctly or you may not have the right permissions on this connection.

As per title, it seems an issue with connection of server configured in ssis package. I thought same because this type of error comes when server is not connecting or it does not have proper and rights to connect. First I checked all servers connection and tried to resolved it, but not succeed.

Please note I received this error only while running ssis package with scheduled job or batch file, But it was working fine while running ssis package directly. It means it is not an issue in ssis package but it is with batch file or schedule job. After tried I visited one conversion which says i t may be issue with 32 bit/64 bit mode. And here I configured ssis package with schedule job and batch file with servers having operating system 64 bit.

After changing ssis package with schedule jobs and batch file with 32 bit, it works fine for me. Let me share,

With Batch file,

I changed path of dtexec.exe from <drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn to <drive>:\Program Files(x86)\Microsoft SQL Server\100\DTS\Binn like following batch file content,

"C:\Program Files (x86)\Microsoft SQL Server\100\dts\Binn\dtexec.exe" /FILE  "C:\SSIS\SSISTest\bin\testing.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING EWCDI

With Scheduled job,

I am sharing one screen shot of schedule job step configuration of ssis package,

Day after this changed, I always see this package succeed. I am leaving one discussion open here “why it was failing with 64 bit mode run?

Handling multiple resultsets returned from Stored Procedure

Wed, 20 Mar 2013 00:00:00 GMT

One of my friends asked me if it is possible to load multiple resultsets returned from a stored procedure into a temporary table for some analysis purpose. The answer is "It depends". If all the resultsets return same number of columns then it is possible. Consider the following stored procedure

create procedure proc_testing
as
select 345 as number
select 52345 as number
select 1200 as number

Executing this procedure will give three resultsets Now create a temporary table

create table #temp(number int)

Add data to this table by executing the stored procedure

insert into #temp(number) 
exec proc_testing

Check the result from the table

select * from #temp

The result is

number
-----------
345
52345
1200

Note: Currently it is not possible to identify a specific resultset from the stored procedure.

Adding/Modifying files and filegroup in Database - SQL Server

Sat, 16 Mar 2013 00:00:00 GMT

You all know about the database file structure as whenever we created databases without specifying file location and filegroups , they will be created in default path and default filegroup. Here i have tried to created a new database without defining any options, look the file structure on the default location and the default group.

CREATE DATABASE FILEGROUPDB
GO

SP_HELPDB FileGroupDB
GO

(Click on image to enlarge)

We can change the default filegroup with following script.

ALTER DATABASE AdventureWorks2008R2 
MODIFY FILEGROUP [PRIMARY] DEFAULT; 
GO

As a best perspective for the database Architecture some frequently updated or used tables should be on different filegroup and the database secondary and log files also be one different filegroup. TempDB data and log files should have alone disk and filegroup as well.

Let us we go ahead with some of activities as how can we can create a new filegroup, how can wee add the new secondary files to that filegroup. We will also look for the modifying the database file and changing the location. First capture the above image of the database file structure before doing any activities with database files. Please go with details explained by comments in the script below and then we will compare the database file structure with original one.

USE MASTER 
GO

-- Adding a new filegroup to database 
ALTER DATABASE FileGroupDB 
ADD FILEGROUP NewFileGroup; 
GO

--Adding a new seconday and log files to a database to above created filegroup 
ALTER DATABASE FileGroupDB 
ADD FILE 
( 
    -- New secondary files added here 
    NAME = FileGroupDB_Data_1, 
    FILENAME = 'C:\FileGroupDB_Data_1.ndf', 
    SIZE = 15 MB, 
    MAXSIZE = 100 MB, 
    FILEGROWTH = 5 MB 
), 
( 
    -- New log file added here 
    NAME = FileGroupDB_Log_1, 
    FILENAME = 'C:\FileGroupDB_Log_1.ldf', 
    SIZE = 5 MB, 
    MAXSIZE = 100 MB, 
    FILEGROWTH = 5 MB 
) 
TO FILEGROUP NewFileGroup; -- Defining filegroup name here 
GO

-- Modifying size of the file 
ALTER DATABASE FileGroupDB 
MODIFY FILE  
( 
-- Changing log file size here 
NAME = FileGroupDB_Log, 
SIZE = 20 MB 
)

-- Moving log file to another location 
ALTER DATABASE FileGroupDB 
MODIFY FILE 
( 
NAME = FILEGROUPDB_log, -- Moving first log file to new location here 
FILENAME = 'C:\FileGroupDB_Log.ldf' 
) 
GO

(Click on image to enlarge)

After running last script you will notified by following message,
"The file "FILEGROUPDB_log" has been modified in the system catalogue. The new path will be used the next time the database is started.

It will use the new file location when SQL Service will be restarted. We can have another option is taking database offline and use same scripts written above and again database needs to back online which does not need to restart database service.

BCP - Changing the default column terminator

Fri, 08 Mar 2013 00:00:00 GMT

BCP is one of the fastest methods to export data to various formats like csv,txt,etc and doing this in a command mode is faster than doing it via SSMS.

Let us create this simple table

create table test(id int, name varchar(50))

insert into test(id,name)
select 1,'Sankar' union all
select 2,'Kumar' union all
select 3,'Nilesh'

Now open the command processor and run this code (Replace dbname,Servername, username and password with actual values)

BCP dbname..test out "C:\test.csv" -c -S Servername -U username -P Password

The data from the table test is now available in the file test.csv. When you open the file, you can notice that the column terminator is a tab. If you want to change it to a comma(,) or a pipe sysmbol (|), or any other character as terminatiors use the option -t followed by terminator. So the following code uses comma as the column terminator

BCP test..test out "C:\test.csv" -c -t, -S Servername -U username -P Password

If you want pipe symbol as a column terminator, use the following code

BCP test..test out "C:\test.csv" -c -t"|" -S Servername -U username -P Password

Note that only a comma can be specified without double quotes. All other terminators should be specified within double quotes.

So use the column terminator option -t to change the column terminator as you wish

How to increase the number of characters retrieved from the server for XML data-SQL Server

Mon, 04 Mar 2013 00:00:00 GMT

You may worked with xml and you may aware of title mentioned in this post. Today when I was working to collect information from one table which having xml column, I was trying to open xml data result set from query analyzer with new query window. For some of xml data result set it raised an error below while opening with new window ,

Unable to show XML. The following error happened: There is an unclosed literal string.

This is just an error I faced for some xml data which are lengthy in size. But error is showing solution too “One solution is to increase the number of characters retrieved from the server for XML data. To change this setting, on the Tools menu, click Options”. Let follow toward it and go ahead the step suggested, please see below snapshot which is showing the option where we can change the setting suggested,

Tools –> Options

Nothing to say other than screen shot above. I know this is common post but I shared it with you because I never faced this issue earlier and also did not aware about this setting. Let us share here if have this in your mind earlier.

Skip distributor agent error in sql server transactional replication - How to

Wed, 27 Feb 2013 00:00:00 GMT

A while before few weeks, I discussed about an error of replication “The row was not found at the Subscriber when applying the replicated command”. We had a tricks to get discrepancies for error table and resolved issue. Let me put another method (Actually patch) to come out from same error which is very interesting. Before to move this method, Please read some my posts related to replication which you may like,

Now going to move ahead , here I am talking about the solution for the above highlighted error while is “Skip error in sql server transactional replication, How to ? ”.

Raise a problem

We will consider the same objects created and same scenario for transactional replication in this post. Let us check the data of ready made tables,

SELECT * 
FROM   test.dbo.sample1 (nolock) 

SELECT * 
FROM   test1.dbo.sample1 (nolock)

We have same records in both tables at publisher and subscriber side. Now it is time to create discrepancies using following script,

-- Deleting one record from table in subscriber database 
DELETE FROM test1.dbo.sample1 
WHERE  id = 2 

-- Updating same record from table in publisher database 
UPDATE test.dbo.sample1 
SET    name = 'test5' 
WHERE  id = 2 

-- Inserting new record in table in publisher database 
INSERT test.dbo.sample1 
SELECT 4, 
       'test4'

Monitoring replication after above script ran,

And viewing table’s data again from publisher and subscriber after an error,

You can see nothing happened at subscriber because of an error occurred for one missing row.

Solution

We are about to skip this error where all replication commands stuck, So sp_setsubscriptionxactseqno system stored procedure help us which is used to troubleshooting to specify the log sequence number (LSN) of the next transaction to be applied by the Distribution Agent at the Subscriber. So let us first get sequence number of an error which we have from the replication monitor. We can also use following script to get all transactional replication errors and executed in distributor server in distribution database,

USE distribution 
GO

DECLARE @PublisherServer  VARCHAR(50), 
        @PublicationDB    VARCHAR(50), 
        @SubscriberServer VARCHAR(50), 
        @SubscriberDB     VARCHAR(50), 
        @PublicationName  VARCHAR(50) 

SET @PublisherServer = '<Publisher>' 
SET @PublicationDB = 'test' 
SET @SubscriberServer = '<Subscriber>' 
SET @SubscriberDB = 'test1' 
SET @PublicationName = 'testpub' 

EXEC Sp_helpsubscriptionerrors 
  @PublisherServer, 
  @PublicationDB, 
  @PublicationName, 
  @SubscriberServer, 
  @SubscriberDB 

GO

(Click on image to enlarge)

Please get a top sequence number of an error from where all transaction commands stuck and run following script at subscriber side,

USE test1 
GO

DECLARE @PublisherServer VARCHAR(50), 
        @PublicationDB   VARCHAR(50), 
        @PublicationName VARCHAR(50) 

SET @PublisherServer = '<Publisher>'
SET @PublicationDB = 'test' 
SET @PublicationName = 'testpub' 

EXEC Sp_setsubscriptionxactseqno 
  @PublisherServer, 
  @PublicationDB, 
  @PublicationName, 
  0x00000022000000C1000400000000 

GO

Finally running above script at subscriber database, this error skipped and all remaining and pending commands were applied which we can see the data of both tables from publisher and subscriber database,

This is just my experience which I am sharing with you. It is recommending to find a route of this error and solve it.

Did you received such error and you skipped any? Please share your thoughts!

Import data - solving data convertion and truncation error

Wed, 27 Feb 2013 00:00:00 GMT

Often developers use Import wizard from SSMS or SSIS to import data from a text file into a table. You may or may not import it successfully based on the nature of the data.

Create a text file named test.txt with the following data

Now using an import wizard from SSMS, try to import this file to a table.

Right click on database-->Import data
Choose Data source as Flat file source, browse the file, Under the Data source tab, choose the columns and make sure that Row delimiter is {CR}{LF} and column delimiter as Vertical Bar {|}, click next and choose the destination as SQL Server native client 11.0, choose the relevant authentication mode and a database, click next and at last click Finish.

In the step named Executing, the status shows as error. Click on the error, it will pop-up a new window with the error message

- Executing (Error)

Messages

Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "address" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task 1: The "Source - test_txt.Outputs[Flat File Source Output].Columns[address]" failed because truncation occurred, and the truncation row disposition on "Source - test_txt.Outputs[Flat File Source Output].Columns[address]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:\test.txt" on data row 3.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - test_txt returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

So where is the problem?

Now go back to the initial step of this wizard (Choosing Data Source). Under Data Source click on Advanced, choose the column address. The output column width is 50 (by default all varchar columns will have width of 50), change it to 100 and do the same process. All data will get imported succesfully.

So kwwp this in mind while using a import wizard or SSIS and increase the width from default value of 50

Print screen for Remote Desktop

Wed, 20 Feb 2013 00:00:00 GMT

Earlier on Beyondrelational I read a tip, for the same topic. combination of keys Shift + Print screen, allows us to take screen shot of Remote Desktop. But recently I faced problem using these keys. It allowed taking print screen only once. Every other time, it comes up with the first screen shot i took. Solution I found was : Start menu -> Accessories -> Accessibility -> On-Screen Key board. On the keyboard that pops up, use the keys : alt + printscreen + enter. This has solved my issue, I can take as many screen shots as I want for Remote desktop

Merge statement with TOP clause - SQL Server

Sun, 17 Feb 2013 00:00:00 GMT

A week ago, I posted for Insert, Update and Delete statement with TOP clause and Merge statement as individual post. If you not read those posts then read it before to move next. In this posts I used TOP clause with DML operations and Merge statement but both are individual posts. Let me put these two posts together here and create new one.

What is it ?

It is nothing but the form of two individual posts and it is Merge statement with TOP clause. I never used Merge statement and TOP clause at once. Let me create required objects for this demo or we can pick from earlier post,

-- Creating tables used for merge operation 
IF ( Object_id('UsingTable') > 0 ) 
  DROP TABLE usingtable 

CREATE TABLE usingtable 
  ( 
     refid INT IDENTITY(1, 1), 
     name  VARCHAR(100) 
  ) 
GO

IF ( Object_id('TargetTable') > 0 ) 
  DROP TABLE targettable 

CREATE TABLE targettable 
  ( 
     childid INT, 
     val     INT 
  ) 
GO

-- Inserting records in both tables 
INSERT INTO usingtable 
            (name) 
VALUES      ('Target-1'), 
            ('Target-2'), 
            ('Target-3'), 
            ('Target-4'), 
            ('Target-5') 

GO 

INSERT INTO targettable 
            (childid, 
             val) 
VALUES      (1, 
             1), 
            (2, 
             2), 
            (3, 
             3), 
            (6, 
             6) 
GO

Now we will run merge statement with TOP clause and also view Target table’s data before and after script run,

SELECT * 
FROM   targettable 

MERGE TOP (2) targettable 
using usingtable 
ON ( refid = childid ) 
WHEN matched AND childid = 3 THEN 
  DELETE 
WHEN matched THEN 
  UPDATE SET val = val + 5 
WHEN NOT matched BY target THEN 
  INSERT(childid, 
         val) 
  VALUES(4, 
         4) 
WHEN NOT matched BY source THEN 
  DELETE; 

SELECT * 
FROM   targettable 

GO

Merge with TOP clause

Merge without TOP clause

You can see from both images, with Merge statement with TOP clause updated only 2 rows and remaining insert and delete operation not happened which happened with Merge statement without TOP clause. Did you used both at once?

Question of the month February 2013 - How does HAVING clause work without GROUP BY Clause?

Wed, 13 Feb 2013 00:00:00 GMT

When you run the following code

select name from sys.objects
having 1=1

you get the following error

Msg 8120, Level 16, State 1, Line 2
Column 'sys.objects.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

But the follwing query runs fine without any problem

select 1
having 1=1

How does the second query work without any GROUP BY clause?

Could not find the Distributor or the distribution database for the local server-Error while posts a tracer token in Replication

Tue, 12 Feb 2013 00:00:00 GMT

A few days back, I spoke about the manual failover of mirroring and also explained one issue and workaround too. Continuing with the same failover, I want to express one more issue here. This issue is not very critical but it somehow to create an issue while collecting some information for report or any other purpose. Let me elaborate everything here, why and how this error raised.

You all know about system stored procedure sys.sp_posttracertoken which posts a tracer token into the transaction log at the Publisher and begins the process of tracking latency statistics. which we can schedule on some frequency to post tracer tokens. Tracer tokens can be inserted with Replication monitor also,

You can find tsql code for same below which must be run against publisher database,

USE publisherdb 
GO

DECLARE @out_tracer_token_id INT 

EXEC sys.Sp_posttracertoken 
  @publication = N'<Publication Name>',  -- Put Publication name here
  @tracer_token_id=@out_tracer_token_id out 

SELECT @out_tracer_token_id

Error

But after failover when I tried same tsql code in switched publisher database I received an error,

"Could not find the Distributor or the distribution database for the local server.

The Distributor may not be installed, or the local server may not be configured as a Publisher at the Distributor."

Solution

This script was running fine in original publisher database before failover. During investigation as per error message I found sp_helpdistributor was returning NULL values in publisher database. sp_helpdistpublisher also not showing publisher in Distributor server or server where distribution database belongs to. That means we have to do two things,

Configure distributor at publisher.
Configure publisher at distributor.

So moving ahead towards the solution and apply below solution,

1. sp_adddistributor which creates an entry in linked server and executed at publisher in master database to configure remote distributor,

Use master
GO

EXEC sp_adddistributor  
           @distributor=  '<Distributor>' , -- Put your distributor server name here
           @password= 'testpwd' -- password of distributor_admin

2. sp_adddistpublisher configures a publisher in distributor server which executed at distributor side in master database,

USE master
GO

EXEC sp_adddistpublisher 
	 @publisher= '<Publisher>'  -- Put publisher servername here
	,@distribution_db= 'distribution'  -- Distribution database name
	,@security_mode= 1 
	,@login= 'sa' 
	,@password= 'testpwd'

After this workaround I was able to ran this script successfully at publisher and scheduled to every 5 minutes, so I can used it for replication latency alert and reports too. Are you using sys.Sp_posttracertoken system stored procedure? Share your feedback here.

#0239 - SQL Server 2012 - Msg 402 - The data types datetime and time are incompatible in the add/subtract operator

Mon, 11 Feb 2013 00:00:00 GMT

Microsoft SQL Server 2008 came with a wide array of T-SQL enhancements. One of them was the ability to split a DATETIME value into DATE and TIME values. From a storage standpoint this allowed us to store and bind (on the UI) date and time values separately, while appending the values when displaying on a report or exporting to a 3rd party system. They are also useful to store time revisions with respect to a DATETIME value.

Here’s a quick example:

SQL Server 2008/2008 R2

In the query below, I will be taking two variables - a DATETIME and a TIME value and then add and subtract the TIME value from the DATETIME value to get the final result.

--Please execute on a SQL 2008/2008 R2 instance
USE tempdb;
GO
DECLARE @tDate DATETIME = '2013-02-11 00:00:00.000';
DECLARE @tTime TIME = '08:00:00.000';

SELECT @tDate AS DateTimeSource, 
       @tTime AS TimeSource,
       (@tDate + @tTime) AS DateTimeAddResult,
       (@tDate - @tTime) AS DateTimeSubtractResult;
GO

The output of the above query is shown below:

As you can see, we were able to successfully add and subtract the DATE and the TIME values to create corresponding DATETIME values.

Changes in SQL Server 2012

If your application uses code similar to the one shown above, it will not work as expected in SQL Server 2012 (SQL 11/”Denali”):

--Please execute on a SQL 2012 instance
USE tempdb;
GO
DECLARE @tDate DATETIME = '2013-02-11 00:00:00.000';
DECLARE @tTime TIME = '08:00:00.000';

SELECT @tDate AS DateTimeSource, 
       @tTime AS TimeSource,
       (@tDate + @tTime) AS DateTimeAddResult;

SELECT @tDate AS DateTimeSource, 
       @tTime AS TimeSource,
       (@tDate - @tTime) AS DateTimeSubtractResult;
GO

The following error message is encountered:

Msg 402, Level 16, State 1, Line 6
The data types datetime and time are incompatible in the add operator.
Msg 402, Level 16, State 1, Line 10
The data types datetime and time are incompatible in the subtract operator.

As you can see, SQL Server 2012 no longer allows us to add/subtract a TIME value from a DATETIME value using the conventional Add (+) and Subtract (-) operators.

Workaround

A workaround to this issue is to modify the code such that the TIME value is converted to a DATETIME value before the Add (+) or Subtract (-) operation takes place.

--Please execute on a SQL 2012 instance
USE tempdb;
GO
DECLARE @tDate DATETIME = '2013-02-11 00:00:00.000';
DECLARE @tTime TIME = '08:00:00.000';

SELECT @tDate AS DateTimeSource, 
       @tTime AS TimeSource,
       (@tDate + CAST(@tTime AS DATETIME)) AS DateTimeAddResult,
       (@tDate - CAST(@tTime AS DATETIME)) AS DateTimeSubtractResult;
GO

Conclusion

Between just two releases, this is a considerably major change because one may have a considerable number of objects written using this ability of DATETIME and DATE/TIME data-types to add/subtract. With an upgrade of the database to SQL Server 2012, these objects will need to be modified. I would therefore present the following questions before you:

Have you faced this issue in your migration from SQL Server 2008 to SQL Server 2012?
What solution/workaround did you apply?

Reference:

DATETIME datatype: http://msdn.microsoft.com/en-us/library/ms187819.aspx
DATE datatype: http://msdn.microsoft.com/en-us/library/bb630352.aspx
TIME datatype: http://msdn.microsoft.com/en-us/library/bb677243.aspx

Until we meet next time,

Be courteous. Drive responsibly.

SSIS - Split single row to Multiple rows

Wed, 06 Feb 2013 00:00:00 GMT

Its been a long long time since I have blogged. So have I been away from forums hence no ideas to blog. I was back at forum today and got a nice scenario. How do you split a single row to multiple rows? The input file has only one row.

There can be 2 scenarios which arise:

Scenario 1. the number of columns expected for each output row are the same.

Input: 1*1*1~2*2*2~3*3*3~4*4*4

Treat “~” as the row delimiter and * as the pipe delimiter

Expected Output:

1	1	1
2	2	2
3	3	3
4	4	4

Scenario 2. The number of rows for each output row are different. We need to default values if we have less than expected columns and ignore the extra columns.

Input:

1*1*1*1~2*2~3*3*3*3*3*3*3~4*4

Treat “~” as the row delimiter and * as the pipe delimiter

Expected Output:

1	1	1
2	2
3	3	3
4	4

Notice the blanks in the 3rd column for 2nd and 4th row.

SOLUTION:

Case 1:

This is simple you just need to setup the Flat File connection manager with a row delimiter as “~” and Column Delimiter as “*”

This will give us the expected results.

Now Let us see at the 2nd Case.

What will happen if we take the second file as the source and set it up similarly? Have a look at the image below:

This image above shows the output data as correctly formed however we have one extra column as per out requirement. We can remove this extra column while mapping to the destination.

When you run the package and view the same data in the Data Viewer you will see the output as below which is again not what we expect but if we do not map the last columns to the destination our job is done.

CONCLUSION:

SSIS In this case first splits the row based on row delimiter and then based on Column Delimiter
SSIS decides the number of columns in output based on the number of columns available in the 1st row (in case above the number of “*” before we get a “~”)
If there are more number of column delimiters than in the first row the extra data goes to the last column, before the next row delimiter is received(see row 3 above)

Approach to be taken for case 2:

At package design time, set up a file with 1st row having one more column than expected in the final output.
Map all columns to the destination except the last one.
Once designed, remove the extra column set up in Step one.
Job achieved very easily.

I was earlier thinking of writing a code in Script Component to achieve this, but while preparing this blog I got this method.

Let me know what you think of this method.

I will also post code that is required to achieve this. Decision is yours which method you choose.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;
#endregion

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    //StramReader to read the input file stream
    private StreamReader textReader;
    //String to save the source file path
    private string SrcFilePath;

    //Int to count the number of records read.
    private int i = 0;


    //Override the AcquireConnections Method to set up the connection once for the file.
    public override void AcquireConnections(object Transaction)
    {
        SrcFilePath = @"H:\MSBI\SSIS\2012\Input\SingleRowToMultiple.txt";
    }

    public override void PreExecute()
    {
        base.PreExecute();
        //Set the textReader at the PreExecute Phase so that we donot initialize it for each record.
        textReader = new StreamReader(SrcFilePath);
    }

    public override void PostExecute()
  {
        base.PostExecute();

        //Close the Text reader once the file has been read in the PostExecute Phase.
        textReader.Close();
    }


    public override void CreateNewOutputRows()
    {
        string nextLine;
        string[] rows;
        string[] columns;
        char[] rowDelimiters;
        char[] colDelimiters;
        rowDelimiters = "~".ToCharArray();
        colDelimiters = "*".ToCharArray();

        //Read next line from the file to the string variable
        nextLine = textReader.ReadLine();

        //Read the file till nextLine variable is not NULL ie. EOF
        while (nextLine != null)
        {
            if (i >= 0 && nextLine.Length > 0)
            {
                
                //Split the records by ~ to later extract the data and in each record.
                rows = nextLine.Split(rowDelimiters);
                {
                    foreach (string row in rows)
                    {
                        // Add new row to Script Component Output
                        this.Output0Buffer.AddRow();

                            // Split the row to columns based on *
                            columns = row.Split(colDelimiters);

                            this.Output0Buffer.Column0 = columns.Length > 0 ? columns[0] : string.Empty;
                            this.Output0Buffer.Column1 = columns.Length > 1 ? columns[1] : string.Empty;
                            this.Output0Buffer.Column2 = columns.Length > 2 ? columns[2] : string.Empty;
                            this.Output0Buffer.Column3 = columns.Length > 3 ? columns[3] : string.Empty;
                    }
                }
            }
            i++;
            //Read the next line
            nextLine = textReader.ReadLine();
        }
    }

}

#0235 - SQL Server - T-SQL Deprecated feature - Start using semi-colons as statement-terminators

Mon, 28 Jan 2013 00:00:00 GMT

A while ago, I read a post from Aaron Bertrand (B|T) regarding an appeal to start using semi-colons actively as statement terminators. Semi-colons as statement-terminators has been around for as long as I can remember, however, they have only been made mandatory in the newer statements:

A semi-colon is required before the WITH clause (as in Common Table Expressions, CTE)
The MERGE statement must end with a semi-colon
In SQL 2012, the THROW statement also requires that the preceding statement ends with a semi-colon

Because the semi-colon is not mandatory, most developers do not use semi-colons in the T-SQL queries that they write. However as Aaron points out, it is already documented in Books On Line that the semi-colon will be a required feature:

Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version.

For most teams, this would mean to modify almost every line of code and a huge testing & development effort. To mitigate the high development effort and bring reliability in the process, teams may decide to write a small program that would add semi-colons after each statement. But there’s a small catch.

Exception to the rule: The batch separator cannot be suffixed by a semi-colon.

USE AdventureWorks2012;
GO;

The code above would simply fail to compile with the following error.

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'GO'.

The reason is quite simple – a T-SQL statement is a sub-set of a batch. Because “GO” is a batch separator, it cannot be suffixed by a statement terminator because an active T-SQL batch would not exist at that time.

Reference:

Ladies and Gentlemen, start your semi-colons! by Aaron Bertrand (B|T)
T-SQL Syntax Conventions
“GO” as Batch Separator – Customize batch separator in SSMS & SQLCMD

Until we meet next time,

Be courteous. Drive responsibly.

Merge statement and identity insert - SQL Server

Fri, 25 Jan 2013 00:00:00 GMT

Today I asked by my friend for merge statement and identity insert, how to insert identity column data using merge statement? I posted for the merge statement without identity insert. Please read that post first before move ahead. so I would like to publish my friend’s question and answer too. It’s nothing but simple as identity insert for single table without merge.

Let me generate objects required for demo,

IF ( Object_id('EmpList1', 'U') > 0 ) 
  DROP TABLE emplist1 

IF ( Object_id('EmpList2', 'U') > 0 ) 
  DROP TABLE emplist2 

CREATE TABLE emplist1 
  ( 
     seq1     INT NOT NULL IDENTITY(1, 1), 
     empid1   INT NOT NULL PRIMARY KEY, 
     empname1 VARCHAR(50) 
  ) 

CREATE TABLE emplist2 
  ( 
     seq2     INT NOT NULL IDENTITY(1, 1), 
     empid2   INT NOT NULL PRIMARY KEY, 
     empname2 VARCHAR(50) 
  ) 

INSERT INTO emplist1 
VALUES      (1001, 
             'Emp1001') 

INSERT INTO emplist2 
VALUES      (1001, 
             'Emp2001') 

INSERT INTO emplist2 
VALUES      (1002, 
             'Emp2002') 

DELETE FROM emplist2 
WHERE  seq2 = 2 

INSERT INTO emplist2 
VALUES      (1002, 
             'Emp2002') 

SELECT * 
FROM   emplist1 

SELECT * 
FROM   emplist2

You can see in the script and image where we have to update and insert record in table emplist1 from emplist2. where record with seq1 will be updated and record with seq3 will be inserted with an identity. So emplist1 will become a target and emplist2 will become a source for this operation. Let me put a script here for same,

SET IDENTITY_INSERT emplist1 ON 

MERGE emplist1 
USING emplist2 
ON ( empid1 = empid2 ) 
WHEN matched THEN 
  UPDATE SET empname1 = empname2 
WHEN NOT matched BY target THEN 
  INSERT(seq1, 
         empid1, 
         empname1) 
  VALUES(seq2, 
         empid2, 
         empname2) 
WHEN NOT matched BY source THEN 
  DELETE; 

SET IDENTITY_INSERT emplist1 OFF

You can see I used IDENTITY_INSERT at top and identity column on code while inserting records. Now checking records after end,

I know you know about this, but I shared this post because I never used merge statement and identity insert at once. Did you ever used?

Different ways to find End of the month

Mon, 21 Jan 2013 00:00:00 GMT

You may need to often find the end of the month for various monthly analysis. In this case you need to take all data from 1st of month to end of month.

Consider the following set of data

Create table #test(date_col datetime)

insert into #test(date_col)
select '20120104' union all
select '19990605' union all
select '19960221' union all
select '19981212' union all
select '20000228'

There are many ways to find out the end of the month. Here are some I knew

Method 1 : Use DATEADD and DATEDIFF combination

select 
	date_col,dateadd(month,datediff(month,0,date_col)+1,-1) as end_of_month 
from 
	#test

Method 2 : Subtract day value from date

select 
	date_col,dateadd(month,1,date_col -day(date_col)+1)-1 as end_of_month 
from 
	#test

Method 3 : Find first day of next month and sbtract 2 milliseconds

select 
	date_col,
	dateadd(millisecond ,-2,dateadd(month, datediff(month,0,date_col)+1,0)) as end_of_month 
from 
	#test

Method 4 : Find Jan 1 of that year and add month value

select 
	date_col,
	dateadd(month,month(date_col),dateadd(year,datediff(year,0,date_col),0))-1 as end_of_month 
from 
	#test

Method 5 : Use EOMONTH function (Version 2012 onwards)

select 
	date_col,
	eomonth(date_col ) as end_of_month 
from 
	#test

In all cases, the results are as follows (except that method 3 will have time part too)

date_col                  end_of_month
-----------------------   -----------------------
2012-01-04 00:00:00.000   2012-01-31 00:00:00.000
1999-06-05 00:00:00.000   1999-06-30 00:00:00.000
1996-02-21 00:00:00.000   1996-02-29 00:00:00.000
1998-12-12 00:00:00.000   1998-12-31 00:00:00.000
2000-02-28 00:00:00.000   2000-02-29 00:00:00.000

Note : Method 3 that depends on millisecond value may not be reliable. If the date has time value along with it, it may not work

How to manual failover mirroring without affecting replication-SQL Server

Sat, 19 Jan 2013 00:00:00 GMT

Before a couple of days we planned to manual failover of production servers and all live databases for that instance to mirror instance and did a failover too. It was a best experience for failover without fail anything like replication, scheduled jobs, linked servers, ssis packages, reports, windows tasks and whatever dependencies . Well perfect planning and team work were key for that succeed failover for us. This post is about to considering mirroring without witness server\automatic failover and transactional replication where production database act as a publisher and principal and the plan for same like following,

Planning

Configured mirror all production databases to mirror instance without witness server.
Created a dns alias for production server.
Used that alias as a data source to connect production sql server instance in linked servers, reports, ssis packages in all servers which pointing production instance and in application too .
Created all scheduled jobs with disable status in mirror instance.
Created all linked servers of production instance in mirror instance.
Created all logins of production instance in mirror instance.
Created all database mail profiles of production instance in mirror instance.
Created sql server agent operators of production instance in mirror instance.
Created windows scheduled tasks with disable status in mirror server.

All of above steps applied in advance with recent changes just before to start failover and need to change dns alias to mirror server, enable scheduled jobs and windows tasks during failover.

Problem

But had a little bit confusion for replication, How to manually failover of mirroring without affecting replication? That was an issue. I have applied solution and made it succeed. After manual failover, transactional replication started to raise an error and working stopped. because it was trying to connect publisher database but it became mirror after failover,

The process could not execute 'sp_replcmds' on '<original Publisher Server>'.

Workaround

There is one more step apart from above listed,

Add Failover Partner as a parameter (–PublisherFailoverPartner) in snapshot, log reader and queue reader agents.

How to add parameter?

I am sharing some screen shots which drive us for the explanation,

1. Go to Replication monitor and move to agent tab. Select agent from Agent types drop box, you will have list of agent, select it and click Agent Profiler from right click property.

2. Under Agent property, create a new user profile which will be created same as system profile, just need to add value <failover partner> of –PublisherFailoverPartner parameter.

3. Add –PublisherFailoverPartner parameter value for all agents like snapshot, log reader, queue reader agent and merge agent we have merge replication configured.

Note : After creating a new user agent profile check the box “Use for this agent” . I created new agent profile because it is not allow to add –PublisherFailoverPartner parameter for system profile from user interface. But we can add it with system procedures with tsql script.

USE distribution 
GO 

-- For Snapshot Agent 
EXEC sp_add_agent_parameter 
  @profile_id = 1, 
  @parameter_name = N'-PublisherFailoverPartner', 
  @parameter_value = N'<Failover Partner>' 

-- For Log Reader Agent 
EXEC sp_add_agent_parameter 
  @profile_id = 2, 
  @parameter_name = N'-PublisherFailoverPartner', 
  @parameter_value = N'<Failover Partner>' 

-- For Distribution Agent 
EXEC sp_add_agent_parameter 
  @profile_id = 3, 
  @parameter_name = N'-PublisherFailoverPartner', 
  @parameter_value = N'<Failover Partner>' 

-- For Merge Agent 
EXEC sp_add_agent_parameter 
  @profile_id = 4, 
  @parameter_name = N'-PublisherFailoverPartner', 
  @parameter_value = N'<Failover Partner>' 

-- Queue Reader Agent 
EXEC sp_add_agent_parameter 
  @profile_id = 9, 
  @parameter_name = N'-PublisherFailoverPartner', 
  @parameter_value = N'<Failover Partner>'

It is allow to add this parameter for system profiles but change the profile_id whatever system or user profile used for agent which you will get it from sp_help_agent_profile system procedure from distribution database.

How to confirm?

With following script we can confirm the parameter values for such agents. Run this script in msdb database from distributor server.

USE msdb 
GO

SELECT a.profile_id, 
       a.profile_name, 
       a.description, 
       a.def_profile, 
       b.parameter_name, 
       b.value 
FROM   msagent_profiles a 
       INNER JOIN msagent_parameters b 
               ON ( a.profile_id = b.profile_id ) 
WHERE  b.parameter_name = '-PublisherFailoverPartner'

(Click on image to enlarge)

Hope you enjoyed this case and might help you a lot. Did you faced this issue or what is your solution? Something missing in failover plan? Please share your ideas and opinion about it. Your comments are most welcome!

Sorting mixed value strings numerically

Wed, 16 Jan 2013 00:00:00 GMT

There was a question in the forum on how to Sort mixed value strings “numerically”

Consider the following set of data

create table #test(val varchar(10))
insert into #test
select '1002' union all
select '29C' union all
select '1' union all
select '205' union all
select '105A'

The resultset should be sorted by taking only number part into consideration. There can be many methods and here are my two methods

Method 1 : Remove non-numerics from string and sort them as numbers

select 
	* 
from 
	#test 
order by
	stuff(val+'a',patindex('%[^0-9]%',val+'a'),len(val),'')*1

Method 2 : Extract numerics from string and sort them as numbers

select 
	* 
from 
	#test 
order by
	substring(val+'a',1,patindex('%[a-zA-Z]%',val+'a')-1)*1

The results of the above two methods are

val
----------
1
29C
105A
205
1002

Note : I have appended a character 'a' so that every value will have atleast a single alphabet suffixed and we can avoid CASE WHEN expressions. Also it is assumed that the string will start with a number and alphabets may or may not end with them. A string like 12B78C is invalid for this sorting.

Split strings in Proper format

Thu, 10 Jan 2013 00:00:00 GMT

This short tip is a result of my quick research in attempt to solve this MSDN thread http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/f02aefbd-09a5-4336-84d2-d67bdf238221 In order to use case sensitive search with PATINDEX we need to use case sensitive collation and we can not use ranges of letters, unfortunately, we need to list all possible letters. So, this is my solution DECLARE @t TABLE (Col VARCHAR(20)) INSERT INTO @t VALUES ('JohnDoe') ,('AvramLincoln') ,('Brad Pitt') SELECT Col ,COALESCE(STUFF(col, NULLIF(patindex('%[abcdefghijklmnopqrstuvwxyz][ABCDEFGHIJKLMNOPQRSTUVWXYZ]%', Col COLLATE SQL_Latin1_General_CP1_CS_AS), 0) + 1, 0, ' '), Col) AS NewCol FROM @t

Replicated transactions are waiting for next Log backup or for mirroring partner to catch up-Issue in SQL Server Replication

Sat, 05 Jan 2013 00:00:00 GMT

Hope you read my earlier post of "Replication components are not installed on this server" issue, you may liked it. One day suddenly replication went to high latency and i was clicked it during monitoring. I opened replication monitor, during analysis i found one message which was showing latency from publisher to distributor due to some issue. The message is as following,

“Replicated transactions are waiting for next Log backup or for mirroring partner to catch up”.

As per message i checked the transaction log backups were happened or not, checked it and log backups were happening . Finally one option is pending to review and it is mirroring. I checked the status of mirroring and seen principal database went to synchronizing mode. May be mirroring went in synchronizing mode due to heavy or so many transactions in the route to apply at mirror database. So replication is waiting to be synchronized status of principal database. I exactly do not why replication went in waiting even transaction log backups were happen. I checked online solution and received some of the solutions from here and it suggests following,

1. EXEC sp_replicationdboption 'PublisherDB','sync with backup',false

This mean that not need to backed up of all transactions before being delivered to the distribution database. Please visit this option here which sets a replication database option for the specified database. This stored procedure is executed at the Publisher or Subscriber on any database.

2. Enable trace flag 1448

After this setting the Log Reader Agent can continue replicating changes regardless of the mirroring state. Please read more here.

I never applied these suggestions. Replication and mirroring both are using transaction logs and this could be the reason why replication was in high delay status while mirroring was stuck or in process to synchronize mirror database. So i turned off mirroring and monitored replication status. Finally replication was succeed to remove delay and applied all pending commands. Then i configured mirroring again after done with replication sync.

Conclusion : We have two options either to wait for synchronized status of principal database or turned of mirroring, getting replication synced properly and reconfigure mirroring again depend on priority and importance.It will be better to go for correct solution to avoid such issue.

But question is here why replication went in waiting for synchronized of mirroring even transactions log backups happened while? I would like you to share if received such issue and solution or any opinion which you applied to resolved it. This may help to me and all people facing same issue.

Getting Started with SSAS Multidimensional - Part 8: Aggregations, Partitions and Perspectives

Thu, 03 Jan 2013 00:00:00 GMT

In our last post, we learned how to create measures in SSAS. This post will focus on building perspectives, partitions and aggregations for the project. Before we start, it is important to understand what aggregations, partitions and perspectives are, and how they are useful. - Aggregations are pre-calculated summaries of data from leaf level, and they help to improve performance by preparing the answers in advance before the questions are asked. For example, it might be useful to roll up the sales by year and store it as an aggregation. This way, if there is a query which asks the sales by year, the answer can be given directly from the aggregation rather than rolling up all the 365 days at runtime. But an important thing to note is that, it is practically impossible to create aggregations for all valid combinations. The SSAS engine creates the aggregations based on an internal cost-vs.-benefit algorithm and we can certainly aid the engine by setting the aggregation usage property to None if it is not a frequently used attribute. - Partitions are used to store data and aggregations for the measure groups. By default, every measure group has one partition and more partitions can be added manually (though we have to ensure that the data in each partition is mutually exclusive of each other and that there is no overlap). Having multiple partitions can be a great way of managing big cubes and can greatly improve query performance and load performance. - Perspectives are basically different views of the same cube. They are used when we want to just display the relevant measures or dimensions to the users, rather than overwhelming them with the entire set of cube measures and dimensions. An important thing to note is that perspectives are not a security mechanism and the security is just derived from the underlying cube. The main purpose of perspectives is to improve the user experience while interacting with the cube for analysis. It is worthwhile to have a very high level overview of the way queries interact with the engine before we go further. Follow the steps below along with the picture to understand better: ![query execution][1] 1. User sends query to the SSAS engine. 2. Engine tries to resolve the result from the cache first. If it was executed before, the results would be available in the cache and the results (green arrow) are sent back to the engine and then to the user. 3. If the results are not available in the cache, then the engine tries to resolve the request from the Aggregations. Now there are two steps here:- - Engine tries to find if there is an exact aggregation for the query. For example, if the query is to find the sales by 2007, engine tries to find an exact match or a direct hit. If found, it returns the result from the aggregation to the engine and then back to the user. - If the engine doesn’t find a direct hit, it tries to find an indirect hit. For example, it tries to see if there is an aggregation for any of the lower levels like Month. If yes, then the engine just needs to aggregate sales for the 12 months compared to 365 days, and then pass the result back to the user. 4. If the results are not available in aggregations, then the engine checks the partitions now where the base data resides. Having multiple partitions which are carefully designed will help in query performance also. For example, if there are multiple partitions partitioned by year, then the queries just need to check in the 2007 partition and aggregate it. If there is just one partition for all the years, there is more number of rows to check, and this might cause additional delays. Anyways, the engine finds all the base rows of the measure group, aggregates it and then sends the results back to the user. With this information in mind, we will start creating some partitions, then move on to creating aggregations and finally create the perspectives. - Let us partition the Fact Internet Sales measure group into two – all years before 2006 (included) and all years after 2006. For that, select the Partitions tab on the Sales cube, click on the ellipsis in the Source column for Fact Internet Sales and then change the Binding type to Query Binding. Now we can modify the query to male our partitions. Please note that we can use table binding if we already have the two tables with the data before 2006 and after in our database / DSV. ![create partitions][2] - Add the following statement to the WHERE clause of the query to restrict by all years less than or equal to 2006. **left([dbo].[FactInternetSales].[OrderDateKey],4)<=2006** It is nice to note that since we had date keys of the format yyyymmdd, it is easy to get the year/month/day from it. Using auto-generated surrogate keys for Date would have made partitioning difficult as we would need to join it with the time dimension tables to get the key. So it is always a good idea to have the Date keys to be of the format yyyymmdd. ![Partition clause][3] - Click on **OK** and we have got our first partition made. Now click on **New Partition**, and then select the **Fact Internet Sales** as the source table in the Partition Wizard, as shown below. Click on **Next**. ![new partition][4] - Then check the **Specify a query to restrict rows** option. ![Restrict rows in new partition][5] Add the following statement to the where clause to get all rows where the year is greater than 2006. **left([dbo].[FactInternetSales].[OrderDateKey],4)>2006** Click on **Next**. - Leave the Processing and Storage Locations as they are, and then click on **Next**. - Name the new partition, choose the Design Aggregations later option and click on **Finish**. ![new partition][6] Now we have got the 2 required partitions. - To create aggregation for these 2 partitions, go to the **Aggregations** tab, click on the **Design Aggregation** icon on the top left and then select the two partitions as shown below. Click on **Next**. ![New aggregation][7] - In the **Review Aggregation Usage** screen, make sure to turn the option as **None** for those unimportant attributes in a real life project. For now, let’s leave everything as default and click on **Next**. ![Review aggregation usage][8] - In the **Specify Object Counts** screen, click on the **Count** button, and then click on **Next** when it is finished. - In the **Set Aggregation Options** screen, choose the appropriate option and then click on **Start**. ![aggregation design][9] Once it is completed, click on **Next**. - Rename the aggregation design and then choose the Save the aggregation but do not process them option for now. Click on **Finish**. ![aggregation][10] - Now that we have got our aggregation design done, lets create 2 perspectives. For that, go to the perspectives tab and then click on the New Perspective icon. Rename the new perspective, and then select a few dimensions and measures. Repeat the step for the next perspective also like shown below. ![perspective][11] By the end of this post, we have made our partitions, aggregations and perspectives. This is all that will be covered in this post. In our next post, we will talk about processing the SSAS objects that we have created and on how we can see the result in the SSAS browser. [1]: http://media.beyondrelational.com/images.ashx?id=74819111f8f14e8ca1ebdda549bd892d&w=628&h=0 [2]: http://media.beyondrelational.com/images.ashx?id=f86e819878964292bb3ced17fffcb1cb&w=628&h=0 [3]: http://media.beyondrelational.com/images.ashx?id=a95f244239b047f3b612ceeb2c1fd5b1&w=628&h=0 [4]: http://media.beyondrelational.com/images.ashx?id=2a7c5bdd695a4cd989c8c6e066182de3&w=628&h=0 [5]: http://media.beyondrelational.com/images.ashx?id=d16aec98f48b42348716571e6710ed5c&w=628&h=0 [6]: http://media.beyondrelational.com/images.ashx?id=9d03c1b9861c478eab440c9b681703ad&w=628&h=0 [7]: http://media.beyondrelational.com/images.ashx?id=771ed717828a47ff862dd5f8ba1278ea&w=628&h=0 [8]: http://media.beyondrelational.com/images.ashx?id=1592c6d6a34841328e5ac3dfbd29a24c&w=628&h=0 [9]: http://media.beyondrelational.com/images.ashx?id=601ae8692abe41dc8ac67777e3c6d4b8&w=628&h=0 [10]: http://media.beyondrelational.com/images.ashx?id=4e540d3ba95b4ab69ac8a6d6027f9681&w=628&h=0 [11]: http://media.beyondrelational.com/images.ashx?id=1914353339524147873232a40a646c8f&w=628&h=0

Getting Started with Ajax - Part 8: Working with JSON

Tue, 01 Jan 2013 00:00:00 GMT

In the previous chapter, we showed how we can process XML data in AJAX application. This time, we will rewrite the XML example to work with JSON data. JSON data format is very popular with JavaScript, because it can be processed very easily, and is built into many JavaScript and AJAX libraries. Starting with XML data from previous chapter, this is what our JSON will look like: { "websites": { "website": [ { "name": "Amazon", "url": "http://www.amazon.com" }, { "name": "Google", "url": "http://www.google.com" }, { "name": "Ebay", "url": "http://www.ebay.com" } ] } } As you can see, there's pretty straightforward mapping from XML to JSON. Each XML tag is mapped into corresponding JSON string, and XML tags that contain multiple children are represented as an array in JSON. As before, we need to create the servlet, which will serve JSON data. This servlet will be mapped to `/ajaxjson` path. Servlet source code is shown below: protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/json"); PrintWriter out = response.getWriter(); try { out.println("{"); out.println("\"websites\": {"); out.println("\"website\": ["); out.println("{\"name\": \"Amazon\", \"url\": \"http://www.amazon.com\"},"); out.println("{\"name\": \"Google\", \"url\": \"http://www.google.com\"},"); out.println("{\"name\": \"Ebay\", \"url\": \"http://www.ebay.com\"}]"); out.println("}"); out.println("}"); } finally { out.close(); } } Finally, we need to create the page with script to send AJAX request and process the response. We will create page called `jsonpage.html`. This page contains single button, which will request data from server when clicked, and display results in table form. Complete source code can be found in attached archive, but we will focus only on callback method here, since that is where JSON processing is implemented. function xmlCallback(){ if(request.readyState == 4){ if(request.status == 200){ var json = eval('(' + request.responseText + ')'); var txt = ""; for(i = 0;i < json.websites.website.length;i++){ txt += ""; name = json.websites.website[i].name; txt += ""; url = json.websites.website[i].url; txt += ""; txt += ""; } txt += "

Site	URL
" + name + "	" + url + "

"; document.getElementById("webSiteList").innerHTML = txt; } } } The key here is the line `var json = eval('(' + request.responseText + ')');` Here, we create variable called “json”, which is obtained by evaluating the response sent back from server. This creates JavaScript object whose attributes represent members of data array, and can be accessed using dot notation. The rest of the callback is pretty much the same as for XML processing function, the only difference is the way we access data for name and URL (JSON-style vs. XML-style). This simple example outlines basics of JSON processing in AJAX. By enabling your applications to handle more than one data format, they can be more easily publicly accessible and will be able to interoperate with a number of third party applications. [You my find the sample application here.][1] [1]: http://beyondrelational.com/modules/12/tutorials/686/downloads/17837/ajax-sample-web-app-part-8.aspx