Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
    
How come you have the same versions for SSMS while you say one is not express and the other one is? Also, can you try using db_id([@dbid]). They may be parsed differently. –  StanleyJohns Mar 7 '12 at 21:05
    
What does SELECT @@VERSION, @@SERVERNAME say. And what is compatibility mode from sys.databases? –  gbn Mar 7 '12 at 21:14
    
@StanleyJohns, no idea why both SSMS versions are the same, I just copy & pasted the HELP -> ABOUT, its un-altered –  Jakub Mar 7 '12 at 21:17
    
Can you post the result of 'SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'? –  DaniSQL Mar 7 '12 at 22:03
2  
@Jakup - level "80" means compatible with sql server 2000. Per the documentation, the db_ID() function is only available for Sql Server 2005 and later (See the "Other versions drop down menu at the top) –  Joel Coehoorn Mar 8 '12 at 15:55
show 7 more comments

1 Answer

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.)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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