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 -------