SQL Server and .NET: insertion fails (silently!) In code, but not when starting manually

Question

My built-in 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() 

... works great when called manually:

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

... but when called from code:

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

... insertion does not occur. Now the identifier column gets the increment, and _id gets the value. But the row itself never appears in the table.

The best guess I could come up with was the insert trigger, which deletes the newly inserted row, but there are no triggers in the table (and why will this work when it's done manually?). My other attempts were to suggest that the stored procedure somehow rolls back the insert and therefore puts begin and end and go and semicolons in the stored proc to correctly separate the "insert" and "identifier" bits. This did not fix anything.

Any ideas?

Update:

Thanks to everyone who has helped so far. In the Preet sentence (first answer), I learned how to use SQL Server Profiler (I can’t believe that I never knew about this before), I thought it was only useful for tuning performance, doesn’t understand what I could see for sure what query to the database with it).

It was shown that the SQL sent by the SqlCommand.ExecuteScalar () method was slightly different from what I ran manually. He sent:

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

I started it manually and voila! Actual SQL Server Error (!):

Error converting varchar data type to datetime.

Since I tested it manually, I simply reduced the datetime time from '2009-09-03 16: 20: 11.7130000' to '2009-09-03 16:20:11' , and this fixed the error; now the row is inserted thin.

But the question is: why cannot Microsoft SQL Server process more than 23 characters in this datetime parameter? It was the Microsoft SqlCommand.ExecuteScalar () method that built such a query, not me. This is a problem because my code is still not working.

As soon as I earned it manually, I looked at how to configure SqlParameter for a date in the code so that it sends a value such as the one that worked. I tried changing the data type from SqlDbType.DateTime to SqlDbType.SmallDateTime . The profiler showed that this did lead to a shorter datetime value of '2009-09-03 17:15:00', but the insert still failed (original problem). But when I copied sql from the profiler and tried it manually - it worked. There is no mistake. Sending it as varchar is the same thing - I like the SSMS request window, the same request through .net fails.

: (

Any other thoughts? Some settings like "environment" on the SQL server, for example, "set ansi_nulls off" or something else that may be different between manual and code connections?

(Another question: why does the sql server not give me an error message and throw an exception every time this error occurs?)

.NET code

In the van request, here is the corresponding .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 the following line:

 param = new SqlParameter(paramName, DbType); 

And it runs as follows:

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

Note: in the above options, this is @download , which is the problem. _downloadDate is a null DateTime value. note that the sql type on this line was used as SqlDbType.DateTime, but I changed it to SqlDbType.SmallDateTime to create a call that works manually (it still doesn't work when run from code).

Update: Allowed Thanks to Matt, I finally found and fixed the problem: the custom helper class lacked cmd.Transaction.Commit () in one of its functions! It had a bunch of different messy things, and I skipped this when I first looked at the code.

So thanks to Matt and Prerat and everyone who contributed.

Lessons learned
- SQL Profiler is really useful. Take the time to learn how to use it.
- If the database swallows errors or seems to be doing something in accordance with your code, but does not actually appear in the database, check the transaction. - Do not trust the DB helper class that someone wrote. Even one that at first seems simple enough. Make sure that you understand exactly what he is doing, as this may be a mistake or just not doing the way you think they are always executed.

Answer 1

The identity column will increase with every attempt to insert, even if it fails, so the part is not necessarily unusual.

I will look for problems with your parameter values ​​and / or types in arrParams. What is the "data" object? (I'm almost afraid to ask, but I don't get any hits on msdn for ExecuteIntScalar)

EDIT:

I think the car is on the right track with regard to the perfect transaction. It looks like you are using some kind of custom helper class to manage calls to stored procedures in the database (and possibly with different access to the database), and it may happen that this code swallows the error caused by the SQL server. I created a small test application and was able to reproduce the behavior you described. Since we cannot determine how your exceptions are for class traps, etc., This may not be your actual problem, but this is one of the ways that calling a stored procedure may 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" (ie just ignore) any errors raised by the DB code. //throw; } } } 

Transactions do not have to be explicitly declared. For example, if you delete the BeginTransaction () and Transaction.Commit () calls from the above code, there will still be an implicit transaction that could theoretically be aborted and caused by a rollback. Thus, the root of your problem may be more subtle than this example, which requires an explicit transaction to demonstrate the concept.

Moreover, you can see (via Profiler) the actual SQL that the application sends to the SQL server, and make sure that it works when executed from SSMS, so it seems to me that the problem is related to the application code that the stored procedure calls.

Answer 2

Either your transaction is not completed, or an exception is thrown, but just is (is ignored).

Answer 3

Put a sql server proxy to see what happens in db.

Answer 4

You may be completing a transaction. This explains why there is no line. I do not think proj_ins_all is not working if you can notice that the _id variable is set. If an exception were thrown, this would not have happened. So it should be so that the transaction is valid and rollback.

Answer 5

If you have a DateTime value in C #, it will be passed as DATETIME to SQL Server as is.

If you have a string value in C # that you want to pass as a DATETIME parameter, it will go through some kind of voodoo localization conversion depending on the language / culture settings of your OS and your database.

I suggest you make sure that your code does indeed pass a DateTime value (which you can check for correctness before calling SP).

Answer 6

Blockquote

Error converting varchar data type to datetime.

Since I tested it manually, I simply reduced the date and time from "2009-09-03 16: 20: 11.7130000" to "2009-09-03 16:20:11", and this fixed the error; now the row is inserted thin.

Blockquote This is a bug in the SQL profiler. http://support.microsoft.com/kb/974289/