Tuesday, December 27, 2011

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.


Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

 

In an effort to improve security Microsoft has turned many features "off by default".  This is a big change from the old policy of turning every feature on so that developers weren't discouraged from using the feature due to difficulties in getting the feature to work.

Solution: Enable the server option 'clr enabled'

The script is pretty simple.  Here it is:
EXEC sp_configure 'show advanced options' , '1';
go
reconfigure;
go
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;
-- Turn advanced options back off
EXEC sp_configure 'show advanced options' , '0';
go
Once you have the CLR enabled, you can write user-defined functions, triggers, stored procedures, user-defined aggregates, and user-defined types in VB.Net and C#.

Monday, December 26, 2011

Insert Data into same Table from table with another column values

step 1:  first  we have to insert data into table
insert into emp select * from emp where id=2


step 2:  then create CTE like this in which we give row_number for each rows for identity of rows

SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE

WITH DuplicateRecords AS
(
SELECT *,row_number() OVER( ORDER BY name
)
AS RowNumber FROM emp where id=2
)

step 3 :  then we update our desired rows
update DuplicateRecords set id=31 WHERE RowNumber>1

Saturday, December 24, 2011

Set Session TimeOut In Asp.Net



1. Set session timeout in IIS.

Open IIS manager by typing inetmgr in Start > run in windows.

Right click on websites > Select Properties.

Go to ASP.NET tab, Click on Edit Configuration.

Click on State Management Tab, Set Session TimeOut value (In Minutes).

2. Set Session TimeOut in Web.Config

We can set session timeout in SessionState section of web.config file as mentioned below, timeout value is in minutes.

<system.web>
    <sessionState mode="InProc" cookieless="false"timeout="15">
    </sessionState>
</system.web>

3. Set session timeout in Global.asax

void Session_Start(object sender, EventArgs e)
{
  // Code that runs when a new session is started
  Session.Timeout = 15;
}

Thursday, December 22, 2011

How to Use LightBox

i am giving some steps to implement LightBox in your page.

 1.  Download good looking CSS and JAVASCRIPT from internet 
       
2.     create new folder and give it name "LightBox Tutorial"   at any where in your hard drives.

3.     paste LightBox downloaded Zipped file in "LightBox Tutorial" folder.

4.    rightclick on  lightbox2.05.zip   then click extract here.

5.  paste any 4 jpg file (small size) in "LightBox Tutorial" folder..
     example.  1.jpg,2.jpg,3.jpg,4.jpg 

6.  open notepad 

7.
    <html>
<head>
<title>Light Box Tutorial</title>
<link href="css/lightbox.css" rel="stylesheet" type="text/css"/>
<script type="text/javascript" src="js/prototype.js"></script>
<script type="text/javascript" src="js/scriptaculous.js?load=effects,builder"></script>
<script type="text/javascript" src="js/lightbox.js"></script>
</head>
<body>
<a href="1.jpg" rel="lightbox[adi]"><img src="1.jpg"></a>
<a href="2.jpg" rel="lightbox[adi]"><img src="2.jpg"></a>
<a href="3.jpg" rel="lightbox[adi]"><img src="3.jpg"></a>
<a href="4.jpg" rel="lightbox[adi]"><img src="4.jpg"></a>
<a href="5.jpg" rel="lightbox[adi]"><img src="5.jpg"></a>
</body>
</html>

8. paste html code in notepad  written in step 7.

9. save it aditya.html

10. open aditya.html in internet and enjoye.....


NOTE:--  in <Head> section we link our css file.
                 and add javascript file using <script> tage in <head> section.
                 in <a> tage section we are using rel ="lightbox[adi]" for relate lightbox transition.
                 in place of "lightbox[adi]" we can use "lightbox"  then we cant use NEXT button on image .

DownLoad Tutorial 








Tuesday, December 20, 2011

DateTime.ToString() Patterns in .Net

DateTime.ToString() Patterns

All the patterns:

