問題描述

我在 T-SQL 腳本中使用 SCHEMA_NAME() 函數(shù).

它為我返回 NULL，但為我的同事返回默認架構.

SCHEMA_NAME() 的文檔僅將 NULL 列為可能的值，同時還為 SCHEMA_NAME() 函數(shù)提供了一個參數(shù).

以下是 SCHEMA_NAME() 的文檔:https://docs.microsoft.com/en-us/sql/t-sql/functions/schema-name-transact-sql

在調(diào)試這個問題時我應該尋找什么?

DBA 已檢查我的同事和我自己的權限是否相同.

我在不帶任何參數(shù)的情況下調(diào)用該函數(shù)，因此文檔中聲明的在 schema_id 無效時返回 NULL 的情況不適用 - 至少沒有明確說明.

DBA 確認我有一個默認架構，我使用以下選擇來確認我有一個默認架構:

select * from sys.database_principles dp where dp.name = USER_NAME();

DBA 將我的默認架構更改為其他內(nèi)容，然后將其更改回來以防后臺設置了某些內(nèi)容.

這發(fā)生在 SQL Studio 中以及當我使用 sqlcmd 在命令行上運行時.

我注意到當我運行 CREATE PROCEDURE myproc 過程是在我的默認架構中創(chuàng)建的，但是當我運行 execute myproc 我需要做 executemyschema.myproc.即:我必須指定架構.

我已通過在調(diào)用存儲過程中硬編碼架構名稱來驗證默認架構是否存在.

當您在與 schema_id 不同的數(shù)據(jù)庫上執(zhí)行查詢時，也會發(fā)生這種情況

例如

select sn = schema_name(schema_id) from [yourdbname].sys.all_objects

并且當前數(shù)據(jù)庫不是 [yourdbname]

I am using the SCHEMA_NAME() function in a a T-SQL script.

It returns NULL for me, but the default schema for my colleague.

The documentation for SCHEMA_NAME() only lists NULL as a possible value when one also supplies an argument to the SCHEMA_NAME() function.

Here is the documentation for SCHEMA_NAME(): https://docs.microsoft.com/en-us/sql/t-sql/functions/schema-name-transact-sql

What should I look for when debugging this problem?

The DBA has checked that my permissions are same for my colleague and myself.

I am calling the function without any arguments so the documentation's stated case of returning NULL when schema_id is not valid does not apply -- at least not explicitly.

The DBA confirmed I have a default schema and I used the following select to confirm I have a default schema:

select * from sys.database_principles dp where dp.name = USER_NAME();

The DBA changed my default schema to something else, then changed it back in case there is something set in the background.

This happens in SQL Studio and when I run on the command line using sqlcmd.

I have noticed that when I run CREATE PROCEDURE myproc the procedure is created in my default schema, but when I run execute myproc I need to do execute myschema.myproc. That is: I must specify the schema.

I have verified that the default schema exists by hard coding the schema name in the call to the stored procedure.

This can also happen when you are performing query on a different database than the schema_id

e.g.

select sn = schema_name(schema_id) from [yourdbname].sys.all_objects 

and the current database is not [yourdbname]

這篇關于SCHEMA_NAME() 返回 NULL的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！