Binding an Excel table to the results of an MDX query

Original article: http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!6009.trak

Code Snippets in case the original site goes down.

   1: <xml id=docprops><o:DocumentProperties

   2:   xmlns:o="urn:schemas-microsoft-com:office:office"

   3:   xmlns="http://www.w3.org/TR/REC-html40">

   4:   <o:Name>SSAS Query Test</o:Name>

   5:  </o:DocumentProperties>

   6: </xml><xml id=msodc><odc:OfficeDataConnection

   7:   xmlns:odc="urn:schemas-microsoft-com:office:odc"

   8:   xmlns="http://www.w3.org/TR/REC-html40">

   9:   <odc:Connection odc:Type="OLEDB">

  10:    <odc:ConnectionString>Provider=MSOLAP.4;Integrated Security=SSPI;

  11:     Persist Security Info=True;Data Source=localhost;

  12:     Initial Catalog=Adventure Works DW 2008</odc:ConnectionString>

  13:    <odc:CommandType>MDX</odc:CommandType>

  14:    <odc:CommandText>select {[Measures].[Internet Sales Amount], 

  15:     [Measures].[Internet Tax Amount]} on  0, 

  16:     [Date].[Calendar Year].members on 1 from [Adventure Works]

  17:     </odc:CommandText>

  18:   </odc:Connection>

  19:  </odc:OfficeDataConnection>

  20: </xml>

   1: <xml id=docprops><o:DocumentProperties

   2:   xmlns:o="urn:schemas-microsoft-com:office:office"

   3:   xmlns="http://www.w3.org/TR/REC-html40">

   4:   <o:Name>SSAS Table Test</o:Name>

   5:  </o:DocumentProperties>

   6: </xml><xml id=msodc><odc:OfficeDataConnection

   7:   xmlns:odc="urn:schemas-microsoft-com:office:odc"

   8:   xmlns="http://www.w3.org/TR/REC-html40">

   9:   <odc:Connection odc:Type="OLEDB">

  10:    <odc:ConnectionString>Provider=MSOLAP.4;Integrated Security=SSPI;

  11:     Persist Security Info=True;Data Source=localhost;

  12:     Initial Catalog=Adventure Works DW 2008</odc:ConnectionString>

  13:    <odc:CommandType>Table</odc:CommandType>

  14:    <odc:CommandText>Adventure Works.$Source Currency</odc:CommandText>

  15:   </odc:Connection>

  16:  </odc:OfficeDataConnection>

  17: </xml>

Converting Multiple Rows into a CSV String (Set Based Method)

original code for Converting Multiple Rows into a CSV String (Set Based Method)
http://www.sqlteam.com/article/converting-multiple-rows-into-a-csv-string-set-based-method


create table #Page47 (
i int not null,
vc varchar(5) not null,
constraint pk_Page47 primary key (i,vc) )
go
set nocount on
declare @i int
select @i = 0
while @i <5000
begin
insert into #Page47 (i,vc)
select round((rand() * 100), 0),
char(round((rand() * 25), 0) + 97) +
char(round((rand() * 25), 0) + 97) +
char(round((rand() * 25), 0) + 97) +
char(round((rand() * 25), 0) + 97) +
char(round((rand() * 25), 0) + 97)
select @i = @i + 1
end
go
--create a table to work with
create table #workingtable (
i int not null,
vc varchar(5) not null,
list varchar(8000),
constraint pk_wt primary key (i,vc) )
insert into #workingtable (i,vc)
select i,vc
from #Page47
order by i,vc
declare
@list varchar(8000),
@lasti int
select
@list = '',
@lasti = -1
--here is the meat of the work
update
#workingtable
set
@list = list = case
when @lasti <> i then vc
else @list + ', ' + vc
end,
@lasti = i
--return a sample from the final rowset
select top 10
i,
case
when len(max(list)) > 50 then convert(varchar(50), left(max(list),47) + '...')
else convert(varchar(50),max(list))
end as list
from
#workingtable
group by
i
order by
newid()
go
i list
----------- --------------------------------------------------
127 itvgq, ljosw, nxmdj, oshrp, plxff, pubig, sthck...
849 gcifo, hbxkf, njkdl, sfesm, sjhky, uxhfq, vjeno...
684 fejly, fqyqf, gpfce, hutht, kwywo, mapco, momqn...
461 fsofv, fzked, murat, vzmek, yrqjo
612 nmmey, tfjhv, ulwuj, xxaaq
374 bbthd, jvjwz, klcsq, mrakf, peztf, pixww, rtwdd
730 dlynf, egqei, hhckx, nsvdn, obnhh, rfbwh, ytgfi
458 eijdr, gtxhu, lhtqh, phprf, qjhcr, vqnos
655 bijer, fwlgk, nrcbm, sohho, trjtw, usjdj, uvpie...
837 ayxcv, epurf, flvtj, ftxcj, imjap, pmygd, sqhcc...

