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
The default schema does not exist.The SP is:
CREATE PROCEDURE MySP
@tableName varchar(100)
AS
BEGIN
SET NOCOUNT ON;
declare @selectQuery varchar(MAX)
set @selectQuery = 'select * from ' + @tableName
exec(@selectQuery)
endkindly help
4条答案
按热度按时间8wigbo561#
我在INSERT语句中遇到了“The default schema does not exist”（默认架构不存在）错误。即使表在dbo架构中，使用dbo架构作为前缀也无济于事。
此用户的默认架构是自定义架构。它确实存在，用户可以查询其中的表。
最后我发现我必须在tempdb中添加一个同名的架构。我不太明白为什么需要这样做，但希望它能帮助其他搜索此错误消息的人。
jq6vz3qz2#
Use
CREATE PROCEDURE dbo.MySPThe 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.
Also you should use
quotename(@tableName)and a parameter type ofsysnamerather thanvarchar(100)to avoid SQL injection or just errors from non standard object names.f8rj6qna3#
You could change your default schema:
then avoiding having to include [dbo]. in the CREATE PROCEDURE
oipij1gg4#
这可能是因为与创建SP的用户关联的默认模式不再存在，或者用户不再具有对该模式的访问权限。
虽然，我认为SQLServer默认为
dbo架构。也许可以尝试限定存储过程的架构。例如，
Create Procedure dbo.MySP