Trouble creating database

Question

I am trying to create a database in sql named TalentAgency. My commands are as follows:

use master;

create database TalentAgency;

go;

use TalentAgency;

create table LOCATION (

      LocationId int not null identity (1,1),

      LocationName varchar(25) not null,

      AreaOrRoom varchar(25) null,

      constraint PK_LocationId primary key (LocationId)

);

create table AGENT (

      AgentId int not null identity (1,1),

      AgentName varchar(50),

      AgentPhone decimal(11,0),

      constraint PK_AgentId primary key (AgentId)

);

create table BOOKING (

      BookingId int not null identity (1,1),

      BookingDate datetime not null,

      PartyDate datetime not null,

      TotalCost money not null,

      LocationId int not null,

      AgentId int not null,

      constraint PK_BookingId primary key (BookingId),

      constraint FK_LocationId foreign key (LocationId)

);

I am getting the following error when executed.

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near 'go'.

Msg 911, Level 16, State 1, Line 5

Database 'TalentAgency' does not exist. Make sure that the name is entered correctly.

The database is not being created. The 'use TalentAgency' line is underlined in red.

I'm not sure where I am going wrong. I have tired to search the web for proper syntax and it seems to be correct to the best of my limited knowledge. :)

Any help would be appreciated.

Answer 1

Here is the fix (no ; after go, incomplete FK statement) with UNIQUE KEY suggestions:

use master;
go 
create database TalentAgency;
go
 

use TalentAgency;
 


create table LOCATION (
 
      LocationId int not null identity (1,1),
 
      LocationName varchar(25) not null UNIQUE,
 
      AreaOrRoom varchar(25) null, 

      constraint PK_LocationId primary key (LocationId)
 
);
 

create table AGENT (
 
      AgentId int not null identity (1,1),
 
      AgentName varchar(50) NOT NULL UNIQUE, 

      AgentPhone decimal(11,0), 

      constraint PK_AgentId primary key (AgentId)
 
);
 

create table BOOKING (
 
      BookingId int not null identity (1,1),
 
      BookingDate datetime not null,
 
      PartyDate datetime not null,
 
      TotalCost money not null,
 
      LocationId int not null, 

      AgentId int not null, 

      constraint PK_BookingId primary key (BookingId),

    UNIQUE (LocationID, AgentID, BookingDate)
 
);

 

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 2

use master

go

create database TalentAgency

go

use TalentAgency

go

create table....

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 3

The error is that you have a semicolon after go. While you are supposed to have semicolon after your SQL statements, this does not apply here, because go is not an SQL statement. "go" is an instruction to (SQL Server Management Studio) SSMS to send all text up to this point to SQL Server as a batch.

That is, when you say:

CREATE DATABASE TalentAgency
go
USE TalentAgency
go
CREATE TABLE LOCATION (...)

SSMS first sends "CREATE DATABASE TalentAgency" to SQL Server and then it sends "USE TalentAgency" and then the CREATE TABLE statements.

Sometimes these "go" are required, sometimes not. For instance there is no need to have "go" between you CREATE TABLE statements. But if you want to create a suite of stored procedures, you need to say:

CREATE PROCECURE first_sp ...
go
CREATE PROCECURE second_sp ...
go
CREATE PROCECURE third_sp...
go

This is because SQL Server requires that CREATE PROCEDURE to be the single statement in the batch.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

>Sometimes these "go" are required, sometimes not

... and if you don't intend to be an expert in the art of putting or not putting "go"-s, you can just place them generously, they are pretty harmless.

First exception: if you use @variables or @tablevariables in the T-SQL script, you need to put the "GO" to the very end of the script because the "GO" is a variable "killer" (terminates the scope of variables).

Second exception: "GO" cannot be used in programmability objects definition like stored procedures. At the very end of the definition you can place a "GO".

Third exception: where a "GO" interrupts the processing logic like in a cursor WHILE loop.

Example T-SQL script from BOL with 2 batches, I added the last "GO":

USE pubs
SET NOCOUNT ON
GO
DECLARE @pub_id char(4), @hire_date datetime
SET @pub_id = '0877'
SET @hire_date = '1/01/93'
-- Here is the SELECT statement syntax to assign values to two local 
-- variables.
-- SELECT @pub_id = '0877', @hire_date = '1/01/93'
SET NOCOUNT OFF
SELECT fname, lname
FROM employee
WHERE pub_id = @pub_id and hire_date >= @hire_date
GO
/*
fname	lname
Anabela	Domingues
Paul	Henriot
*/

http://msdn.microsoft.com/en-us/library/aa258839(v=sql.80).aspx

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 5

... and if you don't intend to be an expert in the art of putting or not putting "go"-s, you can just place them generously, they are pretty harmless.

Yes, all your points are well on the mark. Knowing when you need to use "go" and not, can be difficult even for a seasoned SQL Server programmer. One reason is that the rules have changed over the years. That is, these days you can say "USE mydb" and it takes effect immediately, but I don't think it was the case when I started to use SQL Server. And I still prefer to have go after USE and other statements that changes the presumptions for the rest of the batch.

A generalisation of Kalman's three points is that any piece of code that must be executed as a unit to make sense, must be a single batch. That is, if you have

First piece of code
go
Second piece of code

can, you select the framgemnts and press Execute for only that fragment and it will work? If you can't the "go" should be there.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

That worked. Thanks very much!

Answer 7

Very Helpful! Thanks!