sql

What happens behind the scenes when running aspnet_regsql?

I didn't get any responses from the asp.net forum, so I thought I'd try here. I had an issue with a client where they were having trouble logging into our application that they are hosting. It was working fine for the first day or so after they initially installed our app, but then authentication broke on the following day (5/29/2009)....

Linked Access DB "record has been changed by another user"

I'm maintaining a multiuser Access 2000 DB linked to an MSSQL2000 database, not written by me. The database design is very poor, so you'll have to bear with me. On the 'Customer' form there's a 'Customer_ID' field that by default needs to get the next available customer ID, but the user has the option of overriding this choice with an ...

TableA Left Join (a subset of) Table B

I want to left join TableA to TableB where a certain condition in tableA is true So I do this type of SQL query Select * from TableA Left Join TableB on TableA.fld1 = TableB.fld2 where TableA.fld3 = True This works OK. Now however I want to do the Join only with certain records in TableB, ie those records in TableB where a certain con...

Full text searching error: Word breaking timed out

I just set up SQLSERVER 2008 Express on a new machine, and on the first database on there I created a Fulltext catalog & index. When I try to use fulltext searching, I get the following error: Msg 30053...Word breaking timed out for the full-text query string. etc. I did some searching around and it turns out it is probably bec...

Need help creating this Sql query.

Hi folks, I'm trying to create the following sql query (as in, this is an example of the final query) :- DECLARE @SearchQuery AS NVARCHAR(100) = 'yellow bird' SELECT Id, Name FROM dbo.FooBars WHERE CONTAINS(Name, N'FORMSOF(Thesaurus, yellow)') AND CONTAINS(Name, N'FORMSOF(Thesaurus, bird)') Notice how i've got two 'CONTAINS' line...

Oracle SQL Query (Analytics?)

I am trying to build a query such that some column are built up from a previous matching row. For example with the following data: CREATE TABLE TEST (SEQ NUMBER, LVL NUMBER, DESCR VARCHAR2(10)); INSERT INTO TEST VALUES (1, 1, 'ONE'); INSERT INTO TEST VALUES (2, 2, 'TWO1'); INSERT INTO TEST VALUES (3, 2, 'TWO2'); INSERT INTO TEST VALUES...

SQL multiple join on many to many tables + comma separation

I have these tables: media table - id int primary key, uri varchar. media_to_people - media_id int primary key, people_id int primary key people - id int primary key, name varchar, role int -- role specifies whether the person is an artist, publisher, writer, actor, etc relative to the media and has range(1-10) This is a many to many r...

How to add values coming from 2 queries

From the 1st query I am getting some value and from 2nd query I am getting some value. I want the sum of the two values. Query 1: select sum(EAmount) from EstimateAmount where pid='3' group by pid Query 2: select sum(OPEAmount) from OPEAmount where pid='3' group by pid ...

How do I call a SQL Server 2000 DTS package in VB.net

How do I call a DTS from a VB.net application? ...

How can i change datetime format in sql?

How can i change dateformat?Forexample: 2009-06-10 10:16:41.123->2009-June 2009-05-10 10:16:41.123->2009-May ...

Is it safe to UPDATE a table field with WHERE clause checking that same field?

I have something like this in mind: UPDATE [MyTable] SET [SomeField] = 1 WHERE [SomeField] is NULL Will it work or is it like modifying a collection while iterating over it? Or maybe write something like this? UPDATE [MyTable] SET [SomeField] = 1 WHERE [ID] IN (SELECT [ID] FROM [MyTable] WHERE [SomeFiel...

Intime and OutTime for the Modified date

Question is already posted on June 4, but still am not get the Proper answer Again Table Structure: T_Person – Table 1 CARDNO 168 471 488 247 519 518 331 240 518 386 441 331 T_Cardevent – Table 2 CARDEVENTDATE CARDEVENTTIME 20090225 163932 20090225 164630 20090225 165027 20090225 165137 20090225 165147 20090225 1657...

Need help in complex sql query

Say you have 2 columns you are grouping by and getting a count: select col1, col2, count(*) from yourtable group by col1, col2 Your col2 will be unique, but you will get repeated col1's. To avoid this, you can: select decode(lag(col1,1,0) over (order by col1),col1,' ',col1) col1, col2, cnt from (select col1, col2, count(*) cnt from...

joining one table multiple times to other tables

I have three tables: Table User( userid username) Table Key( userid keyid) Table Laptop( userid laptopid) i want all users who have either a key or a laptop, or both. How do i write the query so that it uses a join between table User and table Key, as well as a join between table User and table Laptop? The main problem is that in t...

Using LINQ2SQL to insert a large number of records...

We have a small c# tool that we threw together to parse a data file, build up some objects and insert them into the DB. The logic is essentially. string [] lines = File.ReadAllLines("C:\\Temp\\Data.dat") foreach(string line in lines) { MyDataObject obj = ParseObject(line); myDataContext.MyDataObjects.InsertOnSubmit(obj); } my...

SQL query--String Permutations

I am trying to create a query using a db on OpenOffice where a string is entered in the query, and all permutations of the string are searched in the database and the matches are displayed. My database has fields for a word and its definition, so if I am looking for GOOD I will get its definition as well as the definition for DOG. ...

How to use rails to find this result across 3 tables ?

A project has 3 tables : client, invoices, deliveries The relations are: client has_many invoices client has_many deliveries invoices belongs_to client deliveries belongs_to client Both invoices and deliveries model have a date field. Now I want to find all clients who has a...

What is wrong with my update statement with a join in Oracle?

I am working with an Oracle 10g Database. I have the following two tables: T_DEBTOR : - ID_DEBTOR - HEADER T_ELEMENT : - ID_ELEMENT - ID_DEBTOR - INSURER These two tables are joined using the ID_DEBTOR field. I want to update the T_ELEMENT.INSURER value with the associated T_DEBTOR.HEADER only if HEADER is not nu...

Is it true that all types of joins reduce to Inner joins in the presence of the check of one table's pk = other table's fk?

Is it true that no matter which type of join you use, if your WHERE clause checks one table's pk = other table's fk, it becomes same as an inner join as far as the result set is conncerned? In other words, if your sql query has some stuff like: "Select ... from A Left join B On (...) E, F Full Outer Join G on (A.pk = G.fk) ... WHERE A.p...

How can I iterate through a list of rows returned from a select statement in SQL?

Given a SQL statement: select id from myTable where id = -1 How do I iterate through each of the results? I want to perform a specific operation for each result e.g (in pseudocode): foreach (result from my statment) { delete all rows from myOtherTable that has a value of result; } How would I write a SQL statement to do this? N....