Difference between Sql server 2005 and 2008

Difference between SQL Server 2000 and 2005

Error message when you execute a linked server query in SQL Server: "Timeout Expired"

You may see either one of the following error messages when you execute a linked server 

query:

Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' reported an error. [OLE/DB provider returned Timeout expired]

-or-

Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the provider because a resource limit was reached. [OLE/DB provider returned message: Timeout expired]

Error 7399 is a generic error message that the provider returns, which indicates there is some sort of problem. You must use trace flag 7300 to get a more detailed and useful error message from the provider. The output from trace flag 7300 can help you to determine if this article covers the specific 7399 error message that you receive.

If you execute a DBCC TRACEON (7300, 3604) statement, and you then execute the query, you may see additional information in the error message; however, whether or not you see more information depends on the provider you use. For example:

Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' reported an error. [OLE/DB provider returned message: Timeout expired] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80004005: ].

-or-

Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the provider because a resource limit was reached. [OLE/DB provider returned message: Timeout expired] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute returned 0x80040e31: Execution terminated by the provider because a resource limit was reached.].

Solutions:

To work around this, you can reconfigure the timeout setting.

Based on which type of error you encounter, you can reconfigure the timeout setting as follows:

• Set the remote login timeout to 30 seconds, by using this code:

  sp_configure 'remote login timeout', 30
  go 
  reconfigure with override 
  go 
       

• Set the remote query timeout to 0 (infinite wait), by using this code:

  sp_configure 'remote query timeout', 0 
  go 
  reconfigure with override 
  go 

For more information about the remote login timeout setting and where IDBInitialize::Initialize is called, refer to the "Connecting to an OLE DB Provider" topic in MSDN or the Microsoft SQL Server 7.0 Resource Guide in the BackOffice Resource Kit.

You may also refer to the following topics in MSDN for a description of how the query processor interacts with an OLE DB provider to enable distributed and heterogeneous queries:

• Microsoft SQL Server 2000 Distributed Queries: OLE DB Connectivity
• Basic OLE DB

For more information please refer microsoft KB article.

Identify which SQL Server instance utilizing most of the CPU

Steps to identify the SQL Server instance utilizing most of the CPU

Step 1:- First launch Windows Task Manager to find out the CPU utilization for your database server. Below is screenshot of the Task Manager at the time of the issue I experienced. You can see all three instances have the same executable i.e. 'sqlservr.exe' and also you can see 76% of the CPU is being utilized by the first sqlservr.exe process.

Based on reviewing Windows Task Manager, one immediate option to determine which SQL Server instance is using all of the CPU is to run each SQL Server instances with a different domain account.  For example, "Domain\SQLDev" for the development environment, "Domain\SQLTest" for the test environment, etc.  Unfortunately, in my circumstance all of the SQL Server instances were running under same domain account. In the image above the accounts have been erased, but they would be found in the fourth column i.e. "User Name". 

Step 2:- Now we will add the PID (Process Identifier) column in Windows Task Manager to in order to find out the PID for each process.  The PID is the Windows Operating System Process Identifier assigned to each process running on the machine. In order to enable this column, ensure the "Processes" tab is active then click on the "View" menu, then choose the "Select Columns..." option.  Once on the "Select Process Page Columns" screen click the check box for the option PID and then press the "OK" button to return to the Processes tab of Windows Task Manager.

Step 3:- Now you can see the PID for the 'sqlservr.exe' process which is utilizing most of the CPU. In our example, the PID of this SQL Server instance is 2352.

Now our next step is to determine which SQL Server instance is running this PID. We have two methods to get this information. First, is the SQL Server configuration manager and second method is the SQL Server error log.

From SQL Server 2005 onwards, it is very easy to find the PID for the SQL Server instances using the SQL Server Configuration manager.  However, with SQL Server 2000 and earlier, it is not as straight forward.  We will proceed with the assumption that community members are using SQL Server 2005 and beyond.

Step 4:- Whenever we start a SQL Server instance, a PID which is also know as the "Server process ID" is assigned to that instance and this information is logged in the SQL Server error log. You can see an example of this in the screenshot below.  The "Server process ID" is normally one of the first entries in the log.

Step 5:-Another option is identify the correct PID for your SQL Server Instance is by using the SQL Server configuration manager. This can be accomplished by launching the  SQL Server Configuration Manager and clicking on the "SQL Server Services" option in the left pane. On right side of this interface, you can see the Process ID values associated with the SQL Server services.

By correlating the information from Windows Task Manager and the SQL Server Error Log\SQL Server Configuration Manager you can correctly determine the SQL Server instance which is utilizing most of the CPU.  At this point you can review the SQL Server processes on the aforementioned instance to determine the culprit process(es) which are causing CPU pressure.

-- Shivasagar V

Connect SQL Server from the command prompt

How can we connect SQL server from the command prompt ?

Hi we can also connect SQL Server from the command prompt also. Using this feature is very helpful in some scenarios. Please go through with the below guidelines.

I hope this is very helpful for you. for more information give me a comment I will explain you clearly.

--  Shivasagar v

SQL Server Builds

Service packs list for all versions:

Quick summary:

	RTM (no SP)	SP1	SP2	SP3	SP4