0 MM/dd/yyyy 08/22/2006
1 dddd, dd MMMM yyyy Tuesday, 22 August 2006
2 dddd, dd MMMM yyyy HH:mm Tuesday, 22 August 2006 06:30
3 dddd, dd MMMM yyyy hh:mm tt Tuesday, 22 August 2006 06:30 AM
4 dddd, dd MMMM yyyy H:mm Tuesday, 22 August 2006 6:30
5 dddd, dd MMMM yyyy h:mm tt Tuesday, 22 August 2006 6:30 AM
6 dddd, dd MMMM yyyy HH:mm:ss Tuesday, 22 August 2006 06:30:07
7 MM/dd/yyyy HH:mm 08/22/2006 06:30
8 MM/dd/yyyy hh:mm tt 08/22/2006 06:30 AM
9 MM/dd/yyyy H:mm 08/22/2006 6:30
10 MM/dd/yyyy h:mm tt 08/22/2006 6:30 AM
10 MM/dd/yyyy h:mm tt 08/22/2006 6:30 AM
10 MM/dd/yyyy h:mm tt 08/22/2006 6:30 AM
11 MM/dd/yyyy HH:mm:ss 08/22/2006 06:30:07
12 MMMM dd August 22
13 MMMM dd August 22
14 yyyy'-'MM'-'dd'T'HH':'mm':'ss.fffffffK 2006-08-22T06:30:07.7199222-04:00
15 yyyy'-'MM'-'dd'T'HH':'mm':'ss.fffffffK 2006-08-22T06:30:07.7199222-04:00
16 ddd, dd MMM yyyy HH':'mm':'ss 'GMT' Tue, 22 Aug 2006 06:30:07 GMT
17 ddd, dd MMM yyyy HH':'mm':'ss 'GMT' Tue, 22 Aug 2006 06:30:07 GMT
18 yyyy'-'MM'-'dd'T'HH':'mm':'ss 2006-08-22T06:30:07
19 HH:mm 06:30
20 hh:mm tt 06:30 AM
21 H:mm 6:30
22 h:mm tt 6:30 AM
23 HH:mm:ss 06:30:07
24 yyyy'-'MM'-'dd HH':'mm':'ss'Z' 2006-08-22 06:30:07Z
25 dddd, dd MMMM yyyy HH:mm:ss Tuesday, 22 August 2006 06:30:07
26 yyyy MMMM 2006 August
27 yyyy MMMM 2006 August

The patterns for DateTime.ToString ( 'd' ) :

0 MM/dd/yyyy 08/22/2006

The patterns for DateTime.ToString ( 'D' ) :

0 dddd, dd MMMM yyyy Tuesday, 22 August 2006

The patterns for DateTime.ToString ( 'f' ) :

0 dddd, dd MMMM yyyy HH:mm Tuesday, 22 August 2006 06:30
1 dddd, dd MMMM yyyy hh:mm tt Tuesday, 22 August 2006 06:30 AM
2 dddd, dd MMMM yyyy H:mm Tuesday, 22 August 2006 6:30
3 dddd, dd MMMM yyyy h:mm tt Tuesday, 22 August 2006 6:30 AM

The patterns for DateTime.ToString ( 'F' ) :

0 dddd, dd MMMM yyyy HH:mm:ss Tuesday, 22 August 2006 06:30:07

The patterns for DateTime.ToString ( 'g' ) :

0 MM/dd/yyyy HH:mm 08/22/2006 06:30
1 MM/dd/yyyy hh:mm tt 08/22/2006 06:30 AM
2 MM/dd/yyyy H:mm 08/22/2006 6:30
3 MM/dd/yyyy h:mm tt 08/22/2006 6:30 AM

The patterns for DateTime.ToString ( 'G' ) :

0 MM/dd/yyyy HH:mm:ss 08/22/2006 06:30:07

The patterns for DateTime.ToString ( 'm' ) :

0 MMMM dd August 22

The patterns for DateTime.ToString ( 'r' ) :

0 ddd, dd MMM yyyy HH':'mm':'ss 'GMT' Tue, 22 Aug 2006 06:30:07 GMT

The patterns for DateTime.ToString ( 's' ) :

0 yyyy'-'MM'-'dd'T'HH':'mm':'ss 2006-08-22T06:30:07

The patterns for DateTime.ToString ( 'u' ) :

0 yyyy'-'MM'-'dd HH':'mm':'ss'Z' 2006-08-22 06:30:07Z

