DB_ID() gives syntax error in one database

Question

DB_ID() gives syntax error in one database

2010 46601

Old Hand

Points: 322
More actions
September 23, 2010 at 11:05 am

#224299

I am using the following sql as part of some scripts:
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL)
It works fine on all databases on my server except for 2 databases which are relatively big (4GB+). On those 2 specific databases, it gives the error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.
However, it works fine if I make the following change:
declare @id int
select @id = DB_ID()
SELECT *
FROM sys.dm_db_index_physical_stats(@id, NULL, NULL, NULL , NULL)
Can somebody suggest me what is wrong here?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What's the compatibility level of the two databases that it doesn't work in?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jon.Morisi SSChampion Points: 12846 More actions · Answer 2

you might find this helpful

http://www.sqlservercentral.com/scripts/67088/[/url]

Jon.Morisi SSChampion Points: 12846 More actions · Answer 3

that error of yours does look like what you'd get if the db is in compatibility mode 80 or lower

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

DB_ID() has been a valid function since at least SQL Server 7.0

winash SSChampion Points: 11446 More actions · Answer 5

Gail and Jon are right - checked on my DB and if I set the compat level to 80 I get the same error...it's not an issue with DB_ID() - it is with using DB_ID() directly as a parameter for 80 compat...

ALTER DATABASE [ScratchPad] SET COMPATIBILITY_LEVEL = 100;

GO

-- compat level 100 works fine

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL);

GO

ALTER DATABASE [ScratchPad] SET COMPATIBILITY_LEVEL = 90;

GO

-- compat level 90 works fine

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL);

GO

ALTER DATABASE [ScratchPad] SET COMPATIBILITY_LEVEL = 80;

GO

-- compat level 80 bombs

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL);

GO

-- this works in 80 compat

DECLARE @dbid int

SELECT @dbid = DB_ID()

SELECT * FROM sys.dm_db_index_physical_stats(@dbid,NULL,NULL,NULL,NULL);

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 6

try this....i have had issues on several servers from diff companies 2005 and 2008 think its a bug

here is the work around

run this ---

select DB_ID() --- get the id of the database say its 5 for example then change your script to the following

SELECT *

FROM sys.dm_db_index_physical_stats(5, NULL, NULL, NULL , NULL)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

I see what the error is:

SQL Server 2000 (compatibility mode 80) does not allow functions to be used as input parameters for table valued functions, while they are allowed in SQL 2005 (compatibility mode 90) and above.

2010 46601 Old Hand Points: 322 More actions · Answer 8

2010 46601

Old Hand

Points: 322

September 23, 2010 at 3:12 pm

#1226628

Thank you everybody.