DBCC Checkident question - suppressing printed error message 1

[Kflasph]

I have a stored procedure in which I reset the indentity field using the dbcc checkident ("TableName",reseed,0) command.
Everything works as expected but I am having something strange happen with a few of my customer when they run the stored procedure.

99% are able to run the stored procedure calling the command with no issues but there are a few when trying to run the stored procedure get a msg returned:
Checking identity information: current identity value '40', current column value '0'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.


This return message causes the calling program to bomb because instead of a recordset, the stored procedure is returning this message.

I know this can be resolve by simply adding the 'with no_infomsgs' at the end of the DBCC checkident call but does anyone have any idea why this would not occur with all of my customers?

Is there a setting within SQL that they may be missing?

The SQL versions I am working with are 2000 and 2005.

Anyone with any ideas?

 

[TheBugSlayer]

According to BOL

Whether or not any of the options are specified for a table that contains an identity column, DBCC CHECKIDENT returns (values may vary):
Checking identity information: current identity value '290', current column value '290'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I am not sure you' re getting this as a resultset though...A message is just that, it should not show up as a resultset. How are the users using the SP?

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[Kflasph]

I agree with TheBugSlayer. . .It actually does not return as a recordset but since the called proc in my program expects a recordset and error occurs with the message coming back from SQL.

The stored procedure is only being called by one program run by one person at any time.

After furter investigation, I found a consistency amoung the users having the issue. All were running SQL 2000 without any service packs applied. I'm having them applied to see if this corrects the issue.

 

[TheBugSlayer]

I hope it does. I wrote a small app to test this. It works no matter what order the SP executes the statements in.

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[Kflasph]

Ok - back again. . .

After applying all service pack updates, still receive the message.

Any help would be appreciated.

Thanks,
KFlasph

 

[TheBugSlayer]

It would be nice if you could show code snippets of how your app consumes the SP, even the SPs code (conceal sensitive info). Here is a quick and dirty example of how I tested it on SQL Server 2005 and VS 2008:

In VS, create an app, add a form to it, then add a datagridview to the form:

private void Form1_Load(object sender, EventArgs e)
{
          
           SqlConnection conn = new SqlConnection(Properties.Settings.Default.YourConnectionString);
           SqlCommand command = new SqlCommand("dbo.CheckIDentityTest", conn);

           command.CommandType = CommandType.StoredProcedure;
           command.Parameters.Add("@TableName", SqlDbType.Text).Value = "dbo.RosterTable";

           SqlDataAdapter adapter = new SqlDataAdapter(command);
           DataSet ds = new DataSet();
           adapter.Fill(ds, "Roster");

           this.dataGridView1.DataSource = ds;
           this.dataGridView1.DataMember = "Roster";
}

In SQL Server create the SP

CREATE PROCEDURE [dbo].[CheckIDentityTest] @TableName sysname
AS
BEGIN
    SET NOCOUNT ON 
    DBCC CHECKIDENT (@TableName);
    EXEC('SELECT TOP 10 * FROM ' + @TableName + '')
    --DBCC CHECKIDENT (@TableName); --Same result if ran from here instead.	
END

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[Kflasph]

Here is the solution I found for my issue. . .

TheBugSlayer was correct and no matter how I executed the command in a stored procedure, the function performed correctly. I gave TheBugSlayer a big thank you star for confirming I was not off my rocker and my code was correct.

The issue was resolved with a couple of the customers by updating to SP4. With the few that remained, scanning and performing a fix on tables corrected the issue.

From what I have found, the message was being returned by SQL is normal but. . .
If you are calling a stored procedure from a program and for some reason the data expected could not be returned, the message is returned. It is not an error, it's just the statement returned. Because it can be confusing, the message can be suppressed by adding the 'with no_infomsgs'.

If my above summary is incorrect, please update feel free to add additional comments.

Thank you again to TheBugSlayer for taking the time and looking at my question.

 

[TheBugSlayer]

Glad to know you fixed your issue. If you ever wanted to handle the messages in your program, here is how you would do it:

private void Form1_Load(object sender, EventArgs e)
        {
          
           SqlConnection conn = new SqlConnection(Properties.Settings.Default.AuroraConnectionString);
           SqlCommand command = new SqlCommand("PR.CheckIDentityTest", conn);
[COLOR=red]
           conn.InfoMessage += new SqlInfoMessageEventHandler(Conn_SqlInfoMessageEventHandler);
[/color]

           command.CommandType = CommandType.StoredProcedure;
           command.Parameters.Add("@TableName", SqlDbType.Text).Value = "PR.tRoster";

           SqlDataAdapter adapter = new SqlDataAdapter(command);
           DataSet ds = new DataSet();
           adapter.Fill(ds, "Roster");

           this.dataGridView1.DataSource = ds;
           this.dataGridView1.DataMember = "Roster";
        }
[COLOR=red]
        private void Conn_SqlInfoMessageEventHandler(object sender, SqlInfoMessageEventArgs e)
        {
            //MessageBox.Show(e.Message);
            listBox1.Items.Add(e.Message);
        }
[/color]
    }

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)