Dynamic Mapping For Source/Destination

Hello,

What I'm trying to accomplish is to have a variable names "SourceTable" and "DestinationTable". So for each SourceTable, the DestinationTable will have the same columns. All I need is to auto-map these columns between source and destination via code?

Is this possible?

Thanks,

awiora

You can do it, but not in the same package. Packages can't modify themselves while they are running. You can, however, use a package (with some code in it) to generate and call another package. Take a look at this post to see an example.

http://blogs.conchango.com/jamiethomson/archive/2007/03/28/SSIS_3A00_-Building-Packages-Programatically.aspx

|||

Exactly what I was looking for.

Thank you... Much Appreciated.

Dynamic If Update() in Trigger - Urgent!

Hi All

I have a question about generating dynmamicly If Update() statement in a
trigger..

in My db, there is a table that holds some column names of an another table.
for example;

Columns Table-A: Col1, Col2, Col3, Col4,Col5

Table-B: Col2, Col5 (The selected columns of Table A)

Then, in the Trigger of Table-A I use;

Select name from syscolumns where id=object_id('Table-A')

fetch next from TableA_Cursor into @.strColName

then, I used a statement like this..

if UPDATE(' + @.strColName + ')

But it gives "incorrect syntax" error..

How can I write this line?

Thanks alot in advance...

--
Message posted via http://www.sqlmonster.comI don't believe it is possible to use IF UPDATE() dynamically, nor is
it necessary. It also doesn't really make much sense to reference IF
UPDATE() in a cursor since the result will be the same for every row.
Anyway you shouldn't use cursors in triggers - they just turn your
set-based update statements into row-based updates, which is bad in
principle from a design poiunt of view and generally performs very
poorly.

If you want your triggers to take account of table structure changes
then generate the trigger code dynamically at DESIGN time rather than
runtime.

If you need a trigger to act on what data has changed then join the
Inserted and Deleted virtual tables and compare the columns. IF UPDATE
doesn't tell you what changed, only which columns were referenced by
the update statement.

If you need more help, please post a fuller description of your problem
including DDL and sample data.

--
David Portas
SQL Server MVP
--

Dynamic header column names in Excel

Folks,

I am running into an issue while trying to export data to a spreadsheet. I actually don't know how to do it... Considering I only know the column names by the time I execute my procedure, I can't use the Excel Destination to export data.

With DTSs I would create an ActiveX script to execute the procedure which loads the results into a temp table. After that I would select everything from this temp table and load the results into a record set, looping through this record set to create the destination spreadsheet with the dynamic column names.

When it comes to SSIS we are advised to write vb.net scripts instead of ActiveX... These ones do not have records set's but dataset's, which at first glance are only applicable to xml and not xls files (when I try to define a variable as a dataset in my vb.net code, I face a message saying: Missing reference required to assembly System.Xml...).

How I would create this spreadsheet using a vb.net code in SSIS packages? Please, help...

Thank you.

I am afraid that is something that is not that easy in SSIS. Jamie, has a serie of posts that talk about some differences with DTS:

http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4156.aspx

In SSIS the data flow needs to know the column metadata at design time. One option could be to generate packages programatically. Books on line has a chapter about that.

|||

Hi:

I have a related problem. I have multiple packages, but only a single table structure, and so I worked around by making a template XLS file, and using a filesystem task to make copies. The template has the required col headers.

Now the problem is that when a data structure changes, I have to re-do all my packages to incorporate the changed metadata.

Your problem description gave me an idea, and I am planning to try out the following:

1. Execute SQL task to call a stored procedure that drops my temp_table, and then inserts my resultset into temp_table (which now has all the columns required).

2. Have a script task that makes a ADO.net connection, gets a dataset, saves my data from temp_table to an XML file, creates a new XLS file, and calls the OpenXML method to open my data in the excel.

This will probably need VSTO installed on the same machine as BIDS. Since the Script Task only allows reference to managed assemblies from a particular path(s), I would need to create a custom .net DLL and store in those paths. The .net DLL will internally use Office Interop COM if I have office 2003 or lower.

Else I could simply save XML files and associate XML files with Excel by default.

I had done something similar in VB.net, and the only problem I see is that the XLS files thus created are bloated. Opening them and saving them again can compress the file to around half its size.

Does this approach look feasible? Maybe the gurus on this forum can point out flaws in this?

HTH

Kar

