SQL Server Performance Forum – Threads Archive

SP needs help

I appologize for bothering you all again but being this new you can imagine how fustrated i am. I have created a procedure and trying to have it print on a scheduled job. do I tello this procedure to print or do I have the job print the result of the sp. I have tried looking on books online for the proper syntax with no success. anybody with pity would help this poor soul. LOL. [:I] CREATE PROCEDURE dbo.solidsmorningreport AS SELECT TOP 100 PERCENT dbo.tblActualValueFloat.LocalDateTime, dbo.RawAnalog.PointName, dbo.RawAnalog.PointID, dbo.RawAnalog.ActualValue FROM dbo.RawAnalog CROSS JOIN dbo.tblActualValueFloat WHERE (dbo.tblActualValueFloat.LocalDateTime > GETDATE() – 1) AND (dbo.RawAnalog.PointID BETWEEN 706 AND 70<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> OR (dbo.RawAnalog.PointID BETWEEN 713 AND 716) OR (dbo.RawAnalog.PointID BETWEEN 732 AND 736) OR (dbo.RawAnalog.PointID BETWEEN 740 AND 743) OR (dbo.RawAnalog.PointID = 745) AND (dbo.RawAnalog.PointID = 746) ORDER BY dbo.RawAnalog.PointName DESC GO Do you think this task can be done scheduling a Job and print? Or am I going the wrong path anyway. I am also puzzed what the "Top 100 Percent" indicates. Thanks the Bugster

Dont know what happened. I replied and it disappeared. Here goes again. Top 100 percent will get you all records. It makes no sense to use Top 100 perecent. Top 100 without the precent will return the first 100 rows. Top xx percent will return the xx percent rows, so only values less than 100 will have an effect on results. Using it when you dont need it,may slow your query down. Not totally sure what you want, but you have some missing brackets in your where statment. Try this WHERE (dbo.tblActualValueFloat.LocalDateTime > GETDATE() – 1) AND ((dbo.RawAnalog.PointID BETWEEN 706 AND 70<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> OR (dbo.RawAnalog.PointID BETWEEN 713 AND 716) OR (dbo.RawAnalog.PointID BETWEEN 732 AND 736) OR (dbo.RawAnalog.PointID BETWEEN 740 AND 743) OR ((dbo.RawAnalog.PointID = 745) AND (dbo.RawAnalog.PointID = 746))) This will always ensure the localdatetime is greater than the current datetime and that the pointid is within one of the ranges specified. Hope that helps (and hope this doesnt dissappear) Chris

I appologize for the delay. The query is to retrieve a trend of temperature, Humidity, and pressure for different zones (pointID) for the past 24hours or 1 day. The trend points are inserted every 15min. Also I was looking at some of the other forum items and noted a question on whether to use a view or sp. Do you feel it would be better with a view?
When I need to have it print do i use a scheduled job or print in Sp? Great thanks to all the
Bugster

You’re not passing any data or parameters, so the code is fixed. So, six of one, half a dozen of the other. A view would be a better option if you want to ‘do’ anything with the data after you bring it back. That way you could join it to another table or whatever. But if you think you would ever be passing parameters (like a date range) then an sp would probably work for that. If you’re going to run it at regular intervals, then put in a job – it would run even if you werent there to start it. If its more of an adhoc thing, then you can just run it when you need it, and you wont be putting a load on the server by retrieving data when no one will use it.

lol…..give me some time on this one. I will have to check BOL for adhoc. The customer requested a report printed with this information at 6:30 in each morning. that would be the only time the query would run.
back to BOL Thanks the
Bugster

I did create a view and it works alot better. Actually I had to make three views, Temp, Humidity, and Pressures. Then I created a view using the last three. Now I am back too work on the subject "Group objects in one datetime". Again, Thanks to all whom have been patient with thee… the
Bugster

]]>