4

I'm running a query that I got today from a previous question, and I just noticed an error that only happens when I run this query ON the server (RDC + MS Studio -- not express) vs on my machine which has MS Studio Express.

Here is the Error:

Msg 102, Level 15, State 1, Line 22
Incorrect syntax near '('.

It refers to this line in my script (full script here on DBA):

FROM sys.dm_db_index_physical_stats
    (
        db_id(@DB),  <---
        OBJECT_ID(@Table), 

The odd thing is, that IF I run this off my local machine, using MS Studio Express AGAINST that same SQL server (dev box), it runs just fine and I get results.

HOWEVER if I remote into the SQL dev box, and paste the VERY SAME sql into the MS Studio (non-express) it throws that error msg 102... above.

I'm scratching my head why that is, my only thought is that the Studio Express is not validating it the same way as the non-studio client?

Here is the version info for the client on the SQL server:

Microsoft SQL Server Management Studio  9.00.4035.00
Microsoft Analysis Services Client Tools    2005.090.4035.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML             2.6 3.0 4.0 6.0 
Microsoft Internet Explorer         8.0.6001.18702
Microsoft .NET Framework            2.0.50727.3625
Operating System                5.2.3790

and my client:

Microsoft SQL Server Management Studio Express9.00.4035.00
Microsoft Data Access Components (MDAC) 6.1.7600.16385 (win7_rtm.090713-1255)
Microsoft MSXML             3.0 4.0 5.0 6.0 
Microsoft Internet Explorer         8.0.7600.16385
Microsoft .NET Framework            2.0.50727.4963
Operating System                6.1.7600

Any ideas?

EDIT 1:

Per @gbn's suggestion I ran the version check on both (SELECT @@VERSION, @@SERVERNAME):

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)   
Nov 24 2008 13:01:59   
Copyright (c) 1988-2005 Microsoft Corporation Developer Edition 
on Windows NT 5.2 (Build 3790: Service Pack 2)  

(server name is same -- not posted)

EDIT 2:

Per @DaniSQL's suggestion I ran SELECT compatibility_level FROM sys.databases WHERE name = 'DATABASE_NAME' and the result was 80 (from both SMSS Express and while RDC'd to the box and connected with non Express SSMS)

2

The remote database has compatibility level 80, which doesn't work with db_id(). More info on a related StackOverflow question.

(Just dumping this into an answer from the question comments so it disappears from the Unanswered list of questions.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.