How to set default null value in to a date field in sql server 2000?
How to set deafult null value in to a date field in the table,if the parameter containing the date value is null? ...
sql-server-2000
Use SQL to count cases in a certain state at a certain time
Hi I need to develop a query that will count the total number of 'open' cases per month. I have a 'cases' table with an id and a name, and a 'state_changes' table with a datetime column, a caseid column and a state. How can I calculate the number of cases in each month that have a record with state 'open' in the past, but without a cor...
Use a left hand value in right hand SQL comparison
I have this SELECT statement: SELECT SUM(dbo.DLData.Quantity) FROM dbo.MasterDates JOIN dbo.DLData ON MasterDates.[ID] = dbo.DLData.DownloadID WHERE dbo.MasterDates.[Date] BETWEEN @From AND @To AND dbo.MasterDates.SiteID = @X If you plug in the @From, @To and @X it works. This is to be the right hand side of a comparis...
Dense_Rank() alternative in sql server 2000?(Set based)
Actually, I have the task of finding the employees based on the salary rank. So I used Dense_Rank() and got the answer. Initially I was told to solve in SQL SERVER 2005. Later on they changed the requirement and is saying that the query should run in SQL SERVER 2000 also. I solved that using a while loop. But they are saying that...
Writing a complex trigger
I am using SQL Server 2000. I am writing a trigger that is executed when a field Applicant.AppStatusRowID Table Applicant is linked to table Location, table Company & table AppStatus. My issue is creating the joins in my query. When Applicant.AppStatusRowID is updated, I want to get the values from Applicant.AppStatusRowID, Applicant....
COLLATION Conflict For Equal To Operation Sql Server 2000
I have a collation conflict in a stored procedure I am trying to run to send it live... it has been explained here http://stackoverflow.com/questions/1519544/sql-server-2000-dts-cannot-resolve-collation-conflict-for-equal-to-operation Is there a way to fix the issue without writing the COLLATE database_default next to every problem equ...
Writing a complex trigger
This posting is an update (with trigger code) from my earlier posting yesterday I am using SQL Server 2000. I am writing a trigger that is executed when a field Applicant.AppStatusRowID Table Applicant is linked to table Location, table Company & table AppStatus. My issue is creating the joins in my query. When Applicant.AppStatusRow...
Selecting with preference in SQL Server
I have a table in SQL Server 2000 with data similar to the following: ReferenceNumber ReferenceValue 00001 Not assigned 00002 Not assigned 00002 ABCDE in which each ReferenceNumber can appear multiple times in the table, either with a ReferenceValue of 'Not assigned' or a true ReferenceValue. ...
Are nullable types perfect for my situation?
Recently converted my ASP.NET project from 1.1 to 3.5. Hooray! Currently developing a form which has a handful of optional fields. Normally, I'd go through in my code, adding tons of if statements, checking for empty field, and setting value to 0 if so. So, I'm wondering if it would be best to instead, declare private, nullable variables...
Sql Server 2000: Return "true" or "false" based on any 1 of 25 columns being "true"
Hi, I have to create a query that checks across several different columns, and if any of them have a 1, I want to return true. Ideal output would be along the lines of: ID:55 Name:John Doe IsDealerType1:True IsDealerType2:True IsDealerType3:False IsDealerType4:False IsDealerType5:True The problem is, instead of those 5 dealer col...
Automated SMTP (not MAPI) emails using SQL Server Job Scheduler
Work on SQL Server 2000.i want to send birthday wishes to all customers whose birthday matches the current day.I have created a database Customers with a table called CustomerDetails with the following fields ID Name BirthDate Email I have written the SQL script shown below. The SQL script loops through the CustomerDetails table and...
Slow (to none) performance on SQL 2005 after attaching SQL 2000 database
Issue: Using the detach/attach SQL database from a SQL 2000 SP4 instance to a much beefier SQL 2005 SP2 server. Run reindex, reorganize and update statistics a couple of times, but without any success. Queries on SQL 2000 took about 1-2 sec. to complete, now the same queries take 2-3 min on the SQL 2005 (and even 2008 - tested it there...
Saving Inserted and Deleted tables into variables for use in .NET code (via sp_oamethod) [SQL Server 2000]
I am trying to create a SQL Trigger in SQL Server that will somehow serialize the Inserted and Deleted tables for use in .NET code (via sp_oamethod). I would like this to be generic enough to use for any table. My first attempt involved using "for xml" to serialize it into XML, and pass it to .NET code. However, I have been unable to ...
T-SQL Parameters
I have the following table in SQL Server 2000: TABLE_NAME | COLUMN_NAME | TYPE_NAME | PRECISION | LENGTH | SCALE | test TestID int 10 4 0 test TestDecimal decimal 18 ...
tsql- set options
I am rebuilding indexes using a script which reorganises or rebuilds indexes according to a predefined fill factor. It is on SQl 2000. I have the following SET options in my script: SET ANSI_WARNINGS OFF SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON I am getting the following error: DBCC failed because the following SET options have i...
Can MS SQL Server 2000 handle Chinese text encoded with Unicode?
Hi all, Now, I am using Ms sql server 2000 and I want to store my data as the unicode for chinese font. But I don't know it can store this type or not? If not, could anybody guide me? Thanks, Sopolin ...
how to compare server time
work on Sql server 2000. i want to get the current server date and time then want to compare it with my table column EntryDate .How to? Declare @id datetime Select @id = DATEPART(hour,GETDATE()) SELECT [Name],[BirthDate],[Email] FROM Customers3 where BirthDate<=@id-1 AND BirthDate>=@id want to get on the range value like @id=10.I wa...
CF Query appears to return incomplete data from text field
I'm using CF8 and SQL2000. I'm storing a bunch of HTML in a Text field in my SQL table. When I do a simple CFQUERY against that data, and CFDUMP it, it's truncated to 64000 characters. I've confirmed that my data is complete in the SQL table, by selecting the tail end of the data using SELECT Substring, and confirmed the length using...
Sql Query First, Next, Last Rows in a Set
I have two tables that I am trying to join together multiple times. The Parent table is called Jobs, and the child table is called Routings. A Job can have 1 or more Routings. I need my output to contain one record for each job, joined with three separate joins to the Routings table. One join is for Current data (the first null date ...
Customizing FreeTextTable in MS SQL 2000
I'm looking for answers for the following 2 questions (SQL Server 2000). I have an order info table that is indexed so that I may search data on particular columns. So, an example query I might run is: SELECT top 50 ft_tbl.*, key_tbl.Rank from OrderInfo as ft_tbl INNER JOIN FREETEXTTABLE(OrderInfo, Address1, 'W Main St') as key_tbl ...