How to know SQL Server Version using backup file?

Backup and restore these are common tasks while working with the database. But before restoring the database it is necessary that we should know about some information about backup file because it may be possible that we have created backup file in upper version of SQL Server and we are trying to restore it in lower version of SQL Server. In this is case database can not be restored.

So before facing this problem it is better to know about backup file means in which version it was created.

To get this information we can simply use RESTORE command with HEADERONLY. Check the bellow command -


RESTORE HEADERONLY 
         FROM DISK = N'd:\filename.bak' 

By passing the path of backup file you can run this command, you will get result like this-

 

By seeing SoftwareVersionMajor and SoftwareVersionMinor you can identify the version of the software.

To know about each column help follow this link-

http://technet.microsoft.com/en-us/library/ms178536.aspx

Here you will get description for each column.

Considerations for SQL Server Replication

There are some points to consider while implementing replication. These points include some technical and environment requirements for the replication, means in this article, I will explain about configuration setting for SQL Server replication. 

Check the below points, here you will get clear idea about configuration for replication-

1.  Select the proper version of SQL Server

As we know that there are different versions of SQL Server present in the market, so it must that first we have to select proper SQL Server version for replication.

Below table will make you understand to select proper version of SQL Server for Replication.

Reference from- SQL Server Versions





By seeing above table, we can easily identify suitable version of SQL Server for our requirement.

2. Name of the Server (Use actual name of server)

While implementing replication, we should use name of the server (Actual Name of the Server) not IP address, If the user logins to server using IP (without actual SQL Server Name) then the replication wizard for creating publication will not work and it will generate error like this-

 


So, we should use Server Name not IP Address of server.

3.  Start SQL Server Agent Service

This service should be started before going for replication, to start this we can get list of services using the Control Panel and then we can start.

Follow this -  Control Panel\All Control Panel Items\Administrative Tools\Services

 

By default, the SQL Server Agent service is disabled when SQL Server is installed unless you explicitly choose to auto start the service during installation.

4.  Enable the TCP IP Ports of SQL Server Instance

TCP IP Ports of SQL Server should be enabled because data transfer is done using TCP Protocol.
To enable this just open the SQL Server Configuration manager and Check for SQL Server Network Configuration, Here we will find Protocols for SQL Server Instance.   Just right click on TCP/IP protocol and enable it.

5. Enable the Named Pipes of SQL Server Instance

As we have enabled the TCP IP Ports, the same way we have to enable Named pipes using the SQL Server Configuration manager.
You can see below image, Here TCP/IP port and Names Pipes are enabled.


 


6. Turn off the Firewall settings

In replication web synchronization requires additional ports to be opened on the firewall because of that firewall should be turned off.

7. Sufficient Disk Space

It may be possible that numbers of Subscribers are more, so in this case we should be more careful about disk space because after synchronization all the database will be sync and this require more space. Synchronization generates high values of data so it is must that we should have sufficient disk space to save the data.

8.  High Bandwidth

It is must that Bandwidth should be good because In case of large amount of data, it uses large amount of bandwidth. Database synchronization will be fast in case of high bandwidth.

9. Proper Database design

It is must that database should be designed properly to reduce possibility of conflict. It may be possible, conflict may occur but if our database design is proper then conflict can be resolved very easily.

We can consider these points while designing database structure-

(i) For Update Conflict-   Each record should have one column to track update time of the record so that while resolving the conflict, we can decide that which record should be reflected (Publisher or Subscriber record). By this way update conflict can be solved very easily.

(ii) For Insert Conflict- Each record should have primary key, it should not repeat at other subscriber also because after synchronization all records will be in same place. By this way insert conflict will not occur.

Like this, we can reduce possibility of conflict.

10. Selection of replication type

It is must that we should well-known about types of replication before implementing replication because we have to select the proper type of replication based on our environment.

These are the considerations, when we go for replication. By following these points implementation of replication can be done easily.

How to insert bulk record into database using SQL Server,C# and XML

If we have list of records then we can not process each record to insert into database because it is time taking process and it will degrade the performance.In this case we can use bulk insert. Suppose if we have bulk record in xml then we can easily dump that xml record into database.

Check the below code sample to insert bulk record.

Suppose this is sample XML string -

XML Data String

<?xml version='1.0' encoding='utf-8'?>
<Employees>
  <Emp>
    <EmpID>101</EmpID>
    <EmpName>Jitendra</EmpName>
    <Age>27</Age>
  </Emp>
  <Emp>
    <EmpID>102</EmpID>
    <EmpName>Ramesh</EmpName>
    <Age>30</Age>
  </Emp>
  <Emp>
    <EmpID>103</EmpID>
    <EmpName>Raj</EmpName>
    <Age>24</Age>
  </Emp>
 </Employees>
 
 Now check the below stored procedure –

Stored Procedure

This stored proceduer will take the XML string as input and it will process this XML to insert into table.

--Stored procedure to insert bulk record using XML

