Says invalid object name after renaming proc..

  • I have a proc in my sql server 2005 db which was accidentally renamed by pressing F2 on sql object browser.

    Now, when i execute the proc, it says "invalid object name".. i tried all the ways like renaming it back to original name , but that does not help...

    I can see the proc in object browser, i can alter it, i can dropa dn recreate it . but i cannot execute it.

    Since my business demands the name of the proc, i should use the original name...am in a fix,...can any one help?

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • Is it the same owner as the orignal?

    Do you have the same name / owner as you have in the proc code?

  • yes ..dbo is the owner

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • Can you post the sp code here and how you are calling it?

  • /****** Object: StoredProcedure [dbo].[sproc_NR_EWMDupVIN] Script Date: 03/02/2011 12:03:00 ******/

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    Create PROCEDURE [dbo].[sproc_NR_EWMDupVIN]

    AS

    BEGIN

    declare @AlertMasterID int

    declare @ProcessingDate datetime

    declare @DealerID int

    declare @vin nchar(20)

    declare @DupFinancedDate datetime

    declare @NoOfDays int

    declare @DealerIGCode int

    declare @ReviewThreshold int

    declare @ActionThreshold int

    declare @MonitoringStatusID int

    declare @DefaultMonitoringStatus int

    -- Get the processing date

    EXEC [sproc_GetTodayDate] @ProcessingDate out

    set @AlertMasterID = 1-- AlertMasterCode for Duplicate VIN alert REF: EWMAlertMaster

    set @DefaultMonitoringStatus = 0

    -- Log the starting of this procedure

    EXECUTE sproc_EWMLog 'sproc_NR_EWMDuplicateVIN','TRACE', 0, 0, 0, 0, 'Starting the stored proc sproc_NR_EWMDuplicateVIN'

    -- COLLECT THE ROWS for DUPLICATE VINs into the output table - this will combine the rows from Floorign and Leasing tables into one table and then we process the review and action thresholds for various IG Codes.

    ----- COLLECT THE VINS FOR FLOORING ACCOUNTS

    INSERT INTO [EWMAlertOutput]

    ([fk_AlertMasterID],[fk_DealerID],[fk_MonitoringStatusID],[Data1],[Data2],[Data3],[Data4],[Data5],[Data6],

    [IncidenceID],[ProcessingDate],[TimeStamp],[SortKey])

    (SELECT @AlertMAsterID, dbo.sproc_NR_EWMDuplicateVIN.fk_DealerID, @DefaultMonitoringStatus, dbo.sproc_NR_EWMDuplicateVIN.VIN,

    dbo.Dealer.DealershipName AS SecDealershipName, rtrim(dbo.FlooringAccount.Transit + ' ' + dbo.FlooringAccount.AccountNumber) as TransitAccount,

    upper(dbo.Inventory.Year + ' ' + dbo.Inventory.Make + ' ' + dbo.Inventory.Model ) AS YearMakeModel,

    dbo.Inventory.CurrBal, dbo.Inventory.FinancedDate, dbo.sproc_NR_EWMDuplicateVIN.VIN as IncidenceID, @ProcessingDate, getdate(),

    rtrim(dbo.sproc_NR_EWMDuplicateVIN.VIN) + rtrim(dbo.Dealer.DealershipName) as SortKey

    FROM dbo.Dealer INNER JOIN

    dbo.FlooringAccount INNER JOIN

    dbo.Inventory ON dbo.FlooringAccount.AccountNumber = dbo.Inventory.AccountNumber AND dbo.FlooringAccount.Transit = dbo.Inventory.Transit ON

    dbo.Dealer.pk_DealerID = dbo.FlooringAccount.fk_DealerID INNER JOIN

    dbo.sproc_NR_EWMDuplicateVIN ON dbo.Inventory.VIN = dbo.sproc_NR_EWMDuplicateVIN.VIN

    WHERE (dbo.Inventory.PaidUnit = '0'))

    ----- COLLECT THE VINS FOR LEASING ACCOUNTS

    INSERT INTO [EWMAlertOutput]

    ([fk_AlertMasterID],[fk_DealerID],[fk_MonitoringStatusID],[Data1],[Data2],[Data3],[Data4],[Data5],[Data6],

    [IncidenceID],[ProcessingDate],[TimeStamp],[SortKey])

    (SELECT @AlertMAsterID, dbo.sproc_NR_EWMDuplicateVIN.fk_DealerID, @DefaultMonitoringStatus, dbo.sproc_NR_EWMDuplicateVIN.VIN,

    dbo.Dealer.DealershipName AS SecDealershipName, rtrim(dbo.LeasingAccount.Transit + ' ' + dbo.LeasingAccount.AccountNumber) as TransitAccount,

    upper(dbo.Inventory.Year + ' ' + dbo.Inventory.Make + ' ' + dbo.Inventory.Model ) AS YearMakeModel,

    dbo.Inventory.CurrBal, dbo.Inventory.FinancedDate, dbo.sproc_NR_EWMDuplicateVIN.VIN as IncidenceID, @ProcessingDate, getdate(),

    rtrim(dbo.sproc_NR_EWMDuplicateVIN.VIN) + rtrim(dbo.Dealer.DealershipName) as SortKey

    FROM dbo.Dealer INNER JOIN

    dbo.LeasingAccount INNER JOIN

    dbo.Inventory ON dbo.LeasingAccount.AccountNumber = dbo.Inventory.AccountNumber AND dbo.LeasingAccount.Transit = dbo.Inventory.Transit ON

    dbo.Dealer.pk_DealerID = dbo.LeasingAccount.fk_DealerID INNER JOIN

    dbo.sproc_NR_EWMDuplicateVIN ON dbo.Inventory.VIN = dbo.sproc_NR_EWMDuplicateVIN.VIN

    WHERE (dbo.Inventory.PaidUnit = '0'))

    -- SO now we have some rows to work with

    --** Now open the Cursor on the collected rows and process other fields, mainly being the "Monitoring Status", which is cross caluculations of Review / Action threshold parameters, different for different IG Codes of the dealer

    BEGIN TRY-- wrap it in try/catch - just in case

    DECLARE cDuplicateVINS CURSOR FOR SELECTAOut.fk_DealerID, AOut.Data1, dbo.L_IGCode.Value

    FROM dbo.EWMAlertOutput AS AOut INNER JOIN

    dbo.Dealer ON AOut.fk_DealerID = dbo.Dealer.pk_DealerID INNER JOIN

    dbo.L_IGCode ON dbo.Dealer.fk_IGCode = dbo.L_IGCode.pk_IGCode

    WHERE (AOut.fk_AlertMasterID = 1)

    OPEN cDuplicateVINS

    FETCH NEXT FROM cDuplicateVINS

    INTO @DealerID, @vin, @DealerIGCode

    -- Start the loop to process the rows

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- Get the First Financed Date for this VIN number when the VIN became Duplicate - The dup VINs could be more than twice

    set @DupFinancedDate = (select top 1 FinancedDate from (Select top 2 FinancedDate from Inventory where VIN = @vin order by FinancedDate DESC)

    as pseudoDupVinDatesTable order by FinancedDate ASC)

    -- THE KEY MEASUREMENT FOR THIS ALERT is 'NUMBER OF DAYS'

    set @NoOfDays = DATEDIFF(day, @DupFinancedDate, @ProcessingDate)

    -- Get the review and action threshold for this Dealer's IG Code (and for this alert type)

    select @ReviewThreshold = cast(ReviewThreshold as int), @ActionThreshold = cast(ActionThreshold as int)

    from EWMAlertParams

    where ((IGCodeSplit > @DealerIGCode and IsBelowIGCodeSplit = 1) OR (IGCodeSplit <= @DealerIGCode and IsBelowIGCodeSplit = 0))

    and fk_AlertMasterID = @AlertMasterID

    -- now we can determine if this is in Review stage or Action required stage

    if @NoOfDays >= @ReviewThreshold and @NoOfDays < @ActionThreshold

    BEGIN

    set @MonitoringStatusID = 1 -- REVIEW lookup code REF: l_MonitoringStatus table

    END

    else if @NoOfDays >= @ActionThreshold

    BEGIN

    set @MonitoringStatusID = 2 -- ACTION lookup code REF: l_MonitoringStatus table

    END

    else

    BEGIN

    set @MonitoringStatusID = 0 -- NONE

    END

    -- Set the Monitoring Status

    update dbo.EWMAlertOutput set fk_MonitoringStatusID = @MonitoringStatusID

    where fk_DealerID = @DealerID and fk_AlertMasterID = @AlertMasterID and Data1 = @vin

    -- Diagnostic version of the update

    --update dbo.EWMAlertOutput set fk_MonitoringStatusID = @MonitoringStatusID , data5 = cast(@DealerIGCode as nchar(20)), data6 = cast(@NoOfDays as nchar(10)), data7 = cast(@DupFinancedDate as nchar(20)), data8 = cast(@ProcessingDate as nchar(20)), data9 = cast(@ReviewThreshold as nchar(20)), data10 = cast(@ActionThreshold as nchar(20))

    --where fk_DealerID = @DealerID and fk_AlertMasterID = @AlertMasterID and Data1 = @vin

    -- GOTO NEXT RECORD

    FETCH NEXT FROM cDuplicateVINS

    INTO @DealerID, @vin, @DealerIGCode

    END

    -- CLEANUP

    -- Delete the rows that have no monitoring status -- those that are below the review threshold value

    delete from dbo.EWMAlertOutput where fk_MonitoringStatusID = 0

    -- FREE THE CURSOR RESOURCES

    CLOSE cDuplicateVINS

    DEALLOCATE cDuplicateVINS

    END TRY

    BEGIN CATCH -- Exception detected

    -- Log the error

    EXECUTE sproc_EWMLog 'sproc_NR_EWMDuplicateVIN',

    'ERROR', Error_Number,

    Error_Severity, Error_State,

    Error_Message, 'Error occured while executing the stored proc sproc_NR_EWMDuplicateVIN'

    END CATCH;

    -- Log the completion of this procedure

    EXECUTE sproc_EWMLog 'sproc_NR_EWMDuplicateVIN','TRACE', 0, 0, 0, 0, 'Completed the stored proc sproc_NR_EWMDuplicateVIN'

    END

    and executing like

    exec sproc_NR_EWMDupVIN

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • Have you tried exec dbo.sproc_NR_EWMDupVIN params.......?

  • I have tried executing

    exec dbo.sproc_NR_EWMDupVIN...

    it says the same error..

    Invalid object name 'dbo.sproc_NR_EWMDupVIN'.

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • Save a copy of the script. Then try manually deleting the proc and then recreate it. If it still doesn't work then something else has been changed in the proc itself...

  • I have saved copy of the script.

    I didnot understand manual deleting part. i have tried dropping and recreating , which does not worked.

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • Close ssms, reopen.

    Go to the db, procedures, select THAT procedure then delete.

    Then use the script to recreate it.

  • Nope..It did not work this way too...:(

    Still the same error..

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • Just curious, have you tried stopping and restarting the SQL Server Service, and/or rebooting the server? Smells like a glitch, so I'd start with doing standard glitch recovery protocols. It's Windows... when in doubt, reboot.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Craig Farrell (3/2/2011)


    Just curious, have you tried stopping and restarting the SQL Server Service, and/or rebooting the server? Smells like a glitch, so I'd start with doing standard glitch recovery protocols. It's Windows... when in doubt, reboot.

    That was my last resort...

    I would have considered restoring from the last valid backup to a test server then moving the code back into the problem db.

    If that doesn't do it then we either missed something real obvious or the "magical" reboot might do it.

  • Actually I would clear the proc cache for that db before doing a reboot... less drastic and might work.

  • Ninja..I tried clearing proc cache and that does not help..I might reboot in some time from now..

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

Viewing 15 posts - 1 through 15 (of 25 total)

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