C# - create new SQL Server database results in error

Question

I'm trying to create a new database on my server programmatically using Microsoft.SqlServer.Management.Smo, but it always fails to create the database:

Server svr = new Server(new ServerConnection(@".\SQLEXPRESS"));
Database db = new Database(svr, "TESTDB");
db.Create();

I get this error at db.create();:

An unhandled exception of type Microsoft.SqlServer.Management.Smo.FailedOperationException' occurred in Microsoft.SqlServer.Smo.dll

The server's name is correct, and I'm using Windows authentication to connect to the server.

Please help and if there is any better way please let me know

Is there a Message on the exception, or an InnerException? There are usually more details available than just the name of the exception. — Scott, Sep 05 '17 at 14:45
I would simply use SqlClient and create the database with TSQL DDL. SMO's not really any easier, and you have to install it. See https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-sql-server-transact-sql — David Browne - Microsoft, Sep 05 '17 at 14:47
when i clicked on details it gives me this : {"Create failed for Database 'TESTDB'. "} — Omar Hamdan, Sep 05 '17 at 14:48
yes @PieroAlberto i took the DLL references from the smo folder — Omar Hamdan, Sep 05 '17 at 14:57
Why use C# To create a table instead of an IDE like SSMS? Seems like an easy solution here. — Jacob H, Sep 05 '17 at 14:57
@DavidBrowne-Microsoft i already have all database queries written with T-SQL and i want to execute them at once on client PC using console app ... is there any better way you suggest — Omar Hamdan, Sep 05 '17 at 14:59
@JacobH i want to deploy my project and i want to use a small console app that can install the database with tables and views and functions ...i want it to be separated from the project — Omar Hamdan, Sep 05 '17 at 15:02
If you have a collection of TSQL batches, just use a SqlConnection and SqlCommand to run them. No need for SMO. — David Browne - Microsoft, Sep 05 '17 at 15:14
If you want to run a bunch of T-SQL command, you can just run them in SQLCMD.EXE. No code required but you have less control over it — Nick.Mc, Sep 06 '17 at 06:23
In fact the native deployment method using SSDT uses SQLCMD to run scripts — Nick.Mc, Sep 06 '17 at 06:26
Also, you're going to have to learn to read error messages properly if you want to solve problems. As mentioned in a message below, the message that matters is "an exception occurred while executing.....". You need to paste all of that into your question — Nick.Mc, Sep 06 '17 at 06:26

score 0 · Answer 1 · answered Sep 06 '17 at 06:19

In the image you posted I see the message "an exception occurred while executing a transact-sql statement or batch".

So, I think it is a permissions issue. As explained here, you can try this step list:

Log on to the computer as an Administrator (or Any user with administrator privileges)
Open "SQL Server Configuration Manager"
Click "SQL Server Services" on the left pane
Stop "SQL Server" and "SQL Server Agent" instance on the right pane if it is running
Run the SQL Express in single-user mode by right clicking on "SQL Server" instance -> Properties (on the right pane of SQL Server Configuration Manager).
Click Advanced Tab, and look for "Startup Parameters". Change the "Startup Parameters" so that the new value will be -m; (without the <>) example: from: -dc:\Program Files\Microsoft SQL.............(til end of string) to: -m;-dc:\Program Files\Microsoft SQL.............(til end of string)
Start the SQL Server
Open your MS SQL Server Management Studio and log on to the SQL server with "Windows Authentication" as the authentication mode. Since we have the SQL Server running on single user mode, and you are logged on to the computer with Administrator privileges, you will have a "sysadmin" access to the database.
Expand the "Security" node on MS SQL Server Management Studio on the left pane
Expand the "Logins" node
Double-click the 'sa' login
Change the password by entering a complex password if "Enforce password policy" is ticked, otherwise, just enter any password.
Make sure that "sa" Account is "enabled" by clicking on Status on the left pane. Set the radio box under "Login" to "Enabled"
Click "OK"
Back on the main window of MS SQL Server Management Studio, verify if SQL Server Authentication is used by right clicking on the top most node in the left pane (usually ".\SQLEXPRESS (SQL Server )") and choosing properties.
Click "Security" in the left pane and ensure that "SQL Server and Windows Authentication mode" is the one selected under "Server authentication"
Click "OK"
Disconnect from MS SQL Server Management Studio
Open "Sql Server Configuration Manager" again and stop the SQL Server instance.
Right-click on SQL Server instance and click on "Advanced" tab. Again look for "Startup Parameters" and remove the "-m;" that you added earlier.
Click "OK" and start the SQL Server Instance again
You should now be able to log on as "sa" using the new password that you have set in step 12.

Anyway, check here if the smo is correctly installed.

C# - create new SQL Server database results in error

1 Answers1