Can aspnetdb.mdf be moved from development to production machine?

1.) Can an aspnetdb.mdf database be configured and setup on one server and then be moved to a production server or is there something machine specific that keeps this from being possible?
2.) Is putting this file in the app_data folder something that is used only for SQL 2005 or SQL express? I had to set up a connection string in my SQL 2000 installation to get the connection to work.

Thanks for any input!

Colelaus

aspnetdb.mdf is the default sql express database created, if you want to move it onto production, your production server has to install sql express and reference it in your connection string|||

I'm using aspnetdb.mdf as my security database but not SQL Express due to llimitations on the server. I guess my question is really is there anything that is generated in the database when setting up security that is machine specific that would keep the database from being moved from one machine to another?

If it is machine specific, it would require as you referenced, me to recreate on the production server and reset all the users again.

Colelaus

|||

Hi Colelaus,

If your production server provides SQL server other editions than the Express edition, you will need to have the .mdf database files attach to server manually, since other edtions of SQL server does not support attaching database files automatically at runtime.

Also, you will need to modify your connection string and make it look like "Data Source=ServerName\InstanceName;Initial Catelog=DatabaseName". It points to a database on server instead of a .mdf file.For other information, you will need to contact the hosting service.

The tool you may use : Aspnet_regsql.exe ( More details about the tool, pls visit http://msdn2.microsoft.com/en-us/library/ms229862(VS.80).aspx )

Thanks.

|||

Thanks. That is exactly the info I needed.

Colelaus

Can any SQL Gurus help?

Hi, I've moved from an access db to SQL server 2000.

I have these two sql commands which work fine in access, but are giving me an error in SQL server.

sql statements:

sqltext = "UPDATE SQLPeople SET Answer='"& Request.Form("Answer")&"', Comments='"& Request.Form("Comments")&"', DateUpdated=" & FormatDateTime(Now, 2) & " WHERE RID = (SELECT MIN(RID) FROM People WHERE Answer IS NULL and ReadingType = 1) "

sqltext1 = "SELECT RID FROM SQLPeople WHERE RID = (SELECT MIN(RID) FROM People WHERE Answer IS NULL and ReadingType = 1) "
rsRecord.Open sqltext1, objConn, adOpenDynamic, adLockOptimistic

objConn.Execute(sqltext)

Error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference

Im a bit stumped =( Again, works fine in Access, but not SQL Server 2000i'd recommend rethinking your where statement for other reasons than sql server not liking it.

i think this page might help you
http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=79

or
http://www.4guysfromrolla.com/webtech/tips/t122600-1.shtml

or passing the id in a field in the <FORM>

anyway, if you only want to change your sql, i think you just replace WHERE with HAVING and add GROUP BY and top

select top 1 MIN(RID) as minRID from People GROUP BY RID having Answer IS NULL and ReadingType = 1|||thanks for the help. I've checked those articles which both seem to create or capture a value for each record using identity@.@.. However, Im still not clear on how to update the record with that value in an ID column

i'm doing my initial insert, then a select for identity@.@. and i assume an update on the record with that value.

not too clear on how this is done, the update portion.

this is the example that is given on 4guys.
**********
Dim db,rcs,new_identity

'Create a database connection
Set db = Server.CreateObject("adodb.connection")
db.Open "DSN=MyDSN"

'Execute the INSERT statement and the SELECT @.@.IDENTITY
Set rcs = db.execute("insert into tablename (fields,..) " & _
"values (values,...);" & _
"select @.@.identity").nextrecordset

'Retrieve the @.@.IDENTITY value
new_identity = rcs(0)
***********

So not clear on how to update the record in question with the record value?

thanks,

j|||sorry, idont have time to look much into it right now, but i didnt use ADO for my updates..i did it in a stored procedure|||I would call a stored proc for this:

From your source code:

sqlstr = "exec my_sp"

And in the SP:
___
INSERT...

UPDATE ... WHERE idField = @.@.IDENTITY
___

This makes you be sure you update the last created id in your table.

If you need to make more transactions between INSERT and UPDATE:
__
INSERT...
SET @.mycurrentID = @.@.IDENTITY

...

UPDATE... WHERE idField = @.mycurrentID
__

Can a DTS package be scripted or transferred to another server?

I have production SQL Server database that must be moved to a new
machine. There is a fairly complex DTS package on the original server
that is used to handle the weekly updates to the database.

Is there a way to export this DTS package in order to set it up on the
new machine as well?

Best Regards,

Warren Wright
Scorex Development Teamwarren.wright@.us.scorex.com (Warren Wright) wrote in message news:<8497c269.0307291200.68fe227b@.posting.google.com>...
> I have production SQL Server database that must be moved to a new
> machine. There is a fairly complex DTS package on the original server
> that is used to handle the weekly updates to the database.
> Is there a way to export this DTS package in order to set it up on the
> new machine as well?
> Best Regards,
> Warren Wright
> Scorex Development Team

http://www.sqldts.com/default.aspx?6,105,204,0,1

The June issue of SQL Server magazine (http://www.sqlmag.com) had a
good article on making DTS packages portable between servers.

Simon|||warren.wright@.us.scorex.com (Warren Wright) wrote in message news:<8497c269.0307291200.68fe227b@.posting.google.com>...
> I have production SQL Server database that must be moved to a new
> machine. There is a fairly complex DTS package on the original server
> that is used to handle the weekly updates to the database.
> Is there a way to export this DTS package in order to set it up on the
> new machine as well?
> Best Regards,
> Warren Wright
> Scorex Development Team

You can, when designing the DTS Package, select "Save As..." and
change the "location" to a structured storage file. You can then
import this file onto your production server. If you scripted all
your local connections as "(local)", then this should be the end of
the story, if not, you'll need to open it and change your local
connections to the production server.

Hope this helps

Hodge|||Warren

Try this link.

http://www.sqldts.com/default.aspx?6,105,204,0,1

Hope that helps

John