tsql

Running fn_trace_gettable

Can I read a tracefile on 2000 SQL server using fn_trace_gettable without having to stop the trace? ...

Stored procedure and .NET: SELECT INTO on temporary table: suppress result

I'm accessing a stored procedure from ADO.NET. The stored procedure should eventually returns a single result set. To compute this result, a temporary table is filled with a SELECT INTO statement. The problem is that the result of the SELECT INTO statement is also returned as a result set to .NET. Can this be suppressed? Or should I...

Trying to find the top 3 properties of a POCO instance.

Hi folks, I have a simple POCO class that contains the student's scores. eg. Math - 83% Engrish - 82% Chemistry - 81% Drama - 100% etc.. Is there a way (using linq?) that i could figure out the top 3 properties ordered by score? I'm assuming the final object will be an IList of an anonymous type, which will have two fields. Name (t...

TSQL: How to convert local time to UTC? (SQLServer2008)

We are dealing with an application that needs to handle global time data from different time zones and daylight savings time settings. The idea is to store everything in UTC format internally and only convert back and forth for the localized user interfaces. Does the SQL Server offer any mechanisms for dealing with the translations given...

Slow SQL Updates within a Cursor

I've added a new column, packageNo, to my table: create table Packages( id varchar(20) primary key, -- ok, I know :) orderNo uniqueIdentifier not null, orderlineNo int not null, packageNo int not null default(0) ) Now I want to generate the packageNo with the following rules: reset it for each order ascendantfor order, or...

using Switch like logic in T-SQL

This looks like a noob T-SQL question but I want do switch like logic in a stored procedure and I was thinking that using a CASE would be the way to do this with something like SELECT CASE @Type WHEN 1 THEN INSERT INTO dbo.Credit ( CompanyName, PhoneNumber, City, State ) VALUES ( @CompanyName, @PhoneNu...

How do i find the total number of records created on a given day using T-SQL?

I need to find out the total number of records that were created on a given day. e.g. ID CreatedDate 1 17/07/2009 2 12/07/2009 3 17/07/2009 4 05/07/2009 5 12/07/2009 6 17/07/2009 Output: 3 Records were created on 17/07/2009 2 Records were created on 12/07/2009 1 Record was created on 05/07/2009 EDIT Upon testing the second su...

Sql Server: Implementing a multi-table search

I'm trying to figure out the best way to search a number of different tables for a simple term. I have two solutions in mind 1) Create a "Search table" with the following structure and create triggers on the tables I would like to search to enter the information into the search table ID || String to check against || ...

What represents a double in sql server?

I have a couple of properties in c# which are doubles and I want to store these in a table in sql server, but noticed there is no double type, so what is best to use, decimal or float? This will store latitude and longitude values, so I need the most accurate precision. Thanks for the responses so far. ...

How to retrieve rows multiple times in SQL Server?

These are my rows that I initially retrieved: 112 Cem Ceminay 210 Ali Salih 132 Gül Sen Now I want to clone every row to be duplicated as 3 rows. So the new results: 112 Cem Ceminay 112 Cem Ceminay 112 Cem Ceminay 210 Ali Salih 210 Ali Salih 210 Ali Salih 132 Gül Sen 132 Gül Sen 132 Gül ...

Tips for deduping a list based on priority column

I have a set of rows that contain duplicate entries because the data originates from multiples sources. I also have a separate reference table that indicates the priority of those data sources. Does anyone have good tips for the most effective t-SQL to deduplicate this list? Basically I have: SELECT a.*, b.priority FROM tableA as a ...

Linq to SQL with Group by

I am trying to convert this T-SQL to a Linq To SQL but can't work out the group by aggregate functions. Any help welcome. select c.ClientID, GivenName, Surname, max(a.Address), max(t.Value) from Client c left join ClientAddress a on c.ClientID = a.ClientID left join ClientContact t on c.ClientID = t.ClientID group by c.ClientID, GivenNa...

Making a SQL Query More Efficient

I have a query along the lines of select b.* from (select key, max(val) as val from (somequery) group by key) as a inner join (somequery) as b on a.key = b.key and a.val = b.val order by key And I was wondering if there is an obvious way (that I am missing) to simplify it (given that somequery might be rather long). Any thoughts woul...

Getting a query to index seek (rather than scan)

Running the following query (SQL Server 2000) the execution plan shows that it used an index seek and Profiler shows it's doing 71 reads with a duration of 0. select top 1 id from table where name = '0010000546163' order by id desc Contrast that with the following with uses an index scan with 8500 reads and a duration of about a secon...

Find the row associated with a Min/Max, without inner loop

Hi, I have a question related to T-SQL and SQL Server. Let's say I have a table Orders with 2 columns: ProductId int CustomerId int Date datetime I want the date of the first order for every product, so I perform this type of query: SELECT ProductId, MIN(Date) AS FirstOrder FROM Orders GROUP BY ProductId I have an index on Produc...

How do you group by one column and retrieve a row with the minimum value of another column in T/SQL?

So I know this is a pretty dumb question, however (as the rather lengthily title says) I would like to know how do the following: I have a table like this: ID Foo Bar Blagh ---------------- 1 10 20 30 2 10 5 1 3 20 50 40 4 20 75 12 I want to group by Foo, then pull out rows with minimum Bar, i.e. I want the following: I...

Is there a better way to write this SQL query?

I'm looking to return the row with biggest create_dt. This works fine, however I would like to know if there is a more proper way of doing this? select * from table1 where job_no='101047' and create_dt in (select max(create_dt) from table1 where job_no='101047') ...

How to sort the result set of an UPDATE statement that uses the OUTPUT clause?

The title says it all. I'd like to sort the rows returned by an UPDATE statement, but there's no direct way to do this according to the msdn page for the OUTPUT Clause, which says: SQL Server does not guarantee the order in which rows are processed and returned by DML statements using the OUTPUT clause. Since I can't just tack "or...

Why is the DataAdapter not getting error thrown by trigger

I have a trigger on a view that handles update operations. When I use the MS Management Studio and do an insert I get this error: Msg 50000, Level 16, State 10, Procedure Trigger_TempTableAttr_Lot_UpdateDelete, Line 166 Violation of UNIQUE KEY constraint 'UK_Lot_LotAccountCode'. Cannot insert duplicate key in object 'app.Lot'. Msg 3616,...

How to insert JPEG into a SQL Server 2000 database field of image type using Transact SQL

Hi, I'm trying to figure out how to insert a .JPG file into a SQL Server 2000 database field of type image using Transact SQL. Thanks. ...