CODEWITHSUNDEEP
c#.netsql-serverlinq-to-sql
Fedearne
Fedearne

Reputation: 7348

LINQ fails to create database

The following code example fails randomly on some PC's. On other PC's the problem cannot be reproduced. All PC's are running .NET 3.5 SP1 on Vista SP1.

string connection = @"Data Source=PCNAME\SQLEXPRESS;Database=TestDatabase ;User Id=sa;Password=ThePassword;";

TestDatabase db = new TestDatabase (connection);

if (!db.DatabaseExists())
{
    db.CreateDatabase();
}

DatabaseExists() returns false but CreateDatabase() throws this Exception:

System.Data.SqlClient.SqlException: Database 'TestDatabase ' already exists. Choose a different database name.

The documentation states that DatabaseExists() returns true if the database exists and can be opened.

What could cause the database not to be available?

EDIT: The database server is SQL Server Express 2008

Upvotes: 1

Views: 744

Answers (4)

Fedearne
Fedearne

Reputation: 7348

The connection issues are caused by the AUTO_CLOSE property was set to true. This is default for all SQL Server Express 2008 databases.

Setting this feature to off solved the issue:

ALTER DATABASE [database] SET AUTO_CLOSE OFF

Because AUTO_CLOSE stops the database after 30 seconds idle time, this also solved other unexplained database-related errors in the application.

More info on AUTO_CLOSE

Upvotes: 0

Amy B
Amy B

Reputation: 110071

Could it be a multi-user race condition?

User1: test database existance (false)
User2: test database existance (false)
User1: create database
User2: create database (exception!)

Upvotes: 0

Rahul Soni
Rahul Soni

Reputation: 4968

Just created a simple Windows Application with the following code...

       string connection = @"Data Source=.\SQLEXPRESS;Database=TestDatabase;Integrated Security=True";

        LinqToSQLDataContext dc = new LinqToSQLDataContext(connection);

        if (!dc.DatabaseExists())
        {
            dc.CreateDatabase();
        }

Ensure that there was no database with the name TestDatabase. As the code works as expected. The only difference in my case is that I am use Integrated Security. Of course, this is not the complete code, and I would get an error as expected at dc.CreateDatabase as follows...

alt text

Hence, I can confirm that the code is good. It looks like you don't have enough perms on the server.

Upvotes: 0

Lazarus
Lazarus

Reputation: 43064

It could be a rights issue, i.e. the database exists but you don't have permission to open it. That would return a false and also the exception you provided.

Upvotes: 2

Related Questions