How to create dynamic Gridview?

Many times we have the requirement where we have to create columns dynamically.
This article describes you about the dynamic loading of data using the DataTable as the datasource.

Details of the Grid

Let?s have a look at the code to understand better.

Create a gridview in the page,

Drag and drop the GridView on to the page
Or

Manually type GridView definition in the page.


public partial class _Default : System.Web.UI.Page

{

#region constants

const string NAME = “NAME”;

const string ID = “ID”;

#endregion

protected void Page_Load(object sender, EventArgs e)

{

loadDynamicGrid();

}

private void loadDynamicGrid()

{

#region Code for preparing the DataTable

//Create an instance of DataTable

DataTable dt = new DataTable();

//Create an ID column for adding to the Datatable

DataColumn dcol = new DataColumn(ID ,typeof(System.Int32));

dcol.AutoIncrement = true;

dt.Columns.Add(dcol);

//Create an ID column for adding to the Datatable

dcol = new DataColumn(NAME, typeof(System.String));

dt.Columns.Add(dcol);

//Now add data for dynamic columns

//As the first column is auto-increment, we do not have to add any thing.

//Let’s add some data to the second column.

for (int nIndex = 0; nIndex < 10; nIndex++)

{

//Create a new row

DataRow drow = dt.NewRow();

//Initialize the row data.

drow[NAME] = “Row-” + Convert.ToString((nIndex + 1));

//Add the row to the datatable.

dt.Rows.Add(drow);

}

#endregion

//Iterate through the columns of the datatable to set the data bound field dynamically.

foreach (DataColumn col in dt.Columns)

{

//Declare the bound field and allocate memory for the bound field.

BoundField bfield = new BoundField();

//Initalize the DataField value.

bfield.DataField = col.ColumnName;

//Initialize the HeaderText field value.

bfield.HeaderText = col.ColumnName;

//Add the newly created bound field to the GridView.

GrdDynamic.Columns.Add(bfield);

}

//Initialize the DataSource

GrdDynamic.DataSource = dt;

//Bind the datatable with the GridView.

GrdDynamic.DataBind();

}

}

2010
01/19
CATEGORY
ASP.NET
TAGS



Write comment

Display image Dynamically on crystal report

This is very common requirement to display image on crystal report from database. Generally we store image path in database not whole image, because it increase the size of the database. I had the same requirement few days ago and search lot about the same. Finally I got the solution and decide to publish the post.

Here I put the code in C#, so just copy and paste the code and change it as per your need.

dsOrgDetails = objOrganisationMasterBal.GetOrganisationDetails();

if (dsOrgDetails.Tables[0].Rows.Count > 0)

{

DataRow drow;

dt.Columns.Add(“OrganizationImage”, System.Type.GetType(“System.Byte[]“));

dt.Columns.Add(“OrganizationName”, System.Type.GetType(“System.String”));

drow = dt.NewRow();

FileStream fs;

BinaryReader br;

if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + “OrganisationLogo\\” + dsOrgDetails.Tables[0].Rows[0]["Logo"].ToString()))

{

// open image in file stream

fs = new FileStream(AppDomain.CurrentDomain.BaseDirectory + “OrganisationLogo\\” + dsOrgDetails.Tables[0].Rows[0]["Logo"].ToString(), FileMode.Open);

}

else

{

// if photo does not exist show the nophoto.jpg file

fs = new FileStream(AppDomain.CurrentDomain.BaseDirectory + “OrganisationLogo\\noimages.jpg”, FileMode.Open);

}

br = new BinaryReader(fs);

// define the byte array of file length

byte[] imgbyte = new byte[fs.Length + 1];

// read the bytes from the binary reader

imgbyte = br.ReadBytes(Convert.ToInt32((fs.Length)));

drow[0] = imgbyte;

//drow[0] = br.Read(imgbyte, 0, imgbyte.Length);

drow["OrganizationName"] = dsOrgDetails.Tables[0].Rows[0]["OrganisationName"].ToString();

// add the image in bytearray

dt.Rows.Add(drow);

dt.AcceptChanges();

// add row into the data table

br.Close();

// close the binary reader

fs.Close();

}

First get the data from database, here I mean get the image path from the database. Then create DataTable for temporary conversation of path into image. In DataTable, create column of type System.Byte[].

Then create objects of FileStream and BinaryReader. Check that the file is exists or not, if exists then open the file in FileStream’s object. Then assign the FileStream’s object to the BinaryReader and go ahead as per the code.

Now, add the generated DataTable with Image in your DataSet which is assigned to the crystal report’s datasource.

Now, see at crystal report you can find the “OrganizationImage”  in Field Explorer of Crystal Report. Just Drag n Drop the image on crystal report. That’s it.

In some cases, at run time Image is not displayed on crystal report using above code. So for that do one thing.

Comment out this line : drow[0] = imgbyte;

and Uncomment this line : drow[0] = br.Read(imgbyte, 0, imgbyte.Length);

Now, compile the solution and run the report. Again Drag n Drop the image on report. And do the reverse, i mean Uncomment drow[0] = imgbyte this line and Comment  drow[0] = br.Read(imgbyte, 0, imgbyte.Length); this line, and re-compile the solution and run the report. You will get the image on your report.

2010
01/17
CATEGORY
ASP.NET
TAGS









Write comment

Applying validation in Tab Control of Ajax Toolkit

Introduction

Generally, when we have a requirement to develop a system that contains large amount of controls on the form like User Registration, Patient Information, etc., then one of the best possible ways is to contain these amounts of control at one place is to use Ajax Control Toolkit’s Tab Control.

And if we decide to use the Tab Control then we have to also think about the validations of the controls inside the Tab Control. Because some controls are mandatory on the form, some of them needs date validations, some of them require dependency on other control or some of them require regular expression validations.

How to apply validation in Tab Control of Ajax Toolkit

So what is the best way to use these kinds of validations inside the Tab Control? One of the ways is to check validations when we change the tab or check for the validation while filling information in the control. Another way is to check all validations at once means when end user completes the form or fills all the information on the form and then finally on Save or on Register check all validations that we have set on the controls.

As per my research, best way is to check all the validations at once. Because when we are going to check validations on tab changed event there is a need to call tab changed event and because of that there is a server side call occurs.

So best way is to use JavaScript function for all the validations and call this JavaScript function when user submitting the form.

To do this, First of all put the appropriate server side validation control on the form as per our requirement.

Then for example if we have a User Registration form then we divides the user registration information into separate tabs like we put user’s personal information in Personal Information tab, user’s office information in Office Information Tab, bank information in Bank Information tab, etc.

Now give separate ValidationGroup to each of the tab.

Example

<cc1:TabContainer ID=”MainTabContainer” runat=”server” ActiveTabIndex=”0″ Width=”100%”> <cc1:TabPanel ID=”tabPersonal” runat=”server” TabIndex=”0″>

<HeaderTemplate>

<asp:Label ID=”Label29″ runat=”server” Text=”Personal Information” CssClass=”bodyTxtBold”></asp:Label>

</HeaderTemplate>

<ContentTemplate>

<asp:TextBox ID=”txtUserId” runat=”server” MaxLength=”100″ CssClass=”Textfiled-border verdana11greynormal” Width=”217px” ValidationGroup=”tabPersonal”></asp:TextBox>

<asp:RequiredFieldValidator ID=”rfvUserId” runat=”server” ControlToValidate=”txtUserId” CssClass=”bodyTxtBold” ErrorMessage=”Please enter user id.” ValidationGroup=”tabPersonal” SetFocusOnError=”True” Display=”Dynamic”></asp:RequiredFieldValidator>

</ContentTemplate>

</cc1:TabPanel>

<cc1:TabPanel ID=”tabContact” runat=”server” TabIndex=”1″>

<HeaderTemplate>

<asp:Label ID=”lblContactHeader” runat=”server” CssClass=”bodyTxtBold” Text=”Contact Information”></asp:Label>

</HeaderTemplate>

<ContentTemplate>

<asp:Label ID=”lblOfficeEmail” runat=”server” Text=”E-mail address” CssClass=”bodyTxtBold”MaxLength=”50″></asp:Label>

<asp:TextBox ID=”txtOfficeEmail” runat=”server” CssClass=”Textfiled-border verdana11greynormal” MaxLength=”100″ Width=”80%”></asp:TextBox>

