I am trying to create a database using SMO. I get this exception on the Create():
Create failed for database.
An exception occurred while executing a Transact-SQL statement or batch.
I tried may solutions and nothing worked. I ran the VS as administrator, the MSSMS too, I set the permission to all the users. Also I tried running this query in the MSSMS, with my directory, and it actually did work:
USE [master]
GO
CREATE DATABASE [test1] ON PRIMARY
( NAME = N'test1',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\test1.mdf',
SIZE = 70656KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)
LOG ON
( NAME = N'test1_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\test1_log.ldf',
SIZE = 164672KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
Source
http://dba.stackexchange.com/questions/22250/sql-server-create-file-encountered-operating-system-error-5-access-is-denied
My code is:
Console.WriteLine("Enter username");
string UserName = Console.ReadLine();
Console.WriteLine("Enter password");
string Password = Console.ReadLine();
string connectionString = null;
SqlConnection cnn ;
Server server = new Server();
server.ConnectionContext.LoginSecure = true;
try
{
connectionString = @"Data Source=" + server.Name + @"\Administrator" +
@"AttachDbFilename=|DataDirectory|Resources\DataBase." + UserName + ".mdf;" +
"Integrated Security=True;";
Database database = new Database(server, "DataBase." + UserName);
database.FileGroups.Add(new FileGroup(database, "PRIMARY"));
DataFile dtPrimary = new DataFile(database.FileGroups["PRIMARY"],
"PriValue", @"Resources\DataBase." + UserName + ".mdf;");
dtPrimary.Size = 77.0 * 1024.0;
dtPrimary.GrowthType = FileGrowthType.KB;
dtPrimary.Growth = 1.0 * 1024.0;
database.FileGroups["PRIMARY"].Files.Add(dtPrimary);
LogFile logFile = new LogFile(database, "Log",
@"Resources\DataBase." + UserName + ".ldf");
logFile.Size = 7.0 * 1024.0;
logFile.GrowthType = FileGrowthType.Percent;
logFile.Growth = 10.0;
database.LogFiles.Add(logFile);
database.Create();
database.Refresh();
}
I tried different connection strings.
Oh, and I am running a 2012 SQL.
What am I doing wrong?