SQL Server: How to Figure out Peak/Off-Peak Hours of Production Databases

Bubble Heatmp in SSRS

selecting data from Excel tables using VBA

'select an entire column (data only)
s.Range("Table1[Column2]").Select
'select an entire column (data plus header)
s.Range("Table1[[#All],[Column1]]").Select
'select entire data section of table
s.Range("Table1").Select
'select entire table
s.Range("Table1[#All]").Select

Misc queries

sp_who2
go
xp_cmdshell 'tasklist /FI "IMAGENAME eq Robocopy.exe"'
go
sp_MSforeachtable 'print ''select ''''?'''' as dbn, COUNT(*) cnt, CHECKSUM_AGG(checksum(*)) crc from db1.? (nolock) union all'''
go
sp_MSforeachtable 'print ''select ''''?'''' as dbn, COUNT(*) cnt, CHECKSUM_AGG(checksum(*)) crc from db2.? (nolock) union all'''

Returning the Top X row for each group by By Dave Ballantyne, 2010/12/06

IF OBJECT_ID('tempdb..#RunnersBig') IS NOT NULL drop table #RunnersBig 
go 
Create Table #RunnersBig 
( 
RunnerId integer identity , 
Time integer not null, 
Age integer not null 
) 
go 
insert into #runnersbig ( Time , Age ) 
select top 1000000 ABS ( checksum ( newid ()))% 1000 , 
ABS ( checksum ( newid ()))% 99 
from sys . columns a cross join sys . columns b cross join sys . columns c 
go 
create index idxrunnersbig on #runnersbig ( age , time ) include ( runnerid ) 

