By choyt - 7/15/2008
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 |
By choyt - 7/15/2008
As a reality check, I just did a global search through my solution and all of my BO fill methods either useFillByStoredProcedure CommandType.StoredProcedure
There are none of the below anywhere in the app - FillByPrimaryKey
- CommandType.Text
- CommandType.TableDirects
|
By Greg McGuffey - 7/15/2008
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.
|
By choyt - 7/15/2008
Hi GregThanks 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.
|
By choyt - 7/15/2008
Below are my BO properties.
|
By Greg McGuffey - 7/15/2008
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?
|
By choyt - 7/15/2008
Hi GregYea...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.
|
By Greg McGuffey - 7/15/2008
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.
|
By choyt - 7/16/2008
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.
|
By Greg McGuffey - 7/16/2008
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).
|
By choyt - 7/16/2008
Hi GregThis is apparently what the problem was. StrataFrame needs to make one of 2 changes. Either make this property default to FALSE or give you the option to use a stored proc instead of the DAL generating a raw select statement. The way that this is setup is going to cause anyone who uses stored procs for data access (which I firmly believe is the only way it should be done) a problem at some point. I've been using StrataFrame since it's inception and am very suprised I've not run into this before...and a little concerned that it is happending in production and just havn't heard about it yet. Thanks much for your help! Clay
|
By Greg McGuffey - 7/16/2008
Clay,
Glad you figured it out. I'd say that changing a default would be a bad idea...it'd break lots of apps out there 
However, adding a setting (or two) to indicate that a sproc should be used for this function seems like a good idea.
My guess is that you'll need to handle the concurrency event, run that sproc yourself and then provide the appropriate UI for your users, in order to handle concurrency conflicts.
Well, that was fun! 
|
By Trent L. Taylor - 7/17/2008
LOL...glad you got it going! Thanks for helping out, Greg!!!
|
|