I have a query to return how much is spent on-contract and off-contract at each location, that returns something like this: Location | ContractStatus | Expenses -------------+----------------+--------- New York | Ad-hoc | 2043.47 New York | Contracted | 2894.57 Philadelphia | Ad-hoc | 3922.53 Seattle ...
I have to delete rows from guide_category that have no relation with guide table (dead relations). Here is what I want to do, but it of course does not work. :) DELETE FROM guide_category AS pgc WHERE pgc.id_guide_category IN (SELECT id_guide_category FROM guide_category AS gc ...
The following MySQL query: select `userID` as uID, (select `siteID` from `users` where `userID` = uID) as `sID`, from `actions` where `sID` in (select `siteID` from `sites` where `foo` = "bar") order by `timestamp` desc limit 100 …returns an error: Unknown column 'sID' in 'IN/ALL/ANY subquery' I don't understand what I'm doing wron...
I am using the below sql to import some data from a file from the intranet. However every once a while, there will be a timeout error and the proc would fail, which is why I am using a transaction. If the transaction fails, I want the ImportedTable to get cleared. However this does not seem to happen. Is there anything I am missing here?...
On a web server, we plan to pull back a file stored in SQL in a FILESTREAM using the Win32 streaming API. Are there any special ports that must be opened to make this work? Or will the standard 1433 suffice? ...
Bounty open: Ok people, the boss needs an answer and I need a pay rise. It doesn't seem to be a cold caching issue. UPDATE: I've followed the advice below to no avail. How ever the client statistics threw up an interesting set of number. #temp vs @temp Number of INSERT, DELETE and UPDATE statements 0 vs 1 Rows affected by INSERT, DE...
I'm involved is a project to migrate a project from Oracle to MySQL. I Have a script that i'm running from the MySQL shell command, called CreateTables.sql that looks like this internally: source table\DropForeignKeys.sql source tables\Site.sql source tables\Language.sql source tables\Country.sql source tables\Locale.sql source tables\T...
I've never learned how joins work but just using select and the where clause has been sufficient for all the queries I've done. Are there cases where I can't get the right results using the WHERE clause and I have to use a JOIN? If so, could someone please provide examples? Thanks. ...
I have a mysql trigger that logs every time a specific table is updated. Is there a way to also log WHICH PHP SCRIPT triggered it? (without modifying each php script of course, that would defeat my purpose) Also, is there a way to log what was the SQL statement right before the UPDATE that triggered it? Thanks Nathan ...
I am a beginner user of SQL Reporting Services. I wrote a few reports. They all use stored procedures on my development SQL server. Now I want to deploy them into production, and repoint the datasets and datasources to my production SQL db. How best to do this? Can I just do a global change from development_server_name to production_ser...
I'm about to write some example applications and accompanying documents comparing ways of accessing information stored in relational databases. To demonstrate real-life requirements, I need to include a realistic dataset of hundreds of thousands of facts. Is anyone aware of publicly available, free datasets of that magnitude, of dataset...
I have an Access database of information where I need to replace text that may reside in 1 of 10 columns. I have a number of different requests for find and replace that need to be done. I need to do this twice a day. These are the details. We receive a download of data twice a day that has course information in it. A record can have 10...
Hi, I read it somewhere" "A separate database should be created if SSIS logging is required. (Do not use the Sysdtslog90 table in either master or msdb. This is not a security related concern but could be a performance issue since SSIS can generate a lot of logging data. Microsoft recommends creating a separate datab...
We're thinking about putting up a data warehouse system to load with web access logs that our web servers generate. The idea is to load the data in real-time. To the user we want to present a line graph of the data and enable the user to drill down using the dimensions. The question is how to balance and design the system so that ; (...
I have a database table with the following structure - Week_End Sales 2009-11-01 43223.43 2009-11-08 4324.23 2009-11-15 64343.23 ... Week_End is a datetime column, and the date increments by 7 days with each new entry. What I want is a SQL statement that will identify if there is a week missing in the sequence. So, i...
I have four tables: Customer, CustomerCategory, Limit, and LimitCategory. A customer can be in multiple categories and a limit can also have multiple categories. I need to write a query that will return the customer name and limit amount where ALL the customers categories match ALL the limit categories. I'm guessing it would be simila...
Hi, I read somewhere: Analysis services should run under network account Ensure SQL Browser uses a domain account Why is that? ...
I have a database table with the following structure - Week_End Sales 2009-11-01 43223.43 2009-11-08 4324.23 2009-11-15 64343.23 ... What I want is a SQL statement that will take a start and end date, and identify all rows within that period where the sales figure is more than 3% above or below the average sales f...
How do I write this query in linq VB.NET? select top 15 count(1), A.Latitude, A.Longitude from Bairro A inner join Empresa B on B.BairroID = A.BairroID where A.CidadeID = 4810 group by A.Latitude, A.Longitude order by COUNT(1) desc I reached this code: Dim TopBairros = (From A In Bairros _ Join B In New BO.Empresa()...
I have a table as follows Name | Words A words for A1 here B words for B1 here C words for C1 here A words for A2 here B words for B2 here C words for C2 here I want to pivot the above table to get the following result A ...