<asp:RequiredFieldValidator ID=”rfvOfficeEmail” runat=”server” ControlToValidate=”txtOfficeEmail” CssClass=”bodyTxtBold”ErrorMessage=”Please enter office e-mail.” ValidationGroup=”tabContact” Display=”Static”>

</asp:RequiredFieldValidator>

<asp:RegularExpressionValidator ID=”revOfficeEmail” runat=”server” ControlToValidate=”txtOfficeEmail” CssClass=”bodyTxtBold” ErrorMessage=”Invalid e-mail id.” ValidationExpression=”\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*” Display=”Dynamic” ValidationGroup=”tabContact”></asp:RegularExpressionValidator>

</ContentTemplate>

</cc1:TabPanel>

</cc1:TabContainer>

In this example, we have a two tab panel inside one tab container named tabPersonal and tabContact. In tabPersonal there is one textbox for user id and in tabContact there is also one textbox to take office e-mail id from user. Also note that we have given same ValidationGroup as the id of tab panel to each control.

Now to call these validations,

<asp:Button ID=”btnSave” runat=”server” ValidationGroup=”tabPersonal, tabContact” CommandName=”Insert” OnClick=”btnSave_Click” OnClientClick=”switchToTab();” />

We have one button as above; here we give both the tab panel id to the button’s ValidationGroup and on client click of button we have to call our JavaScript as OnClientClick=”switchToTab();”

Here is our JavaScript function,

function switchToTab(sender,args)

{

var tabContainer = $find(“<%= MainTabContainer.ClientID %>”);

var tab = $find(“<%= MainTabContainer.ClientID %>”).get_activeTabIndex();

switch (tab)

{

case 0:

if (Page_ClientValidate(‘tabPersonal’) == false)

{

$find(“<%= MainTabContainer.ClientID %>”).set_activeTabIndex(0);

$find(“<%= tabPersonal.ClientID %>”).set_activeTabIndex(0);

}

else if (Page_ClientValidate(‘tabContact’) == false)

{

$find(“<%= MainTabContainer.ClientID %>”).set_activeTabIndex(1);

$find(“<%= tabContact.ClientID %>”).set_activeTabIndex(0);

}

break;

case 1:

if (Page_ClientValidate(‘tabPersonal’) == false)

{

$find(“<%= MainTabContainer.ClientID %>”).set_activeTabIndex(0);

}

else if (Page_ClientValidate(‘tabContact’) == false)

{

$find(“<%= MainTabContainer.ClientID %>”).set_activeTabIndex(1);

$find(“<%= tabContact.ClientID %>”).set_activeTabIndex(0);

}

}

}

In some cases, set_activeTabIndex(0) method doesn’t work so instead of that we can also use another method to active current tab that is invalid is:

 tabContainer.set_activeTab(tabContainer.get_tabs()[TabIndex]);

2009
10/25
CATEGORY
ASP.NET
TAGS



1 comment

Alternative way to write dynamic SQL queries

Introduction

When we write Dynamic queries, most of the time only “WHERE” clause needs to be dynamic in the stored procedure rest of the part remains static, I mean “SELECT” and “FROM” Clause remain static most of the time.

To write dynamic query, we are choosing variables so that we can write “WHERE” clause using appropriate conditions and then putting a query in variables. Writing queries like this generates so much confusion while debugging and maintenance of the query.

Here is one of the process by which we can eliminate use of variable while writing queries which provide the same result as a dynamically written query provides.

We can use this technique if the “SELECT” list is going to be static and we need to take care of only “WHERE” clause. Otherwise we can use variables to store SQL query if the columns in the “SELECT” list will be generated dynamically and/or name of the source table will be decided at runtime. To understand this approach let us see one example.

Example

Original Stored Procedure:

We have one stored procedure as below

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

– exec [SC_Report_GetDataByCustomerLounge] 0,1,0,’2009-01-01′,’2009-03-01′

– exec [SC_Report_GetDataByCustomerLounge] 0,0,0,”,”

ALTER PROCEDURE [dbo].[SC_Report_GetDataByCustomerLounge]

@CustomerId int = 0,

