Need to audit structure change

How to audit changes made by SQL users on table
structure. Some changes include data type, column name,
and number of columns.
Thanks for suggestion.Look up sp_trace_create in the books online - one of the security audit
event types will allow you to capture all DDL which should get everything
you're looking for here. You can also use sql profiler to build the trace
for you and then script it out if you don't want to use the GUI all the
time...
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim Fuller" <anonymous@.discussions.microsoft.com> wrote in message
news:020801c3ba8a$69ee1650$a101280a@.phx.gbl...

quote:

---

> How to audit changes made by SQL users on table
> structure. Some changes include data type, column name,
> and number of columns.
> Thanks for suggestion.

---

Need SQL query

Hi there. U have table structure shown here:
category (Int - AutoIncrement) parent_category(Int) Title(string)
1 0
HOME PAGE
2 1
FIRST DEPTH 1
3 1
FIRST DEPTH 2
4 1
FIRST DEPTH 3
5 2
SECOND DEPTH 1
6 3
SECOND DEPTH 2
7 3
SECOND DEPTH 3
8 6
THIRD DEPTH 1
I want 1 (one) SQL query that can return me full depth for certain caregory.
In some meta-language
for category 8 it look like this:
SELECT * FROM table WHERE category = 8
WHILE parent_category <> 1
SELECT * FROM table WHERE category = [parent_category from previous
iteration]
WEND
So in that case resulting recordset would be
category (Int - AutoIncrement) parent_category(Int) Title(string)
8 6
HOME PAGE
6 3
FIRST DEPTH 1
3 1
FIRST DEPTH 2
I know that I can do this using more that one recordset, but I want all this
in one recordset.
I am using ADO, not ADO.NET
ThanksBola,
You can achieve this using user defined function, see following example.
create table tree
(category Int, parent_category Int, Title varchar(50))
go
insert into tree
select 1, 0,'HOME PAGE' union all
select 2, 1,'FIRST DEPTH 1' union all
select 3, 1,'FIRST DEPTH 2' union all
select 4, 1,'FIRST DEPTH 3' union all
select 5, 2,'SECOND DEPTH 1' union all
select 6, 3,'SECOND DEPTH 2' union all
select 7, 3,'SECOND DEPTH 3' union all
select 8, 6,'THIRD DEPTH 1'
select * from tree
go
create function fn_get_tree (@.y int)
returns
@.tb table(empid int,
supervisor int,
empname varchar(300)
)
as
begin
declare @.x table (empid int)
insert into @.x
select parent_category from tree where category = @.y
union all
select @.y
while 1=1
begin
insert into @.x
select parent_category from tree where category in (select distinct empid from @.x)
and parent_category not in(Select empid from @.x)
if @.@.rowcount = 0
break
end
insert into @.tb
select * from tree where
exists
(select * from @.x a where a.empid= tree.category)
return
end
go
--usage
select * from fn_get_tree (7)
--
- Vishal

Need Query for update and insert in one go.

Hi All,
Please look at the following tables. I have Two tables
Data and TmpData with the following structure.

Data ( All int columns, ID column is Primary and identity)

Id UserID PrgID RoldID
322 1 1 2
323 1 2 2
324 1 3 2
325 2 1 2
326 2 2 2
327 2 3 2
328 3 1 2
329 3 2 2
330 3 3 2

TmpData
Id UserID PrgID RoldID
82 1 1 3
83 1 2 3
84 1 3 3
85 2 1 3
86 2 2 3
87 2 3 3
91 20 1 2
92 20 2 2
93 20 3 2
94 21 1 2
95 21 2 2
96 21 3 2

Now I need to run a query so that
Part 1: It updates existing RoleId columns ( Based on Userid,PrgID) in 'Data' Table with corresponding values from 'Data' table.
Part2 : It inserts new rows in 'TmpData' to 'Data' table.

I am done with Part1 using the simple update statement.

