Best practices

http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx

Report Builder accessing from Internet

Report Builder 2008 if accessed from the Internet from external computers not in the same domain as report server

need to do the following:

on the remote machine, go to your User Account in Control Panel

Pick your Account

Select Manage Network Passwords

add an entry > reports url

put in the name of the report server >DOMAIN\username

put in the user and password you use to login > Password

apply it

run the report builder app again and it should download and install

this has to do with permissions not getting passed for non-domain members so that the Click Once install routine can download the application file

Project Cresent & Denali...:)

http://blogs.technet.com/b/dataplatforminsider/archive/2010/11/12/analysis-services-roadmap-for-sql-server-denali-and-beyond.aspx

Export the datagrid Image to excel

Use the full path

some thing like this.. just use the padding too here for reference :)

Return (HttpContext.Current.Request.Url.GetLeftPart(UriPartial.Authority) & HttpContext.Current.Request.ApplicationPath & "/thumbs/" & strArgument.PadLeft(7, Convert.ToChar("0")) & "_80.jpeg")

unable to copy from obj/debug to Bin in dotnet

Use this in the Build Events...

if exist "$(TargetPath).locked" del "$(TargetPath).locked"
if not exist "$(TargetPath).locked" move "$(TargetPath)" "$(TargetPath).locked"

Kill every one ..:) just connection to SQL Server

create procedure [dbo].[sp_killusers](@database varchar(30))

as

----------------------------------------------------

-- * Created By David Wiseman, Updated 19/11/2006

-- * http://www.wisesoft.co.uk

-- * This procedure takes the name of a database as input

-- * and uses the kill statment to disconnect them from

-- * the database.

-- * PLEASE USE WITH CAUTION!!

-- * Usage:

-- * exec sp_killusers 'databasename'

----------------------------------------------------

set nocount on

declare @spid int

declare @killstatement nvarchar(10)



-- Declare a cursor to select the users connected to the specified database

declare c1 cursor for select request_session_id

from sys.dm_tran_locks

where resource_type='DATABASE'

AND DB_NAME(resource_database_id) = @database

open c1

fetch next from c1 into @spid

-- for each spid...

while @@FETCH_STATUS = 0

begin

-- Don't kill the connection of the user executing this statement

IF @@SPID <> @spid

begin

-- Construct dynamic sql to kill spid

set @killstatement = 'KILL ' + cast(@spid as varchar(3))

exec sp_executesql @killstatement

-- Print killed spid

print @spid

end

fetch next from c1 into @spid

end

-- Clean up

close c1

deallocate c1

data model

A data model visually represents the nature of data, business rules governing the data, and how it will be organized in the database. A data model is comprised of two parts logical design and physical design


For example Company ABC is planning to build a guest house(database) and it calls the building architect(data modeler) and projects its building requirements (business requirements). Building architect(data modeler) develops the plan (data model) and gives it to company ABC. Finally company ABC calls civil engineers(DBA) to construct the guest house(database)

More @ http://www.learndatamodeling.com/dm_overview.htm

Office 2010 installation sucks... The Language of this installtion package is not supported by your system

The Language of this installtion package is not supported by your system

The reason I was getting this error was due to the method I had used in extracting the data from the file en_office_professional_plus_2010_x86_515486.exe

I had used Winzip, by right clicking the file and selecting Extract to D:\Office

The correct way for extacting the data was to extract using the command line. The syntax being

D:\en_office_professional_plus_2010_x86_515486.exe /extract:D:\Office

The switch /extract:D:\Office extracts the files contents to D:\Office on my PC, but could be any location.

Krish Krishnan's Be eye Blog

http://www.b-eye-network.com/blogs/krishnan/

datetime in integer

Select @dt = GETDATE()
Select CONVERT(varchar(26),@dt, 121)
, CONVERT(bigint, replace(replace(replace(replace(convert(varchar(23),@dt,121),'-',''),':',''),'.',''),' ',''))
, CONVERT(int, @dt) -- date offset starting 1899-12-31
, DATEADD( D, -(CONVERT(int, @dt) ), @dt)

file system. sql server

--==============================================================
--Script Author: Timothy Ford aka SQLAgentMan
-- http://thesqlagentman.com
-- Can be used for personal use; credit must be given to author
-- Code can not be used for commercial use, repackaged, resold without permission
--==============================================================

