I have a piece of legacy VB6 code that is inserting data into a sql server 2000 database table. The code uses the row count returned for various purposes. We recently put a trigger on the table which calls a stored procedure to update another table. It now appears that the VB6 code is picking up the row count returned from the triggered...
Thankfully, I haven't had to work with particularly complex SQL queries before. Here's my goal. I have the table hams, which I would like to cross-join with the table eggs - that is, get all ham-egg combinations... to an extent. The eggs table also has an attribute how_cooked, which is defined as ENUM('over-easy','scrambled','poached')...
Hi, This might be a bit difficult to explain but I have two columns in my SQL server database which I have simplified... Items ID itemName voteCount score Votes itemID score So, basically I am storing every vote that is placed in one table, but also a count of the number of votes for each item in the item table along with it's averag...
I'm adding "promo code" functionality to an online shopping cart written by someone else. This is a pretty micky mouse architecture question, but I would like to hear a couple of opinions. All three of my ideas would work, but which do you think is best for maintainability? So, here's the basic table structure for the promo codes: CR...
I am trying to insert about 100,000 in my SQL Server database. It is really slow when I create 100,000 separate queries, so I tried to insert all these records in one query, it worked for the first few thousands records but then it threw me a timeout error. What would be the fastest way to insert multiple records into database? ...
Hi, So I am new to working on web projects in general. I am working on an MVC application in Visual Studio 2008. I have generated an SQL database within VS, and I have deployed my application on IIS. However when I try to do anything in the application which will spark an SQL query, I get the following error: "Failed to generate a user...
I am writing a stored procedure like this.. declare @avg float set @avg = select avg(rating) from videorating where videoid = 4 Can I write it like this? Can I store the value in @avg? ...
My question is as follows: User name = Admin Whenever I perform an insert/update/delete operation on a table "a". I need to have a trigger that would insert the username in table "b" Is that possible? ...
I have a table called Orders in which the data looks like this: EMpID OrderValue OrderID 1 100 1 2 167 89 .... There are multiple orders for each empID. What I want is to get output in this form EMPID RANK VALUETOTAL VALUETHISEMPID 1 1 300 100 4 2 300 50\ ..... If there are mu...
in the sqlite database, i set a field typed blob. then, i wrote java code: PreparedStatement preStmt = con.prepareStatement("INSERT INTO zz(pic) VALUES(?)"); preStmt.setBinaryStream(1, new FileInputStream(file), file.length()); i got a exception: java.lang.AbstractMethodError: org.sqlite.PrepStmt.setBinaryStream(ILjava/io/InputStrea...
How to split comma separated text (list of IDs) in MySQL stored procedure to use result in SQL "IN" statement. SELECT * FROM table WHERE table.id IN (splitStringFunction(commaSeparatedData, ',')); ...
Hi guys I have two tables as such: Categories: ID - Name - Desc Items ID - Name - CategoryID - Desc - Price I want a query that returns a list of categories ranked by most occurences in the items table ...
When am using sequence container to roll back my transactions in execute sql task i am getting the error Connection manager Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025 "The partner transaction manager has disabled its support for remote/network transactions.". hav...
Is there a more elegant way of doing this. I want to replace repeating blanks with single blanks.... declare @i int set @i=0 while @i <= 20 begin update myTable set myTextColumn = replace(myTextColumn, ' ', ' ') set @i=@i+1 end (its sql server 2000 - but I would prefer generic SQL) ...
hey, following problem here: i have table with persons that have one location, if i delete the location for the location table, i don't wont to lose the assigned persons. ive a entry in the location with the id=1 and the name is: "No location". and that what I'm trying to do, but i fail! ALTER TABLE `persons` ADD CONSTRAINT `persons_i...
Hey! Our project keeps world data base in tree-structure inside INNODB table of MySQL db. Earth is the root, then countries, then country regions and the locations are the leaves. A foreign-key is used to provide a fast access to children (for example cities in a region). Now we want to implement a fast geo-search in the data-base for...
I have this SQL query but its running soo slow, SELECT wr.wr_re_id as ReID, sum(wr.wr_total) as PRTotal FROM Workorder2 w INNER JOIN Workorder_Row2 wr ON wr.wr_wo_id = w.wo_id WHERE (w.wo_type = 1 or w.wo_type = 2) AND wr.wr_row_type = 2 AND w.wo_lo_id like '%' AND w.wo_date_time >= '2010-01-01'...
Does anyone know how to reduce the 'Number of executions' you can see in the execution plan of a SQL Query in MS SQL Server? We have a query that runs slow on one production server (others are fine). When checking the execution plan, we see that one of the first steps is a Clustered Index Scan. On the servers that run fine, this scan is...
Hi, I'm trying to group by multiple columns here - one on each table. It's a scenario where I want to find the top portfolio value for each client by adding their current portfolio and cash together but a client may have more than one portfolio, so I need the top portfolio for each client. At the moment, with the code below I'm getting ...
after did Query query =hibernate.createSQLQuery("select abc,def from table"); Is it possible to auto "parse" the result to "pojo" list? so that i can do below List<CustomPOJO> abc = query.list(); //CustomPOJO is pojo not entity , no @Entity tag ...