問題描述
我在 T-SQL 腳本中使用 SCHEMA_NAME() 函數.
它為我返回 NULL，但為我的同事返回默認架構.
SCHEMA_NAME() 的文檔僅將 NULL 列為可能的值，同時還為 SCHEMA_NAME() 函數提供了一個參數.
以下是 SCHEMA_NAME() 的文檔:https://docs.microsoft.com/en-us/sql/t-sql/functions/schema-name-transact-sql
在調試這個問題時我應該尋找什么?
DBA 已檢查我的同事和我自己的權限是否相同.
我在不帶任何參數的情況下調用該函數，因此文檔中聲明的在 schema_id 無效時返回 NULL 的情況不適用 - 至少沒有明確說明.
DBA 確認我有一個默認架構，我使用以下選擇來確認我有一個默認架構:
select * from sys.database_principles dp where dp.name = USER_NAME();DBA 將我的默認架構更改為其他內容，然后將其更改回來以防后臺設置了某些內容.
這發生在 SQL Studio 中以及當我使用 sqlcmd 在命令行上運行時.
我注意到當我運行 CREATE PROCEDURE myproc 過程是在我的默認架構中創建的，但是當我運行 execute myproc 我需要做 executemyschema.myproc.即:我必須指定架構.
我已通過在調用存儲過程中硬編碼架構名稱來驗證默認架構是否存在.
當您在與 schema_id 不同的數據庫上執行查詢時，也會發生這種情況
例如
select sn = schema_name(schema_id) from [yourdbname].sys.all_objects并且當前數據庫不是 [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模板網！