Should queries live inside the classes that need the data? Should queries live in stored procedures in the database so that they are reusable? In the first situation, changing your queries won't affect other people (either other code or people generating reports, etc). In the second, the queries are reusable by many and only exist in o...
Hello: I am working on a work queueing solution. I want to query a given row in the database, where a status column has a specific value, modify that value and return the row, and I want to do it atomically, so that no other query will see it: begin transaction select * from table where pk = x and status = y update table set status ...
The SQL Server was not found or was not accessible I got this error while setting up a system that requires a remote computer to access SQL 2008 stored on another computer in the same domain: The server was not found r was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connect...
When to log errors in ASPX when using database connections? In the following code, the authors of Pro ASP.NET 3.5 in C# 2008 (Third ed), suggest logging an SQL error in the catch block. My questions are: Is this common practice? Is this the best practice? In short, would it be better to close the current connection and handle the erro...
Hello, How have people intergrated custom CRM type applications with email? I have a Access 2003 front-end application with a SQL Server 2005 backend. One CRM part of the application tracks the activity with the customer in a traffic log table. Sometimes the salesstaff has communication with their customer using email instead. What...
Hi, This is a bit of code in one of the pages I am reviewing and trying to update. What I am trying to do is swap the inline query for stored procedures. This is the bit of code: numOfDelRec = 1 Do While (numOfDelRec > 0) cm.CommandText = "DELETE Folder WHERE FolderID IN (SELECT ch.FolderID FROM Folder ch LEFT JOIN Folder p...
I have a temp table, let's call it #invoices, defined as create table (id int identity(1, 1), billed money, credited money, balance money) i have the following data in it Billed Credited 140.00 20.00 60.00 20.00 -20.00 I would like to update the balance column with the running balance. so that the...
Ok, so I'm working on a basic search SPROC. One of the Parameters is a search text (this will be the test the user enters in, words separated by spaces etc.) Now all I need is to search these words on a single column in a table, BUT I want it to have ALL the keywords that were entered (at the moment all I can do is if 1 of them is there...
I have a simple where clause on a keyed field, a join between two tables on a different field, and then a sort on another field of the second table. I have keys on everything, but it seems that the describe is giving me a filesort and a 30 second query execution : mysql> describe SELECT * FROM `alias` LEFT OUTER JOIN `aliaspoint` ON (`...
Hi all, I work in a large, separated development team. The project I'm currently working on has an Oracle DB team that develops the stored procedures and other related components that our J2EE web-tier talks to. One thing that's arisen from development is the concept of a closed cursor when results aren't found. From my experience, c...
Hi, I am running the following command in a batch file: osql -S dbname -U username -P password -i C:\inputSQL.sql -o C:\postMigration.log -n The dbname, username, and password have all been set correctly. However , when I run the batch file I get this output to the "C:\postMigration.log" log [SQL Native Client]Shared Memory Provid...
Sorry to ask this kind of questions. But I am eager to know some good sites where I can have the opportunity to have some sql competitions. I found 1 TroyK's T-SQL Challenge but it is not free. I am looking for a free one. And also C# programming competitions. One I found is Code Chef I am Looking for some more. Also, it should ...
Here's part of my table: id team_id log_id 1 12 1 2 12 1 3 12 1 4 12 1 5 1 2 6 1 2 7 1 3 8 1 3 What query would produce this output (so only the records with the highest log_id values are returned that correspond to ...
Quite often at work we'll have a scenario where we have an item in the database that we need to assign multiple staff members to. Normally we do this assigning by creating a new table with two columns, the first being the items ID and the second the Staff member's ID so when we want to either look at all the items assigned to a staffmemb...
I have a parent-child relationship in an Oracle 9i database-table like: parent | child 1 | 2 2 | 3 2 | 4 null | 1 1 | 8 I have an absolute parent (e.g. child 1) and i need a csv list or resultset of all childs of this parent. ...
Hi, I'm trying to display a table showing a list of courses. I would like to include a column which shows the number of people who have completed the course and if possible, also the number who have yet to complete it. My table structure is like so: Courses courseid | title | etc Studying courseid | studentid | some other fields | ha...
I have a JDBC Date column, which if a i use getDate is get the 'date' part only 02 Oct 2009 but if i use getTimestamp i get the full 'date' 02 Oct 2009 13:56:78:890. This is excatly what i want. However the 'date' returned by getTimestamp 'ignores' the GMT values, suppose date; 02 Oct 2009 13:56:78:890, i end up getting 02 Oct 2009 15:5...
I'm using an INSERT .. SELECT to 'duplicate' rows from a table (with a different foreign key). This works fine, however it's inserting multiple rows and I now need to do another INSERT to insert multiple rows that are linked to each individual row in the previous SELECT... does that even make sense? Haha. Basically there's a one to man...
I wan't sure how to word this question so I'll try and explain. I have a third-party database on SQL Server 2005. I have another SQL Server 2008, which I want to "publish" some of the data in the third-party database too. This database I shall then use as the back-end for a portal and reporting services - it shall be the data warehouse. ...
If i have a Date/Time field in Access and i need to find all the entries that are in a specific year... say 2009. If my fields are set up as MM/DD/YYYY, how would i do a query to essentially do //2009 update Thanks for the help everyone, but i guess the issue is that I'm kind of dynamically constructing my SQL statement in ASP. "SELEC...