I have a problem. Actually in our application, earlier the customer was allowed to pay 3 installemnt per month but now it can be any number . So I have the earlier query declare @tbl table([MonthName] varchar(50), [Installment] int) insert into @tbl select 'Jan',100 union all select 'Jan',200 union all select 'Jan',300 union all sele...
Hi, I am making changes to an existing database while developing new software. There is also quite a lot of legacy software that uses the database that needs to continue working, i.e. I would like to maintain the existing database tables, procs, etc. Currently I have the table CREATE TABLE dbo.t_station ( tx_station_id VA...
Scenario I have 3 database tables. One is for Images and the other two are People & Places. Since each person can have many images and each place can have many images, I want to have a ONE TO MANY Relationship between both people and images, as well as places and images. Question Does the foreign key have to be called the same name as...
I understand that prior to SQL Server 2005, you could "trick" SQL Server to allow use of an order by in a view definition, by also include TOP 100 PERCENT in the select clause. But I have seen other code which I have inherited which uses SELECT TOP 100 PERCENT ... within dynamic SQL statements (used in ADO in a ASP.NET apps, etc). Is the...
The following are my sql server 2005 table structures: Products (productID INT PK, ...) ProductBids (productID INT, userID INT, Created DATETIME) Users(UserID INT PK, TOTALBIDS INT) Each day a user can bid as many times as they want on all the products. There is a sql job that is run periodically, that counts the total bids a user ha...
Hi, I have a complex sorting problem with my SQL statement. I have a table with the following columns. No Time Value -- ---- ----- 1 0900 '' 2 1030 '' 3 1020 '' 4 1010 '' 5 1100 '' 1 1015 'P' 2 1045 'P' I want to sort this table by doing the following steps. Select rows from the ...
I have an application and I want to move some of the tables from the production database to the development database to refresh the data on development. I don't need to move all of the tables. This application is still running on SQL2000 and planned to be upgraded next year. I have SQL2008 installed on my workstation. I was thinking ...
Hi all, Background, I am running MS SQL Server 2005. What do you guys think about this. I have an analytics process whereby a stored procedure is fired off on every page of my website and the results logged into my analytics table. Given that I have thousands of users on my site daily, this a very frequent albeit "light" overhead to ...
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 ...
I have a Total value that I need to distribute among several rows in a SQL table: DECLARE @total numeric(38,5); DECLARE @count int; SET @total=123.10000 SET @count = SELECT COUNT(*) FROM mytable WHERE condition=@val; -- let's say @count is now 3 UPDATE mytable SET my_part=@total/@count WHERE condition=@val; --each record now has 41....
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 ...
How could i select the latest records by datetime of an SQL Server? Here is the pseudo-code... SELECT Records FROM MyTable WHERE current time >= (CurrentTime - 2 minutes) Supposing the current Time is 10:25:39 pm 26/10/2009 10:25:39 pm 26/10/2009 10:25:00 pm 26/10/2009 10:24:53 pm 26/10/2009 10:24:19 pm 26/10/2009 10:23:58 pm 2...
Windows Azure Data Services supports a subset of TSQL. What are the features of SQL 2008 not supported? ...
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...
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 ...
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...
Hi, I have the following code: UPDATE myTable SET Col1 = @Value However, I have a table that has over a 100 columns and want to be able to specify a column name by passing the name into the query similar to: UPDATE myTable SET @ColName = @Value When I do this I get an error. Is there a good solution to this? Its probably s...
What is wrong with the following ? DECLARE @ImageIDsToDelete TABLE ( xID INT, ImageID NVARCHAR(500)) INSERT INTO [@ImageIDsToDelete] (xID, ImageID) SELECT 1,'x' ...
I've got a UNIONed query which returns: ReceiptFolderID FolderParentID FolderTypeID FolderType FolderName FolderDescription ReceiptCount --------------- -------------- ------------ --------------------------------------------...
I need to change the collation of an nvarchar variable. By documentation: (...) 3. The COLLATE clause can be specified at several levels. These include the following: Casting the collation of an expression. You can use the COLLATE clause to apply a character expression to a certain collation. Character literals a...