with cteN 
as 
( 
select number from master .. spt_values 
where type = 'p' and number between 0 and 100 
) 
Select * 
from cteN cross apply ( Select top ( 2 ) * from #RunnersBig where #RunnersBig . Age = cteN . number order by Time ) as runners 
order by cteN . number , runners . Time 

SQL finding Gaps and Islands

http://www.manning.com/nielsen/SampleChapter5.pdf
or
http://www.manning.com/nielsen/nielsenMEAP_freeCh5.pdf
or
http://www.sql.co.il/books/insidetsql2005/Inside%20Microsoft%20SQL%20Server%202005%20T-SQL%20Querying%20(0-7356-2313-9)%20-%20Chapter%2006%20-%20Aggregating%20and%20Pivoting%20Data.pdf

Rethrow SQL error

Not very interesting, but I use it all the time.

BEGIN TRY

 begin tran

    -- RAISERROR with severity 11-19 will cause execution to 
    -- jump to the CATCH block.
    RAISERROR ('Error raised in TRY block.', -- Message text.
               16, -- Severity.
               1 -- State.
               );
               
 commit tran
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

 rollback tran

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;

Concatenate rows into a single column

From web site http://blogs.technet.com/b/wardpond/archive/2008/03/13/database-programming-the-string-concatenation-xml-trick.aspx


create table Parent
(
ParentID INT
,ParentString VARCHAR(100)
)

INSERT Parent VALUES (1, 'Parent 1 String')
INSERT Parent VALUES (2, 'Parent 2 String')
INSERT Parent VALUES (3, 'Parent 3 String')


SELECT Parent.ParentString
FROM Parent

-- PERFORM THE TRICK
-- PIVOT Parent VALUES INTO 1 COLUMN FOR 1 BASE ROW
SELECT STUFF(( SELECT [text()]= ',' + ISNULL(Parent.ParentString, '') + ''
FROM Parent
ORDER BY Parent.ParentString
FOR XML PATH('')), 1,1, '') AS Parent_CSV


Undocumented SQL stored procedures


exec sp_help 'sp_checknames'
exec sp_help 'sp_columns_rowset'
exec sp_help 'sp_enumoledbdatasources'
exec sp_help 'sp_fixindex'
exec sp_help 'sp_gettypestring'
exec sp_help 'sp_MS_marksystemobject'
exec sp_help 'sp_MSaddguidcolumn'
exec sp_help 'sp_MSaddguidindex'
exec sp_help 'sp_MSaddlogin_implicit_ntlogin'
exec sp_help 'sp_MSadduser_implicit_ntlogin'
exec sp_help 'sp_MScheck_uid_owns_anything'
exec sp_help 'sp_MSdbuseraccess'
exec sp_help 'sp_MSdbuserpriv'
exec sp_help 'sp_msdependencies'
exec sp_help 'sp_MSdrop_object'
exec sp_help 'sp_MSforeachdb'
exec sp_help 'sp_MSforeachtable'
exec sp_help 'sp_MSget_qualified_name'
exec sp_help 'sp_MSgettools_path'
exec sp_help 'sp_MSgetversion'
exec sp_help 'sp_MSguidtostr'
exec sp_help 'sp_MShelpcolumns'
exec sp_help 'sp_MShelpindex'
exec sp_help 'sp_MShelptype'
exec sp_help 'sp_MSindexspace'
exec sp_help 'sp_MSis_pk_col'
exec sp_help 'sp_MSkilldb'
exec sp_help 'sp_MSloginmappings'
exec sp_help 'sp_MStablekeys'
exec sp_help 'sp_MStablerefs'
exec sp_help 'sp_MStablespace'
exec sp_help 'sp_MSunc_to_drive'
exec sp_help 'sp_MSuniquecolname'
exec sp_help 'sp_MSuniquename'
exec sp_help 'sp_MSuniqueobjectname'
exec sp_help 'sp_MSuniquetempname'
exec sp_help 'sp_tempdbspace'
exec sp_help 'sp_who2'
exec sp_help 'xp_delete_file'
exec sp_help 'xp_dirtree'
exec sp_help 'xp_enum_oledb_providers'
exec sp_help 'xp_enumcodepages'
exec sp_help 'xp_enumdsn'
exec sp_help 'xp_enumerrorlogs'
exec sp_help 'xp_enumgroups'
exec sp_help 'xp_fileexist'
exec sp_help 'xp_fixeddrives'
exec sp_help 'xp_get_MAPI_default_profile'
exec sp_help 'xp_get_MAPI_profiles'
exec sp_help 'xp_getnetname'
exec sp_help 'xp_qv'
exec sp_help 'xp_readerrorlog'
exec sp_help 'xp_regaddmultistring'
exec sp_help 'xp_regdeletekey'
exec sp_help 'xp_regdeletevalue'
exec sp_help 'xp_regenumvalues'
exec sp_help 'xp_regread'
exec sp_help 'xp_regremovemultistring'
exec sp_help 'xp_regwrite'
exec sp_help 'xp_subdirs'
exec sp_help 'xp_varbintohexstr'


Automatically add Foreign Keys

 
declare @code nvarchar(4000)
declare @template nvarchar(4000)
declare @PRIM_TABLE_SCHEMA sysname
declare @PRIM_TABLE_NAME sysname
declare @PRIM_COLUMN_NAME sysname
declare @CHILD_TABLE_SCHEMA sysname
declare @CHILD_TABLE_NAME sysname
declare @CHILD_COLUMN_NAME sysname
declare @rowid bigint
declare @maxrows bigint
declare @guid uniqueidentifier
 
set @template = 
'ALTER TABLE <<CHILD_TABLE_SCHEMA>>.<<CHILD_TABLE_NAME>> ADD CONSTRAINT
 FK_<<CHILD_TABLE_NAME>>_<<PRIM_TABLE_NAME>>_<<GUID>> FOREIGN KEY
 (
 <<CHILD_COLUMN_NAME>>
 ) REFERENCES <<PRIM_TABLE_SCHEMA>>.<<PRIM_TABLE_NAME>>
 (
 <<PRIM_COLUMN_NAME>>
 ) ON UPDATE  NO ACTION 
  ON DELETE  NO ACTION'
 
 
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
if OBJECT_ID('tempdb..#primkeys') is not null
 drop table #primkeys
 
 
select distinct 
 col.TABLE_SCHEMA
 ,col.TABLE_NAME
 ,col.COLUMN_NAME
into #primkeys
from 
 INFORMATION_SCHEMA.TABLES t
 JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS con
  on con.TABLE_SCHEMA = t.TABLE_SCHEMA 
   and con.TABLE_NAME = t.TABLE_NAME 
            AND con.CONSTRAINT_TYPE = 'PRIMARY KEY'
 join INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
  on con.CONSTRAINT_SCHEMA = u.CONSTRAINT_SCHEMA 
   and con.CONSTRAINT_NAME = u.CONSTRAINT_NAME 
 join INFORMATION_SCHEMA.COLUMNS col
  on u.TABLE_SCHEMA = col.TABLE_SCHEMA
   and u.TABLE_NAME = col.TABLE_NAME
   and u.COLUMN_NAME = col.COLUMN_NAME
where
 t.TABLE_TYPE = 'base table'
 and t.TABLE_NAME != 'sysdiagrams'
 
 
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
if OBJECT_ID('tempdb..#NonKeyColumns') is not null
 drop table #NonKeyColumns
 
 
select distinct 
 col.TABLE_SCHEMA
 ,col.TABLE_NAME
 ,col.COLUMN_NAME
into #NonKeyColumns
from 
 INFORMATION_SCHEMA.TABLES t
 join INFORMATION_SCHEMA.COLUMNS col
  on t.TABLE_SCHEMA = col.TABLE_SCHEMA
   and t.TABLE_NAME = col.TABLE_NAME
where
 t.TABLE_TYPE = 'base table'
 and t.TABLE_NAME != 'sysdiagrams'
 
except
 
select
 *
from
 #primkeys
 
 
 
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
if OBJECT_ID('tempdb..#colMappings') is not null
 drop table #colMappings
 
 
select
 ROW_NUMBER() over(order by p.TABLE_SCHEMA, p.TABLE_NAME, p.COLUMN_NAME,
  n.TABLE_SCHEMA, n.TABLE_NAME, n.COLUMN_NAME) as  RowID
 ,p.TABLE_SCHEMA as PRIM_TABLE_SCHEMA
 ,p.TABLE_NAME as PRIM_TABLE_NAME
 ,p.COLUMN_NAME as PRIM_COLUMN_NAME
 ,n.TABLE_SCHEMA as CHILD_TABLE_SCHEMA
 ,n.TABLE_NAME as CHILD_TABLE_NAME
 ,n.COLUMN_NAME as CHILD_COLUMN_NAME
into #colMappings
from
 #primkeys p
 join #NonKeyColumns n
  on (
   p.TABLE_SCHEMA != n.TABLE_SCHEMA
   or p.TABLE_NAME != n.TABLE_NAME
   )
   and p.COLUMN_NAME = n.COLUMN_NAME
 
 
 
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
select 
 @rowid = 1
 ,@maxrows = MAX(RowID)
from
 #colMappings
 
 
while (@rowid <= @maxrows)
begin
 
 select
  @PRIM_TABLE_SCHEMA = PRIM_TABLE_SCHEMA
  ,@PRIM_TABLE_NAME = PRIM_TABLE_NAME
  ,@PRIM_COLUMN_NAME = PRIM_COLUMN_NAME
  ,@CHILD_TABLE_SCHEMA = CHILD_TABLE_SCHEMA
  ,@CHILD_TABLE_NAME = CHILD_TABLE_NAME
  ,@CHILD_COLUMN_NAME = CHILD_COLUMN_NAME
  ,@guid = NEWID()
 from
  #colMappings m
 where
  m.RowID = @rowid
 
 
 set @code = replace(@template, '<<PRIM_TABLE_SCHEMA>>', @PRIM_TABLE_SCHEMA)
 set @code = replace(@code, '<<PRIM_TABLE_NAME>>', @PRIM_TABLE_NAME)
 set @code = replace(@code, '<<PRIM_COLUMN_NAME>>', @PRIM_COLUMN_NAME)
 set @code = replace(@code, '<<CHILD_TABLE_SCHEMA>>', @CHILD_TABLE_SCHEMA)
 set @code = replace(@code, '<<CHILD_TABLE_NAME>>', @CHILD_TABLE_NAME)
 set @code = replace(@code, '<<CHILD_COLUMN_NAME>>', @CHILD_COLUMN_NAME)
 set @code = replace(@code, '<<GUID>>', replace(@guid, '-', ''))
 
 
 print @code
 
 --exec sp_executesql @code
 
 set @rowid = @rowid +1
end
 
 

WatiN Web Test and Web Automation tool

WatiN is an awesome web test and web automation tool. I've been using it to automate the montly uploading of data to a site. This was the best IE solution I could find, and it is fantastic. Selenium & Firefox are another great tool combination. I really like Selenium but the coding of the web site wouldn't work with Firefox.

http://watin.sourceforge.net/


In case WatiN doesn't work with a particular dialog, be sure to check out the methods CanHandleDialog(Window window). WatiN did not originally work with FileUpload element of a particular web site. It worked with several other web site using various FileUpload elements. The solution was to modify the method FileUploadDialogHandler.CanHandleDialog(Window window).

  public override bool CanHandleDialog(Window window)
  {
            return (window.StyleInHex == "96CC20C4") || (window.StyleInHex == "96CC02C4")
                || (window.StyleInHex == "97CC02C4") || (window.StyleInHex == "97CC02C4");
  }

For some reason the style for the upload file dialog was different. I'm not sure if this was caused by a newer browser version, web site, or Windows 7. But once I added the new styles it worked fine.

In summary an awesome tool!

Uninstall applications through the registry

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.CreateTextFile("UninstallAppList.tsv", True)
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")

objTextFile.WriteLine "DisplayName" & vbtab & _
    "InstallDate" & vbtab & "ProdId" & vbtab & _
    "Publisher" & vbtab & "Version" & vbtab & "32/64Bits"

'add a where clause
Set colSoftware = objWMIService.ExecQuery("Select * from Win32Reg_AddRemovePrograms")
For Each objSoftware in colSoftware
    objTextFile.WriteLine objSoftware.DisplayName & vbtab & _
        objSoftware.InstallDate & vbtab & _
        objSoftware.ProdId & vbtab & _
        objSoftware.Publisher & vbtab & _
        objSoftware.Version & vbtab & _
        "32"
Next

Set colSoftware = objWMIService.ExecQuery("Select * from Win32Reg_AddRemovePrograms64")
For Each objSoftware in colSoftware
    objTextFile.WriteLine objSoftware.DisplayName & vbtab & _
        objSoftware.InstallDate & vbtab & _
        objSoftware.ProdId & vbtab & _
        objSoftware.Publisher & vbtab & _
        objSoftware.Version & vbtab & _
        "64"
Next


objTextFile.Close

'Use this code to uninstall the application
'Set objShell = CreateObject("Wscript.Shell")
'For Each objSoftware in colSoftware
'    objShell.Run objShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\" & objSoftware.ProdId & "\UninstallString"), 1, true
'Next

Refresh Excel file using SSIS

copied from Jessica Moss' blog, in case that blog is ever offline.

http://jessicammoss.blogspot.com/2008/10/manipulating-excel-spreadsheets-in-ssis.html

Next, create a script task in your SSIS package that contains the following code (include your spreadsheet name):

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop.Excel

Public Class ScriptMain

Public Sub Main()
Dts.TaskResult = Dts.Results.Success

Dim excel As New Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook

wb = excel.Workbooks.Open("C:\\TestExcelSS.xlsx")
wb.RefreshAll()
wb.Save()
wb.Close()

excel.Quit()
Runtime.InteropServices.Marshal.ReleaseComObject(excel)

End Sub

End Class

You'll see error squiggles, but don't worry about them because they will disappear in just a minute. Save and close your package. In your Solution Explorer, right click on the package and select ‘View Code’.

In the resulting XML, change the Build Settings ReferencePath property to:
"C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\9.0.242.0__89845dcd8080cc91\;C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91\;C:\Windows\assembly\GAC\Microsoft.Office.Interop.Excel\12.0.0.0__71e9bce111e9429c\"

Also change the Build References to include:
Name = "Microsoft.Office.Interop.Excel"
AssemblyName = "Microsoft.Office.Interop.Excel"
/>

Save the XML, and reopen the package. Open the script task and select ‘Save’. This will compile the code, and now you can run your package.

When working with COM references, you can use the script task GUI to add the reference by adding the desired component to the .NET framework folder. I could not find Microsoft.Office.Interop.Excel.dll on my machine to move to the framework folder, which is why we added the reference through the XML.

As Douglas Laudenschlager notes, writing server-side code to access client-side Office is unsupported. Please take these possible problems under advisement and code as necessary. You have been warned. :)

