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