Solved

CREATE SQL VIEW

Posted on 2010-09-12
13
540 Views
Last Modified: 2012-05-10
I am having a problem creating a VIEW on SQL. I have all the required tables built, however when I specify to use a DATABASE

USE ABC123
CREATE VIEW abc_only AS
SELECT letter, Font, inserted_date
FROM summary
WHERE role = 'noun'

I get the following error:

'CREATE VIEW' must be the first statement in a query batch.


What is causing this error?
0
Comment
Question by:TAMSCODAN
  • 6
  • 6
13 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 33656550
The stupid SQL Server parser....

Put the word 'GO' on a line between to two commands.



Kent
0
 
LVL 11

Accepted Solution

by:
aelliso3 earned 167 total points
ID: 33656557
when you create a view, you are creating it in a specific database and it will use the database that you have it as the starting (or USE) db.
If you are planning on having it somehwere else, then just set the select statement as below with a fully qualified name:
CREATE VIEW abc_only AS
SELECT letter, Font, inserted_date
FROM ABC123.dbo.summary
WHERE role = 'noun'
0
 
LVL 3

Author Comment

by:TAMSCODAN
ID: 33656567
cool, however where would I put the GO statement.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 333 total points
ID: 33656569
USE ABC123
GO

CREATE VIEW abc_only AS
SELECT letter, Font, inserted_date
FROM summary
WHERE role = 'noun'
0
 
LVL 3

Author Comment

by:TAMSCODAN
ID: 33656574
wow, that was the issue! is there a way to delete the view in the script if it was already created? so i can run the script again.
0
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 333 total points
ID: 33656578
I'm working from memory, but I think that this is it:

  IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableName]') AND type in (N'V'))
   
0
 
LVL 3

Author Comment

by:TAMSCODAN
ID: 33656582
U ARE DA MAN!!!! THANKS!!!!
0
 
LVL 3

Author Comment

by:TAMSCODAN
ID: 33657906
I added the GO Statement as you mentioned previously, and I added a DROP VIEW statement at the begining of the code. Looks like the query executes without errors, however I dont see any results. Any suggestions?
0
 
LVL 45

Expert Comment

by:Kdo
ID: 33658059
Post the script that you're running.
0
 
LVL 3

Author Comment

by:TAMSCODAN
ID: 33658070
USE ABC123
GO
DROP VIEW abc_only
GO
CREATE VIEW abc_only AS
SELECT letter, Font, inserted_date
FROM summary
WHERE role = 'noun'
0
 
LVL 45

Expert Comment

by:Kdo
ID: 33658099

SELECT * FROM abc_only;

That should get you everything where role='noun'.

Are you saying that it returns no rows?  Check the role column and make sure that at least 1 row has the value 'noun'.  Remember that string comparison is case sensitive so it must be all lower case, too.


Kent
0
 
LVL 3

Author Comment

by:TAMSCODAN
ID: 33658147
There are 4 colums, letter, Font, inserted_date and role. The view that I want are letter, Font, inserted_date that contain noun in the role column. In reference to my database it has 4 entries. Are you saying to replace SELECT letter, Font, inserted_date with SELECT * FROM abc_only;
0
 
LVL 45

Expert Comment

by:Kdo
ID: 33658587
Sure.  The view that you've created returns the rows that you want.   A view name can be queried almost every place that a table name is appropriate.


Kent
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question