SSMS-EE: Using Views T-SQL Error Messages 156 & 111

Question

Post reply

SSMS-EE: Using Views T-SQL Error Messages 156 & 111

S. H. Chang

Old Hand

Points: 349
More actions
May 15, 2006 at 6:52 am

#366193

Hi all,
I executed the following code statements in my SQL Server Management Studio Express Edition program that is on Windows XP Pro PC-Microsoft Windows NT 4 LAN system:
-----SQLQuery.sql-------
USE
testDb
GO
CREATE
VIEW InventoryPublisherView AS
(
SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON I.ID=P.InventoryID
SELECT
* from InventoryPublisherView
SELECT
Name, type, quantity, pubName from InventoryPublisherView
DELETE
FROM InventoryPublisherView WHERE ID=1
ALTER
VIEW InventoryPublisherView AS
(
SELECT ID,name,type,quantity,pubName FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON I.ID=P.InventoryID)
GO
///////////////////////////////////////////////////////////////////////////////////
I got the following error messages in Parse or Execute without Results:
Msg 156, Level 15, State 1, Procedure InventoryPublisherView, Line 3
Incorrect syntax near the keyword 'SELECT'.
Msg 111, Level 15, State 1, Procedure InventoryPublisherView, Line 6
'ALTER VIEW' must be the first statement in a query batch.
************************************************************
I have no ideas where in the SSMS-EE program I can look up for solving theses errors.
Please help tell me where I should look up and advise me what I should do to correct these problems.
Thanks in advance,
Scott Chang

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 1

Based on your posting

CREATE VIEW InventoryPublisherView AS

(

SELECT * FROM dbo.Inventory AS FULL OUTER JOIN dbo.Publisher P ON I.ID=P.InventoryID) -- Brace is Missing

GO

SELECT

* from InventoryPublisherView

GO

SELECT

Name, type, quantity, pubName from InventoryPublisherView

GO

DELETE

FROM InventoryPublisherView WHERE ID=1

GO

ALTER

VIEW InventoryPublisherView AS

SELECT ID,name,type,quantity,pubName FROM dbo.Inventory FULL OUTER JOIN dbo.Publisher P ON I.ID=P.InventoryID

Prasad Bhogadi
www.inforaise.com

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 2

Infact you don't really need a brace

CREATE VIEW InventoryPublisherView AS

SELECT * FROM dbo.Inventory FULL OUTER JOIN dbo.Publisher P ON I.ID=P.InventoryID

GO

Prasad Bhogadi
www.inforaise.com

S. H. Chang Old Hand Points: 349 More actions · Answer 3

Hi, I tried the revised code listed below:

USE

testDb

GO

ALTER

VIEW InventoryPublisherView

GO

CREATE

VIEW InventoryPublisherView AS

(

SELECT Name, type, quantity, pubName FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON I.ID=P.InventoryID)

GO

SELECT

* FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON I.ID=P.InventoryID

SELECT

* from InventoryPublisherView

SELECT

Name, type, quantity, pubName from InventoryPublisherView

DELETE

FROM InventoryPublisherView WHERE ID=1

GO

/////////////////////////////////////////////

I got the following new error message withou Results:

Msg 102, Level 15, State 1, Procedure InventoryPublisherView, Line 1

Incorrect syntax near 'InventoryPublisherView'.

******************************************

What wrong is it? Please help and respond.

Thanks,

Scott Chang