sql

Android Database error?

I have a MenuItem in my Android application that deletes all the values in the database, its used more for my debugging purposes, after a while, inserting dummy values get redundant. Anyways, I have used LogCat to debug my program as it runs in the emulator and it gives me an error: Failure 21 (out of memory) on 0x0 when preparing 'DELE...

SQL's Fallthrough CASE Statement

In C# we can write switch(num) { case 0: case 1: // do something; break; case 2: ............ ........... case n: // do something break; default: //do something; break; } How can I achieve the similar kind of stuff in SQL SERVER ? I am not talking about the simple way of writing CASE in SQL SERVER. I am talking about if...

MYSQL SUM GROUP BY

I'm working on a high school grading system. At my school, grades can be changed by reworking problems and I store these changes with dates. I have a function that properly returns averages because the most recent grade is flagged with a "current" field with a value of '1'. I'd like to make the function capable of returning the most re...

Does this query guarantee me a 'race free' PK value?

I was just reading How to avoid a database race condition when manually incrementing PK of new row. There was a lot of good suggestions like having a separate table to get the PK values. So I wonder if a query like this: INSERT INTO Party VALUES( (SELECT MAX(id)+1 FROM (SELECT id FROM Party) as x), 'A-XXXXXXXX-X','Joseph'...

how to select a particular table from a storedprocedure which returns multiple table?

Hi all, I have a storedproc which has multiple select statement. When it is executed in sql server , it returns multiple tables. I need a query which will select a particular table from the storedproc e.g. sp_help. Please help. ...

Pagination with hasMany association cakePHP

I have two tables: Contestant and Votes Contestant hasMany Votes I've tried doing a count(Vote.id) as Votes so I can place it on the recordset and just paginate them but I have no idea where to place it. I did it on the fields array but it gave me the total count of votes regardless of the contestant they belong to. The votes are link...

How do I programatically perform a Modify on all stored procedures in my database in SQL 2008

What I want to do is simulate right clicking a stored procedure an selecting Modify, then execute so that my stored procedure runs. Some of the tables in our database have changed and not all the sp's have been modified. ie old SP = ALTER PROCEDURE [dbo].[myProcedure] SELECT name, address, typename from names GO Then the names tabl...

How to validate sql query syntax?

java 1.4 Sql server 2000 i am taking input of sql query (for validation of field value against values retrieved by executing sql query) from admin user which will be stored in database and later i will executing sql query corresponding to field.Before inserting sql query in database i want to validate its syntax in java code. Fields ...

Handling circular data in Oracle's SQL

Hi everyone, There is a problem that don't know how to resolve only with SQL (I could do it with PL/SQL, but it should be doable only with SQL). I have a table (in fact it's not a table but the result of a with query) with contains pair of values like this: column1 column2 --------- --------- value1 value2 value1 ...

mysql query to dynamically convert row data to columns

I am working on a pivot table query. The schema is as follows Sno, Name, District The same name may appear in many districts eg take the sample data for example 1 Mike CA 2 Mike CA 3 Proctor JB 4 Luke MN 5 Luke MN 6 Mike CA 7 Mike LP 8 Proctor MN 9 Proctor JB 10 Proctor MN 11 Luke MN As you see i have a set of 4 distinct districts (...

Calling stored procedure and passing parameter from another stored procedure

This is my stored procedure that deletes rows according to array that was passed to it as XML parameter: BEGIN DECLARE @docHandle int EXEC sp_xml_preparedocument @docHandle OUTPUT, @XMLDoc DELETE Comments WHERE ID IN (SELECT * FROM OPENXML(@docHandle, '/values/value1', 2) WITH (value1 INT '.')) EXEC sp_xml_removedocument @doc...

Archiving Reports SQL 2005 Reporting Services

Hi, Is there a way to configure the Report Server to Archive the reports to the Report Server or to a path on the File System. I need to save the reports generated as .pdf files. TIA. ...

Can I select distinct/group by characters in a field in SQL?

I'd like to run a query which for a given field will count the instances of a particular character. For example if I had a table called 'Friends' with a 'Names' field containing rows: Edward, James, Mike. I'd get the output: A 2, D 2, E 3, I 1, and so on... ...

Adding new row datagridview to use default values

Hi, I have the following SQL that creates a table and inserts the first row of data without a problem. The default DateTime values are also inserted as expected. CREATE TABLE Jobs ( [Id] int PRIMARY KEY IDENTITY(1,1), [JobName] nvarchar(256) Default 'SomeName', [CreateDate] DateTime DEFAULT GETDATE(), [ModifyDate] ...

T-SQL: Why is my query faster, if I use a table variable?

Hello, can anybody explain me why this query takes 13 seconds: SELECT Table1.Location, Table2.SID, Table2.CID, Table1.VID, COUNT(*) FROM Table1 INNER JOIN Table2 AS ON Table1.TID = Table2.TID WHERE Table1.Last = Table2.Last GROUP BY Table1.Location, Table2.SID, Table2.CID, Table1.VID And this one only 1 second: D...

SQL statement queries parameters become case sensitive how so???

Hi guys, I'm running a SQL query on my mysql database but for some reason its case sensitive like if an entry exists as 'NAME = Andrew' running a sql query like: SELECT * WHERE NAME Like 'and%' Doesn't work but SELECT * WHERE NAME Like 'And%' does work? WHat gives I'm running a php mysql set up here... ...

SQL use GROUP BY such that it results in the original SELECT

I have a SQL query that is something like SELECT SUM(price) FROM budget GROUP BY {{PLACEHOLDER}} where {{PLACEHOLDER}} will be replaced in the code. Is it possible to replace it by something that will result in the same output as the following statement? SELECT price FROM budget ...

Help in writing Query for hall booking date availability search

I need help in writing query to find the time slot availability for a hall booking system.. details are given below I have a Hall table which stores the hall details and HallBooking table which stores the start and from time of the bookings done.. Hall - HallId - Name HallBooking - HallBookingId - HallId - BookingPersonName - StartDat...

How to copy a table schema and constraints to a table of different database?

Hi all, I need an sql which will copy schema of a specified table to a table in different db. How to implement this? Please help. ...

How can I set an alias name in SQL Server

IF RTRIM(@GLDD_LEDGER_CODE)= '00' BEGIN SELECT @SYS_REM =GLMA_ACCT_NAME FROM GLAS_GL_MASTERS WHERE GLMA_COMP_CODE = @COMP_CODE AND GLMA_LDGR_CODE = '00' AND GLMA_DEPT_NO = SUBSTRING(@GLDD_ACCT_CODE, 2, 2) AND GLMA_ACCT_CODE = SUBSTRING(@GLDD_ACCT_CODE, 4, 4) AND GLMA_YEAR = DATEPART(YYYY, GETDATE()) END ELSE BEGIN...