Ravi Kakumani
....Dare 2 Dream - Success is not final, failure is not fatal: it is the courage to continue that counts.
"None can stop the rising sun, clouds can hide for a while........" -Ravi
Tuesday, May 17, 2011
Concatenating row values in Transact-SQL
Nice article for concatenating row values in SQL: Click Here
Friday, November 12, 2010
Thursday, June 3, 2010
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();
}
}
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
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'
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'
Subscribe to:
Posts (Atom)