Basically I'm trying to pull a random poll question that a user has not yet responded to from a database. This query takes about 10-20 seconds to execute, which is obviously no good! The responses table is about 30K rows and the database also has about 300 questions. SELECT questions.id FROM questions LEFT JOIN responses ON ( questi...
My query: SELECT issues.*, comments.author AS commentauthor, comments.when_posted AS commentposted FROM issues LEFT JOIN (SELECT * FROM comments ORDER BY when_posted DESC LIMIT 1) AS comments ON issues.id=comments.issue ORDER BY IFNULL(commentposted, issues.when_opened) ...
I need to insert a data into a DB using query like this one: INSERT INTO Table1 (aID) VALUES (select aID from Table2 where aID="aID")) The problem is that if Table2 doesn’t have the aID I need to update it with it and extract the newly created ID. Is there a SQL command that automatically does it? ...
Hi, To start with here is the bigger picture of the task I'm trying to do. I need to create a xml file from the results of the particular SQL request and store it in a file on the client computer. For that I have a SQL script that does the DBMS_XMLGen with xslt, which I'm going to run from a command line with sqlplus and pipe the outpu...
I have defined a class FieldProperty with some of properties corresponding to field defined in it.ArrayList object contains objects of class FieldProperty. now i want to store data in table .There is one record in table corresponding to each object of FieldProperty.if i call query for each record insertion.It will be very slow.Is there a...
If I run the following in Management Studio (SQL Server 2008) : exec [USP_CNT_BookingDetail_ExtractAccountingPlanData] '4AFD6633-CB90-4165-913D-EE3EA74708DA', '7EF7CCB2-E09F-4408-AE2D-F857C063F2C1' I get the result back in less than a second I however I run it in VB.Net like this : Using aConnection = New System.Data.SqlClient.SqlCo...
In this presentation: http://docs.google.com/present/view?id=ddqht6x_1559tbtcgg Slide 6. The presenter's snippets_tags table has a primary key, why? isn't that just wasting space? I usually use the other two cols. and not bother with a primary key. ...
My current project is a WPF application with an SQL Server back end. In WPF, the UI can only be modified by the UI thread. If a UI modification needs to be done on another thread, then the dispatcher object can be called and given an action. Effectively, this is mapping my Delegate to a WM_ message. Since the linq datacontexts to SQL S...
Hi I am using SQL Server 2008, and trying to run the following query in management studio: UPDATE Table1 as T1 INNER JOIN Table2 as T2 ON T1.ID=T2.ID SET T1.SomeValue = T2.SomeValue GO Doesn't work though. Is this sort of thing supported? Karl [EDIT] Let me just be more clear, I wan't to do somethin...
I've just been reading 'Give us Real Domains!' on SQL Server MVP Erland Sommarskog's SQL Server Wishlist. I was thinking of trying out CREATE TYPE with bound rules but was alarmed to learn that this is a deprecated feature. Does SQL Server have anything resembling support for domains that is worth using? Are bound rules are worth usin...
I find myself doing the same things over and over again just to make one small modification to standard model output. I have a series of tables that I store information about products, etc. and all of which store prices. The prices are stored in US dollars but the output depends on the currency the user wants which is stored in a thei...
I am using Toad. Frequently i need to compare tables in two different test environments. the tables present in them are same but the data differs. i just need to know what are the differences in the same tables which are in two different data bases.Are there any tools which can be installed on windows and use it to compare. Much apprec...
When I run cmd.ExecuteScalar() or cmd.ExecuteNonQuery() the Output and InputOutput parameters on the command object get updated from the changes in the stored procedure. However the same does not happen for cmd.ExecuteReader(). This is happening on both Sql Server and MySql Connections. Is this behavior by design? ...
Hi all, How to retrieve create statement for table from db in C#? I need to create a script file containing create statement for an existing table in db using C#. I can generate create statement of storedproc using sp_helptext but can't generate it for table. Please help. ...
When I include the 2 commented out lines in the following subquery, seems that it takes forever until my Sybase 12.5 ASE server gets any results. Without these 2 lines the query runs ok. What is so wrong with that grouping? select days_played.day_played, count(distinct days_played.user_id) as OLD_users from days_played inner join days_r...
Lets say I have a table with "Groups of Questions" GroupID | value --------+------ 42 | How often do you 9071 | Other question ... And, for each group of questions I have "questions" and "possible answers" Group | QuestionID | value ------+------------+------ 42 | 5 | ... brush your teeth? 42 | 89 | .....
Hello, I have had some issues with downtime as a result of hitting the max_user_connections limit in MySql. The default connection timeout is 8 hours, so once we hit the limit (and having no access to kill the connections on our shared hosting) I simply had to wait 8 hours for the connections to time out. I would like to add the follow...
I want to order the results in a GROUP_CONCAT function. The problem is, that the selection in the GROUP_CONCAT-function is another function, like this (fantasy select): SELECT a.name, GROUP_CONCAT(DISTINCT CONCAT_WS(':', b.id, c.name) ORDER BY b.id ASC) AS course FROM people a, stuff b, courses c GROUP BY a.id I want to get a resu...
So I have a database with around 100 audit tables and I want to empty them out preferably in 1 sql query. The database has 2 sets of tables [audit] and [dbo]. I'm not even sure what I should be calling these groups of tables so finding any kind of results from google is proving difficult. Any suggestions? ...
I have this query: select total.lecgrouplecture(l.groupcode) lecturename, total.lecgrouptime(l.groupcode) lecttime from total.lecgroup l where term = (select term from total.CURENTTERM) and rownum < 10 order by lecturename I want to know what total.lecgrouptime(l.groupcode) is, and get this informat...