sql

Problem With DISTINCT!

Here is my query: SELECT DISTINCT `c`.`user_id`, `c`.`created_at`, `c`.`body`, (SELECT COUNT(*) FROM profiles_comments c2 WHERE c2.user_id = c.user_id AND c2.profile_id = 1) AS `comments_count`, `u`.`username`, `u`.`avatar_path` FROM `profiles_comments` AS `c` INNER JOIN `users` AS `u` ON u.id = c.user_id WHERE (c.profile_id = 1) ORD...

How do I insert/delete/update in an ordered tree

I have two tables in which I store a tree with ordered levels like this: Table: TreeData --------------- ID (int) (primary key) Data (string) Level (int) (not null) --------------------------------------------- Table: SubTree --------------- parentID (int) (foreign key #1 to TreeData.ID) childID (int) (foreign key #2to TreeData.ID) o...

Is a commit needed on a select query in DB2 ?

I have a vendor reporting product executing queries to pull report data, no inserts, no updates just reading data. We have double our heap size 3 times and are now at 1024 4k pages, The app will run fine for a week then we will begin to see DB2 SQL error: SQLCODE: -954, SQLSTATE: 57011 indicating the transaction log is not able to acco...

Is it always possible to do something in SQL if you could do it in Linq?

I'm trying to write a SQL Statement that should function like the below Linq query. Mainly, I want to sum two columns (Cash and Check) out of one column (Value * Quantity) based on a certain condition (Type == "Check"). So, if the Type is "Check", then put the value in the Check column, otherwise put it in the Cash column. Linq: from a...

How to get procedure text before ALTER from DDL trigger

I am creating a trigger to track how procedure text has been ALTERed. Inside a database DDL trigger, it's possible to access current procedure Text through /EVENT_INSTANCE/TSQLCommand. Even after investigating EVENTDATA(), it did not contain values for previous definition of procedure before ALTER. Is there a way to retrieve previous ...

SQL Server query - Selecting COUNT(*) with DISTINCT

In SQL Server 2005 I have a table cm_production that lists all the code that's been put into production. The table has a ticket_number, program_type, and program_name and push_number along with some other columns. GOAL: Count all the DISTINCT program names by program type and push number What I have so far is: SELECT DISTINCT COUNT(*...

T-SQL Scripts to copy all table constraints

I have created many tables on my local database and moved them to production database. Now I am working on fine tuning the database and created many constraints on my local database tables such as PK, FK, Default Values, Indexes etc. etc. Now I would like to copy only these constraints to production database. Is there a way to do it...

SQL SERVER Procedure Inconsistent Performance

I am working on a SQL Job which involves 5 procs, a few while loops and a lot of Inserts and Updates. This job processes around 75000 records. Now, the job works fine for 10000/20000 records with speed of around 500/min. After around 20000 records, execution just dies. It loads around 3000 records every 30 mins and stays at same speed...

Getting record differences between 2 nearly identical tables

I have a process that consolidates 40+ identically structured databases down to one consolidated database, the only difference being that the consolidated database adds a project_id field to each table. In order to be as efficient as possible, I'm try to only copy/update a record from the source databases to the consolidated database ...

SQL query in SQL SERVER 2005 - Comparing Dates

I'm having a hard time doing this query. I want to compare dates in my query, dates from my DB are in this format: (MM/DD/YYYY HH:MM:SS AM) I want to compare this date with tomorrow's day, today plus one. My questions are: How do I declare tomorrow's date in sql server? How would you compare these two dates? Thank you!! =D EDIT : DATE...

How to generate a RNGCryptoServiceProvider-like number in TSQL

Does anyone know of a way to generate a RNGCryptoServiceProvider-like random number in T-SQL without having to register any .NET Assemblies? ...

SQL Server - Storing linebreaks in XML data type

Is it possible to store non-alphanumeric characters (more specifically line break characters) in a XML data type? The code below illustrates my problem: declare @a xml declare @b nvarchar(max) set @b = '<Entry Attrib="1'+CHAR(13)+'2" />' print @b set @a=convert(xml,@b,1) set @b=convert(nvarchar, @a,1) print @b The output is: <E...

What is the fastest way to delete massive numbers of records in SQL?

Possible Duplicate: What is the best way to delete a large number of records in t-sql? What is the fastest way to delete massive numbers (billions) of records in SQL? I want to delete all the records that match a simple rule like myFlag = 3. Is it possible to add a WHERE clause to a TRUNCATE? Another solution that could be po...

Questions about Sql Server 2008 FullText Search

I'm using Sql Server 2008 FullText Search for a project. I need to be able to search PDf files, and I had some questions relating to that: How do I enable PDF searching? I've heard of the adobe filter, but couldn't find a clear guide on how to get started. Are the PDF files stored in the DB itself, or in the file system? I was mainly...

Is WHERE ID IN (1, 2, 3, 4, 5, ...) the most efficient?

I know that this topic has been beaten to death, but it seems that many articles on the Internet are often looking for the most elegant way instead of the most efficient way how to solve it. Here is the problem. We are building an application where one of the common database querys will involve manipulation (SELECT’s and UPDATE’s) based ...

Help with Recursive SQL

I have a category table similar to this: uid | ParentLevel | ParentID | Name ------------------------------------ 1 | 0 | 0 | foo 2 | 1 | 1 | blat 3 | 1 | 1 | baz 4 | 2 | 3 | blah 5 | 0 | 0 | bar I am trying to get ...

SQL query challenge

So here's yet another 'write a query to X' challenge. I'm monitoring a number of networked vending machines. Each machine has a number of parts, e.g. bank note acceptor, coin system, printer and so on. Problems with machine parts are logged in table, let's call it 'faults', which looks something like this (irrelevant fields omitted): ...

TSQL: string operations to match a pattern or wildcard for one character

Consider the need to query for a certain pattern of data within a column. The example I'll use are customers with Canadian postal codes. ID Postal -- ------- 442 90210 631 T0R 4C2 447 YO31 1EB 145 F9S8S6 73 K9J 3K3 Pretend you don't have an easy out (like a state/prov or country field), or that...

Pivoting in T-SQL based on date offset

There is a table called Orders has three columns: OrderID UserID OrderDate I need to know, for X months in the past, A users placed 1 order, B users placed 2 orders, C users placed 3 orders, etc. So the output is a table w/ a MinDate field and then columns for each # of orders from 1 to N, e.g.: declare @t table ( MinDate dateti...

Call a Sproc on another SQL Server without being linked via TSQL

I want to call a sproc on server B from server A in TSQL without linking the servers. Is it possible to use something like a connection string to execute this sproc? The return will be a single nvarchar value. Regards. ...