March 2, 2011 at 10:20 am
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?
March 2, 2011 at 10:24 am
Is it the same owner as the orignal?
Do you have the same name / owner as you have in the proc code?
March 2, 2011 at 10:25 am
March 2, 2011 at 10:28 am
Can you post the sp code here and how you are calling it?
March 2, 2011 at 10:29 am
/****** 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
March 2, 2011 at 10:33 am
Have you tried exec dbo.sproc_NR_EWMDupVIN params.......?
March 2, 2011 at 10:40 am
I have tried executing
exec dbo.sproc_NR_EWMDupVIN...
it says the same error..
Invalid object name 'dbo.sproc_NR_EWMDupVIN'.
March 2, 2011 at 10:44 am
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...
March 2, 2011 at 11:01 am
I have saved copy of the script.
I didnot understand manual deleting part. i have tried dropping and recreating , which does not worked.
March 2, 2011 at 11:03 am
Close ssms, reopen.
Go to the db, procedures, select THAT procedure then delete.
Then use the script to recreate it.
March 2, 2011 at 11:09 am
Nope..It did not work this way too...:(
Still the same error..
March 2, 2011 at 11:13 am
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.
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
March 2, 2011 at 11:22 am
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.
March 2, 2011 at 11:23 am
Actually I would clear the proc cache for that db before doing a reboot... less drastic and might work.
March 2, 2011 at 11:31 am
Ninja..I tried clearing proc cache and that does not help..I might reboot in some time from now..
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply