Hanging stored procedures
-
As I said, this MIGHT be it, but I would expect to have this problem reoccuring once in a while, not just at the first execute after the proc is modified.
I would try this option, but I'd keep checking around here for other solution from other users because I'm really not sure that this is it.
-
Well, I didn't get rid of the temporary tables as much as I collapsed the script and the three stored procedures into one script - the DTS package worked.
Problem is, before I pretty the thing up and add comments and clear breaks the thing is up to 700 lines. Not to mention it's a bunch of code that if I ever need to update I'll have to do in two places (which I strongly suspect as it's really in it's first cycle and there are probably business cases looming on the horizon).
Any ideas how to make this work with the stored procedures back in place.
Thanks to Remi for all the advice ... and to anyone who's made it this far in the thread.
Everett Wilson
ewilson10@yahoo.com -
Do you remember which proc failed??
This could be a very simple fix but I just didn't think about it :
try adding another return somewhere in the proc.
(Long story short, the SysComments table can take up to 4000 characters, but on occasion a cariage return (or another character... not too sure) gets split on two differents rows in the table. But when you merge the whole thing back up it fails because of that (sorry if I can't provide more precise details).
-
Have you been able to solve your problem?
-
Hello. It appears to be dying on both of the sub-procedures (working on undertanding it right now).
I do have a question, though. I can't see any way to add a Return without breaking the sub-procedures.
Everett Wilson
ewilson10@yahoo.com -
Sorry for the confusion, I meant cariage return as in
in html. As I was trying to explain, it's just a matter of a characther being lost between 2 rows in the syscomments table when the proc is saved on the server... sorry if I can't explain it in more details. -
Well, what I did didn't work. In the smaller of the two procedures (which has 4,886 characters) I added a few returns. The problem still persists.
Could I test the length issue by embedding a procedure to move some of the code out? One of the insert statements is about 3000 characters long.
Everett Wilson
ewilson10@yahoo.com -
Maybe that could do it. Try making a new proc for the insert statement only. If that doesn't solve your problem, I'm officially out of ideas... or not
. -
Nope, thanks again though.
I have to go off for a bit so some quick notes. The idea died on:
Server: Msg 8164, Level 16, State 1, Procedure sprSnapshotPriDepartment, Line 10
An INSERT EXEC statement cannot be nested.
I tried it as both a temporary table and an actual table. Seems like I've read about heavy nesting of stored procedures so I'm not sure what to make of it.
I'll research later.
Thanks again.
Everett Wilson
ewilson10@yahoo.com -
Do you have something like this??
proc 1
insert into... exec proc
proc 2
do some stuff
call proc 3
proc 3
Select that inserts into proc 1
I've never heard of that problem... but that could be something that may cause it... but it's actually more of a guess than anything else.
I'll try recreating the problem on my machine to see if it's the same error.
-
I just found the reference I was searching about the rogue crlf (Aaron Templeton's answer) :
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=139302
-
No luck. Once again with the caveat that I'm understanding correctly, I did overkill and removed all comments from the current version that I'm playing with, and then recreated it(delete old and run create).
Note that without the comments the size of the procedure fell to 3,658 characters. Also the procedure worked if I ran it once and then performed the insert action.
Question: is there a default hex editor or an easy way to view a document in ascii?
From the first question
Originally
DTS(query analyzer) script calls procedure 1
procedure 1 calls procedure 2
procedure 1 calls procedure 3
problem came up when moved part of 3 into 4
using only insert statement from procedure 1
insert statement calls procedure 3
procedure 3 inserts (calls) information from procedure 4
Everett Wilson
ewilson10@yahoo.com -
Well I'm out of ideas.. so I'll have to wish you good luck with this problem...
-
No problem. Again, thanks for the input.
Everett Wilson
ewilson10@yahoo.com -
Almost HTH
.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply