none
SQLMetal & ASPNET 2.0 memberships RRS feed

  • Question

  • Found another problem with sqlmeta / aspnet 2.0 membership stored procs.

     

    When using the built-in aspnet 2.0 membership role stuff, it creates a load of stored procs that are being rejected by SQLMetal.. 

     

    Warning : SQM1020: Unable to extract stored procedure 'dbo.aspnet_Membership_Get
    Password' from SqlServer because its result set contains multiple anonymous columns.
    Warning : SQM1014: Unable to extract stored procedure 'dbo.aspnet_Personalizatio
    nAdministration_FindState' from SqlServer. Invalid object name '#PageIndex'.
    Warning : SQM1014: Unable to extract stored procedure 'dbo.aspnet_Profile_GetPro
    files' from SqlServer. Invalid object name '#PageIndexForUsers'.
    Warning : SQM1014: Unable to extract stored procedure 'dbo.aspnet_Setup_RemoveAl
    lRoleMembers' from SqlServer. Invalid object name '#aspnet_RoleMembers'.
    Warning : SQM1014: Unable to extract stored procedure 'dbo.aspnet_Setup_RestoreP
    ermissions' from SqlServer. Invalid object name '#aspnet_Permissions'.

    the #table errors are due to the fact that they stored procs are refering to temporary tables it hasn't created e.g.

     

     CREATE  PROCEDURE [dbo].aspnet_Setup_RestorePermissions
         @name   sysname
     AS
     BEGIN
         DECLARE @object sysname
         DECLARE @protectType char(10)
         DECLARE @action varchar(20)
         DECLARE @grantee sysname 
         DECLARE c1 cursor FORWARD_ONLY FOR
             SELECT Object, ProtectType, [Action], Grantee FROM #aspnet_Permissions where Object = @name
     
         OPEN c1
    ...
     
    etc

     

    Although these specific procs aren't something we need to worry about, there are other procs that we use that we do need to call that have the same problem.

    Monday, March 10, 2008 7:41 PM

Answers

  • Hi Adrain, Thanks for bringing this up. This is currently by design with LinqToSQL.

     

    Here's what happens: Stored procedures can produce arbitrary results (in terms of the kind of data they return). When a stored procedure contains a temp table and LINQ to SQL queries it for metadata information (in order to determine what to write to the dbml file), SQL server is unable to extract that information. This is not surprising, given that sprocs can execute arbitrary SQL. The warnings indicate that SQL Server was unable to determine the shape of the result set returned by the stored procedure. The user needs to provide the mapping for such sprocs.

    The workaround is to either map the sproc by hand (in dbml or code), or write sprocs that do not use temp tables.

    Wednesday, June 4, 2008 6:18 PM