I need to delete records in a table if the current date is greater than the record creation date + a preset number of days (defined by @numberOfDays). I am using the following SQL statement but am not sure if it’s very efficient. Is there a better way? I am using MS SQL 2008 server. DELETE FROM deviceManager.Test2 WHERE DATEADD(day, @n...
I am using SQL Server 2008 and developing a project which is in maintenance phase. I want to insert record in a table whose primary key is an Integer but not an identity. e.g. table name is tblFiles and fields are ID, FileName, FileContent. Actually that table is in use so I don’t want to make any schema change in it. And I want the ...
I'd like to return a list of items of only those that have two or more in the group: select count(item_id) from items group by type_id; Specifically, I'd like to know the values of item_id when the count(item_id) == 2. ...
Hi, I am working on an Access database and in it I need to extract several reports. For one of them I need to compare two lists of numbers, List1 and List2(I get both from separate queries) and display only those numbers that are in List1 but not in List2. Can anyone help please? Thank you. ...
So my .NET utility is performing massive amounts of inserts dynamically and it would make sense to drop the index while performing these and reconstructing it after performing the inserts. Is the best way to do this using OracleCommand and setting command text to literal SQL? So for example: OracleCommand dropIndexCommand = new Oracle...
Can I force a log out through SQL for asp.net membership account? ...
If I were to define some function in the database (perhaps Postgres, or any other database): create or replace function isValidCookie(ckie); I would call it from SQL as: select * from cookietable c where isValidCookie(c.cookie); How can I call a custom function such as this from Hibernate? ...
I've been having it analyze my queries and pushing the recommendations for some time now. Are there any potential pitfalls with doing this? Would I be better of creating my own indexes by hand? ...
Using SQL Server 2008, but could relate to other databases as well probably. If I had the following data (table t): id text date 1 Data1 2/1/2009 2 Data2 2/2/2009 3 Data3 2/3/2009 4 Data4 2/4/2009 5 Data5 2/5/2009 How could I find the index of a certain record? indexOf(select id from t where id = 1) = 0 or indexOf(select id from...
SELECT col1, col2, dbo.myFunc(@param1, @param2, col1, col2) FROM theTable how do I add a "WHERE" here to the result of the function dbo.myFunc, like WHERE resultOfMyFunc > 10 for example? ...
If I wanted to know how many entries I have where product_price is within the 500-1000 range. How can I do that? Thanks! ...
Consider the following table: Song ---- SongID GUID Primary Key Name Varchar dateAdded DateTime I am creating a website that plays songs from the Song table. I want to generate a randomly ordered playlist for each visitor of the site that persists across requests without using any server-side storage (no session-style or database sto...
I am checking website entrys that are recorded in a database columns: browser, click_type_id, referrer, and datetime if multiple rows have the same browser, click_type_id, and referrer and are timestamped (occur within 1 minute of one another) they are considered a duplicate. I need a sql statement that can query for these duplicates ...
Is there a way to get SQL Server 2005 to call back to a connected application, such that the connected application will know when a record in a table has had a field modified by another application using the same database? A simple example would be two instances of the same application connecting to the same table in the same database. ...
I can't figure this one out. On SQL Server I have a process that is run dozens of times per second (data being sent to the server). The process runs great, processing requests takes between 50ms and 200ms. Then, roughly (but sporadically) once every 1.5 minutes all requests suddenly take 15000ms to 22000ms (15 to 22 seconds). At the same...
I have the below query... It works but it runs extremely slow. Was hoping someone might be able to give me a few tips to improve execution time? SELECT tb_clients.*, tb_clients_phone_fax.* FROM tb_clients, tb_clients_phone_fax WHERE tb_clients.client_id=tb_clients_phone_fax.client_id AND MATCH (client_company,client_description,client_k...
Hi, My problem is on Oracle, but is probably database independent (?). I have the following tables: aa vid cb --- -- 1 10 2 15 bb vid cb --- -- 3 25 4 24 *rep repid vid p ----- --- -- 99 1 aa 99 2 aa 99 3 bb 99 4 bb The column p indicates in which table to get the row....
Hi there This query works well as you can see I have to put split between between @SearchCriteria and the rest of the query due to it's varchar. If I forced, the syntax works well BUT it return nothing when you query possible because extra ' Can you help? ALTER PROCEDURE [dbo].[sp_rte_GetRateList] ( @TenantID INT, @Cu...
Is there a cross platform solution for sql? My prototype was in sqlite. I am switching to a server that offers tsql and i was considering mysql in the past for my webservers(maybe i should stick to tsql and sqlite). I am wondering if theres a .NET lib that allows me to write sql compatible with all. Some annoyance i had was in create ta...
I was just trying this knowing that my select would return one row. Is something like this possible or will I need a temporary variable? lets say my stored procedure took one parameter: exec dbo.GetUserData @UserName = UserName from @MyTempTable where UserId= @UserId Or what if the parameter expected was XML? Is there a way I can do ...