The patterns for DateTime.ToString ( 'U' ) :

0 dddd, dd MMMM yyyy HH:mm:ss Tuesday, 22 August 2006 06:30:07

The patterns for DateTime.ToString ( 'y' ) :

0 yyyy MMMM 2006 August

Building a custom DateTime.ToString Patterns

The following details the meaning of each pattern character. Note the K and z character.
d Represents the day of the month as a number from 1 through 31. A single-digit day is formatted without a leading zero
dd Represents the day of the month as a number from 01 through 31. A single-digit day is formatted with a leading zero
ddd Represents the abbreviated name of the day of the week (Mon, Tues, Wed etc)
dddd Represents the full name of the day of the week (Monday, Tuesday etc)
h 12-hour clock hour (e.g. 7)
hh 12-hour clock, with a leading 0 (e.g. 07)
H 24-hour clock hour (e.g. 19)
HH 24-hour clock hour, with a leading 0 (e.g. 19)
m Minutes
mm Minutes with a leading zero
M Month number
MM Month number with leading zero
MMM Abbreviated Month Name (e.g. Dec)
MMMM Full month name (e.g. December)
s Seconds
ss Seconds with leading zero
t Abbreviated AM / PM (e.g. A or P)
tt AM / PM (e.g. AM or PM
y Year, no leading zero (e.g. 2001 would be 1)
yy Year, leadin zero (e.g. 2001 would be 01)
yyy Year, (e.g. 2001 would be 2001)
yyyy Year, (e.g. 2001 would be 2001)
K Represents the time zone information of a date and time value (e.g. +05:00)
z With DateTime values, represents the signed offset of the local operating system's time zone from Coordinated Universal Time (UTC), measured in hours. (e.g. +6)
zz As z but with leadin zero (e.g. +06)
zzz With DateTime values, represents the signed offset of the local operating system's time zone from UTC, measured in hours and minutes. (e.g. +06:00)
f Represents the most significant digit of the seconds fraction; that is, it represents the tenths of a second in a date and time value.
ff Represents the two most significant digits of the seconds fraction; that is, it represents the hundredths of a second in a date and time value.
fff Represents the three most significant digits of the seconds fraction; that is, it represents the milliseconds in a date and time value.
ffff Represents the four most significant digits of the seconds fraction; that is, it represents the ten thousandths of a second in a date and time value. While it is possible to display the ten thousandths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock's resolution is approximately 10-15 milliseconds.
fffff Represents the five most significant digits of the seconds fraction; that is, it represents the hundred thousandths of a second in a date and time value. While it is possible to display the hundred thousandths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock's resolution is approximately 10-15 milliseconds.
ffffff Represents the six most significant digits of the seconds fraction; that is, it represents the millionths of a second in a date and time value. While it is possible to display the millionths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock's resolution is approximately 10-15 milliseconds.
fffffff Represents the seven most significant digits of the seconds fraction; that is, it represents the ten millionths of a second in a date and time value. While it is possible to display the ten millionths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock's resolution is approximately 10-15 milliseconds.
F Represents the most significant digit of the seconds fraction; that is, it represents the tenths of a second in a date and time value. Nothing is displayed if the digit is zero.
: Represents the time separator defined in the current DateTimeFormatInfo..::.TimeSeparator property. This separator is used to differentiate hours, minutes, and seconds.
/ Represents the date separator defined in the current DateTimeFormatInfo..::.DateSeparator property. This separator is used to differentiate years, months, and days.
" Represents a quoted string (quotation mark). Displays the literal value of any string between two quotation marks ("). Your application should precede each quotation mark with an escape character (\).
' Represents a quoted string (apostrophe). Displays the literal value of any string between two apostrophe (') characters.
%c Represents the result associated with a c custom format specifier, when the custom date and time format string consists solely of that custom format specifier. That is, to use the d, f, F, h, m, s, t, y, z, H, or M custom format specifier by itself, the application should specify %d, %f, %F, %h, %m, %s, %t, %y, %z, %H, or %M. For more information about using a single format specifier, see Using Single Custom Format Specifiers.
||\c || Represents the escape character, and displays the character "c" as a literal when that character is preceded by the escape character (\). To insert the backslash character itself in the result string, the application should use two escape characters ("\\").
Any other character copies any other character to the result string, without affecting formatting.

