sql

ms-access: runtime error 3354

i'm having a problem running an sql in ms-access. im using this code: SELECT readings_miu_id, ReadDate, ReadTime, RSSI, Firmware, Active, OriginCol, ColID, Ownage, SiteID, PremID, prem_group1, prem_group2 INTO analyzedCopy2 FROM analyzedCopy AS A WHERE ReadTime = (SELECT TOP 1 analyzedCopy.ReadTime FROM analyzedCopy WHERE analyze...

How do you create an auto-incrementing revision number unique to a key in PGSQL?

Assuming I have the following tables. PARENT: PARENT_ID serial, DESCRIPTION character varying(50) CHILD: PARENT_ID integer, CHILD_ID integer, DESCRIPTION character varying(50) What I would like to see is each row in CHILD having a CHILD_ID that starts at 1 and increments by 1, unique per PARENT_ID. It would be similar to a revision n...

SSRS - Producing a report that is not dynamic in size

I want to build a report that is completely static in size and shape. I'm attempting to mimic a hand-entered report that someone in my organization has been building from a word doc for years. The critical piece appears to be fixing the number of rows that are produced in the various Table grids that fill the page. I would like them t...

Why does this SQL INSERT statement return a syntax error?

Ok I have a very simple mysql database but when i try to run this query via mysql-admin i get weird errors INSERT INTO customreports (study, type, mode, select, description) VALUES ('1', '2', '3', '4', '5'); Error: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for t...

How do I use T-SQL's Exists keyword?

I have a query I want to run as a subquery that will return a set of FK's. With them I want to return only rows that has a matching key. Subquery: SELECT ID FROM tblTenantTransCode WHERE tblTenantTransCode.CheckbookCode = (SELECT ID FROM tblCheckbookCode WHERE Description = 'Rent Income') That will return all the transac...

How to enable GUI behaviors for sorting a JTable when SQL does the sorting?

How do I enable JTable icons and behaviors for sorting table rows by a column, without letting it use a comparison predicate to do the sorting? That is to say, how do I tell the table headers to show the arrow for ascending/descending sort order in the column being used, and get it to call appropriate methods when sort order/column chan...

Writing SQL query where a value maps to all possible values

I have a table A name num ------------ A 1 B 0 C 3 B num marks --------------- 1 90 3 40 Query: ?? Result: name marks -------------- A 90 C 40 B 90 B 40 So number "0" in table A corresponds to all num column values. Is there a way this query can be writ...

SQL distinct and count

I have a query where I want to get distinct dates, the phone numbers associated with those dates, and a count of the phone numbers per date. For example, I have a database with dates and phone numbers and I want the result to be 9/2005 5554446666 3 9/2005 4445556666 1 10/2005 1112223333 1 11/2005 2223334444 ...

SQL 2008 full text word break pointers

If I do a full text search on SQL 2008, can I get a pointer ( File , or Database) so that I don't have to load the 100MB memo field to by Business Object and do search again ? ...

Changing newid() to newsequentialid() on an existing table

Hi, At the moment we have a number of tables that are using newid() on the primary key. This is causing large amounts of fragmentation. So I would like to change the column to use newsequentialid() instead. I imagine that the existing data will remain quite fragmented but the new data will be less fragmented. This would imply that I sho...

Database Design for Transport timetable system

Its been a while since my Database Design classes in my sophomore year at Uni. and I haven't done any designs in the interim so my skills are at best rusty at the moment. I have begun working on a personal project involving the railway timetable system and seem to be stuck at the table design which resembles something like this - Stati...

I increment numerical value if there is same record , or insert wants to do 0 if there is not it

Please help me. select * from hoge where id=xxx; If there is a data, I do it to do it, but though I implement this step, there is if in a program, and insert hoge set data=0 is troublesome if update hoge set data=data+1, a result are 0 lines. May not you realize this procedure by a blow by SQL? replace hoge select id, data+1 as da...

saving the FOR XML AUTO results to variable in SQL

select @[email protected]('*') for xml raw,type Above statement will generate following alert: Msg 6819, Level 16, State 3, Line 2 The FOR XML clause is not allowed in a ASSIGNMENT statement. ...

DELETE Command is too slow in a Table with Clustered Index

I have a rather big table named FTPLog with around 3 milion record I wanted to add a delete mechanism to delete old logs but delete command takes long time. I found that clustered index deleting takes long time. DECLARE @MaxFTPLogId as bigint SELECT @MaxFTPLogId = Max(FTPLogId) FROM FTPLog WHERE LogTime <= DATEADD(day, -10 , GETDATE()) ...

Making my SSIS package portable - how to do this?

I know how to create SSIS packages and getting my data source and destinations. But what will i have to do in my package if i want to make it portable in the sense where i can change the source and destination connection strings when i move my package onto another PC? Thanks in advanced! ...

How can I run SQL statements on a named range within an excel sheet?

This is a very simple question which I am pulling my hair out with. No amount of web searching seems to help! All I am trying to do is take a standard range on an excel sheet (i.e. a named range, or even A1:F100), and run some sql queries on it, and return a recordset that I can either step through in VBA code, or even just paste into s...

SQL Timeout Errors

I'm getting a problem with only 1 of about 30 sites we run on a W2003 Web Server. Probably for about 25% of the day, the website constantly returns : SQL Timeout Errors on various connections to SQL (using ODBC) I have checked and updated the ODBC drivers to the latest that I could find (3.5.x?) and I am also checking the SQL server t...

Fastest way to script generation SQL server DB schema for hashing

I'd like to ge the whole SQL schema for a DB, then generate a hash of it. This is so that I can check if a rollback script returns the schema to it original state. Is there a SP I can use or some other cunning method? I'd like it to be as fast as possible. ...

SQL; Only count the values specified in each column

In sql i have a column called answer and it can either be 1 or 2, i need to generate an SQL query which counts the amount of one and twos for each month, i have the following query but it does not work: SELECT MONTH(`date`), YEAR(`date`),COUNT(`answer`=1) as yes, COUNT(`answer`=2) as nope,` COUNT(*) as total FROM results GROUP BY YEAR...

SQL connection lifetime

I am working on an API to query a database server (Oracle in my case) to retrieve massive amount of data. (This is actually a layer on top of JDBC.) The API I created tries to limit as much as possible the loading of every queried information into memory. I mean that I prefer to iterate over the result set and process the returned row o...