SQL Server 2016      codename ?	RC3
SQL Server 2014      codename Hekaton SQL14	12.0.2000.8	12.0.4100.1 or 12.1.4100.1
SQL Server 2012      codename Denali	11.0.2100.60	11.0.3000.0 or 11.1.3000.0	11.0.5058.0 or 11.2.5058.0	11.0.6020.0 or 11.3.6020.0
SQL Server 2008 R2      codename Kilimanjaro	10.50.1600.1	10.50.2500.0 or 10.51.2500.0	10.50.4000.0 or 10.52.4000.0	10.50.6000.34 or 10.53.6000.34
SQL Server 2008      codename Katmai	10.0.1600.22	10.0.2531.0 or 10.1.2531.0	10.0.4000.0 or 10.2.4000.0	10.0.5500.0 or 10.3.5500.0	10.0.6000.29 or 10.4.6000.29
SQL Server 2005      codename Yukon	9.0.1399.06	9.0.2047	9.0.3042	9.0.4035	9.0.5000
SQL Server 2000      codename Shiloh	8.0.194	8.0.384	8.0.532	8.0.760	8.0.2039
SQL Server 7.0      codename Sphinx	7.0.623	7.0.699	7.0.842	7.0.961	7.0.1063

For more information Kindly refer Microsoft website.

Contained Databases in SQL Server 2012

Problem

While looking through the new features and improvements in SQL Server 2012, we found a potentially interesting feature called Contained Databases. A contained database basically includes all database settings and the metadata within itself thereby resulting in no configuration dependencies on the instance of the SQL Server Database Engine where the database is actually installed. Users will be able to connect to a contained database without authenticating a login at the Database Engine level. This feature really helps to isolate the database from the Database Engine thereby making it possible to easily move the database from one instance of SQL Server to another. In this tip we will take a look at how to configure and use this feature of SQL Server 2012.

Solution

Let us go through an example to configure a Contained Database in SQL Server 2012. Below are the steps to configure and use this feature.

1. Enable Contained Database Feature in SQL Server 2012 Instance Level
2. Create a Contained Database in SQL Server 2012
3. Create an SQL Server User to Access the Contained Database
4. Login and Verify the User Permission on the Contained Database

---

Enable Contained Database Feature in SQL Server 2012 Instance Level

Execute the T-SQL code below to enable the Contained Database feature at the SQL Server instance level.

Use master
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO 
sp_configure 'contained database authentication', 1
GO
RECONFIGURE WITH OVERRIDE
GO 
sp_configure 'show advanced options', 0 
GO
RECONFIGURE WITH OVERRIDE 
GO

---

Create a Contained Database in SQL Server 2012 Using SQL Server Management Studio

Once the Contained Database Feature is enabled at the SQL Server instance level, then create a Contained Database.

1. In the Object Explorer, right click the Databases and select New Database... option from the drop down list.
2. In the General Page you can provide the name of the database as DemoContainedDB and then click on Optionspage on the left side pane.
3. In the Options Page, to create a Contained Database you need to choose the value as Partial in the Other Options drop down list as highlighted in the screen shot below.

4. Finally, click OK to create the Contained Database.

---

Create a Contained Database in SQL Server 2012 Using T-SQL code

You can create a Contained Database by executing the T-SQL code below.

Use master
GO
CREATE DATABASE [DemoContainedDB]
CONTAINMENT = PARTIAL
ON PRIMARY 
( 
NAME = N'DemoContainedDB', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DemoContainedDB.mdf' , 
SIZE = 3072KB , 
FILEGROWTH = 1024KB 
)
LOG ON 
( 
NAME = N'DemoContainedDB_log', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DemoContainedDB_log.ldf' , 
SIZE = 1024KB , 
FILEGROWTH = 10%
)
GO

---

Create a SQL Server User to Access the Contained Database

Once the contained database is created successfully, next create an SQL Server or Windows User, which will have access to the newly created contained database. Follow these steps to create a SQL Server User within the contained database.

1. In the Object Explorer, expand Databases, expand DemoContainedDB Database which was created earlier, expand Security and then right click Users and choose New User... option from the drop down list as shown in the screen shot below.

2. In Database User - New window, you need to choose the User Type value as SQL User with Password and provide the SQL Server User Name and Password. You also have the option to select the Default language andDefault schema. Finally, to create the user click the OK button.

3. You can also create the user by executing the T-SQL code below.

USE [DemoContainedDB]
GO
CREATE USER [ContainedDBUser] WITH PASSWORD=N'Contained$DBUser@5', 
 DEFAULT_LANGUAGE=[English], 
 DEFAULT_SCHEMA=[dbo]
GO

---

Login and Verify the User Permissions on a Contained Database

Let's try connecting to the newly created Contained Database using the SQL Server Login.

1. In the Login tab you need to provide the Server name, choose the Authentication type as "SQL Server Authentication" and specify the Login and Password.

2. In Connection Properties tab, you need to specify the database name as highlighted in the screen shot below in the Connect to database option and then click the Connect button to get connected to the Contained Database.

3. In the screen shot below, you can see that you were able to successfully get connected to the Contained Databaseusing the newly created SQL Server Login which doesn't have access to the Database Engine.

4. You can also see in the screen shot above that the SQL Server Login (Contained User) only has access to the newly Contained Database.

5. Now let us try connecting to the SQL Server Instance using System Administrator (SA) privileges. Here you will be able to see all the databases. However, you will not be able to see the Contained Database User under Server Logins as shown in the screen shot below.

Please refer MSSQL Tips blog