SQL Server“找不到存储过程”没有架构名称
SQL Server 2005/2008,db=user=schema=owner='John',ASP.net 2.0.50727,vb,IIS7
我正在将网站从一台服务器移动到另一台服务器。这段代码在装有 SQL Server 2005 的旧服务器上运行良好。
Dim sqlCmdVehicle As SqlCommand = New System.Data.SqlClient.SqlCommand("SP_Name", sqlConn)
Dim dtVehicle As New DataTable
With sqlCmdVehicle
.Parameters.AddWithValue("FullStockNo", "N102010")
.CommandType = CommandType.StoredProcedure
End With
sqlConn.Open()
sqlAdapter.SelectCommand = sqlCmdVehicle
sqlAdapter.Fill(dtVehicle)
数据库已在新服务器上备份并成功恢复。当我尝试使用新的 SQL Server 2008 运行时,出现错误:
找不到存储过程“SP_Name”。指向最后一行。
我可以看到“MS SQL Management studio”的程序不是 dbo.SP_Name 而是 John.SP_Name
当我更改
System.Data.SqlClient.SqlCommand("SP_Name", sqlConn)
为
System.Data.SqlClient.SqlCommand("John.SP_Name", sqlConn)
所有程序时效果都很好,但与所有其他程序以及代码中的很多此类位置相同:(
我得到了http://msdn.microsoft.com/en-us/library/ms189915.aspx,但调用者的默认架构是正确的。 同样,在旧盒子上一切正常。
我应该在 SQL 中修复什么,以允许在没有明确提及的用户/架构名称的情况下运行 SP?
谢谢。
=========================================
不幸的是,我没有找到好的解决方案。我采取的方法是将整个项目的 SP_Name 搜索并替换为 John.SP_Name。感谢所有参与者。
SQL Server 2005/2008, db=user=schema=owner='John', ASP.net 2.0.50727, vb, IIS7
I'm moving website from one server to another. This piece of code works well on old server with SQL Server 2005.
Dim sqlCmdVehicle As SqlCommand = New System.Data.SqlClient.SqlCommand("SP_Name", sqlConn)
Dim dtVehicle As New DataTable
With sqlCmdVehicle
.Parameters.AddWithValue("FullStockNo", "N102010")
.CommandType = CommandType.StoredProcedure
End With
sqlConn.Open()
sqlAdapter.SelectCommand = sqlCmdVehicle
sqlAdapter.Fill(dtVehicle)
DB is backed up and successfully restored on new server. When I try to run with new SQL Server 2008 I got an error:
Could not find stored procedure 'SP_Name'. pointing at last line.
I can see procedures with 'MS SQL management studio' not as dbo.SP_Name but as John.SP_Name
When I change
System.Data.SqlClient.SqlCommand("SP_Name", sqlConn)
into
System.Data.SqlClient.SqlCommand("John.SP_Name", sqlConn)
all works well, but it's the same with all other procedures and a lot of such kind places in code :(
I got http://msdn.microsoft.com/en-us/library/ms189915.aspx, but caller's default schema is correct.
Again, all works well on old box.
What should I fix in SQL to allow run SP without explicitly mentioned user/schema name?
Thank you.
=======================================
Unfortunately, I didn't find fine solution. The way I took was search-and-replace SP_Name to John.SP_Name for the whole project. Thanks to all participants.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
![扫码二维码加入Web技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(7)
我不是 100% 确定,但您可能使用 sql username/pw 连接到数据库,然后使用用户名“john”。
在这种情况下,您的默认架构也是“john”,您必须直接使用 dbo.SP_name 才能正确访问您的过程。
同样有效的是将用户数据库的用户 john 的默认模式设置为“dbo”。您可以在用户的属性菜单中执行此操作。
站点注释:不要使用 sp_。如果您创建自己的过程,请使用例如 proc_ 代替。
使用 sp_ 总是使 SQL Server 首先查找 master 数据库。这会降低性能。
I am not 100% sure, but you probably have a connection to the database using sql username/pw and then user the username "john".
In this case your default schema is "john" as well and you directly have to use dbo.SP_name to access your procedure correctly.
What also works is to setup the default shema for the user john for the user database to "dbo". You can do this in the properties menu of the user.
Sitenote: Don't use sp_. If you create your own procedures use e.g. proc_ instead.
Using sp_ always make the SQL server looks in the master database first. This slow down the performance.
今天我们发现,如果您将用户权限更改为 SQL Server 上的系统管理员,即使您已在数据库中为用户指定了默认架构,但如果您尝试运行存储过程而不指定默认架构,默认架构也会被忽略。模式名称,它会认为该存储过程位于 dbo 模式下!当您从登录用户中删除 sysadmin 权限时,如果您在 EXEC 命令中未指定架构名称,它将使用为该用户的数据库设置的默认架构来运行存储过程。
We discovered today that, if you change your users permissions to be a sysadmin on the SQL Server, even though you have specified the default schema for your user in the database, the default schema is ignored if you try to run the stored procedure without specifying the schema name, it will think that the stored procedure is under the dbo schema! When you remove sysadmin rights from the login user, then it will run the stored procedure using the default schema set up for that database for the user, if you specify no schema name in your EXEC command.
如果您运行 SQL Profiler 并查看跟踪数据,您将确切地知道发生了什么。您将看到对数据库的确切调用是什么以及发生了什么。
我知道您已经提到过这一点,但我会仔细检查您的代码使用的用户是什么作为默认架构。如果未指定架构,SQL Server 将首先使用用户的默认架构,如果找不到该 DB 对象,则会尝试dbo em> schema,如果这不起作用,您将收到您看到的错误。
You will know exactly what is happening if you run SQL Profiler and view the trace data. You'll see what the exact call is to the database and what's happening.
I know you've already mentioned this, but I'd double check on what the user is that your code uses has as a default schema. If no schema is specified, SQL Server will first use the default schema of the user, and if it can't find that DB object then it'll try the dbo schema, and if that doesn't work, you'll get that error you see.
在 SQL Server 中>数据库>您的数据库名称>安全>模式
选择 dbo 模式 >属性>选择权限>
在该找到“用户和角色”部分
添加您的登录用户名和用户名的权限,然后检查“执行”。
In SQL Server> database> Your database Name> Security > Schemas
Select dbo Schemas > properties > Select Permission >
In that find "Users and role" section
Add your login User Name and permission for username give check on Execute.
SQL Server 2008 的默认架构是“dbo”。这就是它尝试命中 dbo.SP_Name 的原因。
您需要为用户设置默认架构。请参阅以下问题:
能否为 SQL 2008 查询设置默认架构
The default schema for SQL Server 2008 is "dbo". This is why it's trying to hit dbo.SP_Name
You need to set a default schema for the user. See the following question:
Can you set default Schema for SQL 2008 Query
不幸的是,我没有找到很好的解决方案。我采取的方法是将整个项目的 SP_Name 搜索并替换为 John.SP_Name。感谢所有参与者。
Unfortunately, I didn't find fine solution. The way I took was search-and-replace SP_Name to John.SP_Name for the whole project. Thanks to all participants.
@putnik 已经选择了答案,但有一个解决方案。
请参考@Joe P的回答。您不应使用 sysAdmin 创建用户。相反,您必须从默认架构中删除 dbo 并选择要使用的架构。
在 SQL Server 中 =>数据库=>数据库名称=>安全>用户
选择相关用户的属性=>选择常规=>选择默认架构 =>
点击三点=>浏览对象 =>选择相关架构而不选择 dbo 架构。
@putnik has already choosen the answer but there is a solution for this.
Please take reference @Joe P answer. You should not create a user with sysAdmin. Rather than that you have to remove dbo from default schema and choose which schemas you are going to use.
In SQL Server => database => database Name => Security > Users
Select Properties of related user => select General => Select Default Schema =>
Click three dot => browse for objects => choose related schemas without choosing dbo schema.