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.

2588 questions
3223
votes
44 answers

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?
Mathias
  • 33,351
  • 7
  • 26
  • 33
1412
votes
30 answers

Check if table exists in SQL Server

I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statements. Here are two possible ways of doing it. Which one is the standard/best way of doing it? First way: IF EXISTS (SELECT 1 …
Vincent
  • 22,366
  • 18
  • 58
  • 61
934
votes
10 answers

How to Join to first row

I'll use a concrete, but hypothetical, example. Each Order normally has only one line item: Orders: OrderGUID OrderNumber ========= ============ {FFB2...} STL-7442-1 {3EC6...} MPT-9931-8A LineItems: LineItemGUID Order ID Quantity …
Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
407
votes
21 answers

How do I generate a random number for each row in a T-SQL select?

I need a different random number for each row in my table. The following seemingly obvious code uses the same random value for each row. SELECT table_name, RAND() magic_number FROM information_schema.tables I'd like to get an INT or a FLOAT out…
MatthewMartin
  • 32,326
  • 33
  • 105
  • 164
157
votes
23 answers

Find index of last occurrence of a sub-string using T-SQL

Is there a straightforward way of finding the index of the last occurrence of a string using SQL? I am using SQL Server 2000 right now. I basically need the functionality that the .NET System.String.LastIndexOf method provides. A little googling…
Raj
  • 2,557
  • 2
  • 19
  • 17
154
votes
19 answers

Can I protect against SQL injection by escaping single-quote and surrounding user input with single-quotes?

I realize that parameterized SQL queries is the optimal way to sanitize user input when building queries that contain user input, but I'm wondering what is wrong with taking user input and escaping any single quotes and surrounding the whole string…
Patrick
  • 5,970
  • 4
  • 24
  • 21
143
votes
19 answers

How to get the first and last date of the current year?

Using SQL Server 2000, how can I get the first and last date of the current year? Expected Output: 01/01/2012 and 31/12/2012
Gopal
  • 11,712
  • 52
  • 154
  • 229
133
votes
5 answers

Grant execute permission for a user on all stored procedures in database?

I generated script from old database, created a new database and imported all data from old database. So far so good, however, no user has execute rights for stored procedures. I know I can use GRANT EXECUTE ON [storedProcName] TO [userName] If it…
Nick
  • 1,489
  • 4
  • 14
  • 10
131
votes
4 answers

Is there a way to list open transactions on SQL Server 2000 database?

Does anyone know of any way to list open transactions on SQL Server 2000 database? I am aware that I can query the view sys.dm_tran_session_transactions on SQL 2005 (and later) database versions, however this is not available on SQL 2000.
James Wiseman
  • 29,946
  • 17
  • 95
  • 158
111
votes
10 answers

How do I drop a function if it already exists?

I know this must be simple, but how do I preface the creation of a function with a check to see if it already exists? If it exists, I want to drop and re-create it.
DavidStein
  • 3,149
  • 18
  • 41
  • 62
106
votes
8 answers

Is there a way to persist a variable across a go?

Is there a way to persist a variable across a go? Declare @bob as varchar(50); Set @bob = 'SweetDB'; GO USE @bob --- see note below GO INSERT INTO @bob.[dbo].[ProjectVersion] ([DB_Name], [Script]) VALUES (@bob,'1.2') See this SO question for the…
NitroxDM
  • 5,039
  • 10
  • 44
  • 56
104
votes
1 answer

Declaring a default constraint when creating a table

I am creating a new table in Microsoft SQL server 2000 by writing the code instead of using the GUI, I am trying to learn how to do it "the manual way". This is the code I am actually using, and it works fine: CREATE TABLE "attachments" ( …
Albireo
  • 10,977
  • 13
  • 62
  • 96
91
votes
14 answers

Is SQL IN bad for performance?

I have a query doing something like: SELECT FieldX, FieldY FROM A WHERE FieldW IN (108, 109, 113, 138, 146, 160, 307, 314, 370, 371, 441, 454 ,457, 458, 479, 480, 485, 488, 490, 492, 519, 523, 525, 534, 539, 543, 546, 547, 550, 564, 573, 629, 642,…
Victor Rodrigues
  • 11,353
  • 23
  • 75
  • 107
86
votes
7 answers

Get structure of temp table (like generate sql script) and clear temp table for current instance

How do I get structure of temp table then delete temp table. Is there a sp_helptext for temp tables? Finally is it possible to then delete temp table in same session or query window? Example: select * into #myTempTable -- creates a new temp…
RetroCoder
  • 2,597
  • 10
  • 52
  • 81
84
votes
6 answers

select a value where it doesn't exist in another table

I have two tables Table A: ID 1 2 3 4 Table B: ID 1 2 3 I have two requests: I want to select all rows in table A that table B doesn't have, which in this case is row 4. I want to delete all rows that table B doesn't have. I am using SQL Server…
Wai Wong
  • 2,671
  • 4
  • 21
  • 17
1
2 3
…
99 100