I am working on a SP. It is quite long (2800) rows. To work on it and trouble
shoot issues of not returning the correct data and what not, I create an alter
statement to the editor and then I comment out the top of an alter statement. I
then find and replace the 4 variables (@StartDate, @EndDate, @Period, and @Year)
with the values that I want and then I run the SP.
In Microsoft Management Studio, I will get 8003 rows. I run it in Toad 5.0.0.444
and I only get 6849 rows. I run it in Toad 4.6 and I get 8003 rows. Now, while
working on this SP in Toad 5.0 today, I probably had and error pop up like 5 or
so times while typing in code where I was typing some database object name and
then typed the dot “.” and I would get the error to pop up. I sent
one to you earlier this week or last week. Because it happened while typing the
dot, I am guessing this is a Code Complete error. When this error happened, Toad
did not crash, but kept running. I would just click OK for the error and keep
working. I had the same session of Toad open all day and using the same editor
window.
So I closed Toad 5 and opened again and run the code for the SP and now it
returns a correct row count. So something is amiss after I get this code
complete error and thus cannot trust Toad 5.0 after this crash. I am willing to
help anyway I can to stop this error, just let me know what I can do. I know it
was discussed that since it is a intermittent error that it is going to be tough
to chase down but I did not see these errors on 4.6. Tomorrow I will be working
on this SP again most of the day and I will use 4.6 to see if I can get the same
error to happen but I can guarantee that if I work in 5.0 for very long that
this error will show it ugly face. So if I make it thru tomorrow in 4.6 without
this error happening, then that might give you some place to start your bug
search.
Please let me know if I can be of any help to you in your quest (pun intended)
to solve this code complete bug.
Matt, this may be a Toad issue or not but as a fellow developer, I HIGHLY advise
against writing an SP with that many lines of code. There’s got to be some way
to break it down into smaller bits.
Can you post an error message or the exception stack of the error you were
getting while typing “.”? Hank also reported the similar problem, we
just want to know if it’s the same problem.
Well, it is to pull accounting data. So I have to go to three systems, our
accounting application, and two TMS applications. Each one of those I have to
grab both AP and AR data. And the business rules are incredible! There must be
40 of them. That is what happens when there is not a set standard for paying the
customers and shippers. The accounting staff wants to run this at will. Yes I
could easily take this and run each part and populate separate tables but the
problem is I could have more than one accounting staff running this at one time
and if the data was populating tables with temp data, they could trip over each
other. Or I would have rather just populated a cube or reporting database each
night but the accounting staff want to run this…look for issues. Run
invoices…run again all in the same day. Having the data current as last night
was no good for them….
Don’t worry, even though it is 2800 lines, a lot of it are comments,
spaces between the AP and AR sections to make things readable , a lot of line
wrapping to keep the code to
sounds. For all it does, it takes less than 60 seconds to run to pull a whole
lotta data together.
One time where I was reading some sql web page and guy said that the sql code
they write, if it does not run in 1 second or less, it cannot be put on the
production server. I laughed…that would eliminate about 99% of our queries
and SP’s. Does not matter…my SQL server is sitting at 5 to 10%
processer load most of the time.
Well, it is to pull accounting data. So I have to go to three systems, our
accounting application, and two TMS applications. Each one of those I have to
grab both AP and AR data. And the business rules are incredible! There must be
40 of them. That is what happens when there is not a set standard for paying the
customers and shippers. The accounting staff wants to run this at will. Yes I
could easily take this and run each part and populate separate tables but the
problem is I could have more than one accounting staff running this at one time
and if the data was populating tables with temp data, they could trip over each
other. Or I would have rather just populated a cube or reporting database each
night but the accounting staff want to run this…look for issues. Run
invoices…run again all in the same day. Having the data current as last night
was no good for them….
Don’t worry, even though it is 2800 lines, a lot of it are comments,
spaces between the AP and AR sections to make things readable , a lot of line
wrapping to keep the code to
sounds. For all it does, it takes less than 60 seconds to run to pull a whole
lotta data together.
One time where I was reading some sql web page and guy said that the sql code
they write, if it does not run in 1 second or less, it cannot be put on the
production server. I laughed…that would eliminate about 99% of our queries
and SP’s. Does not matter…my SQL server is sitting at 5 to 10%
processer load most of the time.
Matt, what is the possibility of having a reporting server, where the production
database on server (A) is replicated by Log Shipping to sever(b) and the later
is in READ-ONLY mode. SOOO reports and adhoc® could have all the Run-Fun they
want by writing script and code against the R0 version … The Quest Logshipping
systems, I have set up in the past, this worked great and was also another
failsafe copy of the database that was never more than 15 mins behind because we
wanted it that way.
Just a thought, for which you know I’d help if needed.
Matt, what is the possibility of having a reporting server, where the production
database on server (A) is replicated by Log Shipping to sever(b) and the later
is in READ-ONLY mode. SOOO reports and adhoc® could have all the Run-Fun they
want by writing script and code against the R0 version … The Quest Logshipping
systems, I have set up in the past, this worked great and was also another
failsafe copy of the database that was never more than 15 mins behind because we
wanted it that way.
Just a thought, for which you know I’d help if needed.
Matt, what is the possibility of having a reporting server, where the production
database on server (A) is replicated by Log Shipping to sever(b) and the later
is in READ-ONLY mode. SOOO reports and adhoc® could have all the Run-Fun they
want by writing script and code against the R0 version … The Quest Logshipping
systems, I have set up in the past, this worked great and was also another
failsafe copy of the database that was never more than 15 mins behind because we
wanted it that way.
Just a thought, for which you know I’d help if needed.
Yea, I thought about that as well. I would have to make another instance of SQL
on that server as I only have one “production” database. This query
was wrote by a different developer and when I have time, I would like to see if
I could make it work. When she made it, it took like 15 minutes plus to run. Not
acceptable. I worked on it and got it to less than 60 seconds with some
tweaking. Now I would like to streamline it further but I think a re-think and a
re-write from the ground up is in order.
If I get in a jam, you will be the first person I will call…… J
Yea, I thought about that as well. I would have to make another instance of SQL
on that server as I only have one “production” database. This query
was wrote by a different developer and when I have time, I would like to see if
I could make it work. When she made it, it took like 15 minutes plus to run. Not
acceptable. I worked on it and got it to less than 60 seconds with some
tweaking. Now I would like to streamline it further but I think a re-think and a
re-write from the ground up is in order.
If I get in a jam, you will be the first person I will call…… J
OK, now I am not sure what to do…I came in today, opened up a Toad 4.6
session. Opened my query and ran it and only had like 4600 rows returned. Then I
ran it in MSSMS and I got 8014 rows. I closed and opened Toad 4.6 and returned
the correct rows……Now I don’t think I can trust Toad.
OK, now I am not sure what to do…I came in today, opened up a Toad 4.6
session. Opened my query and ran it and only had like 4600 rows returned. Then I
ran it in MSSMS and I got 8014 rows. I closed and opened Toad 4.6 and returned
the correct rows……Now I don’t think I can trust Toad.
Also, am I missing something on how the “like” filter is supposed to
work in the grid?
You can see that I have two rows in the selection list that correspond to the
filter I have selected which is two distinct rows in the result set (there are
actually 4 rows that contain my value). Since this is a “Like” and
not a “Begins With” or “Ends With”, I am assuming that
if my selection is contained anywhere within the cell, it should display?
Please correct me if I am interpreting this wrong.
