Assertion and 823 errors!

Hi,
We have been getting a lot of 823 errors lately, ran
checkdb and it always comes out with 0 errors.
We ran a detailed disk and hardware diagnostic and could
not come up with any issues.
However, today, we got a failed assertion error :
SQL Server Assertion: File:
<R:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
I isolated this error to an application that is mostly UI
driven (with respect to performing Unions and joins),
since i read in MSKB that this may also be caused by any
subquery with a distinct clause or anything that returns a
row bigger than 8060KB.
I have 3 queries that run, and i checked the execution
plans, the tables are all properly indexed and they seem
to look fine.
we have EMC hardware , sql 2000 on Advanced server
(8.00.818)
Not sure what the next steps should be, Appreciate your
guidance and suggestions.
Thanks,Sounds like a case for MS Support to me (assuming you are up on service pack and have searched KB).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"mark" <anonymous@.discussions.microsoft.com> wrote in message
news:02f701c3b449$acd3a670$a101280a@.phx.gbl...
> Hi,
> We have been getting a lot of 823 errors lately, ran
> checkdb and it always comes out with 0 errors.
> We ran a detailed disk and hardware diagnostic and could
> not come up with any issues.
> However, today, we got a failed assertion error :
> SQL Server Assertion: File:
> <R:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <=> MAXDATAROW'.
> I isolated this error to an application that is mostly UI
> driven (with respect to performing Unions and joins),
> since i read in MSKB that this may also be caused by any
> subquery with a distinct clause or anything that returns a
> row bigger than 8060KB.
> I have 3 queries that run, and i checked the execution
> plans, the tables are all properly indexed and they seem
> to look fine.
> we have EMC hardware , sql 2000 on Advanced server
> (8.00.818)
> Not sure what the next steps should be, Appreciate your
> guidance and suggestions.
> Thanks,
>

ASP.NET SQL Server Registration Tool (Aspnet_regsql.exe)

I just ran the utility without any commands ant the wizard has come up. This is all independent of my web application.

I am guessing this is going to install the database tables to my existing database.UPDATE: They have been created

Will I then connect my web app to this database schema that is created?

How do I change the membership/roles users in my web app to point to the database tables?

To connect you app to the membership database you define the membership provider in web.config
main setting are connectionstring and application name.

Here a link with more info about the settings for you

http://weblogs.asp.net/scottgu/archive/2006/04/22/Always-set-the-_2200_applicationName_2200_-property-when-configuring-ASP.NET-2.0-Membership-and-other-Providers.aspx

To manage the membership role and user you can use ASP.NET Configuration tool you will
find it on the website menu in visual web developer.

Hope that helps you.

|||

Weird, there is only 1 thing in my applications web config that references the roles and memberships..

<roleManager enabled="true" />

What should I be doing to connect the current roles and membership to the new SQL Server 2000 tables that the utility created?
In the ASP.NET Configuration tool, there is a provider tab, in this area there is a AspNetSqlProvider and a radio button, there is also a test link.

When I click the test link it eventually fails, even though I have created all the necessary tables in the 2000 database.

|||

Ok you will need to setup a connection string to you database and define the membership provider.

Here is example for you

<connectionStrings><remove name="LocalSqlServer"/><!-- Connection for SQL 2000 --><add name="LocalSqlServer" connectionString="Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;" providerName="System.Data.SqlClient"/></connectionStrings> <membership> <providers> <clear/> <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="LocalSqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true"requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" applicationName="/" /> </providers></membership>

You then can setup the users and roles using the asp.net confrigation tool you will find it in the website menu in visual web developer express

I hope this helps

|||

Ok, I will try to get farther. I am suprised none of this was added to the Web.config file as I have a working membership login ability... anyone know why?

Also, for the record, I am using Visual Studio 2005Standard Edition, will that change anything?

What is the situation with <remove name="...">

Are you just clearing the existing pair/key value? Is that needed?

|||

I think the logic around why it is not added is to allow you to define the setting ie because you are using SQL 2000 or 2005.

No problem with a higher version just lots of people here are using express.

Yes you are right about the remove name, it just to make sure you are overriding the system defaults.

Hope it helps

ASP.NET / SQL 2k Membership prob

Almost have my custom membershipprovider finsihed, but have ran into a small snag.

Scenario:: 2 methods. CreateUser(blah blah) and ValidateUserName(string username). CreateUserWizard control calls CreateUser(blah blah) which in the start of the method calls ValidateUserName(string username). ValidateUserName runs astored procedure onSQL 2k to check and see if user exists in the db. Returns a bool true or false. If true, then everything works correctly and returns a message displaying that the user exists, please enter a different name.

Now the problem. If it returns false, meaning that the username doesnt exist and is available to use, it calls a stored procedure to create the record in the users db. This is where the problem lies. It does NOTHING but sit there waiting. But it never seems to time out. If I comment out the validating. It will add a record successfully.

Im wondering if it has something to do with calling 2stored procedures consecutivly??

::CODE TO FOLLOW::Heres the code.
------------

public override MembershipUser CreateUser(string username, stringpassword, stringemail, string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey, out MembershipCreateStatus status)
{
SqlConnection connect = null;
SqlCommand cmd = null;
MembershipUser newUser = null;

if (!ValidateUsername(username))
{
status = MembershipCreateStatus.DuplicateUserName;
return null;
}

try
{
connect = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnString].ConnectionString);

cmd = new SqlCommand("createuser", connect);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@.UserName", username));
cmd.Parameters.Add(new SqlParameter("@.Pwd", password));
cmd.Parameters.Add(new SqlParameter("@.PwdQuestion", passwordQuestion));
cmd.Parameters.Add(new SqlParameter("@.PwdAnswer", passwordAnswer));
cmd.Parameters.Add(new SqlParameter("@.Email", email));
cmd.Parameters.Add(new SqlParameter("@.Comment", _Name));
cmd.Parameters.Add(new SqlParameter("@.IsApproved", MyMethods.BoolToInt(isApproved)));
cmd.Parameters.Add("@.IsLockedOut", SqlDbType.Bit).Value = 0;
cmd.Parameters.Add(new SqlParameter("@.CreationDate", DateTime.Now));
cmd.Parameters.Add(new SqlParameter("@.LastLoginDate", DateTime.Now));

connect.Open();
cmd.ExecuteNonQuery();

// Right now I am giving default values for DateTime
// in Membership constructor.
newUser = new MembershipUser(
_Name,
username, null, String.Empty, String.Empty,
String.Empty, true, false, DateTime.Now,
DateTime.Now, DateTime.Now, DateTime.Now,
DateTime.Now
);
status = MembershipCreateStatus.Success;
}
catch (Exception ex)
{
status = MembershipCreateStatus.ProviderError;
newUser = null;
throw ex;
}
finally
{
cmd.Dispose();
connect.Close();
}
return newUser;
}

private bool ValidateUsername(string userName)
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
bool IsValid = false;

try
{
conn = new SqlConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings[ConnString].ConnectionString;

cmd = new SqlCommand("CheckUsername", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@.Username", userName));

conn.Open();
reader = cmd.ExecuteReader();
while (!reader.Read())
{
IsValid = true;
}
return IsValid;
}
catch (Exception ex)
{
throw ex;
}
finally
{
reader.Close();
cmd.Dispose();
conn.Close();
}
}|||

OK! NOT EVERYONE AT ONCE ! <big grin>

just kidding, anyways figured out the problem after pulling all but one last hair out.

The "while(!reader.Read())" was causing problems, just replaced it with "while(reader.HasRows)" and viola, it worked. Beats me.