|||

Rafael and Karfast -

I really appreciate your replies. I took a look at Jamie's post mentioned by Rafael and I am just speechless. My concern in this migration from DTS to SSIS is all what we had in DTS and we are not having in SSIS. I truly understand some losses are needed towards a better final result but this case for an example (dynamic header column names) really seems not to be possible to be implemented in SSIS, or at least will require a lot of code intelligence versus something that was really simple in DTS.

I work for EDS and DBAs here are saying we will migrate soon for the next SQL Server 2005 version (which does not support DTS) and I am just deadly scared of what is gonna happen when we truly have to migrate all our packages.

I wonder why Microsoft still has these surprises for us and maybe this is something that they were willing to change in SSIS? As Karfast said, I guess I will wait for our SSIS gurus to discuss this a little bit more.

Thanks to all.

|||

Gabriel,

Perhaps few functionalities have been changed from DTS, but personally SSIS has a lot more potential to become an Enterprise ETL platform. How familiar are you with SSIS at this point? perhaps you should look a little bit into the all other features it offers. I never used DTS, but I cerntarly understand how surprising is to find out that something like that is missing.

Dynamic dataset column names and number of columns

I have a dataset that is based on a dynamic pivot stored procedure. There is
at least one column name that I know will be in the dataset, the key column,
but the rest of the columns are dependent on the data in the underlying table.
Once I have the dataset, how can I put it in the layout given that the
column names are dynamic and the number of columns are dynamic?
StephanieOn Aug 20, 4:36 pm, Stephanie <Stepha...@.discussions.microsoft.com>
wrote:
> I have a dataset that is based on a dynamic pivot stored procedure. There is
> at least one column name that I know will be in the dataset, the key column,
> but the rest of the columns are dependent on the data in the underlying table.
> Once I have the dataset, how can I put it in the layout given that the
> column names are dynamic and the number of columns are dynamic?
> Stephanie
You will want to look into Matrix Reports, where the pivoted column's
unique values are actually the number of columns in the report. This
provides the dynamic functionality you need.
Regards,
Enrique Martinez
Sr. Software Consultant

Dynamic create table

Hi,

I need to create a table....whose column names should be dynamic...I wouldnt know in advance what the column names would be...These coulmn names are availabe in another table as rows...

Ex: TABLE_A Data

Column Names

Col1

Col2

Col3

Col4...

Some column Names are known already...I need help in writinf the Create table statement..

I need to write something like

Create table Table1

(SerialNo int,Name nvarchar(50)+ AS select * from TABLE_A +

Address nvarchar(500))....

Now the Table1 should look like

Serial No Name Col1 Col2 Col3 Col4 Address

Can some one please let me know how can i accomplish this...I know i need to write a Dynamic Query but dont exactly know ho to write it and execute it....

Thanks

You need to create your "CREATE TABLE.." statement dynamically. How would a user select the columns? If you dont know the column names do you know what datatype they should be?|||

Yeah I know the DataType they are all of float datatype...

But the rows in TableA may change so I need to create a temporary table with the column names with how many ever rows are available in the TableA.

Eventually in the stored proc I am going to drop this Temporary table after selecting the Data from it....

|||Can you please let me know if i can create a table by using both as select and some already knowm column names|||

something like that ?

declare @.sql nvarchar(2000)

set @.sql = N'create table myTempTable ( id int identity, name varchar(100) );'

execute sp_executesql @.sql

|||

You can try this .It's working correctly.

Create proc prcTempTab

AS
SET NOCOUNT ON
declare @.j int
set @.j=1
declare @.strtemp varchar(1000)
declare @.c varchar(4)
Create table #temptab(i int)
while(@.j<4)
begin
set @.c='a'+convert(varchar(2),@.j)
set @.strtemp='ALTER TABLE #temptab ADD '+@.c+' int'
set @.j=@.j+1
exec (@.strtemp)
end

insert into #temptab values(1,2,3,4)
select * from #temptab

--exec prcTempTab

All the best

|||Good work Sarika, appreciable

Dynamic create table

Hi,

I need to create a table....whose column names should be dynamic...I wouldnt know in advance what the column names would be...These coulmn names are availabe in another table as rows...

Ex: TABLE_A Data

Column Names

Col1

Col2

Col3

Col4...

Some column Names are known already...I need help in writinf the Create table statement..

I need to write something like

Create table Table1

