|
Group: Forum Members
Posts: 78,
Visits: 246
|
Hi All I have an odd problem that I cannot get past. It's somewhat complex so please bear with me. - I am trying to update a table via a business object which uses only stored procedures (no db_reader/writer)
- I have confirmed that the stored procedure is correctly named in the BO and that all the other properties of the BO are the same as every other BO I'm using.
- I've used SQL Profiler to watch the execution and the problem is not on the update itself but on a SELECT statement that follows directly after.
- I've walked my code and it dies on the bo.Save(true) statement
- There are several events in the same BO but I've confirmed that those all run fine by walking through them w/ breakpoints
- Below are 3 exhibits. The first is the actual error I'm getting, the second is the update sproc and the 3rd is the Select statement.
- Ultimately, I'm looking for where the Select statement is coming from. Since I cannot debug past the .Save() method, I'm not sure where to go from here.
- The @PARAM0=10024 is the same as the ID passed to the update sproc.
1) Error AF.Exceptions.SaveFailedException was caught
IsTransient=False
Message="The save operation to the SYSTEM_ROLE_REQUEST table failed. MicroFour.StrataFrame.Data.DataLayerSavingException: SELECT permission denied on object 'SYSTEM_ROLE_REQUEST', database 'AF_COMMON', schema 'dbo'. ---> System.Data.SqlClient.SqlException: SELECT permission denied on object 'SYSTEM_ROLE_REQUEST', database 'AF_COMMON', schema 'dbo'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at MicroFour.StrataFrame.Data.DbDataSourceItem.GetDataTable(DbCommand Command, OnChangeEventHandler CallBack)
at MicroFour.StrataFrame.Data.SqlDataSourceItem.GetDataTable(DbCommand Command, OnChangeEventHandler CallBack)
at MicroFour.StrataFrame.Data.DbDataSourceItem.GetDataTable(QueryInformation QueryInfo, OnChangeEventHandler CallBack)
at MicroFour.StrataFrame.Data.DataLayer.GetRowFromServer(DataRow LocalRow)
at MicroFour.StrataFrame.Data.DataLayer.HandleConcurrencyException(DataRow LocalRow)
at MicroFour.StrataFrame.Data.SqlDataSourceItem.UpdateRow(QueryInformation QueryInfo, DataRow RowToUpdate, ConcurrencyExceptionHandler ConcurrencyHandler, AddRowErrorHandler RowErrorHandler, Boolean RecreateCommand)
at MicroFour.StrataFrame.Data.DbDataSourceItem.UpdateRow(QueryInformation QueryInfo, DataRow RowToUpdate, ConcurrencyExceptionHandler ConcurrencyHandler, AddRowErrorHandler RowErrorHandler)
at MicroFour.StrataFrame.Data.DataLayer.UpdateDataTableThread(Object ThreadParams)
--- End of inner exception stack trace ---
at MicroFour.StrataFrame.Business.BusinessLayer.Save(Boolean Transactional, String TransactionKey)
at AF.CommonBO.SystemRoleRequest.Save(Boolean IsTransactional, String TransactionKey) in E:\US_AIR_FORCE\AF_Common\SystemRoleRequest.vb:line 82"
Source="GeneralUtility"
StackTrace:
at AF.Utility.ErrHandler.SaveFailedHandler(String TableName, Exception e) in E:\US_AIR_FORCE\AF_Utility\ErrorHandler.vb:line 109
at AF.CommonBO.SystemRoleRequest.Save(Boolean IsTransactional, String TransactionKey) in E:\US_AIR_FORCE\AF_Common\SystemRoleRequest.vb:line 91
at MicroFour.StrataFrame.Business.BusinessLayer.Save(Boolean Transactional)
at AF.CommonBO.SystemRoleRequest.Save(Boolean IsTransactional) in E:\US_AIR_FORCE\AF_Common\SystemRoleRequest.vb:line 54
2)Update Sproc set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[system_role_request_upd]
(
@SYSTEM_ROLE_REQUEST_ID int,
@SYSTEM_ROLE_REQUEST_USER_ID int,
@REQUEST_STATUS_ID int,
@APPLICATION_NAME nvarchar(256),
@ROLE_ACTIVE uniqueidentifier,
@ROLE_REQUESTED uniqueidentifier,
@JDC_INPUT_SOURCE char(2),
@IS_MSG_COMMANDER bit,
@IS_ALTERNATE bit,
@COMMANDER_UNIT_ID int,
@IS_UNIT_REVIEWER_REQUIRED bit,
@UNIT_REVIEWER_EMAIL varchar(50),
@IMAGE_2875 image,
@LAST_USAGE_DT datetime,
@FIRST_ADDED_DT datetime,
@LAST_UPDT_DT datetime,
@LAST_USER_ID nvarchar(50),
@VERSION timestamp OUTPUT
)
AS
-------------------------------------------------
-- Update all fields by primary key (_ID field)
-- Generated using MyGeneration
-- Created 6/13/2008 9:45:38 AM
-- Uses Optimistic Concurrency via TimeStamp
------------------------------------------------- BEGIN SET NOCOUNT OFF
DECLARE @Err int UPDATE dbo.SYSTEM_ROLE_REQUEST
SET
SYSTEM_ROLE_REQUEST_USER_ID = @SYSTEM_ROLE_REQUEST_USER_ID,
REQUEST_STATUS_ID = @REQUEST_STATUS_ID,
APPLICATION_NAME = @APPLICATION_NAME,
ROLE_ACTIVE = @ROLE_ACTIVE,
ROLE_REQUESTED = @ROLE_REQUESTED,
JDC_INPUT_SOURCE = @JDC_INPUT_SOURCE,
IS_MSG_COMMANDER = @IS_MSG_COMMANDER,
IS_ALTERNATE = @IS_ALTERNATE,
COMMANDER_UNIT_ID = @COMMANDER_UNIT_ID,
IS_UNIT_REVIEWER_REQUIRED = @IS_UNIT_REVIEWER_REQUIRED,
UNIT_REVIEWER_EMAIL = @UNIT_REVIEWER_EMAIL,
IMAGE_2875 = @IMAGE_2875,
LAST_USAGE_DT = @LAST_USAGE_DT,
LAST_UPDT_DT = GetDate(),
LAST_USER_ID = @LAST_USER_IDef
WHERE
SYSTEM_ROLE_REQUEST_ID = @SYSTEM_ROLE_REQUEST_ID
AND ((VERSION = @VERSION) OR (@VERSION IS NULL))
SET @Err = @@Error
SELECT @VERSION = VERSION
FROM dbo.SYSTEM_ROLE_REQUEST
WHERE SYSTEM_ROLE_REQUEST_ID = @SYSTEM_ROLE_REQUEST_ID
RETURN @Err
END
3) Select Statement exec sp_executesql N'SELECT [SYSTEM_ROLE_REQUEST_ID], [SYSTEM_ROLE_REQUEST_USER_ID], [REQUEST_STATUS_ID], [APPLICATION_NAME], [ROLE_ACTIVE], [ROLE_REQUESTED], [JDC_INPUT_SOURCE], [IS_MSG_COMMANDER], [IS_ALTERNATE], [COMMANDER_UNIT_ID], [IS_UNIT_REVIEWER_REQUIRED], [UNIT_REVIEWER_EMAIL], [IMAGE_2875], [LAST_USAGE_DT], [FIRST_ADDED_DT], [LAST_UPDT_DT], [LAST_USER_ID], [VERSION] FROM [dbo].[SYSTEM_ROLE_REQUEST] WHERE ((SYSTEM_ROLE_REQUEST_ID = @PARAM0)); ' ,N'@PARAM0 int',@PARAM0=10024
|