--==============================================================
--CREATE DATABASE iDBA IF IT DOES NOT ALREADY EXIST:
--==============================================================
/*
IF NOT EXISTS (SELECT name FROM master..sysdatabases WHERE name = 'iDBA')
BEGIN
CREATE DATABASE iDBA;
ALTER DATABASE [iDBA] SET AUTO_SHRINK OFF;
ALTER DATABASE [iDBA] SET RECOVERY SIMPLE WITH NO_WAIT;
ALTER DATABASE [iDBA] MODIFY FILE (NAME = N'iDBA', MAXSIZE = 1000MB , FILEGROWTH = 100MB);
ALTER DATABASE [iDBA] MODIFY FILE (NAME = N'iDBA_log', MAXSIZE = 200MB , FILEGROWTH = 100MB);
END

*/


--====================================================================
--CREATE THE STORED PROCEDURE AS USED FOR LOADING DBA REPOSITORY NIGHTLY
--====================================================================
USE [iDBA];

CREATE PROCEDURE [dbo].[usp_database_file_snapshot] AS
DECLARE @SQL VARCHAR(5000)
DECLARE @version smallint


--DETERMINE IF THE INSTANCE IS SQL 2005 OR LATER SO CATALOG VIEWS CAN BE USED
SELECT @version = CONVERT(smallint, LEFT(CONVERT(varchar(20),SERVERPROPERTY('ProductVersion')),CHARINDEX('.',CONVERT(varchar(20),SERVERPROPERTY('ProductVersion')))-1))

IF EXISTS (SELECT NAME FROM tempdb..sysobjects WHERE NAME = 'RESULTS_FILE_HISTORY')
BEGIN
DROP TABLE [tempdb].[dbo].[RESULTS_FILE_HISTORY]
END

CREATE TABLE [tempdb].[dbo].[RESULTS_FILE_HISTORY] ([Server] nvarchar(128), [DatabaseName] sysname, [Name] sysname, [Filename] NVARCHAR(260),
[FileType] varchar(4), [Size_In_Mb] int, [Available_Space_In_Mb] int, [Growth_Increments] int, [Growth_Units] varchar(2),
[Max_File_Size_In_Mb] int)

--IF THIS IS A SQL 2000 OR EARLIER INSTANCE YOU MUST USE THE SYSTEM TABLES
IF @version < 9
BEGIN
SELECT @SQL =
'USE [?] INSERT INTO [tempdb].[dbo].[RESULTS_FILE_HISTORY]([Server], [DatabaseName], [Name], [Filename],
[FileType], [Size_In_Mb], [Available_Space_In_Mb], [Growth_Increments],
[Growth_Units], [Max_File_Size_In_Mb])
SELECT @@servername, DB_NAME(),
[name] AS [Name],
[filename] AS [Filename],

[FileType] =
CASE (status & 0x40)
WHEN 0 THEN ''Data'''
+
'ELSE ''Log'''
+
'END,

[Size_In_Mb] =
CASE ceiling([size]/128)
WHEN 0 THEN 1
ELSE ceiling([size]/128)
END,
[Available_Space_In_Mb] =
CASE ceiling([size]/128)
WHEN 0 THEN (1 - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128)
ELSE (([size]/128) - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128)
END,

[Growth_Increments] =
CASE (status & 0x100000)
WHEN 0 THEN CAST(CEILING([growth]/1024*8) AS int)'
+
'ELSE [growth]'
+
'END,

[Growth_Units] =
CASE (status & 0x100000)
WHEN 0 THEN ''Mb'''
+
'ELSE ''%'''
+
'END,
[Max_File_Size_In_Mb] =
CASE [maxsize]
WHEN -1 THEN NULL
WHEN 268435456 THEN NULL
ELSE [maxsize]/1024*8
END
FROM dbo.sysfiles
ORDER BY [fileid]'
END
ELSE --IF THIS IS A SQL 2005 OR LATER INSTANCE YOU WILL USE THE CATALOG VIEWS
BEGIN
SELECT @SQL =
'USE [?] INSERT INTO [tempdb].[dbo].[RESULTS_FILE_HISTORY]([Server], [DatabaseName], [Name], [Filename],
[FileType], [Size_In_Mb], [Available_Space_In_Mb], [Growth_Increments],
[Growth_Units], [Max_File_Size_In_Mb])
SELECT @@servername, DB_NAME(),
[name] AS [Name],
physical_name AS [Filename],
[FileType] =
CASE type
WHEN 0 THEN ''Data'''
+
'WHEN 1 THEN ''Log'''
+
'END,
[Size_In_Mb] =
CASE ceiling([size]/128)
WHEN 0 THEN 1
ELSE ceiling([size]/128)
END,
[Available_Space_In_Mb] =
CASE ceiling([size]/128)
WHEN 0 THEN (1 - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128)
ELSE (([size]/128) - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128)
END,

