Get object dependencies in SQL Server

Get object dependencies of a sproc via script instead of Management Studio:

DECLARE @my_object nvarchar(1024) = N'dbo.my_sproc'
CREATE TABLE #tempTable (name nvarchar(1024), [type] nvarchar(1024), updated nvarchar(1024), selected nvarchar(1024), [column] nvarchar(1024))
INSERT INTO #tempTable EXEC sp_depends @objname = @my_object
SELECT name FROM #tempTable group by name
DROP TABLE #tempTable
GO

Also useful:

SELECT * FROM sys.Tables

http://blog.sqlauthority.com/2009/09/23/sql-server-insert-values-of-stored-procedure-in-table-use-table-valued-function/

Increasing heap for Weka

Any OS
From command/terminal:
java -Xmx1024m -jar weka.jar

OSX gui
From terminal:
cd /Applications/weka-3-5-7.app/Contents
open Info.plist
Under  "Java" you will find an entry called "VMOptions" with the value "-Xmx256M".

Windows gui
Edit the line maxheap=1536m from C:\Program Files\Weka-3-X\RunWeka.ini

SQL to concatenate many rows into a single string

I used this SQL script to convert multiple returned rows into a single text string in SQL Server 2008.
SELECT REPLACE(
(SELECT CASE TBL.DayNumber
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'
ELSE NULL
END AS 'data()' 
FROM [dbo].[MyTableName] TBL
FOR XML PATH (''))
, ' ', ',') AS CommaDelimDayNames

This will convert:
Id          DayNumber
----------- --------------------
77          4
77          5
77          6

into:
Id          CommaDelimDayNames
----------- --------------------
77          Wed,Thu,Fri


As usual I had inspiration from the wonderful stackoverflow, in particular Diwakar's answer:
http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string/5580166#5580166
This has the answer:

REPLACE((select FName AS 'data()'  from NameList  for xml path('')), ' ', ', ')

My hg/mercurial settings

My .hgignore for Visual Studio projects:


# use glob syntax
syntax: glob


# Ignore Visual Studio files
*.obj
*.exe
*.pdb
*.user
*.vspscc
*.bak
*.cache
*.ilk
*.log
*.sbr
*.scc
[Bb]in
[Dd]ebug*/
obj/
[Rr]elease*/
_ReSharper*/
[Tt]est[Rr]esult*
[Bb]uild[Ll]og.*
*.[Pp]ublish.xml


#*.suo # depending on public/private repo

SQL Server PIVOT and UNPIVOT to transpose a table

I had to rotate/transpose a SQL table variable for reporting purposes and eventually got SQL Server 2008 PIVOT and UNPIVOT operators to achieve this (rather that using a longer UNION ALL approach.)

Source table definition:
DECLARE @ResultTable TABLE
(
Ordinal INT,
Calc1 FLOAT,
Calc2 FLOAT,
Calc3 FLOAT
)

SELECT * FROM @ResultTable:
1 | 1000000 | 0.438556290234799 | 3.53674427608709
2 | 0 | 0.877112580469597 | 7.07348855217417
3 | 1000000 | 0.877112580469597 | 7.07348855217417
4 | 0 | 1.3156688707044 | 10.6102328282613
5 | 1000000 | 1.3156688707044 | 10.6102328282613
6 | 0 | 0 | 0

SQL to rotate/transpose rows to columns:
;WITH CTE AS 
  (
    SELECT Ordinal, Col1 as SeriesName, Col as Value FROM (SELECT * FROM @ResultTable) P
      UNPIVOT (Col FOR Col1 IN (
        Calc1, Calc2, Calc3 -- Series to return as rows
      )) UN
  )
  SELECT * FROM CTE
    PIVOT 
    (MAX(value) FOR Ordinal IN (
      [1],[2],[3],[4],[5],[6]  -- Ordinals to return as columns
    )) PVT

Transposed table results:
Calc1 | 1000000 | 0 | 1000000 | 0 | 1000000 | 0
Calc3 | 3.53674427608709 | 7.07348855217417 | 7.07348855217417 | 10.6102328282613 | 10.6102328282613 | 0
Calc2 | 0.438556290234799 | 0.877112580469597 | 0.877112580469597 | 1.3156688707044 | 1.3156688707044 | 0

Note that the limitation with this solution is the number rows in source table/columns in transposed table is fixed and requires SQL Server 2005+

Also thanks to Rishabh K from http://social.msdn.microsoft.com/Forums/en-AU/transactsql/thread/680023ea-bd2e-4569-a016-62810fee2001

ASP.NET url changes with 301 permanent redirect

I deployed a new ASP.NET MVC web site and needed to indicate to google and links from other sites that the url for most pages had permanently changed (rather than give them a 404 error).

Adding simple dummy .aspx files for these old pages with the following (non-code behind) script with a 301 HTTP response did the trick with little fuss:


<%@ Page Language="C#" AutoEventWireup="false" Inherits="System.Web.UI.Page" %>
<script runat="server">
    protected override void OnLoad(EventArgs e)
    {
        base.OnLoad(e);
        // Tell old links to go to the new url:
        Response.Status = "301 Moved Permanently";
Response.AddHeader("Location","http://www.mydomain.com/mynewurl");
    }
</script>

At some point I'll simply delete them once I think the important external links have been updated.

Install Elmah logging with ASP.NET MVC 3

Elmah is a great logging tool for ASP.NET and works fine with ASP.NAT MVC 3 with these quick steps:

1. Ensure NuGet is installed in Visual Studio 2010 (shows as Library Package Manager under Tools menu)

2. Open the Package Manager console and install Elmah by typing:
PM> Install-Package Elmah.MVC
This updates the web.config with sectionGroup, httpModules and httpHandlers and adds dll references.

3. Add to web.config:
  <elmah>
    <security allowRemoteAccess="no" />
    <errorLog type="Elmah.XmlFileErrorLog, Elmah" logPath="~/App_Data/Elmah" />
  </elmah>

4. To enable error logging when customErrors is "on" add the following to Global.asax: [see: http://stackoverflow.com/questions/766610/how-to-get-elmah-to-work-with-asp-net-mvc-handleerror-attribute/5936867#5936867]

public class ElmahHandledErrorLoggerFilter : IExceptionFilter
{
    public void OnException (ExceptionContext context)
    {
        // Log only handled exceptions, because all other will be caught by ELMAH anyway.
        if (context.ExceptionHandled)
            Elmah.ErrorSignal.FromCurrentContext().Raise(context.Exception);
    }
}


And:

public static void RegisterGlobalFilters(GlobalFilterCollection filters)
        {
            filters.Add(new ElmahHandledErrorLoggerFilter());  // Required so that Elmah still loggs errors when customErrors mode="On"
            filters.Add(new HandleErrorAttribute());
        }

Done.