Trying to create a Database structure using T-SQL.

Mark McCumber 431 Reputation points
2022-08-17T16:10:52.623+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,656 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
0 comments No comments
{count} votes

7 answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2022-08-17T19:40:41.45+00:00

    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.

    1 person found this answer helpful.

  2. Tom Phillips 17,716 Reputation points
    2022-08-17T17:38:26.343+00:00

    Please post the entire script you are running.

    The error indicates you are not in the correct database.


  3. Tom Cooper 8,466 Reputation points
    2022-08-17T18:15:20.58+00:00

    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


  4. Bjoern Peters 8,781 Reputation points
    2022-08-18T00:29:31.45+00:00

    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)

    0 comments No comments

  5. CosmogHong-MSFT 22,621 Reputation points Microsoft Vendor
    2022-08-18T02:14:31.173+00:00

    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.

    0 comments No comments