Trying to create a Database structure using T-SQL.

Question

I am trying to create a basic structure of a database using T-SQL. I checked it out using the SSMS parser and it came back with no errors. However, when I Execute the SQL I get the following errors:

Msg 2745, Level 16, State 2, Line 42
Process ID 66 has raised user error 50000, severity 22. SQL Server is terminating this process.
Msg 50000, Level 22, State 127, Line 42
Error in InstUS_Oligarchs.SQL, 'USE US_Oligarchs' failed! Killing the SPID now.
Msg 596, Level 21, State 1, Line 39
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 39
A severe error occurred on the current command. The results, if any, should be discarded.

The error occurs here:
if db_name() <> 'US_Oligarchs'
raiserror('Error in InstUS_Oligarchs.SQL, 'USE US_Oligarchs' failed! Killing the SPID now.'
,22,127) with log

GO

Please tell me what I am doing wrong.

Thanks,
MRM256

Answer 1

As Tom said, creating a database does not change your context to that database.

You need to do this:
...
CREATE DATABASE US_Oligarchs
GO
USE US_Oligarchs
GO
...

Also, there is not a reason to manually run CHECKPOINT in your script and I HIGHLY recommend against creating user defined data types, except for when needed for tables to pass to functions and procs.

I would highly recommend you use SSDT and project deployment, rather than manually creating deployment scripts.

Answer 2

Please post the entire script you are running.

The error indicates you are not in the correct database.

Answer 3

You create the database US_Oligarchs, but you don't have a
USE US_Oligarchs
statement, so your current database is master (from the USE master statement). So when your code checks the database name, it is not US_Oligarchs and the error is raised.

Tom

Answer 4

Your script start with the check if the database already exists, if so -> drop it

Why are you not using that kind of check also after creation?

If it exists then use database otherwise raise error

Then the rest of your creation script ;-)

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Answer 5

Hi @Mark McCumber
Your code has this statement to check whether the database currently in use is US_Oligarchs:

if db_name() <> 'US_Oligarchs'  
raiserror('Error in InstUS_Oligarchs.SQL, ''USE US_Oligarchs'' failed!  Killing the SPID now.',22,127) with log  

Without USE US_Oligarchs in your code, you will always using Database Master. Therefore, you need to add USE US_Oligarchs after the CREAE DATABASE statement.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.