SSRS Reporting Services Tips & Tricks

1 - Date formatting
If you have a string parameter coming in to your report (from a stored procedure for example) and need it displayed nicely, try using the expression:
=CDate(Fields!MeetingDate.Value).ToString("dd/MM/yyyy")

You will get minimum date values being displayed if the date is null for particular search criteria. If this is a problem for you, try using:
=IIF(IsDate(Fields!MeetingDate.Value),CDate(Fields!MeetingDate.Value).ToString("dd/MM/yyyy"),"")

Obviously in both these cases you can insert whatever format you'd like in place of "dd/MM/yyyy" :)

2 - IIF statement gotcha
Beware of using the IIF statement above - it works beautifully if the string is either null or a valid date but if the string is something like "tomorrow" (or even an empty string) then you will see "#Error" in your report. This is because IIF statements execute both paths when evaluating (they're not lazy enough!) so Reporting Services tries quite hard to convert "tomorrow" to a date and falls over when it can't. If you need to get around this try writing your own function (Report/Report Properties/Code) using more standard .NET IF statements and call it from your expression. Otherwise, just be aware and be careful when using IIF.

3 - Newlines in text boxes
If you're using an expression to build up a paragraph or two of text in a text box and need a new line, use this:
VBCRLF (Visual Basic Carriage Return Line Feed for those who're interested!)

E.g. ="This is line 1" + VBCRLF + "This isn't" will produce:
This is line 1
This isn't

Gotcha: If VBCRLF is the first or last thing in your text box, Reporting Services will ignore it (I have no idea why!). Make sure you put in one extra VBCRLF at the beginning or end if you're using them there.

4 - Checkboxes
I would have thought it would be fairly common to want to display a check box on a report which is checked based on a value in the dataset but it would appear that it's not so common as there is no control provided to handle this. If your client, like mine, really wants a checkbox displayed on a report, you'll need to use wingdings. Yes, you heard right, add a small text box to your report, set the font to wingdings and use something like the following expression:
=iif(Fields!BookingMade.Value = "1",chr(0254),"o")

Feel free to play around with different wingdings characters, but I found these two gave me a nice looking checkbox that stayed the same size whether it was checked or not!

5 - Alternating row colours
Large tables look better and are easier to read if you shade alternate rows. This is easily accomplished by adding the following to the BackgroundColour expression of your detail row (substituting your desired colours of course):
=iif(RowNumber(Nothing) Mod 2, "#eefafd", "White")

I had a situation recently where my client needed alternating row colours but, for a variety of reasons, the rows displayed on the report were all group header rows. Since the RowNumber resets at every group all my rows stayed white. Using the following expression gave me header rows with alternating colours (the IDNumber is what I was grouping on):
=iif(RunningValue(Fields!IDNumber.Value, CountDistinct, nothing) mod 2,"#eefafd","White")

For More Info 

For More Info 2 

Monday, December 19, 2011

A Potentially Dangerous Request.Form Value Was Detected From Client

A potentially dangerous Request.Form value was detected from the client.

This A potentially dangerous Request.Form value was detected from the client error occurs when user enter any script tag <> like or any html <> tag like etc in taxtbox.

Reason for this is asp.net prevent any attempt to compromise the security of application by script injection like cross site scriptiong etc through textbox.

To resolve this issue we can do following things.



1. Set validateRequest property to false in page directive.


<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs"
Inherits="_Default" ValidateRequest="false" %>

2.You can set ValidateRequest property to false in web.config if you want to turn validateRequest off for the whole application
<configuration>
  <configuration>
    <system.web>
      <pages validateRequest="false" />
    </system.web>
</configuration>

3. If you don't want to set validateRequest to false then use methods like regular expression or replace() to check for any special character or script tag entry in text box

<asp:textbox id="TextBox2" runat="server"
             onblur="this.value = this.value.replace(/&lt;\/?[^>]+>/gi, '');">
</asp:textbox>

Create Water Mark Text Box using JavaScript

we can create this using JavaScript code is given below:
----------------------------------------------------------------------------------
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
 
         <script language="javascript" type="text/javascript">
        function Focus(objname, waterMarkText) {
            obj = document.getElementById(objname);
          
                obj.value = "";
                if (obj.value == "Aditya" || obj.value == "" || obj.value == null) {
                    obj.style.color = "black";
             
            }
        }
        function Blur(objname, waterMarkText) {
            obj = document.getElementById(objname);
            if (obj.value == "") {
                obj.value = waterMarkText;
                                             
            }
                  if (obj.value == "Aditya" || obj.value == "" || obj.value == null) {
                obj.style.color = "gray";
            }
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
      <table>
        <tr>
            <td>
                User Name
            </td>
            <td>
                <asp:TextBox ID="txtUserId" runat="server"
        onfocus="Focus(this.id,'Aditya')"
                    onblur="Blur(this.id,'Aditya')"
            Width="126px" style="background-color:pink" >Aditya</asp:TextBox>
            </td>
        </tr>
      </table>
    </form>
</body>
</html>
------------------------------------------------------------------------------------
try this

Delete Duplicate Records Or Rows - Sql Server

Delete/Remove duplicate records from MS Sql Server      
  we have 3 methods to do this
like we have table emp with column fname,dept
 table:  emp
fname           dept 
aditya           mca
abhishek       mca
akhilesh        mca
anand           mba
ram niwas     mba
aditya          mca
abhishek      mca
arbind          btech

(aditya mca) & (abhishek mca)  rows are deuplicates in above table so we have three methods to remove duplicacy


Method 1:- 
by using Identity Column

step1.   create a identity column in our table

alter table emp add id int identity(1,1) 

step 2 write this query 

delete from emp 
where id not in  (select min(id)
from  emp  group by fname,dept)

Method 2:-

Delete duplicate records using Row_Number()

 If you do not want to make any changes in table design or don't want to create identity column on table then you can remove duplicate records using Row_Number in sql server 2005 onwards.


WITH DuplicateRecords AS
(
SELECT *,row_number() OVER(PARTITION BY fname,dept ORDER BY fname 
)
AS RowNumber FROMemp
)
DELETE FROM DuplicateRecords WHERE RowNumber>1
  
Method 3:-

Remove duplicate rows/Records using temporary table

by using distinct


SELECT distinct * into TempTable FROM emp
GROUP BY fname,dept
HAVING COUNT(fname) > 1
DELETE emp WHERE fname
IN (SELECT fname FROM TempTable)
INSERT emp SELECT * FROM TempTable
DROP TABLE TempTable

 


 

 



Sunday, December 18, 2011

the difference between DDL, DML and DCL commands?

DDL
Data Definition Language (DDL)
statements are used to define the database
structure or schema. Some examples:
CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces
allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object
DML
Data Manipulation Language (DML) statements are used for managing data
within schema objects. Some examples:
SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records
remain
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency
DCL
Data Control Language (DCL)
statements. Some examples:
GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command
TCL
Transaction Control (TCL) statements are used to manage the changes made by
DML statements. It allows statements to be grouped together into logical
Transactions.
COMMIT - save work done
SAVEPOINT - identify a point in a transaction to which you can later roll
back
ROLLBACK - restore database to original since the last COMMIT
SET TRANSACTION - Change transaction options like isolation level and
what rollback segment to use

Could not load file or assembly 'AjaxControlToolkit' or one of its dependencies. The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_IN

Could not load file or assembly 'AjaxControlToolkit' or one of its dependencies. The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_IN
Problem :
Could not load file or assembly … The parameter is incorrect … HRESULT: 0x80070057 (E_INVALIDARG)

On Computer ShutDown due to power failure got the above mentioned error
Could not load file or assembly 'AjaxControlToolkit' or one of its dependencies. The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG))


Solution :

Delete Relevant OR All Files from
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files.
OR
C:\Users\adnan\AppData\Local\Temp\Temporary ASP.NET Files
OR
C;\Users\{Your User Name}\AppDate\Local\Microsoft\Temp\Temporary ASP.NET Files\


This will solve Your problem

Friday, December 16, 2011

Show Header of GridView when it has no rows to show

------------------
.aspx file
------------------
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Grdview with Header</title>
<style type="text/css">
.Gridview
{
font-family:Verdana;
font-size:10pt;
font-weight:normal;
color:black;
width:300px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gvdata" runat="server" CssClass="Gridview" AutoGenerateColumns="false" HeaderStyle-BackColor="#7779AF" HeaderStyle-ForeColor="White">
<Columns>
<asp:BoundField DataField="id" HeaderText="ID"/>
<asp:BoundField DataField ="name" HeaderText="Name" />
<asp:BoundField DataField="Sal" HeaderText="Salary" />

</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
----------------------------
.cs file
----------------------------
protected void Page_Load(object sender, EventArgs e)
{
if(!IsPostBack)
{
BindGridviewData();
}
}
protected void BindGridviewData()
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=testdb;Integrated Security=True");
con.Open();
SqlCommand cmd = new SqlCommand("select id,name,sal from emp ", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
if(ds.Tables[0].Rows.Count==0)
{
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
gvdata.DataSource = ds;
gvdata.DataBind();
int columncount = gvdata.Rows[0].Cells.Count;
gvdata.Rows[0].Cells.Clear();
gvdata.Rows[0].Cells.Add(new TableCell());
gvdata.Rows[0].Cells[0].ColumnSpan = columncount;
gvdata.Rows[0].Cells[0].Text = "No Records Found";
}
else
{
gvdata.DataSource = ds;
gvdata.DataBind();  
}
con.Close();
}
 

Imp Sql Server Queries

Q1: write 3 way to accurate count of the number of records in the table?
Ans:
1:        Select *from tablename
2:        Select count(*) from tablename
3:      this is fastest way
Select rows from sysindexes where id=OBJECT_ID(‘tablename’) AND indid<2; 
Q2: create a database
Ans:
Create database newdatabasename
Q3: rename a database
Ans:
Sp_rename  ‘olddatabasename’ ,’newdatabasename’;
Q4: rename table
Ans: 
Sp_rename ‘oldtablename’,’newtablename’;
Q5:  rename column
Ans: 
Sp_rename ‘tablename.[oldcolumnname]’,’newname’,’column’;
Ex:   sp_rename ‘emp.sal’,’empsal’,’column’
Q6: select top n rows
Ans: 
Select top n * from tablename;
Q7: select top 2nd salary from table
Ans:
Select max(salary) from emp where salary not in(select max(salary) from emp);
Q8: select top nth salary from table
Ans:
Select top(1) from emp where sal in (select top(n) sal from emp order by sal desc) order by sal asc;
Q9: concatenate the fname & lname to give a complete name
Ans:
Select fname||??||lname from emp;
Q10: retrieve first five char of fname column of table emp
Ans: 
Select substring(fname,1,5) frm emp;
Q11: sql quary to delete duplicate records in table
Ans: 
Delete from table1 where empid in(select distinct a.empid from table1 a,table1 b
where(a.firstname=b.firstname AND a.lastname=b.lastname) AND a.empid<b.empid);
Q12: list all tables in database
Ans:
Select table_name from information_schema.tables;
Q13: list all columns in database
Ans: 
Select * from information_schema.columns
Q14: list all table constraints
Select *from information_schema.table_constraints
Q15: creating a new table using existing table
Ans:
Select *into newtablename from existingtablename;
Q16:  creating a new dummy table using existing table
Ans:
Select *into dummy_table from emp where 1=2;
Q17: copy data from existing table into another table
Ans:
Insert into anothertablename select *from existing tablename;

Q18: imp question
In a table we put by mistake m in place of f and f in place of m in gender column which
strategy u would like to apply to chane it?
Ans: we should apply cursor here
//database name adi12 use adi12                                                                   
create table gen (id int, g char(1)) 
insert into gen values(1,'m'),(2,'m'),(3,'f') (4,'f'),(5,'f'),(6,'m') 
select *from gen 
//cursor start from here
declare mycur cursor for select id,g from gen 
declare @g1 as char(1) 
declare @id as int  
open mycur fetch next from mycur  
into @id,@g1 
while(@@fetch_status=0) 
begin 
if(@g1='m') 
update gen set g='f' where id=@id  
else  
update gen set g='m' where id=@id 
fetch next from mycur into @id,@g1 
end  
close mycur