I'm working on an ASP.NET Web Forms application with SqlMembershipProvider. As it is running on Azure too, I use KB2006191 Updated ASP.NET scripts for use with SQL Azure for tables and SPs.
Apparently, they do not work smoothly with a standalone SQL Server 2008 R2 SP1. Especially dbo.aspnet_Users_DeleteUser fires an internal error:
Msg 8624, Level 16, State 1, Procedure aspnet_Users_DeleteUser, Line 111 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.
Edit:
I broke down the issue to one SQL statement which actually fails (even if I hard-code a value for @UserId):
DELETE FROM dbo.aspnet_Users WHERE UserId = @UserId
Original:
I tried fixing my SQL Server installation by downloading a Cumulative Update Package and setting trace flag 4199. However, this did not help. To be honest, I'm not too sure whether I applied the patch properly. I'm doing further research on this.
Moreover, I tried to rewrite the SP a little, as I found some tips on the web. This did not help either, and the same query is generated by aspnet_regsql for standalone SQL Server anyway. Actually, I tried to get rid of a subquery around line 111 by turning this:
IF ((@TablesToDeleteFrom & 1) <> 0 AND
(@TablesToDeleteFrom & 2) <> 0 AND
(@TablesToDeleteFrom & 4) <> 0 AND
(@TablesToDeleteFrom & 8) <> 0 AND
(EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId)))
... into this:
IF ((@TablesToDeleteFrom & 15) <> 0)
Did anybody come across similar issues dealing with membership and both Azure and standalone SQL Server?
According to Scott Hanselman and this blog post, the ASP.NET Universal Providers For SqlExpress 1.1 package (System.Web.Providers) is the officially supported method of enabling the SQL membership, role and session providers across SQL Azure and standalone SQL Server.