My organisation uses Microsoft CRM 3.0, and I am attempting to backup the database. The following error is preventing me from doing so, does anyone know how to resolve this issue? Error: System.Data.SqlClient.SqlError: The backup of full-text catalog 'ftcat_documentindex' is not permitted because it is not online. Check errorlog file f...
I would like to take the data output from the query below and join all of the email addresses together separated by a semicolon grouped by the employee name. SELECT DISTINCT p.email , e.name FROM PERSON p INNER JOIN EMPLOYEE e ON p.agentofrecord_id = e.employee_id WHERE dbo.GetPersonMember(p.person_id) =...
I have a table and in it a certain value - by default it is set to -1, but I want to change it to 0 for a random row. What is the right query for this operation? Here is what I tried: UPDATE statuses SET status = 0 WHERE word_id = ( SELECT word_id FROM statuses WHERE status = -1 ORDER BY RANDOM() LIMIT 1 ) ...
This is similar to a previous question MSSQL: Change type of a column with numbers from varchar to int, except in this case not all of the original data can be successfully converted from varchar to int. In these cases, I want the output to just be NULL. For example, the incoming set of values in a varchar column will look like: {'123...
I have the following situation: I have a stored procedure that takes data from a bunch of tables and creates a record into a single table. After that the end user can get some graphics based on the data from the row in the resulting table. The problem is that the collecting of data from multiple tables into the resulting one can have a v...
Does anyone have any experience having worked with datasets where records become valid and invalid based on an effective start and end date? The issue is when records in these tables have foreign keys to other tables that also have effective start and end dates Seems you have to end up creating new records for just about every table to ...
Hello everyone, I searched but cannot find a good temp table usage tutorial for a newbie in SQL Server 2005/2008. I want to learn pros and cons of temp table compared with normal table, its life time and how temp table is shared (in the same session, cross sessions)? thanks in advance, George ...
Hi, I have some SQL similar to the following, which joins four tables and then orders the results by the "status" column of the first: SELECT * FROM a, b, c, d WHERE b.aid=a.id AND c.id=a.cid AND a.did=d.id AND a.did='XXX' ORDER BY a.status It works. However, it's slow. I've worked out this is because of the ORDER BY clause and ...
Hi, I'm having an SQL query (MSSQLSERVER) where I add columns to the resultset using subselects: SELECT P.name, (select count(*) from cars C where C.type = 'sports') AS sportscars, (select count(*) from cars C where C.type = 'family') AS familycars, (select count(*) from cars C where C.type = 'business') AS businesscars FROM people P ...
I have two tables, the first has a primary key that is an identity, the second has a primary key that is not, but that key has a foreign key constraint back to the first table's primary key. If I am inserting one record at a time I can use the Scope_Identity to get the value for the pk just inserted in table 1 that I want to insert into...
I have a table like this (MySQL 5.0.x, MyISAM): user{id, login, ip, banned} (Banned: 0 false, 1 true) I would like to find all users not banned (banned=0) if at least 5 other users with the same ip have already been banned (banned=1). Thanks for your help! :) ...
I have the following simple table: CREATE TABLE tbl_test ( id serial NOT NULL, poly polygon NOT NULL ) WITH (OIDS=FALSE); I then try to insert a row with a polygon: insert into tbl_test values(1, PolyFromText('POLYGON((0 0, 10 10, 10 0, 0 0))')) And run into this error: column "poly" is of type polygon but expression is of ...
This is written to count how many people have visited within the last day. I want to also include how many have visited in the last week and year and have it output altogether without doing 3 separate queries. SELECT COUNT(updated_at) AS 'TODAY' FROM parts_development.page_views p WHERE updated_at >= DATE_SUB(NOW(),INTERVAL 1 day) GR...
Hi guys, I need to check if a database is totally empty (no tables) using an SQL query. How can this be done? Thanks for the help! ...
I am using a combination of Spring 2.5.6 and Hibernate Annotations. I have three objects(tables or w/e) under consideration: Customer, Address, Order. Customer has the Cascade DELETE_ORPHANS property set for addresses. What i am doing is a customer merge, i'm moving all addresses and orders from one customer to another, then setting a d...
I am working with the following SQL query: SELECT a.AppointmentId, a.Status, a.Type, a.Title, b.Days, d.Description, e.FormId FROM Appointment a (nolock) LEFT JOIN AppointmentFormula b (nolock) ON a.AppointmentId = b.AppointmentId and b.RowStatus = 1 JOIN Type d (nolock) ON a.Type = d.TypeId LEFT JOIN AppointmentForm e (nolock) ON e.Ap...
I am trying to get info from an embedded db called NexusDB using java. Alternative 1: I've read in NexusDB website that there is an ODBC driver so I might use it with unixODBC. Then I need to do a JDBC-ODBC Bridge as stated here. Alternative 2: Get some sort of application to migrate NexusDB db to another db. Would like to know one. ...
In SQL Server 2005 I have a table with the following columns: Table Reservations: ID arrival datetime departure datetime reservation_object (FK to objects table) Now I need to generate a report that shows for a specified period the status of an object. This report will have to look like this: 6/1/09 6/2/09 6/3/09 6/4/0...
How do I do an inverse join with more than one key column? In this baby-toy SqlServer example, I have the following CREATE TABLE [dbo].[CarList]( [myID] [int] IDENTITY(1,1) NOT NULL, [CarColour] [varchar](32) NOT NULL, [CarName] [varchar](128) NOT NULL, [CarCompany] [varchar](32) NOT NULL, CONSTRAINT [PK_CarList] PRIMARY KEY CLUS...
I recently moved a bunch of tables from an existing database into a new database due to the database getting rather large. After doing so I noticed a dramatic decrease in performance of my queries when running against the new database. The process I took to recreate the new database is this: Generate Table CREATE scripts using sql ser...