(SerialNo int,Name nvarchar(50)+ AS select * from TABLE_A +

Address nvarchar(500))....

Now the Table1 should look like

Serial No Name Col1 Col2 Col3 Col4 Address

Can some one please let me know how can i accomplish this...I know i need to write a Dynamic Query but dont exactly know ho to write it and execute it....

Thanks

You need to create your "CREATE TABLE.." statement dynamically. How would a user select the columns? If you dont know the column names do you know what datatype they should be?|||

Yeah I know the DataType they are all of float datatype...

But the rows in TableA may change so I need to create a temporary table with the column names with how many ever rows are available in the TableA.

Eventually in the stored proc I am going to drop this Temporary table after selecting the Data from it....

|||Can you please let me know if i can create a table by using both as select and some already knowm column names|||

something like that ?

declare @.sql nvarchar(2000)

set @.sql = N'create table myTempTable ( id int identity, name varchar(100) );'

execute sp_executesql @.sql

|||

You can try this .It's working correctly.

Create proc prcTempTab

AS
SET NOCOUNT ON
declare @.j int
set @.j=1
declare @.strtemp varchar(1000)
declare @.c varchar(4)
Create table #temptab(i int)
while(@.j<4)
begin
set @.c='a'+convert(varchar(2),@.j)
set @.strtemp='ALTER TABLE #temptab ADD '+@.c+' int'
set @.j=@.j+1
exec (@.strtemp)
end

insert into #temptab values(1,2,3,4)
select * from #temptab

--exec prcTempTab

All the best

|||Good work Sarika, appreciable

Dynamic columns

Hi,

In my report i want to display dynamic columns.For displaying what are columns available to user,Iam displaying the columns names in one multivalued parameter.For example in my report i have Date,CaseId,Age,State as columns,and multivalued parameter name is "Columns".

I wrote a custom function i.e ShowParameterValues(...) returns String s.If we select the Date and CaseID from the columns then s contain value:Date CaseID

Public Function ShowParameterValues(ByVal parameter as Parameter) as string
Dim s as String
For i as integer = 0 to parameter.Count-1
s =s+ CStr(parameter.Value(i))
Next
Return s
End Function

Now i want to show those two columns only.How to acheve this.

Thanks in advance

Currently, the RS object model doesn't support this feature. You can:

1. Include all columns on the report and hiding the ones that are not needed conditionally (Hidden property = True)

2. Implement a custom application which changes and uploads the report definition.

|||

Hi,

How to hide some columns conditionally.I need all the columns,show the columns which the user selects from multivalued parameter.

Thanks in advance

|||

Each column has a Hidden property which can be expression-based, e.g.:

=IIF(<evaluate your parameter selection condition>, True, False)

|||

Hi,

I know this.But how to write the condition,how would we know which column is selected from multivalued parameter

Please help me

Thanks in advance

|||Hi,
i am doing something similar for a selector where the user pick a start period and an end period. I am not using Matrix in this case because I want the table.
Should be similar enough to what you want to do.

In the second Column Hidden property put something like that:

= iif( CountRows("(Items_Selected") <2, True, False)
And increase the number by 1 for each consecutive column.

Have a dataset "Items_Selected" build upon your multi-select, something like that:

select Distinct
Case @.Time_Items when 'Qtr' then b.Qtr when 'Mth' then b.Mth when 'Week' then b.Week end as item
FROM onGlobals.dbo.tb_TimeBillBack b with(nolock)
where Case @.Time_Items when 'Qtr' then b.Qtr when 'Mth' then b.Mth when 'Week' then b.Week end >= @.Period_Start
AND Case @.Time_Items when 'Qtr' then b.Qtr when 'Mth' then b.Mth when 'Week' then b.Week end <= @.Period_End

Where you would have the b table build with all possible values present in your multi-value parameter.

Change the code above to use a IN clause to evaluate your multi-values.

It is kind of a hack which I hate but it works. Set the report layout with all possible columns and set the hidden property of each and every one of them.
Philippe|||http://msdn2.microsoft.com/en-us/library/aa337293.aspx

Dynamic Column Names?

Using SQL 2000
I am passing in the name of the column I want to update, @.Column. When I
SELECT @.Column it returns the value of @.Column instead of the value of the
actule table column. I have tried to use
Col_Name(OBJECT_ID('antwerp_cutoff'),OBJ
ECT_ID(@.Column)) but this does not
get the column name.
When I pass in Display1 I need to get back the value 2/2/2005
|Display1 | Display2|
--
|2/2/2005 | 2/3/2005|
--
Any help would be greatly appreciated.
Thanks,
Scott R. Butler
Stored Proc:
Create Procedure "Update_Antwerp_Displays"
(
@.Column varchar(256)
)
As
Declare @.i int
Declare @.Count int
set @.Count = (SELECT Count (*) FROM antwerp_trans)
UPDATE antwerp_cutoff
Set @.Column = (select @.Column+(Select Delta from antwerp_cutoff where id =
1 ) from antwerp_cutoff where Delta = 0)
WHERE ID = 1
UPDATE antwerp_cutoff
Set @.Column = (select @.Column+(Select Delta from antwerp_cutoff where id =
2 ) from antwerp_cutoff where Delta = 0)
WHERE ID = 2
UPDATE antwerp_depart
Set @.Column = (select @.Column+(Select Delta from antwerp_depart where id =
1 ) from antwerp_cutoff where Delta = 0)
where id = 1
UPDATE antwerp_arrive
Set @.Column = (select @.Column+(Select Delta from antwerp_arrive where id =
1 ) from antwerp_cutoff where Delta = 0)
where id = 1
While (@.i < @.Count)
BEGIN
UPDATE antwerp_trans
Set @.Column = (select @.Column+(Select Delta from antwerp_cutoff where id =
@.i ) from antwerp_cutoff where Delta = 0)
WHERE ID = @.i
SET @.i = @.i +1
ENDYou cannot use variables for tables, columns, etc. in queries. You'll
need to use dynamic SQL:
http://www.sommarskog.se/dynamic_sql.html
David Gugick
Imceda Software
www.imceda.com|||Thanks for the quick response... even though it was not the response I was
looking for.
Quess I will have to find another way to get it done.
Scott
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OYjcU7uDFHA.3324@.TK2MSFTNGP15.phx.gbl...
> You cannot use variables for tables, columns, etc. in queries. You'll need
> to use dynamic SQL:
> http://www.sommarskog.se/dynamic_sql.html
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Go back to your freshman software engineering books and look up the
concepts of coupling and cohesion.
What you want is a procedure with so little cohesion that any random
user, present or future, has more control over the RDBMS than the DBA.
This is **foundations of programming**, not anything particular to SQL.|||It seems that you are using column names to represent data. This is a
fundamental mistake and causes lots of problems, such as the one you
are having here. Columns should represent a single attribute and
repeating groups of columns are in violation of First Normal Form. Fix
your table design and you won't need messy dynamic SQL to accomplish
this.
David Portas
SQL Server MVP
--

Dynamic Column Names

Hi guys 'n gals,

I am trying to achieve something a bit complex (or at least appears to be for me).

I have the following table structure:

UID, GroupID, ColumnName, ColumnValue

here is some example data:

UID, GroupId, ColumnName, ColumnValue
1, 1, MAC Address, 8a7sd87sad
2, 1, IP Address, 192.168.1.100
3, 1, Name, John
4, 2, MAC Address, 09a8sd098as
5, 2, Name, Steven

and here is what I would like the example to come out like:

GroupID, MAC Address, IP Address, Name
1, 8a7sd87sad, 192.168.1.100, John
2, 09a8sd098as, NULL, Steven

It needs to be completely dynamic though, as a new column name could be entered to the source table at any time...

I have tried Dynamic SQL and JOINs, but can only seem to get it to work correctly. I was starting to consider doing a loop similar to this:

Code Snippet

DECLARE @.ColumnName varchar(128)
DECLARE @.Sql varchar(255)
SELECT @.ColumnName = [ColumnName] FROM tblSourceData
SELECT @.Sql = 'SELECT [GroupId], [ColumnValue] AS ['+@.ColumnName+'] FROM tblSourceData'
EXEC (@.Sql)

Could somebody please point me in the right direction? I've heard a bit about Pivot tables in my search for this solution, is that perhaps the route I need to go?

Regards,
Justin

You need not to have dynamic SQL, you are trying to achieve the table pivoting..

Code Snippet

Create Table #data (

[UID] Varchar(100) ,

[GroupId] Varchar(100) ,

[ColumnName] Varchar(100) ,

[ColumnValue] Varchar(100)

);

