Happy Coding

26 Feb 2014

How to recover the deleted stored procedure, function, trigger, view

In this article, we will learn how to recover views, stored procedures, functions & triggers via SQL server log.

Step 1 :
Lets create few objects to explain the recovery process.

CREATE TABLE [dbo].[Student](

      [Sno] [int] NOT NULL,

      [Student ID] nvarchar(6) Not NULL ,

      [Student name] [varchar](50) NOT NULL,

      [Date of Birth]  datetime not null,

      [Weight] [int] NULL)

GO

Create View Vw_Student

as

Select * from [Student]

GO

Create Procedure SP_Student

@StudentID nvarchar(6)

as

Select * from Student Where [Student ID] =@StudentID

GO

Create FUNCTION [dbo].[Fn_Student](@StudentID nvarchar(6))

RETURNS int

AS

Begin

    Declare @Weight int

    Select  @Weight = [Weight]

        from Student Where [Student ID] =@StudentID

    Return  @Weight

End

GO

CREATE TRIGGER trg_Student

ON Student

FOR INSERT

AS RAISERROR (50009, 16, 10)

GO

Step 2:
Lets drop these objects.

Drop View [dbo].[Vw_Student]

GO

Drop Procedure [dbo].SP_Student

GO

Drop Function [dbo].[Fn_Student]

GO

Drop Trigger [dbo].[trg_Student]

GO

Step 3:
Check the existence of these objects to make sure that objects are dropped properly.

Select * from [Vw_Student]

GO

EXEC SP_Student 1

GO

Select dbo.[Fn_Student](1)

Step 4:
Create the given below stored procedure to recover the dropped objects.

-- Script Name: Recover_Dropped_Objects_Proc

-- Script Type : Recovery Procedure

-- Develop By: Muhammad Imran

-- Date Created: 04 Dec 2012

-- Modify Date:

-- Version    : 1.0

Create PROCEDURE Recover_Dropped_Objects_Proc

@Database_Name NVARCHAR(MAX),

@Date_From DATETIME='1900/01/01',

@Date_To DATETIME ='9999/12/31'

AS

DECLARE @Compatibility_Level INT

SELECT @Compatibility_Level=dtb.compatibility_level

FROM

master.sys.databases AS dtb WHERE dtb.name=@Database_Name

IF ISNULL(@Compatibility_Level,0)<=80

BEGIN

    RAISERROR('The compatibility level should be equal to or greater SQL SERVER 2005 (90)',16,1)

    RETURN

END

Select Convert(varchar(Max),Substring([RowLog Contents 0],33,LEN([RowLog Contents 0]))) as [Script]

from fn_dblog(NULL,NULL)

Where [Operation]='LOP_DELETE_ROWS' And [Context]='LCX_MARK_AS_GHOST'

And [AllocUnitName]='sys.sysobjvalues.clst'

AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM    sys.fn_dblog(NULL, NULL) 

WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')  

And [Transaction Name]='DROPOBJ'

And  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)

And Substring([RowLog Contents 0],33,LEN([RowLog Contents 0]))<>0

GO

--Execute the procedure like

--EXEC Recover_Dropped_Data_Proc 'Database Name'

----EXAMPLE #1 : FOR ALL Dropped Objects

EXEC Recover_Dropped_Objects_Proc 'test'

--GO

------EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGE

EXEC Recover_Dropped_Objects_Proc 'test','2011/12/01','2013/01/30'

--RESULT

28 Jan 2014

Get Table Metadata in SQL Server 2008

SELECT c.name AS [Column Name]
,type_name(c.xusertype) [Data Type]
,c.length [Length]
,cd.value AS [Column Desc.]
FROM sysobjects t
INNER JOIN syscolumns c ON c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd ON cd.major_id = c.id
AND cd.minor_id = c.colid
AND cd.name = 'MS_Description'
WHERE t.type = 'u'
AND t.name = 'tablename'
ORDER BY t.name
,c.colorder

31 Oct 2013

UPLOAD FILES WITH PROGRESS BAR IN ASP.NET USING JQUERY

In this article I will explain how to upload multiple files AJAX style along with progress bar in ASP.Net using jQuery Uploadify Plugin.

And the answer is Uploadify plugin for JQuery which does the same in few simple steps. In this article I’ll explain the same.

Step 1

Download the Uploadify JQuery plugin and the JQuery Library using the links below.

Download JQuery

Download Uploadify

Once downloaded you’ll need to place the below four files

1. jquery-1.3.2.min.js

2. jquery.uploadify.js

3. uploader.fla

4. uploader.swf

in a folder called scripts in the root folder of your ASP.Net website application

Step 2

Start Visual Studio, create a new website and do as done below

Inherit the following files you downloaded earlier in the head section of the aspx or the master page

Add an ASP.Net FileUpload Control to the form tag

<asp:FileUpload ID="FileUpload1" runat="server" />

</div>

</form>

Place the following script in the head section or the ContentPlaceHolder in case you are using Master Pages

