We have a sql 2000 db that has some replicated tables (from another server) We're using full text indexing Sometimes, network connectivity stops replication, requiring us to restart it, which isn't a problem- however, this seems to remove the replicated tables from the full text catalogs, which causes problems Any ideas how i can fix t...
what is the difference (system resource wise) between using views and temporary tables? I have a complex report that will be built from numerous tables. I am trying to determine if I should use a series of views or temp tables (SQL 2008). Is there a difference? ...
Hi everyone, As an exercise (read:interview question) in index optimisation, I need a query which is slow on the standard AdventureWorks database in SQL2005. All the queries I've tried take about 1 second and I would prefer to have a query which takes multiple seconds so that it can be optimised effectively. Can anyone here create such...
Hi there, I'm building a website for property agents and tenants. Tenants can sign up and fill in their desired locations for properties, including Street, Town and Postcode. Once they sign up, this automatically emails agents who have properties that match those search criteria. At present I have the query set up as follows so that it...
I have to find the next available id (if there are 5 data in database, I have to get the next available insert place which is 6) in a MySQL database. How can I do that? I have used MAX(id), but when I delete some rows from the database, it still holds the old max value it didn't update. ...
Hi, I am using SQL Server 2008 and I have the following SQL script: Select o.CustomerId as CustomerNoId, OrderValue, OrderDate From dbo.Orders as o Inner Join ( Select Top (10) CustomerId From dbo.Customers where Age < 60 ) As c On c.CustomerId = o.CustomerId This works as desired when used with dbo.Customers and dbo.Orde...
I am trying to create a series of queries in Access that count the number of clients of each ethnicity in each of 77 counties. Here is SQL code for one of my queries that I'm trying to do this for... SELECT [ClientsByCounty-Asian].County, [ClientsByCounty-Asian].CountyName, Count([ClientsByCounty-Asian].Asian) AS CountOfAsian FROM [Cl...
I am having some problems with MySQL and selecting column count from a joined table. I have a feeling this is going to require a sub-select statement to fetch the rows I would like to join instead of doing it in my primary where condiutional. I am trying to select a list of project which are linked to a certain user. I would also like t...
I have a SQL Server 2000 database with approximately 220 tables. These tables have a number foreign key relationships between them. Through performance analysis, we've discovered a number of these foreign key relationships are missing indexes. Instead of being reactive to performance problems, I'd like to be pro-active and find all fo...
Hi, I have a query that looks a bit like this: SELECT weekEnd, MAX(timeMonday) FROM timesheet GROUP BY weekEnd The valid values for timeMonday are: null, -1, 0, 1-24. At the moment, MAX() places the preference of those values in the order null, -1, 0, 1-24, however what I actually want is -1, null, 0, 1-24, so that null is considered...
Given the following schema / data / output how would I format a SQL query to give the resulting output? CREATE TABLE report ( id BIGINT AUTO_INCREMENT, name VARCHAR(255) NOT NULL UNIQUE, source VARCHAR(255) NOT NULL UNIQUE, PRIMARY KEY(id) ) ENGINE = INNODB; CREATE TABLE field ( id BIGINT AUTO_INCREMENT, name VAR...
Disclaimer: I have figured out the problem (I think), but I wanted to add this issue to Stack Overflow since I couldn't (easily) find it anywhere. Also, someone might have a better answer than I do. I have a database where one table "Common" is referenced by several other tables. I wanted to see what records in the Common table were o...
I'm trying to determine if there is a way to accomplish this elegantly. I have a query: SELECT TASK.*, PROJCOST.act_cost, PROJCOST.target_cost FROM task LEFT OUTER JOIN projcost ON task.task_id = projcost.task_id I just found out that PROJCOST.target_cost and act_cost are not 1 to 1 and need to be summed. I understand how to use SUM ...
Hello Everyone, I currently have mirroring setup between three computers, principle, mirror, and witness. During the day, all these systems are fine; they failover with the failover command, and the failover when unplugging any of the stations. My problem is at night I lose Quorum between the three stations, and the only way to get it b...
Is there a way to do a conditional insert in the compact edition? I've tried two ways that I think would work on SqlServer: INSERT INTO CUSTQUOTE (QTE_ID) VALUES (1) WHERE EXISTS(SELECT * FROM JOB WHERE JOB_NUMBER = 'EW090800345') There was an error parsing the query. [ Token line number = 2,Token line offset = 1,Token in error = WHERE...
The following query returns the total amount of orders, per week, for the past 12 months (for a specific customer): SELECT DATEPART(year, orderDate) AS [year], DATEPART(month, orderDate) AS [month], DATEPART(wk, orderDate) AS [week], COUNT(1) AS orderCount FROM dbo.Orders (NOLOCK) WHERE customerNumber = @custnum AND...
If I am using .Net and SQL Server 2008, what is the best way for me to store a color in the database, should I use ToString or convert it to an integer, or something else? Edit: All I want from the colour is to be able to retrieve it and draw something on the screen in the specified colour. I don't need to be able to query against it. ...
Hi all. I have a WCF service that needs to notify it's clients when changes occur to the database (sql server 2005). This is relatively easy accomplished, as long as I find a way to notify my service of any changes. I can probably create a database trigger on a table and have that trigger start a small service client that notifies my se...
I am getting this Error on Insert statement to AS400 database, using Java with JDBC. ...
I have a table that contains intervals: CREATE TABLE tbl ( user_id: INTEGER, start: TIMESTAMP, end: TIMESTAMP, billable: BOOLEAN ); I want to create a view that looks like this: user_id | billable_time | unbillable_time I can get one of these two using a query like this: SELECT user_id, sum(end - start) AS billable...