Update Data
Set Data.programroleid=tmp.ProgramRoleID
from TmpData tmp
where Data.userid=tmp.UserId
and Data.programId=tmp.ProgramId

This works fine for existing userids,programids in 'Data' and 'TmpData' tables. But I am struggling with inserting new rows into Data from 'TmpData' ( That exists only in 'tmpData' table). Based on above table structures how do I insert new data into 'Data' table from 'TmpData'.INSERT INTO Data(ID, UserID, PrgID, RoldID)
SELECT * FROM TmpData
WHERE ID not in (SELECT ID FROM TmpData Inner Join Data on TmpData.UserID=Data.UserID and TmpData.ProgID=Data.PrgID and TmpData.RoldID=Data.RoldID)|||ID is auto-generated identity value, and thus cannot be inserted into the target table (unless you specifically disable this for the transaction...).
I wouldn't expect the identity values to match between Data and TmpData anyway, so the "NOT IN" method is probably not appropriate. It is also not efficient. NOT EXISTS is faster than NOT IN.

INSERT INTO Data
(ID,
UserID,
PrgID,
RoldID)
SELECT ID,
UserID,
PrgID,
RoldID
FROM TmpData
WHERE NOT EXISTS
(SELECT *
FROM Data
WHERE Data.UserID = TmpData.UserID
and Data.PrgID = TmpData.PrgID
and Data.RoldID = TmpData.RoldID)

Need help writing query/ stored proc

