stored procedure

cannot find stored procedure SQL Server

I was creating a stored procedure on my live server and was constantly getting the error message “Cannot find stored procedure” no matter how many times i created the same stored procedure on the server. Worse if I would create the same procedure again, I would get error, the object already exists.

Found out that I was not using dbo.StoredProcedure with the stored procedure name, that is schema was missing. So remember on live server, using dbo (schema) can make a big difference esp if your site is being hosted on a shared host like mine.

Lesson learned, dbo is important with the stored procedure name!

executenonquery returns -1 on success

ExecuteNonQuery is suppose to return the number of rows inserted, updated or deleted but in my case it always return -1 even though the record is successfully added.

Found out

SET NOCOUNT OFF

was missing my in my stored procedure. If you have multiple SQL statements in your stored procedure, initially set it to on by

SET NOCOUNT ON

which will not count any rows affected. Later to monitor the statement you are actually interested in, set it to OFF.

Hope that resolves your problem!