System.Data.sqlClient will only create default database on server

Question

Background:

I am using sql statements to create a Temp database on a server which will store data until it is needed further by my client program.

Problem:

My sql statement to create the database works properly and creates the database with all the required specifications when run through Sql Management studio, on the other hand when my program executes the statement it only creates a database with the 'Default' settings except for the name.

Questions:

Why is this?
How can I make it create a database with my specifications

Sql statement:

CREATE DATABASE Temp ON PRIMARY( NAME = Temp , FILENAME = 'C:\Temp.mdf' , SIZE = 2MB , FILEGROWTH = 10%) LOG ON ( NAME = Temp_Log , FILENAME = 'C:\Temp.ldf' , SIZE = 1MB, MAXSIZE = 70MB , FILEGROWTH = 10%)

Code:

public void AcuConvert()
        {
            using (DestD)
            {
                SqlCommand command = new SqlCommand();
                DestD.Open();
                command.Connection = DestD;
                foreach (var item in Entity.SqlDestinationQueries.ToList())
                {
                    command.CommandText = item.Query; 
                    command.ExecuteNonQuery();    //This is where the command is run
                }
                foreach (var item in Entity.SystemQueries.ToList())
                {
                    command.CommandText = item.Query.Replace("@Sys", SysD.Database);
                    command.ExecuteNonQuery();
                }
                foreach (var item in Entity.InsertQueries.ToList())
                {
                    command.CommandText = item.Query.Replace("@Source", SourceD.Database); ;
                    command.ExecuteNonQuery();
                }

            }
        }

Have you tried running SQL Profiler on the server and capturing the exact command executed against the server? That might give you a clue as to why it does not add the settings. — SchmitzIT
– SchmitzIT, Commented Oct 9, 2012 at 19:04
What is the connection string value and which user you're connecting in SSMS? — Rikki
– Rikki, Commented Oct 9, 2012 at 22:38
The user connecting has full rights to everything, the settings applied to the database upon creation through the above code has a logfile max size of 5mb and a default starting size of 10mb — Joshua Van Hoesen
– Joshua Van Hoesen, Commented Oct 10, 2012 at 12:12
I do the exact same thing in one of my applications - but using OleDB - and it works fine... — MiMo
– MiMo, Commented Oct 10, 2012 at 20:08

Dave R. · Accepted Answer · 2012-10-12 01:45:55Z

5

+100

Have you tried using SQL Server Management Objects instead of a raw SQL statement?

For example:

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

...

// Connect to the default instance
Server server = new Server();
// Establish new database details
Database database = new Database(server, "MyTempDB");
// Add primary filegroup details
database.FileGroups.Add(new FileGroup(database, "PRIMARY"));

// Set Primary datafile properties
DataFile primaryFile = new DataFile(database.FileGroups["PRIMARY"],
    "MyTempDB_Data", "C:\\MyTempDB.mdf");
primaryFile.Size = 2048;   // Sizes are in KB
primaryFile.GrowthType = FileGrowthType.Percent;
primaryFile.Growth = 10;
// Add to the Primary filegroup
database.FileGroups["PRIMARY"].Files.Add(primaryFile);

// Define the log file
LogFile logfile = new LogFile(database, "MyTempDB_Log", "C:\\MyTempDB_Log.ldf");
logfile.Size = 1024;
logfile.GrowthType = FileGrowthType.Percent;
logfile.Growth = 10;
logfile.MaxSize = 70 * 1024;
// Add to the database
database.LogFiles.Add(logfile);

// Create
database.Create();
database.Refresh();

You can connect to the server with specific credentials, too, of course:

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.aspx

If, however, you're stuck with using text scripts to create your database, I'd ensure that your Initial Catalog is set correctly (i.e. to 'master') in your connection string and your user has the necessary permissions - CREATE DATABASE / CREATE ANY DATABASE / ALTER DATABASE. If this still doesn't give you any joy, try stripping out the rest of your C# code and run the create SQL independently of the other statements - it could be that there's a side-effect from a preceding query. Use Profiler to see exactly what's running as you add them back in.

Edit:

I tested your script against a local SQL Server instance (2012 SqlLocalDB) via a small C# program using SqlClient and it ran just fine after changing the file paths to ones I had write access to (root of C is protected by default). The only other amendment was that the Primary size had to start at 3MB or more. Any smaller and the default tables could not be created. This may be another avenue of investigation for you to explore.

edited Oct 12, 2012 at 1:45

answered Oct 10, 2012 at 21:06

Dave R.

7,3033 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TToni Over a year ago

How big the primary data file has to be depends on the model database since it is created as a copy of the model db. You can shrink the model db to allow for smaller initial db sizes.

Dave R. Over a year ago

@TToni - that's interesting, I never knew you could shrink model. The 3MB default primary size was for a new SqlLocalDB install. I believe 2005, 2008 and 2008 R2 have a 2MB default, but I don't know what version the OP is using.

Rustin · Accepted Answer · 2012-10-16 08:51:04Z

0

Your alternative option could be to use the Process class to run the sqlcmd.exe Eg. var process = Process.Start(WORKING_PATH, argument);

%WORKING_PATH% being "C:\tools\sql\sqlcmd.exe"

%argument% being "C:\scripts\Create.sql"

I use this strategy to dump test data into test environments when bootstrapping acceptance test fixtures.

Cheers

answered Oct 16, 2012 at 8:51

Rustin

893 bronze badges

Collectives™ on Stack Overflow

System.Data.sqlClient will only create default database on server

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related