Sample SQL Server DataBase

http://msftdbprodsamples.codeplex.com/releases/view/4004

SQL Server 2005 Shortcut Keys

SQL Server 2005 Shortcut Keys

Below I am giving generally used shortcut keys in SQL Server 2005 :

* Cancel a query. ALT+BREAK
* Connections: Connect. CTRL+O
* Connections: Disconnect. CTRL+F4
* Connections: Disconnect and close child window. CTRL+F4
* Database object information. ALT+F1 (i.e Structure Of Table etc. ) Equivalent to sp_help)
* Editing: Clear the active Editor pane. CTRL+SHIFT+DEL
* Editing: Comment out code. CTRL+SHIFT+C
* Editing: Decrease indent. SHIFT+TAB
* Editing: Increase indent. TAB
* Editing: Delete through the end of a line in the Editor CTRL+DEL
* Editing: Find. CTRL+F
* Editing: Go to a line number. CTRL+G
* Editing: Make selection lowercase. CTRL+SHIFT+L
* Editing: Make selection uppercase. CTRL+SHIFT+U
* Editing: Remove comments. CTRL+SHIFT+R
* Editing: Replace. CTRL+H
* Execute a query. CTRL+E , F5
* Help for the selected TSQL statement. SHIFT+F1
* Navigation: Switch between query and result panes. F6
* Navigation: Switch panes. Shift+F6
* New Query window. CTRL+N
* Object Browser (To show/hide). F8
* Object Search. F4
* Parse the query for checking syntax. CTRL+F5
* Print. CTRL+P
* Results: Display results in grid format. CTRL+D
* Results: Display results in text format. CTRL+T
* Results: Save results to file. CTRL+SHIFT+F
* Results: Show Results pane (toggle). CTRL+R
* Save. CTRL+S
* Tuning: Index Tuning Wizard. CTRL+I
* Change / Select Database. CTRL+U

get top 7 salary

select min(salary) from tblSalary where salary in(
select distinct top 7 salary from tblSalary order by salary desc)

UML Tutorial

http://www.tutorialspoint.com/uml/
http://atlas.kennesaw.edu/~dbraun/csis4650/A&D/UML_tutorial/

Learn Report SSRS 2005

.Net framework Interview Questions

http://www.andymcm.com/dotnetfaq.htm#13.5
http://www.andymcm.com/dotnetfaq.htm
http://www.andymcm.com/csharpfaq.htm#3.7
Anonymous Methods in C# 2.0
http://www.c-sharpcorner.com/UploadFile/DipalChoksi/anonmethod_cs20_dc09192006162337PM/anonmethod_cs20_dc.aspx

Are C# parameters passed by reference or by value?
All parameters are passed by value by default in C#. However, know that when passing a reference type, a reference is being passed—by value—rather than an object itself. The Java language works in the same way.

SQL Server Reporting Services(SSRS) Interview Questions

http://www.datawarehousingguide.com/microsoft-bi-interview-questions/14-sql-server-reporting-servicesssrs-interview-questions-.html

http://www.careerride.com/Reporting-services-advantages.aspx


1. Explain the flow from a report request to report delivery.
2. Where does connection string of a report exists? Can we read it?
3. Where does reporting services stands as a BI tool compared to BO, Cognos, Crystal reports, Hyperion etc?
4. What is Subreport control used for?
5. What is the difference between Matrix control & Table control?
6. What are the different aggregate functions available in SSRS?
7. How to change the format of the currency field?
8. What are report parameters?
9. What are the different types of parameters used in SSRS, explain each one of them.
10. How to add a cascading parameter to a report?
11. How to create a drilldown report? Explain the steps.
12. How to add page breaks in a report?
13. What is data driven subscription? How to implement this?
14. What services in windows need to run for SSRS to run properly?
15. What is the report history in SSRS manager means? How to enable history?
16. Explain the security architecture of SSRS
17. Mention some of the in built security roles in SSRS
18. Why do we use Reporting services configuration manager tool? What are the tasks we achieve in this?

Q.What are the drawbacks of SSRS?
Ans-For many years Microsoft had no direct solution for reporting with the SQL server except their dependency on Crystal Reports. With SQL Server 2005 onwards they have come up with SSRS – SQL Server Reporting Services, but as such this is no better than Crystal Reports although it comes bundled with the Server. The components of this are:
1. Report Builder
2. Report Designer
3. Report Server
4. Report Manager
5. Datasource
6. Report Server Database

The drawbacks are (that you can reply in your interview):
1. It is complex as of now to understand the complete functionality and structure of this new component
2. Users are still depending on Crystal reports as they are more confident as used to compared to SSRS
3. Report Designer is for developers for standard reports and report builder is for end users for designing their simple formatted reports.
4. Complete understanding and exposure to both is important to utilize both functions fully and extensively
5. For end users using report builder, data models or report models are used
6. Report model generation rules building is a complex process
7. Report exporting to excel beyond a particular size results in loss of data

Q.What can SQL Server Reporting Services do?

With Reporting Services,

* You can create interactive, tabular, graphical, or free-form reports from relational, multidimensional, or XML-based data sources.
* You can publish reports, schedule report processing, or access reports on-demand.
* You can create ad hoc reports based on predefined models and interactively explore data within the model.
* You can select from a variety of viewing formats, export reports to other applications, and subscribe to published reports.
* You can view the reports created over a Web-based connection or as part of Windows application.
***************************************************************************************
SQL Server - how to send a SSRS report from SSIS? - Feb 27, 2010 at 11:50 AM by Shuchi Gauri
How to send a SSRS report from SSIS?

Steps to send a SSRS report from SSIS:

* Create a subscription to the report from Report Manager.
* At subscription level, mention the report format and email address of the recipient user. When a schedule is created for SSRS report, an SQL Server agent job is created.
* Execute the SSRS report subscription using sp_start_job with the appropriate report name.

SQL Server - how to send a SSRS report from SSIS? - May 05, 2009 at 22:00 PM by Rajmeet Ghai
How to send a SSRS report from SSIS?

SSIS has the ability to send SSRS report in different formats like Excel, PDF etc. This is achieved by creating a report subscription using the Report manager. In the report subscription, the format of the SSRS report can be mentioned along with the email address of the recipient. sp_start_job is used to execute the report subscription.

SQL Server - how to send a SSRS report from SSIS? - June 21, 2009 at 09:00 PM by Amit Satpute

After the SSIS package has finished loading the data, the SSRS report can be sent to different users in .xls, .pdf, etc formats.

The steps one needs to follow to do this are:

* Creation of an SSRS report subscription from Report Manager.
* Entering the report format and the email address of the recipient in the subscription.
* Creation of a schedule for the SSRS report so that the SQL Server Agent Job gets created.
* Execution of SSRS report subscription from the SSIS by using sp_start_job and providing a relevant job name.
***************************************************************************************

Interview Questions and Answers

http://jaganinfo.blogspot.com/2010/02/dell-net-interview-questions.html
http://www.aired.in/2009/09/c-interview-questions-and-answers-c.html
http://venkatcsharpinterview.blogspot.com/2009/07/c-interview-questions-on-constructors.html

ASP.NET Session State FAQ