$(window).load(

function() {

$("#<%=FileUpload1.ClientID %>").fileUpload({

'uploader': 'scripts/uploader.swf',

'cancelImg': 'images/cancel.png',

'buttonText': 'Browse Files',

'script': 'Upload.ashx',

'folder': 'uploads',

'fileDesc': 'Image Files',

'fileExt': '*.jpg;*.jpeg;*.gif;*.png',

'multi': true,

'auto': true

});

}

);

</script>

As you can see we need to specify some settings along with the FileUpload control. The complete list of settings and their description is available here

Important setting to point out is 'script': 'Upload.ashx' which will handle the FileUpload and save the uploaded files on to the disk.

Below is the code for the Upload.ashx file

<%@ WebHandler Language="C#" Class="Upload" %>

using System;

using System.Web;

using System.IO;

public class Upload : IHttpHandler {

public void ProcessRequest (HttpContext context) {

context.Response.ContentType = "text/plain";

context.Response.Expires = -1;

try

{

HttpPostedFile postedFile = context.Request.Files["Filedata"];

string savepath = "";

string tempPath = "";

tempPath = System.Configuration.ConfigurationManager.AppSettings["FolderPath"];

savepath = context.Server.MapPath(tempPath);

string filename = postedFile.FileName;

if (!Directory.Exists(savepath))

Directory.CreateDirectory(savepath);

postedFile.SaveAs(savepath + @"\" + filename);

context.Response.Write(tempPath + "/" + filename);

context.Response.StatusCode = 200;

}

catch (Exception ex)

{

context.Response.Write("Error: " + ex.Message);

}

public bool IsReusable {

get {

return false;

}

VB.Net

<%@ WebHandler Language="VB" Class="UploadVB" %>

Imports System

Imports System.Web

Imports System.IO

Public Class UploadVB : Implements IHttpHandler

Public Sub ProcessRequest(ByVal context As HttpContext) ImplementsIHttpHandler.ProcessRequest

Dim postedFile As HttpPostedFile = context.Request.Files("Filedata")

Dim savepath As String = ""

Dim tempPath As String = ""

tempPath = System.Configuration.ConfigurationManager.AppSettings("FolderPath")

savepath = context.Server.MapPath(tempPath)

Dim filename As String = postedFile.FileName

If Not Directory.Exists(savepath) Then

Directory.CreateDirectory(savepath)

End If

postedFile.SaveAs((savepath & "\") + filename)

context.Response.Write((tempPath & "/") + filename)

context.Response.StatusCode = 200

End Sub

Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable

Get

Return False

End Get

End Property

End Class

As you will notice that the handler simply accepts the posted files and saves the file in folder called uploads inside the website root directory whose path is placed in an AppSettings key in the Web.Config file Refer below

</appSettings >

That’s all you need to do now run the application and you’ll notice your website running

Browsing the File

Uploading Multiple Files like GMAIL in ASP.Net with AJAX and progressbar

Selecting Multiple Files Simultaneously

Selecting Multiple files in single browse ASP.Net

Uploading Multiple Files with upload progress

You might have noticed that the files are auto uploaded once browsed if you do not want this feature you can simply set the'auto' settings to false. But in that case you’ll need to provide a trigger the uploading of files on user interaction by placing an Upload button

First you’ll need to set the Auto Upload setting to false refer the bold part

$(window).load(

function() {

$("#<%=FileUpload1.ClientID%>").fileUpload({

'uploader': 'scripts/uploader.swf',

'cancelImg': 'images/cancel.png',

'buttonText': 'Browse Files',

'script': 'Upload.ashx',

'folder': 'uploads',

'fileDesc': 'Image Files',

'fileExt': '*.jpg;*.jpeg;*.gif;*.png',

'multi': true,

'auto': false

});

}

);

</script>

Then add the following link that will trigger the upload

<a href="javascript:$('#<%=FileUpload1.ClientID%>').fileUploadStart()">Start Upload</a>

That’s it now until user clicks the above link uploading of files won’t take place. Now since the upload is triggered by user it would be great to give him an additional link to clear the browsed files in one go

<a href="javascript:$('#<%=FileUpload1.ClientID%>').fileUploadClearQueue()">Clear</a>

The above code has been tested in the following browsers

Internet Explorer

* All browser logos displayed above are property of their respective owners.

26 Oct 2013

Get Table Structure SQL Server 2008

SELECT c.name AS [column name]
,DATA_TYPE [data type]
,CHARACTER_MAXIMUM_LENGTH [length]
,IS_NULLABLE [isnull]
,cd.value AS [desc]
FROM sysobjects t
INNER JOIN sysusers u ON u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td ON td.major_id = t.id
AND td.minor_id = 0
AND td.name = 'MS_Description'
INNER JOIN syscolumns c ON c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd ON cd.major_id = c.id
AND cd.minor_id = c.colid
AND cd.name = 'MS_Description'
LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS ON t.name = TABLE_NAME
AND c.name = COLUMN_NAME
WHERE t.type = 'u'
AND t.name = 'TableName'
ORDER BY t.name
,c.colorder