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.