execute "big" stored procedure - insert data in SQL Database

[Eli]

Hi all

We currently have a strange problem with calling a Stored Procedure
(SQL Database) in our C# Project. The only error I get is "System
error" which says a lot

Background:
We have several stored procedures to Insert and update datas in our
SQL database. Some stored procedures are smaller (insert datas in only
one table) and some of them are quite big (insert datas in several
tables with more than 7 parameters to execute). All of the stored
procedures run fine when we execute them in the query analyzer. But
some of them won't work in our C# GUI, which is very important to us!

So the actual execute of a stored procedure is not the problem -> When
we run a smaller stored procedure in our Project, which only inserts
data in one table and only has got 1 parameter, then it's no problem.
But once we want to run the big stored procedures with lots of
paramemters we get the "System error".
We already checked the datatypes of the parameters (sqldbtypes) in the
C# project -> They're fine. We checked the procedures manually ->
running fine.

What is the problem here? Has anybody got any experience on calling
procedures that update more than one table? Is there a problem we
can't see or we forgot about?

We're glad about any ideas!!

Thanks
Eli

PS: We call the procedures via a SQLCommand-Object where we say it's a
Stored procedure. The call itself should be fine.

PSPS: This project is our diploma PLEASE HEEEEEEEEELPP WE WANNA PASS

 

[Miha Markic [MVP C#]]

Perhaps you are experiencing timeouts?
How long do they take to execute?
Try increasing SqlCommand.CommandTimeOut parameter.
Also, you might show use the sp declarations (only the header) and the code
you use to invoke them.

 

[Cletus Van Damme]

I don't think the problem you're experiencing is related to the number of
parameters or tables you're stored procedure uses. As a reference, I
recently wrote some code that called a stored procedure that has 40+
parameters, inserts/updates a number of tables and returns an identity value
as an output parameter. This worked without any probems. From your post,
it looks like you're saying that you are setting the CommandType property on
the SqlCommand object but I could be reading that incorrectly. If you're
not doing that, make sure you specify the CommandType property as
CommnadType.StoredProcedure. If you don't do this and you add parameters to
the SqlCommand's Parameters collection, you'll get an error.

Cletus