JanaDotNet

Wednesday, 26 August 2015

Script for Generate Sample Data for Table Name

create proc GENERATEQUERYSampleData (@TBL varchar(40))
as
begin
declare @para varchar(max)= 'DECLARE @count INT
SET @count = 1
SET NOCOUNT ON ;

WHILE @count <= 100000
BEGIN

INSERT INTO ';

select @para +=TABLE_SCHEMA+'.'+TABLE_NAME +'('
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME=@TBL

select @para += cl.COLUMN_NAME+','
from INFORMATION_SCHEMA.COLUMNS cl
join sys.columns C
    on c.is_identity=0 and Cl.TABLE_NAME=OBJECT_NAME(object_id) and cl.COLUMN_NAME=c.name
where cl.TABLE_NAME= @TBL;

SET @para = LEFT(@para,LEN(@para)-1);
SET @para += ')' +char(10)+' select ';

select @para += case
        when cl.DATA_TYPE in ('bit')
        then ' DATEPART(MILLISECOND, GETDATE()) >= 500 THEN 0 ELSE 1 END '
        when cl.DATA_TYPE in ('bigint','smallint','tinyint','int','numeric')
        then '(ABS(CHECKSUM(NEWID())) % 1231) + ((ABS(CHECKSUM(NEWID())) % 634) * 1)'
        when cl.DATA_TYPE in ('decimal','float','money')
        then '(ABS(CHECKSUM(NEWID())) % 100001) + ((ABS(CHECKSUM(NEWID())) % 100001) * 0.00001) '
        when cl.DATA_TYPE ='date'
        then 'dateadd(YEAR, (ABS(CHECKSUM(NEWID())) % 1000),dateadd(DAY, (ABS(CHECKSUM(NEWID())) % 10) , dateadd(MONTH, (ABS(CHECKSUM(NEWID())) % 10) , cast (GETDATE() as date ))))'
        when cl.data_type ='datetime'
        then 'DATEADD(MILLISECOND, (ABS(CHECKSUM(NEWID())) % 6000) * -1, DATEADD(MINUTE, (ABS(CHECKSUM(NEWID())) % 1000000) * -1, GETDATE()))'
        when cl.Data_type in ('char','nchar','nvarchar','varchar','binary','varbinary')
        then dbo.GetString(CHARACTER_MAXIMUM_LENGTH)
        end +','
from INFORMATION_SCHEMA.COLUMNS cl
join sys.columns C
    on c.is_identity=0 and Cl.TABLE_NAME=OBJECT_NAME(object_id) and cl.COLUMN_NAME=c.name
where cl.TABLE_NAME= @TBL;

SET @para = LEFT(@para,LEN(@para)-1)
SET @para += char(10)+char(10)+'SET @count += 1
END'

print @para

end

--------------------------

CREATE function GetString (@Type varchar(20))
returns varchar(500)
as
begin

declare @q varchar(500);
set @Type =lTrim(rtrim(@Type))
if(@Type =1)
begin

set @q= 'CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) '

end
else if(@Type =2)
begin

set @q= 'CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97)'

end
else if(@Type =3)
begin

set @q= 'CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97)+CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) '

end
else if(@Type =4)
begin

set @q= 'CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97)+CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) +CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97)'

end
else if(@Type =5)
begin

set @q= 'CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97)+CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) +CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97)+ CHAR((ABS(CHECKSUM(NEWID())) % 26)+ 97) '

end
else
begin

set @q= 'CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97)+CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) +CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97)+ CHAR((ABS(CHECKSUM(NEWID())) % 26)+ 97) +CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97)'

end
return @q
end

Tuesday, 25 August 2015

script to create store procedure disable row by giving table name in sql server

alter PROC [dbo].[GENERATEQUERYDel]
@TBL VARCHAR(50)=''
AS
BEGIN
DECLARE @CR VARCHAR(200)='';
select @CR +='CREATE PROCEDURE '+TABLE_SCHEMA+'.Pr'+upper(left(@TBL,1))+right(@TBL,len(@TBL)-1)+'Del'
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME=@TBL

declare @schema varchar(10);
select @schema =TABLE_SCHEMA
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME=@TBL

declare @db varchar(10);
select @db =TABLE_CATALOG
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME=@TBL

declare @par nvarchar(1000)='';
select @par+='@'+COLUMN_NAME+' '+DATA_TYPE +case
    when CHARACTER_MAXIMUM_LENGTH is null
    then ''
    when CHARACTER_MAXIMUM_LENGTH is not null
    then '('+CAST( REPLACE(CHARACTER_MAXIMUM_LENGTH,'-1','max') as varchar)+')'
    end +CHAR(10)
