Post reply

When I try to run this with other queries it fails.

  • June 1, 2009 at 10:49 am

    #134482

    ------------------------------------------------------------------------------------------------------------

    -- Author: Mathieu Cupryk

    -- Create date: 05/06/2009

    -- Description: Get the fields from the table using ticketID

    -- ===========================================================================

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sprGetTicketByTicketID]') AND type in (N'P', N'PC'))

    DROP PROCEDURE [dbo].[sprGetTicketByTicketID]

    GO

    CREATE PROCEDURE [dbo].[sprGetTicketByTicketID]

    (

    @TicketID int

    )

    AS

    SET NOCOUNT ON

    declare @returnCode int

    select @returnCode = 0

    SELECT

    [TicketID],

    [TicketNumber],

    [TicketType],

    [TicketStatus],

    [TicketSeverity],

    [ShortDescription],

    [ExternalTicket],

    [ReasonMenu],

    [ProblemNote],

    [SolutionNote],

    [ActionNote],

    [FollowupNote],

    [CompaniesFlag],

    [AgentCreated],

    [DateCreated],

    [DateLastModified],

    [AgentClosed],

    [DateClosed],

    [AgentFollowup],

    [FollowupBy],

    [DeletedFlag],

    [TicketTypeOld],

    [AssignedTo],

    [RequestType],

    [ServiceImpact],

    [DateTimeAssigned],

    [Priority],

    [DateAssigned],

    [TrackIt],

    [ContactMethod],

    [LocationSIMs],

    [UserID],

    [TicketApplicationType],

    [AccountID]

    FROM [tbl_tickets]

    WHERE

    [TicketID] = @TicketID AND [DeletedFlag] = 0

    if (@@ERROR <> 0)

    BEGIN

    select @returnCode = @@ERROR

    END

    OnExit:

    SET NOCOUNT OFF

    RETURN @returnCode;

    GO

    -----------------------------------------------------------------------------------

    I am tying to run this with other stored procedure creations.

  • June 1, 2009 at 10:55 am

    #1002915

    Care to provide the error message(s) you are getting when you attempt this?

  • June 1, 2009 at 11:00 am

    #1002918

    Msg 2714, Level 16, State 3, Procedure sprGetTicketByTicketID, Line 60

    There is already an object named 'sprGetTicketByTicketID' in the database.

    But there is not. Something weird.

  • June 1, 2009 at 11:31 am

    #1002931

    Maybe there is S.P with the same name under different schema? Hard to believe that SQL is lying.

    What does this gives?

    select * from sys.sysobjects where xtype = 'P' and name = 'sprGetTicketByTicketID';

  • June 1, 2009 at 12:25 pm

    #1002958

    1 sprGetTicketByTicketID 330484256 P 1 0 0 0 0 0 2009-06-01 12:02:36.843 0 0 0 P 0 4 0 2009-06-01 12:02:36.843 0 0 0 0 0 0 0

  • June 1, 2009 at 12:27 pm

    #1002959

    looks ok to me.

  • June 1, 2009 at 12:32 pm

    #1002965

    Works fine on my system. Did you execute the complete script (including the IF EXISTS -> DROP)?

    I copied the procedure and changed the SELECT to an existing table in my environment. Works fine.

    The more I learn, the more I know what I do not know
    Blog: Things about Software Architecture, .NET development and T-SQL

    How to Post Data/Code to get the best Help How to Post Performance Problems

  • June 1, 2009 at 1:09 pm

    #1002983

    seems like S.P is there. So if u run your IF EXISTS DROP statement, without the CREATE S.P. (your below statemnt), and then run the SELECT * FROM sysobjects, do u still see the s.p in ur query result window?(You should not be seeing ur S.P), if u see it then maybe ur there is something wrong in your DROP statement(Although looks o.k to me)

  • June 1, 2009 at 4:03 pm

    #1003081

    i will take alook at this. I apprecaite your help.

Viewing 9 posts - 1 through 8 (of 8 total)

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