@LoungeId int = 0,

@CountryId int = 0,

@FromDate datetime,

@ToDate datetime

AS

DECLARE @StrQuery nvarchar(3000)

DECLARE @WhereCondition nvarchar(3000)

SET @WhereCondition = ”

BEGIN

IF(@CustomerId != 0)

BEGIN

SET @WhereCondition = @WhereCondition + ‘ Trans.CustomerId = ‘ + Convert(varchar,@CustomerId)

END

IF(@LoungeId != 0 AND @WhereCondition != ”)

BEGIN

SET @WhereCondition = @WhereCondition + ‘ AND Trans.LoungeId = ‘ + Convert(varchar,@LoungeId)

END

ELSE IF(@LoungeId != 0)

BEGIN

SET @WhereCondition = @WhereCondition + ‘ Trans.LoungeId = ‘ + Convert(varchar,@LoungeId)

END

IF(@CountryId != 0 AND @WhereCondition != ”)

BEGIN

SET @WhereCondition = @WhereCondition + ‘ AND Trans.CountryId = ‘ + Convert(varchar,@CountryId)

END

ELSE IF(@CountryId != 0)

BEGIN

SET @WhereCondition = @WhereCondition + ‘ Trans.CountryId = ‘ + Convert(varchar,@CountryId)

END

IF (@FromDate != ‘01/01/1900′ AND @ToDate != ‘01/01/1900′ AND @WhereCondition != ”)

BEGIN

SET @WhereCondition = @WhereCondition + ‘ AND Convert(varchar(10),Trans.AccessDate,101) >= ”’ +

Convert(varchar,Convert(varchar(10),@FromDate,101)) + ”’ AND

Convert(varchar(10),Trans.AccessDate,101) <= ”’ +

Convert(varchar,Convert(varchar(10),@ToDate,101)) + ””

END

ELSE IF (@FromDate != ‘01/01/1900′ AND @ToDate != ‘01/01/1900′)

BEGIN

SET @WhereCondition = @WhereCondition + ‘ Convert(varchar(10),Trans.AccessDate,101) >= ”’ +

Convert(varchar,Convert(varchar(10),@FromDate,101)) + ”’ AND Convert(varchar(10),

Trans.AccessDate,101) <= ”’ + Convert(varchar,Convert(varchar(10),@ToDate,101)) + ””

END

IF(@WhereCondition != ”)

BEGIN

Set @StrQuery = ‘Select Trans.TransactionId,Lounge.LoungeId,Cust.CustomerId,

(Cust.FirstName + ” ” + Cust.MiddleName + ” ”+ Cust.LastName ) As CustomerName,

Lounge.LoungeName,City.CityName,Country.CountryName,Cust.Address1,Cust.Address2,

Cust.MobileNo,Cust.EmailAdress,Airport.AirportName,Trans.AccessDate,Trans.Remarks

from dbo.CustomerInformation as Cust

Inner join dbo.TransactionMaster Trans ON Cust.CustomerId = Trans.CustomerId

Inner join dbo.LoungeInformation as Lounge ON Lounge.LoungeId = Trans.LoungeId

Inner join dbo.CityInformation as City ON City.CityId = Trans.CityId

Inner join dbo.CountryInformation as Country ON Country.CountryId = Trans.CountryId

Inner join dbo.AirportInformation as Airport ON Airport.AirportId = Trans.AirportId

WHERE ‘ + @WhereCondition

END

ELSE

BEGIN

Set @StrQuery = ‘Select Trans.TransactionId,Lounge.LoungeId,Cust.CustomerId,

(Cust.FirstName + ” ” + Cust.MiddleName + ” ”+ Cust.LastName ) As CustomerName,

Lounge.LoungeName,City.CityName,Country.CountryName,Cust.Address1,Cust.Address2,

Cust.MobileNo,Cust.EmailAdress,Airport.AirportName,Trans.AccessDate,Trans.Remarks

from dbo.CustomerInformation as Cust

Inner join dbo.TransactionMaster Trans ON Cust.CustomerId = Trans.CustomerId

Inner join dbo.LoungeInformation as Lounge ON Lounge.LoungeId = Trans.LoungeId