http://www.eggheadcafe.com/articles/20021016.asp
Basic use of Session in ASP.NET (C#):

STORE:
DataSet ds = GetDataSet(whatever parameters);
Session["mydataset")=ds;

RETRIEVE:
DataSet ds = (DataSet)Session["mydataset"];

Storage location

* InProc - session kept as live objects in web server (aspnet_wp.exe). Use "cookieless" configuration in web.config to "munge" the sessionId onto the URL (solves cookie/domain/path RFC problems too!)
* StateServer - session serialized and stored in memory in a separate process (aspnet_state.exe). State Server can run on another machine
* SQLServer - session serialized and stored in SQL server

Performance

* InProc - Fastest, but the more session data, the more memory is consumed on the web server, and that can affect performance.
* StateServer - When storing data of basic types (e.g. string, integer, etc), in one test environment it's 15% slower than InProc. However, the cost of serialization/deserialization can affect performance if you're storing lots
of objects. You have to do performance testing for your own scenario.
* SQLServer - When storing data of basic types (e.g. string, integer, etc), in one test environment it's 25% slower than InProc. Same warning about serialization as in StateServer.


Performance tips for Out-of-Proc (OOP) modes

If you're using OOP modes (State Server or SQL Server), one of your major cost is the serialization/deserialization of objects in your session state. ASP.NET performs the serialization/deserialization of certain "basic" types using an optimized internal method. "Basic" types include numeric types of all sizes (e.g. Int, Byte, Decimal, ... etc), String, DateTime, TimeSpan, Guid, IntPtr and UIntPtr.

If you have a session variable (e.g. an ArrayList object) that is not one of the "basic" types, ASP.NET will serialize/deserialize it using the BinaryFormatter, which is relatively slower.

For performance sake it is better to store all session state data using one of the "basic" types listed above. For example, if you want to store two things, Name and Address, in session state, you can either

(a) store them using two String session variables, or
(b) create a class with two String members, and store that class object in a session
variable. Performance wise, you should go with option (a).


Robustness

* InProc - Session state will be lost if the worker process (aspnet_wp.exe) recycles, or if the appdomain restarts. It's because session state is stored in the memory space of an appdomain. For details, see KB324772.
* StateServer - Solve the session state loss problem in InProc mode. Allows a webfarm to store session on a central server. Single point of failure at the State Server.
* SQLServer - Similar to StateServer. Moreover, session state data can survive a SQL server restart, and you can also take advantage of SQL server failover cluster, after you've followed instructions in KB 311029.


Caveats

InProc - It won't work in web garden mode, because in that mode multiple aspnet_wp.exe will be running on the same machine. Switch to StateServer or SQLServer when using web garden. Also Session_End event is supported only in InProc mode.

StateServer

* - In a web farm, make sure you have the same in all your web servers. See KB 313091 on how to do it.
* - Also, make sure your objects are serializable. See KB 312112 for details.
* - For session state to be maintained across different web servers in the web farm, the Application Path of the website (For example \LM\W3SVC\2) in the IIS Metabase should be identical in all the web servers in the web farm. See KB 325056 for details

SQLServer


- If you specify integrated security in the connection string (e.g. "trusted_connection=true", or "integrated security=sspi"), it won't work if you also turn on impersonation in asp.net. Unfortunately, this bug
isn't reported in KB yet. (There is a QFE fix for it.)
- Also, make sure your objects are serializable. See KB 312112 for details.
- For session state to be maintained across different web servers in the web farm, the Application Path of the website (For example \LM\W3SVC\2) in the IIS Metabase should be identical in all the web servers in the web farm.
See KB 325056 for details.

FAQ's:

Question list:

Q: Session states works on some web servers but not on others.

Q: Why isn't Session_End fired when I call Session_Abandon?

Q: Why are my Session variables lost frequently when using InProc mode?

Q: Why does the SessionID remain the same after the Session times out or abandoned?

Q: Why does the SessionID changes in every request?

Q: Can I share session state between ASP.NET and ASP pages?

Q: What kinds of object can I store in session state?

Q: How come Response.Redirect and Server.Transfer is not working in Session_End?

Q: Do I have a valid HttpContext in Session_End?

Q: How do I use session state with web services?

Q: I am writing my own HttpHandler. Why is session state not working?

Q: I am using a webfarm, and I lost session state when directed to some web servers.

Q: Why isn't session state availabe in the Application_OnAcquireRequestState (or other)

Q: If using "cookieless", how can I redirect from a HTTP page to an HTTPS page?

Q: What isn't Session available in my event handlerin global.asax?

Q: Does session state have a locking mechanism that serialize the access to state?

Answers:

Q: Session states works on some web servers but not on others.
A: Maybe machine name problem. See http://support.microsoft.com/default.aspx?scid=kb;EN-US;q316112 .

Q: Why isn't Session_End fired when I call Session_Abandon?
A: First of all, Session_End event is supported only in InProc mode. In order for Session_End to be fired, your session state has to exist first. That means you have to store some data in the session state and has completed at least one request.

Q: Why are my Session variables lost frequently when using InProc mode?
A: Probably because of application recycle. See http://support.microsoft.com/default.aspx?scid=kb;en-us;Q316148

Q: Why does the SessionID remain the same after the Session times out or abandoned?
A:Even though the session state expires after the indicated timeout period, the session ID lasts as long as the browser session. What this implies is that the same session ID can represent multiple sessions over time where the instance of the browser remain the same.

Q: Why does the SessionID changes in every request?
A: This may happen if your application has never stored anything in the session state. In this case, a new session state (with a new ID) is created in every request, but is never saved because it contains nothing.

However, there are two exceptions to this same session ID behavior:
- If the user has used the same browser instance to request another page that uses the session state, you will get the same session ID every time. For details, see "Why does the SessionID remain the same after the Session times out?"
- If the Session_OnStart event is used, ASP.NET will save the session state even when it is empty.

Q: Can I share session state between ASP.NET and ASP pages?
A: Yes! Here is our article on how to do this in either direction using two "intermediate" pages. And here is an article on how to do it with SQL Server.

Q: What kinds of object can I store in session state?
A: It depends on which mode you are using:
- If you are using InProc mode, objects stored in session state are actually live objects, and so you can store whatever object you have created.
- If you are using State Server or SQL Server mode, objects in the session state will be serialized and deserialized when a request is processed. So make sure your objects are serializable and their classes must be marked as so. If not, the session state will not be saved successfully. In v1, there is a bug which makes the problem happen unnoticed. See this KB for more info:
http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;q312112

Q: How come Response.Redirect and Server.Transfer is not working in Session_End?
A: Session_End is fired internally by the server, based on an internal timer. Thus, there is no HttpRequest associted when that happens. That is why Response.Redirect or Server.Transferdoes not make sense and will not work.

Q: Do I have a valid HttpContext in Session_End?
A: No, because this event is not associated with any request.

Q: Will my session state be saved when my page hit an error?
No. Unless you call Server.ClearError in your exception handler.

Q: How do I use session state with web services?
A: The extra trick needed is on the caller side. You have to save and store the cookies used by the web service. See the MSDN documentation on HttpWebClientProtocol.CookieContainer property.

However, please note if you're using proxy object to call a web service from your page, the web service and your page cannot share the same session state due to architecture limitation.

This can be done if you call your web service through redirect.

Q: I am writing my own HttpHandler. Why is session state not working?
A: Your HttpHandler has to implement the "marker" interface IRequiresSessionState or IReadOnlySessionState in order to use session state.

Q: I am using a webfarm, and I lost session state when directed to some web servers.
A: For session state to be maintained across different web servers in the web farm, the Application Path of the website (For example \LM\W3SVC\2) in the IIS Metabase should be identical in all the web servers in the web farm.
See KB 325056 for details.

Q: Why isn't session state availabe in the Application_OnAcquireRequestState (or other)
event handler?
A: Session state is available only after the HttpApplication.AcquireRequestState event is called. For details, see: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconhandlingpublicevents.asp

Q: If using "cookieless", how can I redirect from a HTTP page to an HTTPS page?
A: Try this:
String originalUrl = "/fxtest3/sub/foo2.aspx";
String modifiedUrl = "https://localhost" + Response.ApplyAppPathModifier(originalUrl);
Response.Redirect(modifiedUrl);
NOTE: Fully qualified URLs in the response.redirect, server.transfer, and FORM action
tags cannot be used with cookiless sessions. Here is an example of a fully qualified
URL: http://www.eggheadcafe.com/default.asp More info here:

Q: What isn't Session available in my event handler in global.asax?
A: It depends on which event you're handling. Session is available only after AcquireRequestState event.

Q: Does session state have a locking mechanism that serialize the access to state?
Session state implements a reader/writer locking mechanism:
- A page (or frame) that has session state write access (e.g. <%@ Page EnableSessionState="True" %>) will hold a writer lock on the session until the request finishes.
- A page (or frame) that has session state read access (e.g. <%@ Page EnableSessionState="ReadOnly" %>) will hold a reader lock on the session until the request finishes.
- Reader lock will block a writer lock; Reader lock will NOT block reader lock; Writer lock will block all reader and writer lock.
- That's why if two frames both have session state write access, one frame has to wait for the other to finish first.

Configuring Web Gardens with IIS 6.0 (IIS 6.0)

http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/659f2e2c-a58b-4770-833b-df96cabe569e.mspx?mfr=true


Web gardens are different from Web farms. A Web garden is configured on a single server by specifying multiple worker processes for an application pool. Web farms use multiple servers for a Web site.

ASP.NET Caching: SQL Cache Dependency With SQL Server 2000

http://www.c-sharpcorner.com/uploadfile/mosessaur/sqlcachedependency01292006135138pm/sqlcachedependency.aspx

Passing Values between User Controls and ASPX Page

http://www.c-sharpcorner.com/uploadfile/santhi.m/passingvaluesfrmuctoaspx11212005050040am/passingvaluesfrmuctoaspx.aspx

http://www.dotnetfunda.com/articles/article97.aspx

http://www.beansoftware.com/ASP.NET-Tutorials/User-Control.aspx

Good dot net site and blog

http://www.dotnetspark.com/kb/773-what-is-encapsulation.aspx

I Have Employee table having column name as ID,SALARY how to get second max salary from employee table with id ex ID SALARY 1 20000 7 37000 2 5000

CREATE TABLE [dbo].[tblSalary]
(
[ID] [int] NOT NULL,
[Salary] [INT]
)
ON [PRIMARY]

INSERT INTO [tblSalary] ([ID],Salary) VALUES (1,20000)
INSERT INTO [tblSalary] ([ID],[Salary]) VALUES (1,30000)
INSERT INTO [tblSalary] ([ID],[Salary]) VALUES (2,40000)
INSERT INTO [tblSalary] ([ID],[Salary]) VALUES (3,50000)
INSERT INTO [tblSalary] ([ID],[Salary]) VALUES (4,60000)
INSERT INTO [tblSalary] ([ID],[Salary]) VALUES (4,70000)
INSERT INTO [tblSalary] ([ID],[Salary]) VALUES (4,80000)

select max(salary) from tblSalary
where salary < (select max(salary) from tblSalary)

I Have Employee table having column name as ID,SALARY how to get second max salary from employee table with id ex ID SALARY 1 20000 7 37000 2 5000

CREATE TABLE [dbo].[tblSalary]
(
[ID] [int] NOT NULL,
[Salary] [INT]
)
ON [PRIMARY]

INSERT INTO [tblSalary] ([ID],Salary) VALUES (1,20000)
INSERT INTO [tblSalary] ([ID],[Salary]) VALUES (1,30000)
INSERT INTO [tblSalary] ([ID],[Salary]) VALUES (2,40000)
INSERT INTO [tblSalary] ([ID],[Salary]) VALUES (3,50000)
INSERT INTO [tblSalary] ([ID],[Salary]) VALUES (4,60000)
INSERT INTO [tblSalary] ([ID],[Salary]) VALUES (4,70000)
INSERT INTO [tblSalary] ([ID],[Salary]) VALUES (4,80000)

select max(salary) from tblSalary
where salary < (select max(salary) from tblSalary)

SQL Server Cursor Examples

http://www.mssqltips.com/tip.asp?tip=1599
Cursor Components
http://msdn.microsoft.com/en-us/library/ms180169.aspx

Based on the example above, cursors include these components:

* DECLARE statements - Declare variables used in the code block
* SET\SELECT statements - Initialize the variables to a specific value
* DECLARE CURSOR statement - Populate the cursor with values that will be evaluated
o NOTE - There are an equal number of variables in the DECLARE CURSOR FOR statement as there are in the SELECT statement. This could be 1 or many variables and associated columns.
* OPEN statement - Open the cursor to begin data processing
* FETCH NEXT statements - Assign the specific values from the cursor to the variables
o NOTE - This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement
* WHILE statement - Condition to begin and continue data processing
* BEGIN...END statement - Start and end of the code block
o NOTE - Based on the data processing multiple BEGIN...END statements can be used
* Data processing - In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic
* CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened
* DEALLOCATE statement - Destroys the cursor

Delete single row from duplicate rows in SQL Server 2005 and 2000

http://chiragrdarji.wordpress.com/2007/07/23/delete-single-row-from-duplicate-rows-in-sql-server-2005-and-2000/

Lets assume that you are using SQL Server 2005 for your current project. You found that you have few rows which have duplicate data in all the columns. Lets consider that you have table name “Example” which has two columns ID and Name.

CREATE TABLE [dbo].[Example]
(
[ID] [int] NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]

INSERT INTO [Example] ([ID],[Name]) VALUES (1,Chirag)
INSERT INTO [Example] ([ID],[Name]) VALUES (1,Chirag)
INSERT INTO [Example] ([ID],[Name]) VALUES (2,‘Shailesh’)
INSERT INTO [Example] ([ID],[Name]) VALUES (3,‘Dipak’)
INSERT INTO [Example] ([ID],[Name]) VALUES (4,‘Mihir’)
INSERT INTO [Example] ([ID],[Name]) VALUES (4,‘Mihir’)
INSERT INTO [Example] ([ID],[Name]) VALUES (4,‘Piyush’)

Now you want to delete duplicate rows in such a way that only one row will be exist after delete statement. First let me write the query which will give return all the duplicate rows from table.

SELECT
[ID],[NAME],COUNT([ID])
FROM
[Example]
GROUP BY
[ID],[NAME]
HAVING
COUNT([ID]) > 1

Query to identify duplicate rows in table.

Here I have used COUNT([ID]) in select statement as ID is not null filed. You can use any column which is not NULL. If all the columns in your table allows NULL value than you can use COUNT(*). The Difference between COUNT(Column Name) and COUNT(*) is, if your column allows null value and in table you have 5 records with 2 null values in ColumnA. If you use COUNT(ColumnA) it will returns 3 and if you use COUNT(*) it will returns 5. So COUNT(Column Name) ignores NULL value. Lets get back to our query. I have used all the column in SELECT and GROUP BY clause. You also have to write all the columns of your table in SELECT and GROUP BY clause. This way you can identify all the duplicates row from table.

Lets assume that you have to delete the row which has value (1, ‘Chirag’) so that only one row remains. Here is the query, (Note: This will work only in SQL Sever 2005)

DELETE TOP(1) FROM [Example] WHERE [ID] = 1

Fig – (3) Delete single row from duplicate rows.

Here I have used TOP(1) , If you have n rows which has all the values same than you have to use TOP(n-1) so that only 1 row will be remain after delete statement. To delete all the duplicate rows you need to write a cursor as shown below,

DECLARE @ID int
DECLARE @NAME NVARCHAR(50)
DECLARE @COUNT int

DECLARE CUR_DELETE CURSOR FOR
SELECT [ID],[NAME],COUNT([ID]) FROM [Example] GROUP BY [ID],[NAME] HAVING COUNT([ID]) > 1

OPEN CUR_DELETE

FETCH NEXT FROM CUR_DELETE INTO @ID,@NAME,@COUNT
/* Loop through cursor for remaining ID */
WHILE @@FETCH_STATUS = 0
BEGIN

DELETE TOP(@COUNT -1) FROM [Example] WHERE ID = @ID

FETCH NEXT FROM CUR_DELETE INTO @ID,@NAME,@COUNT
END

CLOSE CUR_DELETE
DEALLOCATE CUR_DELETE

Fig – (4) Cursor to delete all duplicate records

This is all about deleting duplicate rows in SQL Server 2005.

Now to do the same in SQL server 2000. There is function called ROWCOUNT in SQL. I have used same [Example] table. You can do this by,

SET ROWCOUNT 1
DELETE FROM [Example] WHERE [ID] = 1

Fig – (5) Delete duplicate row in SQL Server 2000

ROWCOUNT function specify that how many rows will be affected by the statement which is immediately written below. Here also you have to write ROWCOUNT (n -1) to delete n duplicate rows such that only 1 row will remain in database.

PIVOT queries in SQL Server 2005

http://geekswithblogs.net/lorint/archive/2006/08/04/87166.aspx
http://articles.techrepublic.com.com/5100-10878_11-6143761.html
Previously we are using CASE,GroupBy In place of this concept Sql Server 2005 provide
"PIVOT" concept,
With the new PIVOT operator in SQL Server 2005, the CASE statement and GROUP BY statements are no longer necessary.
(Every PIVOT query involves an aggregation of some type, so you can omit the GROUP BY statement.)
The PIVOT operator provides the same functionality that we tried to achieve with the CASE statement query, but you can achieve it through less code
MSDN states that you cannot pivot two columns
If you want two columns with the same column header, then you cannot achieve it

LIMITATION
The PIVOT operator is useful in many situations, yet there are drawbacks.
"hard code" the fields that you are pivoting into columnar data
You can overcome this by building the PIVOT query through dynamic TSQL statements, but this is not the most desirable solution.

http://www.mssqltips.com/tip.asp?tip=1019

Improves Stored Procedure Performance in SQL Server

1.SET NOCOUNT ON is used in procedure for improve the performance of the store procedure.
put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed.
This is performed for all SELECT, INSERT, UPDATE, and DELETE statements.

If you still need to get the number of rows affected by the T-SQL statement that is executing you can still use the @@ROWCOUNT option.  By issuing a SET NOCOUNT ON this function (@@ROWCOUNT) still works and can still be used in your stored procedures to identify how many rows were affected by the statement.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
@Param1 = <Default_Value_For_Param1, , 0>,
@Param2=<Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

Improving SQL Performance 
http://www.componentace.com/help/absdb_manual/increasesqlperformance.htm  

Extention method

http://www.developer.com/net/csharp/article.php/3592216
Following are the interview questions
1.Difference between asp.net 3.5 and 4.0
2.why we are using Ajax.
3.what is Extention method.
4.how the procedure will faster.
5.how to improve the performance of the SQL Server.
6.What is pivot table.
7.did you know generic.

Introduction To Role-Based Security In SQL Server Reporting Services

http://odetocode.com/Articles/215.aspx

InterView Question

1.What is DTS Package in SQL SERVER.
2.What are the sequrities used in the SSRS Report.
3.What is the advantage to use SSIS Package in place of window service. 

Introducing Microsoft Enterprise Library Application Blocks

http://aspalliance.com/1075_Introducing_Microsoft_Enterprise_Library_Application_Blocks.1
Data Access Application Block
http://www.4guysfromrolla.com/articles/070203-1.aspx
Example use of enterprise library
http://forums.asp.net/p/1613886/4131477.aspx

.Net Interview Question

http://www.scribd.com/doc/72849/Net-Interview-Questions
http://www.scribd.com/doc/6790770/NET-Interview-Questions

Degine Patteren

http://www.dotnetspider.com/resources/Category537.aspx
For Details
http://www.dotnetspider.com/resources/25466-Software-Architecture-Interview-Questions-Part.aspx#Canyouexplainfactorypattern
Builder Pattern
http://wiki.asp.net/page.aspx/498/builder-pattern/

Interview in image.

1.question on SSIS package.
2.did you use third party tool in data access layer ?
3.What is Microsoft enterprises  ?
4.asp.net page life cycle.

What is the difference between USER CONTROLS and a CUSTOM CONTROL?

http://www.dotnetspider.com/forum/13719-What-difference-between-USER-CONTROLS-CUSTOM-CONTROL.aspx
FOR Details
http://ondotnet.com/pub/a/dotnet/excerpt/progaspdotnet_14/index1.html?page=3

Discussion about finalize and dispose method

http://bytes.com/topic/c-sharp/answers/875442-cleaning-up-managed-unmanaged-resources
http://www.devx.com/dotnet/Article/33167/0/page/2

Dictionary Information

http://www.cfilt.iitb.ac.in/~hdict/webinterface_user/dict_search_user.php
http://www.shabdkosh.com/
http://dictionary.reference.com/
http://www.merriam-webster.com/top-ten-lists/top-10-phrases-from-shakespeare/cruel-to-be-kind.html

SQL Server 2005 Setup Experience

1.first configure your IIS in your system other wise it is throwing error
2.Control Panel > Add or Remove Programmes >  Click on Add/Remove Windows components
then checked IIS and click next to install IIS.

follow this link
http://frater.wordpress.com/2009/06/14/sql-server-setup-failed-to-obtain-system-account-information-for-the-aspnet-account/

Fix this by registering asp .net with IIS. To do this open a command prompt and go to the following directory:
C:\windows\Microsoft.NET\Framework\v2.0.60727\
This is assuming you’re using .Net 2.0 or higher (3.0, 3.5). Even if you’re using the 3.x series and Visual Studio 2008, you still need to go to the v2.0 directory for this operation – this is because the 3.x series are not a replacement for the 2.x series, but rather extensions. (3.x is a superset of 2.x and there have been no breaking changes to 2.x functionality).

f you want to understand this further, there is an interesting post about it here: http://blogs.msdn.com/vijaysk/archive/2008/03/20/running-asp-net-3-5-on-iis.aspx
For now though, run the following command: aspnet_regiis.exe -i

execute it  "aspnet_regiis.exe -i" in command window



Windows XP Service pack 3 Download site

http://www.microsoft.com/downloads/en/confirmation.aspx?FamilyID=5b33b5a8-5e76-401f-be08-1e1555d4f3d4&displaylang=en

Deploy SSIS Package.....

http://www.protalk.in/sql-server/steps-to-create-and-deploy-ssis-package-as-a-sqlagent-job/
all the information related to SSIS Package
http://technet.microsoft.com/en-us/library/cc966389.aspx 
DTS VS SSIS 
http://decipherinfosys.wordpress.com/2008/03/11/dts-vs-ssis-a-basic-overview/
How to start and stop windows service from SSIS
http://sqlreality.com/blog/ssis/how-to-start-and-stop-windows-service-from-ssis/
Could not execute SSIS package from windows service
http://www.sqldev.org/sql-server-integration-services/could-not-execute-ssis-package-from-windows-service-17034.shtml

New features in SSIS 2008 that can Improve SSIS 2008 Performance over SSIS 2005
http://siddhumehta.blogspot.com/2009/03/new-features-in-ssis-2008-that-can.html
Scheduling SSIS Package using SQL Server Agent
http://www.c-sharpcorner.com/UploadFile/MIkkykumar/3772/Default.aspx 
Executing Remote SSIS Package via Web Service
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_24515667.html 

Profiles In ASP.NET 2.0

http://odetocode.com/Articles/440.aspx
http://www.codersource.net/asp-net/asp-net-2-0/profiles-in-asp-net-2-0.aspx

SSRS Interview Questions

1.How to configure SSRS default report directory
2.Explain Drill down and drill threw report
3.Use of Switch and IIF functions 
*********************************************************
Q.What can SQL Server Reporting Services do?
A-With Reporting Services, you can create interactive, tabular, graphical, or free-form reports from relational, multidimensional, or XML-based data sources.............
Read answer
Q-Explain the architecture of reporting services.?
A-Reporting Services runs as a middle-tier server as part of the existing server architecture.
SQL Server 2000 should be installed for the database server, and Internet Information Services 6.0 as a Web server............

Describe Reporting Life-cycle.
The phases of the Reporting Life Cycle involve:



Report authoring
This stage involves creation of reports that are published using the Report Definition language. RDL is an XML based industry standard for defining reports..............

Q-What are the ways to tune Reporting Services?
Following are a few ways you can tune up Reporting Services:
You can expand your current Server or avail the reporting service database on another server. Depending on report characteristics and application logic, it is sometimes better to have a copy of data separate to improve the performance............

   
**********************************************************
Q.Can you explain Report definition language (RDL) file in reporting services?
Ans - RDL is an XML description of a report in reporting services. So basically the RDL file has the XML grammar which describes a reporting services report.

Q.How can we consume reports in ASP.NET?
Ans -  There are three famous ways of calling reporting services report in ASP.NET:-
. Using URL way to access reports.
. Using reporting web service to pro-grammatically access reports.
. Using report viewer control.

http://www.dotnetspark.com/kb/273-crystal-and-reporting-services-interview.aspx

Q. What is Expressions ?
Ans- An expression is a short statement, written in code, which sets the value of a property. An expression can set the value of a property based on just about any data available in the report: parameters, report global values, fields in a dataset, and built-in functions.
let's say we want it to highlight all the customers from a particular State. We could use the expression:
=iif(Fields!State.Value = "CA", "Bold", "Normal")
Q.What is  Common functions ?
 Ans- The last option is Common Functions. Expand the Common Functions node then you find different function like Text,Datetime,math,Aggregate etc,

Q.what is Global variables ?
Ans- In expression editor click on Globals in the first column of the expression editor.
In the second column, you will see the list of the global variables that are provided to the report.
we are going to use the global variables to create a page-numbering scheme for our report
add a footer to the FirstReportMan.rdl report. You enable this by going to the Layout tab and selecting the Report Footer option from the Report main menu. Now that you have a footer, drag four text boxes into the footer section.

Page

=Globals!PageNumber

of

=Globals!TotalPages

Some other globals worth noting are:

• ExecutionTime – this tells you how long it took to run the report
• ReportServerUrl – this allows you to verify that you are getting your report from the correct source
• UserID - this shows under which User ID the report was run 

Q. What is Custom functions?
Ans - There are about 100 common functions in SSRS 2005
 nd they can handle most of what you will need to do. Occasionally, however, you will still need to set an expression with more control than the common functions can manage. So, to make SSRS even more flexible, you can write custom VB.NET or C# functions and use them in expressions.
Now we are going to add a custom function to the FirstReportMan.rdl report.
Open the report and navigate to the Layout tab. From the Report menu select Report Properties and then jump to the Code tab. We are going to write a custom function that returns a different color, depending on the value that is passed to it. We will then use that function to set the background color for the status field in the report detail table.
Copy the following code into the code window:

Public Function GetColor(ByVal status as String) as String
IF status = "100" Then
Return "White"
End IF
IF status = "101" Then
Return "Yellow"
End IF
IF status = "102" Then
Return "Tomato"
End IF
End Function

Click OK and close the window.
 Now that we have a function that returns color names we need to wire up that function to an expression. Click on the Customer Status cell and open the Properties window. Find the Background Color property and choose Expression from the dropdown. Add the following line of code to create an expression:
=code.GetColor(Fields!CustomerStatus.Value)
When the report runs and this expression is resolved, SSRS will call your GetColor function and pass the value of the customer status for that instance of the row to the function.
Q. What is Sub report ?

http://www.simple-talk.com/sql/reporting-services/beginning-sql-server-2005-reporting-services-part-2/
Interview Questions
http://www.careerride.com/SQL-Server-Reporting-Services.aspx
 What is the difference between drill down and drill through?
Ans-

Drill down means navigating the summary level to detail level in the same report. 

where as drill through is connected to different reports.

Ans-2

Drill DOWN means a way to filter the results to a more 
detailed level from the Parent Level.Drill UP on the other hand means to go up to a Higher (Parent) Level from a Lower (Child) level.
There is a another term for this : SLICING & DICING (Multi Dimesion Analysis)
 

Drill Through on the other hand means to drill into (get into ) another report for getting more details. The values 
from the Parent(Main) report are passed to the Child (Detailed Report) for filter conditions. 

Ans-3

Drill down means to navigate one level or summary level to next detailed level.And also the next detailed level is 
shown inthe same report. But drill through navigate from one report to another report. Here we can use some 
parameters and other things are needed. Drill down is applicable for only dims data. 

ans-4

Actually I found 4 different types of drilling mode.

Drill Down  - When you move from summary level to detail 
level in the same report and in the same hierarchy. 

Drill Up - When you move back from detail level to summary 
level in the same report and in the same hierarchy.

Drill Across - When you need to move from one hierarchy to 
another.

Drill Through  - When you need to get additional detail 
data from the Database or from any other report. 

SSIS related Interview Questions with answers

Here are some SSIS related Interview Questions with answers. hope they help.

1) What is the control flow
2) what is a data flow
3) how do you do error handling in SSIS
4) how do you do logging in ssis
5) how do you deploy ssis packages.
6) how do you schedule ssis packages to run on the fly
7) how do you run stored procedure and get data
8) A scenario: Want to insert a text file into database table, but during the upload want to change a column called as months - January, Feb, etc to a code, - 1,2,3.. .This code can be read from another database table called months. After the conversion of the data , upload the file. If there are any errors, write to error table. Then for all errors, read errors from database, create a file, and mail it to the supervisor.
How would you accomplish this task in SSIS?
9)what are variables and what is variable scope ?
Answers
For Q 1 and 2:
In SSIS a workflow is called a control-flow. A control-flow links together our modular data-flows as a series of operations in order to achieve a desired result.

