Object_ID and db_id functions

I am using sql functions with some DMVs as follows
SELECT * FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL,
NULL)
and getting the following error :
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
The statement succed if I use variables as follows :
Declare @.test int
Declare @.test2 int
set @.test = db_id()
set @.test2 = OBJECT_ID('MyDB.dbo.Member2')
SELECT * FROM sys.dm_db_index_physical_stats (@.test, @.test2, NULL, NULL,
NULL)
What is strange is when I executed the first one against AdventureWorks it
works correctly without the need to use variables. It sends back info for al
l
indexes ?......
So am I missing any config param sonewhere
ThanksThis is probably because the database which give you the error isn't in comp
atibility mode 90.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:E0D7F135-ADDE-4C2E-881F-2E84D0BBDDD8@.microsoft.com...
>I am using sql functions with some DMVs as follows
> SELECT * FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL,
> NULL)
> and getting the following error :
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near '('.
> The statement succed if I use variables as follows :
> Declare @.test int
> Declare @.test2 int
> set @.test = db_id()
> set @.test2 = OBJECT_ID('MyDB.dbo.Member2')
> SELECT * FROM sys.dm_db_index_physical_stats (@.test, @.test2, NULL, NULL,
> NULL)
> What is strange is when I executed the first one against AdventureWorks it
> works correctly without the need to use variables. It sends back info for
all
> indexes ?......
> So am I missing any config param sonewhere
> Thanks|||Exact, I changed compatibilityto 90and it worked,thanks a lot
"Tibor Karaszi" wrote:

> This is probably because the database which give you the error isn't in co
mpatibility mode 90.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:E0D7F135-ADDE-4C2E-881F-2E84D0BBDDD8@.microsoft.com...
>