Insert Into #data Values('1','1','MAC Address','8a7sd87sad');

Insert Into #data Values('2','1','IP Address','192.168.1.100');

Insert Into #data Values('3','1','Name','John');

Insert Into #data Values('4','2','MAC Address','09a8sd098as');

Insert Into #data Values('5','2','Name','Steven');

On SQL server 2000/2005,

Code Snippet

Select

GroupId,

Max(Case When[ColumnName] = 'MAC Address' Then ColumnValue End) as [MAC Address],

Max(Case When[ColumnName] = 'IP Address' Then ColumnValue End) as [IP Address],

Max(Case When[ColumnName] = 'Name' Then ColumnValue End) as [Name]

from

#data

Group By

GroupId

Only on SQL server 2005,

Code Snippet

Select * From

(Select GroupId,ColumnName,ColumnValue from #data) as Data

Pivot

(

Max(ColumnValue) For ColumnName in([MAC Address], [IP Address], [Name])

)

as PVT

|||

The one problem though is that you are still manually specifying column names, when I don't want to have to manually change my SQL each time I add a new column in the table... Is there no way of making this more dynamic?

Regards,

Justin

|||

Using the previous temp table data,

On SQL Server 2000/2005

Code Snippet

Declare @.PreparedQuery as Varchar(8000);

Declare @.SqlQuery as Varchar(8000);

Set @.PreparedQuery = 'Max(Case When[ColumnName] = ''?'' Then ColumnValue End) as [?]'

Set @.SqlQuery = 'Select GroupId'

Select @.SqlQuery = @.SqlQuery + ',' +Replace(@.PreparedQuery,'?',ColumnName)

From

(select Distinct ColumnName from #data) as Data

Exec (@.SqlQuery + 'from #data Group By GroupId')

On SQL Server 2005

Code Snippet

Declare @.PreparedQuery as Varchar(8000);

Declare @.SqlQuery as Varchar(8000);

Set @.PreparedQuery = '[?]'

Set @.SqlQuery =

'Select * From (Select GroupId,ColumnName,ColumnValue from #data) as Data

Pivot (Max(ColumnValue) For ColumnName in('

Select @.SqlQuery = @.SqlQuery + Replace(@.PreparedQuery,'?',ColumnName) + ','

From

(select Distinct ColumnName from #data) as Data

Set@.SqlQuery = Substring(@.SqlQuery,1,Len(@.SqlQuery)-1) + ')) as Pvt'

Exec (@.SqlQuery)

|||Works a charm, and is a lot less code than I originally thought it would be, thank you!

dynamic column name?

I want to create a table where the column names and number of columns is not
known. It can be a temp table or table variable.
Something like:
DECLARE @.output table (@.columnname int)
or
ALTER TABLE @.output ADD @.columnname varchar(50)
-Max*shudder*
http://www.sommarskog.se/dynamic_sql.html
http://www.aspfaq.com/
(Reverse address to reply.)
"Max" <nospam@.notvalid.com> wrote in message
news:u8i9m9qHFHA.588@.TK2MSFTNGP15.phx.gbl...
> I want to create a table where the column names and number of columns is
not
> known. It can be a temp table or table variable.
> Something like:
> DECLARE @.output table (@.columnname int)
> or
> ALTER TABLE @.output ADD @.columnname varchar(50)
> -Max
>|||>> I want to create a table where the column names and number of
columns is not known.<<
Why, in the name of God!!' The whole idea of a database is that you
have a reality, you model it and then you work with it.
What you are saying is tha tyou have no idea what you are doing.
Tell me the name of your employer. I think that I can get $2000.00 per
day consulting contract fixing your screw ups. My estimate is based on
a mere 35 years in the IT industry, and 10 years on the ANSI X3H2
Database Standards Committee.
Max, if this is no joke and you are writing databases, then stop and
walk away from your job. Your question is that kind of fundamentally
stupid.
And before you start whining about myn abusing you, consider that last
year I did an email consult with an organization that sent medical
supplies to Africa. Their problem was a programmer who did not know
what 1NF was. The shipments were shorted and children died.
It take SIX YEARS to become a Journeyman Union Carpenter in New York
State. how many years did you think that you need to say are database
designer?|||I want to output a table that is dynamically created in a stored proc.
Queries are dynamically generated all the time. Why is that stupid?
-Max
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109719661.313856.309060@.l41g2000cwc.googlegroups.com...
> columns is not known.<<
> Why, in the name of God!!' The whole idea of a database is that you
> have a reality, you model it and then you work with it.|||>> I want to output a table that is dynamically created in a stored
proc. Queries are dynamically generated all the time. Why is that
stupid? <<
All of 40 years of software engineering, you idiot :) Do you want to
the references ?
--CELKO--|||From someone who gets strange ideas all the time:
Doctrines of Demons and Democrats!! HOW Dare you raise the stench of Hell in
this Sacred and Holy place with your blasphemies ideas. You will cause the
Holy Judgment and wrath of the great and powerful gods of SQL (and there a
lot of’em around here) to rain destruction upon us all!!! Your doom will
be
complete. YOU SHOULD BE CAST OUT!! BEGONE YE SPAWN OF THE EVIL ONE!!
I mean really! These religious fanatics around here really get carried away
sometimes. So someone has an idea. It may be good one, it may be a bad one.
No need to get all puritanical. Come on! Lighten up already. Believe it or
not our souls do not hang by a thread.
and as always i wish to say that i get a LOT of good advice here all the tim
e!
thank all
kes
"Max" wrote:

> I want to create a table where the column names and number of columns is n
ot
> known. It can be a temp table or table variable.
> Something like:
> DECLARE @.output table (@.columnname int)
> or
> ALTER TABLE @.output ADD @.columnname varchar(50)
> -Max
>
>|||What is so fundamentally wrong with discovering and creating database schema
or metadata during runtime? I can give you 50 enterprise level examples of
this.
-Max
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109722606.650060.311970@.f14g2000cwb.googlegroups.com...
> proc. Queries are dynamically generated all the time. Why is that
> stupid? <<
> All of 40 years of software engineering, you idiot :) Do you want to
> the references ?
> --CELKO--
>|||I've always imagined how I'd be at age 60, a grumpy old man, except for that
I would have embraced high technology instead of condemned it as would the
typical grumpy old man of today. Well I think I just found an example of
this hi-tech old man. ;)
-Max
"Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in message
news:C101A921-6189-4BD7-9B3D-F889A02179D7@.microsoft.com...
> From someone who gets strange ideas all the time:
> Doctrines of Demons and Democrats!! HOW Dare you raise the stench of Hell
> in
> this Sacred and Holy place with your blasphemies ideas. You will cause the
> Holy Judgment and wrath of the great and powerful gods of SQL (and there a
> lot of'em around here) to rain destruction upon us all!!! Your doom will
> be
> complete. YOU SHOULD BE CAST OUT!! BEGONE YE SPAWN OF THE EVIL ONE!!|||Why are the return columns of the query changing? Send the columns back and
let the presentation tier determine which columns to show/hide. Or have
different stored procedures based on the type of report.
On 3/1/05 6:40 PM, in article OACwYgrHFHA.720@.TK2MSFTNGP14.phx.gbl, "Max"
<nospam@.notvalid.com> wrote:

> I want to output a table that is dynamically created in a stored proc.
> Queries are dynamically generated all the time. Why is that stupid?
> -Max
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1109719661.313856.309060@.l41g2000cwc.googlegroups.com...
>|||Ye hath seen the LIGHT!! Go now!! and sin no more!!!!
(ya gota love this ______. have a good one!!!) ;)
"Max" wrote:

> I've always imagined how I'd be at age 60, a grumpy old man, except for th
at
> I would have embraced high technology instead of condemned it as would the
> typical grumpy old man of today. Well I think I just found an example of
> this hi-tech old man. ;)
> -Max
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:C101A921-6189-4BD7-9B3D-F889A02179D7@.microsoft.com...
>
>

dynamic audit trigger

Hi. I'm trying to create a dynamic audit trigger for a rather large datbase.
the trigger should be able to dynamically read the field names form the
deleted tabel and check each column for updates to store them to a table (i
only want to store fieldnames and values that changed)
the spilified table looks like:
date --the date of the update
field --the updated column
value --the inserted value
I can read column names, from deleted and check for updates by comparing to
inserted. the problem i have is the retrieval of the columns value.
How do i fetch the value of one single column from the deleted table if i
have stored the columns name in a variable as i cannot access deleted via
sp_execute?
this does not work:
select @.fieldName = name from syscolumns where colid=@.field and...
declare @.sql nvarchar(4000);
declare @.params nvarchar(4000);
set @.sql = 'select @.val = cast('+@.fieldname+' as nvarchar(3000)) from
deleted';
set @.params = '@.val nvarchar(3000) OUTPUT';
exec sp_executesql @.sql, @.params, @.val=@.value OUTPUT;
anyone any idea'
thanks so farhow did you check for the updates by comparing with the inserted table.
Can you paste the script for that?|||Hi I think i solved the problem storing the values of deleted to a temporary
table. not quite what i wanted but it works.
here's the script i use anyway
CREATE TRIGGER dbo.mytable_trigger ON dbo... FOR UPDATE
AS
DECLARE @.bit INT,
@.field INT,
@.char INT,
@.fieldName NVARCHAR(50),
@.modificationDate DATETIME,
@.contextId BIGINT,
@.value NVARCHAR(3000),
@.value2 NVARCHAR(3000),
@.maxCols INT
SET @.field = 0;
--the modification date is used to indicate date and time the update occured
SET @.modificationDate = GETDATE();
--DROP THE TEMPORARY TABLE WHICH STORES THE VALUES FROM deleted IF IT EXISTS
IF EXISTS(SELECT id FROM sysobjects WHERE NAME='tmpDel') DROP TABLE tmpDel;
--STORE VALUES FROM updated COLS in a temporary table
SELECT * INTO tmpDel FROM deleted;
--iterate throug all columns of the source table and check the values
against the deleted table to
--find the cols updated
--store the cols to a history table using a stored procedure
WHILE @.field < (SELECT MAX(colid) FROM syscolumns WHERE id = (SELECT id FROM
sysobjects WHERE name = 'mytable')) BEGIN
--DEBUG OUTPUT
--print 'field: '+cast(@.field as nvarchar(50));
--calculate the field id's (for details refer to:
http://msdn2.microsoft.com/de-de/library/ms186329.aspx)
SET @.field=@.field+1;
SET @.bit = (@.field-1)%8+1;
SET @.bit = power(2, @.bit-1);
SET @.char = ((@.field - 1) / 8) + 1
--check if a column has been updated and perform the compare operation here
IF substring(COLUMNS_UPDATED(),@.char, 1) & @.bit > 0 BEGIN
SELECT @.field, name FROM syscolumns WHERE colid=@.field AND id = (SELECT id
FROM sysobjects WHERE name = 'mytable')
-- select the ucrrent fieldName
select @.fieldName = name FROM syscolumns WHERE colid=@.field AND id =
(SELECT id FROM sysobjects WHERE name = 'mytable');
--DEBUG OUTPUT
--print 'fieldname: '+@.fieldName
SELECT @.contextId = id FROM deleted;
--DEBUG OUTPUT
--print 'contextid: "'+cast(@.contextId as nvarchar(10));
--as a variable cannot be used to select a col by its name stored in the
variable
--use dynamic sql here.
--the field to select is written by a variable to an sqlString
--within the sql string a variable called @.val is definded as an output
parameter
--to avoid type problems all selected values are casted to nvarchar
--via sp_executesql the statement is executed and the variable assigned.
DECLARE @.sql NVARCHAR(4000);
DECLARE @.params NVARCHAR(4000);
--select the actual value for the field
SET @.sql = 'select @.val = cast('+@.fieldname+' as nvarchar(3000)) from
mytable';
SET @.params = '@.val nvarchar(3000) OUTPUT';
EXEC sp_executesql @.sql, @.params, @.val=@.value OUTPUT;
--select the fields old value
SET @.sql = 'select @.val = cast('+@.fieldname+' as nvarchar(3000)) from
tmpDel';
SET @.params = '@.val nvarchar(3000) OUTPUT';
EXEC sp_executesql @.sql, @.params, @.val=@.value2 OUTPUT;
--if the values do not match the column has been updated
IF(@.value <> @.value2) BEGIN
--calll stored proc to save values to history
EXEC dbo.HistorySave4Table 'dbo.[mytable-History]', @.modificationDate,
@.contextId, null, @.fieldName, @.value2;
END
end
end
--DROP THE TEMPORARY TABLE WHICH STORES THE VALUES FROM deleted IF IT EXISTS
IF EXISTS(SELECT id from sysobjects where name='tmpDel') drop table tmpDel;
"Omnibuzz" wrote:

> how did you check for the updates by comparing with the inserted table.
> Can you paste the script for that?
>