Last executed sql by spid

DECLARE @handle VARBINARY(64)
SELECT @handle = sql_handle from sys.sysprocesses where spid = 55
SELECT text FROM sys.dm_exec_sql_text(@handle)

Shrink tempdb

USE [tempdb]
GO
DBCC SHRINKDATABASE(N'tempdb' )
GO

Stored procedure for truncating SQL 2008 Logs and shrinking the database

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[uspShrinkDatabase]
as
DECLARE @LogFileName sysname
DECLARE @DataFileName sysname
DECLARE @CatalogName sysname
set @CatalogName = db_name()
SELECT
@LogFileName = rtrim([name])
from
sys.database_files
where
type_desc = 'LOG'
SELECT
@DataFileName = rtrim([name])
from
sys.database_files
where
type_desc = 'rows'
select
@LogFileName as LogFileName,
@DataFileName as DataFileName
Checkpoint;
DBCC SHRINKFILE(@LogFileName, 1);
DBCC SHRINKDATABASE(@CatalogName);

Find slowest sproc

http://www.databasejournal.com/features/mssql/article.php/3802936/Finding-the-Worst-Performing-T-SQL-Statements-on-an-Instance.htm


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[usp_Worst_TSQL]
/*
Written by: Gregory A. Larsen
Copyright 2008 Gregory A. Larsen. All rights reserved.
Name: usp_Worst_TSQL
Description: This stored procedure displays the top worst performing queries based on CPU, Execution Count,
I/O and Elapsed_Time as identified using DMV information. This can be display the worst
performing queries from an instance, or database perspective. The number of records shown,
the database, and the sort order are identified by passing pararmeters.
Parameters: There are three different parameters that can be passed to this procedures: @DBNAME, @COUNT
and @ORDERBY. The @DBNAME is used to constraint the output to a specific database. If
when calling this SP this parameter is set to a specific database name then only statements
that are associated with that database will be displayed. If the @DBNAME parameter is not set
then this SP will return rows associated with any database. The @COUNT parameter allows you
to control the number of rows returned by this SP. If this parameter is used then only the
TOP x rows, where x is equal to @COUNT will be returned, based on the @ORDERBY parameter.
The @ORDERBY parameter identifies the sort order of the rows returned in descending order.
This @ORDERBY parameters supports the following type: CPU, AE, TE, EC or AIO, TIO, ALR, TLR, ALW, TLW, APR, and TPR
where "ACPU" represents Average CPU Usage
"TCPU" represents Total CPU usage
"AE" represents Average Elapsed Time
"TE" represents Total Elapsed Time
"EC" represents Execution Count
"AIO" represents Average IOs
"TIO" represents Total IOs
"ALR" represents Average Logical Reads
"TLR" represents Total Logical Reads
"ALW" represents Average Logical Writes
"TLW" represents Total Logical Writes
"APR" represents Average Physical Reads
"TPR" represents Total Physical Read
Typical execution calls
Top 6 statements in the AdventureWorks database base on Average CPU Usage:
EXEC usp_Worst_TSQL @DBNAME='AdventureWorks',@COUNT=6,@ORDERBY='ACPU';
Top 100 statements order by Average IO
EXEC usp_Worst_TSQL @COUNT=100,@ORDERBY='ALR';
Show top all statements by Average IO
EXEC usp_Worst_TSQL;
*/
(@DBNAME VARCHAR(128) = ''
,@COUNT INT = 999999999
,@ORDERBY VARCHAR(4) = 'AIO')
AS
-- Check for valid @ORDERBY parameter
IF ((SELECT CASE WHEN
@ORDERBY in ('ACPU','TCPU','AE','TE','EC','AIO','TIO','ALR','TLR','ALW','TLW','APR','TPR')
THEN 1 ELSE 0 END) = 0)
BEGIN
-- abort if invalid @ORDERBY parameter entered
RAISERROR('@ORDERBY parameter not APCU, TCPU, AE, TE, EC, AIO, TIO, ALR, TLR, ALW, TLW, APR or TPR',11,1)
RETURN
END
SELECT TOP (@COUNT)
COALESCE(DB_NAME(st.dbid),
DB_NAME(CAST(pa.value AS INT))+'*',
'Resource') AS [Database Name]
-- find the offset of the actual statement being executed
,SUBSTRING(text,
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 + 1 END,
CASE WHEN statement_end_offset = 0
OR statement_end_offset = -1
OR statement_end_offset IS NULL
THEN LEN(text)
ELSE statement_end_offset/2 END -
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 END + 1
) AS [Statement]
,OBJECT_SCHEMA_NAME(st.objectid,dbid) [Schema Name]
,OBJECT_NAME(st.objectid,dbid) [Object Name]
,objtype [Cached Plan objtype]
,execution_count [Execution Count]
,(total_logical_reads + total_logical_writes + total_physical_reads )/execution_count [Average IOs]
,total_logical_reads + total_logical_writes + total_physical_reads [Total IOs]
,total_logical_reads/execution_count [Avg Logical Reads]
,total_logical_reads [Total Logical Reads]
,total_logical_writes/execution_count [Avg Logical Writes]
,total_logical_writes [Total Logical Writes]
,total_physical_reads/execution_count [Avg Physical Reads]
,total_physical_reads [Total Physical Reads]
,total_worker_time / execution_count [Avg CPU]
,total_worker_time [Total CPU]
,total_elapsed_time / execution_count [Avg Elapsed Time]
,total_elapsed_time [Total Elasped Time]
,last_execution_time [Last Execution Time]
FROM sys.dm_exec_query_stats qs
JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
OUTER APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE attribute = 'dbid' AND
CASE when @DBNAME = '' THEN ''
ELSE COALESCE(DB_NAME(st.dbid),
DB_NAME(CAST(pa.value AS INT)) + '*',
'Resource') END
IN (RTRIM(@DBNAME),RTRIM(@DBNAME) + '*')
ORDER BY CASE
WHEN @ORDERBY = 'ACPU' THEN total_worker_time / execution_count
WHEN @ORDERBY = 'TCPU' THEN total_worker_time
WHEN @ORDERBY = 'AE' THEN total_elapsed_time / execution_count
WHEN @ORDERBY = 'TE' THEN total_elapsed_time
WHEN @ORDERBY = 'EC' THEN execution_count
WHEN @ORDERBY = 'AIO' THEN (total_logical_reads + total_logical_writes + total_physical_reads) / execution_count
WHEN @ORDERBY = 'TIO' THEN total_logical_reads + total_logical_writes + total_physical_reads
WHEN @ORDERBY = 'ALR' THEN total_logical_reads / execution_count
WHEN @ORDERBY = 'TLR' THEN total_logical_reads
WHEN @ORDERBY = 'ALW' THEN total_logical_writes / execution_count
WHEN @ORDERBY = 'TLW' THEN total_logical_writes
WHEN @ORDERBY = 'APR' THEN total_physical_reads / execution_count
WHEN @ORDERBY = 'TPR' THEN total_physical_reads
END DESC

Remove carriage returns, tab, line feed from Excel text

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J12, CHAR(10), " "), CHAR(13), " "), CHAR(9), " "), " ", " "))

getting user account for security in MS SQL

select PARSENAME(REPLACE(SUSER_NAME(), '\', '.'), 1)

select SUSER_NAME()

Remote Server Administration Tools for Vista

http://www.trainsignaltraining.com/windows-vista-rsat/2008-04-03/

Installing Remote Server Administration Tools for Vista
There are two versions of the RSAT tool, one for 32-bit machines and one for 64-bit machines. You can download the version you need at their respective download locations:
Download: Remote Server Administration Tools (x86)
Download: Remote Server Administration Tools (x64)

http://www.microsoft.com/downloads/details.aspx?FamilyId=9FF6E897-23CE-4A36-B7FC-D52065DE9960&displaylang=en
http://www.microsoft.com/downloads/details.aspx?FamilyId=D647A60B-63FD-4AC5-9243-BD3C497D2BC5&displaylang=en

SQL Round to day

DATEADD(Day, DATEDIFF(Day, 0, DateValue), 0)