Stored Procdure complete response

Stored Procdure complete response

Post by littleccgu » Thu, 03 Mar 2011 22:57:59



C# 3.5 VS 2008
SQL 2008
Win XP

Still learning, be gental.

Some one has asked me to create a little exe that will run a series of
stored procedures sequentially.  Each are dependant on the one before
it completing successfully, else show which failed and error and
break.

I asked him why doesn't he do this with SSIS.  He said dev does not
want any jobs set up on the test server that wouldn't be used on a
production server. - fine.

I have tried working through this using async communication.  Most of
what I have comes from here:
http://msdn.microsoft.com/en-us/library/7b6f9k7k(v=VS.90).aspx

However, there is no user interaction required because some of the job
run long.  He just wants to start the exe and walk away.

Maybe I shouldn't be using async.  I'm just not sure. Here is what I
would like:
Running sp1...
sp1 completed successfully
Running sp2...
sp2 completed successfully

With async I end up with
Running sp1...
Running sp2...
sp1 completed successfully
sp2 completed successfully

Any simple ideas or examples you can point me too would be great.

Thanks

 
 
 

Stored Procdure complete response

Post by Jamie Frase » Thu, 03 Mar 2011 23:00:35


If you want to run them in order, don't run them ASYNC!


Quote:> C# 3.5 VS 2008
> SQL 2008
> Win XP

> Still learning, be gental.

> Some one has asked me to create a little exe that will run a series of
> stored procedures sequentially.  Each are dependant on the one before
> it completing successfully, else show which failed and error and
> break.

> I asked him why doesn't he do this with SSIS.  He said dev does not
> want any jobs set up on the test server that wouldn't be used on a
> production server. - fine.

> I have tried working through this using async communication.  Most of
> what I have comes from here:
> http://msdn.microsoft.com/en-us/library/7b6f9k7k(v=VS.90).aspx

> However, there is no user interaction required because some of the job
> run long.  He just wants to start the exe and walk away.

> Maybe I shouldn't be using async.  I'm just not sure. Here is what I
> would like:
> Running sp1...
> sp1 completed successfully
> Running sp2...
> sp2 completed successfully

> With async I end up with
> Running sp1...
> Running sp2...
> sp1 completed successfully
> sp2 completed successfully

> Any simple ideas or examples you can point me too would be great.

> Thanks


 
 
 

Stored Procdure complete response

Post by littleccgu » Thu, 03 Mar 2011 23:59:52


Thanks Jamie.

I guess the other part of the problem is understanding how to caputer
success or failure of the sp.

would it be something like
int i = commande.ExecuteNonQuery();

and what should 'i' be on success or failure?

Thanks

 
 
 

Stored Procdure complete response

Post by Jamie Frase » Fri, 04 Mar 2011 00:18:26


Well - basically i will be the number of rows affected by your SP. What that
means it entirely dependant on what your SP does! How  you determine if they
"pass" or "fail" is up to you really.


Quote:> Thanks Jamie.

> I guess the other part of the problem is understanding how to caputer
> success or failure of the sp.

> would it be something like
> int i = commande.ExecuteNonQuery();

> and what should 'i' be on success or failure?

> Thanks

 
 
 

Stored Procdure complete response

Post by littleccgu » Fri, 04 Mar 2011 00:26:25


In this case, all the jobs are just SELECTS (reports), so I think -1
is success and anything else would be a failure.  Does that sound
right?

so if (i == -1) {...} in a try catch should do it - i think.

 
 
 

Stored Procdure complete response

Post by craz » Fri, 04 Mar 2011 01:20:24


You can Use try.. catch in each sp for check its success or failure...
see below..

Begin Try

Begin Transaction

--INSERT

--UPDATE

Commit

End Try

Begin Catch


ROLLBACK

End Catch


Quote:> In this case, all the jobs are just SELECTS (reports), so I think -1
> is success and anything else would be a failure.  Does that sound
> right?

> so if (i == -1) {...} in a try catch should do it - i think.

--
"People who never make mistakes, never do anything."

dEv

 
 
 

Stored Procdure complete response

Post by Jamie Frase » Fri, 04 Mar 2011 01:25:26


Except that he is performing SELECTs.....

If you are performing SELECTs... how can you determine "success" or
"failure"?


> You can Use try.. catch in each sp for check its success or failure...
> see below..

> Begin Try

> Begin Transaction

> --INSERT

> --UPDATE

> Commit

> End Try

> Begin
> Catch


> ROLLBACK

> End Catch


>> In this case, all the jobs are just SELECTS (reports), so I think -1
>> is success and anything else would be a failure.  Does that sound
>> right?

>> so if (i == -1) {...} in a try catch should do it - i think.

> --
> "People who never make mistakes, never do anything."

> dEv

 
 
 

Stored Procdure complete response

Post by littleccgu » Fri, 04 Mar 2011 02:47:51


They are in Beta and having issues all over the place.  I figured it
would be good to check.  If the SELECT completes, it should return
-1.  Otherwise the system should throw and error.
 
 
 

Stored Procdure complete response

Post by littleccgu » Fri, 04 Mar 2011 03:29:24


Well things just changed.  I guess it's always better to walk over and
look.  He is not running stored procedures - they are Jobs in SQL
2008.

I see a couple of sites with examples, so I'll give those a shot.

Thanks for all the feedback.

 
 
 

1. how to pass parameters in loop to a stored procdure and call stored procedure

Hi all,
how to pass parameters in loop to a stored procdure and call stored procedure.

I have reader returning result as one cloumn containing emailids only
and have stored procedure which accept these values one by one and
display results .....but this is not working it is just taking 1st.
value from reader.

how to pass parameters in loop to a stored procdure and call stored procedure
--
Thanks and Regards,
Ravi

2. How to config the chooser of A DEC XTERM

3. Complete control of WebService response

4. Deleted User Profile by Mistake

5. Saving Stored Proc --- "Operation could not be completed"

6. FS:UK: Compaq PC Companion 4mb Windows CE Handheld computer

7. Using stored procedure to complete search

8. New! Letterpress mailing list

9. Displaying two different Response ContentTypes on web page using Response.Write()

10. open in a new window using response redirect/response.write

11. Response.Write or Response.WriteFile?

12. Response.Write or Response.WriteFile ?

13. Response.Write vs Response.OutputStream.Write