0
голосов
3ответов
1704 просмотров

sp_spaceused alternative in SQL 2000

Like we have alternative for sp_helpdb sp = sysdatabases table, Do we have an alternative table for sp_spaceused sp in SQL server 2000? Regards Manjot

2
голосов
3ответов
4733 просмотров

INSERT conflicted with foreign key - SQL Server bug?

UPDATE: the issue does not happen when run against SQL Server 2008. So this is something strange (or wrong) with SQL Server 2000. I try to do a simple insert on SQL Server 2000: INSERT INTO UserAddresses (UserId, AddressId) SELECT UserId, Id FROM Addresses and I get this: INSERT state...

0
голосов
1ответов
318 просмотров

How can I use Group By (instead of self-joins) to get correct data (specific example included)

Looking at the related questions, I don't think this specific question has been asked, so here goes. I had a situation where I joined on a table three times to get different data based on dates. This took too long, so in an effort to optimize, I rewrote it using a group by as defined here: http...

1
голосов
4ответов
6546 просмотров

SQL server table population source

I have an Audit database(created by someone else). Something is polulating it, with table sizes data (which makes sense as it is Audit database). The SQL server has too many jobs. I want to know what is populating the audit tables. Is there anything like sys.comments etc? which can tell me wh...

2
голосов
4ответов
6144 просмотров

How to save a single result set to a temp table from a SQL stored procedure returning multiple sets?

I need to store the result set of a stored procedure in a temporary table (using SQL Server 2000). From what I've read, this (poorly constructed example) should work: create table #tempTable (TempId int primary key, Column1 varchar(100), Column2 varchar(100), DateCreated datetime) insert into...

0
голосов
2ответов
186 просмотров

Value should be 0 between the two dates?

Using SQL Server 2000 I want to compare the table2.date between table1.from, table1.todate, if exist then value should be 0 (zero) Table1 ID FromDate ToDate 001 20090801 20090815 002 20090817 20090820 …, Table2 Id Date Value 001 20090730 100 001 20090731 200 001 20090801 300 001 20090802 ...

1
голосов
2ответов
1765 просмотров

Searching for data from one table in another table in t-sql (sql server2000)?

I have table A. This table does not have any PK, it just stores lots of rows, which can only be identified by combination of its column values. There is procedure that takes data from table A, and from other tables, does proper matching/processing and feeds table B. Now, how do I check if data fr...

0
голосов
4ответов
376 просмотров

How to skip date where holiday code = 1

Using SQL Server 2000 Table1 Date Holiday 23-02-2009 0 24-02-2009 1 25-02-2009 0 Table2 ID Date 01 23-02-2009 01 24-02-2009 02 25-02-2009 …, Here I want to skip the date where Holiday =1 Expected Output 01 23-02-2009 02 25-02-2009 How to make a query f...

0
голосов
2ответов
1512 просмотров

linked-server sql - access Problem

I have a SQL server 2000 and an Access database mdb connected by Linked server on the other hand I have a program in c # that updates data in a SQL table (Users) based data base access. When running my program returns the following error message: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' report...

0
голосов
2ответов
1764 просмотров

If record exists in database, UPDATE a single column

I have a bulk uploading object in place that is being used to bulk upload roughly 25-40 image files at a time. Each image is about 100-150 kb in size. During the upload, I've created a for each loop that takes the file name of the image (minus the file extension) to write it into a column name...

0
голосов
1ответов
110 просмотров

Compare with Date and ID problem

Table1 ID Date Intime Outtime A001 20000501 12:00:00 22:00:00 A001 20000502 14:00:00 22:00:00 A001 20000503 12:00:00 23:00:00 A002 20000501 11:00:00 20:00:00 A002 20000502 13:00:00 21:00:00 So on…, Table2 ID Date Intime Outtime A001 20050501 14:00:00 23:00:00 A002 20050501 08:00:...

0
голосов
1ответов
101 просмотров

Compare with Dates and ID?

Using SQL Server 2000 I want to get Table2.TimeIn Table2.TimeOut according to Table1.personid and also If Table1.Date = Table3.Date then it should take a Table3.TimeIn, Table3.TimeOut. 3 Tables Table1 ID Date 001 20090503 001 20090504 001 20090506 002 20090505 002 20090506 So on…...

0
голосов
3ответов
178 просмотров

Help with Recursive SQL

I have a category table similar to this: uid | ParentLevel | ParentID | Name ------------------------------------ 1 | 0 | 0 | foo 2 | 1 | 1 | blat 3 | 1 | 1 | baz 4 | 2 | 3 | blah 5 | 0 ...

0
голосов
2ответов
2957 просмотров

SQL Server 2000 DTS - Cannot resolve collation conflict for equal to operation

