Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage


  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats

    • 7,319,108 Views

Archive for February 1st, 2008

sp_reset_connection

Posted by decipherinfosys on February 1, 2008

While using SQL Server Profiler to trace out events, you might have noticed calls to the system stored procedure: sp_reset_connection. The only thing that is mentioned in BOL is:

The sp_reset_connection stored procedure is used by SQL Server to support remote stored procedure calls in a transaction. This stored procedure also causes Audit Login and Audit Logout events to fire when a connection is reused from a connection pool.

So, what exactly does this system stored procedure do? This system stored procedure gets executed when the ODBC or the OLEDB connection pool takes a connection that has been released to the connection pool and is still active. Before re-using the connection from the pool, this system stored procedure gets executed to make sure that the connection options are re-set. We can see this by a simple example in SQL Server Management Studio:

SET NOCOUNT ON

You can now see which of these options are set in your environment:

DECLARE @options INT

SELECT @options = @@OPTIONS

IF ( (512 & @options) = 512 ) SELECT ‘NOCOUNT’

And you will get:

——-
NOCOUNT

And now, if we re-set the connection (in SQL Server Management Studio, you can simulate it by right clicking and then Connection/Change Connection and then connect to the same instance as before) – and then when you execute the above, you will see that NOCOUNT will not be present since the connection has been re-set.  The same thing happens when sp_reset_connection gets executed. There are a lot of things that are re-set by this stored procedure like re-setting of the SET command options, re-setting of @@identity, @@rowcount, @@trancount etc., aborting any open transactions, closing out any open SQLXML handles, releasing any memory that was owned up by the connection etc.. Here is a link to a site which states what does and does not get re-set by this system stored procedure:

http://www.sqldev.net/misc/sp_reset_connection.htm

This is a lightweight stored procedure so there is no cause of worry to see it along with your Audit Login and Logout events.

Posted in SQL Server | Leave a Comment »

Yet another big Acquisition: MSFT bids $44.6 billion for Yahoo

Posted by decipherinfosys on February 1, 2008

Microsoft made a $44.6 billion dollar bid for the acquisition of Yahoo today. This is not the first time that they have made a bid for Yahoo. You can read more on this in this cnn post:

http://money.cnn.com/2008/02/01/technology/microsoft_yahoo/index.htm?cnn=yes

Posted in News | 1 Comment »