Insert data into a table in one database from a table in another database. PLEASE HELP!

  • 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

  • How sure are you that parameter @SurveyCode contains the correct value when calling the SP?

  • 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.

Viewing 3 posts - 1 through 3 (of 3 total)

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