June 5, 2012 at 7:26 am
Hi,
i am finding an issue where in if we take the profiler data and executes in SSMS the SP executes and able to find the output but the same is not getting executed and not getting the output via application (.net).
Here the SP is in DBO schema but the tables have a different schema. Please help me in this regard. Till Now it was working and suddenly it is not.
here is the sample of the SP
ALTER PROC [dbo].[UpsertSummary_ExtendedSectionUsers]
(
@IDBIGINT = NULL,
@ScheduleUserIDBIGINT = NULL,
@SectionIDBIGINT = NULL,
@TotalScoreDECIMAL(18,1) = NULL,
@PercentageDECIMAL(18,1) = NULL,
@QuestionAttemptedBIGINT = NULL,
@CorrectAnswersMade BIGINT = NULL,
@AbilitiesDECIMAL(18,2) = NULL,
@TechnicalDECIMAL(18,2) = NULL
)
--WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF NOT EXISTS (SELECT TOP 1 1 FROM [MySchema].[Summary_TableUser] WHERE [ScheduleUserID]=@ScheduleUserID AND SectionId=@SectionID)
BEGIN
INSERT INTO [MySchema].[Summary_TableUser] ([ScheduleUserID],[SectionID],[TotalScore],[Percentage],[QuestionAttempted],[CorrectAnswersMade],[Abilities],[Technical])
VALUES (@ScheduleUserID,@SectionID,@TotalScore,@Percentage,@QuestionAttempted,@CorrectAnswersMade,@Abilities,@Technical)
END
ELSE
BEGIN
UPDATE [MySchema].[Summary_TableUser]
SET [SectionID]=ISNULL(@SectionID,[SectionID]),[TotalScore]=ISNULL(@TotalScore,[TotalScore]),[Percentage]=ISNULL(@Percentage,[Percentage]),[QuestionAttempted]=ISNULL(@QuestionAttempted,[QuestionAttempted]),[CorrectAnswersMade]=ISNULL(@CorrectAnswersMade,[CorrectAnswersMade]),[Abilities]=ISNULL(@Abilities,[Abilities]),[Technical]=ISNULL(@Technical,[Technical])
WHERE [ScheduleUserID]=@ScheduleUserID
END
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
END TRY
BEGIN CATCH
DECLARE @ErrorDetail AS NVARCHAR(MAX)
EXEC [GenerateErrorHandling] @ErrorDetail
DECLARE @Exception AS NVARCHAR(MAX)
SET @Exception=ERROR_MESSAGE() + ' -> ' + @ErrorDetail
RAISERROR (@Exception, 16, 1)
END CATCH
SET NOCOUNT OFF;
END
June 5, 2012 at 8:40 am
When you run the stored procedure in SSMS do you run it as the same user that attempts to run it from the application?
June 5, 2012 at 3:05 pm
I would trace it with Profiler to see if some error is raised from SQL Server (and possibly ignored by the application).
Have you tried that?
-- Gianluca Sartori
June 5, 2012 at 10:08 pm
Yes its the same user, which i use in application with the SSMS. it doesnt throw any error while profiling the same and execute in SSMS.
June 5, 2012 at 10:10 pm
I would trace it with Profiler to see if some error is raised from SQL Server (and possibly ignored by the application).
Have you tried that?
I have traced with profiler its not showing any error. it easily executes and gives the output. but application is not throwing any error.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply