sql

How do you backup CRM3.0 when the 'ftcat_documentindex' is offline?

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...

SQL - Multiple rows into single column

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) =...

Update randomly selected row in SQLite

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 ) ...

Change a database column type while ignoring conversion errors using SQL

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...

Collecting SQL Data with feedback to asp.net web app

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...

Database Records - Effective Start and End Dates and Foreign Keys

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 ...

recommend a good temp table tutorial in SQL Server

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 ...

How To Create a SQL Index to Import ORDER BY performance

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 ...

SQL: selective subqueries

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 ...

Set based insert into two tables with 1 to 0-1 relation.

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...

New records with common data

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! :) ...

SQL query for point-in-polygon using Postgres

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 ...

How do I combine 3 SQL queries into 1?

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...

SQL to check if database is empty (no tables)

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! ...

Bypassing DELETE_ORPHANS in a transaction when moving objects from one parent to another, hibernate.

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...

JOIN and LEFT JOIN equivalent in LINQ

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...

Accessing NexusDB from Java

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. ...

Generate resource free/busy query using SQL Server

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 to INSERT using an inverse JOIN on multiple keys?

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...

SQL Server slow down after duplicating database

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...