SQL Server and .NET: insert fails (silently!) in code but not when run manually

Question

My insert stored procedure:

ALTER procedure proj_ins_all 
(
@proj_number INT, 
@usr_id INT, 
@download DATETIME, 
@status INT 
)
as
INSERT INTO project
(proj_number, usr_id, date_download, status_id)
VALUES
(@proj_number, @usr_id, @download, @status)
select SCOPE_IDENTITY()

... runs fine when called manually like so:

exec proj_ins_all 9001210, 2, '2009-09-03', 2 

... but when called from code:

_id = data.ExecuteIntScalar("proj_ins_all", arrParams);

... the insert doesn't happen. Now, the identity column does get incremented, and _id does get set to it's value. But the row itself never appears in the table.

The best guess I could come up with was an insert trigger which deletes the newly inserted row, but there are no triggers on the table (and why would it work when done manually then?). My other tries were around a guess that the stored procedure is rolling back the insert somehow, and so putting begin and end and go's and semicolons into the stored proc to properly separate the 'insert' and the 'identity select' bits. That fixed nothing.

Any ideas?

Update:

Thanks all who have helped so far. On Preet's suggestion (first answer) I learned how to use SQL Server Profiler (I can't believe I never knew about it before - I thought it was only useful for performance tuning, didn't realise I could see exactly what query was going to the DB with it).

It revealed that the SQL sent by the SqlCommand.ExecuteScalar() method was slightly different from what I was running manually. It was sending:

exec proj_ins_all @proj_number=9001810,@usr_id=2,@download='2009-09-03 16:20:11.7130000',@status=2

I ran that manually and voila! An actual SQL server error(!):

Error converting data type varchar to datetime.

Since I was testing it manually, I simply shortened the datetime from '2009-09-03 16:20:11.7130000' to '2009-09-03 16:20:11' and this fixed the error; now the row inserted fine.

But this begs the question: why can't Microsoft's SQL Server handle more than 23 characters in that datetime parameter? It was Microsoft's SqlCommand.ExecuteScalar() method that constructed the query like that, not me. This is a problem because my code still doesn't work.

As soon as I got it working manually, I looked at how to set up the SqlParameter for the date in the code so it would send a value like the one that worked. I tried changing the data type from SqlDbType.DateTime to SqlDbType.SmallDateTime. The profiler showed that this indeed produced a shorter datetime value '2009-09-03 17:15:00', but the insert still failed silently (the original problem). But when I copy-pasted the sql from profiler and tried it manually - it worked. no error. Same deal sending it as varchar - SSMS query window likes it, same query through .net fails silently.

:(

Any other thoughts? Some 'environment' type setting in SQL server like 'set ansi_nulls off' or whatever that might be different between manual and through-code connections?

(Another question is why doesn't sql server give me an error message and generate an exception each time this error occurs?)

.Net code

On van's request, here is the relevant .NET code (C# ASP.NET):

data.MakeInParam("@proj_number", SqlDbType.Int, _projNo),
data.MakeInParam("@usr_id", SqlDbType.Int, _usr.Id),
data.MakeInParam("@download", SqlDbType.SmallDateTime, _downloadDate),
data.MakeInParam("@status", SqlDbType.Int, (int)_status),

and MakeInParam has this line:

param = new SqlParameter(paramName, DbType);

And it gets executed like this:

SqlCommand cmd = CreateCommand(procName, prams);
object result = cmd.ExecuteScalar();

Note: in the above paramaters, it's @download that's the problem. _downloadDate is a nullable DateTime. note that the sql type in this line used to be SqlDbType.DateTime, but I changed it to SqlDbType.SmallDateTime to produce a call that works manually (It still fails when run from code).

Update: solved Thanks to Matt, I finally found and fixed the problem: the custom helper class was missing a cmd.Transaction.Commit() in one of it's functions! It had a bunch of different messy stuff in it and I missed this when going through the code initially.

So thanks to Matt and Preet and everyone else who contributed.

Lessons learned
- SQL Profiler is really useful. Take the time to learn how to use it.
- If the DB is swallowing errors, or appears to be doing something according to your code but it's not actually appearing in the DB, check the transaction is being committed.
- Don't be so trusting of a DB helper class someone else wrote. Even one that seems simple enough at first. Make sure you understand exactly what it does , as it may be buggy or just not do things the way you think they're always done.

Answer 1

The identity column will be incremented whenever an insert is attempted, even if it fails, so that part is not necessarily unusual.

I'd look for issues with your parameter values and/or types in arrParams. What kind of object is 'data'? (I'm almost afraid to ask, but I'm not getting any hits on msdn for ExecuteIntScalar)

EDIT:

I think van is on the right track with respect to the transaction not being committed. It loos like you're using some sort of custom helper class to manage calls to stored procedures in the database (and other DB access, presumably), and it could be that this code is swallowing the error raised by SQL server. I created a little test app and was able to reproduce the behavior you describe. Since we can't tell how your class traps exceptions etc. this may not be your actual problem, but it's one way that a stored procedure call could fail in the way you describe.

// call the proj_ins_all SP every time a button is clicked.
protected void Button1_Click(object sender, EventArgs e)
{
    using (SqlConnection conn = new SqlConnection(myConnectionString))
    using (SqlCommand cmd = new SqlCommand("proj_ins_all", conn))
    {
        try
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@proj_number", SqlDbType.Int));
            cmd.Parameters["@proj_number"].Value = 9001810;
            cmd.Parameters.Add(new SqlParameter("@usr_id", SqlDbType.Int));
            cmd.Parameters["@usr_id"].Value = 2;
            cmd.Parameters.Add(new SqlParameter("@download", SqlDbType.SmallDateTime));
            cmd.Parameters["@download"].Value = "2009-09-03 16:20:11";
            cmd.Parameters.Add(new SqlParameter("@status", SqlDbType.Int));
            cmd.Parameters["@status"].Value = 2;

            conn.Open();
            cmd.Transaction = conn.BeginTransaction();

            object _id = cmd.ExecuteScalar();

            // _id now contains the value of the Identity column for
            // the row just inserted by proj_ins_all

            // Assume (or simulate) an error is raised after the SP is called but
            // before the transaction is committed.
            // (Comment this line out and the DB update succeeds, as expected.)
            throw new Exception();

            // If the transaction is not committed, it'll be rolled back when
            // the connection is closed and the inserted row won't be in the
            // table even though the incremented Identity value was returned above.
            cmd.Transaction.Commit();

        }
        catch (Exception)
        {
            // "swallow" (i.e. just ignore) any errors raised by the DB code.
            //throw;
        }
    }

}

Transactions don't have to be explicitly declared to occur. For example, if you removed the calls to BeginTransaction() and Transaction.Commit() from the code above, there would still be an implicit transaction that could theoretically be interrupted and caused to roll back. So the root of your issue might be more subtle than this example, which requires an explicit transaction to demonstrate the concept.

More practically, you can see (via Profiler) the actual SQL that the app sends to the SQL server and verify that it works when executed from SSMS, so that makes me think the issue is probably with the app code that calls the stored procedure.

Answer 2

Either your transaction is not committed or the exception is thrown and just gets eaten (ignored).

Answer 3

Put the sql server profiler on to see what is happening in the db.