June 30, 2012 at 2:18 pm
There is another way to fix this as well.
This CTE can be modified as such and the group by changed back to the original code.
with SampleData as (
select
cast(MsgText as varchar(8000) as SampleRec
from
dbo.Syslogd
where
MsgDate = dateadd(dd, -1, cast(getdate() as date)) and
MsgText like '%CALL_END%'
),
June 30, 2012 at 2:23 pm
It really is the simple things that trip you up. What can I say except thank you for all the help. As I said earlier, this is the forum to be in if you have questions like this, although I feel bad that you spent so much time working on this. Myself (and my colleagues) can't thank you enough. This affects a lot of people and we have a lot of IP gateways. There are smarter people than I who were stumped by this issue. If there is anything I can do for you, let me know. I owe you. Big time! I'm off to run some more queries and set up the front end for testing.
Rob.
June 30, 2012 at 2:30 pm
Just a few more things.
One, I would change the CTE as I showed in my last post and change the group by back to its original state.
Two, please ask any questions that you or others may have about the code. You need to understand it as you will have to maintain it.
Third, if you look at my signature block, there are several articles linked there that you should read. They will help increase your understanding of some key topics that will benefit you and your team.
Remember, we are here to help you learn and expand your knowledge. There are others here more knowledgeable than me that I still learn from regularly.
July 1, 2012 at 3:54 pm
To all, especially Lynn,
Thanks for helping me solve this. I did a LOT of reading, and made the changes. I also modified the code and created a stored procedure that accepts date strings as variables, so now there is almost no code in my front end, everything is done at the SQL Server. Using CTE's makes a big difference in performance. Less than 30 seconds on an old test server for 16k records! Took a LOT longer than that via RBAR:-) You guys rock. I will try not to bug you again.
Rob.
July 2, 2012 at 12:29 am
rjbirkett (7/1/2012)
To all, especially Lynn,Thanks for helping me solve this. I did a LOT of reading, and made the changes. I also modified the code and created a stored procedure that accepts date strings as variables, so now there is almost no code in my front end, everything is done at the SQL Server. Using CTE's makes a big difference in performance. Less than 30 seconds on an old test server for 16k records! Took a LOT longer than that via RBAR:-) You guys rock. I will try not to bug you again.
Rob.
You aren't bugging us, we are here to help.
Viewing 5 posts - 91 through 94 (of 94 total)
You must be logged in to reply to this topic. Login to reply