from INFORMATION_SCHEMA.COLUMNS cl
join sys.columns C
    on c.is_identity=1 and Cl.TABLE_NAME=OBJECT_NAME(object_id) and cl.COLUMN_NAME=c.name
where cl.TABLE_NAME= @TBL


declare @beb varchar(200) ='AS' +CHAR(10) +'BEGIN'+CHAR(10)+'SET ANSI_NULLS ON;'+CHAR(10)+'SET QUOTED_IDENTIFIER ON;'+CHAR(10)+'SET NOCOUNT ON;'+CHAR(10)+CHAR(10);
SET @beb+='BEGIN TRAN'+CHAR(10);
SET @beb+='BEGIN TRY'+CHAR(10) +CHAR(10);

-- Delete start

declare @update varchar(1000) ='UPDATE '+@schema+'.'+@TBL+' SET CurrentStatus=''D''';

declare @identityCol varchar(50)='';
select @identityCol=COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS cl
join sys.columns C
    on c.is_identity=1 and Cl.TABLE_NAME=OBJECT_NAME(object_id) and cl.COLUMN_NAME=c.name
where cl.TABLE_NAME= @TBL;

if(len(@identityCol) > 1)
begin
set @update +=' WHERE ';

set @update+=@identityCol+'=@'+@identityCol;

end

-- Delete end

DECLARE @END VARCHAR(50) =CHAR(10)+'COMMIT TRAN'+CHAR(10)+'END TRY'+CHAR(10);
DECLARE @CATCH VARCHAR(500) ='BEGIN CATCH'+CHAR(10)+'ROLLBACK TRAN'+CHAR(10)+CHAR(10)+CHAR(5)+CHAR(5)+CHAR(5)+'INSERT INTO ADMIN.TBLERRORlOG (CustomeError) VALUES (NULL);'+CHAR(10)+CHAR(10)+'END CATCH'+CHAR(10)+'END';

declare @h bit=0;

if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME='CurrentStatus' and TABLE_NAME=@tbl )
if( len(@par) > 1 )
begin

print @CR+CHAR(10)+@par+CHAR(10)+@beb+CHAR(10)+@update+CHAR(10)+@END+CHAR(10)+@CATCH+char(10)+'Go';

end

END

Script to create Store Procedure by Table name sql server

CREATE PROC [dbo].[GENERATEQUERY]
@TBL VARCHAR(20)=''
AS
BEGIN
DECLARE @CR VARCHAR(200)='';
select @CR +='CREATE PROCEDURE '+TABLE_SCHEMA+'.Pr'+upper(left(@TBL,1))+right(@TBL,len(@TBL)-1)+'IU'
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME=@TBL

declare @schema varchar(10);
select @schema =TABLE_SCHEMA
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME=@TBL

declare @db varchar(10);
select @db =TABLE_CATALOG
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME=@TBL

declare @par nvarchar(1000)='';
select @par+='@'+COLUMN_NAME+' '+DATA_TYPE +case
    when CHARACTER_MAXIMUM_LENGTH is null
    then ','
    when CHARACTER_MAXIMUM_LENGTH is not null
    then '('+CAST( REPLACE(CHARACTER_MAXIMUM_LENGTH,'-1','max') as varchar)+'),'
    end +CHAR(10)
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=@TBL
    AND COLUMN_NAME NOT IN ('CreatedOn','UpdatedOn','UpdatedBy','UserId');

SET @par +='@CmdType char(1)'+char(10);
SET @par =REPLACE(@par,'@CreatedBy','@UserId')

declare @beb varchar(200) ='AS' +CHAR(10) +'BEGIN'+CHAR(10)+'SET ANSI_NULLS ON;'+CHAR(10)+'SET QUOTED_IDENTIFIER ON;'+CHAR(10)+'SET NOCOUNT ON;'+CHAR(10)+CHAR(10);
SET @beb+='BEGIN TRAN'+CHAR(10);
SET @beb+='BEGIN TRY'+CHAR(10) +CHAR(10)+'IF(@CmdType=''I'')'+char(10)+'BEGIN'+char(10);

-- insert start
declare @insert varchar(1000) ='INSERT INTO '+@schema+'.'+@TBL+'(';

select @insert+=c.name+','
from INFORMATION_SCHEMA.COLUMNS Cl
join sys.columns C
    on c.is_identity=0 and Cl.TABLE_NAME=OBJECT_NAME(object_id) and cl.COLUMN_NAME=c.name
where cl.TABLE_NAME= @TBL and c.name <> 'Createdon';

SET @insert = LEFT(@insert,LEN(@insert)-1);
SET @insert+=') VALUES ('