A control flow consists of one or more tasks and containers that execute when the package runs. To control order or define the conditions for running the next task or container in the package control flow, you use precedence constraints to connect the tasks and containers in a package. A subset of tasks and containers can also be grouped and run repeatedly as a unit within the package control flow.

SQL Server 2005 Integration Services (SSIS) provides three different types of control flow elements: containers that provide structures in packages, tasks that provide functionality, and precedence constraints that connect the executables, containers, and tasks into an ordered control flow.

A data flow consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations. Before you can add a data flow to a package, the package control flow must include a Data Flow task. The Data Flow task is the executable within the SSIS package that creates, orders, and runs the data flow. A separate instance of the data flow engine is opened for each Data Flow task in a package.

SQL Server 2005 Integration Services (SSIS) provides three different types of data flow components: sources, transformations, and destinations. Sources extract data from data stores such as tables and views in relational databases, files, and Analysis Services databases. Transformations modify, summarize, and clean data. Destinations load data into data stores or create in-memory datasets.
Q3:
When a data flow component applies a transformation to column data, extracts data from sources, or loads data into destinations, errors can occur. Errors frequently occur because of unexpected data values.

For example, a data conversion fails because a column contains a string instead of a number, an insertion into a database column fails because the data is a date and the column has a numeric data type, or an expression fails to evaluate because a column value is zero, resulting in a mathematical operation that is not valid.