Inner join dbo.CityInformation as City ON City.CityId = Trans.CityId

Inner join dbo.CountryInformation as Country ON Country.CountryId = Trans.CountryId

Inner join dbo.AirportInformation as Airport ON Airport.AirportId = Trans.AirportId ‘

END

PRINT @StrQuery

exec sp_executesql @StrQuery

END

From above stored procedure we can see that SELECT query part is static and the query is building dynamically as per change in the WHERE Clause. The above query gives me the following output.

Result before applying the approach

Result before applying the approach

Alternative way to write same query:

We will be able to replace the above stored procedure using the following stored procedure and that will give same result as previous one.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

– exec [SC_Report_GetDataByCustomerLounge] 0,1,0,’2009-01-01′,’2009-03-01′

– exec [SC_Report_GetDataByCustomerLounge] 0,0,0,”,”

ALTER PROCEDURE [dbo].[Test]

@CustomerId int = NULL,

@LoungeId int = NULL,

@CountryId int = NULL,

@FromDate datetime = NULL,

@ToDate datetime = NULL

AS

DECLARE @StrQuery nvarchar(3000)

DECLARE @WhereCondition nvarchar(3000)

SET @WhereCondition = ”

BEGIN

Select Trans.TransactionId,Lounge.LoungeId,Cust.CustomerId,

(Cust.FirstName + ‘ ‘ + Cust.MiddleName + ‘ ‘+ Cust.LastName ) As CustomerName,

Lounge.LoungeName,City.CityName,Country.CountryName,Cust.Address1,Cust.Address2,

Cust.MobileNo,Cust.EmailAdress,Airport.AirportName,Trans.AccessDate,Trans.Remarks

from dbo.CustomerInformation as Cust

Inner join dbo.TransactionMaster Trans ON Cust.CustomerId = Trans.CustomerId

Inner join dbo.LoungeInformation as Lounge ON Lounge.LoungeId = Trans.LoungeId

Inner join dbo.CityInformation as City ON City.CityId = Trans.CityId

Inner join dbo.CountryInformation as Country ON Country.CountryId = Trans.CountryId

Inner join dbo.AirportInformation as Airport ON Airport.AirportId = Trans.AirportId

WHERE (Trans.CustomerId = Convert(varchar,@CustomerId) OR Trans.CustomerId = NULL)

AND (Trans.LoungeId = Convert(varchar,@LoungeId) OR Trans.LoungeId = NULL)

AND (Trans.CountryId = Convert(varchar,@CountryId) OR Trans.CountryId = NULL)

AND (Convert(varchar(10),Trans.AccessDate,101) >= Convert(varchar,Convert(varchar(10),@FromDate,101))

AND Convert(varchar(10),Trans.AccessDate,101) <= Convert(varchar,Convert(varchar(10),@ToDate,101)))

END

The above stored procedure gives me the following output:

Result after applying the approach

Result after applying the approach

The benefits of using this technique are:

  • It will reduce the complexity of query.

  • Using this, we can easily maintain the query.

  • Syntax checking will be much simpler as most of the time quote(‘) gives problem to novice developers when query is stored in a variable.

  • And most important, when we use LINQ –to-SQL in our application structure it will reduce the conversion error between ISingleResult and INT in the case of writing dynamic query.

References:

www.SQLServerCentral.com

2009
07/06
CATEGORY
SQL Server
TAGS





Write comment

A Script to Find Latest Database backup

USE MASTER

GO

IF OBJECT_ID(‘USP_DBA_RECENTBACKUPS’) IS NOT NULL

DROP PROC USP_DBA_RECENTBACKUPS

GO

exec USP_DBA_RECENTBACKUPS ‘NTERP’,‘R’

CREATE PROC USP_DBA_RECENTBACKUPS

@P_DBNAME VARCHAR(200)= NULL,

@PLAN CHAR(1)= NULL

AS

BEGIN

DECLARE @DB_NAME VARCHAR(200)

SET @DB_NAME=@P_DBNAME

IF @DB_NAME IS NULL

BEGIN

WITH

BACKUP_RECENT AS

