Columns_Updated () and Update() functions

Hello all,

I'm trying to apply an audit DB trigger on a master-detail tables ,I do not need to insert record in the audit master every time an update happend in the audit detail,I tried to use columns_updated() > 0 but it didn't work the way I axpected it to be ..it stopped inserting into the audit master even if an update was applied against the master table ...any help please and I use the Update() function ? is their any major difference?

your help is appreciated

Thanks

Alaa M

COLUMNS_UPDATED() returns a value as a VARBINARY datatype, so for your comparison to work you need to convert the value to an INT first, like this:

CAST(COLUMNS_UPDATED() AS INT)

Chris

|||

Thanks CHRIS

Actually it didn't work ..

or may be i'm missing something here ,anyway this is the code I'm using if you or anyone can point the wrong thing that I'm doing here will be great.

this is the trigger for the master table ,and I only need to insert the changes applied against this table into Audit master no matter how many updates applied to the detail table.

ALTER Trigger trig_Audit_Upd
on [dbo].[Master]

For Update
--WITH ENCRYPTION
AS
IF EXISTS
(
SELECT 'True'
FROM deleted d
LEFT JOIN [Master] A
ON d.MasterID = A.MasterID
)

if (CAST(COLUMNS_UPDATED() AS INT ) & 255) > 0

begin
INSERT INTO [dbo].[syslogAuditMaster](
[MasterID], [Name], [CodeIDType], [DateStarted], [DateDue], [CodeIDReminderType],
[CodeIDAuditStatus], [NoteID], [DeletedOnDate], [Event])
Select del.[MasterID],del.[Name],del.[CodeIDType],del.[Datestarted],del.[Datedue],del.[CodeIDReminderType],
del.[CodeIDAuditStatus], del.[NoteID], del.[DeletedOnDate], 'UPDATE'
FROM deleted del
WHERE del.MasterID = MastertId

end;

thank

|||

I get the feeling that you might be trying to over-complicate things. I can't see the point of explicitly checking for updated columns using the COLUMNS_UPDATED() function - the fact that at least one row has been updated is implied by the existence of rows in the 'deleted' virtual table.

I'm also confused by the WHERE clause in the INSERT statement:

WHERE del.MasterID = MastertId

Does the MastertId column exist in the 'deleted' virtual table? If so does MastertId ever equal del.MasterID?

Would the re-worked example below work for you?

Chris

ALTER TRIGGER trig_Audit_Upd

ON [dbo].[Master]

FOR UPDATE

--WITH ENCRYPTION

AS

IF EXISTS (SELECT 1 FROM deleted)

BEGIN

INSERT INTO [dbo].[syslogAuditMaster]

([MasterID],

[Name],

[CodeIDType],

[DateStarted],

[DateDue],

[CodeIDReminderType],

[CodeIDAuditStatus],

[NoteID],

[DeletedOnDate],

[Event])

SELECT del.[MasterID],

del.[Name],

del.[CodeIDType],

del.[Datestarted],

del.[Datedue],

del.[CodeIDReminderType],

del.[CodeIDAuditStatus],

del.[NoteID],

del.[DeletedOnDate],

'UPDATE'

FROM deleted del

END

|||

Thnaks for your replay,

I think you are missing the whole point of my question here ,which is to avoid the repetition in the Header or Master table when any update done against the Detailed table ..

I'm not sure if the "deleted and insered" tables hold a data record at the time or do they hold a set of records till the commit time .

it helped when I compared the "deleted " against the "inserted" data and by setting flag for any changes the insert to the Audit table was done.

although I'm not quite sure that is a good solution ,that I always think of using the functions will be more reliable and that was my resaon of asking about the Columns_Updated () and how far can I use it .

Thanks for your replies ..

|||

Just to clarify your requirement could you post examples of data in your Master table before and after the data has been updated, along with an example of the data that you would expect to be inserted into the syslogAuditMaster table as a result of the update?