Errors typically fall into one the following categories:

-Data conversion errors, which occur if a conversion results in loss of significant digits, the loss of insignificant digits, and the truncation of strings. Data conversion errors also occur if the requested conversion is not supported.
-Expression evaluation errors, which occur if expressions that are evaluated at run time perform invalid operations or become syntactically incorrect because of missing or incorrect data values.
-Lookup errors, which occur if a lookup operation fails to locate a match in the lookup table.

Many data flow components support error outputs, which let you control how the component handles row-level errors in both incoming and outgoing data. You specify how the component behaves when truncation or an error occurs by setting options on individual columns in the input or output.

For example, you can specify that the component should fail if customer name data is truncated, but ignore errors on another column that contains less important data.

Q 4:
SSIS includes logging features that write log entries when run-time events occur and can also write custom messages.

Integration Services supports a diverse set of log providers, and gives you the ability to create custom log providers. The Integration Services log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files.

Logs are associated with packages and are configured at the package level. Each task or container in a package can log information to any package log. The tasks and containers in a package can be enabled for logging even if the package itself is not.

To customize the logging of an event or custom message, Integration Services provides a schema of commonly logged information to include in log entries. The Integration Services log schema defines the information that you can log. You can select elements from the log schema for each log entry.

To enable logging in a package
1. In Business Intelligence Development Studio, open the Integration Services project that contains the package you want.
2. On the SSIS menu, click Logging.
3. Select a log provider in the Provider type list, and then click Add.
Q 5 :