I have a SQL Server 2000 DTS package. One of the steps of this package has the following SQL: SELECT * FROM [Crocus_Limited$OrderRequestDetail] WHERE (rep_updated > GETDATE() -2) AND NOT EXISTS (SELECT OrderID FROM NavisionUpgrade.navision4.dbo.[WEBOrderDetails] rd WHERE rd.OrderID = ...

0
голосов
3ответов
116 просмотров

ID should not display between the specified date?

Using SQL Server 2000 Database1.Table1 ID Name Date title 001 Ravi 23-02-2009 Accountant 001 Ravi 24-02-2009 Accountant 001 Ravi 25-02-2009 Accountant 002 Ram 21-02-2009 Supervisors 002 Ram 25-02-2009 Supervisors 002 Ram 26-02-2009 Supervisors So on…, Database2.Table2 ID Name...

0
голосов
1ответов
432 просмотров

Saving sql server xml result to disk

I have a C# application that calls a stored procedure that produces an xml result (using FOR XML Explicit) that I want to save to disk. Doing some research I have found the following method: var data = new DataSet(); XmlReader reader = cmd.ExecuteXmlReader(); data.ReadXmlSchema(reader); data.Re...

0
голосов
1ответов
55 просмотров

Need Latest Event Of Certain Type Where Several Events May Exist For That Date

First of all, I'm working in SQL Server 2000 (although prayers have been made to the Federal Stimulus Money gods for an upgrade to 2008.) I have a listing of students and educational events. I can successfully get the most recent event, except that it may be a combination of events: StudentID ...

0
голосов
1ответов
341 просмотров

Minus of time value issue?

Using SQL Server 2000 query Select id, CONVERT(char(8), CASE WHEN DateAdd(Day, - DateDiff(Day, 0, OutTime), OutTime) > Normal_Outtime THEN Cast(Normal_Outtime AS datetime) ELSE DateAdd(Day, - DateDiff(Day, 0, OutTime), OutTime) END - CASE WHEN DateAdd(Day, - DateDiff(Day, 0, InTime), InTime)...

0
голосов
2ответов
191 просмотров

How to make total?

Using SQLSERVER 2000 How to make a total of intime value Table 1 InTime 02:00:48 22:00:22 ....., Intime Datatype is varchar 02:00:12 - (HH:MM:SS) Before I tried in access 2003 select format( Int(24*sum(Intime)), '0') & format( sum(Intime) , ':ss' ) AS totaltime from ta...

2
голосов
5ответов
351 просмотров

T-SQL Query Returning Items It Shouldn't Be

Here's the scenario. The column in question is called 'datein' and it's type is 'datetime'. I have three rows with the value of '2009-10-01 00:00:00.000' for 'datein'. Why does this query return the aforementioned rows? SELECT * FROM t_call AS tc WHERE tc.datein >= '2009-09-30 00:00:...

1
голосов
1ответов
965 просмотров

SQL Combine Columns into a string

I have a table with the following columns in table: id, t1, t2, t3, t4 All are of type bit (There are other columns as well, however I am only showing the relevant one) Now, I need to get the following string based on the ID: t1 t2 t3 t4 The best I thought of would be this: declare @t1 bit,...

1
голосов
1ответов
1003 просмотров

XACT_ABORT within TransactionScope for simple error handling

This question is an attempt to explore the semantics involved in the interaction between TransactionScope and xact_abort in SQL Server 2000. If the following sql is executed within a TransactionScope, and the first delete command errors, will the second delete command be run? (Assume a foreign ...

1
голосов
2ответов
374 просмотров

Can I force certain settings by user in SQL Server?

I have a bunch of users making ad-hoc queries into a database running SQL Server. Occasionally someone will run a query that locks up the system for a long period of time as they retrieve 10MM rows. Is it possible to set some options when a specific login connects? e.g.: transaction isolation ...

1
голосов
4ответов
209 просмотров

Consolidating Queries

I'm trying to understand how to combine queries when one of them returns more than one record. This is an invoicing report where I want to pull in the Serial Numbers of products invoiced. I'll abbreviate the script as much as possible to clarify. Here is my script before adding the serials: ...

4
голосов
4ответов
3479 просмотров

SQL Server dynamic Order By in query, different data types

In SQL Server 2000 I have a query like SELECT DISTINCT A.COLUMN1, B.COLUMN2 FROM TABLEA A, TABLEB B WHERE A.KEY_ID = B.FK_ID ORDER BY CASE @ORDER_NAME WHEN 'COL1' THEN COLUMN1 WHEN 'COL2' THEN COLUMN2 ELSE COLUMN2 END ASC Here A.COLUMN1 is varcha...

0
голосов
7ответов
1645 просмотров

How to build the sum of `DATETIME` columns?

I have a table with two DATETIME columns: Column1 Column2 1/1/1900 12:20:45 1/1/1900 23:22:25 1/1/1900 09:00:00 1/1/1900 18:10:30 … … (Times are in the format HH:MM:SS.) I want to take a total of column 1 and total of column 2. If the table had only the t...

1
голосов
4ответов
5018 просмотров

How to get SQL Server Date in its native format?

I need to get a Date from an SQL Server Database (SQL Server 2000), the date is stored as such: 2009-09-30 00:00:00.000 I want to pass this date into a Classic ASP function to use this date in the Database somewhere else, however when I get the date, it converts in my ASP code to a locale-depe...

0
голосов
3ответов
2341 просмотров

While Using Distinct still getting duplicate values?

Using SQL Server 2000 How to avoid the duplicate values? Query SELECT DISTINCT Modification.dbo.Reference.Dates AS DailyDate, tmp_Cardevent2.PERSONID, tmp_Cardevent2.empname, tmp_cardevent2.cardno, tmp_Cardevent2.titlecode, tmp_Cardevent2.titlename, tmp_Carde...

0
голосов
3ответов
205 просмотров

How to modify the date column values?

Using SQL Server 2000, Date Column Datatype is varchar... In my table date column values are like: 2009/01/31 2009/02/00 2009/02/01.... 2009/03/31 2009/04/00 2009/04/01.... so on..., I want to display 2009/01/31 instead of 2009/02/00, If date is 2009/02/00 it should display previous date. ...

0
голосов
3ответов
515 просмотров

Table Diff in SQL Server 2000

I'm using SQL Server 2000, and given a a set of data (with unique ID's), I want to figure out the diffs with rows in the database, matching on the unique IDs. I'm also trying to keep it as flexible as possible. I can use C#/VB in the front end to take the parameters, or even return things. M...