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
2949
votes
41 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
  • 30,547
  • 7
  • 22
  • 33
1244
votes
29 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. When you Google for the answer, you get so many different answers. Is there an official/backward and forward compatible…
Vincent
  • 20,510
  • 16
  • 54
  • 61
825
votes
12 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
  • 220,884
  • 228
  • 805
  • 1,125
353
votes
21 answers

How do I generate random number for each row in a TSQL 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
  • 29,993
  • 30
  • 102
  • 160
145
votes
18 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,850
  • 4
  • 22
  • 21
138
votes
22 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,337
  • 2
  • 18
  • 17
120
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
  • 10,888
  • 47
  • 141
  • 225
116
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…
Nick
  • 1,299
  • 4
  • 13
  • 10
107
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
  • 28,429
  • 17
  • 89
  • 155
102
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
  • 2,977
  • 17
  • 38
  • 62
101
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,294
  • 12
  • 57
  • 95
88
votes
9 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
  • 4,601
  • 8
  • 41
  • 54
75
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
  • 10,673
  • 22
  • 73
  • 106
69
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,344
  • 10
  • 49
  • 79
68
votes
13 answers

How to find column names for all tables in all databases in SQL Server

I want to find all column names in all tables in all databases. Is there a query that can do that for me? The database is Microsoft SQL Server 2000.
Salman A
  • 229,425
  • 77
  • 398
  • 489
1
2 3
…
99 100