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