Changing the default database files path fails

SQL Server SMO/DMO

Waleed Yaseen


i have the following code segment which works fine as is:

try

{

ServerConnection connection = new ServerConnection("localhost");

Server server = new Server(connection);

if (!server.Databases.Contains("SMO_TEST"))

{

Database database = new Database(server, "SMO_TEST");

//FileGroup fileGroup = new FileGroup(database, "PRIMARY");

//database.FileGroups.Add(fileGroup);

//DataFile dataFile = new DataFile(fileGroup, "SMO_TEST_Data");

//dataFile.FileName = "D:\\SMO_TEST_Data.mdf";

//fileGroup.Files.Add(dataFile);

//LogFile logFile = new LogFile(database, "SMO_TEST_Log");

//logFile.FileName = "D:\\SMO_TEST_Log.ldf";

//database.LogFiles.Add(logFile);

database.Create();

}

}

catch (FailedOperationException ex)

{

Console.WriteLine(ex.Message);

}

when i uncomment the lines commented above it fails.

Any help



Top

Re: Changing the default database files path fails

Jens K. Suessmeyer


Fails with which error

Jens K. Suessmeyer

---
http://www.sqlserver2005.de
---



Top

Re: Changing the default database files path fails

Waleed Yaseen

hi Jenz

thanks for your reply. the code posted earlier worked smoothly just after a computer restart.the error message was either

"Create failed for Database 'SMO_TEST'." or "Drop failed for Database 'SMO_TEST'." with whatever mistake i made, which provided no help in correcting the problem.

however another issue arised when i tried to use a transaction using either method connection.BeginTransaction(); or server.ConnectionContext.BeginTransaction();

(1) how to correct this problem and

(2) what kind of transaction occures here is it a distributed transaction

also i would like to ask

(3)if there is any way to diable full text indexing other than using

connection.ExecuteNonQuery(databaseName + ".dbo.sp_fulltext_database @action = 'disable'"); (see the code below) .

sql server management studio creates the database with full text indexing disabled by default. but when using smo it is enabled by default and the documentation claims the opposite.

(4) so where does smo take its default values from

string sqlServerInstance = "localhost";

string databaseName = "SMO_TEST";

string dataFileLogicalName = databaseName + "_Data";

string logFileLogicalName = databaseName + "_Log";

string dataFilePhysicalName = dataFileLogicalName + ".mdf";

string logFilePhysicalName = logFileLogicalName + ".ldf";

string dataFilePath = "D:\\TEST_DIR\\";

string logFilePath = "D:\\TEST_DIR\\";

if (!Directory.Exists(dataFilePath))

{

Directory.CreateDirectory(dataFilePath);

//throw new DirectoryNotFoundException("The data file directory " + dataFilePath + " does not exists.");

}

if (!Directory.Exists(logFilePath))

{

Directory.CreateDirectory(logFilePath);

//throw new DirectoryNotFoundException("The log file directory " + logFilePath + " does not exists.");

}

string defaultFileGroupName = "PRIMARY";

ServerConnection connection;

connection = new ServerConnection(sqlServerInstance);

Server server = new Server(connection);

try

{

//connection.BeginTransaction();

server.ConnectionContext.BeginTransaction();

if (server.Databases.Contains(databaseName))

{

server.Databases[databaseName].Drop();

server.Alter();

}

Database database = new Database(server, databaseName);

FileGroup fileGroup = new FileGroup(database, defaultFileGroupName);

database.FileGroups.Add(fileGroup);

DataFile dataFile = new DataFile(fileGroup, dataFileLogicalName);

dataFile.FileName = dataFilePath + dataFilePhysicalName;

dataFile.GrowthType = FileGrowthType.KB;

dataFile.Growth = 1024d;

fileGroup.Files.Add(dataFile);

LogFile logFile = new LogFile(database, logFileLogicalName);

logFile.FileName = logFilePath + logFilePhysicalName;

logFile.GrowthType = FileGrowthType.Percent;

logFile.MaxSize = 2147483648d;

database.LogFiles.Add(logFile);

database.Create();

// disable full text indexing

connection.ExecuteNonQuery(databaseName + ".dbo.sp_fulltext_database @action = 'disable'");

//connection.CommitTransaction();

server.ConnectionContext.CommitTransaction();

}

catch (FailedOperationException ex)

{

//connection.RollBackTransaction();

server.ConnectionContext.RollBackTransaction();

Console.WriteLine(ex.Message);

}

finally

{

if (connection != null)

{

if (connection.IsOpen)

{

connection.Disconnect();

}

connection = null;

}

}

thanks again



Top

Re: Changing the default database files path fails

Jens K. Suessmeyer

<P align=left><FONT face=Arial size=2>1) Some commands cannot be used within a transaction, but the problem in your case is that the database seems to be in use during the dropping action, see the blog post on my site for more information about that:<BR><BR>2) local one<BR><BR>3) DId not see anything for that, SMO tell that this functionality is deprecated in further versions.</FONT></P>
<P align=left>&nbsp;</P>
<P align=left>4) Which values do you mean Some are coded in the SMO library, some are taken from the db system.</P>
<P dir=ltr style="MARGIN-RIGHT: 0px" align=left><BR><BR>Jens K. Suessmeyer<BR><BR>---<BR><A href="http://www.sqlserver2005.de">http://www.sqlserver2005.de</A><BR>---</P>


Top

Re: Changing the default database files path fails

Allen White

Waleed,

You need to add the data and log files before you can assign the filename properties. Try the code here (I've adjusted your code):

try
{
ServerConnection connection = new ServerConnection("localhost");
Server server = new Server(connection);

if (!server.Databases.Contains("SMO_TEST"))
{

Database database = new Database(server, "SMO_TEST");

FileGroup fileGroup = new FileGroup(database, "PRIMARY");
database.FileGroups.Add(fileGroup);
DataFile dataFile = new DataFile(fileGroup, "SMO_TEST_Data");
fileGroup.Files.Add(dataFile);
dataFile.FileName = "D:\\SMO_TEST_Data.mdf";
LogFile logFile = new LogFile(database, "SMO_TEST_Log");
database.LogFiles.Add(logFile);
logFile.FileName = "D:\\SMO_TEST_Log.ldf";
database.Create();
}
}
catch (FailedOperationException ex)
{
Console.WriteLine(ex.Message);
}





Top

Re: Changing the default database files path fails

Waleed Yaseen

dear MVPs,

I tried to loop through the inner exception of the failedOperationException and it turned out that the problem is that Database.Create(), Database.Drop() and

connection.ExecuteNonQuery(databaseName + ".dbo.sp_fulltext_database @action = 'disable'");

cannot be contained in a transaction

this answers my question about transactions

thank you very much


Top