December 8, 2011 at 1:36 pm
Gang,
I'm writing some C# code to extract FILESTREAM data back out of the datastore and I am encountering a strange error message:
The current transaction cannot be committed and cannot support operations that write to the log file.
When I examined the details further, I see that the procedure it flagged is named usp_Check_User_Permission which is an sp that I wrote to implement detailed permissioning masks. The actual sp is probably too large to show, but here is a snippet of code in which I select the bit-masks for the various object type.
SELECT
RoleName,
CASE @obj
WHEN 'incidents'THEN p.incident_mask
WHEN 'assets'THEN p.asset_mask
WHEN 'structures'THEN p.structure_mask
WHEN 'phones'THEN p.phone_mask
WHEN 'bulletins'THEN p.bulletin_mask
WHEN 'cameras'THEN p.camera_mask
WHEN 'files'THEN p.file_mask
WHEN 'personnel'THEN p.personnel_mask
WHEN 'alerts'THEN p.alert_mask
WHEN 'users'THEN p.user_mgmt_mask
WHEN 'groups'THEN p.group_mgmt_mask
WHEN 'roles'THEN p.role_mgmt_mask
WHEN 'fences'THEN p.fence_mask
WHEN 'roadblocks'THEN p.roadblock_mask
END AS mask
INTO #myTemp
FROM dbo.cc_role_permission AS p
INNER JOIN dbo.aspnet_Roles AS rON r.RoleId= p.role_id
INNER JOIN dbo.group_role_xref AS grxON grx.role_id= r.RoleId
INNER JOIN dbo.cc_groups AS gON g.group_id= grx.group_id
INNER JOIN dbo.user_group_xref AS ugxON ugx.group_id = g.group_id
INNER JOIN dbo.aspnet_Users AS uON u.UserId= ugx.user_id
WHERE u.UserName = @username
DECLARE @comboMask SMALLINT = 0;
SET @comboMask = ( SELECT
SUM( DISTINCT( mask & 0x00000001)) +
SUM( DISTINCT( mask & 0x00000002)) +
SUM( DISTINCT( mask & 0x00000004)) +
SUM( DISTINCT( mask & 0x00000008)) +
SUM( DISTINCT( mask & 0x00000010)) +
SUM( DISTINCT( mask & 0x00000020)) +
SUM( DISTINCT( mask & 0x00000040)) +
SUM( DISTINCT( mask & 0x00000080)) +
SUM( DISTINCT( mask & 0x00000100)) +
SUM( DISTINCT( mask & 0x00000200))
FROM #myTemp
)
PRINT 'comboMask value is: ' + CAST( @comboMask AS NVARCHAR(20))
I do the select (expecting multiple permission masks to be returned) all in order to the SUM( ) operations on each of the bits individually. The end result is a composite permission bit-mask with which to test against. Every stored procedure that inserts, updates, deletes, associates objects within the database always test against the supplied @username of the caller to ensure that they have permissions to do whatever they are trying to do.
...so, the original error manifested itself when I authored some .NET C# code to extract a FILESTREAM file. If I comment out the internal execution of this code from the stored procedure which performs the look-up, then it works fine. Is there some restriction about calling stored procedures that create #temp tables?
December 9, 2011 at 7:23 am
Where is the C# code running? is it an external application or some CLR code from inside the server? You were not clear on that and where it is coming from will have an impact on what you can and can't do.
Having said that, I would probably opt for using a table variable instead of a temp table for building the composite permission mask.
The probability of survival is inversely proportional to the angle of arrival.
December 9, 2011 at 7:43 am
Yep...I'm looking into that right now. I haven't created a table variable before, so I've got to do some homework first.
...and the code runs on the SQL Server but is being called by .NET code running on a desktop (in some cases) and by a web-service running on one of our web servers. In this particular case, however, it was being invoked from my desktop in a Visual Studio debug session.
The first query isn't that complex and it only deals with a single column of smallint values. It seems like it ought to be able to be able to be pasted right in place of the FROM #myTemp statement of the second query avoiding the #temp table completely, but I can't get the syntax right.
Thanks for the tip...please share any knowledge you have about the table variable jazz...that one is new to me.
Larry
December 9, 2011 at 7:53 am
you basically declare the table variable like this:
declare @PermMask table ( RoleName varchar(20), mask int)
then your code would be like:
insert @PermMask
SELECT
RoleName,
CASE @obj
WHEN 'incidents' THEN p.incident_mask
WHEN 'assets' THEN p.asset_mask
WHEN 'structures' THEN p.structure_mask
WHEN 'phones' THEN p.phone_mask
WHEN 'bulletins' THEN p.bulletin_mask
WHEN 'cameras' THEN p.camera_mask
WHEN 'files' THEN p.file_mask
WHEN 'personnel' THEN p.personnel_mask
WHEN 'alerts' THEN p.alert_mask
WHEN 'users' THEN p.user_mgmt_mask
WHEN 'groups' THEN p.group_mgmt_mask
WHEN 'roles' THEN p.role_mgmt_mask
WHEN 'fences' THEN p.fence_mask
WHEN 'roadblocks' THEN p.roadblock_mask
END AS mask
FROM blah blah...
The probability of survival is inversely proportional to the angle of arrival.
December 12, 2011 at 6:30 am
Many thanks for the tips and pointers STurner!
Defining the #temp table as a table variable instead did the trick!
Here is the snippet of modified T-SQL code from my Check_User_Permission( ) procedure:
DECLARE @myTemp TABLE ( mask INT );
INSERT @myTemp
( mask )
(SELECT
CASE @obj
WHEN 'incidents'THEN p.incident_mask
WHEN 'assets'THEN p.asset_mask
WHEN 'structures'THEN p.structure_mask
WHEN 'phones'THEN p.phone_mask
WHEN 'bulletins'THEN p.bulletin_mask
WHEN 'cameras'THEN p.camera_mask
WHEN 'files'THEN p.file_mask
WHEN 'personnel'THEN p.personnel_mask
WHEN 'alerts'THEN p.alert_mask
WHEN 'users'THEN p.user_mgmt_mask
WHEN 'groups'THEN p.group_mgmt_mask
WHEN 'roles'THEN p.role_mgmt_mask
WHEN 'fences'THEN p.fence_mask
WHEN 'roadblocks'THEN p.roadblock_mask
END AS mask
FROM dbo.cc_role_permission AS p
INNER JOIN dbo.aspnet_Roles AS rON r.RoleId= p.role_id
INNER JOIN dbo.group_role_xref AS grxON grx.role_id= r.RoleId
INNER JOIN dbo.cc_groupsAS gON g.group_id= grx.group_id
INNER JOIN dbo.user_group_xref AS ugxON ugx.group_id = g.group_id
INNER JOIN dbo.aspnet_Users AS uON u.UserId= ugx.user_id
WHERE u.UserName = @username
)
DECLARE @comboMask SMALLINT = 0;
SET @comboMask = ( SELECT
SUM( DISTINCT( mask ) & 0x00000001) +
SUM( DISTINCT( mask ) & 0x00000002) +
SUM( DISTINCT( mask ) & 0x00000004) +
SUM( DISTINCT( mask ) & 0x00000008) +
SUM( DISTINCT( mask ) & 0x00000010) +
SUM( DISTINCT( mask ) & 0x00000020) +
SUM( DISTINCT( mask ) & 0x00000040) +
SUM( DISTINCT( mask ) & 0x00000080) +
SUM( DISTINCT( mask ) & 0x00000100) +
SUM( DISTINCT( mask ) & 0x00000200)
FROM @myTemp
)
PRINT 'comboMask value is: ' + CAST( @comboMask AS NVARCHAR(20))
The above changes implemented the table variable instead of the #temp table which prevented any transaction log records from being created.
December 12, 2011 at 6:58 am
Glad you got it working! Ho Ho Ho
The probability of survival is inversely proportional to the angle of arrival.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply