Ravi Kakumani

Tuesday, May 17, 2011

Concatenating row values in Transact-SQL

Nice article for concatenating row values in SQL: Click Here

Thursday, June 3, 2010

SNOW in Ellicott City, MD

Wednesday, May 26, 2010

Excute SSIS package from .NET application

Fowlloing code is used to excute SSIS package from web or windows applications.

First we need to add reference Microsoft.SqlServer.ManagedDTS to the project ( Right click on References. Click on 'Add Reference'. It will pop up Add reference window. select Microsoft.SqlServer.ManagedDTS under .NET components and click on OK).

HTML Source:

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">

         <title>Excute SSIS package from .NET application</title>
</head>
<body>
          <form id="form1" runat="server">
                  <div>
                         <asp:Button ID="btnExcute" runat="server" OnClick="btnPackage_Click" Text="Excute Package" />
                   </div>
          </form>
</body>
</html>

C# Code:

protected void btnPackage_Click(object sender, EventArgs e)

{
          string filename = "C:/Documents/Projects/TestPackage/bin/Deployment/test.dtsx";
          Application app = new Application();
          Package pckg = null;
          try
          {
                  pckg = app.LoadPackage(filename, null);
                  DTSExecResult result = pckg.Execute();
            }
           catch (Exception ex)
          {
                  throw ex;
           }
           finally
           {
                    pckg.Dispose();
            }
}

Tuesday, March 9, 2010

Search tables by using column name in SQL Server

Following query is used to find all tables and views which contain specific column name.

SELECT [TABLE NAME]=SYSOBJECTS.NAME,

[COLUMN NAME]=SYSCOLUMNS.NAME,
[DATA TYPE]=SYSTYPES.NAME,
[COLUMN LENGTH] = SYSCOLUMNS.LENGTH
FROM SYSOBJECTS
JOIN SYSCOLUMNS ON SYSOBJECTS.ID = SYSCOLUMNS.ID
JOIN SYSTYPES ON SYSCOLUMNS.XTYPE=SYSTYPES.XTYPE
WHERE SYSCOLUMNS.NAME ='COLUMN NAME'

You need to give column name

List of all Tables, Views and their columns in selected SQl database

Following query gives complete list of tables and views in a SQL database.

USE [DATABASE NAME]

GO
SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Following is the sample query to get column names and data types for selected table:

SELECT
COLUMN_NAME 'COLUMN NAME',
DATA_TYPE 'DATA TYPE',
COULMN_DEFAULT 'COLUMN DEFAULT'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TABLE NAME'