0

Prior to installation our application checks SQL to make sure it has correct permissions along with various settings. One of those settings is @@OPTION NOCOUNT.

I cannot seem to figure out for the life of me how to pull this value from the db.

I've tried this. Doesn't matter if NOCOUNT is on or off, 1 is always returned.

  DECLARE @NOCOUNT INT = 1; IF ( (512 & @@OPTIONS) = 512 ) SET   @NOCOUNT = 10;          
   SELECT @NOCOUNT AS NOCOUNT;

I've tried to create a temp table using separate SQL statements in Java and try to pull the value from there with no success. The tables get created just fine, but the value that's always insert in this case is zero.

CREATE TABLE TempNoCount(tNoCount int);
DECLARE @NOCOUNT int = 0; 
IF ( (512 & @@OPTIONS) = 512 ) SET @NOCOUNT = 1;
INSERT INTO #TempNoCount (tNoCount) VALUES (@NOCOUNT);

It doesn't matter what I do, I cannot seem to pull the correct value from the database.

Even running this query within Java returns zero.

 DECLARE @NOCOUNT INT = 0
 IF @@OPTIONS & 512 > 0 SET @NOCOUNT = 2
 SELECT @NOCOUNT AS NC

I have also just tried simple querys via java:

 SELECT CONVERT(INT,@@OPTIONS & 512) as NOCOUNT;

Still always returns zero regardless if it's on or off.

I've spent a few days on trying to figure out how check if NOCOUNT is on or off using java application and I've run out of ideas.

The strange part is When I use SQLMgmt studio everything works fine and dandy. Creates insert etc everything works. When I use my java program it doesnt.

Is this i limitation of java? Does MS not want to allow you to pull the @@OPTIONS using external programs?

I even tried a stored proc. When I call the stored proc it still returns zero even though the result should be 512 when NOCOUNT is turned....

Any suggestions? I am more then happy to try anything at this point.

gofr1
  • 15,741
  • 11
  • 42
  • 52
user1158745
  • 2,402
  • 9
  • 41
  • 60
  • Can you share more info on which library is being used to connect to SQL Server? – Greg Mar 11 '16 at 22:07
  • Also, in your first TSQL example, how did you set NOCOUNT on/off? Did you try explicitly putting it in there? ie add `SET NOCOUNT ON` at the top. – Greg Mar 11 '16 at 22:13
  • I have SQLMgmt studio open aswell. I turn it on and off from there. – user1158745 Mar 11 '16 at 22:14
  • I am just using the java.sql libarays. import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; – user1158745 Mar 11 '16 at 22:18
  • That is a client side setting, you are turning it on/off for that specific connection. If you want it on/off for a given connection, then explicitly set it, then do your check. – Greg Mar 11 '16 at 22:19
  • Hi Greg, I don't wan to turn it on or off i want to check what the status of it is. However at this time it doesn't seem to be possible. – user1158745 Mar 16 '16 at 13:51
  • As mentioned, SET NOCOUNT is a client side option, not a server side option. You set it at run time, inside your TSQL. Setting it at SSMS side will have zero effect on other connections. See my answer below, it shows you how to toggle it on/off for your java connection, so you can see the effects of it. – Greg Mar 16 '16 at 17:17
  • What about the server side setting? Within SMSS right clicking on the server in SSMS, Properties, Connections, check off No Count. Restart the SQL server. Should this not set NoCount globally? – user1158745 Mar 17 '16 at 19:01

1 Answers1

0

Java.sql.* drivers use JDBC, while SSMS connects to sql server using ODBC i believe (depends on what version of SQL you have). I am going to guess that these SET statements are proprietary specific, thus default values will be different between ODBC/SQLOLEDB and JDBC. So, i will guess that SET NOCOUNT will default to off for ODBC/SQLOLEDB, and on for JDBC.

You can easily verify this, just toggle the SET NOCOUNT value, and run:

SET NOCOUNT OFF
DECLARE @NOCOUNT INT = 1;
IF ( (512 & @@OPTIONS) = 512 ) SET @NOCOUNT = 10;          
SELECT @NOCOUNT AS NOCOUNT;
Greg
  • 3,861
  • 3
  • 23
  • 58
  • SSMS uses the `.Net Provider for SQL Server` (a.k.a. SqlClient) for data access. The current version of SQLCMD uses ODBC. – Dan Guzman Mar 12 '16 at 13:35
  • Humm interesting... I didn't think simply checking if NoCount was on or off was going to be this hard. Let me try the suggestions. – user1158745 Mar 14 '16 at 13:58
  • This must have something to do with Mgmt studio and the commands. So I tried a test. Create the table, create a stored proc that inserted the value of NoCount into the table. The java program exec the stored proc and then I did a select. When the java program exec the stored proc, it always inserted a value of zero, if I exec the stored proc from SQLMgmt studio it add's the correct value... – user1158745 Mar 14 '16 at 18:03