I'm writing a web application that should show very large results on a search query. Say some queries will return 10.000 items. I'd like to show those to users paginated; no problem so far: each page will be the result of a query with an appropriate LIMIT statement. But I'd like to show clues about results in each page of the paginated q...
Hey, I have two tables: In one, I save all seats for a specific room. In the other table, data for the reservations of the seats is stored. Now, i would like to create a view, where I have a column which marks a seat as marked or unmarked. Or more abstract: I would like to give out true/false in a table column for a view, if a certain...
What is the advantage of using FAST_FORWARD for defining a cursor? Is it better for performance? why? ...
Hi, I've got problem with SQL. Here is my code: SELECT Miss.Name, Miss.Surname, Master.Name, Master.Surname, COUNT(Date.Id_date) AS [Dates_together] FROM Miss, Master, Date WHERE Date.Id_miss = Miss.Id_miss AND Date.Id_master = Master.Id_master GROUP BY Miss.Name, Miss.Surname, Master.Name, Master.Surname ORDER BY [Dates_tog...
I have a table with two linked columns, a compulsory boolean and an optional date. There can only be a date when the boolean is FALSE. So I have this structure: CREATE TABLE FOO ( FOO_ID INT IDENTITY(1, 1) NOT NULL, MY_DATE DATETIME, MY_BOOLEAN BIT DEFAULT 0 NOT NULL, CONSTRAINT FOO_PK PRIMARY KEY (FOO_ID) ); And I've...
Many examples out there advocate explicit rollback of database transactions, along the lines of: using (var transaction = ...) { try { // do some reading and/or writing here transaction.Commit(); } catch (SqlException ex) { // explicit rollback transaction.Rollback(); } } Howeve...
I need to delete millions of rows from a table from within an EJB Timer. The problem is that the timer has a transaction timeout of 90 seconds, so I should divide the work into bite-size chunks. Since I don't know how many rows can be deleted in 90 seconds the algorithm should loop and delete a few at a time until the time is almost up....
What would the command be for Microsoft SQL Server 2008 to alter an existing column to allow for more characters? Would this have an effect on any previous entries in the column if I'm only expanding it? I have a URL column that I need to add about 100 characters to. ...
I have a query as follows select * from table where col1 = @param1 and col2 = @parm2 And another select * from table where col1 = @param1 Is it possible do both operations in same query based on parameter passed, if it is null query all or when parameter has value select them. My queries are very big and i have to create 2 version...
Hi, I'm writing sql query to get post and only last comment of this post(if exists). But I can't find a way to limit only 1 row for right column in left join. Here is sample of this query. SELECT post.id, post.title,comment.id,comment.message from post left outer join comment on post.id=comment.post_id If post has 3 comments I get 3...
An example is shown below; imagine each commar separated date is a row in the database Input: - 2010-01-11, 2010-01-18, 2010-01-25, 2010-02-01, 2010-02-08, 2010-02-15, 2010-02-22, 2010-03-01 it should return Ouput: 2010-01-25, 2010-02-22, 2010-03-01 The output is derived by getting the last date in the month, note for March there is o...
I have a stored procedure that has a parameter called UserName and in my code behind I have a SqlCommand object that I add the parameters to with the Add method. But for some reason when the command object tries to run the ExecuteReader method, it throws an exception. I am totally at a loss and have no idea why it's not recognizing the p...
Hi all, I have an application which performs backups and restores of SQL databases, this works fine on the local machine, however if I run this against a SQL server hosted on another machine I get the following error Microsoft.SqlServer.Management.Smo.FailedOperationException: Backup failed for Server '25.98.30.79'. ---> Microsoft....
I have an SP in SQL Server which runs hundreds of times a minute, and needs to check incoming traffic against a database. At the moment it does the following INSERT INTO table SELECT @value1,@value2 WHERE NOT EXISTS (SELECT * FROM table WHERE value1 = @value1 AND value2 = @value2); However, I could also go with IF NOT EXISTS(SELECT...
I have a query with a where clause that looks like: WHERE field IN ( 1, 2, 3 ) Is possible to parametrize this so it looks something like: WHERE field in ( :list )? How do you create the :list parameter, assuming it's a list of ints, that could be anywhere from 1 to 10 ints? ASP.net, webforms if that makes a difference. ...
Hi, I am trying to perform a SQL query which generates a table with two columns referencing the same data but using different conditionals. My result query needs to contain columns like: Query Result: Total Quantity Available Quantity Where Total Quantity is the total number of a certain item and Available is a subset of the same ...
2 tables: owners & cars An owner can have many cars. A car can be marked as usable_offroad, usable_onroad, or both. The cars table has usable offroad and usable_onroad fields which can be set to 0 or 1 (no or yes) Consider the following query: SELECT * FROM owners LEFT JOIN cars on cars.owner_id = owners.id GROUP BY owners.id ORDER ...
Hello, Probably this isn't the right place... but ill give it a try. I want to buy Microsoft SQL Server 2008 Web Edition in order to remotly install it on the server. The question is: Can i buy a licence in USA? and pay in dollars? or do i have to buy it in my country (Portugal)? However since the servers are in Germany, should i buy ...
I am using Oracle's to_char() function to convert a date to a week number (1-53): select pat_id, pat_enc_csn_id, contact_date, to_char(contact_date,'ww') week, ... the 'ww' switch gives me these values for dates in January of this year: Date Week 1-Jan-10 1 2-Jan-10 1 3-Jan-10 1 4-Jan-10 1 5-Jan...
I have a simple query like this.. USE AdventureWorks; GO SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost FROM Production.Product GROUP BY DaysToManufacture; DaysToManufacture AverageCost 0 5.0885 1 223.88 2 359.1082 4 949.4105 A simple piv...