Stack Overflow
Stack Overflow

Questions tagged [sql-server-2000]

Use this tag for questions specific to the 2000 version of Microsoft's SQL Server. Note that as of April 9, 2013, Microsoft no longer supports this version of SQL Server, to the point that even security patches are no longer created.

SQL Server 2000 (codename Shiloh, version 8.0), released in September 2000, is the successor to SQL Server 7.0.

2591 questions
35
votes
7 answers

WHERE IS NULL, IS NOT NULL or NO WHERE clause depending on SQL Server parameter value

I have a stored procedure in SQL Server 2000 that performs a search based on parameter values. For one of the parameters passed in, I need a different WHERE clause depending on its value - the problem is that the 3 values would be where MyColumn…
Russ Cam
  • 117,566
  • 29
  • 193
  • 253
34
votes
6 answers

What is the syntax to drop a Stored Procedure in SQL Server 2000?

Simple question, as the title suggests: What is the syntax to drop a Stored Procedure (SP) in SQL Server 2000, by first checking that the SP exists? Please provide the full code.
Saajid Ismail
  • 7,121
  • 10
  • 43
  • 55
32
votes
11 answers

How do you deal with transport-level errors in SqlConnection?

Every now and then in a high volume .NET application, you might see this exception when you try to execute a query: System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. According to my…
Eric Z Beard
  • 35,488
  • 25
  • 97
  • 144
32
votes
1 answer

Restore a SQL Server 2000 backup on SQL Server 2012

I have got following error message while restoring a backup from SQL Server 2000 to the latest version. I do not know the old version, now I am using SQL Server 2012 (11.0.3128.0). The error message is: Msg 3169, Level 16, State 1, Server FF101,…
user2053420
31
votes
5 answers

t-sql replace on text field

I have hit a classic problem of needing to do a string replace on a text field in an sql 2000 database. This could either be an update over a whole column or a single field I'm not fussy. I have found a few examples of how to use updatetext to…
PeteT
  • 16,600
  • 26
  • 87
  • 132
30
votes
6 answers

TSQL - Is it possible to define the sort order?

Is it possible to define a sort order for the returned results? I would like the sort order to be 'orange' 'apple' 'strawberry' not ascending or descending. I know ORDER BY can do ASC or DESC but is there a DEFINED('orange', 'apple', 'strawberry')…
Justin808
  • 19,126
  • 41
  • 143
  • 241
29
votes
2 answers

T-SQL List Tables, Columns

In T-SQL (SQL Server 2000). How can I list all tables and columns in a database? Also, in a separate query is there a way to list all columns along with data type and constraints (NULLS, etc). Thanks.
user1804387
  • 339
  • 1
  • 3
  • 8
26
votes
5 answers

Most efficient method to detect column change in MS SQL Server

Our system runs on SQL Server 2000, and we are in the process of preparing for an upgrade to SQL Server 2008. We have a lot of trigger code where we need to detect a change in a given column and then operate on that column if it has…
mwigdahl
  • 15,122
  • 7
  • 48
  • 62
26
votes
3 answers

SQL update query syntax with inner join

Can anyone find my error in this query? I'm using SQL Server 2000 and I want to update all entries in the CostEntry table to the corresponding value in the ActiveCostDetails table. The where clause DOES work with a select statement. UPDATE…
MAW74656
  • 3,229
  • 20
  • 69
  • 114
26
votes
3 answers

SQL Server: How to make server check all its check constraints?

It seems that some scripts generated by Enterprise Manager* (or not, it doesn't matter) created check constraints WITH NOCHECK. Now when anyone modifies the table, SQL Server is stumbling across failed check constraints, and throwing errors. Can i…
Ian Boyd
  • 220,884
  • 228
  • 805
  • 1,125
24
votes
7 answers

How do I join the first row of a subquery?

I've got a table of invoices and a child table of related data related by key. In particular, for each invoice, I'm interested in only the first related row from the child table. Given that I want the one related row for every invoice key - how do…
BenAlabaster
  • 36,299
  • 19
  • 100
  • 147
23
votes
2 answers

What is this operand (*= star-equals) in SQL server 2000?

I have a query that I pulled from ms sql 2000 and plugged into a MySql query. It did not work, MySql would choke on the *= operator. In this example I have two varchar columns called person_name. SELECT * FROM tbl1 a, tbl2 b WHERE a.id = b.id AND…
Blaise Swanwick
  • 1,675
  • 1
  • 16
  • 18
22
votes
2 answers

How to catch the output of a DBCC-Statement in a temptable

I tried the following on SQL-Server: create table #TmpLOGSPACE( DatabaseName varchar(100) , LOGSIZE_MB decimal(18, 9) , LOGSPACE_USED decimal(18, 9) , LOGSTATUS decimal(18, 9)) insert #TmpLOGSPACE(DatabaseName, LOGSIZE_MB, LOGSPACE_USED,…
Ice
  • 1,112
  • 3
  • 12
  • 27
22
votes
8 answers

How do I limit a LEFT JOIN to the 1st result in SQL Server?

I have a bit of SQL that is almost doing what I want it to do. I'm working with three tables, a Users, UserPhoneNumbers and UserPhoneNumberTypes. I'm trying to get a list of users with their phone numbers for an export. The database itself is old…
Justin808
  • 19,126
  • 41
  • 143
  • 241
22
votes
6 answers

Why does SQL Server 2000 treat SELECT test.* and SELECT t.est.* the same?

I butter-fingered a query in SQL Server 2000 and added a period in the middle of the table name: SELECT t.est.* FROM test Instead of: SELECT test.* FROM test And the query still executed perfectly. Even SELECT t.e.st.* FROM test executes without…
Chris Van Opstal
  • 33,129
  • 8
  • 70
  • 89
1 2
3
…
99 100