SQL Server Performance

Select gone bad

Discussion in 'SQL Server 2005 General DBA Questions' started by bugster, Jan 20, 2009.

  1. bugster New Member

    I am switching from server 2000 to 2005 and was going pretty smooth... the databases are fine but new functions from 3rd party may be interupting my fun. Here is the select statement which I am having issues with:SELECT
    dbo.fnu_clrLocalTime (dbo.tblActualValueFloat.UTCDateTime, 53) AS Date, CASE WHEN tblPoint.PointID IN (92, 93, 94)
    THEN tblActualValueFloat.ActualValue END AS Humidity, CASE WHEN tblPoint.PointID IN (95, 96, 97)
    THEN tblActualValueFloat.ActualValue END AS Temps, dbo.tblPoint.RoomFROM
    dbo.tblPoint INNER JOINdbo
    .tblPointSlice ON dbo.tblPoint.PointID = dbo.tblPointSlice.PointID INNER JOINdbo
    .tblActualValueFloat ON dbo.tblPointSlice.PointSliceID = dbo.tblActualValueFloat.PointSliceIDWHERE (dbo.tblActualValueFloat.UTCDateTime >= dbo.fnu_clrLocalTime(GETDATE() - 1.25, 53)) AND
    (dbo.tblActualValueFloat.UTCDateTime < dbo.fnu_clrLocalTime(GETDATE() - .25, 53)) AND (dbo.tblPoint.PointID IN (92, 93, 94, 95, 96, 97)) AND (dbo.tblPointSlice.IsRawData = 1)I was using a function called "dbo.UTCToLocalDateTime"; however, the new schema had named a new one called "fnu_clrLocalTime" I will continue looking for my mistake..lol but would like any assistance if possible.
    I forgot the main issue.. I am receiving the following error: Incorrect syntax near the keyword 'SELECT'.
  2. Adriaan New Member

    Try to isolate the part where the error might be ...
    Skip all the columns and expressions, just put a 1, to see if the error disappears.
    SELECT 1
    FROM dbo.tblPoint INNER JOIN .....................
    If the error is now gone, add the individual expressions one by one, to see when the error comes up again.
    Is this SELECT the first item in your script? If there are other items before this item, then the error might be before the SELECT.
  3. bugster New Member

    Adriaan, I will try that....
  4. bugster New Member

    I inserted one at a time and the statement worked. All I could think is maybe a space or comma was out of place. One of these days I will have better knowledge to help someone instead of always asking...
  5. Adriaan New Member

    With today's LCD monitors, I find that commas often look like full-stops, at least in some of the available fonts. Font size and screen resolution will have some effect too.
    So make sure that, in your editor of choice, you are using a font that displays a comma clearly as a comma.
  6. moh_hassan20 New Member

    set compatability level 80 , and be sure code is working without errors
    change to compatability 90 , if errors appear , modify code or continue compatability 80
    sure it is best database to be 90 level comp.

Share This Page