sql-server-2000

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. How to make a query for this condition? ...

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_Cardevent2.departname, CASE WHEN tm...

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-dependant version, such as: 30/09/2009 I ju...

How to get a total of time?

Using SQL Server 2000 My table column values are datetime 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 and so on… 12:20:45 means (HH:MM:SS)` Here I want to take a total of column 1 and total of column 2 Expected Output column1 column2 1/1/1900 21:20:45 1...

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 varchar(50) and B.COLUMN2 is datetime. This quer...

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: SELECT ARM.fcustno AS [Cust No] , ...

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 level max rowcount query timeout If this...

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 key from parent to child in order to ensur...

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:00.000' AND tc.datein <= '2009-09-30 ...

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 table1 Above Query is working perfectly in Access 2003 How to...

Minus of time value problem?

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) < Normal_Intime THEN Cast(Normal_Intime AS d...

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 Event Date 1 Triennia...

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.ReadXml(reader, XmlReadMode.Fragment); data....

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 Date1 Date2 001 Ravi 23-02-2009 ...

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 = [Crocus_Limited$OrderRequestDetail].OrderID ...

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 | bar I am trying to get ...

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…, Table2 ID TimeIn TimeOut 001 08:00:...

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:00 16:00:00 From the above table I want...

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 named "sku". Also, for each file being upload...

linked-server sql - access Problem

Hi, 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' reported an error. Authentication failed. [...