I need to write query or stored proc that is going to be used to gen. a
report
This is a structure of my table
Id Date ItemType OrdersPlaced
1 08/21 1 100
1 08/21 2 500
1 08/21 3 200
1 08/21 4 150
2 ... ... ...
2 ... ... ...
3 ... ... ...
4 ... ... ...
4 ... ... ...
The report is going to take Id and Date as inputs and report generated
is in following format
Id: 1
Date: 08/21
ItemType 1 ItemType 2 ItemType 3 ItemType 4
100 500 200 150
This can definetely be done creating a temp table and writing to this
temp table thru multiple selects for each itemtype.
Is there any better way'
ThanksHave a look at the PIVOT function in SQL 2005 Books online.
Or this example on SQL 2000 will get you on the way
http://www.dandyman.net/sql/samples/pivottable.txt
__________________________________________________
Dandy Weyn - Dandyman (r)
MCSE-MCSA-MCDBA-MCDST-MCT Community Leader
MCTS SQL 2005- MCITP Database Administrator
Author of Sybex Exam Study Guide MCTS SQL 2005
http://www.dandyman.net
"absoft" <arpit.00@.gmail.com> wrote in message
news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
>I need to write query or stored proc that is going to be used to gen. a
> report
> This is a structure of my table
> Id Date ItemType OrdersPlaced
> 1 08/21 1 100
> 1 08/21 2 500
> 1 08/21 3 200
> 1 08/21 4 150
> 2 ... ... ...
> 2 ... ... ...
> 3 ... ... ...
> 4 ... ... ...
> 4 ... ... ...
> The report is going to take Id and Date as inputs and report generated
> is in following format
> Id: 1
> Date: 08/21
> ItemType 1 ItemType 2 ItemType 3 ItemType 4
> 100 500 200 150
> This can definetely be done creating a temp table and writing to this
> temp table thru multiple selects for each itemtype.
> Is there any better way'
> Thanks
>|||You can generate a summarized query using SUM(OrdersPlaced) and GROUP BY
[Id], [Date]. The sideways generation you're looking for is a pivot table
type query, which can be done with a "monster" CASE statement in SQL 2000 or
the PIVOT operator in SQL 2005. Either method would require you to know,
and hard-code, your column "headings" (Item Type 1, Item Type 2, etc.) in
advance. If you don't know them in advance you can use dynamic SQL to work
it out.
Personally I'd recommend doing the SUM()/GROUP BY and transferring the
results to a front-end app and format it there.
"absoft" <arpit.00@.gmail.com> wrote in message
news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
>I need to write query or stored proc that is going to be used to gen. a
> report
> This is a structure of my table
> Id Date ItemType OrdersPlaced
> 1 08/21 1 100
> 1 08/21 2 500
> 1 08/21 3 200
> 1 08/21 4 150
> 2 ... ... ...
> 2 ... ... ...
> 3 ... ... ...
> 4 ... ... ...
> 4 ... ... ...
> The report is going to take Id and Date as inputs and report generated
> is in following format
> Id: 1
> Date: 08/21
> ItemType 1 ItemType 2 ItemType 3 ItemType 4
> 100 500 200 150
> This can definetely be done creating a temp table and writing to this
> temp table thru multiple selects for each itemtype.
> Is there any better way'
> Thanks
>|||Thanks y'all I am using SQL 2000 so pivot operator is not an option
open for me. I like the idea of using the "monster" CASE statement ...
and it almost returns the kind of report that I need ... just one snag
apart from the above fields there is one more calculated field that
references another table
e.g.
Table B
Id ItemType EstimatedTotal
1 1 200
1 2 800
1 3 200
1 4 200
The new field is a sum of EstimatedTotal for all ItemType and is used
for comparison to the actual total. The new report looks something like
this:
> > Id: 1
> > Date: 08/21
> > ItemType 1 ItemType 2 ItemType 3 ItemType 4 ActualTotal EstimatedTotal
> > 100 500 200 150 950 1400
going forward with the logic of using CASE and GROUP BY, can I get this
EstimatedTotal of 1400 returned by same query without using cursors or
temp tables
Any thoughts!!
Mike C# wrote:
> You can generate a summarized query using SUM(OrdersPlaced) and GROUP BY
> [Id], [Date]. The sideways generation you're looking for is a pivot table
> type query, which can be done with a "monster" CASE statement in SQL 2000 or
> the PIVOT operator in SQL 2005. Either method would require you to know,
> and hard-code, your column "headings" (Item Type 1, Item Type 2, etc.) in
> advance. If you don't know them in advance you can use dynamic SQL to work
> it out.
> Personally I'd recommend doing the SUM()/GROUP BY and transferring the
> results to a front-end app and format it there.
> "absoft" <arpit.00@.gmail.com> wrote in message
> news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
> >I need to write query or stored proc that is going to be used to gen. a
> > report
> > This is a structure of my table
> >
> > Id Date ItemType OrdersPlaced
> > 1 08/21 1 100
> > 1 08/21 2 500
> > 1 08/21 3 200
> > 1 08/21 4 150
> > 2 ... ... ...
> > 2 ... ... ...
> > 3 ... ... ...
> > 4 ... ... ...
> > 4 ... ... ...
> >
> > The report is going to take Id and Date as inputs and report generated
> > is in following format
> >
> > Id: 1
> > Date: 08/21
> > ItemType 1 ItemType 2 ItemType 3 ItemType 4
> > 100 500 200 150
> >
> > This can definetely be done creating a temp table and writing to this
> > temp table thru multiple selects for each itemtype.
> > Is there any better way'
> >
> > Thanks
> >

Need help writing query/ stored proc

