none
When I create a function which return an table value in single_user mode in SQL Server2008 R2 SP1, there may be a problem "Database 'DbName' is already open and can only have one user at a time."

    Question

  • When I set the database in single_user mode, I try to create the function like below:

    CREATE FUNCTION AllOverridesOf
    (
    	@baseMethod UNIQUEIDENTIFIER
    )
    RETURNS 
    @Overrides TABLE 
    (
    	Id UNIQUEIDENTIFIER 
    )

    there may be a problem sometimes like below:

    Msg 924, Level 14, State 1, Procedure AllOverridesOf, Line 20
    Database 'DbName' is already open and can only have one user at a time.

    And I find if I don't use the temp table  in the function and create an physical table, the error is gone.

    Could anyone tell me why this happen and how to solve the problem, as there are many functions like this and I don't want to create an physical table?

    Thanks.

    Saturday, January 05, 2013 1:48 AM

All replies

  • So what is on line 20 in this function? Can you post the complete code?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, January 05, 2013 11:03 AM
  • >Database 'DbName' is already open and can only have one user at a time.

    Put the database DBNAME into multi-user mode.

    And I find if I don't use the temp table  in the function and create an physical table, the error is gone.

    You cannot use a #temptable in a user-defined function. You can only use @tablevariable.

    Functions have only READ access to the databases including tempdb.

    In a stored procedure you can use #temptable, in fact, "sky is the limit".

    For additional help, post code.


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012



    Sunday, January 06, 2013 1:11 AM
  •  
    CREATE FUNCTION AllOverridesOf
    (
    	@baseMethod UNIQUEIDENTIFIER
    )
    RETURNS 
    @Overrides TABLE 
    (
    	Id UNIQUEIDENTIFIER 
    )
    AS
    BEGIN
    	
    	WITH Overrides_CTE(Id) AS
    	(
    		SELECT Id FROM NamedElement WHERE Overridden = @baseMethod
    		UNION ALL
    		SELECT NamedElement.Id FROM NamedElement, Overrides_CTE WHERE Overrides_CTE.Id = NamedElement.Overridden
    	)
    	
    	INSERT INTO @Overrides SELECT * FROM Overrides_CTE
    	
    	RETURN 
    END
    GO
    GRANT REFERENCES, SELECT ON [AllOverridesOf] TO [public]
    GO

    Thanks
    Sunday, January 06, 2013 1:20 AM
  • For your case you don't need a multi line table valued function, you can create inline table valued function which performs better. 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Sunday, January 06, 2013 1:25 AM
  • Sorry, I didn't make it clear.

    Here is the code like below

    --use temp table

    declare table @Overrides ( Id UNIQUEIDENTIFIER )

    --use physical table

    create table Overrides
    (
    Id UNIQUEIDENTIFIER
    )

    Sunday, January 06, 2013 1:28 AM
  • I am not able to reproduce the problem. However, I run with intellisense disabled, and I would guess that intellisense is the culprit. You cannot use intellisense in an single-user database, since intellisense uses a background connection to get the metadata.

    The easiest way out is of course to set the database in multi-user mode. Is there any particular reason you want to have it in single-user mode?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, January 06, 2013 10:53 AM
  • 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?

    Tuesday, January 08, 2013 12:59 AM
  • 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?

    Tuesday, January 08, 2013 1:00 AM
  • 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?

    Tuesday, January 08, 2013 1:00 AM
  • Why is the database in the single-user mode?

    That is just asking for trouble.

    Put the database into the multi-user mode:

    USE [master]
    GO
    ALTER DATABASE [YOURDATABASE] SET  MULTI_USER WITH NO_WAIT
    GO


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Tuesday, January 08, 2013 1:05 AM
  • Thanks for your reply.

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

    Tuesday, January 08, 2013 1:10 AM
  • I have posted the code where the problem happens above.

    Does that enough to see the problem?

    Tuesday, January 08, 2013 1:45 AM
  • First of all, you need to install SP2 - the latest SP for SQL Server 2008 R2.

    Secondly, if your function code is what you showed, then I do not see a reason for multi line table valued function. Create this function as inline function and see if the problem goes away.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, January 08, 2013 3:41 AM
  • You should put the database in single-user mode in the script where you run the upgrade. I also recommend that you run the script from SQLCMD, or from your own upgrade program, but not from SSMS, in which case Intellisense can trip you.

    Instead of single-user mode, you could consider RESTRICTED USER. In this mode, only users in the db_owner database role and the sysadmin and dbcreated server roles can access the database. That is, plain users does not apply. ...unless your application always connects with sa or something else of an equally vile nature.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, January 08, 2013 8:34 AM
  • I have upgrade to SQL 2008 R2 SP2.

    For the inline table function, For example, should I do it like this below:

    The original function:

    CREATE FUNCTION [dbo].[AllNamedElementsInFolder] 
    (
    	@owner ObjectId
    )
    RETURNS 
    @result TABLE 
    (
        Id UNIQUEIDENTIFIER PRIMARY KEY
    )
    AS
    BEGIN
        DECLARE @folders TABLE
        (
           Id uniqueidentifier,
           Kind int
        )
        DECLARE @members TABLE
        (
           Id uniqueidentifier,
           Kind int
        );
    	-- Get all FOLDERS under the '@owner' until a NameSpace is found.
        WITH Folders_CTE(Id, Kind)
        AS
        (
           SELECT NamedElement.Id, NamedElement.Kind
           FROM NamedElement, FolderMember
           WHERE FolderMember.Owner = @owner AND NamedElement.Id = FolderMember.Node
           UNION ALL
           SELECT NamedElement.Id, NamedElement.Kind
           FROM NamedElement, FolderMember, Folders_CTE
           WHERE (Folders_CTE.Kind = 2 OR Folders_CTE.Kind = 64 OR Folders_CTE.Kind = 524288) AND
                 Folders_CTE.Id = FolderMember.Owner AND
                 NamedElement.Id = FolderMember.Node
        )
    	-- [@folders] contains all immediate FOLDERS and the most immediate NameSpaces from the '@owner'.
    	INSERT INTO @folders SELECT * FROM Folders_CTE
    	-- Collect all FOLDERS into [@members]. NameSpaces will be added later.
    	INSERT INTO @members SELECT * FROM @folders WHERE [@folders].[Kind] = 2 OR [@folders].[Kind] = 64 OR [@folders].[Kind] = 524288;
    	-- Get all members of the NameSpaces found above.
    	WITH NameSpace_CTE(Id, Kind)
        AS
        (
           SELECT *
           FROM @folders
           WHERE [@folders].[Kind] != 2 AND [@folders].[Kind] != 64 AND [@folders].[Kind] != 524288
           UNION ALL
           SELECT NamedElement.Id, NamedElement.Kind
           FROM NamedElement, NameSpace_CTE
           WHERE NamedElement.Owner = NameSpace_CTE.Id
        )
    	-- Collect all NamedElements found into [@members].
    	INSERT INTO @members SELECT * FROM NameSpace_CTE
    	-- Finally, return all distinct Id's (no repetition).
    	INSERT INTO @result SELECT DISTINCT Id FROM @members
    	RETURN 
    END

    Then change it to inline table function is

    CREATE FUNCTION [dbo].[AllNamedElementsInFolder] 
    (
    	@owner ObjectId
    )
    RETURNS TABLE 
    AS
    BEGIN
        DECLARE @folders TABLE
        (
           Id uniqueidentifier,
           Kind int
        )
        DECLARE @members TABLE
        (
           Id uniqueidentifier,
           Kind int
        );
    	-- Get all FOLDERS under the '@owner' until a NameSpace is found.
        WITH Folders_CTE(Id, Kind)
        AS
        (
           SELECT NamedElement.Id, NamedElement.Kind
           FROM NamedElement, FolderMember
           WHERE FolderMember.Owner = @owner AND NamedElement.Id = FolderMember.Node
           UNION ALL
           SELECT NamedElement.Id, NamedElement.Kind
           FROM NamedElement, FolderMember, Folders_CTE
           WHERE (Folders_CTE.Kind = 2 OR Folders_CTE.Kind = 64 OR Folders_CTE.Kind = 524288) AND
                 Folders_CTE.Id = FolderMember.Owner AND
                 NamedElement.Id = FolderMember.Node
        )
    	-- [@folders] contains all immediate FOLDERS and the most immediate NameSpaces from the '@owner'.
    	INSERT INTO @folders SELECT * FROM Folders_CTE
    	-- Collect all FOLDERS into [@members]. NameSpaces will be added later.
    	INSERT INTO @members SELECT * FROM @folders WHERE [@folders].[Kind] = 2 OR [@folders].[Kind] = 64 OR [@folders].[Kind] = 524288;
    	-- Get all members of the NameSpaces found above.
    	WITH NameSpace_CTE(Id, Kind)
        AS
        (
           SELECT *
           FROM @folders
           WHERE [@folders].[Kind] != 2 AND [@folders].[Kind] != 64 AND [@folders].[Kind] != 524288
           UNION ALL
           SELECT NamedElement.Id, NamedElement.Kind
           FROM NamedElement, NameSpace_CTE
           WHERE NamedElement.Owner = NameSpace_CTE.Id
        )
    	-- Collect all NamedElements found into [@members].
    	INSERT INTO @members SELECT * FROM NameSpace_CTE
    	-- Finally, return all distinct Id's (no repetition).
    	RETURN SELECT DISTINCT Id FROM @members
    END

    Does it correct?

    I'm sorry, I can't find more examples from the Internet and ask for your help.

    Thanks.


    • Edited by Puppey Tuesday, January 08, 2013 10:07 AM
    Tuesday, January 08, 2013 10:07 AM
  • No, that's not an inline function. An inline function consists of a single SELECT statement.

    I am not sure that I would rewrite that function to an inline function.

    In any case, inline vs. multi-statement has nothing to your problem with single-user databases.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, January 08, 2013 10:31 AM
  • No, the code for inline function is incorrect as it should only include SELECT statements. Your function code is a bit involved and I don't think it's easy to re-write it to inline. You probably should keep it as is then.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, January 08, 2013 12:40 PM
  • Now I have reproduced it on SQL 2008 R2 SP2 and as you sat, I  shouldn't change the function into inline table function.

    Do you have any other advices?

    Thanks.

    Wednesday, January 09, 2013 2:48 AM
  • Hi ,I have pasted more code below.

    Can you have a look? Or Should I do something more?

    Thanks for your help.

    Wednesday, January 09, 2013 2:50 AM
  • I have thought about your suggestion.

    As we set the databse into restricted user  to create the function and use my own upgrade program, there is still the problem, so we didn't take this way.

    Do you have any other suggestions, thanks.

    Wednesday, January 09, 2013 3:13 AM
  • Still what problem?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, January 09, 2013 8:41 AM
  •  I only set the step of create and call function to restricted user, after that I reset it back to single_user because there are other steps to deal with.

    I just run this in my own upgrade program, firstly I success serval some times, then failed just like the original problem.

    Once I find this way may result in failuer too, I decide not to try the way and try another way, such us closing other sql service, not use cursor.

    However, the problem still happens randomly.

    Wednesday, January 09, 2013 9:05 AM
  • If you had the database in restricted user, it cannot fail with an error that the database in is single-user mode, so much is clear. What error message do you get?

    It is difficult to assist with a problem that appears only random when I have so little information.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, January 09, 2013 10:09 AM