SQL Publishing Wizard - A tool to generate database schema and data in Sql Server 2005

I was searching for some functionality to generating database schema with existing data in Sql Server Management Studio Express. But could not find such functionality there. So, I was searching for some tool that can generate database schema with data.

Finally I've found that there is a tool - "SQL Publishing Wizard" comes with Sql Server 2005 express in Visual Studio 2008 installation dvd (I'm not sure about VS 2005). If you install SqlServer2005 express with Visual Studio 2008 installation then you can find this tool in - "C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2\SqlPubWiz.exe". If this is not installed you can download it from - http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

You can also start this tool from Visual Studio. Right click on any Data Connection from Server Explorer and select "Publish to Provider". This will start "SQL Publishing Wizard" for the selected database.

How to mock indexer and out parameter in NMock

In my last project I was trying to mock an indexer and some out parameters. But I could not find anything about mocking indexer and out parameter in NMock documentation. Then after some google searches, I have found some good post about these topics.

You can view this blog post on mocking out parameters. I think this post is good enough to learn how to mock out parameter. Now, if you want to mock an indexer then you can see in this post. You can also take a look in this post to know how to mock an indexer in NMock.

Array/List of numbers in oracle procedure or function

Sometimes you may need to pass a list of numbers or string into a oracle procedure or function. But there is no built in support for an array or list in oracle. To solve this type of problem you can create your own array type and a small utility function which returns an array of numbers from a comma(,) delimited string of numbers. Here are the steps -

1. Create an array type

CREATE OR REPLACE TYPE array AS TABLE OF NUMBER;

2. Create the utility function which will return array of numbers from an comma delimited string of numbers.

CREATE FUNCTION get_array(num_list IN VARCHAR2)

RETURN array PIPELINED

IS

    countAppearance NUMBER := 0;

    delimiter_position NUMBER := -1;

    start_position NUMBER := 1;

    num VARCHAR2(10);

 

BEGIN

    WHILE delimiter_position != 0 LOOP

        countAppearance := countAppearance + 1;

        delimiter_position := InStr(num_list, ',', 1,countAppearance);

        IF delimiter_position = 0 THEN

            num := Trim(SubStr(num_list,start_position));

        ELSE

            num := Trim(SubStr(num_list,start_position,(delimiter_position - start_position)));

            start_position := delimiter_position + 1;

        END IF;

 

        PIPE ROW(TO_NUMBER(num));

 

    END LOOP;

    RETURN;

END get_array;

 

Here is a sample function which uses this get_array function -

CREATE OR REPLACE FUNCTION testFunc(num_list IN VARCHAR2)

RETURN NUMBER

IS

   total NUMBER := 0;

BEGIN

   SELECT

      COUNT(*) INTO total

   FROM

      testTable

   WHERE

      age IN (SELECT * FROM TABLE(get_array(num_list)));

 

   RETURN total;

END testFunc;

 

In the same way you can also create array for string. It will be helpful when you will need to pass an array from a programming language to a oracle function/procedure.

CruiseControl.NET (ccnet) webdashboard in IIS7

Cruise Control.Net (ccnet) installer does not add webdashboard as a virtual directory under iis7. If you want to use webdashboard then you have to manually add it as an application.

After you adding it as an application you may get some errors because of Managed Pipeline Mode of your Application Pool settings. By default iis7 will configure your site with DefaultAppPool which has Integrated Managed Pipeline Mode. You have to chage your webdashboard's ApplicationPool's Managed Pipeline Mode to Classic in order to make it work properly.

If you want to keep default settings (Integrated Managed Pipeline Mode), then you have to add following settings in your webdashboard's web.config file. This configuration will work both in iis6 and iis7.

<system.webServer>

<validation validateIntegratedModeConfiguration="false" />

<handlers accessPolicy="Read, Write, Script, Execute">

<add name="ccnetAspxHandler" verb="*" path="*.aspx" type="ThoughtWorks.CruiseControl.WebDashboard.MVC.ASPNET.HttpHandler,
ThoughtWorks.CruiseControl.WebDashboard" />

<add name="ccnetXmlHandler" verb="*" path="*.xml" type="ThoughtWorks.CruiseControl.WebDashboard.MVC.ASPNET.HttpHandler,
ThoughtWorks.CruiseControl.WebDashboard" />

</handlers>

</system.webServer>