I have an ASP.NET web application using the default ASPNETDB that I did in VWD 2005, and deployed to a production server (IIS).
I need to make a table addition to the production database (ASPNETDB). I'm trying to use SQL Management Studio to attache the database, but it keeps saying the database is locked by a process. I shut down all the services that would likely be using it, but it's still showing locked. What do I need to shut down to attach the database to make the change?
I'm having my website hosted with webhost4life.com which is very helpful and a really great company if you are using Visual Web Developer with SQL Express. The question I have for anyone who can help me is that my site will offer member roles for customer account maintenance with login and the works. As you know, when you use memberships with Visual Web Developer, it associates all these controls with a database called aspnetdb.mdf. And you maintain this database through the asp.net configuration control. Once this database is launched, I've learned how to access it by changing my web.config file removing the LocalSQLServer and adding one with the connection string to the aspnetdb.mdf database on the server. My question is, once I've done this and connected through that string, can I still use the asp.net configuration to edit the database (e.g. the users, roles, etc)?
I need to update about 1.3 million rows in a table of mine. I am getting the data from one of the columns of the same table and updating the new column. I am doing this using a cursor which I have put in a stored procedure. As this is a production table which users might be accessing.It is a web based application and I can't slow the system down. So I am willing to run the stored prcedure during off peak hours. However, do I need to put this in a transaction? If I did put it in a transaction what type of isolation level should I opt for? Data integrity is very important for me and I don't mind to compromise on the performance. I am doing this because one of the columns which has "short description" entry is has become too small for business purposes and we want to increase it's length from varchar(100) to varchar(150). As this is SQL 6.5, I can't increase the lenght of the column. So Iadded a new column and will run the stored proc. What precautions are to be taken? This is on a high priority basis and very important too.
Thanks in advance...
Stored procedure code:
USE DB_Registration_Dev GO IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME='usp_update_product' AND TYPE='P') DROP PROCEDURE usp_update_product GO CREATE PROC usp_update_product AS DECLARE @short_desc varchar(100) DECLARE @prod_id int
DECLARE sdesc_curs CURSOR FOR SELECT [Product].[product_id] , [Product].[short_description] FROM Product
OPEN sdesc_curs
FETCH NEXT FROM sdesc_curs INTO @prod_id, @short_desc
WHILE @@FETCH_STATUS = 0 BEGIN UPDATE Product SET [Product].[sdesc] = @short_desc WHERE Product_id=@prod_id FETCH NEXT FROM sdesc_curs INTO @prod_id, @short_desc IF @@FETCH_STATUS <> 0 PRINT ' Finished Updating the table...go ahead and have fun ...! ' END DEALLOCATE sdesc_curs GO
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
I am using SQL Server 2005 Express and the table diagrams and everything works great locally. However, pretty soon I'll have to create the same database structure on the production server (hosted remotely at DiscountASP.NET) and I'm not quite sure what's the best way to go about doing that. I only need to copy over the data for a few tables (ones with enumerated values, for instance), but the rest I only need to create the table structure (what's the point in copying over test users for instance).Also, any future modifications to the database structure will need to be updated to the server along with code updates. How do you all handle this? Do you keep an Excel file of database modifications that you run when you perform an update? Or is there an easier way?Thanks!
I am getting the error: Cannot open database "aspnetdb" requested by the login. The login failed. When I browse to my ASP.NET 3.5 LINQ web application on the IIS 6.0 server on Server 2003. I imagine this is because while I granted SQL Server 2005 login and permissions to my database that the application stores its data in, I did NOT grant any rights to the service account the IIS Application Pool uses for its identity to the aspnetdb database on SQL Server which is where all my roles information is stored at. My question is what are the MINIMUM permissions needed for this database so it can perform its roles related functions? I'm using Windows Authentications with the SQL Role provider for authorization.
Thank you.
EDIT: I think I only need to open the aspnetdb database and add my login to the aspnet_Roles_FullAccess role. Is that correct?
When I first sign up with Visual Web Developer, Do I continue to use this database ASPNETDB.MDF or do I need to connect to the Microsoft SQL Server Database? Does Visual Web Developer Express automaticaliyy connect to SQL Server Express Edition? Thanks Computergirl
I have established roles to restrict access to the data entry pages. The database is SQL Server which I've established on the hosting server. Security was previously maintained through ~/app_data/aspnetdb.mdb (MS Access). It works, but isn't too stable, so I have modified the security to use SQL Server with an aspnetdb.mdf file, also located in the app_data folder. Is this appropriate? Since it doesn't work, I assume not.It's now my assumption that I need to setup a SQL Server database to support the security/roles. I have seen some reference to using a script to build the aspnet.mdf on the hosting server. If I do create such a database, is there anything special I need to do to have my application read the aspnetdb? Do I need a connection string in the membership and/or roleManager sections of my web.config. A little guidance would be appreciated.Thanks
Hi,I'm building an intranet app for a client using ASP.NET 2. The client is running SQL Server 2000 - and does not have plans to upgrade to 2005 anytime soon.Is there a script that I can use to create the objects in the ASPNETDB database so that I can do this in SQL Server 2000? Also, what additional changes would I have to make in order for the application to point to a SQL Server 2000 database with these objects?Thanks in advance, Al
Hi there ; I uploaded my app to host but crashed during membership activities, and i figured out it's cause of ASPNETDB.mdf that is not accessible in remote server, & it's driving me crazy.As a solution i wanted to know :is it possible to attatch my db in App_Data to remote server and make use of it just like before?Please take it into concideration that i've already tried 'aspnet_regsql.exe' and the server gives me error:40 (only local access allowed ). thanks in advance
Running ASP.NET 2.0 on a Windows SBS2000 with IIS5 and SQLServer 2000 on the same box. Trying to use membership controls to authenticate. Installed aspnetdb on the server using aspnet_regsql.exe Ran an ASPX page with the asp:CreateUserWizard control. The "create user" button returns the following error: A user instance was requested in the connection string but the server specified does not support this option I appears that the connection string I am using is wrong.(See below for web.config entries) I have tried: "data source=RGMILLSITES;Integrated Security=True;database=aspnetdb;User Instance=True""data source=RGMILLSITES;Integrated Security=True;database=aspnetdb;User Instance=False""data source=RGMILLSITES;Integrated Security=True;database=aspnetdb""data source=RGMILLSITES;Integrated Security=True;Initial Catalog=aspnetdb;User Instance=False""data source=RGMILLSITES;Integrated Security=True;Initial Catalog=aspnetdb" I have renamed LocalSqlServer to LocalSqlServerASPNETDB to ensure that a global configuration is not used. I tried to rename the server in the connection string and got the same error"data source=RGMILLSITES00;Integrated Security=True;Initial Catalog=aspnetdb" I've checked the Machine.config and web.config in the C:WINNTMicrosoft.NETFrameworkv2.0.50727CONFIG folderNeither have the LocalSqlServerASPNETDB configuration listed. So now I'm thinking there is a cashing isssue. I've tried to restart the site and got the same result. Any thoughts would be appreciated. RobGMiller
The web.config file contains the following related skeema. <connectionStrings> <add name="LocalSqlServerASPNETDB" connectionString="data source=RGMILLSITES;Integrated Security=True;Initial Catalog=aspnetdb;User Instance=False" ProviderName="System.Data.SqlClient" /></connectionStrings> <roleManager enabled="true" defaultProvider="CustomizedRoleProvider"> <providers> <add name="CustomizedRoleProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="LocalSqlServerASPNETDB" /> </providers></roleManager> <membership defaultProvider="CustomizedProvider"> <providers> <add name="CustomizedProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="LocalSqlServerASPNETDB" applicationName="/" minRequiredPasswordLength="5" minRequiredNonalphanumericCharacters="0"/> </providers></membership>
I have uploaded my site on localhost and sharing on intranethow can i answer these question? Q1) How many users have loggedin? and using my database?Q2) Which table has lots of load?Q3) how can i immediately close particularly connection?
Hi, I am not using Express 2005 on my production server, rather I'm using SQL Server 2005 standard edition. So I have my site working perfectly on the development server, but once I publish it, the builtin aspnetdb that I use for logging in and such doesn't work. I have another database my app uses and it works fine once I changed the connection string. So I think I just need to change the connection string all the fancy new login controls use. But I can't figure out how to do that. Any help would be much appreciated. Thanks!
I used aspnet_regsql to put membership tables, etc on sql server 2005. Is there a tool to help me build the web.config statement to point to these tables? I found the code below at http://www.c-sharpcorner.com/UploadFile/dsdaf/104092006022507AM/1.aspx?ArticleID=bc2ca2b0-9e3d-42ad-b4b9-0dce96231ce6 but it throws this error: Parser Error Message: Unrecognized configuration section membership. <membership> <providers> <remove name="AspNetSqlMembershipProvider" /> <add connectionStringName="MyDBConnectionString" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" /> </providers> </membership
Hi Eevryone: I seem to have corrupted my ASP.net Web Site Administration Tool some how. It worked fine a few days ago, but reloaded SQL Server 2005 Express Edition W Adv Services SP1 yesterday I can not get past the "Unable to connect to SQL Server database" error. I have run the aspnetdb.exe and created both a aspnetdb.mdf and loaded the tables to into a existing mdf. I have checked all the permissions. So any ideas on what I am doing wrong, or can you point me to some documentation on the 'AspNetSqlProvider'
Hi All... We're cloning an application currently under development on Server 2003 / SQL Server 2005 / dotnet 2.0. The clone's going to live on an XPSP2 box with SQL Server 2003 - it's for demo purposes... The application's able to get to our own database, but it's having a hard time creating the ASPNETDB that dot net likes to have. Aside from the OS, the only difference between the two SQL Servers is that on the working system, we're using windows authentication on the database while on the non-working system, we're using SQL Server authentication. But we've tuned the connection strings appropriately - again, we can get to our own db just fine. We have the following lines in our web.config: <connectionstrings> <clear /> <add name="LocalSqlServer" connectionstring="Server=localhost; Database=ASPNETDB; UID=sa; PWD=mypassword" /></connectionstring> On the working system, the Server is an actual IP address, integrated security is set to true, and of course the uid/pwd is a bit different. But again, using a very similar connection string, we able to get to OUR database on the non-working system... The error we're getting at runtime is: Cannot open database "ASPNETDB" requested by the login. The login failed. Login failed for user 'sa'. When we poke into the database from SQL Server Management Studio - on a remote machine - we can see our own database is there, but the ASPNETDB isnt there. Yet... I suspect it failing getting in to just create the db.... Any thoughts? Thanks for the help!!! -- Curt
In my ASP.NET application I am using authentication/Login controls. I have my development configuration running correctly... my providers are setup in the web config along with the appropriate connection strings for these providers. In the development environment .NET Framework 2.0 and SQL Server Express 2005 configured in IIS, ASPNETDB installed in App_Data. I am able to create new user accounts and re-login under these accounts, so all validation is working on the development servers. Now... I need to deploy the ASP.NET 2.0 site. My provider is allegedly ASP.NET 2.0, with SQL Server 2000, implimented on a seperate backend server which I utilize in my application during development to reduce configuration issues during deployment. I can create databases and utilize SQL Server Management Studio locally (2005) to perform all appropriate development tasks on my remote SQL 2000 Server. However, I need to deploy ASPNETDB on that remote SQL 2000 Server (apparently my provider has no instance of SQL Server Expres 2005 running on the web server). I tried to use the Import/Export Wizard in SQL Server 2005 Management Studio, I can only transfer the tables and views to my remote ASPNETDB database (which I had to create from the provider side utilities). I have tried using the "Copy Database..." command on the ASPNETDB database, but my error is: "The destination SQL Server must an SQL Server 2005 instance". ...Then I tried to script the database and run it against the remote server within the Import/Export Wizard... this failed miserably... massive Syntax Errors puked out of the parser. ...Then I tried to mirror the ASPNETDB database to the remote SQL Server 2000 instance... my error is: "The destination SQL Server must an SQL Server 2005 instance". ...Then I tried creating an SSIS package utilizing the "Transfer SQL Server Objects Task". This failed as well... reporting that the Login cannot be null... apparently a known issue and unrelease fix. So now I'm stuck! How can I impliment the ASPNETDB on a remote SQL Server 2000 instance? Theoretically, if I change the configuration of the providers for ASP.NET the Login/Authentication controls in the web.config file to point to the new ASPNETDB instance attached to the remote SQL 2000 Server, and of course... if I could get the stored procedures ported it should work right? The schema should be in tact whereas I used the Import/Export wizard to at least get the tables and views ported to the remote SQL 2000 Server. But the 55 stored procedures and 1270 system stored procedures still need to be transferred. How can I accomplish this? I hope I provided enough of an overview... Thanks in advance for any suggestions. Don't hesitate on any questions.
hi,i'm new to deploying asp.net pages with built in asp.net login controls over the web server. i've uploaded my asp.net pages with web.config and placed my ASPNETDB in App_Data folder on the webserver as it was placed in local machine. but it dint work and displayed the following error
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
i also enabled the TCP/IP connection on my local machine to accept remote connections.. Can any1 please provide me a step to step guide on how to deploy the webiste? Thanking u in advance. Regards
I have this simple full text search query that works perfectly on my own computer using sql server 2005 express, however, on the production server(shared hosting)when I added the first 50+ rows, the full text search works perfect, but as the number of rows increases, the full text search can only see the first50+ rows, but not the new ones. Is there any quick solution for this or it's just a common mistake for developers for not properly indexed columns?Is there a way to re-indexed all rows without loosing data on the live server? search query: SELECT TOP 50 *FROM li_BookmarksWHERE FREETEXT(Keywords,@Keywords)
I currently have a SQL Server cluster setup with a Primary DB Server SERVER1 and the Standby server SERVER2. SERVER1 has been failing more than normal is the past few weeks and its takes upto 5 mins for SERVER2 realize that SERVER1 is down. I am looking for a better way to implement a backup server on production with minimum downtime. Please adivse..
Production and development servers are on different domains and they do not trust each other. How do I import data from the table t1 from a database db1 in production and load it into table t1 inside database db1 in development?
Any help would be greatly appreciated.My problem is that I need to set up a backup SQL Server 2000 machinewhich can be used in case of a failure to my primary. All databases(30 as of now) must be an up to the minute exact copy of productionand include most recent changes in data as well as any structurechanges (Tables, Views, SP's, Triggers, Users . . etc).When I tried this using Transactional Replication, the replicationprocess gets fouled up once I introduce any kind of structure changesto the DB. I've considered the idea of doing periodic backups andrestoring it to my backup SQL server, but this does not give me theconcurrency needed with 0 latency.I've seen articles that recommend using Transaction Replication with'Scheduled Table Refresh', and also doing database dumps to restore onthe backup machine, but I have not been able to find any documentationregarding this to try out. How can I implement this type of backupstrategy in SQL 2000?
I would like to deploy several reports to production server, Do i need to install reporting services entire software in order to run the reports or is it possible to just have runtime files installed on it to run the reports.
please help, i have almost 100 reports to be deployed on this server which is located in other country.
Thanks for the helpful information.
(i am using SQL server 2005 / reporting services 2005.)
ok ok, stop laughing. for real, is there any programatic way of doing this? whom ever created this database i inherited (SQL 2000) created the LDF and DATA files on the same drive and in the same folder for that matter. just trying to do a little disaster magament.
This feels like a silly question, but I`m going to ask it anyway...
I have limited SQL Server experience, but have run into a wall with a client`s Web/Access combination. I need to upgrade to SQL Server. I have Beta3 installed on a development box and am very happy with it. Is anyone running this thing in a production environment? This isn`t going to be experience huge loads, so I`m tempted. Tell me if I`m crazy for wanting to try it.
I have a brand new database server with system databases. I need to copy like four production database from another server to this new server. Can i do restore of the last production backups and restore them on the new server without creating the empty databases on the new server.If any one has better approach i will appreciate
1.First issue The port number already allocated was 2059. We have changed that into the default port 1433 in the node. Now we are able to connect the node from the client application. But we are not able to see the configuration manager in any of the environment now.
2. 2nd issue Cluster Environment
Error while executing the Package The connection details are not loaded in the connection manager tag
I have production server 2000. The server gets disconnected sometime by itself and sometime it is working fine.Sometimes it even doesn't get restarted. Is there any problem with service packs and some performance issues. Can you SQL guru give me best suggestion and how should i proceed.
Hi,I ran test data on my development machine and it took 1 minute toinsert the data. Ran the same set of data on the server and took 5minutes.Check both database and everything is the same. I even copied theproduction DB on my machine and it was taking still about 1 minute.Look at the fragmentation, and all the numbers are better on the serverthan my development machine so it should be faster.In the application I put some timer and discover that the insert istaking 0.015 ms on the server and 0 on the development. So the problemis on the insert.It is a Web application using ASP.NET.Here are the spec of the computers:Development: P4 HT 3.2GHz 1gig memory running WIN XPServer: Xeon 2.8GHz 1.5Gig memory running WIN 2000 serverAny idea how I to pinpoint the problem? I'm not at the point ofthinking that it can be the hardware, but how to verify that?ThanksFrank
Like most enterprise there is the database administrator (dba) and there are the developers(dev). The dba are conservative while the developers are also exploring their options.
One of the current usage I'm experimenting on is to provide data visualization - image for the data. Like most I needed to "create" the System.Drawing assembly in the database, marking it unsafe.
During my testing, my code had some exception and that brought down SQL Server.
I read that the CLR is better compare to the sp_OAs as well as the extended stored procedures written in C++ because it isolates the execution in a separate app domain and termination is clean - in case of any errors, it should not bring down SQL Server.
Also I read marking assembly unsafe void these benefits of isolation.
Instead of having to manage the situation where it involves code review by the dba and asking the dba to take some risk, is there a technique where all CLR code that runs in the production server does not pose stability issues.
How can I apply SP2 on running production server if SP resets all protocols (from TCP/IP enabled on production server to Shared Memory enabled) without disrupting its connections and thereafter its work?
