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.
2,588
questions
3268
votes
45
answers
3.8m
views
How to 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?
- 33.8k
1433
votes
30
answers
1.9m
views
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 ...
- 22.6k
945
votes
10
answers
850k
views
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-...
- 252k
413
votes
21
answers
532k
views
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 ...
- 32.7k
163
votes
23
answers
502k
views
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....
- 2,617
157
votes
19
answers
93k
views
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 ...
- 6,000
146
votes
19
answers
549k
views
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
- 11.9k
137
votes
5
answers
465k
views
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 ...
- 1,529
133
votes
4
answers
231k
views
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, ...
- 30.2k
112
votes
10
answers
156k
views
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.
- 3,159
110
votes
8
answers
63k
views
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]) ...
- 5,091
106
votes
1
answer
81k
views
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 ...
99
votes
14
answers
104k
views
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, ...
- 11.6k
90
votes
7
answers
119k
views
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:
...
- 2,657
86
votes
6
answers
210k
views
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 ...
- 2,753
86
votes
7
answers
374k
views
SQL where datetime column equals today's date?
How can I get the records from a db where created date is today's date?
SELECT [Title], [Firstname], [Surname], [Company_name], [Interest]
FROM [dbo].[EXTRANET_users]
WHERE DATE(Submission_date) = ...
- 29k
85
votes
7
answers
193k
views
SQL Server 2000: How to exit a stored procedure?
How can I exit in the middle of a stored procedure?
I have a stored procedure where I want to bail out early (while trying to debug it). I've tried calling RETURN and RAISERROR, and the sp keeps on ...
- 252k
77
votes
2
answers
138k
views
How can I insert binary file data into a binary SQL field using a simple insert statement?
I have a SQL Server 2000 with a table containing an image column.
How do I insert the binary data of a file into that column by specifying the path of the file?
CREATE TABLE Files
(
FileId int,
...
- 28.1k
75
votes
7
answers
178k
views
Get the time of a datetime using T-SQL
How can I get the time for a given datetime value?
I have a datetime in database like this:
2010-09-06 17:07:28.170
and want only the time portion:
17:07:28.170
Is there a function for that or ...
- 12.5k
75
votes
5
answers
160k
views
Is there a way to get a list of all current temporary tables in SQL Server?
I realize that temporary tables are session/connection bound and not visible or accessible out of the session/connection.
I have a long running stored procedure that creates temporary tables at ...
- 1,461
67
votes
9
answers
82k
views
ORDER BY DATE showing NULLS first then most recent dates
I have a stored procedure which executes a select statement. I would like my results ordered by a date field and display all records with NULL dates first and then the most recent dates.
The ...
- 3,198
63
votes
3
answers
48k
views
How to fix the embedded text qualifier issue while exporting data to CSV flat file?
###RFC 4180:
RFC 4180 defines Common Format and MIME Type for Comma-Separated Values (CSV) Files. One of the requirements of the RFC 4180 is stated as below. This is the point #7 in the RFC link.
If ...
user756519
61
votes
8
answers
88k
views
How to output a boolean in T-SQL based on the content of a column?
I made a view to abstract columns of different tables and pre-filter and pre-sort them. There is one column whose content I don't care about but I need to know whether the content is null or not. So ...
- 4,357
54
votes
4
answers
51k
views
How do I select a 1 as a bit in a sql-server view?
I want to create a view in which I select something like the following:
select id, name, 1 as active
from users
However, I want the active field, which I am creating in the select statement (it ...
- 22.1k
54
votes
4
answers
150k
views
MSSQL Select statement with incremental integer column... not from a table
I need, if possible, a t-sql query that, returning the values from an arbitrary table, also returns a incremental integer column with value = 1 for the first row, 2 for the second, and so on.
This ...
- 4,375
52
votes
8
answers
413k
views
SQL get the last date time record [duplicate]
I'm trying to get the last datetime record from a table that happens to store multiple status.
My table looks like so:
+---------+------------------------+-------+
|filename |Dates |...
- 1,167
51
votes
4
answers
110k
views
Select column, if blank select from another
How does one detect whether a field is blank (not null) and then select another field if it is?
What I really need is a IsBlank function that works the same as IsNull but with with blanks.
REPLACE ...
- 16.4k
50
votes
3
answers
257k
views
INSERT INTO @TABLE EXEC @query with SQL Server 2000
Is it true that SQL Server 2000, you can not insert into a table variable using exec?
I tried this script and got an error message:
EXECUTE cannot be used as a source when inserting into a table ...
- 899
43
votes
6
answers
114k
views
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.
- 8,209
39
votes
3
answers
9k
views
What exactly does the T-SQL "LineNo" reserved word do?
I was writing a query against a table today on a SQL Server 2000 box, and while writing the query in Query Analyzer, to my surprise I noticed the word LineNo was converted to blue text.
It appears ...
37
votes
2
answers
76k
views
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). ...
- 519
36
votes
7
answers
199k
views
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 ...
- 125k
34
votes
3
answers
107k
views
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 ...
- 3,497
34
votes
5
answers
59k
views
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 ...
- 19k
34
votes
1
answer
59k
views
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 ...
user2053420
33
votes
11
answers
28k
views
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 ...
- 38.1k
31
votes
6
answers
32k
views
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 ...
- 21.3k
26
votes
3
answers
27k
views
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 ...
- 252k
26
votes
5
answers
73k
views
How do you return a constant from an sql statement?
How do I return a constant from an sql statement?
For example how would I change the code below so "my message" would return if my (boolean expression) was true
if (my boolean expression)
"my ...
- 12.3k
26
votes
5
answers
87k
views
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 ...
- 16.4k
26
votes
7
answers
44k
views
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 ...
- 39.5k
25
votes
8
answers
57k
views
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 ...
- 21.3k
24
votes
2
answers
64k
views
How can I query the list of database roles in a SQL Server 2000 database?
In Sql Server 2000, is it possible to return, via SQL query, a complete list of database roles that exist in a given database?
I know it is possible to see these roles by expanding the Security, ...
- 438
24
votes
4
answers
145k
views
Joining tables from different servers
Any suggestions how to join tables from different servers in stored procedure?
- 4,213
23
votes
4
answers
90k
views
Check if record exists, if yes "update" if not "insert"
I want to check table PREMIUM_SERVICE_USER if any records exists for
strClientID update timeValid for +30 if no records for strClientID insert to premium_service_user table.
What am I doing wrong?
...
- 5,392
23
votes
2
answers
18k
views
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.
...
- 1,745
22
votes
4
answers
15k
views
Schema, Owner for objects in MS SQL
By default, objects (tables, stored procedures, etc) are set up with the dbo owner/schema (I think ms sql 2000 calls it owner, while ms sql 2005 calls it schema)
The owner/schema is really a role or ...
- 45.6k
22
votes
2
answers
14k
views
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 #...
- 1,162
22
votes
6
answers
675
views
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....
- 37.1k
21
votes
10
answers
75k
views
how to get the next autoincrement value in sql
I am creating a winform application in c#.and using sql database.
I have one table, employee_master, which has columns like Id, name, address and phone no. Id is auto increment and all other ...
- 451