none
在Single_User的模式下,创建返回表的函数有时会遇到Database 'Dbname' is already open, and can only have one user at a time RRS feed

  • 问题

  • 当我把数据库设置为Single_User后,有些操作需要创建函数。
    当我创建类似于下面的函数

    CREATE FUNCTION [dbo].[AllNamedElementsInFolder] 
    (
    	@owner ObjectId
    )
    RETURNS 
    @result TABLE 
    (
        Id UNIQUEIDENTIFIER PRIMARY KEY
    )

    当函数的返回类型是表时,有时会遇到类似下面的错误
    Msg 924, Level 14, State 1, Procedure AllNamedElementsInFolder, Line 20
    Database 'DbName' is already open and can only have one user at a time.

    当我把返回表的参数改成了临时表,也会出现这个错误。只有当该参数改成了物理表,这个错误就消失了。请问有人知道这个是为什么么?
    我不想创建物理表,请问还有什么解决办法么?
    谢谢解答啦。

    2013年1月5日 5:30

答案

全部回复

  • Because sql needs open another session to the db in this case.
    2013年1月5日 19:16
  • Could you explain it more clearly?

    Thanks.

    2013年1月6日 1:16
  • Why put db in single user mode?
    2013年1月6日 2:24
  • Because we want to upgrade the database and not allow others to modify the database when we upgrade the database.

    2013年1月6日 5:33
  • You can set db to dba use only instead.
    2013年1月6日 20:20
  • The problem is reproduced on SQL 2008 R2 SP1 and it happens randomly,  at the rate of 3% -10%. And we have not reproduced it on SQL  2008 R2. So it may be a problem that related to SP1.

    As I find from the website of Microsoft, I find on page  ‘http://support.microsoft.com/kb/220842/en-us ‘ “BUG: Error 924 When Cross-Database Query Run from Context of Database in Single User Mode ”. The symptoms of the problem is below and extremely likes what we meet:

    If the following sequence of events occurs, you may receive error 924:

    1. A connection is made to a database in single user mode.

    2. From another connection, a query is run against the database that is in single user mode.

    3.A cross-database query is run from the first connection.

    The following is error 924:

        Server: Msg 924, Level 14, State 1, Line 0
        Database 'db_name' is already open and can only have one user at a time.

    Microsoft has confirmed this to be a problem in SQL Server 7.0, I think we may meet the same problem on SQL 2008 R2 SP1.

    What do you think about it?

    Do the Microsoft fix the problem?



    • 已编辑 Puppey 2013年1月8日 0:59
    2013年1月8日 0:58
  • Did you run cross-db query? Possible to test it again against sql2k8 r2 sp2? By the way, putting db in single user mode doesn't mean app user can't connect to the db.
    2013年1月8日 4:01
  • Did you run cross-db query? Possible to test it again against sql2k8 r2 sp2? By the way, putting db in single user mode doesn't mean app user can't connect to the db.

    I have upgrade to Sql2k8 r2 sp2 and have reproduced the problem.

    In single use mode, if one have connect to the db, the other users should can't to modify the database.

    In my opinion, I do some query in the function. And the return value is an table which is created in the tempdb database. Do this is a cross-db query?

    Thanks.

    2013年1月8日 8:23
  • You can report this issue to https://connect.microsoft.com/SQLServer.
    2013年1月8日 14:21
  • ok, thanks
    2013年1月9日 2:44