sql

Why FULLTEXT index is not being populated - MYSQL

I am trying to add a fulltext index against a table, but it does not seem to apply. ADD FULLTEXT `genre` ( `genre` ) The query executes, but the cardinality remains at just 1 (even though i ahve a few thousand records) I tried ANALYZE, REPAIR and neither seemed to update the index. If I create a regular index the cardinality is upda...

Aggregrate all a single column in a grouped resultset in one query: is it possible?

At the moment I have the following MySQL query: SELECT COUNT(*) AS `count`, `v`.`value` FROM `client_entity_int` AS `v` INNER JOIN `client_entity` AS `e` ON e.id = v.entity_id WHERE (v.attribute_id = '1') AND (e.deleted = 0) GROUP BY `v`.`value` Which returns the following: +-------+-------+ | count | value | +-------+...

Read XML from Sproc and Write to File

I need to read a large xml result (using For XML) from a stored proc and write it to a file in a .Net app. My first shot is to read the xml from the proc using XmlReader. I then need to write it to a file. Is this best way to handle this scenario, or is there a "better" method? ...

How do I connect to my 64-bit SQL Server with ODBC?

I recently installed SQL Server 2008 Express on my Windows 7 Ultimate x64 home machine. I also have IIS 7.5 with PHP 5.3, and I was trying to connect to SQL via ADODB, but kept getting this error: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application After doing a small...

Execution cannot be found (rsExecutionNotFound)

Hi, I keep getting the errror "Execution 'APPLICATION ID' cannot be found (rsExecutionNotFound)" using SQL Server Reporting. It keeps happening for the same user, with the same application ID. While it happens for some other users it happens a lot for this one user. I have tried increasing the session timeout as menetioned here: http://...

Help needed in AdventureWorks in a sql query.

I was just playing with adventureworks database in sqlserver. I got stuck in a query. I wanted to Select all titles from HumanResources.Employee which are either 'Male' or 'Female' but not both. i.e if title Accountant is Male and Female both I want to leave that title. I need only those titles where Gender is either Male or Female. I h...

Additional SQL Server sample databases to practice with

I'm helping a friend learn SQL, and need more databases to help him get more practice. We have of course AdventureWorks, Northwind, and Pubs. Does anybody know of any other SQL databases samples that might be available to download? I know some sites have some databases where you can practice queries on the site, but I was looking for s...

In Rails, how can I return a set of records based on a count of items in a relation OR criteria about the relation?

I'm writing a Rails app in which I have two models: a Machine model and a MachineUpdate model. The Machine model has many MachineUpdates. The MachineUpdate has a date/time field. I'm trying to retrieve all Machine records that have the following criteria: The Machine model has not had a MachineUpdate within the last 2 weeks, OR The Mac...

Composite foreign keys

This comes up a lot for me. In SQL Server 2008, I have 3 tables. 2 with unique keys and 1 which is a mapping between them. Example: People Events Schedule ------ ------ -------- PersonId EventId ScheduleId Name Place PersonId EventId Rsvp ScheduleId isn't needed if I make a com...

SQL query to return only 1 record per group ID

I'm looking for a way to handle the following scenario. I have a database table that I need to return only one record for each "group id" that is contained within the table, furthermore the record that is selected within each group should be the oldest person in the household. ID Group ID Name Age 1 134 John B...

What is mysql indexes?

I don't get what is that? It's id which auto increase number? ...

Defining indexes: Which Columns, and Performance Impact?

I know how to use indexes(clustured and non clustured) But when should i use non clustured indexes in my table. What scenarios should be there, so as to make my column non clustured index. I have gone throught msdn guidelines but still little bit confusion. Should i make only unique columns as NC or should there any other columns also a...

users and permissions

I want to give some users certain permissions. Would this be the best way? I don't want to make groups like: admin, moderator, normal and so on. I want to be able to give one user maybe access to make news post and another one create poll, and maybe another delete polls. I'm thinking tables like this: CREATE TABLE `users` ( `id` medium...

What is wrong with this SQL query?

I run this query "insert into students (StudentName) values ('reza');insert into Records (RecordValue,StudentID)" + " values (20,@@IDENTITY)"; in C# and get following exception : Characters found after end of SQL statement ...

changing the order that tables are linked in sql

Question: In the Crystal Reports report creation wizard there is the option of changing the order which more than two two tables are linked. The link between Table A and Table B could be made before Table C is linked, or vice versa. It says "The order may affect the resulting data set." How could that be true? I could see it affecting pe...

Need a .NET database versioning script runner

Hi Guys, I'm looking at versioning databases and came across the usual articles regarding how to do this (coding horror, ode to code, etc). This all make perfect sense to me, however I'm trying to find a script runner that will run the sql scripts for me. All these articles mention having something to run them automaticaly, but none of ...

automate check index cardinality in MySQL

I have ran into some problems with MySQL that causes our index cardinality to show up as "NULL". I have noticed that for an index with multiple columns, only the Last column has an actual cardinality value. I have written this query but I want to confirm that there is nothing that I am missing: SELECT s.TABLE_SCHEMA, s.TABLE_NAM...

using sql server 2005 synonyms for table causes dynamic query generation errors

Hi, I have a situation where by i have a catalog on Server A, and a same named Catalog on Server B. I'm using synonyms on Server B so that some tables are actually just going through to server A. Such that, calling select * from ServerB.Table, is actually returning Data from ServerA.Table transparently. This seems to work fine when ...

SQL Server update command question

i'm using sql server 2000 and i have some table need to update. one of problem is how to update some portion of column? for example: here is two table one is OrderDate and ShipName and i want to change Date value in OrderDate, such like all 1996 year to 1999 year and all 'tom' in ShipName to 'ken' value OrderDate ShipName ...

Critique this SQL stored procedure.

Hey guys, below is a massive stored procedure that a contract developer wrote for me, I feel like I am picking on the developer but it is just terrible. What are the main issues you can see with it? CREATE PROCEDURE [dbo].[usp_SHS_XXXX] ( @request_identifier varchar(255), @category_guids varchar(4000), @url varchar(500)...