|
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
|
|
Group: Forum Members
Posts: 78,
Visits: 246
|
As a reality check, I just did a global search through my solution and all of my BO fill methods either use FillByStoredProcedure CommandType.StoredProcedure
There are none of the below anywhere in the app - FillByPrimaryKey
- CommandType.Text
- CommandType.TableDirects
|
|
Group: Forum Members
Posts: 2K,
Visits: 6.6K
|
The first thing I'd check is that the db user used by your BO has table Select privileges on SYSTEM_ROLE_REQUEST. Do this in SQL Server Management Studio. That is what the SQL error is telling you. You can also verify by running a query as this user and attempt a select on the table.
|
|
Group: Forum Members
Posts: 78,
Visits: 246
|
Hi Greg Thanks for the reply. I do everything under a role named db_executor which I grant EXECUTE to all sprocs but only that. The user SprocUser is a member of that role and nothing else. I've logged into management console using SprocUser and can run the sproc without a problem. Its the SELECT statement that causes it to puke. When I grant db_reader to the SprocUser, it works...but that is not going to fly long term. I have 40 or so BOs in this project and all of the rest work fine.
|
|
Group: Forum Members
Posts: 78,
Visits: 246
|
Below are my BO properties. 
|
|
Group: Forum Members
Posts: 2K,
Visits: 6.6K
|
Hmmm...how do you load the BO? Via sprocs again? And they work fine?
Also, (although it's likely you've already checked) the BO is using the same data source key as the other BOs? You haven't done something funky with the data source have you? Use a different user?
Do your other sprocs work OK? The insert and delete?
|
|
Group: Forum Members
Posts: 78,
Visits: 246
|
Hi Greg Yea...the key is the same accross everything. It was one of the first things I checked. I've not checked inserts and deletes b/c they never happen in "real life". With that said, I could rig something to try it. All data access is performed via sproc. I purposely develop using a SQL User that has only EXECUTE rights on the sprocs to prevent these types of problems going into production. The problem still comes down to the SELECT statement shown above. I can absolutely guarantee you that I didn't write it. I would never have named a parameter "@PARAM0=10024" My parameters are all either named for the Field name (@FIRST_NAME) or using a convention (@aiMyParam). I did do a global search of the solution to be sure and it did come up empty. This has to be coming from somewhere in the StrataFrame DAL.
|
|
Group: Forum Members
Posts: 2K,
Visits: 6.6K
|
So, were did that SQL statement come from? It looks like something from SQL Server Management Studio, not from the app (exec sp_executesql being the big hint on that one  ). Is the SQL from debugging the data source? It looks like DAL generated SQL that would update a single record. I seem to recall that SF does this automatically after a save to make sure the it has the latest values off the server, but not sure. I just spent some time looking at the DAL code and I don't see were that SQL could come from. There is one thing that I can see might cause this to occur: you are having a concurrency issue. In this case, I'm pretty sure the DAL would generate the indicated SQL to get the server values. To test turn off the GetServerValuesOnConcurrencyException. I'm not sure if this affects things when it is raising events.
|
|
Group: Forum Members
Posts: 78,
Visits: 246
|
The SQL Statement was part ofthe trace I ran. Just prior to hitting the .Save(), I started a trace. There were 3 lines in the trace...the 3rd of which died. - The update stored procedure which ran fine w/o failure
- An sp_reset_connection which ran fine w/o failure
- The Select statement.
I have absolutely no idea where the Select came from and worse...no idea how to get rid of it.
|
|
Group: Forum Members
Posts: 2K,
Visits: 6.6K
|
What about concurrency? Did you try turning off the GetServerValuesOnConcurrencyException property on the BO? That is the only thing I can think of (and I'm using both of my brain cells).
|