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.idENDthen 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