select @insert+='@'+COLUMN_NAME+','
from INFORMATION_SCHEMA.COLUMNS cl
join sys.columns C
    on c.is_identity=0 and Cl.TABLE_NAME=OBJECT_NAME(object_id) and cl.COLUMN_NAME=c.name
where cl.TABLE_NAME= @TBL and c.name <> 'Createdon';

SET @insert = LEFT(@insert,LEN(@insert)-1);
SET @insert =REPLACE(@insert,'@CreatedBy','@UserId')
SET @insert =REPLACE(@insert,'@UpdatedBy','@UserId')
SET @insert =REPLACE(@insert,'@UpdatedOn','GETDATE()')
SET @insert+=');'+CHAR(10)+CHAR(10)+'END'+char(10)+'ELSE'+char(10)+'BEGIN'+char(10);

--insert end

-- update start

declare @update varchar(1000) ='UPDATE '+@schema+'.'+@TBL+' SET ';

select @update+=c.name+'=@'+c.name+','
from INFORMATION_SCHEMA.COLUMNS Cl
join sys.columns C
    on c.is_identity=0 and Cl.TABLE_NAME=OBJECT_NAME(object_id) and cl.COLUMN_NAME=c.name
where cl.TABLE_NAME= @TBL and c.name not like 'Create%';

set @update =left(@update,len(@update)-1);

declare @identityCol varchar(50)='';
select @identityCol=COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS cl
join sys.columns C
    on c.is_identity=1 and Cl.TABLE_NAME=OBJECT_NAME(object_id) and cl.COLUMN_NAME=c.name
where cl.TABLE_NAME= @TBL;

if(len(@identityCol) > 1)
begin
set @update +=' WHERE ';

set @update+=@identityCol+'=@'+@identityCol;

end

SET @update =REPLACE(@update,'@UpdatedBy','@UserId')
SET @update =REPLACE(@update,'@UpdatedOn','GETDATE()')
set @update +=char(10)+'END'+char(10)+char(10);
-- updaate end

DECLARE @END VARCHAR(50) ='COMMIT TRAN'+CHAR(10)+'END TRY'+CHAR(10);
DECLARE @CATCH VARCHAR(500) ='BEGIN CATCH'+CHAR(10)+'ROLLBACK TRAN'+CHAR(10)+CHAR(10)+CHAR(5)+CHAR(5)+CHAR(5)+'INSERT INTO ADMIN.TBLERRORlOG (CustomeError) VALUES (NULL);'+CHAR(10)+CHAR(10)+'END CATCH'+CHAR(10)+'END';

print @CR+CHAR(10)+@par+CHAR(10)+@beb+CHAR(10)+@insert+CHAR(10)+@update+CHAR(10)+@END+CHAR(10)+@CATCH+char(10)+'Go';

END

Wednesday, 19 August 2015

Programmatically Create IIS Website and Application Pool Using C#

In this article, we will look into creation of an IIS 7\7.5 website and corresponding application pool programmatically using a C# console application. These activities are most commonly done by an IIS administrator. We can automate those activities using this application. Let's create a console application and name it IISAutomation in Visual Studio 2010 by targeting .NET 3.5 as in the following:

Let's add a reference to Microsoft.Web.Administration that is present under C:\Windows\System32\inetsrv [IIS installation directory]. This assembly contains classes that a developer can use to administer the IIS Manager. First we will create an application pool using the following code:

private static void CreateAppPool(string poolname,bool enable32bitOn64, ManagedPipelineMode mode,string runtimeVersion="v4.0")
{
using (ServerManager serverManager = new ServerManager())
{
ApplicationPool newPool = serverManager.ApplicationPools.Add(poolname);
newPool.ManagedRuntimeVersion = runtimeVersion;
newPool.Enable32BitAppOnWin64 = true;
newPool.ManagedPipelineMode = mode;
serverManager.CommitChanges();
}
}

Here, we created an application pool by creating an instance of ApplicationPool object. Then, we set the properties of the application pool, like the name, .NET version to be used and the committed changes by calling CommitChanges(). Similarly, we will create a website using the following code:

private static void CreateIISWebsite(string websiteName, string hostname, string phyPath, string appPool)
{
ServerManager iisManager = new ServerManager();
iisManager.Sites.Add(websiteName, "http", "*:80:" + hostname, phyPath);
iisManager.Sites[websiteName].ApplicationDefaults.ApplicationPoolName = appPool;
foreach (var item in iisManager.Sites[websiteName].Applications)
{
item.ApplicationPoolName = appPool;
}
iisManager.CommitChanges();
}

Here, we created a new web site by creating an instance of ServerManager, adding it to the Sites collection and setting its properties like name, physical path and so on and committed the changes.

We will use the preceding methods in our Main method and create an application pool and a web site using the following code:

static void Main(string[] args)
{
Console.WriteLine("Do you want to create an Application Pool:y/n");
string response = Console.ReadLine();
if (response.ToString() == "y")
{
Console.Write("Please enter Application Pool Name:");
string poolname = Console.ReadLine();
bool isEnable32bit = false;
ManagedPipelineMode mode = ManagedPipelineMode.Classic;
Console.Write("Need to enable 32 bit on Windows 64 bit?y/n [Applicable for 64 bit OS]: y/n?");
string enable32bit = Console.ReadLine();
if (enable32bit.ToLower() == "y")
{
isEnable32bit = true;
}
Console.Write("Please select Pipeline Mode: 1 for Classic, 2 for Integrated:");
string pipelinemode = Console.ReadLine();
if (pipelinemode.ToLower() == "2")
{
mode = ManagedPipelineMode.Integrated;
}
Console.Write("Please select Runtime Version for Application Pool: 1 for v2.0, 2 for v4.0:");
string runtimeVersion = Console.ReadLine()== "1" ? "v2.0" : "v4.0";
CreateAppPool(poolname, isEnable32bit, mode, runtimeVersion);
Console.WriteLine("Application Pool created successfully...");
}
Console.WriteLine("Do you want to create a website:y/n");
response = Console.ReadLine();
if (response.ToString() == "y")
{
Console.Write("Please enter website name:");
string websiteName = Console.ReadLine();
Console.Write("Please enter host name:");
string hostname = Console.ReadLine();
Console.Write("Please enter physical path to point for website:");
string phypath = Console.ReadLine();
Console.WriteLine("Please enter Application pool Name:");
foreach(var pool in new ServerManager().ApplicationPools)
{
Console.WriteLine(pool.Name);
}
Console.WriteLine("");
Console.Write("Please enter Application pool Name for web site:");
string poolName = Console.ReadLine();
CreateIISWebsite(websiteName,hostname,phypath,poolName);
Console.WriteLine("Web site created successfully...");
Console.ReadLine();
}
}

Here, we set the attributes necessary for web site and application creation by getting input from the console. Let's run the application and input the details as shown below:

Sunday, 9 August 2015

SQL Server Migration Assistant (SSMA) Team's Blog

Microsoft SQL Server Migration Assistant (SSMA) v5.3 is now available.

Rate This

ajaykumarks

15 May 2014 8:11 AM

SSMA simplifies database migration process from Oracle/Sybase/MySQL and Microsoft Access to SQL Server and SQL Azure. SSMA automates all aspects of migration including migration assessment analysis, schema and SQL statement conversion, data migration as well as migration testing to reduce cost and reduce risk of your database migration project.

The new version of SSMA - SSMA 5.3 provides the following major enhancements:

Support of Migration to MS SQL Server 2014.
Improved conversion mechanism when migrating to Azure.
New features in the Migration GUI.
No requirement to get a License key to start using SSMA.

Download SQL Server Migration Assistant (SSMA) v.5.3 :

Launch the download of the SSMA for Oracle.

Launch the download of the SSMA for Sybase.

Launch the download of the SSMA for MySQL.

Launch the download of the SSMA for Access.

The SSMA product team is available to answer your questions and provide limited technical support. Contact the team at ssmahelp@microsoft.com

Friday, 7 August 2015

SQL SERVER – Find Most Expensive Queries Using DMV

The title of this post is what I can express here for this quick blog post. I was asked in recent query tuning consultation project, if I can share my script which I use to figure out which is the most expensive queries are running on SQL Server. This script is very basic and very simple, there are many different versions are available online. This basic script does do the job which I expect to do – find out the most expensive queries on SQL Server Box.

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.TEXT)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1),

qs.execution_count,

qs.total_logical_reads, qs.last_logical_reads,

qs.total_logical_writes, qs.last_logical_writes,

qs.total_worker_time,

qs.last_worker_time,

qs.total_elapsed_time/1000000 total_elapsed_time_in_S,

qs.last_elapsed_time/1000000 last_elapsed_time_in_S,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY qs.total_logical_reads DESC -- logical reads

-- ORDER BY qs.total_logical_writes DESC -- logical writes

-- ORDER BY qs.total_worker_time DESC -- CPU time

Last Ran Query – Recently Ran Query

How many times we have wondered what were the last few queries ran on SQL Server? Following quick script demonstrates last ran query along with the time it was executed on SQL Server 2005.

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]

FROM sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

ORDER BY deqs.last_execution_time DESC

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL – sys.dm_exec_query_stats, BOL – sys.dm_exec_sql_text