set context_info — Option to get parameter value during session across multiple batches

/*some time we need to use parameter values to be available for multiple bathces in same session
We can use context_info this case. Its value is available during current session and we can get its info from
sys.dm_exec_requests
sys.dm_exec_sessions
sys.sysprocesses

*/

–Example

DECLARE @var VARCHAR(50)

SET @var = ‘My Name is Amish M Shah’

DECLARE @binvar BINARY(128)

SELECT @binvar = CONVERT(BINARY(128), @var)

SET context_info @binvar

go

/* New batch starts from here, context_info values which we set in previous batch is available in this batch also, from which we will get parameter value.*/

SELECT CONVERT(VARCHAR(50), context_info) parameter

FROM   sys.dm_exec_sessions

WHERE  session_id = @@SPID

Maintenance :- Job History (sp_delete_backuphistory)

/*
Recently for one of my client I found that his backuphistory table has become very large. Since SQL Server do not clear backupjob history we need to clear it regular interval
*/

sp_delete_backuphistory
EXEC sp_delete_backuphistory @oldestdate
It will delete backup history for date before @oldestdate

sp_delete_database_backuphistory
EXEC sp_delete_database_backuphistory @dbname
Will delete history for specified database

sp_help_jobhistory

Provides information for jobhistory
EXEC sp_help_jobhistory @job_name = ‘Expired subscription clean up’
It accepts many other parameters. More info you can find from Books Online for this.

MDX:-Total of last N Days from Today.

Since I dont have forums on this blog.I would like to post the problems by mail using blogs.
Recently I got a problem to get sum of last N days from current Date.

I am posting a sample here which will give a sum of last 14 days from today.
We need to use VBA function to generated a string which is exactly like member name and then convert it to member using strtomember function.

This sample is using Adventureworks Database ,Analysis Service 2005.

with member [Measures].[CurrDate] as
“[Date].[Calendar].[Date].[” +Format(Now(),”MMMM”) + ” ” + CStr(Day(Now()))+”, “+CStr(Year(Now())) +”]”
set [Last14Days] as
LastPeriods (14, strtomember([Measures].[CurrDate]))
member [Date].[Calendar].[Total14Days]as
aggregate([Last14Days])
select [Measures].[Internet Sales Amount] on 0,
{[Last14Days],[Total14Days]} on 1
from
[Adventure Works]

Table Partitions

Table Partition is a new feature in SQL Server 2005.
Here I am posting a sample code how to create a Partitioned table and view its meta data in system tables.
/* We need to create a partition scheme and function on which we will partition the data */

create partition function part_test_func(datetime) as range
left for values (‘2008-09-10′,’2008-09-20′,’2008-09-30’)

/* so it has range values like
<= ‘2008-09-10’ > ‘2008-09-10 and <= ‘2008-09-20’ > ‘2008-09-20 and <= ‘2008-09-30′ >’2008-09-30’
*/
/* Now we need to map file group to each of this partition
Here first , second and third are filegroups */

create partition scheme part_test_scheme
as partition part_test_func to(first,second,third,first)

/*Now we will cretae a table on this partition scheme */

create table part_test
(id int identity(1,1) ,
data varchar(100),
date datetime)
on part_test_scheme(date)

insert into part_test values(‘a’,’2008-09-05′)
insert into part_test values(‘a’,’2008-09-15′)
insert into part_test values(‘a’,’2008-09-25′)
insert into part_test values(‘a’,’2008-10-05′)

/* Now we will get info for partion function */
/* for partition function */
select * from sys.partition_functions where name = ‘part_test_func’
/* for partition scheme */
select * from sys.partition_schemes where name = ‘part_test_scheme’
or
select * from sys.partition_schemes where function_id in
(select function_id from sys.partition_functions where name = ‘part_test_func’)
/* To get rowinfo for partition wise */

select * from sys.partitions where object_id = object_id(‘part_test’)

/*This will give data type info on which partition is created */

select * from sys.partition_parameters where function_id in
(select function_id from sys.partition_functions where name = ‘part_test_func’)

/* To get range for each partition */
select * from sys.partition_range_values where function_id in
(select function_id from sys.partition_functions where name = ‘part_test_func’)

select * from sys.indexes where object_id = object_id(‘part_test’)

/* This will give info for each data space */
select * from sys.data_spaces

/* To get filegroup associated to each partition */

select p.*, d.name filegroupname from sys.destination_data_spaces p inner join sys.data_spaces d
on p.data_space_id = d.data_space_id
where partition_scheme_id in
(
select data_space_id from sys.partition_schemes where name = ‘part_test_scheme’
)