ASP.NET Application - Migrating From Access 2000 To SQL Server 7
Jul 23, 2005
First off, sorry if my cross posting offends anyone. I'm posting this
in Access and SQL Server groups - not sure which one is appropriate.
I have a relatively simple ASP.NET/VB.NET application that is now
hitting an Access 2000 database over an intranet. We have to migrate
the database to SQL Server 7. My experience with ASP.NET is pretty
limited and my experience with SQL Server is nonexistent.
We have an MSDN subscription, so I went to the downloads section to get
SQL Server 7. Guess what? SQL Server 6.5 is available, as is 2000 and
2005, but no SQL Server 7. So my first question is, does anyone know if
it is available for MSDN subscribers?
The next question is, does anyone know of a good resource that explains
how to make the transition from Access 2000 to SQL Server 7? I know the
data has to be migrated and the connection between the application and
the database modified, but am really not sure exactly what to first and
the correct way to go about it. The application is a simple product
configurator. There's not a lot of data and it's not a very complex
database.
Thanks in advance. If you'd like, please copy responses to
lcifers(AT)yahoo.com (AT) = @
I am new to SQL Server, but the current project that I am working on has the following requirement:-
1) Migrate the application (a servlet based web application on Apache Tomcat) from Solaris to Wintel 2) Migrate the supporting database from SQL Server 2000 to Sql Server 2005 3) Get IIS to communicate with Tomcat for serving servlet/jsp etc to the client
Though I successfully completed item 1 above, am stuck at item 2. Details are below
Actions taken for item 2
A. Installed MS 2005 B. Created new database in MS 2005 (logged in as user 'sa') C. Generated SQL scripts (such as create table table_A etc) from existing MS 2000 D. Genearted SQL scripts (such as insert into table_A etc) from existing MS 2000 E. Created new schema in MS 2005 F. Ran scripts C & D in the new schema. All tables are records populated. G. Obtained new JDBC driver and test run to see if connection is working fine, and it worked. Even ran an sql statment
Code Block[select count(*) from sa.table_A]
and got appropriate response.
H. When I made the application to talk to this new database (which is a copy of Production from step C, D above), it's behaving as though it cannot find the record. I. When I further debugged, I realised that the web application is excuting queries without mentioning the schema. For eg.
Code Block[Select firstName, lastName from table_A]
Or rather it assumes that the user connecting to database is same as the schema name.
J. To further ascertain my point, I ran the query
Code Block[Select firstName, lastName from sa.table_A]
and it worked!
Now the real problem is that I cannot modify the existing code to append a schema name and this approach is rather not recommended best practise.
I tried to match the user name with the schema name, even made this schema as default to the user. But still not finding any luck.
I request all you experts out there to help me out with this problem.
I need to migrate my access database to SQL2000 Server. I have three tables in my access database. In each table have 3000-6000 records. So how to put all these records in to sql2000 server tables. The table structure in Access database and sql2000 database are same.
Here core work is just insert all these 3000 - 6000 records (rows) from Access database to sql2000 database
Hi,I have developed an application using VB 6 (SP 5), MS Access 2000 andCrystal Reports 9. I migrated from from access to SQl Server 2000.This wasn't a problem as i could import all the tables to SQL Server.Also, i could make VB6 talk to SQl Server.The problem arsies when i run my application. The sql syntax foraccess seems to be different than that for SQL Server. A simpleexample being: In access boolean datatype is true/false ,whereas inSQL Server the boolean equivalent is bit (numerical 1 or 0). Thesekind of issues are causing problems and most queries don't run.Would i need to go and change all the queries in accordance with SQlServer syntax ,which would be very time consuming or is there anyfunction which will convert the access datatype into its equivalentSQl Server datatype??Any input/thoughts/suggestions would be appreciated.ThanksJatin
I have installed the SQL Server 2000 and its up running.
I can connect to the database direct as administrator, but my test application(asp.net 2.0) returns an exception.
SqlConnection sqlConn = new SqlConnection("data source=10.61.90.24;initial catalog=testdb;persist security info=False;user id=sa;pwd=data;Type System Version=SQL Server 2000;");
sqlConn.Open();
I receive this exception: 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
We use MS Access 2000 as our database, but run into lots of proplems. So we decided to research the migration to SQL Server.
I used Access Upsizing utitlity to migrate tables and their data to SQL server very easily, and all the relationships, indecies and other information are converted correctly.
Next is to migrate the queries. I found that to be a pain. Is there any tool out there that can do it for me? Any help is greatly appreciated.
Once that's done, then it's the application itself, but I am not worrying that for now.
Hi, We are planning to migrate data from access to sqlserver. I have good knowledge on sql server...but never used access to migrate. Can anyone help me with the basics to be kept in mind and methods and errors you get in migrateing data.If possible explain in detail and what is to be done while migrating data.....thank you in advance.And its urgent please.
I have a query in Access that has an iif statement in the select system like so:
SELECT [01_qryCommonCost_01].*, tblPercent_Afe.AfeDescription, tblPercent_Vendor.[Vendor%], tblPercent_Afe.[Afe%], IIf([manual%] Is Not Null,[Manual%],IIf([PropType]="NonMkt",0,IIf([Vendor%] Is Not Null,[Vendor%],IIf([Afe%] Is Not Null And [GlType]<>"OpExp",[Afe%],[Gl%])))) AS [%], 0+round2([Amount]*[%]) AS Allocated, 0+round2([Allocated]*[Salvage%]) AS Salvage FROM (01_qryCommonCost_01 LEFT JOIN tblPercent_Vendor ON ([01_qryCommonCost_01].GlType = tblPercent_Vendor.GlTyp) AND ([01_qryCommonCost_01].VendorName = tblPercent_Vendor.VendorName)) LEFT JOIN tblPercent_Afe ON ([01_qryCommonCost_01].AfeNo = tblPercent_Afe.AfeNo) AND ([01_qryCommonCost_01].Group = tblPercent_Afe.Group)
But I can't figure out how to nest the iif statement in T-SQL. Any hints?
Afternoon all,Apologies for cross-posting but as my query covers both Access and SQLServer I thought I'd send it both!I have inherited a project to migrate a fairly complex series ofAccess databases into a single proper SQL database with a web frontend.Its quite a nasty job as people are working on a variety of data setsat several Universities around the world and the data has got verymessy; hence the requirement to put it all on one live web enableddatabase server and provide a web-based front end (particularly assome users insist on using Macs so can't run Access as a front endanyway).If anyone could give me hints on how to perform such a migration or ifanyone knows of any good books or other documents on this I'd begrateful for assistance.Many thanksRich MayMuseum of London
I want to migrate my Access XP database to SQL Server 2005 Express Edition. The Start Menu does not display any migration assistant in SQL Server 2005 Express menu list.
Any suggestions on migrating Access97 tables to sql server 2000? The DTS seems cumbersome and the Access97 upsizing wizard add-in is only compatible up to sql server 7.0.
Hi I am trying to migrate my database in Oracle 8i to Sql Server 2000 but am unsure of how to carry out the migration. How do I do it and what do I have to consider. My OS is Windows 2003. Thanks in advance
Has anyone had experience of migrating from 2000 to 2005 on the same box , and maintaining the SERVERNAMEINSTANCE_NAME. What are some effective ways of migrating and retaining the same servername/instance name ?
The issue revolves around minimising the use of extra boxes , as I would like to do the migration on the same server and keep the name the same .
Jack Vamvas -------------------- Search IT jobs from multiple sources- http://www.ITjobfeed.com
We are migrating from SQL Server 2000 to 2005. We currently use ADO.Net to make connection to sql server. I just want to know after migration to sql server 2005 do we have to make any changes in code in the way we make connection to use ADO.Net2 which is part of sql server 2005. ??
whenever i modified the connection of my current database...I'll always got an error,when asking for "server name"..it says [DBNETLIB...etc]Named Pipes etc error..my current database was SQLEXPRESS and i want to change it as an SQL OLEDB,2000,2005.. but as i said il always got an error..sometimes it says "SQL server does not exit..ODBC" etc...when im entering the "server name"..so i could only used Access or SQLEXPRESS server database.. what should i do with the error?? thanks for helping again..
Just a quick question, does anyone know any major issues with Migrating Sybase Databases to SQL Server? Just wondering if their any oddities I should be aware of? Thanks nixies
I'm in the process of migrating a lot of data (millions of rows, 4GB+of data) from an older SQL Server 7.0 database to a new SQL Server2000 machine.Time is not of the essence; my main concern during the migration isthat when I copy in the new data, the new database isn't paralyzed bythe amount of bulk copying being one. For this reason, I'm splittingthe data into one-month chunks (the data's all timestamped and goesback about 3 years), exporting as CSV, compressing the files, and thenimporting them on the target server. The reason I'm using CSV isbecause we may want to also copy this data to other non-SQL Serversystems later, and CSV is pretty universal. I'm also copying in thisformat because the target server is remotely hosted and is notaccessible by any method except FTP and Remote Desktop -- nodatabase-to-database copying allowed for security reasons.My questions:1) Given all of this, what would be the least intrusive way to copyover all this data? The target server has to remain running and berelatively uninterrupted. One of the issues that goes hand-in-handwith this is indexes: should I copy over all the data first and thencreate indexes, or allow SQL Server to rebuild indexes as I go?2) Another option is to make a SQL Server backup of the database fromthe old server, upload it, mount it, and then copy over the data. I'mworried that this would slow operations down to a crawl, though, whichis why I'm taking the piecemeal approach.Comments, suggestions, raw fish?
When I try to migrate a database on a SQL Server 2000 server to a SQL Server 2005 server with the Copy Database Wizard of the SQL Server Management Studio, I'm confronted with the following problem;
Performing operation...
- Add log for package (Success) - Add task for transferring database objects (Success) - Create package (Success) - Start SQL Server Agent Job (Success) - Execute SQL Server Agent Job (Error) Messages * The job failed. Check the event log on the destination server for details. (Copy Database Wizard)
When I take a look at 'Event viewer' on the SQL 2005 server, the following error is displayed;
InnerException-->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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I already enabled the MSSQLSERVER network configuration protocols (TCP/IP and Named Pipes ).
I'm migrating SQl Server Reporting Services from 2000 to 2005.The reports are generated normally in 2000 but it seems that it is taking more time in 2005 or sometimes it does not generate the report at all.Could you kindly suggest a solution?
I have successfully moved my data from a SQL Server 2000 hosting site to a SQL Server 2005 hosting site. I Made a backup of my database using Enterprise Manager (2000) and imported the database tables using SQL Server Management Studio (2005). I do not know how to move the 25 or so stored procedures that I have in SQL Server 2000. I have a very short amount of time to figure this out and am hoping that someone can give me a brief step by step answer on how to get this done. I would appreciate any information you can provide. Thank you!!
i have tried the below three steps but i dont get script to any output file or clipboard after the executing the script generation. And i would also like to why i get lo of errors when i run the scritp from the step one,i got the script and i try to run it on sql server 2000,there is lot of errors. I there any order i need to follow when i run the script in sql server 2000.
Plz help me out. This is urgent.....
How to Downgrade a Database from SQL Server 2005 to SQL Server 2000
As you may all know, SQL Server 2005 request a minimum of 8GB RAM to work€¦ let say satisfactorily. I first didn€™t knew that and after a while from the upgrade I did from SQL Server 2000 to 2005 my SQL Services were starting to crash three or four times per DAY!!!
At first I thought I was being attacked, but soon I realized it was nothing like that. I then decided to downgrade to an SQL Server 2000 edition. Though I looked around the internet to find some information on how to do that, I got very disappointed when I realized that no actual documentation of any kind could be found for that. So I am posting this thread to inform you on the procedures I had to follow for this action.
Before beginning I must assume, firstly that the user, who will attempt such thing, has a basic knowledge of SQL Environment, secondly that he has the two versions already installed (both 2000 and 2005), that a basic backup of the databases has been created and finally that all the 2005 SQL Server Users have been created at the SQL Server 2000 environment as well.
Step 1 Generating Scripts for the Database Elements and Structures
1) Right-click over the desired Database at 2005, Choose Tasks and the Generate Scripts (Option). 2) At the pop-up Dialog Box click at the Script All Objects in the selected Databases check box, to activate it and then Click the Next Button. 3) Set the following Elements to the following Values a. Script Collation , set to TRUE b. Script Database Create, set to TRUE c. Script of SQL Version, set to SQL SERVER 2000 d. Script foreign keys, set to FALSE e. Script Triggers, set to FALSE Then Hit the Next button 4) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end. 5) Click Finish
After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Structure of the Database will be created.
Be careful, the SQL Server 2005 Edition inserts the Views in a random place through the script. Therefore, all the scripts that are referred to the Views MUST be moved to the end of the script. If the Query Analyzer shows some errors do not be bothered. Delete all the elements created from the script and after you fix the code run it again.
Step2 Moving the data from 2005 to 2000
1) After completing the previous step successfully, moving the data follows. Right-click at the 2005 database you used to run the previous step and select Tasks and then choose the Export Data (option). 2) From the pop-up Dialog Box, select the Source Db and Click at the Next Button. 3) At the next step you will have to choose the destination server and the destination Database for the Data to be exported. Then Click Next. 4) A List of all the Source Database€™s Elements will appear in the screen. Select one by one all the Elements you wish to move and for each one click at the button Edit Mappings (Located at the bottom right corner of the Dialog Box just under the Elements list). A new Dialog box will pop-up. Select the Delete rows in Destination Tables option and activate the Enable Identity Insert Option. (Remember to repeat this action for each of the selected Element from the list that will be moved.
CAUTION!!! A malfunction of the SQL Server 2005 has been found. Not sure why, after multiple tries I have observed that when I tried to move more than twelve Elements at once, the Export Data Wizard of SQL Server 2005 seemed to disable the Enable Identity Insert Option that was activated over the Edit Mappings Dialog Box. But if the number of the selected Elements is smaller than 12 no problem seemed to appear.
Step 3 Generating Scripts for the Database Foreign Keys and Triggers
Finally, to successfully finish the downgrade of the Database, the Triggers and the Foreign Keys of the DB must be produced. The procedure that should be followed is the one stated next:
1) Right-Click at the SQL 2005 Database and Select from Tasks Menu the Generate Scripts Option. 2) Using the pop-up Dialog Box make sure that the check box Script All Objects in the selected Databases is not enabled and hit the Next Button. 3) Set all the Elements on the List to a False Value except the ones that follow: a. Include IF NOT EXISTS , set to TRUE b. Script Owner, set to TRUE c. Script of SQL Version, set to SQL SERVER 2000 d. Script foreign keys, set to TRUE e. Script Triggers, set to TRUE Then Hit the Next button 4) After finishing reading the Elements of the Database, a new list will appear at the Dialog Box. Make sure that you select ONLY THE TABLES of the Database and hit the Next Button. 5) At the screen that follows hit the Select All button and the Next. 6) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end. 7) Click Finish Button.
After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Foreign Keys and the Triggers of the Database will be created.
After these steps the database should be fully functional under the SQL Server 2000 edition.
Hi, Can anyone tell me how i can access an application or open tables in MS sql server that are in MS Access. Its urgent and any help will be appriaciated.
We are planning to migrate from SQL Server 2000 to SQL Server 2005.
I am trying to connect to the SQL Server 2000 using SQL Upgrade Advisor. But I am getting the following error whenever I click on the detect button after entering the server name:
Upgrade Advisor could not detect SQL Server component on the server.
The Network Path was not found.(mscorlib)
Please let me know if any of you have faced the same issue.
I have to 2 database server, one is running in SQL Server 2005 and the other one is SQL Server 2000. Now, I want to transfer my database from SQL Server 2005 to SQL SERVER 2000 and gives error. Any solution?
Hello all, We have a SQL database that uses a MS Access projectfront-end, which I am now creating an ASP.Net application for (And am completely new to so may be missing some foundation concepts). With the Access project I added Active Directory groups to the database to use for permissions, logins, etc. For the ASP app I have it set to use Forms authentication with Active Directory (ActiveDirectoryMembershipProvider). I can login to the app with the AD usernames/passwords but the connection from ASP.Net to SQL is using the ASP User. Is there a way to provide the authentication from the ActiveDirectoryMembership through to the SQL server, so the user: bobuser would be authenticated to SQL instead of ASPNETUSER?Or do I need to have the ASP.net app handle the permissions of which SP's a user can execute?Any help or suggestions are appreciated.Thanks,Jamie.
I have recently transfered data from MS Access to MS SQL Server 2000 and created user interface in MS Access Project (ADP). Now I need to secure the data on server and want to access it ONLY through interface I created in MS Access. Every user has to log in the application (user id and password). Users may have the same access rights to the server (so maybe just one account or whatever I need for all of them). Can anyone advice me how to set it up? To access sql server from MS Access I am using ADO.
Certain people in our company want to use Crystal Reports for data-processing. Problem is we want them to NOT be able to access data in databases within the live server.
Given that we use trusted connection to validate all kind of data-access, I am wondering if there is a way so that (via NT administration or via some SQL Server security features) the live-server can refuse any connection request from the Crystal Reports application. At the moment the same group of people are allowed access to database (and should remain to be so) on this "live" server via some other applications (e.g. Microsoft Access).
Hi there,I have written an application which uses MS Access for it's database engine.Due to the large size which the database has become I have decided that itwould be sensible to use SQL Server with the application instead.I am an extreme SQL Server newbie so I am not really sure what I'm doingyet! I have successfully downloaded and installed the MS SQLDE 2000 andservice pack 3.What do I need to do next? Ideally I would like to convert the existingAccess database to MS SQL Server format. Also I would like to know if it ispossible to create an SQL Server database from scratch using a guienvironment similar to Access and if so which software (preferably free) doI need to achieve this?Many thanks,Clive.
I have a ASP.NET time sheet management application I designed - today I put it live on the webserver and recreated the database on my SQL 2000 server. When I attempt to view, all of the pages where I query the database I catch the error "SQL Server does not exist or access denied".
The concerning part of this is that I have tested the live database from my development PC (remote - offsite) and it works fine. This shows that the database that I brought over is intact and the user has the appropriate permissions. The only thing that I changed in the application when I copied it to the server was the connection string server name to reflect the location change.
The setup is as follows:
Development PC on a broadband connection running Windows 2003 server enterprise, SQL Server 2000 SP3a, Visual Studio 2003 Enterprise.
Web/Database/Email server running Windows 2003 Standard, SQL Server 2000 SP3a, Exchange 2003, Active Directory, DNS etc.
When I run the application on the Web server from a browser with local names ie. http:\SERVER1application I get the "SQL Server does not exist or access denied" errors. When I try to connect to the application on the web server from my development PC i get the same problem.
When I changed the connection string on my development PC to the webserver/dbserver it runs fine.
I don't understand how a local proccess is having difficulty authenticating to the database when I can access it remotely just fine.
My database knowledge are with MySql and Oracle, but recently I was asked to evaluate the migration of an existing (and maybe more) from ms access to sql server. My question is simple, if all of the sql are hard coded into the code ... how well this sql will work, I mean is the sql between access and sql server are plug'n'play ? However in any case, I always rewiew all of the sql.