SQL Server 2005 Integration Services (SSIS) makes it simple to deploy packages to any computer.
There are two steps in the package deployment process:
-The first step is to build the Integration Services project to create a package deployment utility.
-The second step is to copy the deployment folder that was created when you built the Integration Services project to the target computer, and then run the Package Installation Wizard to install the packages.
Q 9 :

Variables store values that a SSIS package and its containers, tasks, and event handlers can use at run time. The scripts in the Script task and the Script component can also use variables. The precedence constraints that sequence tasks and containers into a workflow can use variables when their constraint definitions include expressions.

Integration Services supports two types of variables: user-defined variables and system variables. User-defined variables are defined by package developers, and system variables are defined by Integration Services. You can create as many user-defined variables as a package requires, but you cannot create additional system variables.

Scope :

A variable is created within the scope of a package or within the scope of a container, task, or event handler in the package. Because the package container is at the top of the container hierarchy, variables with package scope function like global variables and can be used by all containers in the package. Similarly, variables defined within the scope of a container such as a For Loop container can be used by all tasks or containers within the For Loop container.


Question 1 - True or False - Using a checkpoint file in SSIS is just like issuing the CHECKPOINT command against the relational engine. It commits all of the data to the database.
False. SSIS provides a Checkpoint capability which allows a package to restart at the point of failure.

