ASP.NET 2.0 Membership and SQL 2000

[fireemt1]

I deeply appologize if this has been posted before, but Im in a rush to figure it out. The scenario:

I have a hosting account with ASP.NET 2.0 and a SQL 2000 db. No SQLExpress present to use the template ASPNETDB.MDF file as a user instance sql db.

I am wanting to use the Membership and role classes in the .net framework but without SQL express this doesnt quite make it as straight forward. I can only import .bak files into the SQL 2000 db and can't use aspnet_regsql to attach a db.

Someone SOMEWHERE has got to have this problem before. Maybe a template bak file to import into a SQL 2000 db.

I'll take any solutions or ideas.

Thanks.

 

[adamroof]

so this is a hosted sql server is what im getting

do you have your own sql server somewhere? do you have enterprise manager access to the hosted db?

what im thinking here is you can do a dts on your sql server to the linked hosted sql to copy/create that db over there.

is that close?

ive had rights issues on hosted sqls before, so i had to go as far as using query analyzer after spitting out the "Genereate SQL Script" on my local db server to create the db, then run the DTS to copy data from my sql to the hosted sql.

if im not makin sense, stop me!

 

[fireemt1]

Thanks for the reply adamroof.

Yes, it is a hosted Sql Server 2000. My only option on importing information is restoring a bak file or using a web based app to create tables and edit info. Since my post, I have atleast created an instance of this db on a MSDE running on my Win2k3 server. Not a SQL genius but isn't it a scaled down version of SQL 2k ? Would it be possible to create a bak of this file and import it to the SQL 2000 server ?

 

[adamroof]

you should be able to open the msde in enterprise manager and then right click on the db name, backup, tell it where to go (click add, browse to folder, type a name.BAK) then you should.

im headin home, its 5pm, but if you still have probs or need someone with EM to help out, post back and ill check back in a couple hours when i get home and get you that zipped bak file somehow.

 

[fireemt1]

Thanks for the help. I've created a bak file with osql due to lack of EM. I'll let you know how it goes. If it doesnt work, I guess I'll just write a new custom provider. An hour of programming beats 2 days of trying to figure out a work around. Thanks again.

I'll let you know how it goes.

 

[fireemt1]

Ok, one last shot. Have the bak file restored on the SQL 2000 server. All tables are present. Ran the web page using the following:

protected void btnGo_Click(Object sender, EventArgs e)
{
try
{
MembershipCreateStatus Status;

MembershipUser newUser = Membership.CreateUser(txtUserName.Text, txtPassword.Text, txtEmail.Text, txtPwdQ.Text, txtPwdA.Text, true, out Status);

if(newUser == null)
{
StatusLabel.Text = Status.ToString();
}
else
{
StatusLabel.Text = "User created successfully";
}
}
catch(Exception ex)
{
StatusLabel.Text = "Unable to create user<br />" + ex.Message;
}
}

....now when I access the page I get this...

Unable to create user
Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

I do know that I have a good connection to the db by creating a test table into the database and calling it from the same page. Works fine.

Any Ideas ?

 

[fireemt1]

Oh sorry, and yes I viewed the sql stored procedures and they ARE present

 

[adamroof]

id say its with the 'dbo.aspnet_CheckSchemaVersion'.

when you copied it over the owner of the object may not have copied over.

try to take the owner out of the proc, you are authenticating with rights to dbo

change
CREATE PROC dbo.aspnet_CheckSchemaVersion

to
CREATE PROC aspnet_CheckSchemaVersion

or
CREATE PROC myUserName.aspnet_CheckSchemaVersion

without EM, youll have to finese the ALTER or recreate it without the name

 

[fireemt1]

Nope, took the dbo out of the call and still giving the same error. It appears that the code is calling as dbo.aspnet_CheckSchemaVersion. So I'm figuring I WILL have to create a custom provider.

 

[antmx]

Have you tried running this?

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe

I just tried it and it created all of the required tables and sprocs to get Profiling etc running on SQL 2000.