sql

Handling Timeouts In A Website SQLCommand Object and c#

I'm using visual studio 2008 and sql server 2005 and everything is working just fine under normal use. However if a user is on a page for a while several minutes with no activity then clicks a button on occassion the site throws the following exception ... Procedure or Function "sp_name" parameter '@SomeParameterName', which was not su...

What's the most efficient query?

I have a table named Projects that has the following relationships: has many Contributions has many Payments In my result set, I need the following aggregate values: Number of unique contributors (DonorID on the Contribution table) Total contributed (SUM of Amount on Contribution table) Total paid (SUM of PaymentAmount on Payment tab...

How effective can this Tagging solution be?

Im working on an image sharing site and want to implement tagging for the images. I've read Questions #20856 and #2504150 I have few concerns with the approach on the questions above. First of all it looks easy to link an image to a tag. However getting images by tag relation is not as easy. Not easy because you will have to get the im...

Prevent full table scan for query with multiple where clauses

A while ago I posted a message about optimizing a query in MySQL. I have since ported the data and query to PostgreSQL, but now PostgreSQL has the same problem. The solution in MySQL was to force the optimizer to not optimize using STRAIGHT_JOIN. PostgreSQL offers no such option. Update Revised I have isolated the part of the query tha...

How to get a list of names + a count of occurrences relating to the name from another table

Hi, Hope my subject line was descriptive enough. Basically, I have a table called Students, and one called Absences. The Students table contains student information (Name, DOB, StudentID etc.), the Absences table contains any absences (Date, Reason, related by StudentID). I want to get a full list of the students, plus a count of their...

Sql - tablename as variable

Hi folks, i am trying to execute this query: declare @tablename varchar(50) set @tablename = 'test' select * from @tablename This produces the following error: Msg 1087, Level 16, State 1, Line 5 Must declare the table variable "@tablename". What's the right way to have table name populated dynamically? TIA ...

SQL - SSMS2005 - How do I copy a database diagram to another database?

Is there a simple method for copying a database diagram from one database server to another? I have a diagram on a development server that needs to be moved to the production server. Thanks (Sql server 2005, SSMS2005 Express) ...

Efficient update of SQLite table with many records

I am trying to use sqlite (sqlite3) for a project to store hundreds of thousands of records (would like sqlite so users of the program don't have to run a [my]sql server). I have to update hundreds of thousands of records sometimes to enter left right values (they are hierarchical), but have found the standard update table set left_va...

Which would be better? Storing/access data in a local text file, or in a database?

Basically, I'm still working on a puzzle-related website (micro-site really), and I'm making a tool that lets you input a word pattern (e.g. "r??n") and get all the matching words (in this case: rain, rein, ruin, etc.). Should I store the words in local text files (such as words5.txt, which would have a return-delimited list of 5-letter ...

whats wrong in this LINQ synatx?

Hi, I am trying to convert a SQL query to LINQ. Somehow my count(distinct(x)) logic does not seem to be working correctly. The original SQL is quite efficient(or so i think), but the generated SQL is not even returning the correct result. I am trying to fix this LINQ to do what the original SQL is doing, AND in an efficient way as the o...

what is called KEY

CREATE TABLE `ost_staff` ( `staff_id` int(11) unsigned NOT NULL auto_increment, `group_id` int(10) unsigned NOT NULL default '0', `dept_id` int(10) unsigned NOT NULL default '0', `username` varchar(32) collate latin1_german2_ci NOT NULL default '', `firstname` varchar(32) collate latin1_german2_ci default NULL, `lastname` va...

SQL with Regular Expressions vs Indexes with Logical Merging Functions

Hello All, I am trying to develop a complex textual search engine. I have thousands of textual pages from many books. I need to search pages that contain specified complex logical criterias. These criterias can contain virtually any compination of the following: A: Full words. B: Word roots (semilar to stems; i.e. all words with certa...

Mysql query problem....

I have below values in my database. been Lorem Ipsum and scrambled ever scrambledtexttextofandtooktooktypetexthastheunknownspecimenstandardsincetypesett Here is my query: SELECT nBusinessAdID, MATCH (`sHeadline`) AGAINST ("text" IN BOOLEAN MODE) AS score FROM wiki_businessads WHERE MATCH (`sHeadline`) AGAINST ("text" IN BOOL...

Have 2 separate tables or an additional field in 1 table?

Hello, I am making a small personal application regarding my trade of shares of various companies. The actions can be selling shares of a company or buying. Therefore, the details to be saved in both cases would be: Number of Shares Average Price Would it be better to use separate tables for "buy" and "sell" or just use one table ...

LINQ thinks I need an extra INNER JOIN, but why?

I have a LINQ query, which for some reason is generating an extra/duplicate INNER JOIN. This is causing the query to not return the expected output. If I manually comment that extra JOIN from the generated SQL, then I get seemingly correct output. Can you detect what I might have done in this LINQ to have caused this extra JOIN? Thanks...

SQL query to select distinct record with 2 or more repetition in another field

So I have this table of book orders, it contains 2 columns, one is the order ID(primary key) and another is the ID of the book that the customer ordered. For example: +---------+--------+ | OrderID | BookID | +---------+--------+ | 0001 | B002 | | 0002 | B005 | | 0003 | B002 | | 0004 | B003 | | 0005 | B005 | | 0...

Strange data swapping error occurs when I attempt to update rows in my table from another table in my sql database...please help!

So I have a table of data that is 10,000 lines long. Several of the columns in the table simply describe information about one of the columns, meaning, that only one column has the content, and the rest of the columns describe the location of the content (its for a book). Right now, only 6,000 of the 10,000 rows' content column is filled...

How to update a record with multiple keys

I am trying a database app on Android. I want to use the SQLiteDatabase update(...) convenience method to update a record. Normally, for a WHERE clause of a single key this is working. Following code is working fine:- values.put("testname", "Quiz1"); mDB.update("Tests", values, "id=?", new String[]{"2"}); //this statement works Howe...

building SQL Query From another Query in php

When I Try to built Query from another Query in php code I Faced some problem can you tell me why? :( code : $First="SELECT ro.RoomID, ro.RoomName, ro.RoomLogo, jr.RoomID, jr.MemberID, ro.RoomDescription FROM joinroom jr,rooms ro where (ro...

how to backup database from sqlexpress

Hi all Please help me to backup my database from sqlexpress. ...