I'm working with an msde database on my local machine, but need to take that database and move it to my remote host. This is problematic because I can not just do a back up and restore.
I was wondering if there were any asp.net applications that would generate lots of sql statements to reproduce the database on my remote host.
I had built a program to back up all the tables in that way, but, forgot that the membership stuff uses stored procedures, and I don't not know how to modify my program in order to back those up too.
Any help will be much appreciated, thank you very much for reading my post
If you just want your stored procs and tables and other objects you could script out the entire DB and compile them at the destination DB. I am not sure if MSDE has that option. Right click on DB -> All Tasks -> Generate SQL Script.|||
woa, thats absolutly fantastic! But still only close. If I copy portions of statemetns at a time it will work, but Asp.net enterprise manager does not seem to like some of the keywords.
things like
CREATE PROCEDURE [dbo].[aspnet_Membership_GetUserByEmail] @.ApplicationNamenvarchar(256), @.Emailnvarchar(256)ASBEGIN IF( @.EmailISNULL )SELECT u.UserNameFROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership mWHERELOWER(@.ApplicationName) = a.LoweredApplicationNameAND u.ApplicationId = a.ApplicationIdAND u.UserId = m.UserIdAND m.LoweredEmailISNULLELSE SELECT u.UserNameFROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership mWHERELOWER(@.ApplicationName) = a.LoweredApplicationNameAND u.ApplicationId = a.ApplicationIdAND u.UserId = m.UserIdANDLOWER(@.Email) = m.LoweredEmailIF (@.@.rowcount = 0)RETURN(1)RETURN(0)END
works, but this will not
I noticed there are a bunch of options will in the wizard that generates the script. Is there a way to get rid all that extra stuff?
SET ANSI_NULLSONGOSET QUOTED_IDENTIFIEROFFGOIFNOT EXISTS (SELECT *FROM dbo.sysobjectsWHERE id =OBJECT_ID(N'[dbo].[aspnet_Membership_GetUserByEmail]')ANDOBJECTPROPERTY(id,N'IsProcedure') = 1)BEGINEXEC dbo.sp_executesql @.statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_GetUserByEmail] @.ApplicationName nvarchar(256), @.Email nvarchar(256)ASBEGIN IF( @.Email IS NULL ) SELECT u.UserName FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WHERE LOWER(@.ApplicationName) = a.LoweredApplicationName AND u.ApplicationId = a.ApplicationId AND u.UserId = m.UserId AND m.LoweredEmail IS NULL ELSE SELECT u.UserName FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WHERE LOWER(@.ApplicationName) = a.LoweredApplicationName AND u.ApplicationId = a.ApplicationId AND u.UserId = m.UserId AND LOWER(@.Email) = m.LoweredEmail IF (@.@.rowcount = 0) RETURN(1) RETURN(0)END'END
I noticed there were a bunch of options when generating the script, is there an option that will get rid of all that extra stuff?
I am trying to move more then one proceedure, I just showed one for an example
Thanks so much
|||does anyone have any experience with this, or have any alternative methods?|||What do you mean by ASP.NET Enterprise Manager? Do you want to use the generated scripts in VS2005 IDE? If so there are many limitations to the sql statements, e.g. SET/EXEC are not supported. The only option I found in the Script Wizard that will result in such differences between the 2 scripts you posted is 'Include IF NOT EXISTS' option.|||My web host uses ASP.NET enterprise manager.. its similiar to phpmyadmin, but for mssql|||'Include IF NOT EXISTS' only gets rid of some of stuff it seems.|||If you have access to Enterprise Manager quick and easy backup the database and restore it on your host server. Hope this helps.
