Some time ago I saw a section in sql 2005 BOL describing the t-sql features that have changed from sql 2000 to sql 2005. For example, some NULL handling and that "*=" is no longer supported. Can anyone find that refernece for me?
TIA,
Barkingdog
Two questions for you all...
1. Has anyone found a good software that does database documentation that is fairly priced and pretty much fully automatic. Something to document tables,views,stored procedures and so on...
2. Have any of you found a paper or better yet a chart that shows what is new to sql server 2005 that wasn't in 2000 and also more importantly what was available in 2000 that will not be supported in 2005?
Thanks
Can anyone point me in the direction of some NON-sales documentation on the differences between these product? I am sure, especially with Express, there are considerable functionality and architecture differences.
I've looked in BOL, and I've done searches online.
All i seem to get is sales related stuff.
I'm curious about the architecture of SQL 2005, SQL express. In 2000, there was some fairly detailed documentaiton on this subject, but 2005 BOL seems REALLY diffucult to find things.
I may just need to try different keywords...
A stored procedure takes an IN parameter, an INOUT parameter, and returns an OUT parameter. When this stored procedure is defined in SQL Server 2000, the JDBC DatabaseMetadata method getProcedureColumns() returns three rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1)
one for the INOUT parameter (COLUMN_TYPE=2), and
one for OUT parameter (COLUMN_TYPE=5).However, when the same stored procedure is defined in SQL Server 2005 (SP2), the getProcedureColumns() method returns only two rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1)
one for the INOUT parameter (COLUMN_TYPE=2).No row for the OUT parameter is returned.
jTDS JDBC driver was used for both of the above tests. When the Microsoft JDBC Driver for 2005 was used against SQL Server 2005, the same behavior (only two rows in the resultset) was observed.
Has someone else run into such a problem? Is this a bug in SQL Server 2005 because the same jTDS driver works as expected against SQL Server 2000 but not against SQL Server 2005? Any feedback will be appreciated.
Thanks,
-- Damodar Periwal
What system stored procedures have changed between SQL 2000 and SQL 2005?
View 4 Replies View RelatedHello,
I have a project where I have to upgrade SQL 2000 to 2005. When we are upgrading we are going to move to new server so server name will change. Most of our applications have a hard coded connection string. Applications are developed with Classic ASP, .NET 1.1 and 2.0 as well.
Now the question is what is the best way to do this? Changing all the connection string will be a very painful task as we have many applications.
Can anyone suggest a way where we can achieve this without changing all the connection strings?
Thanks in advance.
Tareq
what are some of the issues that we might face while upgrading from SQl 2000 to SQl 2005? Actually I have to create upgrade plan for path from MS 2000 to MS 2005.
Any suggestions will be appreciated.
Thanks
Hi,
I was wondering if anyone could point me in the right direction for upgrading from SQL 2000 to SQL 2005?
I was under the impression that it was as easy as following an upgrade path after popping in the CD, but I realized it's not.
I'm a Systems Administrator who has installed both version in stand alone systems, but never tried an upgrade.
Thanks for your help.
Hello,
I would like to upgrade sql server 2000 to sql 2005. So Can I install the sql server 2005 on the same server where sql 2000 is already running and then use the database upgrade wizard. Or should I install the sql server 2005 on a different server.
Thanks
I have a sql 2000/2005 side-by-side install on a single server. I right-click a sql 2000 database, select All Tasks, and the Copy Database to upgrade a sql 2000 database. In the "Copy Database Wizard" I see the message:
"To use the detach and attach method SQL Server Agent must run under an Integration Servicers Proxy account that can access the file system of both the source and destination servers."
1. What does that mean?
2. I decided to click "Finish" in the wizard, and the database copy seemed to work fine. Whaterver 1) means does, by default, the SQL Server Agent already run under this "Integration Services Proxy" account. (Rememberr the upgrade process did work).
TIA,
barkingdong
Hey Guys,
Looking for some type of solution on this issue. I'm going from SQL2000 to SQL2005 and I run the upgrade advisory tool and all and I'm going through the procedures of getting our SQL server updated to SQL2005, when I attempt to run that analyzer to take a look at how well the migration will go. I get a timed out error message on the fallowing.
ORDERBYORDINALS
FTCATPROPITEMCOUNT
OLDSYSTEMTABLES
DROPSYSOBJ
HOSTIDRETURN
FTPROPS
QRYGOVCOSTLIMIT
FTCATALOGNAMERESTRICTION
SPCONFIGURE
INVALIDCHAR
OPENXMLWITH
XPAGENTPROXY
UPDATETEXT
SPHELPTRIGGERNEWCOL
DISKINIT
SYSPERFINFO
DEPRECATEDSP
OLDDBCC
ORDERBYALIAS
Exact error message and its always the same for each entry.
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"
Any help in this are would be greatly appreciated, all other issues have been resolved I'm just stuck on this and I don't want to upgrade until the advisory tool gives me the ok to.....
Thanks in advance.
All,
I am looking for upgrading SQL 2000 databases to SQL 2005.Has anybody got a detailed plan /Documents for doing so?.
Thanks and Regards
Avijit Macker
I am in the beginning of upgrading a SQL 2000 DB on a Small business server to a 64 bit 2005 DB on a new box. Is my best method of upgrading to use the "copy" DB function in 2005? If so, what do I need to do to install the management tools on the 2000 box - since this is a 32 bit edition, and I have the 64 bit media?
View 1 Replies View Related
I got a requirement to upgrade only One SQL 2000 to SQL 2005 server. i did check that there are only databases in this server along with Linked server. I am looking for a best practice document for SQL upgrade and any thoughts or experience any one has faced during upgrade.
Other question is what about if there is a DTS on the server. right now on this particular server there is no DTS but in coming weeks i may need to upgrade other server which is having DTS as well. I have worked alot on SSIS packages but want to see if DTS will be converted to SSIS packages or i will have to manually do it.
Thanks
Hi,
We have 2 .net 1.1 applications using Microsoft SQL Server 2000 Enterprise Edition. To connect to database we are using nHibernate 0.6 for one application and Microsoft Enterprise Library Data Blocks Ver 1.0 in another.
We are planning to upgrade SQL servers of both applications to SQL 2005 Enterprise edition and retain .net 1.1 framework. Are nHibernate 0.6 and Microsoft Enterprise Library 1.0 data blocks compatible with the SQL 2005? Are any upgrades required for these as well?
We ran SQL upgrade advisor report on these applications, there weren't any changes to table/ stored procs suggested. Could that mean that the existing table design and stored procs are compatible with SQL 2005?
Thanks in Advance,
Abhijith Bharadwaj
Hi all,
I just ran sql upgrade advisor and got the following message:
"You can use SQL Server 2005 tools to edit your existing DTS packages. However, upgrading or
uninstalling the last instance of SQL Server 2000 on a computer removes the components required
to support this feature. You can retain or restore these components by installing the special
Web download, €œSQL Server 2000 DTS Designer Components,€? from the Microsoft Download Center
before or after you upgrade or uninstall SQL Server 2000."
I do have several DTS packages in my sql server 2000 database. does that mean if I upgrade my current 2000 database to 2005, those dts will be gone. But I can download sql server 2000 dts deginer components on our new sql 2005 database server before I upgrade. Then when I restore my sql 2000 database on our new 2005 server, eveything would be OK? I am not sure if my understanding is correct.
My advisor is on my local computer and I am running analysis against a remote database(on our intranet),€œSQL Server 2000 DTS Designer Components,€? should be intalled on my local computer or remote database server? what steps I should take in order to retain my dts package or transform it into something else?
can you shed light on me?
Thanks.
Betty
I am going through upgrading our current installation of SQL 2000 to 2005. When I install 2005 it doesn't seem to upgrade the server, but only installs the server tools. Do I need to completely remove 2000 in order to upgrade? Thanks!
View 4 Replies View RelatedDoes SQL 2000 support nested tables? Also, how many columns does it support?
View 3 Replies View RelatedWhere can i find some reference material on how to upgrade to 2005 in a replicated environment please?
does anyone know what order i need to tackle the servers, is it likely to be the same as the Service Pack process?
cheers
Hi all,
Recently, I upgraded one of our database servers from SQL 2000 to SQL 2005. At the moment, all the databases are in 80 compatibility mode. I upgraded them based on the false pretenses that I could perform online reindexing right off the bat. Apparently, that isn't the case, and it requires switching to 90 compatibility. My question is if I switch to 90, will all indexes be invalidated/unusable? Or will I be able to switch to 90 and perform the online reindexing right away with no downtime?
We have a few 24/7 production VLDB's in the mix (one of which is on this newly upgraded server), and only reasonable downtime is possible. The largest window of downtime I have is about 10 PM - 5 AM with prior notice.
The upgrade was performed using SAN-based LUN's with a detach/reattach.
Thanks,
Matt
Hello
Server Details:
OS => Windows Server 2003
SQL => SQL Server 2005
Client Machines:
OS => WinXP sp2
SQL => 2000, 2005 express
The developers at work connect remotely to the server. Some of them have the "client SQL 2005 express" and others are using "SQL 2000"
Over the next week I plan to upgrade all the developers to 2005 Standard.
I have performed an upgrade of '2005 express' to '2005 standard' and encountered no problems. However I have no experience upgrading the 2000 client.
Can anyone suggest a strategy (fresh install/upgrade)or highlight any pitfalls that I should avoid.
Thanks in advance for any help/suggestions offered.
Regards
Toby
Initially, the articles I read suggested that doing an "in place" upgrade is the simplest path, with the drawback that if something goes wrong, I'll have to re-install 2000. And that migrating is more difficult since I'll have to recreate all the logins , security, replication ....
Then I saw a series of posts saying migration is the best ... backup & restore (or detach, reattach) the DBs to a new instance of SQL 2005. What about the systems DBs, such as MSDB and the DTS packages ?? Also, If I migrate to a new instance of SQL, how will I re-establish the connections from other servers ? They are replicating or DTS-ing to ServerA, but now SQL2005 is on ServerASQL2005 (Same server, different instance.
In the scale of things, we have fairly simple environments, although our Production database is almost 300 Gig.
If I install a new instance of SQL 2005 on my SQL 2000 server, and migrate everything from 2000 to 2005, I now have 2 instances: ServerA and ServerASQL2005. All my other servers are referencing the original instance name of ServerA. How do I resolve that problem so that communication between the other servers will still work ??
I've installed a test instance of SQL 2000 on a test box. I want to try the In-Place upgrade to 2005. I installed 2005 on the test box also, but have been unable to see how to perform the upgrade. I thought running 2005 setup again would prompt me for an instance name .... but it just tells me 2005 is already installed. I must be missing something simple.
View 5 Replies View RelatedGood Morning!
I am beginning the process of moving SQL 2000 databases to SQL 2005 cluster.
Has anyone already did this and is there anything I should know?
We plan on leaving the SQL 2000 database up during this process and granting read only at the time.
I am nervous about this as this is a high profile database in our dept.
I don't even know if I know where to begin!
Any suggestions are truely welcome!
Thanks,
/P
hi all,
i have written a small checklist for my upgrades of databases from 2000 to 2005 servers. I was hoping you can take a look and see if im missing something.
TIA
1.Create backup of 2000 Database to a file group
a.Full Backup
b.Overwrite Existing Media Set
c.To File with Extension .BAK
d.Locate backup on DEV @ X:SqlServer_DataMSSQL2K5BACKUP
e.Make sure Backup File Name has _for_2k5_migration.BAK at the end
2.Create Database on SQL Server 2005 System on DEV
a.MYSERVERDEVELOPMENT2K5
b.Data files go to : X:SqlServer_DataMSSQL2K5DATA
c.Log files go to : X:SqlServer_DataMSSQL2K5LOG
d.Set Compatibility to 80 (SQL SERVER 2000)
3.Restore Backup to 2005 From File
a.Options Tab – Check Overwrite existing database
b.Once Restore is completed set compatibility mode to 90 (Sql Server 2005)
4.Script all Logins to 2K5
a.Go into 2000 server Security -> Logins
b.Script as CREATE TO Clipboard
c.Open query on 2K5 DB
d.Paste and run (Database must be installed on 2K5 first)
i have not done anything about maintenece plans - is this okay as it is or am i forgetting / not knowing anything else!
i am upgrading sql server 2000 database to sql server 2005 database.
I am using backup and restore method to upgrade.
i have an issues transferring the logins..
what is the best why to upgrade?????any info is appreciated..
Hello,
First off, thanks to all for taking the time to read and respond to this question of mine. Greatly appreciated.
Let me explain my current scenario followed with the question. We currently have our VB application written and working at various client sites on SQL 2000. We did received a number of request on upgrading to SQL 2005. We did some preliminary testing in-house on SQL 2005 (backed up the 2000 database and restored on 2005 which worked just great). It is also my hope that technically, everything should work just fine since 2005 is backward compatible to 2000.
Is anybody aware of any issues with taking a 2000 database and getting it to work on SQL 2005 without any mods?
Thanks.
Hi Friends, I am trying to Upgrade my Sql server 2000 Enterprise edition with SP4 to Sql server 2005 Enterprise edition (Inplace upgrade). The Sql 2000 machine was just now installed and applied with SP4 and has only system databases I want to try configuring Log Shipping in sql 2000 and then perform some testing by upgrading it to sql 2005.........so thats why i went for sql 2000.The OS is Windows 2003 server with SP1 and has sufficient hard disk space. I knew that it is possible to perform Upgrade from Sql 2000 SP3 or later (Enterprise edition) to Sql 2005 Enterprise edition. But when I run the sql 2005 Enterprise edition Setup, I provide the instance name in the dialog box! and beneath it there is "Installed instances" box is present if i click it, it shows the version and edition of my sql 2000 instance as 8.00.194 which is nothing but Sql 2000 RTM version. Hence I am unable to proceed with the installation as it says to refer the version and edition upgrades are not matching as per the requirements........But if i open the query analyser for that instance and type
Code Block
Select @@Version or Select Serverproperty('Productlevel') it displays the o/p as SP4Also in the error log it shows as 8.02039 which is Sql 2000 SP4 I am a bit confused even though sp4 is installed i am unable to perform the upgrade ? Feel free to provide your valuable suggestionsadvice if any ?RegardsDeepak
Dear All..
I have a few databases running a sql2000 and the time as come to move them onto sql2005. Which would be the best route to take.
If I can think back to older version it was just a simply job of doing a backup in it's current version then restore in the new version? and upgrade was taken while restoring.. I could be wrong but any help !!
Thanks
Martin.
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
These are the steps i plan on taking.
1. sp_help_revlogin gets logins sql 2000
2. Backup from SQL 2000 to SQL 205
3. Change database to 90 mode
4. Change database to check sum and auto stats sync
5. Rebuild Indexes on databases
6. Jobs (i will manually move over)
Anything else i should run after the rebuilds
Thanks
I am upgrading a sql server 2000 with sp4 (I have double and triple checked the sp) on a windows 2003 sp1 server and I keep getting the following error:
Name: Microsoft SQL Server 2000
Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Service pack requirement check:
Your upgrade is blocked because of service pack requirements. To proceed, apply the required service pack and then rerun SQL Server Setup. For more information about upgrade support, see the Version and Edition Upgrades topic in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
I cannot find anything saying that I need any other service pack other than sp4 for sql serer 2000. Any one ever run into this or have a clue what might be causing the installer to think I have the wrong service pack?
Hello all,
My name is Elizabeth. I'm currently trying to upgrade our Reporting Server to version 2005. The SRS that's currently installed on our CRM server is version 2000, and we want to upgrade that to 2005 so that we can use VS 2005 to write new reports (since SRS 2000 is not compatible with VS 2005).
So I followed the steps as on this link http://technet.microsoft.com/en-us/library/ms144267.aspx
However, I'm stuck at step# 11. As it says on the link: "If a component has a check box that is unavailable, the component does not qualify for upgrade to SQL Server 2005." I did get something like below; the check box is unavailable.
When I click on the "Detail" button on that menu, I get a message like below:
If I click on "Next" anyway from the menu, I get this error message:
I'm just wondering why it doesn't let me upgrade to 2005. The current version of SRS that's installed in our CRM server is Microsoft SQL Server 2000 Reporting Service Standard Edition. According to Microsoft link http://technet.microsoft.com/en-us/library/ms143393.aspx that edition should be upgradable.
It just crossed my mind actually; could it be because our Window Server is only using Service Pack 2, while according to that link the server has to use SP3 version or later. Please clarify me if this is the case.
Could you please help me on this one? I really appreciate it!! Thanks in advanced!!
-Elizabeth K.