July 10, 2011 at 7:49 pm
I am inserting data from one table in the source database into a table located on another "target" database (which is on the same server). After the insert completes, I update another table in the source database. I added a user on the target db with datareader and datawriter role permissions for running these scripts. I put the scripts in a stored procedure compiled on the source database. This was working fine at one point. Now all of a sudden the stored procedure doesn't work and I have no idea why. I don't get an error, the stored procedure just doesn't insert the data into the remote database, but does do the update on the table in the local database. What's even more strange is if I take the scripts out of the stored procedure and run it in Management Studio directly, it works (that is the data will get inserted into the remote db). Does anyone have any idea why this script runs successfully when it's not in an SP, but doesn't work when it is in a stored procedure?
w/o stored procedure (runs fine in management studio)
DECLARE @SurveyID int = 32
DECLARE @FacilityID int = 5349
DECLARE @SurveyCode varchar(24) = 'LandlordEngagement'
DECLARE @SurveyKey varchar(1024) = 'F281D1EE-1F90-4AEC-A4AE-A833FE6BD677'
IF @SurveyCode = 'LandlordEngagement'
BEGIN
INSERT INTO [DB-SurveyPortal].[dbo].[LandlordEngagementSurvey]
(SurveyID, FacilityID, SurveyKey, BuildingName,
BuildingAddress, City, [State], Zip)
SELECT
@SurveyID, @FacilityID, @SurveyKey,
f.FacilityName, f.[Address], f.City, f.[State], f.Zip
FROM Facility f
WHERE f.FacilityID = @FacilityID
AND NOT EXISTS (
SELECT 1
FROM [DB-SurveyPortal].[dbo].[LandlordEngagementSurvey] l
WHERE l.SurveyKey = @SurveyKey
)
END
ELSE IF @SurveyCode = 'LandlordVerification'
BEGIN
INSERT INTO [DB-SurveyPortal].[dbo].[LandlordVerificationSurvey]
(SurveyID, FacilityID, SurveyKey, BuildingName,
BuildingAddress, City, [State], Zip)
SELECT
@SurveyID, @FacilityID, @SurveyKey,
f.FacilityName, f.[Address], f.City, f.[State], f.Zip
FROM Facility f
WHERE f.FacilityID = @FacilityID
AND NOT EXISTS (
SELECT 1
FROM [DB-SurveyPortal].[dbo].[LandlordVerificationSurvey] l
WHERE l.SurveyKey = @SurveyKey
)
END
--SET STATUS OF LOCAL SURVEY TABLE
UPDATE SustainabilitySurvey
SET SurveyStatus = 'In Progress'
WHERE SustainabilitySurveyID = @SurveyID
Stored Procedure
CREATE PROCEDURE sp_UpdateSurveyPortal
@SurveyID int, @FacilityID int, @SurveyKey varchar(1024), @SurveyCode varchar(32)
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
IF @SurveyCode = 'LandlordEngagement'
BEGIN
INSERT INTO [DB-SurveyPortal].[dbo].[LandlordEngagementSurvey]
(SurveyID, FacilityID, SurveyKey, BuildingName,
BuildingAddress, City, [State], Zip)
SELECT
@SurveyID, @FacilityID, @SurveyKey,
f.FacilityName, f.[Address], f.City, f.[State], f.Zip
FROM Facility f
WHERE f.FacilityID = @FacilityID
AND NOT EXISTS (
SELECT 1
FROM [DB-SurveyPortal].[dbo].[LandlordEngagementSurvey] l
WHERE l.SurveyKey = @SurveyKey
)
END
ELSE IF @SurveyCode = 'LandlordVerification'
BEGIN
INSERT INTO [DB-SurveyPortal].[dbo].[LandlordVerificationSurvey]
(SurveyID, FacilityID, SurveyKey, BuildingName,
BuildingAddress, City, [State], Zip)
SELECT
@SurveyID, @FacilityID, @SurveyKey,
f.FacilityName, f.[Address], f.City, f.[State], f.Zip
FROM Facility f
WHERE f.FacilityID = @FacilityID
AND NOT EXISTS (
SELECT 1
FROM [DB-SurveyPortal].[dbo].[LandlordVerificationSurvey] l
WHERE l.SurveyKey = @SurveyKey
)
END
--SET STATUS OF LOCAL SURVEY TABLE
UPDATE SustainabilitySurvey
SET SurveyStatus = 'In Progress'
WHERE SustainabilitySurveyID = @SurveyID
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK
DECLARE @ErrMsg varchar(MAX), @ErrSeverity INT
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END
END CATCH
END
GO
July 11, 2011 at 5:06 am
How sure are you that parameter @SurveyCode contains the correct value when calling the SP?
July 11, 2011 at 9:21 am
Can you put some debugging messages in the SP to verify you are hitting all the expected parts. You can use RAISERROR with a severity below 11 (informational only) to verify the expected parameters have been passed in and the different steps have been hit.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy