Monday, March 19, 2012

cant run stored procedure

I trying to move one of my queries to stored procedure, i use sql server 2005

i went to the database, choosed "programability> stored procedure" and the "new stored procedure.

this is what i got after changing the text:

set ANSI_NULLSONset QUOTED_IDENTIFIERONgo-- =============================================-- Author:<Author,,Name>-- Create date: <Create Date,,>-- Description:<Description,,>-- =============================================create PROCEDURE [ShoreshDBUser].[shoreshsearch]-- Add the parameters for the stored procedure here@.tablenamevarchar(50), @.startpointint, @.wordvarchar(50),@.order varchar(50)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereSELECT L.*FROM (SELECT TOP (@.startpoint + 20) *FROM ShoreshDBUser.shoreshsearchWHERE FREETEXT (*, @.word)ORDER BY @.order desc) LLEFTJOIN (SELECT TOP (@.startpoint) *FROM ShoreshDBUser.shoreshsearchWHERE FREETEXT (*, @.word)ORDER BY @.order desc) RON L.id = R.idWHERE (R.idIsNULL)ORDER BY L.idEND
then i tried to run the procedure, i clicked "excute", enter the parameters, and i got error massege; Msg 208, Level 16, State 3, Procedure shoreshsearch, Line 19

Invalid object name 'ShoreshDBUser.shoreshsearch'.

i tried to call it from the asp.net code, but i get the same error: Invalid object name 'ShoreshDBUser.shoreshsearch'

but if i try to create new procedure in the same name, i get error massege: there is a object in that name.

i just can't understand what i do wrong... can any body help me?

The 2 part name means <objectOwner>.<objectName>. So by your script, SQL Server thinks [ShorehDbUser] is the owner of the object. Do you have any user with that name? Generally objects are prefixed with DBO as the owner. SO modify your proc to

CREATE PROCEDURE dbo.[shoreshsearch].

You might also want to drop the previous one, because you will end up with 2 procs - one with dbo as the owner and the other with [ShoreshDBUser] as the owner. This will further cause confusion to application or other users.

DROP PROC [ShoreshDBUser].[shoreshsearch] to drop the old one.

No comments:

Post a Comment