tsql

Modifying a table a cursor is declared on

So I've created a cursor on a temp table in a stored proc. However, in the @@fetch_status while loop, this temporary table is changed. I would expect that the cursor will see the new entries in the table it was declared on and will continue looping on them. Is this actually the case or does SQL take a snapshot of the table when you decla...

How to use CONTAINS with inline queries in SQL Server 2008?

I have this sql query where I'm trying to use CONTAINS to search the title field. But I get this error. "Cannot use a CONTAINS or FREETEXT predicate on column 'Title' because it is not full-text indexed." The Titles table has been indexed and a CONTAINS works fine with a simple search. Does anyone know what I'm doing wrong? Are CONTA...

Join won't do it, and sub query sucks, then what?

Hello all, first of all, sorry for the non descriptive title, I'm just too rushed so I couldn't come up with a better one. Second: I have a portion of my database the looks like the following diagram: I have contributors on the system, each write to many sources, and a source can have many working contributors. Users can subscribe...

using the TSqlParser

I'm attempting to parse SQL using the TSql100Parser provided by microsoft. Right now I'm having a little trouble using it the way it seems to be intended to be used. Also, the lack of documentation doesn't help. (example: http://msdn.microsoft.com/en-us/library/microsoft.data.schema.scriptdom.sql.tsql100parser.aspx ) When I run a simple...

Adding a column to all user tables in t-sql

I need to add a delete flag column to all 40 user tables in a database. I could write a script to loop through sys.tables, but I thought I'd check and see if anyone has either a better solution, or pre-created sql for this scenario. ...

How to create a empty temporary table from another tables schema?

Is there an idiomatic way of doing this? ...

Getting only Month and Year from SQL DATE

I need to access only Month.Year from Date field in SQL Server. Thanks in advance. ...

Duplicates without using While or Cursor in T-SQL

ID Name 1 A 1 B 1 C 2 X 2 Y 3 P 3 Q 3 R These are the columns in a table. I want to get output like ID Company 1 A,B,C 2 X, Y 3 P,Q,R Restriction is that I cannot use WHILE or CURSOR. Please write a query for the same. ...

Declaring a default constraint when creating a table

Hi, I am creating a new table in Microsoft SQL server 2000 by writing the code instead of using the GUI, I am trying to learn how to do it "the manual way". This is the code I am actually using, and it works fine: CREATE TABLE "attachments" ( "attachment_id" INT NOT NULL, "load_date" SMALLDATETIME NOT NULL, "user" VARCHAR(25) NOT NU...

TSQL: Remove duplicates based on max(date)

I am searching for a query to select the maximum date (a datetime column) and keep its id and row_id. The desire is to DELETE the rows in the source table. Source Data id date row_id(unique) 1 11/11/2009 1 1 12/11/2009 2 1 13/11/2009 3 2 1/11/2009 4 Expected Survivors 1 13/11/2009 ...

SQL Server Query Execution Plan Rebuild

I have this query that gets executed though Linq to Entities. First time the query runs it generates the execution plan which takes just under 2 minutes. After the plan is cached the query takes 1 or 2 seconds. The problem I have is that the plan keeps getting rebuild every few hours and I am not sure why that would be? This is the linq...

IF statement within SQL Query

I have the following query, that makes up a View. I'm having trouble with the IF statement within it. Basically, if salesHeader.[Ship-to Name] isn't null, i need to return that AS DeliveryName, if it is null, return something else. There are several fields like this (i've only included one for now) Is there a way of doing this? SELE...

Exclude row if one of 2 flattened columns didn't return

Hello, I am joining against a view, in a stored procedure to try and return a record. In one table I have something like this: Measurement | MeasurementType | Date Performed I need to plot TireHeight and TireWidth. So I am flattening that table into one row. I only want to return a row though if both TireHeight and TireWidth were ...

What's too much SQL Server data and how do I analyze my execution plan

I have a relation between two tables with 600K rows and my first question is, is that a lot of data? It doesn't seem like a lot (in terms of rows, not bytes) I can write a query like this SELECT EntityID, COUNT(*) FROM QueryMembership GROUP BY EntityID And it completes in now time at all, but when I do this. SELECT EntityID, COUNT(*...

SELECT DISTINCT on one column, return multiple other columns (SQL Server)

I'm trying to write a query that returns the most recent GPS positions from a GPSReport table for each unique device. There are 50 devices in the table, so I only want 50 rows returned. Here is what I have so far (not working) SELECT TOP(SELECT COUNT(DISTINCT device_serial) FROM GPSReport) * FROM GPSReport AS G1 RIGHT JOIN (SELECT DIST...

SQL "Group By" VarChar Field With Max Date or All results of the same date

Hello, Lets say I have this table -------------------------------------------------------------- | ID | DATE | GROUPNAME | RESULT | INFO1 | INFO2 | -------------------------------------------------------------- | 1 | 01/06 | Group1 | 12345 | Abc | xxxx | | 2 | 01/04 | Group2 | 54321 | AAA ...

Automatically match columns in INSERT INTO ... SELECT ... FROM ...

Hi! SQL Server question. When doing INSERT INTO T1 SELECT (C1, C2) FROM T2 I don't want to specify column names of T1 because they are the same as in T2 Is it possible to do so? Currently I'm getting error Msg 213, Level 16, State 1, Line 1 Column name or number of supplied values does not match table definition. ...

how to try execute repeatly if command.ExecuteNonQuery() fails

how to try execute repeatly if command.ExecuteNonQuery() fails? ...

How can i set an integer varable using stored procedure

Hi there. exec SP_HastaIcmal_AktifKaliciHastalar 25 it returns 86. DECLARE @iAktifKaliciHastalar int SET @iAktifKaliciHastalar = exec SP_HastaIcmal_AktifKaliciHastalar 25 that code return error. Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'exec'. ...

In TSQL can I create a UDF which has as input a table's row?

I'm looking to create what I would think is simple. I want a user defined function which takes a row from a table as imput and returns a scalar value. Something like select mt.Id, MyUDF(mt) from M MyTable mt where mt.Price > 0 I understand that I can pass in the Id to the UDF, and then lookup the values from within the ...