Getting SQL Errors that should not occur because the database was dropped before reinstallation

Question

Getting SQL Errors that should not occur because the database was dropped before reinstallation

Mark McCumber 431

I have modified my SQL script to modify specific tables, but I am getting the following errors.

Beginning InstUS_Oligarchs.SQL at 27 Feb 2023 09:02:52:830 ....

Msg 50000, Level 1, State 1

Dropping existing US_Oligarchs database ....

Creating US_Oligarchs database....

Now at the create table section ....

Creating tblStatus ....

Msg 2714, Level 16, State 5, Line 94

There is already an object named 'tblStatus' in the database.

Msg 1750, Level 16, State 1, Line 94

Could not create constraint or index. See previous errors.

Inserting default data into tblCand_Status ....

Msg 1088, Level 16, State 11, Line 104

Cannot find the object "dbo.tblStatus" because it does not exist or you do not have permissions.

Msg 1088, Level 16, State 11, Line 112

Cannot find the object "dbo.tblStatus" because it does not exist or you do not have permissions.

Creating tblParty ....

Inserting Defaults data into tblParty ....

Msg 1088, Level 16, State 11, Line 164

Cannot find the object "dbo.tblOffice" because it does not exist or you do not have permissions.

Msg 1088, Level 16, State 11, Line 173

Cannot find the object "dbo.tblCand_Office" because it does not exist or you do not have permissions.

Creating tblCands table ....

Msg 1767, Level 16, State 0, Line 180

Foreign key 'FK__tblCands__Cand_S__3A81B327' references invalid table 'tblStates'.

Msg 1750, Level 16, State 1, Line 180

Could not create constraint or index. See previous errors.

Msg 1911, Level 16, State 1, Line 201

Column name 'StateID' does not exist in the target table or view.

Msg 1750, Level 16, State 0, Line 201

Could not create constraint or index. See previous errors.

Inserting Defaults data into tblStates ....

Msg 1088, Level 16, State 11, Line 213

Cannot find the object "dbo.tblStates" because it does not exist or you do not have permissions.

Completion time: 2023-02-27T09:02:53.1476785-06:00

Most of these errors occur because it says certain table objects exists in the database I am trying to recreate. How? the database has been dropped and recreated. I have run various queries checking both the tables and objects against the Master.dbo. If they don't show up in these queries how are they still in the database?

Thank you,

MRM256

Viorel 122.6K Reputation points

2023-02-28T17:31:07.3366667+00:00

Maybe the tables are created in a wrong database? Or maybe "There is already an object named 'tblStatus'" means that there is a non-table object called 'tblStatus'.

Did you also try stopping the script after creating the database and checking manually if the tables really exists?
Mark McCumber 431 Reputation points

2023-02-28T22:14:33.8466667+00:00

No, the SQL script USES the US_Oligarchs database that is dropped and recreated. However, when I try to create the supporting tabler the error occurs.

I used the following three SQL scripts to check the Master database for any traces of the tblStatus table.

Image 2:

Last one:

How can it say there is an already existing object when the above images say there isn't?

4 answers

Your answer

Viorel 122.6K Reputation points

2023-02-28T17:31:07.3366667+00:00

Maybe the tables are created in a wrong database? Or maybe "There is already an object named 'tblStatus'" means that there is a non-table object called 'tblStatus'.

Did you also try stopping the script after creating the database and checking manually if the tables really exists?
Mark McCumber 431 Reputation points

2023-02-28T22:14:33.8466667+00:00

No, the SQL script USES the US_Oligarchs database that is dropped and recreated. However, when I try to create the supporting tabler the error occurs.

I used the following three SQL scripts to check the Master database for any traces of the tblStatus table.

Image 2:

Last one:

How can it say there is an already existing object when the above images say there isn't?

Answer 1

Hi, it's possible that some objects are not being dropped or removed properly during the database drop/recreate process. Here are some suggestions to troubleshoot this issue:

Check if there are any active connections to the database. You cannot drop a database if any active connection is using it. Check if there are any connections to the database and disconnect them before trying to drop the database.
Double-check the SQL script you are using to recreate the database. Make sure that all the objects are being dropped and recreated properly. If you are using a separate script to drop objects, make sure it is executing correctly.
Try running the SQL script on a different server or instance to see if the issue persists. If the issue occurs on multiple servers, it's likely a problem with the SQL script.
Check if the database is being backed up and restored during the process. If so, make sure that the restore process is not leaving any objects behind.
If the issue persists, you may need to manually drop the objects using SQL commands or a tool like SQL Server Management Studio.

Also, make sure you are running the script with the correct user and permissions. If the user doesn't have the appropriate permissions, the script may not execute correctly.

Mark McCumber 431 Reputation points

2023-02-28T15:47:02.44+00:00

Hi Jose,

The PC where I installed SQL Server is on my home network, so there shouldn't be any other connections.

I have run three different SQL scripts to show if there are any existing objects in the database, they come back empty. I can provide screen shots if needed.

N/A - just one PC holding SQL Server.

N/A - Database isn't being backed up to my knowledge. Is there a way I can check this to be sure?

How do I manually delete the database? In SSMS you can right click on the name and delete it that way. Is this right>

Answer 2

Anonymous

Hi @Mark McCumber

In addition to the above answer, you can refer to this official document for deleting databases.

https://learn.microsoft.com/en-us/sql/relational-databases/databases/delete-a-database?view=sql-server-ver16

There are some prerequisites that need to be met before the database can be dropped.

Best regards,

Percy Tang

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Mark McCumber 431 Reputation points

2023-02-28T16:39:18.1566667+00:00

I am still confused. I manually deleted the database and ran a query to check for the database. If the DB doesn't exist create it and then followed it with a CREATE TABLE to create the tblStatus table.

I am still getting the same error. WHY?

MRM256
Mark McCumber 431 Reputation points

2023-02-28T22:15:34.0933333+00:00

Look at the comment I gave to Voirel.

Answer 3

Erland Sommarskog 121.4K MVP Volunteer Moderator

It had certainly helped to see the full script.

Typically, when this happens, there is a missing USE in the script, so the objects are created in the database from where you ran the DROP DATABASE, and the second time you do that, the tables will exist.

Mark McCumber 431 Reputation points

2023-03-01T15:37:34.2066667+00:00

Erland,

I will have to attach the SQL file, because the website will not allow me to place SQL queries inside the comment box.Inst_US_Oligarchs.txt
Jingyang Li 5,896 Reputation points Volunteer Moderator

2023-03-01T17:07:40.8066667+00:00
You name the primary key with the same name as your table, which causes problem. For example:

CREATE TABLE dbo.tblStatus( [StatusID] [int] NOT NULL IDENTITY, [Status] [varchar](10) NOT NULL, CONSTRAINT tblStatus PRIMARY KEY CLUSTERED ( StatusID )) GO
Mark McCumber 431 Reputation points

2023-03-01T18:34:57.2666667+00:00

This was the cause of the problem. Still have one to iron out

Answer 4

Mark McCumber 431

Jingyang LI pointed out the problem in the CONSTRAINT part of the table definition.

I didn't identify the primary key column in my supporting tables.

However, another monkey has popped up.

I will try to find an answer for this one on the internet before I post another question.

Thanks for the help,

MRM256

Jingyang Li 5,896 Reputation points Volunteer Moderator

2023-03-01T19:01:00.3433333+00:00

Your script has another problem to create reference before the table (column) was created. You may miss the table creation code.
Mark McCumber 431 Reputation points

2023-03-01T19:58:10.7266667+00:00

Yes, I saw that and made the proper correction. It was in the tblOffice. I had the INSERT part, not the CREATE part.

MRM256

Share via

Getting SQL Errors that should not occur because the database was dropped before reinstallation

4 answers

Your answer