(

SELECT MAX(BACKUP_DATE) BACKUP_DATE,MAX(ID) ID,[TYPE],SERVER_NAME,DATABASE_NAME

FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

BACKUP_FINISH_DATE BACKUP_DATE, PHYSICAL_DEVICE_NAME,A.MEDIA_SET_ID,

SERVER_NAME,DATABASE_NAME,[TYPE]

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

) BACKUPS

GROUP BY [TYPE],SERVER_NAME,DATABASE_NAME

),

BACKUP_ALL AS

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

PHYSICAL_DEVICE_NAME

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

)

SELECT SERVER_NAME [SERVER],DATABASE_NAME [DATABASE],BAKUPTYPE=

CASE WHEN [TYPE]=‘D’ THEN ‘FULL’

WHEN [TYPE]=‘I’ THEN ‘DIFFERENTIAL’

WHEN [TYPE]=‘L’ THEN ‘LOG’

WHEN [TYPE]=‘F’ THEN ‘FILE / FILEGROUP’

WHEN [TYPE]=‘G’ THEN ‘DIFFERENTIAL FILE’

WHEN [TYPE]=‘P’ THEN ‘PARTIAL’

WHEN [TYPE]=‘Q’ THEN ‘DIFFERENTIAL PARTIAL’

END,BACKUP_DATE [RECENT BACKUP], PHYSICAL_DEVICE_NAME [LOCATION] FROM BACKUP_RECENT,BACKUP_ALL

WHERE BACKUP_RECENT.ID=BACKUP_ALL.ID

ORDER BY SERVER_NAME,DATABASE_NAME,BACKUP_DATE

END

ELSE

BEGIN

IF @PLAN = ‘R’

BEGIN

WITH

BACKUP_RECENT AS

(

SELECT MAX(BACKUP_DATE) BACKUP_DATE,MAX(ID) ID,[TYPE],SERVER_NAME,DATABASE_NAME

FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

BACKUP_FINISH_DATE BACKUP_DATE, PHYSICAL_DEVICE_NAME,A.MEDIA_SET_ID,

SERVER_NAME,DATABASE_NAME,[TYPE]

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

) BACKUPS

GROUP BY [TYPE],SERVER_NAME,DATABASE_NAME

),

BACKUP_ALL AS

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

PHYSICAL_DEVICE_NAME

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

)

SELECT * FROM(

SELECT SERVER_NAME [SERVER],DATABASE_NAME [DATABASE],BAKUPTYPE=

CASE WHEN [TYPE]=‘D’ THEN ‘FULL’

WHEN [TYPE]=‘I’ THEN ‘DIFFERENTIAL’

WHEN [TYPE]=‘L’ THEN ‘LOG’

WHEN [TYPE]=‘F’ THEN ‘FILE / FILEGROUP’

WHEN [TYPE]=‘G’ THEN ‘DIFFERENTIAL FILE’

WHEN [TYPE]=‘P’ THEN ‘PARTIAL’

WHEN [TYPE]=‘Q’ THEN ‘DIFFERENTIAL PARTIAL’

END,BACKUP_DATE [RECENT BACKUP], PHYSICAL_DEVICE_NAME [LOCATION] FROM BACKUP_RECENT,BACKUP_ALL

WHERE BACKUP_RECENT.ID=BACKUP_ALL.ID AND DATABASE_NAME=@P_DBNAME AND [TYPE] IN (‘D’,‘I’)

UNION

SELECT

SERVER_NAME,DATABASE_NAME,BAKUPTYPE=‘LOG’,

BACKUP_FINISH_DATE BACKUP_DATE,PHYSICAL_DEVICE_NAME [LOCATION]

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

WHERE [TYPE]=‘L’

AND BACKUP_FINISH_DATE>

(

SELECT TOP 1 BACKUP_FINISH_DATE FROM MSDB.DBO.BACKUPSET WHERE [TYPE] IN (‘D’,‘I’)

AND DATABASE_NAME= @DB_NAME ORDER BY BACKUP_FINISH_DATE DESC

)

AND DATABASE_NAME= @DB_NAME ) AS RESTORE_PLAN

ORDER BY [RECENT BACKUP]

END

ELSE

BEGIN

WITH

BACKUP_RECENT AS

