Changing the default database files path fails

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?

Fails with which error ?

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

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

|||<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>|||

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);
}

|||

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