Create Database ADO.NET

  • Thread starter Thread starter Mark Moore
  • Start date Start date
M

Mark Moore

This seems like the most newbie question, but I can't find
the answer after 1/2 a day searching.

How can I create a new database on a (MSDE) SQL server
from .NET?

Here's the code I use, but it looks like ASPNET doesn't
have permission to create a new database.

{
SqlConnection myConnection = new SqlConnection
("Integrated Security=SSPI;Server=
(local);Database=rescopa");
SqlCommand cmd;

try
{
myConnection.Open();
}
catch ( SqlException e )
{
if ( e.Number != 4060 &&
e.Number != 911 ) throw
e; //BUGBUG: How does SqlException select for specific
errors?

SqlConnection conn2 = new SqlConnection
("User ID=sa;Server=(local)");
conn2.Open();
cmd = new SqlCommand( "CREATE DATABASE
rescopa", myConnection );
cmd.ExecuteNonQuery();
conn2.Close();

myConnection.Open();
}
}
 
Damn! I knew I was too quick on the trigger with the
previous post...

The code I posted had some mods I was trying that didn't
work. Here's the code that I meant to post followed by
the response I get from the IIS server when I execute the
CREATE DATABASE command:

-----------code---------------
{
SqlConnection myConnection = new SqlConnection
("Integrated Security=SSPI;Server=
(local);Database=rescopa");
SqlCommand cmd;

try
{
myConnection.Open();
}
catch ( SqlException e )
{
if ( e.Number != 4060 &&
e.Number != 911 ) throw
e; //BUGBUG: How does SqlException select for specific
errors?

SqlConnection conn2 = new SqlConnection
("Integrated Security=SSPI;Server=(local)");
conn2.Open();
cmd = new SqlCommand( "CREATE DATABASE
rescopa", conn2 );
cmd.ExecuteNonQuery();
conn2.Close();

myConnection.Open();
}
}
-----------html---------------
<html>
<head>
<title>CREATE DATABASE permission denied in
database 'master'.</title>
<style>
body {font-family:"Verdana";font-
weight:normal;font-size: .7em;color:black;}
p {font-family:"Verdana";font-
weight:normal;color:black;margin-top: -5px}
b {font-family:"Verdana";font-
weight:bold;color:black;margin-top: -5px}
H1 { font-family:"Verdana";font-
weight:normal;font-size:18pt;color:red }
H2 { font-family:"Verdana";font-
weight:normal;font-size:14pt;color:maroon }
pre {font-family:"Lucida Console";font-
size: .9em}
.marker {font-weight: bold; color:
black;text-decoration: none;}
.version {color: gray;}
.error {margin-bottom: 10px;}
.expandable { text-decoration:underline;
font-weight:bold; color:navy; cursor:hand; }
</style>
</head>

<body bgcolor="white">

<span><H1>Server Error in '/ResCopa'
Application.<hr width=100% size=1 color=silver></H1>

<h2> <i>CREATE DATABASE permission denied in
database 'master'.</i> </h2></span>

<font face="Arial, Helvetica, Geneva, SunSans-
Regular, sans-serif ">

<b> Description: </b>An unhandled exception
occurred during the execution of the current web request.
Please review the stack trace for more information about
the error and where it originated in the code.

<br><br>

<b> Exception Details:
</b>System.Data.SqlClient.SqlException: CREATE DATABASE
permission denied in database 'master'.<br><br>

<b>Source Error:</b> <br><br>

<table width=100% bgcolor="#ffffcc">
<tr>
<td>
<code><pre>

Line 62: conn2.Open
();
Line 63: cmd = new
SqlCommand( &quot;CREATE DATABASE rescopa&quot;, conn2 );
<font color=red>Line 64:
cmd.ExecuteNonQuery();
</font>Line 65:
conn2.Close();
Line 66: </pre></code>

</td>
</tr>
</table>

<br>

<b> Source File: </b>
c:\proj\rescopa\wwwroot\signin.ascx.cs<b> &nbsp;&nbsp;
Line: </b> 64
<br><br>

<b>Stack Trace:</b> <br><br>

<table width=100% bgcolor="#ffffcc">
<tr>
<td>
<code><pre>

[SqlException: CREATE DATABASE permission denied in
database 'master'.]
System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
rescopa.com.SignIn.Authorize() in
c:\proj\rescopa\wwwroot\signin.ascx.cs:64
rescopa.com.SignIn.btnSignIn_Click(Object sender,
EventArgs e) in c:\proj\rescopa\wwwroot\signin.ascx.cs:112
System.Web.UI.WebControls.LinkButton.OnClick(EventArgs
e)

System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBac
kEventHandler.RaisePostBackEvent(String eventArgument)
System.Web.UI.Page.RaisePostBackEvent
(IPostBackEventHandler sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent
(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()
</pre></code>

</td>
</tr>
</table>

<br>

<hr width=100% size=1 color=silver>

<b>Version
Information:</b>&nbsp;Microsoft .NET Framework
Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

</font>

</body>
</html>
<!--
[SqlException]: CREATE DATABASE permission denied in
database 'master'.
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at rescopa.com.SignIn.Authorize() in
c:\proj\rescopa\wwwroot\signin.ascx.cs:line 64
at rescopa.com.SignIn.btnSignIn_Click(Object sender,
EventArgs e) in
c:\proj\rescopa\wwwroot\signin.ascx.cs:line 112
at System.Web.UI.WebControls.LinkButton.OnClick
(EventArgs e)
at
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBac
kEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent
(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent
(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain()
[HttpUnhandledException]: Exception of type
System.Web.HttpUnhandledException was thrown.
at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain()
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequest(HttpContext
context)
at
System.Web.CallHandlerExecutionStep.System.Web.HttpApplicat
ion+IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep
(IExecutionStep step, Boolean& completedSynchronously)
-->
 
MSDE (as you have discovered) is NOT a development tool--it's a SQL Server
engine to be used during deployment. Get a copy of SQL Server Developer
Edition ($49). It has the full toolset needed to create databases and manage
the tables, users, views, stored procedures, triggers and most importantly,
the permissions.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Bill,

Thanks for the tip (I just bought a copy), but... This
still doesn't help me with my problem.

I'm developing a website that will be deployed to servers
that are running MSDE. When I'm bootstrapping the
database (before it exists), I would like to create a
fresh, empty database.

How do I do this programmatically?
 
That's pretty easy. Simply execute (fill in your own name and filenames) the
following script on the Master database.
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\saledat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
-- ____________________________________William (Bill) VaughnAuthor, Mentor,
ConsultantMVP, hRDwww.betav.comPlease reply only to the newsgroup so that
others can benefit. This posting is provided "AS IS" with no warranties, and
confers no rights.__________________________________"Mark Moore"
 
Back
Top