May 6, 2010 at 6:23 pm
I have one controller stored procedure that calls a dozen others, to map a text file to our database.
Each of the dozen procedures in the second layer calls a logging procedure.
So it's 3 layers of nesting, no transactions at the outer level.
The process works fine in Dev.
But when I migrated the code to QA, the result of calling the outer procedure was for it to have the outer procedure dropped with no error, and the logging procedure to be dropped, with error 2812: the system could not find the stored procedure.
If I call one of the second layer procedures directly, it works, and the logging procedure completes without error.
If I move the logging procedure call to the middle layer, it also works.
Dropping and recreating the procedures does not fix the problem. DBCC CHECKDB shows no problem, the SQL log shows no errors.
DEV and QA are originally identical environments: Windows 2003 server, SP2; and SQL Server 2005, SP2.
Has anyone seen this behavior? Do we need to upgrade to SP4 for SQL?
I am thinking of creating a new database, scripting all the objects from QA into it, and pulling over the data. Is this a good solution? We don't have a recent backup, but everything is in VSS.
Thanks for any suggestions.
CREATE PROCEDURE [dbo].[PS_SP_ImportPostalCodes]
AS
BEGIN
DECLARE @ErrorMsg nvarchar(500),
@Result int,
@GeographicalImportID int,
@UserName nvarchar(30),
@Status char(1)
--SET XACT_ABORT ON
SET NOCOUNT ON
BEGIN TRY
SET @UserName = 'POSTAL CODE IMPORT'
SET @Result = 0
SET @ErrorMsg=''
-- Delete city aliases not acceptable to post office
DELETE FROM PS_Stage_PostalCode WHERE citytype='O'
-- put record into import log table
EXEC @Result = dbo.PS_SP_InsertGeographicalImport
@Code = 'POSTAL',
@UserName = @UserName,
@GeographicalImportID = @GeographicalImportID OUTPUT
DECLARE @StepName nvarchar(50)
----------------------------------------
--- if log procedure code is here, it works ----------
SET @StepName = 'PS_SP_UpdateStagePostalCodeRegionID'
EXEC @Result= dbo.PS_SP_InsertGeographicalImportDetail
@GeographicalImportID = @GeographicalImportID,
@StepName = @StepName,
@Status = 'S',
@UserName = @UserName
-------------------------------------
--- If the above procedure is nested in this procedure, it fails
--- and deletes PS_SP_ImportPostalCodes as well as
--- PS_SP_UpdateStagePostalCodeRegionID
-- update regionID in PS_Stage_PostalCode
If @Result = 0
BEGIN
EXEC @Result = dbo.PS_SP_UpdateStagePostalCodeRegionID
@GeographicalImportID = @GeographicalImportID,
@UserName = @UserName,
@ErrorMsg = @ErrorMsg OUTPUT
END
-- and several more calls, followed by a catch
END
CREATE PROCEDURE [dbo].[PS_SP_UpdateStagePostalCodeRegionID]
@GeographicalImportID int,
@UserName nvarchar(30),
@ErrorMsg nvarchar(500) OUTPUT
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @StepName nvarchar(50)
BEGIN TRY
SET @StepName = 'PS_SP_UpdateStagePostalCodeRegionID'
UPDATE PS_Stage_PostalCode SET
RegionID = r.RegionID
FROM PS_Stage_PostalCode spc
INNER JOIN PS_Country c ON
spc.CountryCode=c.CountryCode_2
INNER JOIN PS_Region r ON
c.CountryID=r.CountryID AND
r.RegionCode = spc.RegionCode
WHERE
c.IsActive = 'Y'
---- this code causes the procedures to be deleted
-- EXEC dbo.PS_SP_InsertGeographicalImportDetail
-- @GeographicalImportID = @GeographicalImportID,
-- @StepName = @StepName,
-- @Status = 'S',
-- @UserName = @UserName
RETURN 0
END TRY
BEGIN CATCH
DECLARE @Error int
IF XACT_STATE() <> 0 ROLLBACK TRAN
SELECT @Error = ERROR_NUMBER(),
@ErrorMsg = 'IN ' + ERROR_PROCEDURE() + ': ' + CAST(ERROR_NUMBER() AS VARCHAR) + ' ' +
ERROR_MESSAGE() + '; at line ' + CAST(ERROR_LINE() AS VARCHAR)
SELECT @Error, @ErrorMsg
EXEC PS_SP_InsertGeographicalImportDetail
@GeographicalImportID = @GeographicalImportID,
@StepName = @StepName,
@Status = 'F',
@UserName = @UserName
RETURN @Error
END CATCH
END
May 6, 2010 at 7:41 pm
I have never seen any such thing happen..
1. Get a current backup
2. Either setup a trace or setup a DDL trigger to see what is actuallybeing issued to the database. Iam having a hard time believing that it is doing it on its own..
CEWII
May 7, 2010 at 1:52 am
Use profiler and run a trace to see where the drop statement is coming from. This is not data corruption.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2010 at 6:02 am
Thank you both.
I made a backup yesterday as soon as this started.
A DDL trigger is a good thought, I will experiment with that today.
I ran one trace yesterday, there was so much "noise" from replication jobs that I couldn't find the drop. I will stop those jobs today and rerun the trace.
May 7, 2010 at 6:32 am
Filter your trace. If you're just interested in an object being dropped, just use the Objects: Object Dropped event
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2010 at 10:36 am
Thank you for the filtering idea.
Trace found the issue.
I had to do the promotion with a single text file for all the procedures. I put the drop followed by the create for each procedure. In two cases, I had not manually added a GO after the procedure END, and it kept the following DROP statement.:blush:
I'm changing the script to put all drops first, then all creates. If I miss a GO between the end of one and the start of the next, it makes a syntax error.
May 7, 2010 at 10:58 am
Super, I figured it wasn't corruption..
CEWII
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply