This tag is specifically for the 2000 version of Microsoft SQL Server
13
votes
4answers
2k views
How can I tell WHY an insert on a certain table is slow?
I know that an INSERT on a SQL table can be slow for any number of reasons:
Existence of INSERT TRIGGERs on the table
Lots of enforced constraints that have to be checked (usually foreign keys)
Page ...
11
votes
3answers
787 views
Should I migrate data using detach/copy/attach or through backup-restore-replay?
I am about to embark on migrating database files to a new SAN (from an old SAN) abd I have a couple of options to implement this. (1) It was suggested that I look into the level of effort of restoring ...
9
votes
6answers
1k views
Suddenly Slow Execution Plan for Stored Proc
I'm trying to understand an issue we're having with SQL Server 2000. We are a moderately transactional website and we have a stored proc called sp_GetCurrentTransactions which accepts a customerID, ...
9
votes
1answer
332 views
Are there reasons why I should not set my db owner to [sa]?
Yesterday I asked this question regarding changing the dbo of multiple databases that I have. The change makes sense, but I want to be clear.
Is there any, good reason or circumstance why I should ...
8
votes
3answers
1k views
How to delay SQL Server startup
When we boot SQL Server, SAN drives might not be availiable yet. Is there a way to delay SQL Server start by 1 minute? Right now we have to restart SQL Server after we boot the server.
8
votes
2answers
2k views
How do you reset the sa password?
I lost the sa password on a machine, and when I log in to the machine directly using an account in the admin group, SQL Server Management Studio will not allow me to log in using Windows ...
7
votes
1answer
330 views
Transactional replication from SQL Server 2000 to 2008
I have legacy databases on two SQL Server 2000 servers that I am trying to replicate to a SQL Server 2008 R2 database using transactional replication.
The 2008 server is not in the same LAN, so I am ...
7
votes
2answers
6k views
Could not continue scan with NOLOCK due to data movement
We run SQL Server 2000 and we get a few of these errors every night.
Could not continue scan with NOLOCK due to data movement
The query that throws this error is a large complex query that joins ...
6
votes
4answers
422 views
How can I tell if my SQL Server DB performance is hardware-limited?
Testing an app currently under single-user load - as the test data has increased to production sizes (400k-2M rows per table), some SELECT sp's are not quite fast enough anymore (with limited test ...
6
votes
1answer
162 views
Is there a quickish, straighforward way to measure Transactions Per Second on SQL Server 2000?
The sys.dm_os_performance_counters DMV introduced in SQL Server 2005 makes this task somewhat trivial. However, my Google-Fu has been blocked in finding a way to do this in SQL Server 2000.
How can ...
6
votes
1answer
2k views
SQL Server 2000 : List and Disable all triggers
I have to do a database cleanup for one of our databases on a sql server 2000 environment. The cleanup basically includes the disabling of all triggers, deletion of all old data, enabling of the ...
5
votes
2answers
2k views
Can I connect to a SQL Server 2000 instance from SQL Server Management Studio 2012?
I would like to update my development machine to SQL Server 2012 but I still manage some (very old) SQL Server 2000 machines. Wil my 2012 SSMS be able to connect to those SQL Server 2000 machines?
...
5
votes
1answer
663 views
Migrate DTS Jobs to 2008 Server
We have been running SQL Server 2000 for years.
Finally, at long last, we have an SQL 2008 Server being shipped in.
We have many DTS jobs on our SQL 2000 Server that need to be migrated to the SQL ...
5
votes
2answers
119 views
querying a table of events for reporting
The background (masking a real-world problem behind a word problem)
Farmer Brown has a peach orchard in California and ships hundreds of
thousands of peaches all across the country. When a shipment ...
5
votes
2answers
229 views
Optimizing IP Range Search?
I have a simple query and table, and I would like to know which indexing is efficient on this kind of table and query.
In my table I have 3 columns
CREATE TABLE mYTable(ipFrom BIGINT, ipto BIGINT, ...
5
votes
2answers
113 views
Thought about this SQL Server backup plan?
I just started a new job, and I'm reviewing the database maintenance plan. I've got quite a bit of experience writing SQL, but not much experience with DB administration. My last job was at a large ...
4
votes
2answers
79 views
Why do linked servers have a limitation of 10 branches in a CASE expression?
Why does this CASE expression:
SELECT CASE column
WHEN 'a' THEN '1'
WHEN 'b' THEN '2'
... c -> i
WHEN 'j' THEN '10'
WHEN 'k' THEN '11'
END [col]
...
4
votes
2answers
197 views
What is an advantage or disadvantage for not upgreading database server?
I have a legacy database server. The person who wrote it is long gone. There is no documentation at all just some comments in the code. Luckily, it is not that complicate but if I want to fix it ...
4
votes
1answer
193 views
hostname incorrect in sysprocesses
I'm running SQL Server 2000. While doing some profiling to trace activity, I noticed some hostnames showing up that were mysterious -- names that shouldn't exist on the network anymore. I got the MAC ...
4
votes
4answers
573 views
SQL Server script to delete accounts no longer in Active Directory
We have a SQL Server 2000 that will shortly be migrated to SQL Server 2005. It has years of Windows Authentication accounts created that no longer exist in Active Directory, which prevent the Copy ...
4
votes
3answers
5k views
Upgrade process from SQL Server 2000 to SQL Server 2012
I have a SQL Server 2000 database that I would like to upgrade to SQL Server 2012 (running on a different server).
I don't think there is a direct upgrade path, but if there is, what is it?
My ...
4
votes
4answers
5k views
SQL performance issues with remote query across linked server
This sproc
create proc dbo.Get_Accounts as
begin
declare @current_date datetime
set @current_date = dbo.fn_currdate()
select [fields]
into dbo.current_accounts
from ...
4
votes
5answers
340 views
Improving SQL Server performance on VMware under Linux
I have a Linux server in a small company (3 users) acting as a domain server and running two VMs under VMware: one has SQL Server 2000 on it and one has SQL Server 2005, in each case running on ...
4
votes
2answers
1k views
SQL 2000 - tsql to find out failed jobs in last 24 hours
I want to find out which sql agent jobs failed in last 24 hours...
How can I find it using TSQL for SQL 2000?
Regards
Manjot
4
votes
2answers
456 views
Log Reader replication agents are constantly restarting
I am recreating (push) transactional replication from a SQL 2000 instance to a SQL 2008 instance which I had to break in order to do a db migration. The 2008 instance is both the distributor and a ...
4
votes
1answer
95 views
Possible pitfalls of condensing 2 SQL Server instances into 1
I currently have 2 SQL Server 2000 database instances (each running on a different physical server), each containing around 12 databases (not all are active). We are planning an upgrade to SQL Server ...
4
votes
1answer
119 views
Copy a SQL Server 2000 database to another box without interrupting the backup chain
I have a small-but-important MS SQL Server 2000 database that I'd like to copy to a development box. I understand that the usual way to do this is to make a copy of a full backup file, and restore ...
4
votes
3answers
152 views
How to monitor how much data is transfered in a linked server?
I have a linked server to a remote computer and doing some insert, update and delete operations between local and remote databases.
I want to know how much data is transfered when operation is doing. ...
3
votes
4answers
178 views
Query without having to specify table's schema
I imported a bunch of tables from SQL Server 2000 to my 2008 database. All the imported tables are prefixed with my username eg: erpadmin.tablename.
In the table properties it lists 'erpadmin' as the ...
3
votes
2answers
3k views
How to migrate from SQL Server 2000 to SQL Server 2008 R2 Express Edition
I have been trying to migrate a database from SQL Server 2000 (part of SBS 2003) to SQL Server 2008 R2 Express Edition
I naively tried using the SQL Server Export and Import wizard. This has worked ...
3
votes
2answers
71 views
Finding longest matching prefix
I have two tables:
Sources:
(SName) (SId)
Apple1 100
Apple2 200
Banks 300
BanksB 400
Bankerly 500
Prefixes:
(PName) (PId)
App 1
Bank 2
Banker 3
My goal: To find the longest ...
3
votes
2answers
449 views
Installed SQL Server 2000 with wrong collation.. how to correct for entire server instance?
I installed SQL 2000 Developer (yes, old, don't ask) on my laptop ... with the wrong collation. Oops!
It's showing Latin1_General_CI_AS, but it turns out I need to use SQL_Latin1_General_CP1_CI_AS ...
3
votes
2answers
112 views
SQL Server how to detect unused views and/or get usage stats
Is there a way to determine in SQL Server if a view is no longer being used? (without removing it)
Ideally i would like to know usage for SQL Server 2000 as well as 2012 if that is possible.
I am ...
3
votes
5answers
126 views
Can SQL Server 2000 and SQL Server 2012 CoExist?
We have OLTP Databases on SQL Server 2000 on my client's Prod Server. Planning to create Data Warehouse on the same Server but use SQL Server 2012. Can I install without any issues?
3
votes
2answers
245 views
How group with min(date) and select an another column in the same table
I have a query who return something like it :
Name Gender Job date of hire
John M mechanic 2012-05-08
John M electrician 2010-01-01
Vicky F scientific 2012-11-11
Bob ...
3
votes
1answer
206 views
Can sql server 2000 and 2012 coexist on the same server?
I see that 2005/2008/2012 can all coexist but can't find anything on 2000 with 2012.
3
votes
2answers
75 views
SQL server 2000 transactions with the same time
I have a BIG problem here.
I'm creating a program that can be looping if the connection to the server is broken.
But when the program is running for a few months, I found a bug on SQL server 2000.
I ...
3
votes
1answer
52 views
Is there a behavior change between SQL Server 2000 and SQL Server 2008 R2 OPTION (FAST 1)?
This is question 2 of 2 related to the FAST query hint. Background Information (as an aside, I'm not trying to garner more rep, I'm trying to be true to only asking one question per question).
Our ...
3
votes
1answer
396 views
Using (NOLOCK) in a READ COMMITTED transaction block
We're using SQL Server 2000.
I'm wondering what the risks are if we use a NOLOCK hint within a READ COMMITTED transaction block, for example:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN ...
3
votes
1answer
465 views
SQL 2000 Maintenance Scripts
Hi I've stumbled into the administration of a Legacy SQL 2000 server that has had next to no maintenance for the past 8 years (and is very slow). As I have only been doing DBA / PT for the past 2 ...
3
votes
1answer
351 views
Where is this mystery trace going (SQL Server 2000)?
We had a sudden disk usage explosion this morning and the SQL monitor has no useful query information. I decided to check on any potential traces that could have captured something.
SELECT * FROM :: ...
3
votes
4answers
739 views
tempdb in sql server
Using SQL Server 2000, I am getting the following error.
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space."
How to solve it? I ...
3
votes
1answer
169 views
SQL Server 2008 upgrade advisor tool
We upgraded to SQL Server 2008 from SQL Server 2000. But due to some legacy scripts/queries in our health care application, that run only in 2000, we had to run 2008 version on backwards compatibility ...
3
votes
2answers
145 views
Cleaning up transaction replication del/ins/upd stored procedures?
I'm referring to the ones that start with:
dbo.sp_MSdel...
dbo.sp_MSins...
dbo.sp_MSupd...
I've noticed they are not removed when I run exec sp_dropsubscription on the Publisher then run ...
3
votes
1answer
544 views
recover broken msde 2000 8.0 database
i have a msde (mssql) 2000 / v8.0 database that has one corrupted table. Microsoft SQL Server Management Studio Express can see the database STRUCTURE, but not the data. i can see the DATA of the ...
3
votes
1answer
392 views
Rebuild or Reorganize indexes based on fragmentation
I am using an Index Defrag Script, which provides a nice way to rebuid or reorganize indexes based on fragmentation percentages. I like to rebuild my database indexes if their fragmentation is over ...
3
votes
2answers
607 views
xp_sendmail: failed with mail error 0x80004005
when I run xp_sendmail
exec master.dbo.xp_sendmail @recipients='me@hotmail.com',
@subject='terrrible thing happened',
@message='test'`
I am getting an error
Msg 18025, Level 16, State 1, ...
3
votes
1answer
311 views
Unable to start MSSQL and SQLSERVERAGENT in Windows Server 2003
We are using MICROSOFT SQL SERVER 2000 Enterprise Edition. Our main problem we cant start MSSQL and SQLSERVERAGENT services.
Heres the Error log :
...
3
votes
1answer
475 views
Script to Restore/Create SQL Server 2000 Database from Another Database's Backup
I'm trying to create a database, NewClientDB, by restoring another database's, OldClientDB, backup, d:...oldclientdb.bak. I've always done this via SQL 2000 Enterprise Manager, but the GUI keeps ...
3
votes
2answers
493 views
Query execution showing error as login failed
I want to retrive the table values from other server
Query
EXEC sp_addlinkedserver @server = 'OPSERVER', @srvproduct = 'SQL Server'
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'OPSERVER', @rmtuser = ...
