sql server programming: Using asp.net's aspnet_regsql.exe tool, I created the
tables/procedures/views in sql server 2005 expess edition necessary to
support the .net 2.0 membership system in a website. I'm having an
issue with the Membership.deleteUser function,which calls a stored
procedure. The error I'm getting is a foreign key violation because
before deleting from the Users table, I have to delete from the
Membership table. With that said, I've traced the problem back to a
line in the stored procedure which is:
IF ((@TablesToDeleteFrom & 1) <> 0) AND
((EXISTS (SELECT name FROM sysobjects WHERE (name =
N'vw_aspnet_MembershipUsers') AND (type = 'V'))))

I've found that if I just include the line ' IF ((@TablesToDeleteFrom
& 1) <> 0) ', everything works fine (even though I don't understand the
'&1' statement). That means it's the remaining part of the code that is
causing the problem. Unfortunately I don't know enough about TSQL to
know exactly what that line is doing. I was hoping someone could tell
me what the line:

((EXISTS (SELECT name FROM sysobjects WHERE (name =
N'vw_aspnet_MembershipUsers') AND (type = 'V'))))

is actually doing. It seems that its looking to see if the name (ie
username input param) exists in that view, but that's just a guess and
it seems wrong bc when I manually query the view, I do find the user
with no problem. .

Thanks in advance.

[quoted text, click to view]

The procedure is checking whether the view exists. I can't imagine why.
Perhaps you should ask the authors.


[quoted text, click to view]

You should look up the "&" operator in Books Online:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_operator_7fax.asp

What value are you using for this parameter?


ML

---

IF ((@TablesToDeleteFrom & 1) <> 0)

its a bitwise and. And the result of this expression
@TablesToDeleteFrom & 1

thanks for your help. I think it checks if the view exists because
when you install the membership tables in the database using
microsoft's tool, you have the options of which components/tables to
install (membership, roles, profiles, etc). by checking if the view
exists, you can use one stored procedure to delete the users regardless
of which components/tables you installed. since this procedurewas
created by ms, ill just have to assume this is the answer. with that
said, its not working in my case so i could definitely be wrong. im not
sure what the value of @TablesToDeleteFrom being passed by my web app
is (should be 1), this happens behind the scenes in the
system.web.security.membership class and i dont know how to intercept
the value of the parameter when it hits the database (im very new at
this!).

Thanks.

Better trust aspnet, then. :) I'd also consider installing the whole thing
rather than individual objects, even though installing individual objects
might be a more logical option. Perhaps you could also seek help with the
authors.

IMHO referencing system objects in a user stored procedure could lead to
problems - what if the system objects change? What if the service provider
doesn't allow access to system objects? But these are not questions for you
to answer. :)


ML

---