restriction error on messages between SQLSERVER and dot.net

Question

Update:

I tried to run this on a local instance of sql-server and sadly it worked!!!

now I know that the code is right and there is some kind of DBA restriction I need to find (and ask the DBA to remove)

Any ideas?

using System; 
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace testDBMessages
{
    public class CGeneral
    {
        // Declare and instantiate connection
        private static  Form1 caller;

        public CGeneral(Form1  caller1)
        {
            caller = caller1;
            string connString = "server=(local)\\SQLEXPRESS;database=tests;Integrated Security=SSPI";

            SqlConnection cn = new SqlConnection(connString);

            cn.InfoMessage += new SqlInfoMessageEventHandler(CnInfoMessage);            
            cn.FireInfoMessageEventOnUserErrors = true;
            SqlCommand cmd = new SqlCommand();

            String sql = "dbo.fillTables";
            cmd.Connection = cn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = sql;
            cmd.Parameters.Add(new SqlParameter("@test", 6));
            try
            {
                cn.Open();                
                SqlDataReader sdr;
                sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); 
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                cn.Close();
            }
        }

        static void CnInfoMessage(object sender, SqlInfoMessageEventArgs ev) 
        {
            foreach (SqlError err in ev.Errors)
            {                
                Console.WriteLine("Message- " + err.Message);
                caller.addMessage(err.Message);
            }
        }       
    }
}

form code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace testDBMessages
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            CGeneral a = new CGeneral(this);
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            Application.DoEvents();            
        }

        public void addMessage(string msg)
        {
            listView1.Items.Add(msg);
            listView1.Refresh();

        }
    }
}

stored procedure

ALTER PROCEDURE [dbo].[fillTables]
(
    @test smallint
)

AS

BEGIN
    declare @counter as int
    SET @counter=1
    while @counter<100
    BEGIN
     Insert into tests.dbo.tToFill (id,description,testNum) 
     Values (@counter,'test_1',@test)
RAISERROR ('RECORD NUM %d',10,1,@counter)
     SET @counter=@counter+1
    END
END
GO

Answer 1

Your RAISERROR with severity of 10 is classed as a warning so does not flow to client code.

Use 16, which is defined as "Indicates general errors that can be corrected by the user" (Edit) I'm sure that used to be different...

RAISERROR ('RECORD NUM %d',16,1,@counter)

Answer 2

Does the user that you connect to your database as using the Integrated security have EXECUTE permission on the stored procedure (dbo.filltables) as that indicates only dbo (database owner) has full permissions on the procedure.

You will need to grant permissions for anyone that wants to use it. Be careful of granting EVERYONE the right if security is a concern.