Control If A SQL Database Exists Before Its Creation Or Deletion

Aug 17, 2006

Hi,
I'm using SQL Server 2005, and I would like to understand how to create and to drop a database without errors:
Infact, if I try to create a database that already exists, SQL Server throws the error "Impossible to create the database because it already exists", and if I try to drop a database that doesn't exist, SQL Server throws the error "Impossible to drop the database because it doesn't esist".
Before creating or dropping a database, I should control if it exists or not...
Is there a method to do that?
I found that such control for a table is the following one (in this case, I drop the table only if it exists):

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]

I tried to adapt the statement to the database case, modifying it as follows:
if exists (select * from dbo.sysobjects where id = object_id(N'[Database1]') and OBJECTPROPERTY(id, N'IsDatabase') = 1)
drop database [Database1]

but it didn't function (it was a blind attempt).
Can you suggest me a statement to do that?
Thank you very much

View 3 Replies


ADVERTISEMENT

Database Still 'exists' After Deletion

Apr 14, 2006

hi

Basically, I create a database with sql, then I delete it manually(not via sql statment. This is a problem which I realise. In fact, you can't delete the database because the VS 2005 still is using it) I run the same code again,
then it says the database still exists, even it is physically destroied.

------Here is the errors:
System.Data.SqlClient.SqlException: Database 'riskDatabase' already exists.
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolea
n breakConnection)

------The evidence that the database doesn't exist physically:
Unhandled Exception: System.Data.SqlClient.SqlException: Cannot open database "riskDatabase" requested by the login. The login failed.

------The code:
/*
* C# code to programmically create
* database and table. It also inserts
* data into the table.
*/

using System;
using System.Collections.Generic;
using System.Text;

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace riskWizard
{
public class RiskWizard
{
// Sql
private string connectionString;
private SqlConnection connection;
private SqlCommand command;

// Database
private string databaseName;
private string currDatabasePath;
private string database_mdf;
private string database_ldf;

public RiskWizard(string databaseName, string currDatabasePath, string database_mdf, string database_ldf)
{
this.databaseName = databaseName;
this.currDatabasePath = currDatabasePath;
this.database_mdf = database_mdf;
this.database_ldf = database_ldf;
}

private void executeSql(string sql)
{
// Create a connection
connection = new SqlConnection(connectionString);

// Open the connection.
if (connection.State == ConnectionState.Open)
connection.Close();

connection.ConnectionString = connectionString;
connection.Open();

command = new SqlCommand(sql, connection);
try
{
command.ExecuteNonQuery();
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
}

public void createDatabase()
{
string database_data = databaseName + "_data";
string database_log = databaseName + "_log";

connectionString
= "Data Source=.\SQLExpress;Initial Catalog=;Integrated Security=SSPI;";

string sql = "CREATE DATABASE " + databaseName + " ON PRIMARY"
+ "(name=" + database_data + ",filename=" + database_mdf + ",size=3,"
+ "maxsize=5,filegrowth=10%)log on"
+ "(name=" + database_log + ",filename=" + database_ldf + ",size=3,"
+ "maxsize=20,filegrowth=1)";

executeSql(sql);
}

public void dropDatabase()
{
connectionString
= "Data Source=.\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

string sql = "DROP DATABASE " + databaseName;

executeSql(sql);
}

// Create table.
public void createTable(string tableName)
{
connectionString
= "Data Source=.\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

string sql = "CREATE TABLE " + tableName +
"(userId INTEGER IDENTITY(1, 1) CONSTRAINT PK_userID PRIMARY KEY," +
"name CHAR(50) NOT NULL, address CHAR(255) NOT NULL, employmentTitle TEXT NOT NULL)";

executeSql(sql);
}

// Insert data
public void insertData(string tableName)
{
string sql;

connectionString
= "Data Source=.\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1001, 'Puneet Nehra', 'A 449 Sect 19, DELHI', 'project manager') ";
executeSql(sql);

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1002, 'Anoop Singh', 'Lodi Road, DELHI', 'software admin') ";
executeSql(sql);

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1003, 'Rakesh M', 'Nag Chowk, Jabalpur M.P.', 'tester') ";
executeSql(sql);

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1004, 'Madan Kesh', '4th Street, Lane 3, DELHI', 'quality insurance mamager') ";
executeSql(sql);
}