Question 2 - Can you explain the what the Import\Export tool does and the basic steps in the wizard?
The Import\Export tool is accessible via BIDS or executing the dtswizard command.
The tool identifies a data source and a destination to move data either within 1 database, between instances or even from a database to a file (or vice versa).


Question 3 - What are the command line tools to execute SQL Server Integration Services packages?
DTSEXECUI - When this command line tool is run a user interface is loaded in order to configure each of the applicable parameters to execute an SSIS package.
DTEXEC - This is a pure command line tool where all of the needed switches must be passed into the command for successful execution of the SSIS package.


Question 4 - Can you explain the SQL Server Integration Services functionality in Management Studio?
You have the ability to do the following:
Login to the SQL Server Integration Services instance
View the SSIS log
View the packages that are currently running on that instance
Browse the packages stored in MSDB or the file system
Import or export packages
Delete packages
Run packages

Question 5 - Can you name some of the core SSIS components in the Business Intelligence Development Studio you work with on a regular basis when building an SSIS package?
Connection Managers
Control Flow
Data Flow
Event Handlers
Variables window
Toolbox window
Output window
Logging
Package Configurations

Question Difficulty = Moderate

Question 1 - True or False: SSIS has a default means to log all records updated, deleted or inserted on a per table basis.
False, but a custom solution can be built to meet these needs.

Question 2 - What is a breakpoint in SSIS? How is it setup? How do you disable it?
A breakpoint is a stopping point in the code. The breakpoint can give the Developer\DBA an opportunity to review the status of the data, variables and the overall status of the SSIS package.
10 unique conditions exist for each breakpoint.
Breakpoints are setup in BIDS. In BIDS, navigate to the control flow interface. Right click on the object where you want to set the breakpoint and select the 'Edit Breakpoints...' option.


