In sql2000 EM when you click on the database name, it doesn't display database attributes, table sizes, row count etc.. Is there a way to display this information like in sql 7.o How do you customize that..
Hi , We are using the a 4 CPU SQL Server 2000 Enterprise edition for our application , We want to migrate to SQL2005 with the compatibility level downgraded to SQL2000. If we go for the SQL2005 Standard edition is there any problem? We are not using the Failover clustering in our server.
Hi I have new bought the SQL server 2005 enterprise, but it have 2 CDs, so what are the differences between CD1 and CD2? and so which one should i install first? or is it necessary to install both two or just need to install one of them? And about my original sql 2000 database, can i just attach it's MDF file into the sql 2005 engine, or which import wizard can load the sql 2000 MDF into sql 2005? or do i need to keep the sql 2000 engine before do this? thx
i have sql2000 & sql2005 on the same machine. I am unable to register my localhost in sql2000, get an access denied error. How can I make my localhost use sql2000 database?
i am in the process of Migrating SQL 2000 to my new SQL2000 server i want to know the what would the best way for me to migrate one SQL server to another SQL server on the same network and rename the new server to the old server and bring it up for use in our ecommerce website.
We are migration few database from SQL 2000 Enterprise Server to the SQL 2005 Enterprise Server (64 bit-Itanium Server)
Problem :
- The performance difference observer was not much (Just 15-18%)
- The CPU Utilization goes to 100% immediately, when the software login screen appears and it sustains in between 90% to 100%
- Multi-User Access (3 or more concurrent users), the sql performance is tremendously slow.
Steps followed for Migration:
- We have used the backup and restore method to migrate the data from SQL 2000 Enterprise Server to the SQL 2005 Enterprise Server (64 bit-Itanium Server).
- We ran the DBCC DBREINDEX for each affected tables
- We executed the sp_updatestats procedure for all the tables
- Upgraded the DB compatibility version to 90
- Installed the SQL 2005 Enterprise Server (64 bit-Itanium Server) Service pack 2.
One thing that we observed, was the memory usage, on SQL 2000 the sqlservr.exe uses around 1.5 GB where as on the SQL 2005 its just 120 MB.
A normal select query on 2000 Server took 11-12 sec, having 10 lac records, and the same query on 2005 took 1 min (3-5 sec CPU process taken by sqlservr and rest by the Sever Management Studio.
The only differance noted was that on 2000 the entire data was displayed at one go where as in 2005 the data was displayed at the 1st sec, and was filling the rest in the buffer, and to finally display entire data it took 1 min.
Also
when we run this query
select * from sys.dm_os_sys_info
one column os_priority_class shows a value = 32 ? what does this means.
In the MSSQLSERVER SERVICES, properties, there is a parameter displaying value
Advanced > Running Under 64 bit OS = No ? what does this means.
Could anyone pls suggest as to how to increase the sqlservr.exe memory area, and also how the enhance the performance.
Hi,I have made a web application using SQL Server 2005 Express with a few admin pages that require login. I used the SQL Server Managment Studio Express to setup users and groups and it works fine. But the web server has SQL Server 2000 and all I get is a user id and password to access the database. It seems I cannot use the Login control I put on my "login.aspx" page becuase it uses the integrated authentication which chokes on 2000. I am afraid I wonlt be able to use some of the 2.0 features such as profiles, ... Is this the way 2.0 works with SQL Server 2000? Should I ditch the ISP if they only give me a single access to database with no provisions to create users. (I was told I had to create a "Users" table and manually assign ids and passwords and that I can only use the user id and password they supplied to setup the connection string in the script.)I appreciate your help.
I am developing a Crystal Reports App in VS2005 (VB). I was originally working on an XP machine with MSDE and stored procedures where it worked fine. I then transferred the development to a Win2003 machine with SQL2000 and am now getting the following error; Failed to open a rowset. Description: 'Get_Calls_By_MLO_Date' expects parameter '@DT1' which was not supplied. Plus some other error detail. Here is the SP. CREATE PROCEDURE Get_Calls_By_MLO_By_Date @DT1 datetime, @DT2 datetimeASSELECT MLO, CallNo, DT, Type FROM ActionsWHERE dt >= @DT1 AND dt <= @DT2 AND ActionID=1 ORDER BY MLO, DTGO If I transfer everything back to the XP machine it works fine. Any ideas? Thanks Terry.
How to lock a Row in SQL2000 so that nobody can select that row. I applied ROWLOCK, but i am not finding the way. My query is "SELECT * FROM tablename WITH (ROWLOCK)" Is this the correct way to write locks. I would be thankful if u help me
I am exporting data to an Excel file via a scheduled DTS package. I need to be able to either overwrite the existing Excel file or delete it. I haven't found a way to do this yet. Any help, comments, or direction will be appreciated. TIA Paul
We're beginning to migrate our 6.5 DB's to SQL2000. A question came up regarding naming conventions for stored procedures. In BOL it says that for SQL2000:
"Stored procedures with the prefix sp_ are first looked up in master. If a user-defined stored procedure has the same name as a system-supplied stored procedure residing in master, SQL Server always finds the system-supplied stored procedure. "
Behavior in 6.5 was the opposite: SQL Server searched the current database followed by a search in master.
We started several months ago renaming user stored procedures to "usp_XXX", but we still have many non-system sp's that still use "sp_". I'm looking for opinions of whether we should bite the bullet now and rename all our non-system sp's at this time; prior to moving our production environment to SQL2000. Will we see a performance gain since SQL will not have to hit master first before going to the current DB?
When bcp'ing in data into an sql 6.5 table that is NOT nullable, records in the text file that contain nulls are ommited, but valid records without nulls are inserted. However, using exactly the same table structure and files on a newly installed sql 2000 server (sp2), as soon as the first invalid record in the text file (i.e. with a null value)is encountered, bcp'ing is terminated and NO records are inserted. I can't find any option in bcp/sql 2000 to allow me to carry out a load as before. The help files state that the default no. of invalid records allowed is 10 (both versions sql), and explicitly setting the -m option of bcp to 10 still doesn't work. Most of the data i receive has at least one duff record Is there an option in sql 2000 that allows me to have x number of duff records inserted before a full rollsback occurs?
I can't find anything in technet, msdn or books online.
Does SQL 2000 standard edition support replication if it would be configured as a subscriber. Is SQL 2000 Enterprise Edition required for a Publisher and Distributor or can you get away with standard edition. In a web site environment how many CALs are needed when users access the database via webservers and middleware.
I am experimenting with the FOR XML AUTO clause in SQL2000, but instead of nicely structured XML I get one continuous string oftags that can't be parsed by IE5.5
The BOL seem to indicate just running the query in Query Analyser will produce nicely formatted XML output - can anybody tell me what is wrong?
I need some strong points for the management to convince them to upgrade to sql2000 now than waiting for 6 months. We are currently in process of developing this new product. All development is on sql7. I want to upgrade our database to sql2000 now rather than waiting after the release which might be in another 3 months.
I crashed my HP portable that has SQL2000 ENT installed and I re-installed XP-Pro +SP1 and when I install SQL2000 ENT it refused to install saying ENT cannot install in this O/S. Did I miss anything ?
I went and checked another XP-Pro W/S and SQL2000 is intalled OK.
Is it possible to fire a update trigger from SQL6.5 table to update a SQL2000 server database table field value? (upgrade this 'legacy' to sql2k now is not an option)
If it is not possible, is there a way to accomplish it?
Hi, I noticed that when I installed SQL2000,with "typical install", the default, the following will be used: SQL collation (dictionary order, case-insensitive, assent sensitive) sp_helpsort will give SQL_Latin1_General_CP1_CI_AI
Is there any difference if I choose the "custom install", then and choose the windows locale which gives the result of sp_helpsort: Latin1_General_CI_AI
SQL_Latin1_General_CP1_CI_AI is supposed for backward compatibilty, so are they actually equivalent ?? Any impact or difference we have to be aware of ?? Anyone knows..Thx
I will be upgrading my SQL7 Servers (SP1) to SQL2000 and NT4 to WIN2000 this year. Unfortunately, I will not be able to get new boxes to do this, so I will have to do the upgrades on the systems I have. Our network people have not yet developed a plan for the WIN2K upgrade of our entire network. I will have to upgrade the SQL Servers before they have their plan together. If I want, I can upgrade the SQL Servers to WIN2K before the rest of the network. Assuming that the WIN2K upgrade and the SQL Server upgrade can not be done at the same time, which would be the better order (and why), upgrading to WIN2K and staying at SQL7 first or upgrading to SQL2000 and staying at NT4? The primary goal is to get the indexed views to improve performance on reports and queries.
Why would something perform better on SQL 7.0 than SQL 2000?
The system runs fine for about 4 hours, then we will experience across the board timeouts. After stopping and re-starting the SQL Server service, performance is great.
Microsoft is telling us we need more clustered indexes (the database IS poorly tuned, but SQL 7.0 didn't seem to have a problem without them).
I use ISA2000 +SQL2000. All log write to datebase in SQL. I need help. I want delete all records old 40 days. I run scripts {delete from MyTable where LogTime < DateAdd(d, -40, GetDate())}, but my trunsaction log state more 1G and task stop with error. How i can resolve this problem???