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

Question

当我把数据库设置为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.
当我把返回表的参数改成了临时表，也会出现这个错误。只有当该参数改成了物理表，这个错误就消失了。请问有人知道这个是为什么么？
我不想创建物理表，请问还有什么解决办法么？
谢谢解答啦。

Answer 1

Because sql needs open another session to the db in this case.

Answer 2

Could you explain it more clearly?

Thanks.

Answer 3

Why put db in single user mode?

Answer 4

Because we want to upgrade the database and not allow others to modify the database when we upgrade the database.

Answer 5

You can set db to dba use only instead.

Answer 6

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?

Answer 7

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.

Answer 8

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.

Answer 9

You can report this issue to https://connect.microsoft.com/SQLServer.

Answer 10

ok, thanks