not a recognized table hints option

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

not a recognized table hints option

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2009-09-22 : 12:12:50

Hi, everyone,

I run into this problem and the full msg is:
Msg 321, Level 15, State 1, Line 2
"#max_level" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

I tried to populate the #tmp like this:
select max([path]) as max_path
INTO #max_level
from fnAssetHierarchyTreeExcludeChildren
(
'myguidvalue',
'',
default,
default ,
default
)
where [path] like '%4100%'

Then tried to feed that string in #max_level as follow:
select value
from dbo.fn_Split( #max_level
, '\'
)
where position = 2

Did I miss something by approaching this way?

Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-22 : 13:12:50

You can't pass a table as an argument to a function. Assuming that your db compatibility level is set to 90 AND that fn_Split is a table valued function that returns columns ([value], [position]) then try this:


select ca.[value]
from   #max_level ml
cross apply (
       select [value] 
       from dbo.fn_Split(ml.max_path)
       where  position = 2
       ) as ca

EDIT:
I guess this should be the same thing:


select ca.[value]
from   #max_level ml
cross apply dbo.fn_Split(ml.max_path) as ca
where ca.postion = 2

Be One with the Optimizer
TG

Hommer
Aged Yak Warrior

808 Posts

Posted - 2009-09-22 : 13:53:56

Thanks TG!

However, my db compatibility level is set to 80. Is there a way to get by?

Running your script, I got incorrect syntax near .

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-22 : 13:57:46

Is there a problem setting it to 90? If not:

exec sp_dbcmptlevel @dbname = '<your DB Name>', @new_cmptlevel = 90

Be One with the Optimizer
TG

Hommer
Aged Yak Warrior

808 Posts

Posted - 2009-09-22 : 14:02:59

I haven't done my homework on db compatibility level 80 vs. 90. What are some of the pros and cons? Can you just make the change on the fly back and forth without impacting other operations?

If this is getting to a bigger issue, then I will find other way (=writing a new function to combine the two) to get the data.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-22 : 15:15:47

It's usually not a problem to go from 80 to 90 and the SP executes almost instantly. You can download an "upgrade advisor" which will check for any potential problems. I personally don't like leaving code in place that can't be upgraded simply because that traps you in old technology.

Of course any change to the server or objects should be run and tested first in a non-production environment.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources