SQL 面临“默认架构不存在”的错误。使用 exec() 在 sp 内部执行运行时查询时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3733635/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Facing error of "The default schema does not exist." when executing runtime query inside sp using exec()
提问by HotTester
i have made a runtime query inside a sp and am exceuting the query within the sp using exec(), but when creating the sp i am getting the error
我在 sp 内进行了运行时查询，并使用 exec() 在 sp 内执行查询，但是在创建 sp 时我收到错误
The default schema does not exist.
The SP is:
SP是：
CREATE PROCEDURE MySP
@tableName varchar(100)
AS
BEGIN
SET NOCOUNT ON;
declare @selectQuery varchar(MAX)
set @selectQuery = 'select * from ' + @tableName
exec(@selectQuery)
end
kindly help
好心帮助
回答by Martin Smith
Use CREATE PROCEDURE dbo.MySP
用 CREATE PROCEDURE dbo.MySP
The user you are logged in as must have a non existent default schema.
您登录的用户必须具有不存在的默认架构。
DEFAULT_SCHEMA can be set to a schema that does not currently exist in the database.
DEFAULT_SCHEMA 可以设置为数据库中当前不存在的模式。
Also you should use quotename(@tableName)and a parameter type of sysnamerather than varchar(100)to avoid SQL injection or just errors from non standard object names.
此外，您应该使用quotename(@tableName)参数类型sysname而不是varchar(100)避免 SQL 注入或只是来自非标准对象名称的错误。
回答by RaSor
You could change your default schema:
您可以更改默认架构：
ALTER USER [YOURDOMAIN\HotTester] WITH DEFAULT_SCHEMA=[dbo]
GO
then avoiding having to include [dbo]. in the CREATE PROCEDURE
然后避免必须包含 [dbo]。在创建过程中
回答by codingbadger
It is probably because the default schema associated with the User creating the SP no longer exists or the user no longer has access to the schema.
这可能是因为与创建 SP 的用户关联的默认架构不再存在，或者用户不再有权访问该架构。
Although, I thought SQL Server defaulted to the dboschema. Maybe try to qualify the schema for the Stored Proc.
虽然，我认为 SQL Server 默认为dbo架构。也许尝试限定存储过程的模式。
e.g
例如
Create Procedure dbo.MySP
Create Procedure dbo.MySP