CREATE PROCEDURE [dbo].[SP_EmpBulkInsert]   
(    @XMLData text    )   
AS   
BEGIN   
 SET NOCOUNT ON   
 BEGIN TRANSACTION   
 Begin Try   

  DECLARE @XMLDocPointer INT   
  EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @XMLData   

   INSERT INTO tab_Employees
   (EmpID, EmpName, Age)   
   SELECT EmpID,EmpName,Age  
   FROM OPENXML(@XMLDocPointer,'/Employees/Emp',2)   
   WITH  (EmpID INT,EmpName nvarchar(50),Age INT)    

   EXEC sp_xml_removedocument @XMLDocPointer   
   COMMIT TRANSACTION   
   Return 0;    
 End Try   
 Begin Catch   
   ROLLBACK TRANSACTION   
 End Catch   
END

In this stored procedure I am inserting data into tab_Employees table, based on column selection data will be inserted to this table. Now chek the below code to call this stored procedure from C# application.

C# Code-

//Code to insert bulk records
        private void btnBulkInsert_Click(object sender, EventArgs e)
        {
            try
            {
                //Code to insert bulk record using XML
                string strConnectionString = "Your connection string";
                //Getting XML string
                string strXML = GetXMLString();
               
                //Connecting to database
                using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection())
                {
                    conn.ConnectionString = Convert.ToString(strConnectionString);
                    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SP_EmpBulkInsert", conn);
                    cmd.Parameters.AddWithValue("@XMLData", strXML);
                    cmd.CommandType = CommandType.StoredProcedure;
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    MessageBox.Show("Records inserted successfully.");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

         //XML string to insert bulk record
        private string GetXMLString()
        {
            return  @"<?xml version='1.0' encoding='utf-8'?>
<Employees>
  <Emp>
    <EmpID>101</EmpID>
    <EmpName>Jitendra</EmpName>
    <Age>27</Age>
  </Emp>
  <Emp>
    <EmpID>102</EmpID>
    <EmpName>Ramesh</EmpName>
    <Age>30</Age>
  </Emp>
  <Emp>
    <EmpID>103</EmpID>
    <EmpName>Raj</EmpName>
    <Age>24</Age>
  </Emp>
 </Employees>";
        }

In above code, I am getting XML string using GetXMLString() method. After getting the XML string I am passing this string as parameter for stored procedure. Finally when this code will be executed then you will find the new records in tab_Employees table.

How to update record without primary key?

If we have any primary key in table then it is very easy to update the records  in the database table, but generally we face problem when we don't have primary in table because on that time we can not use any particular column to update records because that column can contain duplicate values.

But there is work around for this problem, we can use” ROW_NUMBER()” function to generate identity value for each record and then based on that “ROW_NUMBER” we can delete record from the table.

In this article, I am explaining - How to update nth record of the table?
Or
How to update record without primary key?


For this I am using “ROW_NUMBER()” function in query, Here is syntax -

Syntax-

WITH Record AS (SELECT *, ROW_NUMBER() OVER (ORDER BY [ColumnName]) AS rownum FROM TableName where condition) update Record  set  col1=value ,col2=value where Record.rownum = value

Example-

WITH Record AS (SELECT *, ROW_NUMBER() OVER (ORDER BY EmpName) AS rownum FROM tab_Employee ) update Record  set  Age=25 ,Salary=45000 where Record.rownum = 1


In this example,   “ROW_NUMBER()” is a function which will generate sequence number for each record, here ordering will be based on EmpName column, you can change it.

Here "rownum" will be additional column in the result set, this will be used to update the record.

Here “Record “ is result set.


Try this example.


Thanks
 

Dot Net Interview questions for 2+ years experience

Hi All,

If you have 2+ years of experience then you should be ready for more paper work instead of oral in interview because there you have to prove yourself in-front of interviewer.

I have collected some interview questions those have attended by me and my friends.

In this article, I am giving you some Dot Net Interview questions for 2+ experiences.

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

1. Tell about your technical profile.
2. What was your role in your project?
3. In which module you have worked explain?
4. Describe GridView events (Paper).
5. How to change Label's color based on Label's value  (Paper)?
6. Write the code to perform edit and delete operations using  GridView (Paper).
7. What are the Validation controls in ASP.Net?
8. How you will implement Custom Validator Control functionality (Paper)?
9. What are Generics?
10. What is the  difference between HashTable and Dictionary?
11. What is Ajax and  Jquery?
12. Which control have you used in AJAX?
13. What is the use of ModalPopUpExtender (Paper)?
14. WCF Basics (Types of binding)
15. What are Database Constraints?
16. Difference between Primary and Unique key?
17. Difference between Function and Procedure?
18. Can we store DataSet in View State?
19. When we will store DataSet in Session then which memory will be filled client side or server side?
20. Difference between reference and out parameter?
21. How to execute stored procedure?

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

1. What is View State?
2. Where is View State is saved and how View State value is retained between Post Back. (Practical)?
3. Form Authentication Process (Using Web.Config file and Database in paper).
4. If View State value is "X" and I have changed it to "Y" in Page_Load then what will be the final value of View State?
5. Page Life cycle with use.
6. Performance Analyzer tool.
7. How to declare unique key?
8. Diff. between Equi join and Right outer join (Paper)?
9. Define Caching types.
10. How to implement SQL Cache (Paper)?
11. How to call Web service using AJAX (Paper)?
12. How to change Color of Label using Jquery (Paper)?
13. What is Table Object/Variable?
14. How to call stored procedure using Entity Framework?
15. What is the difference between Overloading and Overriding?
16. What is the difference between ExecuteScalar() and ExecuteNonQuery()?
17. What if I will pass Select * in ExecuteScalar()?

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

1. Tell me about projects.
2. Your role in project.
3. Which performance tool you have used?
4. Define abstract class and interface?
5. Why to use static members?
6. What is partial class and advantages?
7. GridView and DataList differences.
8. State Management type.
9. What is view state and use?
10. Caching techniques.
11. WCF and Web service differences.
12. Define WCF contracts.
13. Define design pattern.
14. What is facade pattern?
15. Triggers use and types.
16. Define cursor.
17. Difference between clustered and non-clustered index.
18. How many clustered index can be declared for a table.
19. What is view?
20. What is AJAX and Update-panel?
21. If you have 2 Update-panel then how to update one update panel from second?

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

1. Define your Technical skills.
2. Your Role in project.
3. Define features of OOPS.
4. How you will replace the functionality of parent class function in child class.(Paper)
5. Difference between Interface and abstract class (Use).
6. Functions of CLR.
7. Where you will use Cookie, Session and View State.
8. What are InProc and OutProc Session?
9. If you will save session in OutProc then where it will be saved and when it will be expired.
10. Difference between Web service and WCF
11. Design pattern.
12. Write sequence of select query-

 [ WITH ]
SELECT select_list [ INTO new_table ]
 [ FROM table_source ]
 [ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]

The UNION, EXCEPT and INTERSECT operators can be used between queries
to combine or compare their results into one result set.

13. What is ROW_NUMBER () in SQL Server?
14.  Difference between Union and Join.
15. Return value for ExecuteNonQuery().
16. Can we execute DML command in ExecuteScalar()?
17. Difference between DataSet and DataReader.
18. If provider is given OracleClient then can we connect with SQL Server?
19. Where you have used JQuery in your project?
20. Namespace for web-part (SharePoint)    - System.Web.UI.WebControls.WebParts Namespace
21. For creating a site what are the main concepts you need to consider in UL layer, middle-ware and Database Layer.

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

1. Explain different versions of .Net Framework.
2. What is OPPS and explain concept of OPPs?
3. What is static members and where you have used static members in your project?
4. What is difference between Constant  and ReadOnly?
5. Explain different Access Specifier.
6. What is Entity Framework? Explain difference between LINQ and Entity Framework.
7. Explain Page life cycle.
8. Explain Membership provider.
9. What is WCF?
10. Explain different types of WCF Binding and where you will use which binding?
11. Explain Index in SQL Server. Have you created any index for your project?
12. What is difference between Primary key  and Unique key?
13. What is Stored procedure and it's advantages?

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

1. What is difference between ArrayList and HastTable?
2. Where we have to use static members?
3. What is constants?
4. What is difference between Interface and Abstract class?
5. How you will handle error in C#?
6. What is the use of Virtual?
7. What is App Domain?
8. What is Session and Cache object?
9. What is Worker Process?
10. Write syntax for stored procedure.
11. How to handle error in Stored Procedure?
12. Explain Binding in WCF.
13. Explain Contract in WCF.
14. How you will host WCF service?
15. How to handle Error in WCF service?
    
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------

1. Explain your Projects
2. What is Generics?
3. What is Delegate and Events?
4. What is difference between Protected and Protected Internal?
5. What is Entity Framework?
6. Explain Page Life Cycle.
7. Explain Session and Application object.
8. What is Worker Process?
9. What is ISAPI?
10. Use of Global.asax?
11. What us JQuery and How you will use JQuery in your application?
12. What is ISNAN?
13. Have you used Telerik controls in your Project?
14. What is stored procedure?
15. How to call Stored Procedure using .Net code?
16. What is Delete and Truncate?
17. How you will insert Bulk records in database?

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


These are the questions those have attended by me and my friends in different companies; here we can see that most of the questions are same. I hope these questions will help you all to attend the interviews.


Thanks

How to get nth record form table?

If we are filling result to any data-source then it is easy to get nth record from that data-source, by passing row index we can access nth record, but for this we have to perform two steps.

1. First we have to fill data source
2. Based on row index we have to get data from data-source.

Instead of these steps, we can do this directly in database side only, for this we have to use ROW_NUMBER () in query. This function include one sequence number for each record in result set. For this first we have to include ORDER BY clause in query because based on that only ROW_NUMBER() function include one sequential number for record. 

Syntax-

select *
from (select ROW_NUMBER()  over (order by columnname) as rownum from tablename) t1
where t1.rownum =index

Here index is nth row; we have to replace with any number.

Here is example-
 
Select *
from (select ROW_NUMBER()  over (order by columnname) as rownum from tablename) t1
where t1.rownum =2

This query will return 2nd row from table. Result set contains sequence number based on ORDER BY clause which has performed over given column. Sequence number can be different if we are passing different column name for ordering. We can also put where condition in inner query.


Thanks