sql

Query xml data from SQL2000 (xml saved directly in a cell, multiple rows)

Hi I get problem retrieve XML data from SQL2000 server. The table structure is as following: ID Name XML 1 Name1 <Root><DATA1>1-Data1</DATA2><DATA1>1-Data2</DATA2></Root> 2 Name2 <Root><DATA1>2-Data1</DATA2><DATA1>2-Data2</DATA2></Root> 3 Name3 <Root><DATA1>3-Data1</DATA2><DATA1>3-Data2</DATA2></Root> How can I get th...

T-SQL - GROUP BY with LIKE - is this possible?

Is there a way to include a LIKE expression in a GROUP BY query? For example: SELECT Count(*) from tblWhatever GROUP BY column_x [LIKE %Fall-2009%] column_x: -------- BIOL-Fall_2009 HIST Fall_2009 BIOL Spring_2009 Result: ------ Fall_2009 2 Spring_2009 1 ...

Can Sql Server 2008 Stored Procedures (or Triggers) manually parallel or background some logic?

Hi folks, If i have a stored procedure or a trigger in Sql Server 2008, can it do some sql calculations 'in another non-blocking thread'? ie. something in the background also, can two sql code blocks be ran in parallel? or two stored procs be ran in parallel? for example. Imagine we are given the job calculating the scores for each St...

SQL Table vs Table Addition

Is it possible to craft a query that adds values within two tables: For example, say you have two tables id value -- ----- a 1 c 2 d 3 f 4 g 5 and id value -- ----- a 1 b 2 c 3 d 4 e 5 Then when you 'add' the two tables you would get the result where t...

How do you write a conditional in a MySQL select statement?

I'm using MySQL, and I want to do a sort of ternary statement in my SQL like: SELECT USER_ID, ((USER_ID = 1) ? 1 : 0) AS FIRST_USER FROM USER The results would be similar to: USER_ID | FIRST_USER 1 | 1 2 | 0 3 | 0 etc. How does one accomplish this? ...

SQL - SQLite count consecutive numbers

I have a table with the following data: id | numbers | date ---------------------------------- 1 | -1-4-6- | 2009-10-26 15:30:20 2 | -1-4-7- | 2009-10-26 16:45:10 3 | -4-5-8- | 2009-10-27 11:21:34 4 | -2-6-7- | 2009-10-27 13:12:56 5 | -1-3-4- | 2009-10-28 14:22:14 6 | -1-2-4- | 2009-10-29 20:28:16 . . ....... . .....................

what is the query to get "related tags" like in stack overflow

i have 3 tables: links (id, linkName) tags (id, tagName) tagsBridge (tagID, linkID) i am trying to support showing related tags like in SOF. so if you click on tags "XYZ", right now i am showing all the links with tag "XYZ" but i also want to show the distinct list of all other tags that people have tagged those items that also h...

Does facebook fql contain the sql like operator?

Hi all, I am building a small facebook app. Trying to search for values in an auto compelte manner. Is it possible to use sql's like operator in fql? ...

sql delete rows with 1 column duplicated

I have a microsoft sql 2005 db table where the entire row is not duplicate, but a column is duplicated. 1 aaa 1 bbb 1 ccc 2 abc 2 def How can i delete all the rows but 1 that have the first column duplicated? For clarification I need to get rid of the second, third and fifth rows. ...

Hibernate detached queries as a part of the criteria query

Hi Hibernate, java experts can you please help me write detached queries as a part of the criteria query for the following SQL statement. select A.* FROM AETABLE A where not exists ( select entryid FROM AETABLE B where B.classpk = A.classpk and B.userid = A.userid and B.modifiedDate > A.modifiedDate ) and userid = 10...

Restore Partioned database into multiple filegroups

does anyone have any query to restore partioned db that having multiple file groups,In the restore option in the SSME i need to edit manually all the path of the filegroups restore as option it little bit tedious as it having more than 150 filegroups eg:USE master GO -- First determine the number and names of the files in the backup. RE...

Alter column drops index

I have a table CREATE TABLE table1( [classe] [char](30) NOT NULL, [code] [char](30) NOT NULL, [description] [varchar](255) NULL, [codelangue] [char](2) NULL ) ON [PRIMARY] with the index CREATE NONCLUSTERED INDEX [table1_id1] ON [dbo].[table1] ( [codelangue] ASC, [classe] ASC, [code] ASC ) INCLUDE ( [description]) WITH (PAD_...

Generating sql insert into for Oracle

The only thing I don't have an automated tool for when working with Oracle is a program that can create INSERT INTO scripts. I don't desperately need it so I'm not going to spend money on it. I'm just wondering if there is anything out there that can be used to generate INSERT INTO scripts given an existing database without spending lo...

Equivalent to VB AndAlso in SQL?

Is there an equivalent to VB's AndAlso/OrElse and C#'s &&/|| in SQL (SQL Server 2005). I am running a select query similar to the following: SELECT a,b,c,d FROM table1 WHERE (@a IS NULL OR a = @a) AND (@b IS NULL OR b = @b) AND (@c IS NULL OR c = @c) AND (@d IS NULL OR d = @d) For example, if the "@a" parameter passed in as NULL ther...

Deleting rows from a contended table

I have a DB table in which each row has a randomly generated primary key, a message and a user. Each user has about 10-100 messages but there are 10k-50k users. I write the messages daily for each user in one go. I want to throw away the old messages for each user before writing the new ones to keep the table as small as possible. Righ...

Does the SQL Server 2005 database size property include the full-text index?

The title pretty much says it all, but in SQL Server 2005 Management Studio, if I look at the database properties and pick up the size value, will that include the full-text index data? Does it include the log file as well? Rough values are fine, but some of our ft indexes can be large, so I want to be sure of this. ...

Sql Filestream Update problems

Hello, I have implemented the filestream feature of SQL Server 2008 in a VB.Net application. I can insert files, and then retrieve/view them just fine. However, I have huge problems trying to update a file. Eg. The user selects a file from the grid which I execute via process.start. If that file is a .txt file, the user may choose to e...

mysql compare enum

Here is a table i have CREATE TABLE `CUSTOMERSTATUSTYPES` ( `CustomerStatusId` int(1) unsigned NOT NULL auto_increment, `CustomerStatusName` enum('ACTIVE','SUSPEND','TERMINATE','CANCEL') default NULL, PRIMARY KEY (`CustomerStatusId`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1; When i write GROUP BY ... WHERE cst.C...

Stopping a delete on MySQL via Triggers

I'm trying to intercept any DELETE commands against a particular table. MySQL supports triggers but it doesn't seem to support a way to raise an error yet like SQL Server and other databases. Can I do this with just an empty Trigger definition? Something like: create trigger trListsDelete on lists instead of delete as begin /* Do n...

PostgreSQL analog of SQL Server index

Trying to recreate my SQL Server database on PostgreSQL. Everything is ok except I can't find how to recreate this index: USE [mytablename] GO CREATE NONCLUSTERED INDEX [myindex] ON [dbo].[mytablename] ([col1],[col2]) INCLUDE ([col3],[col4]) GO Will be very grateful for help. Alexey Update: http://img38.imageshack.us/...