Question 3 - Can you name 5 or more of the native SSIS connection managers?
OLEDB connection - Used to connect to any data source requiring an OLEDB connection (i.e., SQL Server 2000)
Flat file connection - Used to make a connection to a single file in the File System. Required for reading information from a File System flat file
ADO.Net connection - Uses the .Net Provider to make a connection to SQL Server 2005 or other connection exposed through managed code (like C#) in a custom task
Analysis Services connection - Used to make a connection to an Analysis Services database or project. Required for the Analysis Services DDL Task and Analysis Services Processing Task
File connection - Used to reference a file or folder. The options are to either use or create a file or folder
Excel
FTP
HTTP
MSMQ
SMO
SMTP
SQLMobile
WMI


Question 4 - How do you eliminate quotes from being uploaded from a flat file to SQL Server?
In the SSIS package on the Flat File Connection Manager Editor, enter quotes into the Text qualifier field then preview the data to ensure the quotes are not included.
Additional information: How to strip out double quotes from an import file in SQL Server Integration Services
Question 5 - Can you name 5 or more of the main SSIS tool box widgets and their functionality?
For Loop Container
Foreach Loop Container
Sequence Container
ActiveX Script Task
Analysis Services Execute DDL Task
Analysis Services Processing Task
Bulk Insert Task
Data Flow Task
Data Mining Query Task
Execute DTS 2000 Package Task
Execute Package Task
Execute Process Task
Execute SQL Task
etc.

Question Difficulty = Difficult

Question 1 - Can you explain one approach to deploy an SSIS package?
One option is to build a deployment manifest file in BIDS, then copy the directory to the applicable SQL Server then work through the steps of the package installation wizard
A second option is using the dtutil utility to copy, paste, rename, delete an SSIS Package
A third option is to login to SQL Server Integration Services via SQL Server Management Studio then navigate to the 'Stored Packages' folder then right click on the one of the children folders or an SSIS package to access the 'Import Packages...' or 'Export Packages...'option.
A fourth option in BIDS is to navigate to File | Save Copy of Package and complete the interface.



Question 2 - Can you explain how to setup a checkpoint file in SSIS?
The following items need to be configured on the properties tab for SSIS package:
CheckpointFileName - Specify the full path to the Checkpoint file that the package uses to save the value of package variables and log completed tasks. Rather than using a hard-coded path as shown above, it's a good idea to use an expression that concatenates a path defined in a package variable and the package name.
CheckpointUsage - Determines if/how checkpoints are used. Choose from these options: Never (default), IfExists, or Always. Never indicates that you are not using Checkpoints. IfExists is the typical setting and implements the restart at the point of failure behavior. If a Checkpoint file is found it is used to restore package variable values and restart at the point of failure. If a Checkpoint file is not found the package starts execution with the first task. The Always choice raises an error if the Checkpoint file does not exist.
SaveCheckpoints - Choose from these options: True or False (default). You must select True to implement the Checkpoint behavior.

Question 3 - Can you explain different options for dynamic configurations in SSIS?
Use an XML file
Use custom variables
Use a database per environment with the variables
Use a centralized database with all variables

Question 4 - How do you upgrade an SSIS Package?
Depending on the complexity of the package, one or two techniques are typically used:
Recode the package based on the functionality in SQL Server DTS
Use the Migrate DTS 2000 Package wizard in BIDS then recode any portion of the package that is not accurate


Question 5 - Can you name five of the Perfmon counters for SSIS and the value they provide?
SQLServer:SSIS Service
SSIS Package Instances - Total number of simultaneous SSIS Packages running
SQLServer:SSIS Pipeline
BLOB bytes read - Total bytes read from binary large objects during the monitoring period.
BLOB bytes written - Total bytes written to binary large objects during the monitoring period.
BLOB files in use - Number of binary large objects files used during the data flow task during the monitoring period.
Buffer memory - The amount of physical or virtual memory used by the data flow task during the monitoring period.
Buffers in use - The number of buffers in use during the data flow task during the monitoring period.
Buffers spooled - The number of buffers written to disk during the data flow task during the monitoring period.
Flat buffer memory - The total number of blocks of memory in use by the data flow task during the monitoring period.
Flat buffers in use - The number of blocks of memory in use by the data flow task at a point in time.
Private buffer memory - The total amount of physical or virtual memory used by data transformation tasks in the data flow engine during the monitoring period.
Private buffers in use - The number of blocks of memory in use by the transformations in the data flow task at a point in time.
Rows read - Total number of input rows in use by the data flow task at a point in time.
Rows written - Total number of output rows in use by the data flow task at a point in time.
*************************************************************************
SSIS Interview Questions
1. What does a control flow do?
2. Generically explain what happens inside a data flow task?
3. Explain what ETL is?
4. Which task would you use to copy, move or delete files?
5. Which transform would you use to split your data based on conditions you define?
6. Explain the pros and cons of deploying to a file system vs msdb?
7. If you did not know the answer to a question what would be your next step to find the answer? 
8.what ia a breakpoint in ssis? How is it setup? How do you disable?
9.Can you rollback a transaction on SSIS? Explain step by step how?
10.What is the file extension of the SSIS Package ?
   
 1. About previou project
2. What kind of activities did u do as a DBA?
3. How do you do error handling in SSIS?
4. How do you do trouble shooting in case of change of request in SSIS?
5. How do you decide to go for either start schema or Snow flake schema?
6. Give a scenario where in you have designed a warehouse database for a existing OLTP database
7. what are slowly changing dimensions and explain a scenario where u have implemented?
8. Give a scenario where u have written a complex MDX
9. Give a scenario where u were involved in discussions with customer as a BA
10. How will you handle a SSIS ETL requirement and how will you give the tasks to your team members
11. What is data staging and validation?

Video ********************
http://perseus.franklins.net/dnrtv_0026.wmv
http://perseus.franklins.net/dnrtv_0027.wmv
http://perseus.franklins.net/dnrtv_0028.wmv
************************************
1. What is difference between merge and merge join
2. Which native transformation dose not have error flow redirection.
3. How one can handle transactions in ssis
4. What are pre requisites for handling transactions in SSIS.
5. What is difference between execute SQL task and OLE DB command
6. What is relevance of ? while configuring OLE DB transformation.
7. What is use of multicast 
***************************************************************
Q-How to Generate an Auto Incremental Number in a SSIS Package?
Auto incremental numbers in a SSIS package can be provided using script components. The script component should be dragged and dropped to the data flow and Transformation should be the component type.. 

Q-Lookup's are a key component in SQL Server Integration Services (SSIS). Explain its purpose
Lookup transformation combines data from two sources. The fields of these sources are matched. The lookups performed by the transformation are case sensitive................
Read answer
Q-How to unzip a File in SSIS?
Execute Process Task in the Control Flow task can be used to unzip a file. The execute process task needs to be dragged and dropped to the control flow followed by configuration of Executables to specify the path of application, Arguments to extract zip files (path) and Working directory
SQL Interview Questions  
What is X-path?
What is Validation? How do we do?
Difference between Group-by and Order-by?
What is FILLFACTOR?
How do we return value in Store proc?
In Store Proc how do we only give read permission?
Where do we write SQL commands in DTS?
How do we take a file from network for DTS?
How do we move DB from one server to another server?
What are DBCC commands?
What is difference between logical DB and Physical DB?
What are the difference between Cluster and Non-clusters?
What is Inner Join and Outer Join?
What are the naming conventions for index?
What is * schema and snow flake schema?
What is difference between Dimensional Table and Fact table?
What are the difference between DTD and XML?
What is XML file specification?
How do we download and upload data from XML file?

1) What is olap and oltp. do u know the difference b/w them??
2) What are the components of ssrs
3)what is notification service and where do u use it
4)what is ssis? diff b/w ssis and dts
5)what is sql profiler?
6)what is msdtc?
7)what is firewall in sql server? how do u change it??
8)what is a linked server?
9)what is sql injection?
10) do u know what is lock escalation??
11) what is clustering in sql server? Explain AA and AP modes? What happens when the common hard drive fails..
12) what are isolation levels??
13) how do u write extended store proc's?? difference b/w extended and regular store proc's
14)what is an orphan sessions??
15) what is a temp table?? How do u create and its advantages and disadvantages
16) what is a transaction log? What do u do if the transaction log growth is abnormal??

17) Can DTS be used to send mail??

18) what is a DTS connector. Name some for example.

19) How do u connect to another oracle server??

20) Is the data transfer between linked servers on demand or continuous??

What type of replication did you use and what are all the steps?
What is an index and how many types and what is the difference?
How can you find out a deadlock in a stored proc?
What are the steps we have to take for best performance?
What is DTS?
How do you take transactional back and how will you restore it, if is there any differential back up?
What is isolation?
When did you use triggers?
How many ways we can transport a database?
What are all the DBCC commands generally use in daily basis?
What are all the responsibilities in a daily basis?
How many servers in your company and what is the maximum size of the database you have involved? 25 server
Have you involved in production support?
Have you worked on OLTP and OLAP, and what are they?
When will dead locks occur?
How can you remove dead locks in a stored proc?
***************************************************
What are the Stored Proc you written?
What is the purpose of the Proc?
What is the difference between Indexes?
What are the disadvantages of Indexes?
Why do we use Identity columns?
If we delete a row in a table which has Identity column and if we want to insert in the same row with different row what should we do?
How do we populate first 10rows from a table?
What is a cursor and what are the disadvantages?
How do we check the performance of a stored proc?
How to get first 3 characters from a string?
How to remove blank spaces from a String?
How SQL Server tell us to use Index on a particular column?
You can use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes. This trace will show which tables are being scanned by queries instead of using an index.


What are triggers? Tell me where do you used in your previous project?
What is group-by and order-by where do you used in your stored proc?


What is a global variable?
Diff b/w scope_identity and @identity

They both will return a newly generated ID value but the difference is:

The @@IDENTITY function returns the last IDENTITY value that was generated in your connection
It returns the last IDENTITY value generated in the scope. So what is the scope? The scope is a batch of SQL code such as s stored procedure, a trigger, or a user defined function.

How do u make sure that a query does not return a null value?


What is Colaese stmt in T-Sql?

The COALESCE Function: A more efficient approach to creating dynamic WHERE clauses involves using the COALESCE function. This function returns the first non-null expression in its expression list.

Diff b/w delete and truncate?? Which one is better and why?
Diff b/w Select into and Create
Diff b/w where clause and having clause
Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

What is a corelated query?
What is update statistics?
This command is basically used when we do a large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.
What is left outer join?
**********************************************
How to generate reports automatically?
I have 5 reports how to generate these 5 reports automatically at end of the day
   

HCL,Infosys,Mindtree and other companies dotnet interview Question.

http://subhashnetworld.blogspot.com/2010/10/mind-tree-dotnet-interview-question.html

Delegate In C#

http://www.c-sharpcorner.com/UploadFile/Ashush/Delegates02152008155757PM/Delegates.aspx

Design Patterns

http://wiki.asp.net/page.aspx/276/design-patterns/

Interview in DELL (28/10/2010)

