SQL SERVER – Two Connections Related Global Variables Explained – @@CONNECTIONS and @@MAX_CONNECTIONS

Few days ago, I was searching MSDN and I stumbled upon following two global variables. Following variables are very briefly explained in the BOL. I have taken their definition from BOL and modified BOL example to displayed both the global variable together.

@@CONNECTIONS
Returns the number of attempted connections, either successful or unsuccessful since SQL Server was last started.

@@MAX_CONNECTIONS
Returns the maximum number of simultaneous user connections allowed on an instance of SQL Server. The number returned is not necessarily the number currently configured.

@@MAX_CONNECTIONS is the maximum number of connections allowed simultaneously to the server. @@CONNECTIONS is incremented with each login attempt, therefore @@CONNECTIONS can be greater than @@MAX_CONNECTIONS.

Example:
SELECT GETDATE() AS 'Currunt Time',
@@CONNECTIONS AS 'Total Logins so far',
@@MAX_CONNECTIONS AS 'Max Connection Simultaneously'

Currunt Time Total Logins so far Max Connection Simultaneously
———————– ——————- —————————–
2007-09-03 17:28:12.013 71 32767

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT

Few days ago, one of the Jr. Developer asked me this question (What will be the Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT?) while I was rushing to an important meeting. I was getting late so I asked him to talk with his Application Tech Lead. When I came back from meeting both of them were looking for me. They said they are confused. I quickly wrote down following example for them.

Example:
PRINT 'After ROLLBACK example'
DECLARE @FlagINT INT
SET
@FlagInt = 1
PRINT @FlagInt ---- @FlagInt Value will be 1
BEGIN TRANSACTION
SET
@FlagInt = 2 ---- @FlagInt Value will be 2
PRINT @FlagInt
ROLLBACK TRANSACTION
PRINT
@FlagInt ---- @FlagInt Value will be ?
GO
PRINT '--------------------'
PRINT 'After COMMIT example'
DECLARE @FlagINT INT
SET
@FlagInt = 1
PRINT @FlagInt ---- @FlagInt Value will be 1
BEGIN TRANSACTION
SET
@FlagInt = 2 ---- @FlagInt Value will be 2
PRINT @FlagInt
COMMIT TRANSACTION
PRINT
@FlagInt ---- @FlagInt Value will be ?
GO

ResultSet:
After ROLLBACK example
1
2
2
——————–
After COMMIT example
1
2
2

It is very clear that local variables are not affected with transaction. In both of the above cases we got the same result. Jr. DBA and Application Tech Lead were pleased with this simple example and asked me to blog about it. Let me know if you like this kind of articles. I will post some more like this.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Fix : Error: 3902, Severity: 16; State: 1 : The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

SQL Server Integration Services Error : The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. (Microsoft OLE DB Provider for SQL Server)

Fix/Workaround/Solution:

Option 1:
To work around this problem, do not call the stored procedure by using ODBC Call syntax. You can call the stored procedure in may ways by using ADO. One of the methods is to call a stored procedure by using a command object. (View Example)

Option 2:
If the sql statements are like
BEGIN TRAN

SQL Statements

END TRAN

SET “RetainSameConnection” property on the connection manager to true. This will fix the problem.

Reference : Pinal Dave (http://blog.SQLAuthority.com)