Is it possible to use SQL Server 2008 CROSS APPLY with LINQ-2-SQL? Example SQL: select d.dateCol, tvf.descr, tvf.value from dateTable d cross apply tvFunction(d.dt, 'anotherParam') tvf where d.category='someCat' CROSS APPLY enables using values from a table (dateTable in the example) as parameters to a tablevalue function. This is v...
I have a stored procedure that returns 80 columns, and 300 rows. I want to write a select that gets 2 of those columns. Something like SELECT col1, col2 FROM EXEC MyStoredProc 'param1', 'param2' When I used the above syntax I get the error "Invalid Column Name". I know the easiest solution would be to change the stored procedure,...
Hi, When running a Jasper report in which the SQL is embedded in the report file (.jrxml), is it possible to see the SQL that is executed? Ideally, I'd also like to see the values that are substituted for each of the $P{} placeholders. Cheers, Don ...
I have a multi-user application that keeps a centralized logfile for activity. Right now, that logging is going into text files to the tune of about 10MB-50MB / day. The text files are rotated daily by the logger, and we keep the past 4 or 5 days worth. Older than that is of no interest to us. They're read rarely: either when develop...
I'm attempting to put together some basic report screens. I've got some fairly complicated SQL queries that I'm feeding into ActiveRecord's find_by_sql method. The problem I am having here is that I am losing the order of the columns as given in the original query. I'm assuming that this is because the Hash class does not preserve ent...
Using the following query: SELECT pe.prodtree_element_name_l, MAX(rs.resource_value) AS resource_value FROM prodtree_element pe LEFT JOIN resource_shortstrings rs ON pe.prodtree_element_name_l_rk = rs.resource_key WHERE rs.language_id = '5' AND pe.prodtree_element_name_l <> '' GROUP BY prodtree_elemen...
I have some data with messed-up accented characters. For example in the data we have things like ClΘmentine that should should read Clémentine I'd like to clean it up with a script, but when I do this for example Select Replace('ClΘmentine', 'Θ', 'é') this is what I get: Clémenéine Apparently Θ matches both Θ and t. Any ideas ...
I've often had to load multiple items to a particular record in the database. For example: a web page displays items to include for a single report, all of which are records in the database (Report is a record in the Report table, Items are records in Item table). A user is selecting items to include in a single report via a web app, and...
Currently we have a table that we use to track inivitations. We have an email field that is indexed but we also have three optional keys that the user can specify when adding new record emails. We don't allow duplicates so we have to query if the email plus the optional keys already exists. Currently the keys are only added to the select...
In the following one to many CREATE TABLE source(id int, name varchar(10), PRIMARY KEY(id)); CREATE TABLE params(id int, source int, value int); where params.source is a foreign key to source.id INSERT INTO source values(1, 'yes'); INSERT INTO source values(2, 'no'); INSERT INTO params VALUES(1,1,1); INSERT INTO params VALUES(2,1,2)...
I have a table that has an insert trigger on it. If I insert in 6000 records into this table in one insert statement from a stored procedure, will the stored procedure return before the insert trigger completes? Just to make sure that I'm thinking correctly, the trigger should only be called (i know 'called' isn't the right word) once b...
I guess the real question is: If I don't care about dirty reads, will adding the with (NOLOCK) hint to a SELECT statement affect the performance of: the current SELECT statement other transactions against the given table Example: Select * from aTable with (NOLOCK) ...
Assume a table with the following columns: pri_id, item_id, comment, date What I want to have is a SQL query that will delete any records, for a specific item_id that are older than a given date, BUT only as long as there are more than 15 rows for that item_id. This will be used to purge out comment records older than 1 year for the i...
Is there a way to get the raw SQL string executed when calling PDOStatement::execute() on a prepared statement? For debugging purposes this would be extremely useful. ...
Need to calculate a stock market portfolio based on trades in a database. The calculation I'm looking to do via SQL are average price, rate of return, portfolio value, etc. Sample data: Stock Shares Price Value A 100 50 5000 A -20 60 -1200 A 50 40 2000 I haven't been able to do it with SQ...
What is a fast way to select a random row from a large mysql table? I'm working in php, but I'm interested in any solution even if it's in another language. ...
(sql server 2005) A person uses their cell phone multiple times per day, and the length of their calls vary. I am tracking the length of the calls in a table: Table: Calls [callID, memberID, startTime, duration] I need to a query to return the average call length for users PER DAY. Per day means, if a user used the phone 3 times, fi...
I'm trying to write some SQL that will delete files of type '.7z' that are older than 7 days. Here's what I've got that's not working: DECLARE @DateString CHAR(8) SET @DateString = CONVERT(CHAR(8), DATEADD(d, -7, GETDATE()), 1) EXECUTE master.dbo.xp_delete_file 0, N'e:\Database Backups',N'7z', @DateString, 1 I've ...
Hi All, I have a SQL Server 2000 database with around a couple of hundred tables. There are several SQL user accounts that can access this database but each one has different permissions granted on tables in the DB. How do I create a script to give me a report of the permissions granted to a particular user. i.e. to generate something...
I'm working on a forums system. I'm trying to allow users to see the posts they've made. In order for this link to work, I'd need to jump to the page on the particular topic they posted in that contained their post, so the bookmarks could work, etc. Since this is a new feature on an old forum, I'd like to code it so that the forum syste...