sql

What server should i use for Online Counseling Project ?

We are launching a website which will be used for online counseling of students for engineering entrance . The load will be for 15 days & no. of students accessing site will be more than 1 lakh . These students will access the list of colleges which will be close to 500 & will select 50 college each. After 3 days all selected options wi...

How to implement a Lazy List using SmartGWT and SQL

Hey all, I was trying all of yesterday to try and integrate a SQL Database with SmartGWT for a lazy list but I just couldn't figure out how to implement it. (JavaDoc, and example of a lazy list) What I want to do is create a list of a bunch of "sites" all over the world. The problem is there will probably be about a million of them, s...

SQLCMD.EXE generates ugly report. How to format it?

I did batch to run SQL query like use [AxDWH_Central_Reporting] GO EXEC sp_spaceused @updateusage = N'TRUE' GO It displays 2 tables and generates some ugly report with some kind of 'P' unneeded letters... See below Changed database context to 'AxDWH_Central_Reporting'. database_name ...

Locally enclose a code block in SQL

In Java you can do the following to locally scope variables within a method: public void blah() { { int a = 1; int b = 2; } { int a = 3; int b = 4; } } I don't even know what the name for this technique is, I've only needed to use it once or twice. In my current situation, this may really come in ha...

[SQL] Full-text search CONTAINSTABLE

Hello, I have a question regarding a CONTAINSTABLE function. I would like to find everything that ENDS WITH the search string. I can use the * to find everything that starts with the searchvalue, but I want an equivalent for the % sign in the SQL LIKE function. What I want: find everything that ends with 123, so as searchvalue, I coul...

SQL Count total number of rows whilst using LIMIT

Hi, I'm trying to create a simple image gallery, showing 16 images per page. I'm using LIMIT 16 to display the correct amount on the page, but if there are more than 16 rows, I want to have links at the bottom, allowing the user to navigate to the next page. I know I could achieve the desired result by removing the limit and simply u...

Deleting multiple items in PHP

I have a list of items need to be passed which should be deleted. How can I do this in PHP? $query = "DELETE from members WHERE member_id ='$chan[2]' "; $chan[2] holds multiple values, but deletes only one. ...

Select the last row in a SQL table

Is it possible to return the last row of a table in MS SQL Server. I am using an auto increment field for the ID and i want to get the last one just added to join it with something else. Any idea? Here's the code: const string QUERY = @"INSERT INTO Questions (ID, Question, Answer, CategoryID, Permission) " + @"VALUE...

In SQL, how do I fetch a row with a specific column value if available, otherwise any other row

I currently have the following Product table: Product _______ id, INT name, String featured, ENUM(Y, N) If there is a featured product in the above, I will return that item. If none of the above are featured, I will return anyone of them. I currently have the following query which is functional: SELECT * FROM Product WHERE (featured...

How to reuse code in SQL stored procedures?

We use SQL Server 2005. All our data access is done through stored procedures. Our selection stored procedures always return multiple result sets. For instance: CREATE PROCEDURE hd_invoice_select(@id INT) AS SELECT * FROM Invoice WHERE InvoiceID = @id SELECT * FROM InvoiceItem WHERE InvoiceID = @id SELECT * FROM InvoiceComments WHE...

slow update (primary key)

update auditdata set TATCallType='12',TATCallUnit='1' from auditdata auditdata inner join Auditdata_sms_12 a_sns on auditdata.ID = a_sns.id when I above query it takes more than 10 minutes to execute. what wrong in this Auditdata.ID is primary key.. if i run Update command is that also update the indexes??? is this reason...

(using join) I get not contained in either an aggregate function or the GROUP BY error

I get the following error. [Microsoft][ODBC SQL Server Driver][SQL Server]Column 'STATS.VisitorIP' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. I used VISITORIP column in both select lists. I used GROUP BY clause in one of them so I addded VISITORIP to it. But I dont ...

ASP.NET SQL Profile Provider - Does the ProfileBase.Create() method hit DB?

I am working with the SQLMemebershipProvider and using Profiles. I have a custom class called UserProfile that inherits from the ProfileBase class and I use this to set custom properties like "FullName". I am wanting to loop through all the users in the database and get access to their profile properties. On each iteration I am calling P...

T-SQL equivalent of =rand()

I have several content tables that I want to fill up with random paragraphs of text. In MS Word, I can simply put =rand() and presto! I get three paragraphs of fresh-off-the-press text. Is there a SQL script/command that I can use to generate random dictionary words using t-sql? ...

Order by day_of_week in MySQL

How can I order the mysql result by varchar column that contains day of week name? Note that MONDAY should goes first, not SUNDAY. ...

MySQL LIKE IN() ?

My current query looks like this: SELECT * FROM fiberbox f WHERE f.fiberBox LIKE '%1740 %' OR f.fiberBox LIKE '%1938 %' OR f.fiberBox LIKE '%1940 %' I did some looking around and can't find anything similar to a LIKE IN() - I envision it working like this: SELECT * FROM fiberbox f WHERE f.fiberbox LIKE IN('%140 %', '%1938 %', '%1940 ...

Database design for constraint enforcing pairing

How do I best design a database where I have one table of players (with primary key player_id) which I want to pair into teams of two so that the database can enforce the constraint that each team consists of exactly two players and each player is in at most one team? I can think of two solutions, but both I'm not too happy about. One ...

SQL Server taking a long time to return data to ColdFusion when using Flex

I am working on a Flex application that is connecting via Flash Remoting to ColdFusion 8 with a SQL Server 2005 database. Most of the time, everything runs fine. However, from time to time, it will take an exceptionally long time for SQL Server to return data from a stored procedure call to ColdFusion; returning data from CF to Flex is v...

is sharing temp tables across procedures considered bad form?

I have 2 procedures. One that builds a temp table and another (or several others) that use a temp table that's created in the first proc. Is this considered bad form? I'm walking into an existing codebase here and it uses this pattern a lot. It certainly bothers me but I can't say that it's explicitly a bad idea. I just find it to be an ...

SQL Divide by Two Count()

I have the following query, which is trying to figure out the percentage of a certain product compared to the total number of products. IE: [Product Count] / [Total Products] = Percent ;WITH totalCount AS( SELECT CAST(COUNT(id) as Integer)as totalCount FROM TABLE_NAME ) SELECT ((CAST(COUNT(DISTINCT id) as Integer)/(SE...