public static void Main(String[] argv)
{
string databaseName = "riskDatabase";
string currDatabasePath = "E:\liveProgrammes\cSharpWorkplace\riskWizard\A pp_Data";
// Need to be more flexible.
string database_mdf = "'E:\liveProgrammes\cSharpWorkplace\riskWizard\ App_Data\riskDatabase.mdf'";
string database_ldf = "'E:\liveProgrammes\cSharpWorkplace\riskWizard\ App_Data\riskDatabase.ldf'";

RiskWizard riskWizard = new RiskWizard(databaseName, currDatabasePath, database_mdf, database_ldf);
riskWizard.createDatabase();
riskWizard.createTable("userTable");
riskWizard.insertData("userTable");
//riskWizard.dropDatabase();
}
}
}

View 1 Replies View Related

Database Deletion

Apr 15, 2008

While performing import actions I had a system freeze, when the system returned the sessions had been closed and the database had vanished, with the help of support we recovered the database only to find that the original project ID had a suffix attached ( Original 40/0110, New 40/0110-1 ), when I try to return it to it's original numbering convention it says it has to be a unique number which suggests to me it is not deleted but hiding in the background, can the original be recovered or is it possible to renumber the recovered database, I have searched the whole of the databases and the original is nowhere to be seen.

View 1 Replies View Related

Deletion/Rename Of Master Database.

Nov 19, 2007

Hi All,

Can we have an sql server installation where we dont have a master database. Can the complete data dictionary be stored in another database , or put it other way can master database be renamed.

I have a need to assume that there will always be a master database for any SQL server instance. Want to confirm whether this assumption is true or not.

Thanks in advance.

Chandrakant Karale.

View 4 Replies View Related

Database Creation

Jan 11, 2008

Hi,
I am not sure is this a proper place for my question.
All we know that creating a database can be done as the following.
1) Create a project named DBExample
2) Click Add->New Item.
3) Select SQL Databse and type a name.(Ex:  Database1.mdf) A database is added to the project
4) Then add table and input values.
5) Set the primary key....
My question is I parsed a text file to form a 2d string array x. How can I convert x into the database?
 
Thanks

View 4 Replies View Related

Database Creation

Aug 16, 2005

Hello...
I want to develop a web site having two features
1. Online Shopping2. Forums
Im using SQL Server, ASP.NET and C#. Now the problem is that how do I configure the Databases. Whether I create new database for each or I marge the both things into one database. if i create saperate databases for each of the feature then users have to register for two times, first for forums and second for shopping. I dont want to do this...! I want users to register just for once.
____________Thanks in advNauman Ahmed

View 1 Replies View Related

Database Creation

Feb 6, 2007

I downloaded the sample database AdventureWorks from microsoft . When I run the download it created the data and log files under data folder in MSQLalong with other data and log files of master databases etc. However I do not see this database in SQLserver management studio. Can some one help me how to fix this so that I will be able to see this database in management studio.

I am downloading this for SQL server reporting purpose

View 1 Replies View Related

Database Creation

Mar 15, 2007

hi

if i open the management studio express and trying to create a database by right clicking the server name and trying in the new query window,im getting error as

CREATE DATABASE permission denied in database 'master'.

can anyone help me in rectifying this error?

Thanks in advance



View 4 Replies View Related

Database Creation With SMO In SSE

Aug 4, 2006

Hi there.

I tried finding any info regarding this approach and I can't find any, so here I am (again!).

I'm trying to create a database using SMO on a freshly installed SQL Server Express (not going through the advanced install, not going through the surface area configuration, so a lot of things are "wrong").

I get an error

- use SMO to try and create a database; this is the pseudo-code combo used:

1. create new Smo.Database object;

2. create new primary file group and data file for that database (using Smo.FileGroup and Smo.DataFile); add them to the database.

3. invoke Create(). catch exception with the following details:

Exception details:

Microsoft.SqlServer.Management.Smo.FailedOperationException was caught
HelpLink="http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476"
Message="Create failed for Database 'NewDatabase'. "
Source="Microsoft.SqlServer.Smo"
Operation="Create"
StackTrace:
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
at Microsoft.SqlServer.Management.Smo.Database.Create()
at Penta.Core.Library.Sql.Classes.Penta_SqlDatabase.Create() in C:Documents and Settings[...]


InnerException:

{"Directory lookup for the file "C:\Documents and Settings\[...]\NewDatabase.mdf" failed
with the operating system error 5(Access is denied.).
CREATE DATABASE failed. Some file names listed could not be created. Check related
errors."}


Seems like user rights error.

Long story short, the only way the creation step works is if I change the Log on for the SQL Express service from the default Network Service to Local System (either through code or not).

Now, I know LocalSystem is not a wise idea, but it seems to be the only way.

I know it's probably something stupid, but what am I missing?

Thanks you in advance

View 3 Replies View Related

Dynamic Database Creation

Apr 6, 2007

Hi All,
I have a web site where the user can select from a list of databases (they're actually all the same structure, but have data unique to each client). Each database is a SQL 2005 database held in the App_Data folder. This all works a treat. Each database is simply named after the client it is holding data for, and the connection string for each is stored in a database that is accessed
However, what I need is a form where  a database name can be entered, and I can then create a new database with the same structure as the others - including tables, indexes and stored-procedures. I know I can use the copy database wizard in the Management Studio, but that isn't an option to the users of this system (it's to be used on an intranet, and the page I'm trying to create will only be used by project managers - but these people won't have a clue when it comes to configuring databases themselves). I have scripted a database, but have no idea on how I can actually use this script from within Visual Studio and more importantly, don't know how to configue it to have the database name as a parameter.
I've also seen some examples that suggest using SQL-DMO, but haven't been able to work out how to use it and according to Microsoft it shouldn't be used for new developments (http://msdn2.microsoft.com/en-us/library/ms132924.aspx).
Anyway, I really hope someone is able to offer some assistance on this one as I've spent a whole day trawling forums and various web-sites trying to find a solution to this.
Thanks & regards,
Paul

View 2 Replies View Related

Creation Scripts From A Database

Apr 3, 2006

I would like to know the sql command to generate all the scripts of a database so that I can replicate the same database for testing purpose. For instance, I need those scripts to help me re-create the database itself and indexes, triggers, and stored procedures of it if possible.
I remember there was a command to display all these scripts in Sql but I forgot it. If someone knows it, please help. Thanks in advance.
blumonde

View 3 Replies View Related

Auomate Creation Of Database

Jan 16, 2001

Hi!All
I need to automate creation of database by passing name of database .Is it possible to this?If yes How to do ?

Thanks in advance for your your co operation.
Pradip

View 1 Replies View Related

Creation Of Database From Dump

Aug 29, 2006

Hello,

I have a SQL database dump with me. I would like to create a database using the dump. Could somebody help me with the steps?

Thanks :)

View 1 Replies View Related

Database Creation Script

Oct 15, 2015

I've never scripted a creation of a database and it's schema,I have started to put the script together and wondered if the following two scenarios were possible and if so could the following be possible and if so could you show me.

Scenario 1: Before the script runs it checks to see if a database of the same name exists. In this example the database name is "database1". If it does the script will stop and display a message along the lines of "You are trying to create a database, which name already exists. Please either drop or rename the current database and rerun this script."

Scenario 2:The more difficult one I think. Is that it checks to see if "database1" exists. If it does it displays a message along the lines of "The database you are trying to create already exists. Please confirm that you either want to Drop the Database" You then press a key on the keyboard to continue the script and the database is dropped. It will then tell you to rerun the script in order to create the "database1" database. Or you could even give them the option to either drop the database or rename it with "_todatdate" at the end of it, but if you can get the script to do different things with different keyboard strikes?

DATABASE1 CREATION SCRIPT
Author: Ryan Keast

The following script creates a DATABASE1 database.
*/
/*DATABASE1 database creation */

[code]....

View 1 Replies View Related

Database Creation Problem

Jul 31, 2007

the following msg appears whn i try to create a new database in Microsoft Sql Server Management Studio Express.
I m on Vista Basic and using a administrator account...plz help.... i have my project whose backend is SQL 2005.

------------------------------------------------

TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Create failed for Database 'timepass'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

------------------------------

CREATE DATABASE permission denied in database 'master'. (Microsoft SQL Server, Error: 262)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=262&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

View 4 Replies View Related

[help] Scripted Database Creation

Jul 13, 2007