1.suppose i've a web site http://test.com/  when put it in the url i want to display the default.aspx page how ?
2.what is HTTPHandler and HTTPModuler ?
3.what is the life cycle of the View state ?
4.we have two user control us1 and us2 , in us1 we have 3 like like 1,2,3,4,5,6,7,8 when i click on the 1 the employee
should disply in the us2, how  we create the link between two user control,
5.How to dispaly records in the dropdown , the dropdown is in the gridView Control.
6.Give brief about your profile ?
7.what artitecture you are used in your project ?

Q: what is HTTPHandler and HTTPModuler ?
http://www.15seconds.com/issue/020417.htm
ANS- During the processing of the HTTP Request only HTTPhandler will be called whereas more than one HTTPModuls can be called,
*HTTP handlers are the .NET components that implement the System.Web.IHttpHandler interface.
any class that implements the IHttpHandler interface can act as a target for the incoming HTTP requests.
HTTP handlers can be called directly by using their file name in the URL.

<httpHandlers>
 <add verb="supported http verbs" path="path" type="namespace.classname, assemblyname" />
<httpHandlers>
ASP.NET uses handlers for processing .aspx, .asmx, .soap and other ASP.NET files. 

handlers are mostly used for adding new functionalities to Web servers

What is the difference Between Finalise and dispose methods

Few quick points on Finalize() and Dispose() in C#:

1. Finalize() is the C# equivalent of destructor ~Object() syntax in C#. In VB.Net you implement the Finalize() by overriding it. But, in C# the compiler translates the destructor to a Finalize() method.

2. Finalize() can NOT be overridden or called in C#.

3. Since, Finalize() is called by the Garbage Collector, it is non-deterministic.

4. Dispose() has to be implemented in classes implementing IDispose interface.

5. Its the right place for freeing-up unmanaged resources like file, handles, and connections etc.

6. Dispose() method is called explicitely in the code itself.

7. Dispose() method is automatically called (for objects which implement IDispose), when used in a "using" statement.

Finalize is the destructor which is normally used. Finalize is called automatically when the .NET runtime determines that the object is no longer being used.

Design Pattern : If your classes use unmanaged resources, you need to implement both Dispose & Finalize. Dispose() is called by user code, that is, the code that is using your class.
Finalize/Destructor cannot be called by User code, it's called by Garbage Collector

Finalize : Is a destructor, called by Garbage Collector when the object goes out of scope. Implement it when you have unmanaged resources in your code, and want to make sure that these resources are freed when the Garbage collection happens.
Dispose : Same purpose as finalize, to free unmanaged resources. However, implement this when you are writing a custom class, that will be used by other users. Overriding Dispose() provides a way for the user code to free the unmanaged objects in your custom class.

Finalize :
1.Finalize() is called by the runtime
2.Is a destructor, called by Garbage Collector when the object goes out of scope.
3.Implement it when you have unmanaged resources in your code, and want to make sure that these resources are freed when the Garbage collection happens.

Dispose :
1.Dispose() is called by the user
2.Same purpose as finalize, to free unmanaged resources. However, implement this when you are writing a custom class, that will be used by other users.
3.Overriding Dispose() provides a way for the user code to free the unmanaged objects in your custom class.

Following the difference between Dispose and Finalize method,

1>CLR uses the Dispose and Finalize methods for performing garbage collection of runtime objects of .Net applications.

2>Clr has a Garbage Collector(GC) which periodically checks for unused and unreferenced objects in Heap.It call Finalize() method to free the memory used by such objects.

3>Dispose is another method which is invoked by Garbage Collector to release the memory occupied by an object.Dispose method needs to be explicitly called in code for removing an object from Heap.

4>Dispose method can be invoked only by the classes that IDisposable interface.

Interview 26/10/2010 bangalore company

1.what is IL ?
2.what is JIT ?
3.What is CLR ?
4.What is Profiler ?
5.What is polymerfisim in C# ?
6.What is difference between virtual and New ?
7.what is dispose ? why it is used ? when it is used ?
8.What is the coding standard of the code are you follow Microsoft coding  standard ?
9.How security maintained by the CLR ?
10.Define your profile(Resume) ?
11.What is Finalize ?
12.What is GC.SuppressFinalize ?
13.What is Idisposable interface ?
 


Q.What is IL ?
Ans- MSIL is a platform independent language meaning that it does not dependent on the type of platform,
all the code written in .Net will first be converted into intermediate language and then .net compiler compiles it and generate a machine language code.
Main advantages of intermediate language
Intermediate language is independent of any language and hence there is possibility to create applications with modules that were written in different programming language supported by .net,
This means that we can develop different modules in an application in different programming language and can compile the application.

Q What's the difference between override and new in C#? 
http://www.dotnetinterviewfaqs.com/difference-between-override-and-new-in-polymorphism.aspx
We can use new or override to rewrite a method in an child class.
But what is the difference between new and override?
*****************************************
DerivedType derivedInstance = new DerivedType();
BaseType baseVar = derivedInstance;
baseVar.CallMethod();
------------------------------------------------------------------------
If the DerivedType.CallMethod() is defined as "new", the above code will
code the BaseType version. If it is defined as "override", it wil call the
DerivedType version.

If you are using inheritance to get polymorphism, you probably want
override.
**************************************************
http://bytes.com/topic/c-sharp/answers/261357-what-difference-between-new-override

Fundaments in WCF

Please follow these URL For Fundamental thing in WCF
1.  http://www.dotnetspider.com/forum/194633-Fundaments-WCF.aspx
 2. http://www.aspskills.com/windows-communication-foundation-wcf/397/
Difference between WCF and Web service 
1.   http://www.aspskills.com/difference-between-wcf-and-web-service/388/
wcf interview questions 
1.   http://www.aspskills.com/wcf-interview-questions/571/

How to create SUB REPORT

Create a Main Report
inside the main report drag and drop the subreport and link with the partcular report throw the General Tab.
in visibility tab just choose the Visible option.
In Parameter tab just put the parameter name of report.
In output tab just choose "Auto"

In case of no records please display the message "No Records" in SSRS

=sum(Fields!ward1.Value)
in the report i was devloped on interesting thing suppose the dataset is not returned any records in this case as per the requirement we need to show "No open work is there" ort this type of message
Solutions:-Insert a Text Box then open the properties of the text box
Text box properties screen will displaying
click on the Visibility Tab
write code on the Expression =iif(rownumber("DataSet4") > 0,true,false)
it means if the dataset have records then dispaly the text box
similarly you need to disable to the table where we display the data which is returned from the Dataset4
same press click on the Visibility Tab
=iif(IsNothing("DataSet4"),"test",false)
it means it datase4 have no records then no need to show the table

Create Group in SSRS 2005

Click on the row and select Edit group or create group
Grouping and Sorting Properties Box will display.
Name-put the name of the group
Group on-put the filed name on that base you want to create group
check Include group header.
Click in the "Visibility" Tab
on that interface select Hidden option and checked Visibility can be toggled by another report item
Report item chose the text box

How change the color of row in SSRS Reports

=iif(RowNumber(Nothing) mod 2, "Beige", "WhiteSmoke")
by using this code in expression the color of the row changed 1st row "Beige" and 2nd row "WhiteSmoke"
=FormatCurrency(Fields!Total_DO_Value.Value,2)
The above expression is used for the Currency Format
=IIF(Fields!Contractual_StartDate.Value IS Nothing, "" ,FormatDateTime(Fields!Contractual_StartDate.Value,2))
The above expression is used if StartDate is nothing then display blank space i not then display the date.

 

Interesting Issue in SSRS(10/06/2010)Benefits Report

One interesting issue is coming when i was developing one report in SSRS package.
What is it ?
I was used three procedure,I've one main procedure inside main procedure two other procedure is used.
when I'm executing  main procedure two sub procedure throwing errors.if i am executing other two sub procedure separately it was not showing any errors.

after that the main procedure also working fine what is the cause i was not find out till now today i'm trying to find out  what is the cause. of this type of error.

Good Information