[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to create connection string for creating a new database

Posted on 2006-05-12
11
Medium Priority
?
278 Views
Last Modified: 2012-05-05
I'm using connection strings to SQL Server Express that have the form

"DataSource= .\SQLEXPRESS;AttachDbFilename="(path to my development directory)\CurrentDevelopmentDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

I'm assuming this works because the DB is already there, created "manually".

How might I build a connection string for a SQLCommand for a "Create DataBase" query?

I'm confused about what the AttachDbFileName would be, or if there would be another variable in its place, since there's no file yet.

Any guidance on this would be appreciated.

Thanks!
0
Comment
Question by:codequest
  • 6
  • 5
11 Comments
 
LVL 10

Expert Comment

by:vadim63
ID: 16672444
You can connect to master database and then you can check if "CurrentDevelopmentDB" is exists. If not, you can create it.
0
 
LVL 2

Author Comment

by:codequest
ID: 16674494
Thanks for the input.  I think I get the general idea.   You connect to an existing DB, which then can execute SQL to create a second DB.  

How do you check if the second DB exists?
0
 
LVL 10

Accepted Solution

by:
vadim63 earned 2000 total points
ID: 16674763
T-SQL:

Creating a database without specifying files:

USE master;
GO
IF DB_ID (N'CurrentDevelopmentDB') IS NOT NULL
DROP DATABASE CurrentDevelopmentDB;
GO
CREATE DATABASE CurrentDevelopmentDB;
GO
-- Verify the database files and sizes
SELECT name, size, size*1.0/128 AS [Size in MBs]
FROM sys.master_files
WHERE name = N'CurrentDevelopmentDB';
GO


Creating a database that specifies the data and transaction log files:

USE master;
GO
IF DB_ID (N'CurrentDevelopmentDB') IS NOT NULL
DROP DATABASE Sales;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);

-- execute the CREATE DATABASE statement
EXECUTE ('CREATE DATABASE CurrentDevelopmentDB
ON
( NAME = CurrentDevelopmentDB_dat,
    FILENAME = '''+ @data_path + 'CurrentDevelopmentDB.mdf'',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = CurrentDevelopmentDB_log,
    FILENAME = '''+ @data_path + 'CurrentDevelopmentDB.ldf'',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB )'
);
GO

GO

You can set the Names, Sizes, etc. as you need.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 2

Author Comment

by:codequest
ID: 16674781
Thanks for the detailed explanation.   I can see it's time to learn T-SQL!   Which is a good thing.   I'll take a look at this a little later and get back to you.
0
 
LVL 10

Expert Comment

by:vadim63
ID: 16674782
Two examples above will drop the existed "CurrentDevelopmentDB" and then will create a new one. Or you can just exit from the procedure if the "CurrentDevelopmentDB" already is exists.
0
 
LVL 10

Expert Comment

by:vadim63
ID: 16674795
0
 
LVL 2

Author Comment

by:codequest
ID: 16674811
Cool.   I've never looked at T-SQL before, so I'm going to have to start with "where do you write the code" and "where do you execute the code from";  later, as usual, I'll be looking for how to do it all from within VB.  
0
 
LVL 2

Author Comment

by:codequest
ID: 16674815
Meaning, T-SQL from within VB, if necessary and possible.
0
 
LVL 10

Expert Comment

by:vadim63
ID: 16675003
Sure, you can call any stored procedure from VB. Just declare the Database Name as variable.
0
 
LVL 2

Author Comment

by:codequest
ID: 16675015
Cool.  It'll be a day or so before I get to it, but big help!  I'll be back then with the points.
0
 
LVL 2

Author Comment

by:codequest
ID: 16722319
I got it to work from within VB, and also used T-SQL in some other contexts...your comments were helpful.  Thanks!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Screencast - Getting to Know the Pipeline
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question