Hi all :)My apologies if I posted in the wrong groups, but I just jumpedin MS SQL waters, so any guidance will be appreciated.What I'm trying to do is the following process:[1] present operator with a web page (script)[2] once filled with db name and username, script will create..sql file[3] osql.exe will be called with, i presume, -i file.sql andcreate a databaseI have limited SQL knowledge, but I got the information fromEnterprise Manager when I ran 'All Tasks -Generate SQLScript' on how the .sql file should look like.I realized what are the commands that would create a fresh DB(I ran this for newly created DB), and figured my .php scriptshould create such a file.It's fairly basic, and I'm almost sure all of you know howoutputed .sql file looks like, but anyway here it is:Script is called with parameters 'six' as database name and'magarac' as user name:---------------------------------------------------------------IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name= N'six')DROP DATABASE [six]GOCREATE DATABASE [six] ON (NAME = N'six_Data', FILENAME = N'E:Databasepathsix_Data.MDF' , SIZE = 1, MAXSIZE = 20,FILEGROWTH = 10%) LOG ON (NAME = N'six_Log', FILENAME = N'E:Databasepathsix_Log.LDF' , SIZE = 1, MAXSIZE = 20, FILEGROWTH= 10%)COLLATE SQL_Latin1_General_CP1_CI_ASGOexec sp_dboption N'six', N'autoclose', N'false'GO....use [six]GOif not exists (select * from dbo.sysusers where name =N'guest' and hasdbaccess = 1)EXEC sp_grantdbaccess N'guest'GOif not exists (select * from dbo.sysusers where name =N'sinisam')EXEC sp_grantdbaccess N'magarac', N'magarac'GOexec sp_addrolemember N'db_owner', N'magarac'GO---------------------------------------------------------------I managed to get an exact replica of .sql file that EnterpriseManager created. It leads me to believe that this way ofautomated database creation is indeed possible.Really sorry for making you to go through all this text, butafter I get a green light on this from you guys, I'll have abit more problematic question.Is there any reason why this should not be used, or would fail?Thanks in advance :)P.S.Just as a heads-up, next part of my problem is automatedcreation of new MS SQL server login to use with new DB.

View 3 Replies View Related

Creation Of Reporting Database

Jul 20, 2005

We have a production database, but due to the crippling load of somereporting tools, we are looking to have a separate reporting database.Due to it's nature, the reporting database doesn't need to be as upto date as the master, perhaps it could be refreshed every evening?What I wish to know is the best way to go about this. Replicating awhole database seems excessive as there are 7000+ tables and we don'tneed it so closely matching the production database. Log shippingalso seems out as we need the reporting database to be highlyavailable and if we keep applying transaction logs ever 15 minutes orso, we need to kick all the users out.Is there a way of scripting or automatically getting SQL Server 2k tocopy over the last full backup it did and apply this to anotherserver? Also if we used this method, what would happen to the usersstored in the database? Would I have to keep re-adding them after theupgrade?Is there a better way to tackle this problem? All help gratefullyreceived. We are running SQL Server 2000 Enterprise Edition on aWindows 2000 Server machine.Thanks.

View 3 Replies View Related

Database Creation Using VB.NET Code

Jul 6, 2006

Hello.

I think its silly question but i expect some solution .

Is there any way to create sql sever database by using VB.NET code ? I have to create database,tables,sps and also transfer the data from one database to newly created database.

Please, if somebody have some solution then guide me in detail.

Thanks in adv.

View 12 Replies View Related

Database Script Creation

May 30, 2006

In the paste I have used SQL2000 to create db from the Query Analyser. In SQL2005 I can't see Query Analzyer but found under programs --> Microsoft SQL Server 2005 --> Configuration Tools --> Reporting Services Configuration --> Database Setup. It creates a database and I can connect to it from VS.NET. Here is my dilema, I was provided SQL scripts to create tables, stored procs etc. Where and how do I run these scripts to create these things in my newly create db.

Also how do I remove a db in SQL2005?

View 3 Replies View Related

Database Creation For Personl Starter Kit

Jun 9, 2005

I am unable to create the personal.mdf when I run my homepage.  I get the following error
Failed to generate a user instance of SQL Server due to a failure in
starting the process for the user instance. The connection will be
closed.
I am new at this stuff and any help will be welcomee. .
thanks
Seth

View 4 Replies View Related

SQL 2005 Express Database Creation

Dec 4, 2006

Hi guys,

