Finding Duplicate Records in SQL and Deleting Duplicate Records

SELECT REQUEST_ID, COUNT(*) AS [DupeCount]
FROM REQUESTDETAILS  where UPLOAD_DATE >= CONVERT(date,getdate()-2,101)
and CLIENTNAME = 'lluh'
GROUP BY REQUEST_ID
HAVING COUNT(*) > 1

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

SELECT *
FROM REQUESTDETAILS
WHERE CLIENTNAME = 'lluh'  and REQUEST_ID IN
(
      SELECT REQUEST_ID
      FROM REQUESTDETAILS
      where UPLOAD_DATE >= CONVERT(date,getdate()-2,101)
and CLIENTNAME = 'lluh'
      GROUP BY REQUEST_ID
      HAVING COUNT(*) > 1
)
ORDER BY REQUEST_ID

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


WITH CTE (REQUEST_ID, DuplicateCount)
AS
(
SELECT REQUEST_ID,
ROW_NUMBER() OVER(PARTITION BY REQUEST_ID ORDER BY REQUEST_ID) AS DuplicateCount
FROM REQUESTDETAILS  where UPLOAD_DATE >= CONVERT(date,getdate()-2,101)
and CLIENTNAME = 'lluh'
)
DELETE

Read More

Shedule the Database backup in Sql Server

Schedule the database backup

First I am going to tell you the steps required to schedule the backup. Login to Sql Management studio and connect to the required database. Now from the object explorer, make sure SQL server agent is running, if not start SQL server agent(Right click and press start).

Expand the Management Node from the object explorer, and then select the maintenance plan node. To schedule maintenance plan, you need to have “SYSADMIN” database role. If you dont see the maintenance node, make sure you have the necessary permission. 

Right click the maintenance plan and then select “new maintenance plan”.

Enter the maintenance plan name in the popup box (This can be any name that identifies your task for ). This will identify your backup plan and you should choose a relevant name that suits your plan.

Now you will be in the configuration page for the maintenance plan. . Note the marked area, these are the two areas you need to use for setting up the maintenance plan. The marked area in the right top will be used to configure the time that the plan executes. Choose a time so that the database is least used. The bottom left pane shows the tasks that can be utilized to create an sql maintenance plan. since explaining all of them is not in the scope of this document, I am going to explore only two of them.

Click on the calendar item shown in the right side top. This will bring the job schedule properties popup window that configure the execution time/frequency of the tasks. Configure the data carefully so that it suits your requirement. Usually database backups are taken daily basis. Make sure you are selecting proper time so that your database is least used. Click ok once you finish.

From the maintenance plan tasks pane in the left side, select the backup database plan, this will be used to take backups for the databases. Drag and drop backup database task to the right side(as shown in the diagram).

Double click on the backup database task, it will open up a new window that allows you to configure the database configuration for the backup. Here you configure the databases that you need to backup, then specify a location for the backup, specify the extension for the backup files etc.

From the pop up modal window, by clicking on “Databases” dropdown, you will be able to select the required databases. Also configure the file location, extension for the backup file etc.

Click ok once finished. Now backup plan configuration is over. The backup files will be created on the scheduled time to the mentioned folder. The name of the file will be created by appending the date so that you can identify the back up for a particular date.

Since the backup files are created frequently,… it is a good practice that you delete backup files after a certain period of time. For this you need to execute clean up task  along with the maintenance plan. You can configure the clean up task as follows.

From the left side pane, drag and drop maintenance cleanup task.

Double click on the dropped item inorder to edit the clean up properties. Here you need to specify the backup location, and file extension for the back up files and specify the age of the file. It is a good practice that you keep one month old data, and delete anything prior to one month. 

Once you click ok, then save the maintenance plan. You can either wait till the next execution time or execute it manually inorder to check whether everything is working fine.

Hope this helps.

Read More

Using Oracle Providers for ASP.NET

Purpose

This tutorial demonstrates Oracle Providers for ASP.NET usage and how tightly the providers integrate with existing ASP.NET management tools, controls, and services.
Overview

Oracle Providers for ASP.NET integrates directly with Microsoft ASP.NET controls and services to provide state management capabilities for web sites. State management is critical for managing persistent data, such as user information, user roles, and session information.

This tutorial builds upon the "Building ASP.NET Web Applications with Oracle Developer Tools for Visual Studio" tutorial. In that tutorial, you created a data grid retrieving employee information. In this tutorial, you now want to secure this employee data to ensure only authorized users can view the data. To accomplish this task, you will create an ASP.NET web user using the Oracle Membership Provider and test the web site's authentication with the provider with valid and invalid credentials.
Prerequisites

Before starting this tutorial, you should:

1 .	Install Microsoft Visual Studio 2010
2 .	Install Oracle Database 9.2 or later or Oracle Database XE
3 .	Install Oracle 11g Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio version 11.2.0.1.2 or later from OTN
4 .	Complete the "Building ASP.NET Web Applications With Oracle Developer Tools for Visual Studio" OBE

Enabling Oracle Providers for ASP.NET and Creating a Web User

In this section, you will use the ASP.NET Web Site Administration Tool to direct the web site to use the Oracle ASP.NET providers and create a new web user specific to this web site to demonstrate authentication features. Perform the following steps:

1 .	Reopen the website you created in "Building ASP.NET Web Applications With Oracle Developer Tools for Visual Studio"
2 .	Select View > Solution Explorer Under the root website node double click on Web.config
3 .	In the Web.config file, select all the tags from the <membership> tag to the </roleManager> closing tag and click the Delete key to delete all the selected tags. Note: By default, these web.config entries have been pre-populated to use SQL Server ASP.NET providers. By deleting these entries, we can now configure them to use Oracle's ASP.NET providers.
4 .	Select Website > ASP.NET Configuration.
5 .	The ASP.NET Web Site Administration Tool appears in a browser window. Select the Provider tab.
6 .	On the Provider page, select the second link: Select a different provider for each feature (advanced).
7 .	When the Provider page reappears, change the Membership Provider to OracleMembershipProvider and Role Provider to OracleRoleProvider and select the Security tab.
8 .	Under Users section in the Security tab, click Select authentication type. By default, the ASP.NET site uses Windows authentication to identify users. You are building a web site that will identify users by their site-specific logins and passwords. Therefore, the site must be configured to expect to use logins and passwords.
9 .	Select From the internet and click Done.
10 .	Select Create user.
11 .	In the Create User section, enter your name and a password that contains at least 7 characters, including one non-alphanumeric character. Enter your email, and a security question and answer, then click Create User.
12 .	The user account has been created. Click the Security tab. Note: If you click Continue, you will be allowed to enter another user.
13 .	Note that there is now one existing user. Under Access Rules, select Manage access rules.
14 .	Select Add new access rule.
15 .	Select Anonymous users and Deny, then click OK. By default, anonymous access to the web site is enabled. The above settings secure the web site by disabling anonymous access. Now, only authenticated users can view the employee data.
16 .	The access rule was created successfully. The web site will now deny anonymous users access to the site. ClickDone.
17 .	Close the browser.

Testing Web Site Authentication

Now that you have created a web user specific to this web site, the web site will allow this user access to the employee data and deny access to all other users, including anonymous users. In this section, you will attempt to access the employee data as an anonymous user, as an unauthorized user, as an authorized user with an incorrect password, and finally as an authorized user with the correct password. Only in the last scenario will the web site grant access to the employee data. Perform the following steps:

Note: Five or more consecutive invalid passwords entered for an ASP.NET provider user within a ten minute period will lock the account to prevent unauthorized users from gaining access through password guessing. Oracle Membership Provider sets these security measures through the following properties, which you can modify in the machine.config file or web.config files: MaxInvalidPasswordAttempts (default: 5 attempts) and PasswordAttemptWindow (default: 10 minutes). If the account is locked, then you can unlock the user by calling the UnlockUser method.

1 .	Switch to Visual Studio. A message box will pop up stating that web.config has changed. Select "Yes" to accept the changes.
2 .	Examine the Web.config in the editor. Note that several entries for Oracle providers have been added. These provider values simply default to entries that were added earlier to the machine.config by the Oracle installer. Note: If you wanted to configure the Oracle providers differently for each separate web site, (for example, changing password length requirements for the membership provider) you could copy the entries from the Machine.config into the Web.config.
3 .	Select Debug > Start Without Debugging.
4 .	The login web page appears.
5 .	Enter some other username and password which is different than the user you created earlier in the ASP.NET Web Site Administration Tool. Then click Log In.
6 .	Notice that you receive an error indicating that the user you entered is not valid. The web site correctly denies access to the user.
7 .	Enter the user name you entered in the in the ASP.NET Web Site Administration Tool but with an incorrect password for that web site user. Click Log In. As the screen shot indicates, the user is denied access, demonstrating that the control could not verify this user's credentials with those stored by the Oracle Membership Provider.
8 .	Enter the correct username and password for the web site user. Click Log In. The employee data appears. This demonstrates that only authorized users can access the data. Thus, Oracle Providers for ASP.NET provided web site security in a very simple manner. You have now built a data-driven ASP.NET web application. It performs authentication and retrieves employee data from the database.

Read More

Display Line Numbers in Visual Studio 2008 and 2010

Menu:  Tools -> Options -> Text Editor -> All Languages -> General -> Display

Versions:  2008,2010
Published:  3/7/2010
Code:  vstipEdit0025

Line numbers are not on by default.  To turn on line numbers just go to Tools -> Options -> Text Editor -> All Languages -> General -> Display and check Line numbers:

Read More