I need to write query or stored proc that is going to be used to gen. a
report
This is a structure of my table
Id Date ItemType OrdersPlaced
1 08/21 1 100
1 08/21 2 500
1 08/21 3 200
1 08/21 4 150
2 ... ... ...
2 ... ... ...
3 ... ... ...
4 ... ... ...
4 ... ... ...
The report is going to take Id and Date as inputs and report generated
is in following format
Id: 1
Date: 08/21
ItemType 1 ItemType 2 ItemType 3 ItemType 4
100 500 200 150
This can definetely be done creating a temp table and writing to this
temp table thru multiple selects for each itemtype.
Is there any better way'
ThanksHave a look at the PIVOT function in SQL 2005 Books online.
Or this example on SQL 2000 will get you on the way
http://www.dandyman.net/sql/samples/pivottable.txt
________________________________________
__________
Dandy Weyn - Dandyman (r)
MCSE-MCSA-MCDBA-MCDST-MCT Community Leader
MCTS SQL 2005- MCITP Database Administrator
Author of Sybex Exam Study Guide MCTS SQL 2005
http://www.dandyman.net
"absoft" <arpit.00@.gmail.com> wrote in message
news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
>I need to write query or stored proc that is going to be used to gen. a
> report
> This is a structure of my table
> Id Date ItemType OrdersPlaced
> 1 08/21 1 100
> 1 08/21 2 500
> 1 08/21 3 200
> 1 08/21 4 150
> 2 ... ... ...
> 2 ... ... ...
> 3 ... ... ...
> 4 ... ... ...
> 4 ... ... ...
> The report is going to take Id and Date as inputs and report generated
> is in following format
> Id: 1
> Date: 08/21
> ItemType 1 ItemType 2 ItemType 3 ItemType 4
> 100 500 200 150
> This can definetely be done creating a temp table and writing to this
> temp table thru multiple selects for each itemtype.
> Is there any better way'
> Thanks
>|||You can generate a summarized query using SUM(OrdersPlaced) and GROUP BY
[Id], [Date]. The sideways generation you're looking for is a pivot
table
type query, which can be done with a "monster" CASE statement in SQL 2000 or
the PIVOT operator in SQL 2005. Either method would require you to know,
and hard-code, your column "headings" (Item Type 1, Item Type 2, etc.) in
advance. If you don't know them in advance you can use dynamic SQL to work
it out.
Personally I'd recommend doing the SUM()/GROUP BY and transferring the
results to a front-end app and format it there.
"absoft" <arpit.00@.gmail.com> wrote in message
news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
>I need to write query or stored proc that is going to be used to gen. a
> report
> This is a structure of my table
> Id Date ItemType OrdersPlaced
> 1 08/21 1 100
> 1 08/21 2 500
> 1 08/21 3 200
> 1 08/21 4 150
> 2 ... ... ...
> 2 ... ... ...
> 3 ... ... ...
> 4 ... ... ...
> 4 ... ... ...
> The report is going to take Id and Date as inputs and report generated
> is in following format
> Id: 1
> Date: 08/21
> ItemType 1 ItemType 2 ItemType 3 ItemType 4
> 100 500 200 150
> This can definetely be done creating a temp table and writing to this
> temp table thru multiple selects for each itemtype.
> Is there any better way'
> Thanks
>|||Thanks y'all I am using SQL 2000 so pivot operator is not an option
open for me. I like the idea of using the "monster" CASE statement ...
and it almost returns the kind of report that I need ... just one snag
apart from the above fields there is one more calculated field that
references another table
e.g.
Table B
Id ItemType EstimatedTotal
1 1 200
1 2 800
1 3 200
1 4 200
The new field is a sum of EstimatedTotal for all ItemType and is used
for comparison to the actual total. The new report looks something like
this:
[vbcol=seagreen]
going forward with the logic of using CASE and GROUP BY, can I get this
EstimatedTotal of 1400 returned by same query without using cursors or
temp tables
Any thoughts!!
Mike C# wrote:[vbcol=seagreen]
> You can generate a summarized query using SUM(OrdersPlaced) and GROUP BY
> [Id], [Date]. The sideways generation you're looking for is a piv
ot table
> type query, which can be done with a "monster" CASE statement in SQL 2000
or
> the PIVOT operator in SQL 2005. Either method would require you to know,
> and hard-code, your column "headings" (Item Type 1, Item Type 2, etc.) in
> advance. If you don't know them in advance you can use dynamic SQL to wor
k
> it out.
> Personally I'd recommend doing the SUM()/GROUP BY and transferring the
> results to a front-end app and format it there.
> "absoft" <arpit.00@.gmail.com> wrote in message
> news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...