i`ve just downloaded a copy of MS SQL 2005 Express. I'm relatively new to programming and DB management.

can i create a DATABASE using SQL 2005 Express itself? Or do i need other software?

View 1 Replies View Related

Need To Prevent Database Assembly Creation

Oct 12, 2006

On certain servers, I don't want developers to be able to create assemblies.
Unfortunately, the command

sp_configure 'clr enabled', 0

only prevents the CLR-type from being executed, not its creation.

I am unable to rename nor put triggers on
sys.assemblies,
sys.assembly_modules,
sys.assembly_files, and
sys.assembly_references .

I would prefer the user know the boundaries well before implementation.

Has anyone succeeded at this?

View 3 Replies View Related

Default Permissions On Creation Of New Database

Jan 23, 2008

I notice when I create a new database within my SQL Server that the
permissions for the new database automatically adds a user (Who is configured
as sysadmin) with dbo permissions to this database.

Both within the login properties of the select user (User mapping) is listed
as Default Schema, dbo and within the permissions of the database listed as
user with connect permissions.

I have other users configured as sysadmin and they do not get this rights
(They are not expliticly listed within user mapping with dbo or permissions
as user within the database).

I've inherited this system and wonder if the user has somehow changed the
new database procedure so it changes the default permissions of new databases.

Any way to check what he's done, I can see no differences between him and
the other sysadmins but he's definatly specifically listed as a dbo on all
new databases.

Although I don't mind him having access, he's a sysadmin after all, I'd like
to make it uniform thoughout the system, i.e. using the inhertited
permissions rather than specific permissions that seem to be created when the
new database is created.

Any ideas?

Flubster

View 1 Replies View Related

Database Creation From Inside Of Visual C#

Dec 19, 2006

When I create a database from inside of Visual C# Express, why can't I view it with the manager from the SQL Server Express Edition? I mean, it does not appear at all. Is there any problem?

View 5 Replies View Related

An Attempt To Attach An Auto-named Database For File (file Location).../Database.mdf Failed. A Database With The Same Name Exists, Or Specified File Cannot Be Opened, Or It Is Located On UNC Share.

Sep 2, 2007

Greetings, I have just arrived back into the country (NZ) and back into ASP.NET.
 I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page.
 Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer.
Cheers ~ J
 

View 3 Replies View Related

Changing Default Database Creation Location....

Aug 7, 2002

I have installed my modell database onto c:mssql7data...

How do I now change the properties of this so that when someone creates a database, the path it will get created to is set to d:
I thought that I could just move model, but it seems that this isn't possible.

Any advice?

thanks

derek

View 1 Replies View Related

Trigger Procedure Execution On Database Creation.

Feb 2, 2006

Hello,

Is there a way to trigger the execution of a procedure when a database is created ? We're using this 3rd party system in which you only specify the database server. Whenever the users create a new "Storage Area", the system actually creates a new database.

I need to handle backups for that system so I want to create a procedure that will generate a backup device and backup jobs automatically whenever a database is created.

I will also need 2 procedures to handle database renaming and deletion. I searched around but can't find a way to trigger the execution of my procedures.

View 4 Replies View Related

Trigger To Set Creation Date Of A New Record In Database?

Feb 28, 2014

I need a trigger to set the creation date of a new record in the database... I tried the following, but it changed all records, not just the new one...

CREATE TRIGGER trgCreationDate
ON [dbo].tabCustomerLookup
FOR INSERT
AS
BEGIN
UPDATE tabCustomerLookup
SET CreationDate = getdate()
END

View 3 Replies View Related

How To Change Default Path For Database Creation

May 1, 2007

Hi,
In my system, for creating a databae the default path is in C: drive, but iwant to change into D: drive as a default path.Please tell me how to change??

View 10 Replies View Related

Distributed Database Unique Index Creation

Nov 14, 2007

Can anyone suggest an appropriate strategy to create unique index's when a distributed database is used. As a bit of a background I have several SQL Server 2005 servers scattered around the country and I am trying to come up with a way to create a unique index i.e. one that hasn't been used in a particular table say Staff in any of the databases around the country.

Performance is an issue particularly as our network isn't that fantastic has anyone done something similar and achieved reasonable speeds?

Thanks ...

View 2 Replies View Related

Tools To Generating Database Creation Scripts.

Sep 29, 2006

Hello,
there are a tools, or a "ways" to generating "database creation scripts" from a MDF files?

Thank you for help.
Bye
Toni

View 13 Replies View Related

Integration Services :: Dynamic Creation On DataBase

Jul 31, 2015

I have a reqirement where in i need to create a Database dynamically in SSIS, Database name is given in table.

View 5 Replies View Related

Automate The Creation Of A Backup Job When A New Database Is Created?

Mar 20, 2008

I'd like to be able to create a new database and have a backup plan automatically created for it. Is there a way to do something like this?

Thanks!

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved