Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

error in syntax of stored procedure in mssql server 2005

Posted on 2007-08-06
5
Medium Priority
?
1,279 Views
Last Modified: 2013-11-05
Hi,

I have the following code in a stored procedure: -

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            LH
-- Create date: 02/08/02007
-- Description:      Insert where used information into "dbo."
-- =============================================
ALTER procedure [dbo].[Where_Used](
@ProdID varchar(100),
@RigID varchar(100))
AS
BEGIN
   SET NOCOUNT ON
      insert into dbo.Where_Used values(@Prodid, @RigID)
END

but it comes up: -

Msg 2010, Level 16, State 1, Procedure Where_Used, Line 12
Cannot perform alter on 'dbo.Where_Used' because it is an incompatible object type.

How do I resolve this please.

Many Thanks
Lee
0
Comment
Question by:ljhodgett
  • 2
  • 2
5 Comments
 
LVL 21

Expert Comment

by:ziolko
ID: 19637006
you need:
insert into dbo.Where_Used(your_field_name_1, your_field_name2) values (@Prodid, @RigID)

ziolko.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 19637017
you cannot have a procedure AND a table with the same name.
you need to CREATE the procedure with a different name:

CREATE procedure [dbo].[Add_Where_Used]
( @ProdID varchar(100)
, @RigID varchar(100)
)
AS
BEGIN
   SET NOCOUNT ON
      insert into dbo.Where_Used (ProdID, RigId) values(@Prodid, @RigID)
END
0
 

Author Comment

by:ljhodgett
ID: 19637022
Hi,

No Joy i'm afraid. I've tried: -

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            LH
-- Create date: 02/08/02007
-- Description:      Insert where used information into "dbo."
-- =============================================
ALTER procedure [dbo].[Where_Used](
@ProdID varchar(100),
@RigID varchar(100))
AS
BEGIN
   SET NOCOUNT ON
      insert into dbo.Where_Used(ProductID, RigID) values (@Prodid, @RigID)
END

it says line 12 which is the "AS" part of the stored procedure.

Best Regards
Lee
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19637032
I assume you have tried ziolko's suggestion, but not mine yet...
0
 
LVL 21

Expert Comment

by:ziolko
ID: 19637036
ooops missed that name conflict

ziolko.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

564 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