Find number of columns in a table in sql server 2000 and 2005 also

select count(*) NoOfColumns from SYSCOLUMNS
WHERE id= (Select id from SYSOBJECTS where name = 'emp')

What is live lock and deadlock? what is Lock escalation?

In SQL Server Live Lock occurs when read transactions are
applied on table which prevents write transaction to wait
indefinitely and Deadlocking occurs when two user processes
have locks on separate objects and each process is trying to
acquire a lock on the object that the other process has.

Lock escalation is the process of converting a lot of low
level locks (like row locks, page locks) into higher level
locks (like table locks).

What is OPENXML in SQL Server?

OPENXML can parse the xml data in SQL server very efficiently in SQL Server. OpenXML primarily gives the ability to insert XML data to the relational database, however we can query the data too using OpenXML. We need to specify the path of the xml element using xpath.

Convert Sql DataBase Tables to Xml

select * from emp for xml auto

What is SQL Profiler what is the use of it?

SQl Profiler is a tool provided by SQL Server, which can
capture the queries, procedures executed on the database.
It also capture all the details like user, m/c, input
parameters, time of execution etc. This is very helpful
while bebugging any query / procedure

here id col have primary key and identity id name 1 a 2 b 3 c 4 d delete 2nd row then o/p will be id name 1 a 3 c 4 d next inssert 2nd row and i want o/p will be id name 1 a 2 e 3 c 4 d

set identity_insert order1 on
insert into order1(id,name) values(2,'insertedat2')

Session Expire and Redirect to Login Page

The basic idea for this is to have your authentication cookie + session expire at the same time. The automatic behaviour of asp.net would be to take you back to the defined login page. The "slidingExpiration" attribute on the auth cookie would need to be 'true' to keep extending it's life while the session is active.



system.web>
sessionstate mode="InProc" cookieless="false" timeout="20" />
authentication mode="Forms">
forms name=".SAMPLESITEAUTH" loginUrl="~/Login.aspx" protection="All" timeout="20" slidingExpiration="true" path="/" cookieless="UseCookies">
/authentication>
/system.web>





The slidingExpiration on your cookie doesn't work the way you hope. ASP.NET won't refresh the expiry on the cookie with every request; rather it does that only once every half-the-value-of-timeout minutes. I.e. if you set timeout="20", it only refreshes the cookie every ten minutes.

Transpose In sql server(Its uses for convert Column to Rows and Rows to column)

As known SQL Server 2000 need some dynamic sqls to convert rows to columns or vice versa. PIVOT and UNPIVOT relational operator did the job of converting rows to columns and columns to rows respectively.we can transpose values from rows to columns in sql server 2005 using PIVOT relational operator,

Ex Of Transpose:


create table #t (
id int identity(1,1),
AJAN int,
AFEB int,
AMAR int,
FJAN int,
FFEB int,
FMAR int
)

insert into #t (AJAN,AFEB,AMAR,FJAN,FFEB,FMAR) values (1,2,3,4,5,6)
insert into #t (AJAN,AFEB,AMAR,FJAN,FFEB,FMAR) values (11,12,13,14,15,16)

select * from #t

select
id,
'A'+c as amonth,
sum(case when c1='A' then val else 0 end) as aval,
'F'+c as fmonth,
sum(case when c1='F' then val else 0 end) as fval
from (
select id, val, substring(col,1,1) as c1, substring(col,2,3) as c
from #t
unpivot (val for col in (AJAN,AFEB,AMAR,FJAN,FFEB,FMAR)) up
) t
group by id,c
order by id,c

SQL Server Data Transformation Services (DTS)

INTRODUCTION:

Microsoft released Data Transformation Services (DTS) along with SQL Server 7.0. A very powerful, easy to use, graphical tool for importing, exporting and transforming data, which is only available as a costly add-on in other RDBMS products like Oracle. Prior to SQL Server 7.0, all we had was BCP, which is powerful indeed, but not as friendly and functionally rich as DTS. DTS was made robust and dramatically improved with the release of SQL Server 2000, as more and more DBAs and developers started using DTS for building various data loading and transformation solutions. It wouldn't be an exaggeration to say that DTS is one of the most popular ETL (Extract, Transform and Load) tools currently in use. Not bad for a tool that's been around for just about four years.


INTRODUCTION:

If you need to get data into or out of SQL Server, either as a one-off or on a regular basis, there are several ways to achieve this. However in many situations the best solution is to use the excellent DTS (Data Transformation Services) application included with SQL Server 2000 or the replacement SQL Server Integration Services (SSIS) in SQL Server 2005.

DTS/SSIS are large and complex applications, however they are also extremely powerful and consequently we use them for most data conversion tasks, even when neither the source nor the destination is SQL Server!

In addition to transferring data they can also be used to transform or modify the data during the import process. For example a single import file may be used to update multiple tables within the database.


Examples
Examples of some of the tasks that can be undertaken using DTS/SSIS include....


Importing .csv data files from a separate computer.

Importing data from existing Access database.

Exporting SQL data to an Excel spreadsheet.

Exporting data to csv files.

Copy data from one SQL Server to another.


Specific Example
One use to which we have employed DTS for was for importing new and updated works orders from a third party computer system. Data from the other computer was placed in .csv files at a predefined location on the network. We wrote a DTS system that :
Validated the folder structure to ensure that the expected directories were present.
Checked the validated folder to see if any new files were ready for importing.
Located the first csv file of Address data to import.
Validated the incoming raw Address data and imported it into a temporary table.
Processed all address updates, modifying existing data in the main Address table.
Processed all additions, adding new records to the same Address table.
Located the first csv file of Works Order data to import.
Validated the incoming raw Works Order data and imported it into a temporary table.
Process all works order updates, modifying existing data in the main Works Order table.
Process all additions, adding new records to the same Works Order table.
Re-named the processed files to include the date that they were processed on.
Moved the re-named csv files to a separate archive directory.
Cycle back to stage 2 to see if there are any other files to import.
Create a csv file of updated Works Orders with their revised statuses.
Create a csv file of Invoices for completed Works Orders.
Transferred the created csv files using FTP to the client's UNIX server.

Session Expire and Redirect to login Page specify after Some Time Period

Context.Response.AppendHeader("Refresh",
Convert.ToString(Session.Timeout = 10) + "; URL=Login.aspx" );