June 2, 2008 at 9:44 am
Sounds odd right? In most databases if I run the function below, it works fine, and the function works as expected. However if I run it into the dbaf database (where the rest of it lives) then I end up being told:
Msg 102, Level 15, State 1, Procedure Report_HourlyTrace_Yesterday, Line 7
Incorrect syntax near '('.
create function Report_HourlyTrace_Yesterday()
returns table
as
return
(
select *
from dbaf.dbo.Report_HourlyTrace(getdate()-1, getdate())
)
go
------------
Running 2k5Ent, SP2 as a sysadmin.
Any ideas? Some odd db settings perhaps?
June 2, 2008 at 10:12 am
that is strange. Does it compile without the db name reference? Shouldn't matter, but not sure why this is a problem.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 2, 2008 at 10:19 am
with/without db and schema prefixes both on create and call makes no change - still has the incorrect syntax error.
June 2, 2008 at 10:31 am
grasping at straws here...but could you post the definition of the inner function also....
June 2, 2008 at 10:42 am
Sure, thanks for the interest:
create function Report_HourlyTrace( @from datetime, @to datetime)
returns table
as
return
(
select *
from dbaf.dbo.Main_PerformanceTraceResultTableHourly p
where qtime between @from and @to
and sp_name in
(
select
top 10 percent
sp_name
from dbaf.dbo.Main_PerformanceTraceResultTableHourly p2
where p2.qtime = p.qtime
order by cnt desc
union all
select
top 10 percent
sp_name
from dbaf.dbo.Main_PerformanceTraceResultTableHourly p2
where p2.qtime = p.qtime
order by sumdur desc
union all
select
top 10 percent
sp_name
from dbaf.dbo.Main_PerformanceTraceResultTableHourly p2
where p2.qtime = p.qtime
order by sumreads desc
union all
select
top 10 percent
sp_name
from dbaf.dbo.Main_PerformanceTraceResultTableHourly p2
where p2.qtime = p.qtime
order by sumcpu desc
)
)
June 2, 2008 at 10:42 am
Have you verified that the compatibility mode of all the databases in question is the same?
Not particularly with functions, but I've had weird stuff like this pop up in the minor differences between versions.
Kyle
June 2, 2008 at 11:02 am
Kyle Neier (6/2/2008)
Have you verified that the compatibility mode of all the databases in question is the same?Not particularly with functions, but I've had weird stuff like this pop up in the minor differences between versions.
Kyle
Thanks Kyle - that was exactly the issue. Changed it up to 90 and it works fine... hope the other stuff does too 😉
Thanks for all the help.
June 2, 2008 at 11:13 am
Glad that it worked. I'll add function references to the things that compatibility mode can goof with.
Kyle
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply