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',...
**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...
What's the equivalent of Oracle's RowID in SQL Server?
...
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...
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...
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.
...
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 ...
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 ...
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?
...
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...
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...
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.
...
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...
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...
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 ...
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 would I call a Stored Procedure that returns data in a View? Is this even possible?
...
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.....
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...
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 ...