CODEWITHSUNDEEP
sql-server-2008-r2
Jonny Cundall
Jonny Cundall

Reputation: 2612

'OBJECT_SCHEMA_NAME' is not a recognized built-in function name

I'm trying to run a script to set up tsqlt on SQL server 2008 r2 and getting this error

'OBJECT_SCHEMA_NAME' is not a recognized built-in function name.

on another machine with SQL 2008 r2, the script runs fine.

I can't see any difference in the users or permissions in the sql server setup for each respective machine.

this is the statement that throws the error:

    CREATE FUNCTION tSQLt.Private_GetOriginalTableInfo(@TableObjectId INT)
RETURNS TABLE
AS
  RETURN SELECT CAST(value AS NVARCHAR(4000)) OrgTableName,
    OBJECT_ID(QUOTENAME(OBJECT_SCHEMA_NAME(@TableObjectId)) 
    + '.' + QUOTENAME(CAST(value AS NVARCHAR(4000)))) OrgTableObjectId
    FROM sys.extended_properties
   WHERE class_desc = 'OBJECT_OR_COLUMN'
     AND major_id = @TableObjectId
     AND minor_id = 0
     AND name = 'tSQLt.FakeTable_OrgTableName';
GO

Upvotes: 2

Views: 3894

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280260

If this is in fact on SQL Server 2008 R2, your database is in SQL Server 2000 compatibility mode. You can tell what mode you're in now:

SELECT name, compatibility_level
  FROM sys.databases
  WHERE name = N'your_database';

If this is 80 you will need to move up (but this will require testing):

ALTER DATABASE your_database SET COMPATIBILITY_LEVEL = 100;

If you can't move up, then you can always get the schema name by joining to sys.schemas instead of relying on the built-in function.

That said, if you are on SQL Server 2005 RTM (as implied in a comment), OBJECT_SCHEMA_NAME was introduced in SQL Server 2005 Service Pack 2. You should not be running any system on SQL Server 2005 RTM anymore. Go install SP4 and you will be able to use this function.

Upvotes: 2

Related Questions