Help w/ TSQL Code in Stored Procedure

2 views
Skip to first unread message

andre...@gmail.com

unread,
Apr 12, 2006, 2:17:38 AM4/12/06
to
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.

ML

unread,
Apr 12, 2006, 4:01:02 AM4/12/06
to
> ((EXISTS (SELECT name FROM sysobjects WHERE (name =
> N'vw_aspnet_MembershipUsers') AND (type = 'V'))))

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


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

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

---
http://milambda.blogspot.com/

andre...@gmail.com

unread,
Apr 12, 2006, 11:20:18 PM4/12/06
to
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.

Reply all
Reply to author
Forward
0 new messages