[Growth_Increments] =
CASE [is_percent_growth]
WHEN 0 THEN [growth]'
+
'ELSE CAST(CEILING([growth]/1024*8) AS int)'
+
'END,
[Growth_Units] =
CASE [is_percent_growth]
WHEN 1 THEN''%'''
+
'ELSE ''Mb'''
+
'END,
[Max_File_Size_In_Mb] =
CASE [max_size]
WHEN -1 THEN NULL
WHEN 268435456 THEN NULL
ELSE [max_size]/1024*8
END
FROM sys.database_files
ORDER BY [FileType], [file_id]'
END

--Run the command against each database
EXEC sp_MSforeachdb @SQL

--You can uncomment the following statement in order to see the results returned in SSMS:
/*
SELECT [Server], [DatabaseName], [Name], [Filename],
[FileType], [Size_In_Mb], [Available_Space_In_Mb],
CEILING(CAST([Available_Space_In_Mb] AS decimal(10,1)) / [Size_In_Mb]*100) AS [Free Space %],
[Growth_Increments], [Growth_Units], [Max_File_Size_In_Mb]
FROM [tempdb].[dbo].[RESULTS_FILE_HISTORY];
*/

GO
-------------------------------------------------------------------------------

--==============================================================
--CODE TO PERSIST THE DATA (Daily In This Case)
--==============================================================
--==============================================================
--Script Author: Timothy Ford aka SQLAgentMan
-- http://thesqlagentman.com
-- Can be used for personal use; credit must be given to author
-- Code can not be used for commercial use, repackaged, resold without permission
--==============================================================

/*
AUTHORS NOTE:
This runs via an SSIS process I've instituted to collect metadata from all instances I monitor.
If you're interested in doing so I suggest reading Rodney Landrum's DBA Toolbox book
from Red Gate Press.

The code here only runs locally against non-persisted data - just the temp table

Use Ctl+Shift+M to replace the template parameter for threshold with a value of your choosing. Default is 85%
*/

EXEC iDBA.dbo.usp_database_file_snapshot;

--==============================================================
-- Relies upon previously creating the iDBA.dbo.usp_database_file_snapshot stored procedure
--==============================================================
SELECT [Server], [DatabaseName], [Name], [Filename],
[FileType], [Size_In_Mb] AS [File Size (Mb)], [Available_Space_In_Mb] AS [Free Space (Mb)],
[Max_File_Size_In_Mb] AS [Max File Size (Mb)], ([Size_In_Mb] - [Available_Space_In_Mb]) AS [Space Used (Mb)],
CAST([Growth_Increments] AS varchar(10)) + ' ' + [Growth_Units] AS [Growth Increment],
CAST((([Size_In_Mb] - [Available_Space_In_Mb])*1.0) / [Size_In_Mb] AS decimal(5,2)) AS [% Consumed],
[date_stamp]
FROM [tempdb].[dbo].[RESULTS_FILE_HISTORY]
WHERE (([Size_In_Mb] - [Available_Space_In_Mb])*1.0) / [Size_In_Mb] >= 0.85
ORDER BY [Server], [DatabaseName];

Reporting Services - reading .rdl files

http://www.mssqltips.com/tip.asp?tip=1839

Sins of Time , but its better to have time and date in two separate dimensions

Hats off to Steve

/** * Function: fn_GetTimestampRange
* Author: Steve Gray / steve.gray@cobaltsoftware.net
* Version: 1.0.0.0
* Date: 30 April 2010
*
* Parameters:
* @FromDate - Start of time-range
* @ToDate - End of time-range
* @IncludeSeconds - 0 = Minute level, 1 = Second level
**/
CREATE FUNCTION fn_GetTimestampRange(@FromDate DATETIME, @ToDate DATETIME, @IncludeSeconds BIT)
RETURNS TABLE
AS
RETURN (
/* Loop through the days in the range */
WITH DayCTE([Date])
AS
(
SELECT CONVERT(DATETIME, CONVERT(VARCHAR(255), @FromDate, 20))
UNION ALL
SELECT DATEADD(DAY, 1, [Date]) FROM DayCTE WHERE Date < @ToDate
), /* Obtain a set of numbers 0..59 */
NumberCTE([Number])
AS
(
SELECT 0
UNION ALL
SELECT Number + 1 FROM NumberCTE WHERE Number < 59
)
SELECT
Result.DateKey,
DATEPART(YEAR, Result.DateKey) AS [Year],
DATEPART(QUARTER, Result.DateKey) AS [Quarter],
DATEPART(WEEK, Result.DateKey) AS [Week],
DATEPART(WEEKDAY, Result.DateKey) AS [WeekDayNumber],
DATENAME(WEEKDAY, Result.DateKey) AS [WeekDayName],
DATEPART(DAYOFYEAR, Result.DateKey) AS [DayOfYear],
DATEPART(MONTH, Result.DateKey) AS [Month],
DATEPART(DAY, Result.DateKey) AS [Day],
DATEPART(HOUR, Result.DateKey) AS [Hour],
DATEPART(MINUTE, Result.DateKey) AS [Minute],
DATEPART(SECOND, Result.DateKey) AS [Second]
FROM
(
SELECT
DATEADD(SECOND, COALESCE(SecondCTE.Number, 0),
DATEADD(MINUTE, MinuteCTE.Number,
DATEADD(HOUR, HourCTE.Number, DayCTE.Date))) AS DateKey
FROM
DayCTE
LEFT JOIN NumberCTE HourCTE ON HourCTE.Number
BETWEEN 0 AND 23
AND DayCTE.Date IS NOT NULL
LEFT JOIN NumberCTE MinuteCTE ON MinuteCTE.Number
BETWEEN 0 AND 59
AND HourCTE.Number IS NOT NULL
LEFT JOIN NumberCTE SecondCTE ON SecondCTE.Number
BETWEEN 0 AND 59
AND @IncludeSeconds = 1
AND MinuteCTE.Number IS NOT NULL
WHERE
DATEADD(SECOND, COALESCE(SecondCTE.Number, 0),
DATEADD(MINUTE, MinuteCTE.Number,
DATEADD(HOUR, HourCTE.Number, DayCTE.Date)))
BETWEEN @FromDate AND @ToDate
) RESULT
);

Shrink the log file

select name,recovery_model_desc from sys.databases
GO
Alter database YourDatabaseName Recovery simple
GO
Declare @LogFileLogicalName sysname
select @LogFileLogicalName=Name from sys.database_files where Type=1
print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,100)

Multi-Valued Parameter function for SSRS

CREATE FUNCTION charlist_to_table

(@list ntext,

@delimiter nchar(1) = N',')

RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,

str varchar(4000),

nstr nvarchar(2000)) AS

BEGIN

DECLARE @pos int,

@textpos int,

@chunklen smallint,

@tmpstr nvarchar(4000),

@leftover nvarchar(4000),

@tmpval nvarchar(4000)

SET @textpos = 1

SET @leftover = ''

WHILE @textpos <= datalength(@list) / 2

BEGIN

SET @chunklen = 4000 - datalength(@leftover) / 2

SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)

SET @textpos = @textpos + @chunklen

SET @pos = charindex(@delimiter, @tmpstr)

WHILE @pos > 0

BEGIN

SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))

INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)

SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))

SET @pos = charindex(@delimiter, @tmpstr)

END

SET @leftover = @tmpstr

END

INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)),

ltrim(rtrim(@leftover)))

RETURN

END

GO


----------------------------------------------------------------------

simple one

-----------------------------------------------------------------------