Update (11/12/08): Added last two lines to code to stop Excel process.

Versions: Microsoft Office 2007, SQL Server 2005 SP2

Posted by Jessica M. Moss at 1:11 AM

Download excel file from SSRS using RS

rs -i RSDownloadFile.rss -s http://localhost/reportserver

-------->8 RSDownloadFile.rss 8< --------

Public Sub Main()
Dim fileName as String = "Refreshable_Pivot.xlsx"
Dim strResourcePath as String = "/TestFolder/Refreshable_Pivot.xlsx"

'Dim rs As New ReportingService
Dim myByteArray() As Byte
myByteArray = rs.GetResourceContents(strResourcePath, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")

' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(myByteArray, 0, myByteArray.Length)
stream.Close()
End Sub

uspGenerateSQLAgentJobScheduleReport

create PROC [dbo].[uspGenerateSQLAgentJobScheduleReportWithDuration]
as
/*
Original code by Dr DBA - whomever that is
http://blogs.mssqltips.com/forums/t/977.aspx

modified by Carlos Klapp
*/


select
'Server' = left(@@ServerName,20),
'JobName' = left(S.name,30),
'ScheduleName' = left(ss.name,25),
'Enabled' = CASE (S.enabled)
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE '??'
END,
'Frequency' = CASE(ss.freq_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN
(case when (ss.freq_recurrence_factor > 1)
then 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Weeks' else 'Weekly' end)
WHEN 16 THEN
(case when (ss.freq_recurrence_factor > 1)
then 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' else 'Monthly' end)
WHEN 32 THEN 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' -- RELATIVE
WHEN 64 THEN 'SQL Startup'
WHEN 128 THEN 'SQL Idle'
ELSE '??'
END,
'Interval' = CASE
WHEN (freq_type = 1) then 'One time only'
WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day'
WHEN (freq_type = 4 and freq_interval > 1) then 'Every ' + convert(varchar(10),freq_interval) + ' Days'
WHEN (freq_type = 8) then (select 'Weekly Schedule' = D1+ D2+D3+D4+D5+D6+D7
from (select ss.schedule_id,
freq_interval,
'D1' = CASE WHEN (freq_interval & 1 <> 0) then 'Sun ' ELSE '' END,
'D2' = CASE WHEN (freq_interval & 2 <> 0) then 'Mon ' ELSE '' END,
'D3' = CASE WHEN (freq_interval & 4 <> 0) then 'Tue ' ELSE '' END,
'D4' = CASE WHEN (freq_interval & 8 <> 0) then 'Wed ' ELSE '' END,
'D5' = CASE WHEN (freq_interval & 16 <> 0) then 'Thu ' ELSE '' END,
'D6' = CASE WHEN (freq_interval & 32 <> 0) then 'Fri ' ELSE '' END,
'D7' = CASE WHEN (freq_interval & 64 <> 0) then 'Sat ' ELSE '' END
from msdb..sysschedules ss
where freq_type = 8
) as F
where schedule_id = sj.schedule_id
)
WHEN (freq_type = 16) then 'Day ' + convert(varchar(2),freq_interval)
WHEN (freq_type = 32) then (select freq_rel + WDAY
from (select ss.schedule_id,
'freq_rel' = CASE(freq_relative_interval)
WHEN 1 then 'First'
WHEN 2 then 'Second'
WHEN 4 then 'Third'
WHEN 8 then 'Fourth'
WHEN 16 then 'Last'
ELSE '??'
END,
'WDAY' = CASE (freq_interval)
WHEN 1 then ' Sun'
WHEN 2 then ' Mon'
WHEN 3 then ' Tue'
WHEN 4 then ' Wed'
WHEN 5 then ' Thu'
WHEN 6 then ' Fri'
WHEN 7 then ' Sat'
WHEN 8 then ' Day'
WHEN 9 then ' Weekday'
WHEN 10 then ' Weekend'
ELSE '??'
END
from msdb..sysschedules ss
where ss.freq_type = 32
) as WS
where WS.schedule_id =ss.schedule_id
)
END,
'Time' = CASE (freq_subday_type)
WHEN 1 then left(stuff((stuff((replicate('0', 6 - len(Active_Start_Time)))+ convert(varchar(6),Active_Start_Time),3,0,':')),6,0,':'),8)
WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds'
WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes'
WHEN 8 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' hours'
ELSE '??'
END,

'Next Run Time' = CASE SJ.next_run_date
WHEN 0 THEN cast('n/a' as char(10))
ELSE convert(char(10), convert(datetime, convert(char(8),SJ.next_run_date)),120) + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)
END,
left(qDuration.run_date, 4) + '-' + SUBSTRING(cast(qDuration.run_date as CHAR(8)), 5, 2) + '-' + RIGHT(qDuration.run_date, 2) as run_date,
qDuration.Duration,
qDuration.JobExitStatus
from
msdb.dbo.sysjobschedules SJ
join msdb.dbo.sysjobs S
on S.job_id = SJ.job_id
join msdb.dbo.sysschedules SS
on ss.schedule_id = sj.schedule_id
left join (
select
ROW_NUMBER() over(partition by q.job_id order by q.job_id, q.run_date) as RowId
,q.job_id
,q.run_date
,q.JobExitStatus
,right('0' + rtrim(convert(char(2), q.run_duration_sec / (60 * 60))), 2) + ':' +
right('0' + rtrim(convert(char(2), (q.run_duration_sec / 60) % 60)), 2) + ':' +
right('0' + rtrim(convert(char(2), q.run_duration_sec % 60)),2) as Duration
from
(
select
SH.job_id
,SH.run_date
,case SH.run_status
when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
when 4 then 'In progress'
end as JobExitStatus
,(
(SH.run_duration / 10000) *60*60 + -- hours
((SH.run_duration / 100) % 100) *60 + -- minutes
SH.run_duration % 100 -- seconds
) as run_duration_sec
from
msdb.dbo.sysjobhistory SH
where
SH.step_id = 0
) q
) qDuration
on S.job_id = qDuration.job_id
where
qDuration.RowId between 1 and 10
order by
S.name
,qDuration.run_date desc
,qDuration.Duration




GO

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)

BGINFO - display confirgutaion properties on desktop

 http://technet.microsoft.com/en-us/sysinternals/bb897557.aspx

Vista and Windows Server 2008 the "All Users" folder

Vista and Windows Server 2008 the "All Users" folder is at the following path

C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Startup

List all the columns in the all the tables in all the databases

List all the columns in all the databases.


/*

CREATE TABLE #TmpColumns(
[TABLE_CATALOG] [nvarchar](128) NULL,
[TABLE_SCHEMA] [nvarchar](128) NULL,
[TABLE_NAME] [sysname] NOT NULL,
[COLUMN_NAME] [sysname] NULL,
[ORDINAL_POSITION] [int] NULL,
[COLUMN_DEFAULT] [nvarchar](4000) NULL,
[IS_NULLABLE] [varchar](3) NULL,
[DATA_TYPE] [nvarchar](128) NULL,
[CHARACTER_MAXIMUM_LENGTH] [int] NULL,
[CHARACTER_OCTET_LENGTH] [int] NULL,
[NUMERIC_PRECISION] [tinyint] NULL,
[NUMERIC_PRECISION_RADIX] [smallint] NULL,
[NUMERIC_SCALE] [int] NULL,
[DATETIME_PRECISION] [smallint] NULL,
[CHARACTER_SET_CATALOG] [sysname] NULL,
[CHARACTER_SET_SCHEMA] [sysname] NULL,
[CHARACTER_SET_NAME] [sysname] NULL,
[COLLATION_CATALOG] [sysname] NULL,
[COLLATION_SCHEMA] [sysname] NULL,
[COLLATION_NAME] [sysname] NULL,
[DOMAIN_CATALOG] [sysname] NULL,
[DOMAIN_SCHEMA] [sysname] NULL,
[DOMAIN_NAME] [sysname] NULL
) ON [PRIMARY]

insert into #TmpColumns
exec dbo.uspSearchAllColumnNames
@Database = null,
@Schema = null,
@Table = null,
@Column = '%office%'

select
*
from
#TmpColumns
*/

CREATE PROC [dbo].[uspSearchAllColumnNames](
@Database sysname = null,
@Schema sysname = null,
@Table sysname = null,
@Column sysname = null
)
as

DECLARE @Template NVARCHAR(2000)
DECLARE @Sql NVARCHAR(2000)
DECLARE @Catalog sysname
DECLARE @RowId int
DECLARE @MaxCnt int
SELECT
*
INTO #Columns
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
1=0
SET @Template = '
INSERT INTO #Columns
SELECT
*
FROM
[<>].INFORMATION_SCHEMA.COLUMNS
'
SELECT
IDENTITY(INT,1,1) AS RowID,
[Name]
INTO #DBNames
FROM
sys.databases
WHERE
owner_sid <> 0x01
SELECT
@RowId = 1,
@MaxCnt = COUNT(*)
FROM
#DBNames
WHILE (@RowId <= @MaxCnt)
BEGIN
SELECT
@Catalog = [Name]
FROM
#DBNames
WHERE
@RowId = RowId
SET @Sql = REPLACE(@Template, '<>', @Catalog)
EXEC sp_executesql @Sql
SET @RowId = @RowId +1
end


/*
--debugging
declare
@Database sysname,
@Schema sysname,
@Table sysname,
@Column sysname
SET @Column = '%vista%'
*/
SELECT
*
FROM
#Columns
where
(@Database IS NULL OR TABLE_CATALOG LIKE @Database)
AND (@Schema IS NULL OR TABLE_SCHEMA LIKE @Schema)
AND (@Table IS NULL OR TABLE_NAME LIKE @Table)
AND (@Column IS NULL OR COLUMN_NAME LIKE @Column)
ORDER BY
TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME


DROP TABLE #Columns
DROP TABLE #DBNames

Finding the largest table in a database

A good article with SQL script for calculating the largest SQL tables inside a database including the size occupied by the indexes:
http://www.sqlteam.com/article/finding-the-biggest-tables-in-a-database



/**************************************************************************************
*
* BigTables.sql
* Bill Graziano (SQLTeam.com)
* graz@sqlteam.com
* v1.1
*
**************************************************************************************/
declare @id int
declare @type character(2)
declare @pages int
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
create table #spt_space
(
objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
set nocount on
-- Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = 'U'
open c_tables
fetch next from c_tables
into @id
while @@fetch_status = 0
begin
/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
where objid = @id
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
where objid = @id
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
where objid = @id
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
and objid = @id
fetch next from c_tables
into @id
end
select top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
data_KB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc
drop table #spt_space
close c_tables
deallocate c_tables

Search for a text string in a database's T-SQL

I believe the original author is Scott Burnell.


declare @textToSearchFor nvarchar(200)
declare @objectTypeToSearch char(1)
set @textToSearchFor = 'dimManagerCode'
-- use this as a sproc parameter
set @objectTypeToSearch = 'a'
-- use this as a sproc parameter (t=table,p=procedure,v=views,f=functions,a=all)
-- procedures
if (@objectTypeToSearch in ('a', 'p'))
begin
select
isnull('[' + s.[name] + '].[', '[') + p.[name] + ']' as ProcName --objectName.'
from
sys.procedures p
inner join sys.sql_modules m
on p.object_id = m.object_id
left join sys.all_objects obj
on p.object_id = obj.object_id
left join sys.schemas s
on obj.schema_id = s.schema_id
where
m.definition like '%' + @textToSearchFor + '%'
order by
ProcName
end
-- tables
if (@objectTypeToSearch in ('a', 't'))
begin
select
'[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' as TableName --objectName
from
INFORMATION_SCHEMA.COLUMNS c
where
c.COLUMN_NAME like '%' + @textToSearchFor + '%'
order by
TableName
end
-- views
if (@objectTypeToSearch in ('a', 'v'))
begin
select
isnull('[' + s.[name] + '].[', '[') + p.[name] + ']' as ViewNameDefinition --objectName
from
sys.views p
inner join sys.sql_modules m
on p.object_id = m.object_id
left join sys.all_objects obj
on p.object_id = obj.object_id
left join sys.schemas s
on obj.schema_id = s.schema_id
where
m.definition like N'%' + @textToSearchFor + N'%'
select
isnull('[' + s.[name] + '].[', '[') + obj.[name] + ']' as ViewNameOutputColumn --objectName
from
sys.all_columns c
inner join sys.objects obj
on c.object_id = obj.object_id
left join sys.schemas s
on obj.schema_id = s.schema_id
where
obj.[type] = 'v'
and c.[name] like '%' + @textToSearchFor + '%'
select distinct
'[' + VIEW_SCHEMA + '].[' + VIEW_NAME + ']' as ViewName,
'[' + TABLE_SCHEMA + '].[' + TABLE_NAME + '].[' + COLUMN_NAME + ']'as UsesTableColumn
from
INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
where
COLUMN_NAME like '%' + @textToSearchFor + '%'
order by
ViewName
end
-- functions
if (@objectTypeToSearch in ('a', 'f'))
begin
select
isnull('[' + s.[name] + '].[', '[') + obj.[name] + ']' as FunctionName --objectName
from
sys.objects obj
inner join sys.sql_modules m
on obj.object_id = m.object_id
left join sys.schemas s
on obj.schema_id = s.schema_id
where
obj.[type] = 'FN'
and m.definition like '%' + @textToSearchFor + '%'
order by
FunctionName
end

Stored procedure for truncating SQL 2005 Logs and shrinking the database

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;
Backup LOG @CatalogName with Truncate_Only;
DBCC SHRINKFILE(@LogFileName, 0, TRUNCATEONLY);
DBCC SHRINKDATABASE(@CatalogName);

SQL split function by Karen Gayda

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

create FUNCTION [dbo].[fnSplit]
( @vcDelimitedString varchar(8000),
@vcDelimiter varchar(100) )
RETURNS @tblArray TABLE
(
ElementID smallint IDENTITY(1,1), --Array index
Element varchar(1000) --Array element contents
)
AS
/**************************************************************************
DESCRIPTION: Accepts a delimited string and splits it at the specified
delimiter points. Returns the individual items as a table data
type with the ElementID field as the array index and the Element
field as the data

PARAMETERS:
@vcDelimitedString - The string to be split
@vcDelimiter - String containing the delimiter where
delimited string should be split

RETURNS:
Table data type containing array of strings that were split with
the delimiters removed from the source string

USAGE:
SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID

AUTHOR: Karen Gayda

DATE: 05/31/2001

MODIFICATION HISTORY:
WHO DATE DESCRIPTION
--- ---------- ---------------------------------------------------

***************************************************************************/
BEGIN

DECLARE
@siIndex smallint,
@siStart smallint,
@siDelSize smallint


SET @siDelSize = LEN(@vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@vcDelimitedString) > 0
BEGIN
SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
IF @siIndex = 0
BEGIN
INSERT INTO @tblArray VALUES(@vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
SET @siStart = @siIndex + @siDelSize
SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
END
END

RETURN
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

Dynamic Sort Order In T-SQL

create proc GetEmployees
@SortField nvarchar(20)
as

select * from Employees
order by
case @SortField
when 'FirstName' then cast (FirstName as sql_variant)
when 'LastName' then cast (LastName as sql_variant)
when 'HireDate' then cast (HireDate as sql_variant)
else cast (EmployeeID as sql_variant)
end

Very clever. Originally found at http://www.angrycoder.com/article.aspx?ArticleID=131 "Dynamic Sort Order In T-SQL"

A comment states:
There are two problems (at least) with the code. First, it will not use indexes and thus be fairly slow. And second, it will sort numerical columns alphabeticaly. That is 1, 11, 2, 3, 35, 4, ... Usually not exactly what you wanted.While you might solve the second problem by prepending enough zeroes or spaces or something it would only make the first problem worse. It's SLOW!Sorry, there is no nice solution. There can't really be as the order column can totally change the optimal execution plan. And you can't (so far) have several execution plans for a single query. So ... it's anoying, but the only performance effective solution is to repeat the query as many times as you have the different orders and choose the right one by a row of IF statements :-(

Reading an Excel 2007 file from SSIS 2005

Since SSIS uses ODBC to read Excel 2007 files, changing the ODBC connection string to the following:

Data Source=D:\Test.xlsx;Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 8.0;

Data Source=D:\Test.xlsx;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0


To read an xslm file, I think you need Extended Properties="Excel 12.0
Macro". Similarly, to read an xslx file, you will need "Excel 12.0 Xml".

See the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\ISAM Formats\ for the various providors.

Error 1004 when setting the VisibleItemsList in Excel 2007

If you are receiving the error 1004 while setting the filters for a pivot table in Excel 2007, you need to add "pivfld.CubeField.EnableMultiplePageItems = True" this line to your code.

dim rgstrMonthNames() as variant
'Dynamically build string array from a list....
rgstrMonthNames() = Array("Jan 2007", "Jan 2006")

For Each ws In Worksheets
For Each pt In ws.PivotTables
Set pivfld = pt.PivotFields("[Dim Date].[MonthNames].[MonthNames]") pivfld.CubeField.ClearManualFilter
pivfld.CubeField.EnableMultiplePageItems = True
pivfld.VisibleItemsList = rgstrMonthNames()
End If
Next
Next

ActiveWorkbook.RefreshAll

Setting chart title in Excel 2007

Worksheets(0).ChartObjects(0).Chart.ChartTitle.Text = "new title"

Size and row count of tables in a SQL server

The code creates a temp tables with the total number of rows and size in KB for all the tables in the current SQL server catalog.


declare @TableName nvarchar(1000)

begin try
drop table #TblSpace
end try
begin catch
end catch

create table #TblSpace
(
[name] nvarchar(1000),
[rows] bigint,
reserved nvarchar(1000),
data nvarchar(1000),
index_size nvarchar(1000),
unused nvarchar(1000)
)

declare cur cursor for
select
cast('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' as nvarchar(1000)) as TableName
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE = 'BASE TABLE'

open cur

fetch next from cur into @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

insert into #TblSpace
EXEC sp_spaceused @TableName

fetch next from cur into @TableName
end

close cur
deallocate cur

select * from #TblSpace order by [rows] desc

Using SQL to iterate through all the columns in a table

I use this code in ETL projects where almost all of the columns need to be cleaned, and it is easier to execte the same SQL code over all the columns in all the imported tables, rather than write custom code for each column in the ETL package (SSIS in my case).

declare @TableName as varchar(2000)
declare @ColName as varchar(2000)
declare @Sql as nvarchar(4000)

declare curColNames cursor for
select distinct
'[' + C.TABLE_SCHEMA + '].[' + C.TABLE_NAME + ']' as TableName
,'[' + C.COLUMN_NAME + ']' as ColName
from
INFORMATION_SCHEMA.COLUMNS C
join INFORMATION_SCHEMA.TABLES T
on C.TABLE_SCHEMA = T.TABLE_SCHEMA
and C.TABLE_NAME = T.TABLE_NAME
where
T.TABLE_TYPE = 'BASE TABLE'
order by
TableName
,ColName


open curColNames

fetch next from curColNames
into @TableName, @ColName

WHILE @@FETCH_STATUS = 0
BEGIN
set @Sql = 'update ' + @TableName + ' set ' + @ColName + '= ltrim(rtrim(' + @ColName + '))'

exec sp_executesql @Sql

fetch next from curColNames
into @TableName, @ColName
end

close curColNames
deallocate curColNames

Macro for setting a pivot table filter using Excel 2007

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Dim Geography WW Physical].[Physical Sales Location Name].[Physical Sales Location Name]").ClearAllFilters

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Dim Geography WW Physical].[Physical Sales Location Name].[Physical Sales Location Name]").CurrentPageName = "[Dim Geography WW Physical].[Physical Sales Location Name].&[United States]"

Clearing a SSAS cube cache

Snippet from:
http://geekswithblogs.net/darrengosbell/archive/2007/08/30/SSAS-Query-Performance-Tuning-Whitepaper.aspx

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ClearCache>
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
</Object>
</ClearCache>
</Batch>

GO

SELECT {} ON 0 FROM [Adventure Works]

Disabling alerts in Excel

Disabling alerts in Excel when deleting worksheets in macros.

Application.DisplayAlerts = False
Worksheets("Lists").Delete
Application.DisplayAlerts = True

GUID utilities suitable for use in Excel

I briefly played with this set of utilities, and I'm very pleased with it.

http://www.trigeminal.com/code/guids.bas

Useful article on SQL CLR security

Security in the CLR World Inside SQL Server
http://www.code-magazine.com/article.aspx?quickid=0603031&page=1

Free icons for use in personal and commercial ASP.NET web sites

http://www.aspneticons.com/

Also check out:
http://www.openclipart.org/

"select top 100 percent ... from ... order by ..." not guaranteed to work inside of views

I've recently run across a SQL view attempting to specify the sort order inside a view using "select top 100 percent ... from ... order by ...". Unfortunately it is not guaranteed to work.

See this article for an explanation:
http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

HttpRedirection module problems with Reporting Services 2005

I tried using Mike Volodarsky's HttpRedirection module (http://mvolo.com/blogs/serverside/archive/2007/05/24/Redirect-clients-in-your-application-with-HttpRedirection-module.aspx) with the default IIS web site, which includes Reporting Services 2005. The module worked great, but caused an error when I tried to access reporting services. I recommend sticking with IIRF (http://www.codeplex.com/IIRF/), which worked flawlessly for me, and was compatible with Reporting Services 2005.

Definitely check out Mike's site for useful IIS information.

SSIS import problems - imported rows returning NULLs

When importing data from an Excel workbook using SSIS, some values may be returned as NULL. This is caused by the OLEDB driver believing some columns have multiple data types. Use the IMEX flag to work around this problem.

http://support.microsoft.com/kb/189897/EN-US/
http://support.microsoft.com/default.aspx/kb/194124


change this
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data_200708.xls;Extended Properties="EXCEL 8.0;HDR=YES";

to

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data_200708.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";

Securely wiping a hard disk

http://dban.sourceforge.net/

Online Bussiness Intelligence Courses

I'm currently enrolled in this program, and I recommend it for those interested in Bussiness Intelligence:
http://bellevuecollege.edu/catalog/degrees/busit/

Loading and Running a Remote Package Programmatically

Loading and Running a Remote Package Programmatically
http://msdn2.microsoft.com/en-us/library/ms403355.aspx

SQL Tools

http://www.apexsql.com
http://www.aquafold.com/index.html
http://www.bestsofttool.com/
http://www.dbsolo.com/schema_comparison.html
http://www.devlib.net/decryptsql.htm
http://www.Embarcadero.com
http://www.orafaq.com/tools/dkg/dbdiff.htm
http://www.red-gate.com/ (there is a trial version that can be downloaded)
http://www.schematodoc.com
http://www.sleepyant.com/index.php
http://www.sqlaccessories.com/
http://www.sqledit.com/dcmp/
http://www.sqlmanager.net/products/postgresql/manager
http://www.synametrics.com/SynametricsWebApp/WinSQLFeatures.jsp
http://www.teratrax.com/sql_compare
http://www.xsqlsoftware.com/LiteEdition.aspx

Redirecting HTTP to HTTPS

I recommend using the IIRF isapi filter for performing HTTP to HTTPS redirects. You can find the project at http://www.codeplex.com/IIRF/. I've successfully used this isapi filter for some of my projects.

The syntax of IIRF is very similar to the mod_rewrite for Apache. Googling for "RewriteRule AND https" retrieved some valuable samples and tutorials:

http://www.whoopis.com/howtos/apache-rewrite.html

http://wiki.apache.org/httpd/RewriteHTTPToHTTPS

http://www.karkomaonline.com/article.php/2005080614195334

I'm sure there are more terse ways to write the regular expressions. However, this should provide a good start.

Make sure the folder exists and set logging to max while debugging.
------- 8< ------- >8 -------
RewriteLog c:\temp\iirfLog.out
RewriteLogLevel 5
------- 8< ------- >8 -------

These are the rules I use on our dev box called "PEER". Some of the apps require the full server and domain name. These sets of rules expand the name from "PEER" to the complete domain "peer.dev.us.company.com". Since I'm not an expert on regex I use the logical OR to concatenate the rules.
------- 8< ------- >8 -------
RewriteCond %{HTTPS} on
#RewriteCond %{SERVER_PORT} ^443$
RewriteCond %{HTTP_HOST} ^peer$ I,OR
RewriteCond %{HTTP_HOST} ^peer\:0-9*$ I,OR
RewriteCond %{HTTP_HOST} ^peer.dev.us.company.com\:0-9*$ I
RewriteRule ^/(.*)$ https://peer.dev.us.company.com/$1 R
------- 8< ------- >8 -------

#tests to see if the connection is already HTTPS
RewriteCond %{HTTPS} on

#since we only have one https site, I ignore this. Useful for more than one site.
#RewriteCond %{SERVER_PORT} ^443$

#was the server typed in as just "PEER".
#I - ignore case
#OR - logical OR with the rule below
RewriteCond %{HTTP_HOST} ^peer$ I,OR

#was the server typed in as "PEER" plus some port?
#I - ignore case
#OR - logical OR with the rule below
RewriteCond %{HTTP_HOST} ^peer\:0-9*$ I,OR

#was the server typed in as "peer.dev.us.company.com" plus some port?
#I - ignore case
#OR - logical OR with the rule below
RewriteCond %{HTTP_HOST} ^peer.dev.us.company.com\:0-9*$ I

#Redirect to the https site with the fully qualified name.
#Keep the path the user specified.
#So if a user types in "http://peer/reports" they are
#redirected to "http://peer.dev.us.company.com/reports"
RewriteRule ^/(.*)$ https://peer.dev.us.company.com/$1 R




This rule redirects all port 80 requests to the https site. I've seen the rule written as "^!443$", which means all connections on ports other than 443 are redirected to the https site. I used port 80, because we have test sites on other ports which don't require SSL.
------- 8< ------- >8 -------
RewriteCond %{SERVER_PORT} ^80$
RewriteRule ^/(.*)$ https://www.company.com/$1 R
------- 8< ------- >8 -------