(

SELECT MAX(BACKUP_DATE) BACKUP_DATE,MAX(ID) ID,[TYPE],SERVER_NAME,DATABASE_NAME

FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

BACKUP_FINISH_DATE BACKUP_DATE, PHYSICAL_DEVICE_NAME,A.MEDIA_SET_ID,

SERVER_NAME,DATABASE_NAME,[TYPE]

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

) BACKUPS

GROUP BY [TYPE],SERVER_NAME,DATABASE_NAME

),

BACKUP_ALL AS

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

PHYSICAL_DEVICE_NAME

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

)

SELECT SERVER_NAME [SERVER],DATABASE_NAME [DATABASE],BAKUPTYPE=

CASE WHEN [TYPE]=‘D’ THEN ‘FULL’

WHEN [TYPE]=‘I’ THEN ‘DIFFERENTIAL’

WHEN [TYPE]=‘L’ THEN ‘LOG’

WHEN [TYPE]=‘F’ THEN ‘FILE / FILEGROUP’

WHEN [TYPE]=‘G’ THEN ‘DIFFERENTIAL FILE’

WHEN [TYPE]=‘P’ THEN ‘PARTIAL’

WHEN [TYPE]=‘Q’ THEN ‘DIFFERENTIAL PARTIAL’

END,BACKUP_DATE [RECENT BACKUP], PHYSICAL_DEVICE_NAME [LOCATION] FROM BACKUP_RECENT,BACKUP_ALL

WHERE BACKUP_RECENT.ID=BACKUP_ALL.ID AND DATABASE_NAME=@P_DBNAME

ORDER BY BACKUP_DATE

END

END

END

Referecne : www.SQLServerCentral.com

2009
07/03
CATEGORY
SQL Server
1 comment

Which one to choose for Remote Connection ?

Go To Meeting

LogMeIn

TeamViewer

Web Ex
Encryption Your GoToMeeting session is completely private and secure. GoToMeeting uses built-in, always-on security measures to protect user privacy and access to data and computers. It is the only online meeting solution that uses end-to-end 128-bit AES (Advanced Encryption Standard) encryption for all meeting data, which includes screen images, files, keyboard and mouse input and chat text. The SSL/TLS standard defines a wide choice of cipher suites such as RC4 and 3DES, and some implementations offer more advanced suites such as AES as well. RC4 operates on 128 bit keys, 3DES uses 168 bit keys. AES can support 128 or 256 bitkeys. TeamViewer works with a complete encryption based on RSA public/private key exchange and AES (256 Bit) session encoding. This technology is used in a comparable form for https/SSL and can be considered completely safe by today’s standards. As the private key never leaves the client computer, it is ensured by this procedure that interconnected computers – including the TeamViewer routing servers – cannot decipher the data stream. provides the option of securing all session content with 128-bit encryption using Secure Sockets Layer (SSL), which is the most widely used Internet standard for securing sensitive data communications. With SSL, WebEx encrypts all data within a WebEx Service cluster, including the WebEx session data. This will prevent third parties from accessing any data in transit.
Transmission Security SSL & AES SSL & Proprietary SSL SSL & Proprietary
Access security Meeting ID Session ID ParentID and Session password Meeting ID and password
Record Sequence Numbering SSL/TLS records are numbered by the sender and the order is checked by the receiver. This ensures that an attacker cannot remove or insert arbitrary records into the data stream. same as logmein
Message Authentication Codes (MACs) Every SSL/TLS record ends with a message authentication code that is derived from the session key (known only to the two communicating parties) and the data contained within the record. If MAC verification fails it is assumed that the data was modified in transit. same as logmein
Cipher Block Chaining (CBC mode) The cipher suites preferred by RemotelyAnywhere utilize Cipher Block Chaining, meaning that every SSL/TLS record will depend on the contents of the previous record. In this mode, the input to the cipher is not only the current plain text record but the previous one as well. This again ensures that packets cannot be inserted or removed from the data stream. same as logmein
As per my research on the web, I think LogMeIn is best among the four as it provides 256-bit encryption and interusion detection
2009
06/16
CATEGORY
Remote Connection
Write comment

Hello world!

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!

2009
06/16
CATEGORY
Uncategorized
1 comment