Also if you could post the DDL statements used to create the Master and syslogAuditMaster tables then that would be extremely useful.

Thanks

Chris

|||

Hi Chris,

Thanks for your reply,

the DDL statements used to create the Master ,Detail and syslogAuditMaster are

--for the Master table

CREATE TABLE [dbo].[Master] (
[MasterID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CodeIDType] [int] NOT NULL ,
[DateStarted] [datetime] NULL ,
[DateDue] [datetime] NULL ,
[CodeIDReminderType] [int] NULL ,
[CodeIDAuditStatus] [int] NULL ,
[NoteID] [int] NULL ,
[DeletedOnDate] [datetime] NULL
) ON [PRIMARY]

--for the Detail table

CREATE TABLE [dbo].[Detail] (
[DetailID] [int] IDENTITY (1, 1) NOT NULL ,
[MasterID] [int] NOT NULL ,
[Datestarted] [datetime] NULL , [NoteID] [int] NULL,[ColA] ...[ColB]..etc

) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Detail] ADD
CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED
(
[DetailID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AuditDetail] ADD
CONSTRAINT [Master_Detail_FK1] FOREIGN KEY
(
[MasterID]
) REFERENCES [dbo].[Master] (
[MasterID]
)GO

-- for the Audit table

CREATE TABLE [dbo].[syslogAuditMaster] (
[MasterID] [int] NOT NULL,
[Name] [nvarchar] (254) NOT NULL ,
[CodeIDType] [int] NOT NULL ,
[DateStarted] [datetime] NULL ,
[DateDue] [datetime] NULL ,
[CodeIDReminderType] [int] NULL ,
[CodeIDAuditStatus] [int] NULL ,
[NoteID] [int] NULL ,
[DeletedOnDate] [datetime] NULL ,
[ChangedOnDate] [datetime] NOT NULL DEFAULT GETDATE(),
[UserName] [nvarchar] (150) NOT NULL DEFAULT suser_sname(),
[HostName] [nvarchar] (150) NOT NULL DEFAULT HOST_NAME(),
[Event] [nvarchar] (150) NOT NULL
) ON [PRIMARY]

when the user applied anychanges to the detail table through the .NET application , the store proc sp_Update which has the UPDATE statement for both the Master and Detail tables will executed,and as a result the DB trigget will be fired .

--Currently

if the field [NoteId] for [MasterID] = 3344 and [DetailID]=1122 has been changed in the Detail table ,I'll get the following in the syslogAuditMaster

[MasterID] = 3344,with the event type 'UPDATE'

--what to excpect

I should not get anything in the syslogAuditMaster ,cuz there is nothing new with the Master table to be added only if I changed anything within the Master I should get a record inserted in the AuditMaster

this is the DB trigger I'm using currently

Create Trigger trig_Audit_Upd
on [dbo].[Master]

For Update
--WITH ENCRYPTION
AS
IF EXISTS
(
SELECT 'True'
FROM deleted d inner join inserted i on i.MasterID = d.MasterID
where ltrim(rtrim(i.MasterID))+ltrim(rtrim(isnull(i.Name,'')))+ltrim(rtrim(isnull(i.CodeIDtype,'')))+ltrim(rtrim(isnull(i.DateStarted,'')))+ltrim(rtrim(isnull(i.DateDue,'')))+ltrim(rtrim(isnull(i.codeIDReminderType,'')))+ltrim(rtrim(isnull(i.CodeIDAuditStatus,'')))+ltrim(rtrim(isnull(i.NoteID,'')))+ltrim(rtrim(isnull(i.DeletedonDate,'')))
<> ltrim(rtrim(d.MasterID))+ltrim(rtrim(isnull(d.Name,'')))+ltrim(rtrim(isnull(d.CodeIDtype,'')))+ltrim(rtrim(isnull(d.DateStarted,'')))+ltrim(rtrim(isnull(d.DateDue,'')))+ltrim(rtrim(isnull(d.codeIDReminderType,'')))+ltrim(rtrim(isnull(d.CodeIDAuditStatus,'')))+ltrim(rtrim(isnull(d.NoteID,'')))+ltrim(rtrim(isnull(d.DeletedonDate,'')))
)
begin
INSERT INTO [dbo].[syslogAuditMaster](
[MasterID], [Name], [CodeIDType], [DateStarted], [DateDue], [CodeIDReminderType], [CodeIDAuditStatus], [NoteID], [DeletedOnDate], [Event])
Select del.[MasterID],del.[Name],del.[CodeIDType],del.[Datestarted],del.[Datedue],del.[CodeIDReminderType],
del.[CodeIDAuditStatus], del.[NoteID], del.[DeletedOnDate], 'UPDATE'
FROM deleted del
WHERE del.MasterID = MasterId

end;

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

hope this will help ..

Thank again

Ala'a M

|||

Right, I think I now understand where you are going with this.

Basically, even though you are issuing an UPDATE statement to update a row in the Master table, you might actually not be changing any of the values - in which case you don't want to write an audit row to the syslogAuditMaster table. You, therefore, only want to write a row to the syslogAuditMaster table if at least one of the columns values changes as a result of the update. Is this correct?

The problem with the COLUMNS_UPDATED() and UPDATED() functions is that they will report a column as being updated even if the column's value doesn't actually change as a result of the UPDATE statement. For this reason, if the solution you are using works fine then I see no reason why you shouldn't continue to use it.

Personally, I would move away from the text-based comparison that you are doing and would have explicit checks for each of the columns, see the example below. Note that there is no need for the IF EXISTS statement in this example. Also, if you update several Master rows at a time then the example below will only write audit rows for the Master rows that have actually changed.

Chris

CREATE TRIGGER trig_Audit_Upd

ON [dbo].[Master]

FOR UPDATE

--WITH ENCRYPTION

AS

INSERT INTO [dbo].[syslogAuditMaster](

[MasterID],

[Name],

[CodeIDType],

[DateStarted],

[DateDue],

[CodeIDReminderType],

[CodeIDAuditStatus],

[NoteID],

[DeletedOnDate],

[Event])

SELECT del.[MasterID],

del.[Name],

del.[CodeIDType],

del.[Datestarted],

del.[Datedue],

del.[CodeIDReminderType],

del.[CodeIDAuditStatus],

del.[NoteID],

del.[DeletedOnDate],

'UPDATE'

FROM deleted del

INNER JOIN inserted ins ON ins.MasterID = del.MasterID

WHERE ((del.[Name] <> ins.[Name])

OR (del.CodeIDType <> ins.CodeIDType)

OR ((del.[DateStarted] <> ins.[DateStarted]) OR (del.[DateStarted] IS NULL AND ins.[DateStarted] IS NOT NULL) OR (del.[DateStarted] IS NOT NULL AND ins.[DateStarted] IS NULL))

OR ((del.[DateDue] <> ins.[DateDue]) OR (del.[DateDue] IS NULL AND ins.[DateDue] IS NOT NULL) OR (del.[DateDue] IS NOT NULL AND ins.[DateDue] IS NULL))

OR ((del.[CodeIDReminderType] <> ins.[CodeIDReminderType]) OR (del.[CodeIDReminderType] IS NULL AND ins.[CodeIDReminderType] IS NOT NULL) OR (del.[CodeIDReminderType] IS NOT NULL AND ins.[CodeIDReminderType] IS NULL))

OR ((del.[CodeIDAuditStatus] <> ins.[CodeIDAuditStatus]) OR (del.[CodeIDAuditStatus] IS NULL AND ins.[CodeIDAuditStatus] IS NOT NULL) OR (del.[CodeIDAuditStatus] IS NOT NULL AND ins.[CodeIDAuditStatus] IS NULL))

OR ((del.[NoteID] <> ins.[NoteID]) OR (del.[NoteID] IS NULL AND ins.[NoteID] IS NOT NULL) OR (del.[NoteID] IS NOT NULL AND ins.[NoteID] IS NULL))

OR ((del.[DeletedOnDate] <> ins.[DeletedOnDate]) OR (del.[DeletedOnDate] IS NULL AND ins.[DeletedOnDate] IS NOT NULL) OR (del.[DeletedOnDate] IS NOT NULL AND ins.[DeletedOnDate] IS NULL)))

GO

|||

Thanks alot for giving me the chance to think loudly and exchange thoughts helped me alot ..

thanx Chris

Columns_Updated () and Update() functions

Hello all,

I'm trying to apply an audit DB trigger on a master-detail tables ,I do not need to insert record in the audit master every time an update happend in the audit detail,I tried to use columns_updated() > 0 but it didn't work the way I axpected it to be ..it stopped inserting into the audit master even if an update was applied against the master table ...any help please and I use the Update() function ? is their any major difference?

your help is appreciated

Thanks

Alaa M

COLUMNS_UPDATED() returns a value as a VARBINARY datatype, so for your comparison to work you need to convert the value to an INT first, like this:

CAST(COLUMNS_UPDATED() AS INT)

Chris

|||

Thanks CHRIS

Actually it didn't work ..

or may be i'm missing something here ,anyway this is the code I'm using if you or anyone can point the wrong thing that I'm doing here will be great.

this is the trigger for the master table ,and I only need to insert the changes applied against this table into Audit master no matter how many updates applied to the detail table.

ALTER Trigger trig_Audit_Upd
on [dbo].[Master]

For Update
--WITH ENCRYPTION
AS
IF EXISTS
(
SELECT 'True'
FROM deleted d
LEFT JOIN [Master] A
ON d.MasterID = A.MasterID
)

if (CAST(COLUMNS_UPDATED() AS INT ) & 255) > 0

begin
INSERT INTO [dbo].[syslogAuditMaster](
[MasterID], [Name], [CodeIDType], [DateStarted], [DateDue], [CodeIDReminderType],
[CodeIDAuditStatus], [NoteID], [DeletedOnDate], [Event])
Select del.[MasterID],del.[Name],del.[CodeIDType],del.[Datestarted],del.[Datedue],del.[CodeIDReminderType],
del.[CodeIDAuditStatus], del.[NoteID], del.[DeletedOnDate], 'UPDATE'
FROM deleted del
WHERE del.MasterID = MastertId

end;

thank

|||

I get the feeling that you might be trying to over-complicate things. I can't see the point of explicitly checking for updated columns using the COLUMNS_UPDATED() function - the fact that at least one row has been updated is implied by the existence of rows in the 'deleted' virtual table.

I'm also confused by the WHERE clause in the INSERT statement:

WHERE del.MasterID = MastertId

Does the MastertId column exist in the 'deleted' virtual table? If so does MastertId ever equal del.MasterID?

Would the re-worked example below work for you?

Chris

ALTER TRIGGER trig_Audit_Upd

ON [dbo].[Master]

FOR UPDATE

--WITH ENCRYPTION

AS

IF EXISTS (SELECT 1 FROM deleted)

BEGIN

INSERT INTO [dbo].[syslogAuditMaster]

([MasterID],

[Name],

[CodeIDType],

[DateStarted],

[DateDue],

[CodeIDReminderType],

[CodeIDAuditStatus],

[NoteID],

[DeletedOnDate],

[Event])

SELECT del.[MasterID],

del.[Name],

del.[CodeIDType],

del.[Datestarted],

del.[Datedue],

del.[CodeIDReminderType],

del.[CodeIDAuditStatus],

del.[NoteID],

del.[DeletedOnDate],

'UPDATE'

FROM deleted del

END

|||

Thnaks for your replay,

I think you are missing the whole point of my question here ,which is to avoid the repetition in the Header or Master table when any update done against the Detailed table ..

I'm not sure if the "deleted and insered" tables hold a data record at the time or do they hold a set of records till the commit time .

it helped when I compared the "deleted " against the "inserted" data and by setting flag for any changes the insert to the Audit table was done.

although I'm not quite sure that is a good solution ,that I always think of using the functions will be more reliable and that was my resaon of asking about the Columns_Updated () and how far can I use it .

Thanks for your replies ..

|||

Just to clarify your requirement could you post examples of data in your Master table before and after the data has been updated, along with an example of the data that you would expect to be inserted into the syslogAuditMaster table as a result of the update?

Also if you could post the DDL statements used to create the Master and syslogAuditMaster tables then that would be extremely useful.

Thanks

Chris

|||

Hi Chris,

Thanks for your reply,

the DDL statements used to create the Master ,Detail and syslogAuditMaster are

--for the Master table

CREATE TABLE [dbo].[Master] (
[MasterID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CodeIDType] [int] NOT NULL ,
[DateStarted] [datetime] NULL ,
[DateDue] [datetime] NULL ,
[CodeIDReminderType] [int] NULL ,
[CodeIDAuditStatus] [int] NULL ,
[NoteID] [int] NULL ,
[DeletedOnDate] [datetime] NULL
) ON [PRIMARY]

--for the Detail table

CREATE TABLE [dbo].[Detail] (
[DetailID] [int] IDENTITY (1, 1) NOT NULL ,
[MasterID] [int] NOT NULL ,
[Datestarted] [datetime] NULL , [NoteID] [int] NULL,[ColA] ...[ColB]..etc

) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Detail] ADD
CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED
(
[DetailID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AuditDetail] ADD
CONSTRAINT [Master_Detail_FK1] FOREIGN KEY
(
[MasterID]
) REFERENCES [dbo].[Master] (
[MasterID]
)GO

-- for the Audit table

CREATE TABLE [dbo].[syslogAuditMaster] (
[MasterID] [int] NOT NULL,
[Name] [nvarchar] (254) NOT NULL ,
[CodeIDType] [int] NOT NULL ,
[DateStarted] [datetime] NULL ,
[DateDue] [datetime] NULL ,
[CodeIDReminderType] [int] NULL ,
[CodeIDAuditStatus] [int] NULL ,
[NoteID] [int] NULL ,
[DeletedOnDate] [datetime] NULL ,
[ChangedOnDate] [datetime] NOT NULL DEFAULT GETDATE(),
[UserName] [nvarchar] (150) NOT NULL DEFAULT suser_sname(),
[HostName] [nvarchar] (150) NOT NULL DEFAULT HOST_NAME(),
[Event] [nvarchar] (150) NOT NULL
) ON [PRIMARY]

when the user applied anychanges to the detail table through the .NET application , the store proc sp_Update which has the UPDATE statement for both the Master and Detail tables will executed,and as a result the DB trigget will be fired .

--Currently

if the field [NoteId] for [MasterID] = 3344 and [DetailID]=1122 has been changed in the Detail table ,I'll get the following in the syslogAuditMaster

[MasterID] = 3344,with the event type 'UPDATE'

--what to excpect

I should not get anything in the syslogAuditMaster ,cuz there is nothing new with the Master table to be added only if I changed anything within the Master I should get a record inserted in the AuditMaster

this is the DB trigger I'm using currently

Create Trigger trig_Audit_Upd
on [dbo].[Master]

For Update
--WITH ENCRYPTION
AS
IF EXISTS
(
SELECT 'True'
FROM deleted d inner join inserted i on i.MasterID = d.MasterID
where ltrim(rtrim(i.MasterID))+ltrim(rtrim(isnull(i.Name,'')))+ltrim(rtrim(isnull(i.CodeIDtype,'')))+ltrim(rtrim(isnull(i.DateStarted,'')))+ltrim(rtrim(isnull(i.DateDue,'')))+ltrim(rtrim(isnull(i.codeIDReminderType,'')))+ltrim(rtrim(isnull(i.CodeIDAuditStatus,'')))+ltrim(rtrim(isnull(i.NoteID,'')))+ltrim(rtrim(isnull(i.DeletedonDate,'')))
<> ltrim(rtrim(d.MasterID))+ltrim(rtrim(isnull(d.Name,'')))+ltrim(rtrim(isnull(d.CodeIDtype,'')))+ltrim(rtrim(isnull(d.DateStarted,'')))+ltrim(rtrim(isnull(d.DateDue,'')))+ltrim(rtrim(isnull(d.codeIDReminderType,'')))+ltrim(rtrim(isnull(d.CodeIDAuditStatus,'')))+ltrim(rtrim(isnull(d.NoteID,'')))+ltrim(rtrim(isnull(d.DeletedonDate,'')))
)
begin
INSERT INTO [dbo].[syslogAuditMaster](
[MasterID], [Name], [CodeIDType], [DateStarted], [DateDue], [CodeIDReminderType], [CodeIDAuditStatus], [NoteID], [DeletedOnDate], [Event])
Select del.[MasterID],del.[Name],del.[CodeIDType],del.[Datestarted],del.[Datedue],del.[CodeIDReminderType],
del.[CodeIDAuditStatus], del.[NoteID], del.[DeletedOnDate], 'UPDATE'
FROM deleted del
WHERE del.MasterID = MasterId

end;

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

hope this will help ..

Thank again

Ala'a M

|||

Right, I think I now understand where you are going with this.

Basically, even though you are issuing an UPDATE statement to update a row in the Master table, you might actually not be changing any of the values - in which case you don't want to write an audit row to the syslogAuditMaster table. You, therefore, only want to write a row to the syslogAuditMaster table if at least one of the columns values changes as a result of the update. Is this correct?

The problem with the COLUMNS_UPDATED() and UPDATED() functions is that they will report a column as being updated even if the column's value doesn't actually change as a result of the UPDATE statement. For this reason, if the solution you are using works fine then I see no reason why you shouldn't continue to use it.

Personally, I would move away from the text-based comparison that you are doing and would have explicit checks for each of the columns, see the example below. Note that there is no need for the IF EXISTS statement in this example. Also, if you update several Master rows at a time then the example below will only write audit rows for the Master rows that have actually changed.

Chris

CREATE TRIGGER trig_Audit_Upd

ON [dbo].[Master]

FOR UPDATE

--WITH ENCRYPTION

AS

INSERT INTO [dbo].[syslogAuditMaster](

[MasterID],

[Name],

[CodeIDType],

[DateStarted],

[DateDue],

[CodeIDReminderType],

[CodeIDAuditStatus],

[NoteID],

[DeletedOnDate],

[Event])

SELECT del.[MasterID],

del.[Name],

del.[CodeIDType],

del.[Datestarted],

del.[Datedue],

del.[CodeIDReminderType],

del.[CodeIDAuditStatus],

del.[NoteID],

del.[DeletedOnDate],

'UPDATE'

FROM deleted del

INNER JOIN inserted ins ON ins.MasterID = del.MasterID

WHERE ((del.[Name] <> ins.[Name])

OR (del.CodeIDType <> ins.CodeIDType)

OR ((del.[DateStarted] <> ins.[DateStarted]) OR (del.[DateStarted] IS NULL AND ins.[DateStarted] IS NOT NULL) OR (del.[DateStarted] IS NOT NULL AND ins.[DateStarted] IS NULL))

OR ((del.[DateDue] <> ins.[DateDue]) OR (del.[DateDue] IS NULL AND ins.[DateDue] IS NOT NULL) OR (del.[DateDue] IS NOT NULL AND ins.[DateDue] IS NULL))

OR ((del.[CodeIDReminderType] <> ins.[CodeIDReminderType]) OR (del.[CodeIDReminderType] IS NULL AND ins.[CodeIDReminderType] IS NOT NULL) OR (del.[CodeIDReminderType] IS NOT NULL AND ins.[CodeIDReminderType] IS NULL))

OR ((del.[CodeIDAuditStatus] <> ins.[CodeIDAuditStatus]) OR (del.[CodeIDAuditStatus] IS NULL AND ins.[CodeIDAuditStatus] IS NOT NULL) OR (del.[CodeIDAuditStatus] IS NOT NULL AND ins.[CodeIDAuditStatus] IS NULL))

OR ((del.[NoteID] <> ins.[NoteID]) OR (del.[NoteID] IS NULL AND ins.[NoteID] IS NOT NULL) OR (del.[NoteID] IS NOT NULL AND ins.[NoteID] IS NULL))

OR ((del.[DeletedOnDate] <> ins.[DeletedOnDate]) OR (del.[DeletedOnDate] IS NULL AND ins.[DeletedOnDate] IS NOT NULL) OR (del.[DeletedOnDate] IS NOT NULL AND ins.[DeletedOnDate] IS NULL)))

GO

|||

Thanks alot for giving me the chance to think loudly and exchange thoughts helped me alot ..

thanx Chris

Column name in functions

Hi,

Can we use a parameter that is a column name in a function ?

Here's my function :

CREATE FUNCTION dbo.fn_counting (@.colnumber varchar(2),@.number
varchar(1))
RETURNS int AS

BEGIN
DECLARE @.column varchar(2)
DECLARE @.ColTotal int

SET @.column = 'R' +@.colnumber
(This next line WORKS !!!)
SELECT @.ColTotal = COUNT(*) FROM dbo.Tbl_Answers WHERE R3 = @.number
(This next one DOESN'T WORK - because of the ' it is treated as a
string)
SELECT @.ColTotal = 'COUNT(*) FROM dbo.Tbl_Answers WHERE ' +@.column +
'=' +@.number

RETURN @.ColTotal
END

Thank youNo. But with good design you should never need to. Why wouldn't you know the
column name at design time?

--
David Portas
SQL Server MVP
--|||Because my data table is filled with 40 answers (columns) from a survey
(4,3,2,1) for different group. Then the user will tell me which group,
year, etc he needs the data for and I need to count the number of
4,3,2,1 for that groups for every answer (column). Not really clear !!!

But obviously you are right I will rethink my approach

Thank you for the answer|||For example, try this:

CREATE TABLE Survey (group_no INTEGER NOT NULL REFERENCES Groups (group_no),
year_no INTEGER NOT NULL, answer_no INTEGER NOT NULL CHECK (answer_no
BETWEEN 1 AND 40), response INTEGER NOT NULL CHECK (response BETWEEN 1 AND
4), PRIMARY KEY (group_no, year_no, answer_no))

SELECT response, COUNT(*)
FROM Survey
WHERE group_no = @.group_no
AND year_no = @.year_no
GROUP BY response

--
David Portas
SQL Server MVP
--|||Patrik (patrik.maheux@.umontreal.ca) writes:
> Because my data table is filled with 40 answers (columns) from a survey
> (4,3,2,1) for different group. Then the user will tell me which group,
> year, etc he needs the data for and I need to count the number of
> 4,3,2,1 for that groups for every answer (column). Not really clear !!!
> But obviously you are right I will rethink my approach

You should most certainly make the columns into rows instead. The way
databases work, it's a lot easier to handle repeating groups if they
are rows instead of columns.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I think cannot make my columns into rows because the data comes like
that from an optical reader in a text format that I import.Let me be
clearer :

My main table is autokey-year-personcode-Answer1 thru 40 (43 columns).
I can have 125 respondants(rows) for one code thus the autoid
DATA looks like: 2000-101-4-3-3-4-2-1-3-4-2-3-2...thousands of lines
like these

Then I need to count the number of 4-3-2 and 1 for every personcode.

I will try the proposed solution and let the group know if it works

Thank you again for the help

Erland Sommarskog wrote:
> Patrik (patrik.maheux@.umontreal.ca) writes:
> > Because my data table is filled with 40 answers (columns) from a survey
> > (4,3,2,1) for different group. Then the user will tell me which group,
> > year, etc he needs the data for and I need to count the number of
> > 4,3,2,1 for that groups for every answer (column). Not really clear !!!
> > But obviously you are right I will rethink my approach
> You should most certainly make the columns into rows instead. The way
> databases work, it's a lot easier to handle repeating groups if they
> are rows instead of columns.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||The format the data is supplied in should not dictate the database design.
Design the database correctly and then develop a process to load the data
into that database from its external source.

--
David Portas
SQL Server MVP
--|||Patrik (patrik.maheux@.umontreal.ca) writes:
> I think cannot make my columns into rows because the data comes like
> that from an optical reader in a text format that I import.Let me be
> clearer :
> My main table is autokey-year-personcode-Answer1 thru 40 (43 columns).
> I can have 125 respondants(rows) for one code thus the autoid
> DATA looks like: 2000-101-4-3-3-4-2-1-3-4-2-3-2...thousands of lines
> like these
> Then I need to count the number of 4-3-2 and 1 for every personcode.
> I will try the proposed solution and let the group know if it works

As David said, don't let the input format dictate your data model. That
format will give you a headache somewhere on the line, and I'm telling
you the earlier you handle it in the process, the less headache you will
get.

For this case, I would unpack the string with a list-to-table function,
see http://www.sommarskog.se/arrays-in-...ist-of-integers
for such a function. For your case you would have handle listpos 1, 2
and 3 individually, and then the answers would be everything above 4.
You could use the function as is, but you could also adapt it so it
directly unpacks into the format you need.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Column Formulas

Looking for a decent source that has some examples of column formulas as
well as a list of column formula functions that can be used and how to use
them.

Hard time finding something online. Any suggestions or references would be
greatly appreciated.

Fairly new to this.

ThanksHi

Not sure exactly what you mean by column formulas! You may want to look up
computed columns in Books online.

John

"Scott Castillo" <scasti2@.cox.net> wrote in message
news:fLLZb.7255$C21.4494@.fed1read07...
> Looking for a decent source that has some examples of column formulas as
> well as a list of column formula functions that can be used and how to use
> them.
> Hard time finding something online. Any suggestions or references would
be
> greatly appreciated.
> Fairly new to this.
> Thanks

Collection cannot be modified when TextMode has been set (Microsoft.SqlServer.Smo)

Hi -

I'm trying to generate a script for User Defined Functions and I get this error:

Generate Script Progress

- Determining objects in database 'MyDBName' that will be scripted. (Success)
Messages
* Prefetch objects failed for Database 'MyDBName' . (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Prefetch+objects+Database&LinkId=20476

ADDITIONAL INFORMATION:
Collection cannot be modified when TextMode has been set (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.CollectionCannotBeModified&LinkId=20476

I'm using SQL 2005 - with SP1.

Any suggestion what am I missing....

Rakesh

Are you able to generate scripts for these objects using Management Studio? If so, then the problem is with your code, and it would be helpful if you could post the code to see your logic. If not, then there's a problem with your server.

Collection cannot be modified when TextMode has been set (Microsoft.SqlServer.Smo)

Hi -

I'm trying to generate a script for User Defined Functions and I get this error:

Generate Script Progress

- Determining objects in database 'MyDBName' that will be scripted. (Success)
Messages
* Prefetch objects failed for Database 'MyDBName' . (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Prefetch+objects+Database&LinkId=20476

ADDITIONAL INFORMATION:
Collection cannot be modified when TextMode has been set (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.CollectionCannotBeModified&LinkId=20476

I'm using SQL 2005 - with SP1.

Any suggestion what am I missing....

Rakesh

Are you able to generate scripts for these objects using Management Studio? If so, then the problem is with your code, and it would be helpful if you could post the code to see your logic. If not, then there's a problem with your server.