CREATE FUNCTION dbo.fn_MVParam(@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @VALUES TABLE (Param nvarchar(4000))AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(4000)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @VALUES(Param) VALUES(@Piece)
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0 BREAK
END
RETURN
END

Size of db

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/81fd5ec9-ce0f-4c2c-8ba0-6c483cea6c75.htm

Lock T-sql Queries

SELECT
SessionID = s.Session_id,
resource_type,
DatabaseName = DB_NAME(resource_database_id),
request_mode,
request_type,
login_time,
host_name,
program_name,
client_interface_name,
login_name,
nt_domain,
nt_user_name,
s.status,
last_request_start_time,
last_request_end_time,
s.logical_reads,
s.reads,
request_status,
request_owner_type,
objectid,
dbid,
a.number,
a.encrypted ,
a.blocking_session_id,
a.text
FROM
sys.dm_tran_locks l
JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
LEFT JOIN
(
SELECT *
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
) a ON s.session_id = a.session_id
WHERE
s.session_id > 50

order by SessionId desc
----------------------------------------------------------------------------------

Current Statements

DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = 55
SELECT * FROM ::fn_get_sql(@Handle)







----------------------------------------------------------------------------------

select l.request_session_id,s.login_name,s.[host_name],s.[program_name],l.resource_type as 'Lock Type',db_name(l.resource_database_id) as 'Database',object_name(p.[object_id],l.resource_database_id) as 'Object Name',l.request_mode AS 'LockMode',l.request_status AS 'LockStatus'
,l.request_reference_count as
'aprxmt # of times the same requestor has requested this resource'
from sys.dm_tran_locks as l INNER JOIN sys.partitions as p
ON l.resource_associated_entity_id = p.hobt_id
INNER JOIN sys.dm_exec_sessions as s
ON l.request_session_id = s.session_id
where l.request_session_id > 50

Enable CDC

declare @rc int
exec @rc = sys.sp_cdc_enable_db
select @rc
-- new column added to sys.databases: is_cdc_enabled
select name, is_cdc_enabled from sys.databases

CDC wrapper creation t-sql

DECLARE @wrapper_functions TABLE (
function_name sysname,
create_script nvarchar(max));

INSERT INTO @wrapper_functions
EXEC sys.sp_cdc_generate_wrapper_function;

DECLARE @create_script nvarchar(max);
DECLARE #hfunctions CURSOR LOCAL fast_forward
FOR
SELECT create_script FROM @wrapper_functions;

OPEN #hfunctions;
FETCH #hfunctions INTO @create_script;
WHILE (@@fetch_status <> -1)
BEGIN
EXEC sp_executesql @create_script
FETCH #hfunctions INTO @create_script
END;

CLOSE #hfunctions;
DEALLOCATE #hfunctions;

log file deletion with vbs

Option Explicit
On Error Resume Next
Dim oFSO, oFolder, sDirectoryPath
Dim oFileCollection, oFile, sDir
Dim iDaysOld

'' Specify Directory Path From Where You want to clear the old files

sDirectoryPath = "C:\ETL\LOGFiles"

'' Specify Number of Days Old File to Delete

iDaysOld = 0

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(sDirectoryPath)
Set oFileCollection = oFolder.Files

For each oFile in oFileCollection

If LCase(Right(Cstr(oFile.Name), 3)) = "log" Then

If oFile.DateLastModified < (date() - iDaysOld) Then
oFile.Delete(True)
End If

End If
Next

Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing

Schema

select 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + o.Name from sys.objects o
Inner join sys.schemas s on o.schema_id = s.schema_id
where s.name= 'dbo' and o.Type = 'U' order by o.Name--(o.Type = 'A' Or o.Type = 'P' Or o.Type = 'V')

select 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + o.Name from sys.objects o
Inner join sys.schemas s on o.schema_id = s.schema_id
where s.name= 'dbo' and o.Type = 'P' and o.name <> 'sysdiagrams' order by o.Name--(o.Type = 'A' Or o.Type = 'P' Or o.Type = 'V')

select o.name tablename,s.name schemaname from sys.objects o left join sys.schemas s on o.schema_id =s.schema_id
where o.type ='U' and o.name <> 'sysdiagrams' and s.name not in ('cdc','dbo')

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' order by table_name
declare @tablename Char(50)
SET @tablename='ACH'
select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME=@tablename

ALTER SCHEMA Process TRANSFER dbo.MonthlyTickler

select s.Name , o.Name from sys.objects o
Inner join sys.schemas s on o.schema_id = s.schema_id
where o.Type = 'P'

Purged multiple tables.

create a cursor with all the table Names to be purged

OPEN CurDeleteRec;
FETCH CurDeleteRec INTO @TableName
WHILE (@@fetch_status <> -1)
BEGIN

select @numrec = 0
select @Query1 = 'select @numrec1 = COUNT(*) from TabletoPurge where created < @purgetodate1'
SELECT @Query1 = REPLACE(@Query1, 'TabletoPurge', @TableName)
SET @ParmDefinition1 = '@numrec1 INT OUTPUT, @purgetodate1 DATETIME'

-- print @Query1

EXEC sp_executesql @Query1, @ParmDefinition1, @numrec1 = @numrec OUTPUT, @purgetodate1 = @purgetodate

select @Query2 = 'delete from TabletoPurge where created < @purgetodate2'
SELECT @Query2 = REPLACE(@Query2, 'TabletoPurge', @TableName)
SET @ParmDefinition2 = '@purgetodate2 DATETIME'

--print @Query2

EXEC sp_executesql @Query2, @ParmDefinition2, @purgetodate2 = @purgetodate

insert datapurgelog
(
created ,
tablename ,
Numberofrecord
)
values (getdate(),
@TableName,
@numrec)

FETCH CurDeleteRec INTO @TableName
END
CLOSE CurDeleteRec;
DEALLOCATE CurDeleteRec;

YTD / MTD - SSRS expression

Expression I use to change my parameter default values. These could be written many different ways and actually use VB in the customer code are but I wrote them this way if you’re not a VB expert.

Month over month

=DateAdd("m",-1,Today)

MTD

=DateAdd("d",-(Day(today)-1), Today)

Week over week

=DateAdd("ww",-1, Today)

or

=DateAdd("d",-7, Today)

WTD

=DateAdd("d",-DatePart(DateInterval.WeekDay,Today,0,0)+1,Today)

Year over Year

=DateAdd("yyyy",-1, Today)

YTD

=DateAdd("d",-DatePart(DateInterval.DayOfYear,Today,0,0)+1,Today)

Notice in the YTD and WTD expression I’m calling a DateInterval function that is actually a Visual Basic function but does not require you do add anything to the custom code section of your report.

More @
http://www.bidn.com/blogs/DevinKnight/ssis/381/ssrs-commonly-required-date-expressions

MERGE with SSIS

Use exec sp_executesql N’merge !!!! instead of direct Merge statement in Execute SQL Task

Report Styles

http://www.simple-talk.com/sql/reporting-services/reporting-services-with-style/

Publish multiple .rdl files to Reporting services - First Way Creating MSI for reporting Services

Create a Class library project and add the web references to http://localhost/ReportServer/ReportService2005.asmx.

Add the following code to the Class1.Cs
using System;
using System.Collections.Generic;
using System.Text;
using ClassLibrary1.ReportService2005;
using System.IO;
using System.Configuration.Install;
using System.ComponentModel;

namespace InstallerNameSpace
{

// Set 'RunInstaller' attribute to true.
[RunInstallerAttribute(true)]
public class InstallerClass : System.Configuration.Install.Installer
{
public InstallerClass()
: base()
{
}

// Override the 'Install' method.
// The Installation will call this method to run the Custom Action
public override void Install(System.Collections.IDictionary savedState)
{
base.Install(savedState);


//// get parameters for custom install of data source and report
string dataSourceName = this.Context.Parameters["DSN"];
string dataSourceLocation = this.Context.Parameters["DSL"];
string sqlServerName = this.Context.Parameters["DSSERVERNAME"];
string dbName = this.Context.Parameters["DBNAME"];
string uid = this.Context.Parameters["UID"];
string pwd = this.Context.Parameters["PWD"];

ReportingService2005 rs = new ReportingService2005();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

//Create Folder to drop report in
rs.CreateFolder("MSI Report Demo", "/", null);

//Create Report using RDL doc that was in MSI

string reportPath = @"SampleReport.rdl";
byte[] reportDefinition;
ClassLibrary1.ReportService2005.Warning[] warnings;
FileStream stream = File.OpenRead(@"c:\temp\SampleReport.rdl");
reportDefinition = new Byte[stream.Length];
stream.Read(reportDefinition, 0, (int)stream.Length);
stream.Close();

warnings = (ClassLibrary1.ReportService2005.Warning[])rs.CreateReport(reportPath.Remove(reportPath.Length - 4, 4), @"/MSI Report Demo", true, reportDefinition, null);

//Create DataSource that Report will Use

DataSource dSource = new DataSource();
DataSourceDefinition dDefinition = new DataSourceDefinition();

dSource.Item = dDefinition;
dDefinition.Extension = "OLEDB-MD";
dDefinition.ConnectString = @"Data Source=" + sqlServerName + @";Initial Catalog=" + dbName;
System.Diagnostics.Trace.WriteLine(dDefinition.ConnectString.ToString());
dDefinition.ImpersonateUserSpecified = true;
dDefinition.Prompt = null;
dDefinition.WindowsCredentials = true;
dDefinition.UserName = uid;
dDefinition.Password = pwd;
dDefinition.CredentialRetrieval = CredentialRetrievalEnum.Store;
dSource.Name = dataSourceName;
try
{
rs.CreateDataSource(dataSourceName, dataSourceLocation, false, dDefinition, null);
}

catch (System.Web.Services.Protocols.SoapException ex)
{
Console.WriteLine(ex.Detail.InnerXml.ToString());
}

// Report and Datasource created, now fix up datasource reference to make sure report points at correct ds
System.Diagnostics.Trace.WriteLine("DS Created");
try
{

DataSourceReference reference = new DataSourceReference();
DataSource ds = new DataSource();
reference.Reference = dataSourceLocation + @"/" + dataSourceName;
DataSource[] dsarray = rs.GetItemDataSources(@"/MSI Report Demo/SampleReport");
ds = dsarray[0];
ds.Item = (DataSourceReference)reference;
rs.SetItemDataSources(@"/MSI Report Demo/SampleReport", dsarray);
}
catch (System.Web.Services.Protocols.SoapException ex)
{
Console.WriteLine(ex.Detail.InnerXml.ToString());
}
System.IO.File.Delete(@"c:\temp\Sample Report.rdl");
}




}
}

More Info @ http://blogs.msdn.com/bimusings/archive/2006/03/01/541599.aspx

Some favourites- Links

Charts
Download details Microsoft Chart Controls Add-on for Microsoft Visual Studio 2008
Download details Microsoft Chart Controls for Microsoft .NET Framework 3.5
howto Use the new ASP.NET Chart Controls with ASP.NET MVC Code-Inside Blog International
New ASP.NET Charting Control aspchart runat=server- - ScottGu's Blog
Dashboards
4 Columns of Joy A Dashboard Template from The Dashboard Spy
faceOut - Infragistics NetAdvantage for Silverlight
http--demos.telerik.com-wpf-salesdashboard-
http--xamples.infragistics.com-2009.1-xamShowcase.xbap
Infragistics Dashboard
Telerik Sales Dashboard - Grid, Chart, Gauge, Treeview
DW
Building a Data Warehouse With ... - Google Book Search
Creating Reports with Report Designer
Denis Gobo Business Intelligence Screencasts
Download Reporting Services Scripter
rdoherty's WebLog SQL Server 2008 Integration Services - Scalability Lessons Learned
SQL Server 2008 Change Data Capture (CDC)
SQL Server Data Warehousing & BI Links
SQL Server Data Warehousing & BI Links1
SQLBI - Marco Russo Display the MDX query of an Excel 2007 PivotTable
ssis Exporting data to a text file using a package « Systems Engineering and RDBMS
DW 2.0
Bill Inmon defines DW 2.0 The Business Intelligence Blog
Exams
ts Microsoft SQL Server 2008, Business Intelligence Development and Maintenance

Microstrategy
MicroStrategy Reporting Suite for Microsoft Analysis Services

oracle
2 Microsoft SQL Server and Oracle Compared
http--www.oracle.com-technology-products-database-clustering-pdf-twp_racsqlserver_2008.pdf
Microsoft SQL Server, Sybase Adaptive Server, and Oracle Compared

Silverlight
codeproject 7 simple steps to connect SQL Server using WCF from SilverLight. Free source code and programming help
Hands-On Labs The Official Microsoft Silverlight Site
Silverlight Toolkit Samples
SQL Server - Best Practices
Sizing
Estimating the Size of a Database

Service Broker Advanced Basics Workbench
SQL Server Service Broker Team Blog Fast data push tuning
.NET Interoperability - Calling COM Components from .NET
A Simple Approach to SQL Server 2005 Encryption - SQLServerCentral
Alphanumeric and Special Character Validation- New - SQL Server Central
Analysis Services Double Hop Authentication - Windows Live
Anatomy of an Incremental Load - SQLServerCentral
ASP.NET AJAX Calendar Extender – Tips and Tricks
ASP.NET Portal Starter Kit
Ben Hall's Blog SQL Server 2008 Change Data Capture (CDC)
codeproject How to call SSIS package from the stored procedure. Free source code and programming help
codeproject SQL Server Procedures And Functions Encryptions. Free source code and programming help
Common Solutions for T-SQL Problems - Home
dasBlonde - WCF Webcast Series
Download details Excel 2002-2003 Add-in for SQL Server Analysis Services
Download details Log Parser 2.2
Finding and troubleshooting SQL Server deadlocks
Handling Slowly Changing Dimensions in SQL Server Integration Services SSIS

How to Encrypt a Column of Data
http--binaryelves.wordpress.com-2009-01-22-regular-expressions-remove-all-special-characters-from-a-string-allowing-only-alphanumeric-and-chars-and-
http--vyaskn.tripod.com-code.htm
Introduction to CLR Database Objects
Introduction to New T-SQL Programmability Features in SQL Server 2008
Kimball Group Data Warehouse Training Design Tips
MSN.com

Preparation Guide for Exam 70-551 UPGRADE MCAD Skills to MCPD Web Developer by Using the Microsoft .NET Framework
Radio Station Guide

Reporting Services - Add a logo to the Report Manager - Jon Galloway
SQL Server Central
SQL Server Helper - User-Defined Functions - Simple String Encryption-Decryption
SQL SERVER – 2008 – Configure Database Mail – Send Email From SQL Database « Journey to SQL Authority with Pinal Dave
sqlservercentral.com-articles-Integration+Services+(SSIS)-62063-
SSIS - Decrease your fact table loading time up to 40% - Jorg Klein's Microsoft Business Intelligence Blog [Macaw]
Table-Valued Parameters in SQL Server 2008 (ADO.NET)
TechNet Webcast 24 Hours of SQL Server 2008 Data Warehousing Scale and Performance (Level 200)
TechNet Webcast Data Warehousing Enhancements in Microsoft SQL Server 2008 (Level 200)

Web Design Tools - Visual WebGui Themes & Controls

All SQL jobs

USE msdb
Go

SELECT dbo.sysjobs.Name AS 'Job Name',
'Job Enabled' = CASE dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE dbo.sysschedules.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END,
'Start Date' = CASE active_start_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),active_start_date),1,4) + '/' +
substring(convert(varchar(15),active_start_date),5,2) + '/' +
substring(convert(varchar(15),active_start_date),7,2)
END,
'Start Time' = CASE len(active_start_time)
WHEN 1 THEN cast('00:00:0' + right(active_start_time,2) as char(8))
WHEN 2 THEN cast('00:00:' + right(active_start_time,2) as char(8))
WHEN 3 THEN cast('00:0'
+ Left(right(active_start_time,3),1)
+':' + right(active_start_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(active_start_time,5),1)
+':' + Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
WHEN 6 THEN cast(Left(right(active_start_time,6),2)
+':' + Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
END,
-- active_start_time as 'Start Time',
CASE len(run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(run_duration,3),1)
+':' + right(run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(run_duration,5),1)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
END as 'Max Duration',
CASE(dbo.sysschedules.freq_subday_interval)
WHEN 0 THEN 'Once'
ELSE cast('Every '
+ right(dbo.sysschedules.freq_subday_interval,2)
+ ' '
+ CASE(dbo.sysschedules.freq_subday_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END as char(16))
END as 'Subday Frequency'
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules
ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM dbo.sysjobhistory
GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time = 0
UNION
SELECT dbo.sysjobs.Name AS 'Job Name',
'Job Enabled' = CASE dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE dbo.sysschedules.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END,
'Start Date' = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' +
substring(convert(varchar(15),next_run_date),5,2) + '/' +
substring(convert(varchar(15),next_run_date),7,2)
END,
'Start Time' = CASE len(next_run_time)
WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))
WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))
WHEN 3 THEN cast('00:0'
+ Left(right(next_run_time,3),1)
+':' + right(next_run_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(next_run_time,6),2)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
END,
-- next_run_time as 'Start Time',
CASE len(run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(run_duration,3),1)
+':' + right(run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(run_duration,5),1)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
END as 'Max Duration',
CASE(dbo.sysschedules.freq_subday_interval)
WHEN 0 THEN 'Once'
ELSE cast('Every '
+ right(dbo.sysschedules.freq_subday_interval,2)
+ ' '
+ CASE(dbo.sysschedules.freq_subday_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END as char(16))
END as 'Subday Frequency'
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM dbo.sysjobhistory
GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time <> 0
ORDER BY [Start Date],[Start Time]

Date Picker in OLAP SSRS

1. Change the data type of the Parameter to datetime and remove all default and get query
2. Right click the dataset and change the parameter with the expression as

="[Dim Date].[Date].&["+format(Parameters!FromDimDateDate.Value,"yyyy-MM-dd") +"T00:00:00]"

you are done !!

Multi Valued Parameter display in OLAP SSRS reports

MultiValued Parameters - =Join(Parameters!Product.Label, ",")
=Join(Parameters!Product.Label, vbcrfl) for new line