Hi, I didn't really know how to formulate the title. But this is my problem. We are using SqlCacheDependencies to update out cache objects. But the query behind this is "complicated" that we would like to make it somewhat easier. Our first thought was to add a fieldname like DateChanged and use the DateChanged to check if an object has...
Hello! I am using OleDbDataAdapter to fill a table in the DataSet. The problem is that select sql statement is very complex (using left joins to get data from numerous tables) and OleDbCommandBuilder can't write an insert/update statement for me. I was wondering what is the best course of action here? There aren't that many columns in ...
Hi, In some SQL query performance tuning, I noticed that the following query was running slowly but it wasn't thrashing the CPU and there appeared to be no other system bottlenecks to cause it to run slowly. In fact the CPU average was 15% whilst it ran: UPDATE: The query in question runs in a cursor loop which contains 800 records: c...
I need to replace all iframe tags, stored as nvarchar in my database. I can find the entries using the following sql-question: SELECT * FROM databasename..VersionedFields WHERE Value LIKE '%<iframe%' Say I want to replace the following code segment: code before iframe <iframe src="yadayada"> </iframe> code after iframe With this: ...
I want to swap to tables in the best possible manner. I have an IpToCountry table, and I create a new one on a weekly basis according to an external CSV file which I import. The fastest way I've found to make the switch was doing the following: sp_rename IpToCountry IpToCountryOld go sp_rename IpToCountryNew IpToCountry go The prob...
I have a records of events with starttime and endtime for a calendar control. I want to get the events done on a particular date say 2/28/2009 But the db table has date data in form 2/28/2009 10:00:00, 2/28/2009 12:00:00. I tried this query in a sproc created in VS 2005 IDE but it didn't work ALTER PROCEDURE dbo.spSelectBenchEvent (...
I have a Java project which will include a number of large SQL statements for querying the database. My question is: where should I store them? I'm fairly sure I want each statement its own text file managed by source code control. As Java doesn't support multi-line strings I can't easily put the SQL in my .java files and I don't thi...
Some LINQ queries still puzzle me. for a table 'Hits' containing two columns, 'Page' and 'Date', I want to find the most Pages with the most rows in a defined slice of time. In SQL I would use this: SELECT TOP 10 [Page] ,COUNT([Page]) as Number FROM dbo.[Hits] WHERE [Date] >= CONVERT(datetime,'14 Jan 2009') AND [Date] < CO...
Ho to all! How can I select count(*) fro two different tables (call them tab1 and tab2) having as resulta: Count_1 Count_2 123 456 ? I've tried this: select count(*) Count_1 from schema.tab1 union all select count(*) Count_2 from schema.tab2 but all i have is: Count_1 123 456 Thanx in advance ...
Hello All, I have a quick question - is there a best practice in SQL Table design for storing "either/or" data? I have the following problem - I need to store template data (defining folder structure) in a SQL table. Any given folder might have a static name (for example "Emails") or it might be dynamically generated for each instance...
I have a page that that lists products returned from a mysql query. The query can very greatly depending on many different things. What I want to do is give the user an option to narrow the current results by series of drop-downs. For example to narrow the product type. But to get the available product types I am currently just checking...
Im using an Oracle database. In my query 100 rows are fetched. If I want to filter rows between rownum 50 and 60, what would be the query? SELECT EMPLID, EFFDT, ACTION, ACTION_REASON from JOB where emplid ='12345' ...
I am writing a SQL Function that will take in a decimal and return the base32 representation of that decimal. My problem is with the decimal to ascii conversion. I am allowed to run the following query and return an ascii character "SELECT CHAR( 65 )" which returns "A" However in my function when I am trying to build my output strin...
I'm working on my MSSQL index defragmentation script. Certain kinds of indexes can be rebuilt online, and other kinds can't. For clustered indexes, it's easy enough to see if the table contains any LOB columns, but for a non-clustered index I need to specifically know if there is any LOB columns covered by that specific index. I used ...
Does anyone know the equivalent of ISDATE() in LINQ to SQL query syntax? I've got a varchar field in SQL that contains dates and I need to filter out the non-date rows. was hoping for something like this: var query = SomeDataContext; query = from p in query where p.ISDATE(field1) == true; select p; also, how would one cas...
Lets say I have a table describing cars with make, model, year and some other columns. From that, I would like to get one full row of each make and model for latest year. Or to put it in another way. Each row would have unique combination of make and model with other data from corresponding row with largest value of year. Standard SQL...
I am very new to SQL i can work with basic statements fairly easily but i haven't figured out loops yet. Foreach(JobHeaderID AS @OldJobHeaderID in dbo.EstimateJobHeader WHERE EstimateID=@OldEstimateID) { INSERT EstimateJobHeader (ServiceID,EstimateID) SELECT ServiceID, @NewEstimateID FROM EstimateJobHeader WHERE EstimateI...
Let's say that we have we have a table with the classic 'manager id' recursive relationship: Users user_id int manager_id int (refers to user_id) If you randomly select 2 rows in the table- or 2 nodes- how do you find the lowest level, common ancestor? My platform is SQL Server 2005 (Transact-SQL) but any ANSI compliant SQL will ...
Duplicate of: http://stackoverflow.com/questions/292026/is-there-a-good-reason-to-use-upper-case-for-t-sql-keywords Simple question. I personally find a string of lowercase characters to be more readable than a string of uppercase characters. Is some old/popular flavor of SQL case-sensitive or something? For reference: select th...
I'm currently putting together some changes in our data model which include changing a column (that happens to be part of the primary key) so that it is no longer an identity. Is there a way to do this short of actually removing and recreating the entire column or table? The autogenerated code from SSMS does just that but I was wondering...