Re: Programatically creating a SQL database if it doesn't exist.

From: Matt (no_at_no.com)
Date: 03/22/04 

Date: Mon, 22 Mar 2004 10:49:05 -0500

That's a great idea, I don't know why I didn't think of that. :)

I'll take this idea and look at Peter's code and give it go. Sure
it'll work now.

Thanks much to everyone that offered some help (I'll also look up the
adonet group, didn't see when I looked last time)

M

On Mon, 22 Mar 2004 09:15:41 -0600, "Jay B. Harlow [MVP - Outlook]"
<Jay_Harlow_MVP@msn.com> wrote:

>Matt,
>In addition to the other comments.
>
>I would not expect the connect to work as you do not have the database yet
>(chicken & egg problem).
>
>I would try to simply connect to a known database first "master","model",
>"tempdb" come to mind.
>
>Then execute your statement.
>
>After executing your statement you can then change to your database.
>
>Something like:
>
> Dim connectionString As String = "UID=sa;PWD=pass;" & _
> "Initial Catalog=master;Data Source=localhost;"
>
> Dim sql As String = "CREATE DATABASE mydb ON PRIMARY" & _
> "(Name=test_data, filename = 'C:\mydb_data.mdf', size=3," & _
> "maxsize=5, filegrowth=10%)log on" & _
> "(name=mydbb_log, filename='C:\mydb_log.ldf',size=3," & _
> "maxsize=20,filegrowth=1)"
>
> Dim connection As New SqlClient.SqlConnection(connectionString)
> connection.Open()
> Dim cmd As New SqlClient.SqlCommand(sql, connection)
> cmd.ExecuteNonQuery()
> connection.ChangeDatabase("mydb")
>
> connection.Close()
>
>Of course I would probably have two connection strings or modify a single
>connection string, one to create the database and a second to actually
>connect & use it.
>
>Note: the SqlConnection.ChangeDatabase does a "USE mydb", so the connection
>above is left using the new database after it is created.
>
>Hope this helps
>Jay
>
>"Matt" <no@no.com> wrote in message
>news:snus50p6rkehl1p0issddf6ujh26k0vd85@4ax.com...
>> I need to be able to test an already started MSDE/SQL server for a
>> specific database. If that database doesn't exist, I want to create
>> it.
>>
>> I have the following statement someone provided on site, but if I
>> can't connect to the database, how can I execute the sql command?
>>
>>
>> Dim sql As String = "CREATE DATABASE mydb ON PRIMARY" +
>> "(Name=test_data, filename = 'C:\mysql\mydb_data.mdf', size=3," +
>> "maxsize=5, filegrowth=10%)log on" + "(name=mydbb_log,
>> filename='C:\mysql\mydb_log.ldf',size=3," + "maxsize=20,filegrowth=1)"
>>
>> If I try to connect with the following string, it fails and I can't
>> execute the sql string above, of course.
>>
>>
>> ConnectionString = "UID=sa;PWD=pass;" + "Initial Catalog=mydb;" +
>> "Data Source=localhost;"
>>
>>
>>
>> Thanks much for any help you can offer.
>

Relevant Pages

  • Re: How to protect Python source from modification
    ... They can then execute any arbitrary SQL command. ... >>SQL commands on their own database, who are you to tell them they ...
    (comp.lang.python)
  • Re: Cross-database execution permissions with certificates and sch
    ... activated user cannot access objects in other schemas in this database. ... injection is defended against, however, the threat exists anywhere EXECUTE ... CREATE USER dispatcher ... the certificate is a trusted authenticator because you granted AUTHENTICATE ...
    (microsoft.public.sqlserver.security)
  • Re: Problem with "while/fetchrow_array"
    ... The first returns the last database in the known world as a row. ... I'm not sure if mysql has a mysql-only sql command you can give to get ... and execute it without any error ...
    (perl.dbi.users)
  • Re: Execute Persmission denied on object sp_OACreate
    ... SQL Server doesn't check permissions on indirectly referenced objects as ... You can prevent ad-hoc execution of powerful master database procs while ... >I have a user who has execute permissions on a store procedure in a>database> which in turns executes 4 stored procedures in the master database. ...
    (microsoft.public.sqlserver.security)
  • Re: SQLCE performance from .NET CF v2.0
    ... Please remember when bulk inserting is being executed against SQL CE, ... > local database functionality. ... > database technology and I am considering switching to SQLCE at the same ... > execute the prepared statement again. ...
    (microsoft.public.dotnet.framework.compactframework)
Loading