What Is Your Strategy For Upgrade An Access Db To SQL Server 2000?
Jun 22, 2006
This question probably has been asked many a time. And yet I feel it
is still relevant for one thing a search on this NG does not produce a
desirable answer.
It is kind of disappointing that MS would not be able to transfer ER
relationship from an Access db to a SQL Server 7/2000-based one, the
upgraded db/imported tables sitting on the SQL Server would not have
PKs, say, you have 100 user tables, you have to first recreate PKs for
each of them then set up relationship between/among them, quite time
consuming. Do you have a better way?
Along the same line of the task, what options out there for converting
Access Modules into SQL Server-based Stored Procedures and/or UDFs?
The manual option is sure there, third party tool? I wouldn't trust
them that much though.
TIA.
View 6 Replies
ADVERTISEMENT
Sep 29, 2006
Hello there,
We have about a dozen SQL server 2000 Enterprise Edition servers in house. Our goal is to set up a cluster SQL server 2005 and consodiate the existing dozen servers to a few servers for easy manage and maintainence. So there are 3 things that we want to accomplish:
1. upgrade to SQL server 2005,
2. Consolidate existing servers
3. Make a cluster server to get high availability
But I'm sure what's the right order to acheive them. To upgrade each server to 2005 and then move them to cluster server? or set up the cluster server in 2005 and restore existing dbs to the cluster server. upgrade first or cluster first? upgrade first or consolidate first? pros and cons? upgrade or backup/restore? What do you recommend? We have lots of stored procedures, views and triggers, DTS packages and some replications. Any insight will be greatly appreciated.
-Jessie
View 2 Replies
View Related
Jul 12, 2006
Hi
I would like to get some advise on the capacity of concurrent users on Access 2000. Here is my situation:
- I have a vb6 application that connects to one database(currently is a access 2000 mdb) via Adodc .
- There will be 6-8 copies of this application running on different PCs and that database is shared .
- All 6 PCs read/write to that database concurrently.
I wonder would Access 2000 be a reliable choice?
Thanks for your time
View 5 Replies
View Related
Dec 1, 2005
Probably this question has been asked hundreds of times and yet netsearch has not generated satisfactory enough answer, at least, to me.And OK, let's assume your organization has more than 200 employees,just one measure to indicate that it's not small and data processingneeds are quite extensive (for both OLTP and OLAP).We've heard so much about concurrency support, stability andperformance. Are there any real persuasive paper out there to talkabout it? Now, let me also put it in another perspective, say, you'rea Microsoft sql server sales guy or gal for that matter for newaccounts. What you got?Thanks.
View 5 Replies
View Related
Jan 23, 2004
I cannot connect to the remote SQL Server 2000 running on this machine that has just been upgraded to the W2003 server. Everything worked perfectly before the upgrade on W2000. All the logins, firewall setting stayed the same. Can ping the machine.
But when trying to register/link, or connect thru ODBC, I got the error
SQL server does not exist or access denied.
We don't have SQL server SP3 installed.
ola
View 2 Replies
View Related
Feb 24, 2005
Hello, i have a question that the sql server 2000 is install in window 2000 server. If i want to update to window 2003. Is that any problem in sql server 2000. I am worry about whether we will have problem after update. What i need to do? Many thanks.
View 5 Replies
View Related
Apr 6, 2006
I'm currently upgrading from Access to Sql Server, and I have a broken query that I cannot find a fix for.
The original query:
SELECT DISTINCT DownloadedNames.*FROM DownloadedSkims INNER JOIN (DownloadedNames INNER JOIN DownloadedInfo ON DownloadedNames.DNID = DownloadedInfo.DIDnID) ON DownloadedSkims.DM_ID = DownloadedInfo.DI_DM_IDWHERE DM_ID=23 AND (dWeek BETWEEN '6/26/2006' AND '9/18/2006')ORDER BY DIID, dWeek
I get the error "Msg 145, Level 15, State 1, Line 2
ORDER BY items must appear in the select list if SELECT DISTINCT is specified" when I test the query.
If anyone knows what changes I can make to the query, you'd really be helping me out.
Chris
View 1 Replies
View Related
Jun 6, 2006
Hi,
first time poster/newbie here.
I've
got a football (soccer for the yanks!) predictions league website that
is driven by and Access database. It basically calculates points
scored for a user getting certain predictions correct. This is the URL:
http://www.pool-predictions.co.uk/home/index.asp
There
are two sections of the site however that have almost ground to halt
now that more users have registered throught the season. The players
section and league table section have gone progressively slower to load
throughout the year and almost taking 2 minutes to load.
http://www.pool-predictions.co.uk/home/players.asp?tab=a_d
http://www.pool-predictions.co.uk/home/table.asp
All
the calculations are performed in the Access database Ive written and
there are Access SQL queries to get the data out.
My
question is, is how can I speed the bloody thing up! ! Somone has
alos suggested to me that I use stored procedures and SQL Server to
speed things up? Ive never used SQL Server before so I am bit scared
about using it (Im only a hobbyist), and I dont even know what a SP is
or does. How easy will it be upgrading the whole thing to SQL Server
and will it be worth the hassle, bearing in mind I expect my userbase
to keep growing? Do SP help speed things up significantly? Would
appreciate some advice!
Thanks in advance,
John.
View 1 Replies
View Related
Sep 10, 2001
the first 2 parts install but the installation fails on the MSDE installation.
It gves me an error like about invalid installation media or something?
I have sql server enterprise.
plz help
View 2 Replies
View Related
Jan 7, 2004
I'm running the SQL 2000 upgrade wizard and this error came up in the final phases of the upgrade to SQL 2000. The phase is doing the Export Import of the Table data:
Error: Couldn't connect to the ADMIN$ share on the export machine. Verify that the MSSQLServer Service uses a NT Domain account that is part of the Administrator's group of the export machine and that both machines are in the same domain.
I've checked and service account are in the local admin group and both are in the same resource domain. The service account is in the accounts domain.
I have SQL server 6.5 with service pack 5a. Another machine with W2k and SQL server 2000 with SP3 and disables cnv6x70.dll.
Thanks
View 3 Replies
View Related
Feb 18, 2004
Hello,
We've some of the boxes are SQL Server 7.0 with 65 compatability and SQL Server 7.0 with 70 compatability. Now we are planing to upgrade to SQL 2000 with 80 compatability.
My question, how much complicated it is?
Is there any code changes to do in Stored Procs or Triggers or other? and what are the differences?
If lot of code changes, is there any tool to scan stored procedures?
(As we've more 5000 Stored Procedures)
If my question is not clear, let me know.
Thanks!
View 2 Replies
View Related
Nov 7, 2006
I have:
Server x with windows 2000 server sp4
It was SQL 2000 STD, when it was originally installed.
Now is SQL 2000 Personal edition SP4.
We dont know how it became sql 2000 personal edition.
We are assuming that an administrator had to re-install SQL and used the wrong CD.
Anybody know of any problems I can find when trying to upgrade to SQL 2000 STD??
Should I un-install and then re-install?
View 1 Replies
View Related
Feb 9, 2001
I would like to upgrade my database from 7 to 2000, are there any white papers or guide i can read. Or dose anyone have advise they can give?
Thanks
View 1 Replies
View Related
Dec 14, 2001
Hi,
I run into a problem which I cannot connect to the database using SQL Server 2000 Enterprise Manager. The SQL Server 2000 was working well until I hook a SQL Server 7.0 application to it. The error message: "General OLE Error 16386, You must upgrade your SQL Enterprise Manager & SQL-DMO (SQLOLE) to SQL Server 2000 (SQLDMO) to connect to this server". I checked fixed in the sp1 for SQL Server 2000, and found no entry. In MS support website and found nothing. Need help from someone who has experience on this. Thanks in Advance.
Eugene
View 1 Replies
View Related
Apr 2, 2008
We're upgrading a SQL Server 2000 cluster (Active/Passive) running on Windows 2003 Server 32 - bit Standard to a SQL Server 2005 Cluster running on Windows Server 2003 64-bit Enterprise. Our existent cluster's databases are residing on SAN. We can't purchase new hardware and we have no spare hardware. We also need to move from Windows 2003 32-bit Server to Windows 2003 64-bit Enterprise Server at the same time. We want to keep downtime to a bare minimum.
What we were thinking was the following steps... Anyone try this?
1. Break the link between the servers. Or should we just evict the passive node?
2. Install a fresh copy of windows 2003 64-bit server on one side along with SQL Server 2005. While this step is running, the active node would still be live on Windows 2003 32-bit Server and SQL Server 2000 serving our customers.
3. Bring the active server down.
4. Create new cluster on the newly upgraded server and assign the same cluster name and IP as the original one.
5 Bring the luns from SAN to the newly upgraded server and initialize SQL Upgrade
6. As a final step, the old active node will be rebuilt, we would install a fresh copy of windows 2003 64 - bit server on it and sql server 2005. At this point we would bring it back into the cluster and the cluster would be complete again.
Thoughts?
View 1 Replies
View Related
Jan 31, 2006
I am currently using sql server 2000 and I have just installed sql server 2005 in a separate box that will be used as the production server so I will be upgrading to the new database server soon.
I was reading the upgrade options for this situation and one option is to detach the 2000 database, copy the mdf and log files to the new server and attach it to mssql 2005. Another option is to recreate the tables, views, etc. and export/import the data to the new server.
I would like to ask what the best option is for this situation because I am not sure what the advantages and disadvantages of just detaching/attaching and recreating the database in sql server 2005. If I just detach/attach the database, will there be any disadvantage in the performance because the database files were created in 2000 and is functioning because of backward compatibility in 2005? Would it be better to recreate the database in 2005 and import the data from 2000 so that the database would be running in a way that is designed for 2005?
I am not really sure of the differences of these upgrade options so any ideas would be greatly appreciated.
Thanks in Advance
-Voltaire
View 7 Replies
View Related
Jul 19, 2006
Hello, I have sql server 2000 personal edition on my PC and I have just got a 2005 DVD. I tried to install it but it is not working. I don't know how to manage my databases. As if there is no graphical managemnt tool. The 2005 is also Personal edition.
I reinstalled the sql 2000, and now I'm trying t upgrade to sql 2005, but still don't know how to do that.
Please someone help me!!!!!!!!!!
View 1 Replies
View Related
Sep 13, 2006
I have a production failover cluster running SQL Server 2000 at SP3that I want to upgrade to SP4. I do not have a test failover clusterto test with so I need the install on the primary server to work thefirst time. Per the information I have I just install the patch on theprimary server and it will install both on the primary and on thesecondary.However, I remember when I did the initial install and it failed. Thefirst problem traced to the fact that the install uses temporary filesunder the profile of the installing administrator and the id had neverlogged into the second server so the install failed on creating thetemporary file. The was a second problem that related to an OS featurethat had to be off for the install to work.If anyone out there has done this upgrade and remembers encountering aproblem and its fix/workaround I would appreciate a head up warning.Thank you-- Mark D Powell --
View 1 Replies
View Related
Mar 4, 2008
I've inherited 6 sql server 2000 boxes. I've upgrade 3 with no trouble. now I have one that gives the message :
"your upgrade is blocked because of cross-language compatibility rules.
For more information about cross-language support, see the version..."
all servers are 2003, sp2. All SQL is Enterprise edition, default language is "english". even the collation order is
the same. why the cross language message? Any help would be good else I'll be forced to rebuild the DB and load application again. ugly!!
dave
View 3 Replies
View Related
Jan 21, 2008
Hi,
We have a product which uses SQL Server 2000 Enterprise Edition with Per Seat licensing option. Going forward with a new release of the product, we intend to upgrade to SQL Server 2005. Till now our product was being used in non web based scenarios so the Per Seat licensing option helped our case. In the upcoming release which would use SQL Server 2005 we need to break off from the non web based scenario as the product would use SQL Server over internet.
So isn't it correct to go for the Processor based licensing instead of the Per seat licensing ?
If so then what would be the ideal manner to upgrade the license from Per Seat (in SQL Server 2000) license type to Processor based (in SQL Server 2005) license type ?
I have heard of Software Assurance providing assistance in version upgrades. Would it help in case I need to upgrade from one license type to another ? Or is it applicable only in case of upgrade where the license type is fixed ?
Thanks in advance.
Cheers,
Arun
View 1 Replies
View Related
Aug 20, 2007
Hi all,
My question might be a little bit unusual, but is it possible to upgrade SQL 2005 Express (the free version of SQL) to SQL 2000 Server without any loss of functionality?
Where do I have to pay attention to when doing this?
Thanks in advance!
MMQ
View 1 Replies
View Related
Apr 17, 2008
Hi All,
Currently we use SQL 2K SP4 and snapshot replication with a Central Publisher with Remote Distributor toplogy.
I am looking to upgrade or migrate our SQL servers to SQL 2005 and was wondering what is the best way to do this for our replicated architecture?
Is the best way to run the SQL 2005 Upgrade on all 3 servers (publisher, distributor, subscriber) and should it automatically upgrade the servers including the replication components? Is there anything i should consider/watch out for when doing the upgrade and it involves replication (namely snapshot replication)?
Thanks in advance.
View 1 Replies
View Related
Jun 12, 2007
I would like to upgrade a production active/passive SQL Server 2000 cluster to SQL Server 2005.
I've read all of the documentation I could find, and doing an in-place upgrade seems to be the way to go. (Despite the fact it scares the hell out of me.
But throughout all the documentation, I've yet to see some simple and fairly important questions answered.
How does the process of upgrading SQL Server work when you're dealing with a cluster?
Do you upgrade one machine then the other? If so, do you upgrade the active node first, or the passive node?
What happens if you're forced to failover from one instance to the other before you've had a chance to upgrade both to 2005? In other words, you failover from 2005 to 2000. Would that even work?
What happens if your upgrades fails for some reason? Is it easy to rollback the installation? If not, will reinstalling SQL Server 2000 cause any issues? Will it remember the previous configuration and simply fix the broken install?
Any help/insight would be greatly appreciated.
View 8 Replies
View Related
May 14, 2007
In our application we use the ADODB.Recordset.
In SQL server 2000
If there was a view that joined 2 tables and I accessed the view the 2 ID fields in the view would still have the AutoIncrement attribute still set to true so that I knew those were Identity fields.
In SQL server 2005
I dont' know why but if you reference a View that has Identiy AutoInc fields in ADO it doesn't keep those properties.
Also for whatever reason we Set the ID field to 0 to let ourselves know its a new Record. SQL 2000 let it happen and assumed it to be null where as By Setting the ID to 0 in SQL 2005 causes it to blow up on me.
Is there some sort of setting in SQL that can make SQL 2005 work like SQL 2000 in these two instances...
View 1 Replies
View Related
Aug 9, 2004
Hello,
I recently upgraded my MSDE server to SQL Server 2000 database. After the upgrade, I see, every minute,in the logfile that the database is starting whilst in Enterprise manager the database is up and running. I would be grateful if you could bail me out of this anomaly.
Hope to hear from you soon.
Regards,
Albert.
View 2 Replies
View Related
Feb 28, 2006
Hi,
I'm trying to upgrade from SQL Server 2000 to 2005. The problem I am having is that when I try to attach the existing db files I get a message that says "database cannot be upgraded because it is read only or has read only files...."
Thing is... there is no write protection on the files.
Can anyone advise me on how to overcome this problem so that I can attach the db, please?
Thank you
Robert
View 25 Replies
View Related
Jul 23, 2005
Hi,Simple question: A customer has an application using Access 2000frontend and SQL Server 2000 backend. Data connection is over ODBC.There are almost 250 concurrent users and is growing. Have theysqueezed everything out of Access? Should the move to a VB.Net frontendtaken place ages ago?CheersMike
View 4 Replies
View Related
May 27, 2008
Parameter
Access 2000/XP
SQL Server 7.0
SQL Server 2000
MSDE 2000
Number of instances per server
n/a
n/a
16
16
Number of databases per instance / server
n/a
32,767
32,767
32,767
Number of objects per database
32,768
2,147,483,647
2,147,483,647
2,147,483,647
Number of users per database
n/a
16,379
16,379
16,379
Number of roles per database
n/a
16,367
16,367
16,367
Overall size of database (excluding logs)
2 GB
1,048,516 TB
1,048,516 TB
2 GB
Number of columns per table
255
1024
1024
1024
Number of rows per table
limited by storage
limited by storage
limited by storage
limited by storage
Number of bytes per row
(Excluding TEXT/MEMO/IMAGE/OLE)
2 KB
8 KB
8 KB
8 KB
Number of columns per query
255
4,096
4,096
4,096
Number of tables per query
32
256
256
256
Size of procedure / query
64 KB
250 MB
250 MB
250 MB
Number of input params per procedure / query
199
1,024
2,100
2,100
Size of SQL statement / batch
64 KB
64 KB
64 KB
64 KB
Depth of subquery nesting
50
32
32
32
Number of indexes per table
32
250 (1 clustered)
250 (1 clustered)
250 (1 clustered)
Number of columns per index
10
16
16
16
Number of characters per object name
64
128
128
128
Number of concurrent user connections
255
32,767
32,767
5
View 1 Replies
View Related
Jul 23, 2005
I've created a small company database where the tables reside in a SQLServer database. I'm using Access 2000 forms for a front end.I've got a System DSN set-up to SQL Server and am using links withinAccess 2000 to get to the SQL Server tables.My forms worked fine until I made a few minor changes to the databaseschema on SQL Server (e.g. added a foreign key, or added a column).After that, all the links break - I click on a table link and get anerror msg like "invalid object name."Deleting the links after a schema change and re-adding the links seemedto fix the problem. The forms I'd already created seemed to work fineafter re-creating the links.But then I got more advanced with my forms. I have it set up so thatfor certain entry fields, the combobox gets populated with values froma table (the description appears in the drop-down and the correspondingprimary key value gets populated in the table). I created a number offorms using this technique, entered data, and everything worked fine.Made a small schema change and it broke everything -- not the actualtable links, but the functionality for the drop-downs. My values nolonger appeared, and this was true for forms that accessed tables whoseschemas did not change.This is driving me nuts. Is there any way to keep my forms frombreaking each time I make a small schema change?Thanks.- Dana
View 5 Replies
View Related
Oct 26, 2006
We have 3 MSDE databases (3 seperate servers) which we need to make into one Sql 2000 server (new server). How is this done? Can't find anything clear about this.
Thanks
View 3 Replies
View Related
Nov 29, 2007
Hi.
We are going to upgrade one of our SQL Server 2000/Win200 instance to SQL Server 2005/Win2003. Currently, the SQL Server 2000 which contains about 30 database. This is my plan:
Install SQL Server 2005/Win2003 on a new instance
Backup SQL Server 2000 database instance (how do I back all 30 database at the same time?)
Then do a Restore from the SQL Server 2005 instanceIs this a good method or using the "Copy Database Wizard" is better method?
I'm new to this so please advice? Thanks!
View 14 Replies
View Related
Aug 14, 2006
I have upgraded our test server and on non cluster production machine. My next server is a cluster and I don't have a test cluster server. Do I upgrade the active node and that is all or am I going to need to fail it over and upgrade the other node.
Also the prerequesite for the upgrade advisor is .net 2.0 I am going to up that on each server one at a time but I am only going to add the upgrade advisor to one node is that ok.
I read the SP1 documentation and it clearly states only patch the active node and do not patch the inactive nodes.
View 1 Replies
View Related
Mar 15, 2007
I used the SQL Server 2005 Upgrade Advisor to upgrade from SQL Server 2000 Enterprise to 2005 Standard. The only complaint I got concerned DTS packages, but I had none anyway. When I open SQL Server Management Studio, I can run queries, but they're against tables in the old 2000 databases. The SQL engine and Server agent are version version 8.0. Not surprising that new TSQL statements like 'BACKUP SERVICE MASTER KEY TO FILE' won't work.
Do I have to uninstall my previous version before upgrading?
Thanks.
View 3 Replies
View Related
