whenever i create new database i am facing this error which says "Msg 1801, Level 16, State 3, Line 1
Database 'Abrican_Managment_system' already exists. Choose a different database name.
Msg 2714, Level 16, State 6, Line 2
There is already an object named 'Admin_TABLE' in the database" .
sounds like the database files exist on disk but detached. Look in ghe folder where the mdf,ldf files are pointing and you might see Abrican_Managment_system
Thanks for replying but that was the first DB created ?
note : When I create the Database the process is successful but when I add table massage shows that there is table with same name
According to the title of your post and the error message that you posted clearly the issue seems database related.
Show us your script that fails
CREATE DATABASE Organization_Managers_System;
CREATE TABLE User_table
(
User_ID INT IDENTITY(1, 1),
User_Name VARCHAR(150) UNIQUE,
User_Pass VARCHAR(150),
User_Pho VARCHAR(15),
User_CNIC VARCHAR(15),
User_DOB VARCHAR(150),
User_Email varchar(30),
User_Role varchar(150),
User_Add varchar(150),
CONSTRAINT User_TABLE_PK PRIMARY KEY (User_ID)
);
INSERT INTO User_TABLE VALUES
('Admin','12345', '+11 111 1111111', '11111-1111111-1','01/06/1989','user@gmail.com.','Admin',usa'),
('User','123456', '+22 222 2222222', '22222-2222222-2','01/07/1989','email@gmail.com.','User','canda');
Before create table do
Use Organization_Managers_System
Go
2 Likes
You need to separate the parts of the script into separate batches. Add a GO after the CREATE DATABASE statement - add the USE statement as @yosiasz has indicated and a GO after the CREATE TABLE statement.
If you have gotten errors stating the table already exists - that would be caused by the table being created in the master database instead of your newly created database.
2 Likes
I did this and the error was "Msg 945, Level 14, State 2, Line 3
Database 'Organization_Manager' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 2714, Level 16, State 6, Line 6
There is already an object named 'User_TABLE' in the database."
here is the statements:
CREATE DATABASE Organization_Manager;
Use Organization_Manager
GO
CREATE TABLE User_TABLE
(
User_ID INT IDENTITY(1, 1),
User_Name VARCHAR(150) UNIQUE,
User_Pass VARCHAR(150),
User_Pho VARCHAR(15),
User_CNIC VARCHAR(15),
User_DOB VARCHAR(150),
User_Email varchar(30),
User_Role varchar(150),
User_Add varchar(150),
CONSTRAINT User_TABLE_PK PRIMARY KEY (User_ID)
);
INSERT INTO User_table VALUES
('Admin','12345', '+11 111 1111111', '11111-1111111-1','01/06/1989','*******','Admin','Eritrea'),
('User','123456', '+22 222 2222222', '22222-2222222-2','01/07/1989','*****.','User','Saudi Arabia');
You need to check fhat the sql artifact already exists.
If not exists('select 1 from sys.databases where name ='Organization_Manager'
Begin
CREATE DATABASE Organization_Manager;
End
You missed the GO after the CREATE DATABASE. The GO after the CREATE TABLE...let's fix this up:
USE master;
GO
DROP TABLE IF EXISTS User_TABLE;
GO
CREATE DATABASE Organization_Manager;
GO
Use Organization_Manager
GO
CREATE TABLE User_TABLE
(
User_ID INT IDENTITY(1, 1),
User_Name VARCHAR(150) UNIQUE,
User_Pass VARCHAR(150),
User_Pho VARCHAR(15),
User_CNIC VARCHAR(15),
User_DOB VARCHAR(150),
User_Email varchar(30),
User_Role varchar(150),
User_Add varchar(150),
CONSTRAINT User_TABLE_PK PRIMARY KEY (User_ID)
);
GO
INSERT INTO User_table VALUES
('Admin','12345', '+11 111 1111111', '11111-1111111-1','01/06/1989','*******','Admin','Eritrea'),
('User','123456', '+22 222 2222222', '22222-2222222-2','01/07/1989','*****.','User','Saudi Arabia');
GO
I did modify the Query to what you have said but it is still not working the massage as how below
"Msg 1801, Level 16, State 3, Line 7
Database 'Organization_Manager' already exists. Choose a different database name.
Msg 945, Level 14, State 2, Line 10
Database 'Organization_Manager' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 2627, Level 14, State 1, Line 29
Violation of UNIQUE KEY constraint 'UQ__User_TAB__681E8A60F12408F0'. Cannot insert duplicate key in object 'dbo.User_TABLE'. The duplicate key value is (user).
The statement has been terminated."
This is the statement:
USE master;
GO
DROP TABLE IF EXISTS User_TABLE;
GO
CREATE DATABASE Organization_Manager;
GO
Use Organization_Manager
GO
CREATE TABLE User_TABLE
(
User_ID INT IDENTITY(1, 1),
User_Name VARCHAR(150) UNIQUE,
User_Pass VARCHAR(150),
User_Pho VARCHAR(15),
User_CNIC VARCHAR(15),
User_DOB VARCHAR(150),
User_Email varchar(30),
User_Role varchar(150),
User_Add varchar(150),
CONSTRAINT User_TABLE_PK PRIMARY KEY (User_ID)
);
GO
INSERT INTO User_table VALUES
('user','12345', '+11 111 1111111', '11111-1111111-1','01/06/1989','*******','Admin','Eritrea'),
('user','123456', '+22 222 2222222', '22222-2222222-2','01/07/1989','*****.','User','Saudi Arabia');
GO
You cant create something that already exists. Just dont copy paste, understand the code
You are dropping the table in the wrong database
@yosiasz - I added that because he probably created the table in master from his earlier attempts. Which is why later on he gets an error stating the table already exists - again, in the master database.
Looks like he has multiple problems now - a database that is partially created and needs to be dropped/removed, table created in the wrong database, etc.
1 Like
USE master;
GO
DROP TABLE IF EXISTS User_TABLE;
GO
If not exists(select 1 from sys.databases where name ='Organization_Manager')
Begin
CREATE DATABASE Organization_Manager;
End
go
Use Organization_Manager
GO
DROP TABLE IF EXISTS User_TABLE;
GO
CREATE TABLE User_TABLE
(
User_ID INT IDENTITY(1, 1),
User_Name VARCHAR(150) UNIQUE,
User_Pass VARCHAR(150),
User_Pho VARCHAR(15),
User_CNIC VARCHAR(15),
User_DOB VARCHAR(150),
User_Email varchar(30),
User_Role varchar(150),
User_Add varchar(150),
CONSTRAINT User_TABLE_PK PRIMARY KEY (User_ID)
);
GO
INSERT INTO User_table VALUES
('Admin','12345', '+11 111 1111111', '11111-1111111-1','01/06/1989','*******','Admin','Eritrea'),
('User','123456', '+22 222 2222222', '22222-2222222-2','01/07/1989','*****.','User','Saudi Arabia');
GO