please give any example for using UpdateQueryColumns Property

please help me...

what i required is, When a table is updated i want to get the column names
that affected , I think that UpdateQueryColumns Propertey may help me..

If what i am thinking is correct,please specify how to use that propertey,

otherwise specify how i can satisfy my requirement..

Thanks in advance........

Quote:

Originally Posted by ramesh1210

please help me...

what i required is, When a table is updated i want to get the column names
that affected , I think that UpdateQueryColumns Propertey may help me..

If what i am thinking is correct,please specify how to use that propertey,

otherwise specify how i can satisfy my requirement..

Thanks in advance........

create a trigger. use the UPDATE(field) function

please check this SP syntax - need more eyes!

when I try to create this SP I get: "incorrect syntax near @.MyResult"
I have tried INT and different variable names, but get same error.

CREATE PROCEDURE sp_IsValidLogon
@.UserName varchar(16),
@.Password varchar(16) ,
@.MyResult varchar(3) OUTPUT
As
if exists(Select * From User_Table
Where UserName = @.UserName
And
Password = @.Password)
begin
@.MyResult = 1
end
else
begin
INSERT INTO FailedLogons(UserName, Password)
values(@.UserName, @.Password)

declare @.totalFails int
Select @.totalFails = Count(*) From FailedLogons
Where UserName = @.UserName
And dtFailed > GetDate()-1

if (@.totalFails > 5)
UPDATE User_Table Set Active = 0
Where UserName = @.UserName

@.MyResult = 0
endThe answer is

SET @.MyResult = ...|||Thanks George,
That's sorted it.
I seem to have two threads in this forum now tho' :o sorry guys.|||No worries - I will remove the dupe :)

PIVOT with dynamic columns names created

I am trying to do a PIVOT on a query result, but the column names created by the PIVOT function are dynamic.

For example (modified from the SQL Server 2005 Books Online documentation on the PIVOT operator) :

SELECT
Division,
[2] AS CurrentPeriod,
[1] AS PreviousPeriod
FROM
(
SELECT
Period,
Division,
Sales_Amount
FROM
Sales.SalesOrderHeader
WHERE
(
Period = @.period
OR Period = @.period - 1
)
) p
PIVOT
(
SUM (Sales_Amount)
FOR Period IN ( [2], [1] )
) AS pvt

Let's assume that any value 2 is selected for the @.period parameter, and returns the sales by division for periods 2 and 1 (2 minus 1).

Division CurrentPeriod PreviousPeriod
A 400 3000
B 400 100
C 470 300
D 800 2500
E 1000 1900

What if the value @.period were to be changed, to say period 4 and it should returns the sales for periods 4 and 3 for example, is there a way I can change to code above to still perform the PIVOT while dynamically accepting the period values 4 and 3, applying it to the columns names in the first SELECT statement and the FOR ... IN clause in the PIVOT statement ?

Need a way to represent the following [2] and [1] column names dynamically depending on the value in the @.period parameter.

[2] AS CurrentPeriod,
[1] AS PreviousPeriod

FOR Period IN ( [2], [1] )

I have tried to use the @.period but it doesn't work.

Thanks in advance.

Kenny

This is a one drawback to the current Pivot feature. You will have to use dynamic sql for this.

Itzik has written a good article on this.

http://www.sqlmag.com/Article/ArticleID/94268/sql_server_94268.html