tsql

Instead of trigger in SQL Server loses SCOPE_IDENTITY?

I have a table where I created an INSTEAD OF trigger to enforce some business rules. The issue is that when I insert data into this table, SCOPE_IDENTITY() returns a NULL value, rather than the actual inserted identity. Insert + Scope code INSERT INTO [dbo].[Payment]([DateFrom], [DateTo], [CustomerId], [AdminId]) VALUES ('2009-01-20',...

SQL Server query grouped by max value in column

**Update: using the Rank() over partition syntax available in MS SQL Server 2005 does indeed point me in the right direction, it (or maybe I should write "I") is unable to give me the results I need without resorting to enumerating rows in code. For example, if we select TOP (1) of rank, I get only one value, ie., slot 1. If I use MA...

Equivalent of Oracle's RowID in SQL Server

What's the equivalent of Oracle's RowID in SQL Server? ...

T-SQL: How to log erroneous entries during import

Hi, I do an import and convert columns to other datatypes. During the import I check, if I can convert the values stored in the columns into the target datatypes. If not I put a default value, e.g. I convert a string to int and when I cannot convert the string I insert -1. At this moment it would be great if I could log the erroneous e...

Transact-SQL: insert into xyz ( select * from abc )

Hi, i want to realize a construct in MS SQL that would look like this in Oracles PL/SQL: declare asdf number; begin for r in (select * from xyz) loop insert into abc (column1, column2, column3) values (r.asdf, r.vcxvc, r.dffgdfg) returning id into asdf; update xyz set column10 = asdf where ID = r.ID; end loop; end; Any i...

Is it possible to calculate MD5 hash directly in T-SQL language?

Hi all, I need to hash (MD5) all the password in our Sql Server 2000 database. I can easily generate a C#/VB.NET program to convert (hash) all the passwords, but I was wondering (more for my education than for a real compelling need) if it was possible to calculate MD5 hash directly in T-SQL. Thanks to anyone who will answer. ...

SQL Select Statement For Calculating A Running Average Column

I am trying to have a running average column in the SELECT statement based on a column from the n previous rows in the same SELECT statement. The average I need is based on the n previous rows in the resultset. Let me explain Id Number Average 1 1 NULL 2 3 NULL 3 2 ...

DB2 Equivalent to SQL's GO?

I have written a DB2 query to do the following: Create a temp table Select from a monster query / insert into the temp table Select from the temp table / delete from old table Select from the temp table / insert into a different table In MSSQL, I am allowed to run the commands one after another as one long query. Failing that, I can ...

Reassociate .SQL files with VS T-SQL Editor

I seem to have lost the association from .sql files to the default VS T-SQL editor. I'm using Visual Studio 2008. When i open a .sql file it opens using a text editor with no syntax highlighting. How do I reassociate all .sql files with the default T-SQL editor while inside Visual Studio? ...

Can SSMS display empty space as a character?

Right now I'm working on a dataset where I would like to return records where a certain field is NOT NULL. The dataset is result of a table join. My resulting set is still returning records where the field I told not to return NULL look empty. I'd like to see what the true value in these records are since they don't seem to be true NULL...

How do I use T-SQL's Exists keyword?

I have a query I want to run as a subquery that will return a set of FK's. With them I want to return only rows that has a matching key. Subquery: SELECT ID FROM tblTenantTransCode WHERE tblTenantTransCode.CheckbookCode = (SELECT ID FROM tblCheckbookCode WHERE Description = 'Rent Income') That will return all the transac...

saving the FOR XML AUTO results to variable in SQL

select @[email protected]('*') for xml raw,type Above statement will generate following alert: Msg 6819, Level 16, State 3, Line 2 The FOR XML clause is not allowed in a ASSIGNMENT statement. ...

Loop through without Cursor in SQL Server 2005

Hi there I have a table OrganisationStructure like this: OrganisationID INT ParentOrganisationID INT OrganisationName VARCHAR(64) 1 | 0 | Company 2 | 1 | IT DIVISION 3 | 2 | IT SYSTEM BUSINESS UNIT 4 | 1 | MARKETING DIVISION 5 | 4 | DONATION BUSINESS UNIT I want to have a query that if the app passing let say OrganisatinID = 1 mean...

SELECT DISTINCT not working in .NET application, but works in SQL Mgmt Studio

I have a web app where I am calling a SELECT DISTINCT query on two joined tables. If I try to run the query from the web page, I get the following error: "The text, ntext, or image data type cannot be selected as DISTINCT". When I run it from within SQL Management Studio, the query runs fine - no error. Even more interestingly, there are...

How can a LEFT OUTER JOIN return more records than exist in the left table?

Hi, I have a very basic LEFT OUTER JOIN to return all results from the left table and some additional information from a much bigger table. The left table contains 4935 records yet when I LEFT OUTER JOIN it to an additional table the record count is significatnly larger. As far as I'm aware it is absolute gospel that a LEFT OUTER JOIN ...

Grouping Date Range by Week

I have a date range where a start date is 2 years prior to today. e.g.) '05/29/2007' ~ '05/29/2009'. How can I break above date range so that I would get a list like the following? (start date starts with "05/27/2007", not "05/29/2007" since the start of weekday is Sunday and '05/27/2007' is the first day of week for '05/29/2007' and t...

How to call Stored Procedure in a View?

How would I call a Stored Procedure that returns data in a View? Is this even possible? ...

How do I sort by a column name that's reserved in that context?

I'm working with a legacy database with columns such as "item" and "desc" (for description). Obviously, there's issues when trying to do an ordered select such as: SELECT item, desc FROM blah ORDER BY desc The intent is to do an ascending sort of column "desc", but SQL server gets confused since desc is also a modifier for order by.....

Is there a better way to convert SQL datetime from hh:mm:ss to hhmmss?

I have to write an SQL view that returns the time part of a datetime column as a string in the format hhmmss (apparently SAP BW doesn't understand hh:mm:ss). This code is the SAP recommended way to do this, but I think there must be a better, more elegant way to accomplish this TIME = case len(convert(varchar(2), datepart(hh, timecolum...

How to figure out which column/value the COALESCE operator successfully selected?

I have a table that I wish to find the first non-null value from 3 (and only 3) columns for each ID starting with Col1 then to Col2 then to Col3 Note: Col3 is NEVER NULL ID Col1 Col2 Col3 ------------------------------ 1 A B X 2 NULL C X 3 NULL NULL X 4 D NULL X To get the ...