Cant reference logical table within cursor

Does anyone know if MS SQL can't process a "select * from deleted" from within a cursor that's embedded in a trigger? I'm getting an error when I run this...

DECLARE check_contact_fields CURSOR
FOR SELECT field_id, column_name FROM contacts_fields
OPEN check_contact_fields
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
set @.SQL = 'select ' + @.column_name + ' into ##DeletedData from deleted'
exec sp_executesql @.SQL
set @.SQL = 'select ' + @.column_name + ' into ##InsertedData from inserted'
exec sp_executesql @.SQL
if (select * from ##DeletedData) <> (select * from ##InsertedData)
select * from ##InsertedData
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
END
CLOSE check_contact_fields
DEALLOCATE check_contact_fields

drop table ##DeletedData
drop table ##InsertedData

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'deleted'.

TIAThe error is returned correctly, because logical table deleted is not known within the execution context of dynamic SQL.|||That's what I thought. So, how can I accomplish what I'm trying to do? I can't think of another way to do it without dynamic SQL.

Thanks again|||Maybe you can tell us what you are trying to do...I can't figure it out...

AND a cursor in a trigger is never a good idea....|||Brett -

I need to record the old and new data for a set of contact related fields when a contact record is updated. The set of fields that need to be monitored are stored in the contacts_fields table (because the contact fields are dynamic). So, my thinking was that I could create an update trigger to check those columns in the contacts_fields table that are in the deleted row against the inserted row. If any of the column_name values changed, I would then only record that *specific* column that was modified.

Thanks|||Why bother...just update the whole row...what difference does it make...

What's the DDL of the table look like?

What do you mean by dynamic?|||DDL ("master" audit table):
/****** Object: Table [dbo].[contacts_history] Script Date: 8/4/2004 1:41:36 PM ******/
CREATE TABLE [contacts_history] (
[contact_history_id] [int] IDENTITY (1, 1) NOT NULL ,
[contact_id] [int] NULL ,
[who_created] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[who_modified] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fields_lastUpdated] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_contacts_history] PRIMARY KEY CLUSTERED
(
[contact_history_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

DDL (data capture table):
/****** Object: Table [dbo].[contacts_history_values] Script Date: 8/4/2004 1:41:10 PM ******/
CREATE TABLE [contacts_history_values] (
[contacts_history_value_ID] [int] IDENTITY (1, 1) NOT NULL ,
[contact_history_id] [int] NULL ,
[field_id] [int] NULL ,
[old_data] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[current_data] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_contacts_history_values] PRIMARY KEY CLUSTERED
(
[contacts_history_value_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

Dynamic in the sense that the fields in the "contacts" table can change on the fly (there's a field management area).

Thanks|||holy crap...

You basically have "vertical" rows....

Is this a third party product, did you build this or did you inherit this

What does the concat table DDL look like?

And what do you mean the "fields" (you mean columns right) can change on the fly.

Do you mean the data or the table structure?

What does "if any of the column_name values have changed" mean?

I'm sorry...I just can't see it yet...|||Yes, the columns on the contacts table can change on the fly (but its PK, contact_id, would always exist).

I mean the table structure.

What does "if any of the column_name values have changed" mean?
--> I'm not sure if you saw what the check_contact_fields cursor looks like, but what I mean by this is that since I store all the columns that need to be audited in the contacts_fields table I could "loop" over these columns (hence the cursor) and check each column_name value (these are really just the column names) against the deleted and inserted tables. If the data for any of these columns changed I would then write the changes (along with the old data) to the contacts_history_values table.

Hope this makes sense.|||ok...I understand...not that I want to.....

Is the id 1 per "table"

Are you making up and configuring "tables" for end users through a web application?

I'd be very interested in the reason that your doing this...

As for your solution...I gotta ponder this one.....

Also because there is no ordinal position in your table, and the order of rows of data in a database is meangless...how do you know what column name has changed?

It doesn't work that way.

You column name is essentially a key in this process...actually a composite key along with contactId

So actually trying to udate the key...which to me is a bad thing...

You should go for a logical update...which mean you do no updates but rather a DELETE first, then an INSERT...

That should be rather straight forward in a trigger...|||Sorry, but I don't follow you.

Have you looked at the DDL's I posted? There's a column called "field_id" (in the contacts_history_values table) that's a FK for the contacts_fields table.
This allows the app to keep track of the column that was modified.

I think I'm more confused now... =\|||Ok good...

I think I got confused...

You want to audit the change correct?

Just do a join between inserted and deleted on contactId and fieldid and insert that results to your audit tables from inside a trigger

What happens for a new "field" or a field that has been "deleted"

You still haven't told me who built this...|||I think I found the right design but I'm having issues executing it.

DECLARE check_contact_fields CURSOR LOCAL
FOR SELECT field_id, column_name FROM contacts_fields
OPEN check_contact_fields
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
if (select '+@.column_name+' from inserted) <> (select '+@.column_name+' from deleted)
print @.column_name + ' boo'
else if (select '+@.column_name+' from inserted) = (select '+@.column_name+' from deleted)
print 'identical values'
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
END
CLOSE check_contact_fields
DEALLOCATE check_contact_fields

This always returns "identical values" even though some of the data has changed.

Any ideas?|||What table is the Trigger on?

I don't think you want or need the cursor|||I Think you want this...can't test without the other tables DDL

CREATE TRIGGER Contact_Fields_TR1 ON Contact_Fields
AS
BEGIN
INSERT INTO contacts_history_values(contact_history_id, field_id, old_data, current_data)
SELECT i.ContactId, i.field_Id, o.column_name, i.column_name
FROM inserted i INNER JOIN deleted o
WHERE i.Contact_Id = o.Contact_Id AND i.Field_Id = o.Field_ID
END

Can't get new LUN/Logical Drive visible from within SQL Server on cluster.

We have had a working SQL Server 2000 SP3 (default instance) on two node MS
Windows 2000 Advanced Server with MS Cluster servcie working for over a year
now.
The shared Q: and E: drives are actually two seperate LUNS from a SAN
connected via Host Bus Adapters..
Recently, needed another logical drive. Added another LUN from the SAN.
Configured it as basic disk, drive letter F: It show up in Disk Manager
exactly like the pre-existing E: drive in terms of attribute values.
It is a working a normal drive on the primary node.
Using the cluster administrator, it was added as a physical disk resource
and added to the iManage SQL Cluster Group, the same as the E: drive.
It shows up as online and owned by the primary node where the SQL Server is
active.
However, it does not show up with the Enterprise Manage for the SQL Server.
Only E: is showing. Have even rebooted the nodes for the cluster.
The logical drive F: is available for all other uses, but it cannot be seen
by the SQL Server.
Scott Lord
Willkie Farr & Gallagher LLP
New York, New York
slord@.willkie.com
you have to add new drive as a SQL Server Dependency in cluster administrator. once you added into sqlserver dependency then enterprise mangager see the drive.
Regards
Ram
Systems SQL DBA|||I for get tell one more step . you have to add resource in Cluster administrator.
Regards
Ram

Quote:

Originally posted by yvr4
you have to add new drive as a SQL Server Dependency in cluster administrator. once you added into sqlserver dependency then enterprise mangager see the drive.
Regards
Ram
Systems SQL DBA

|||Have you made it a dependency of the SQL Server group?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Scott Lord" <nomail@.nomail.com> wrote in message
news:ehHkGQKCFHA.2404@.TK2MSFTNGP15.phx.gbl...
We have had a working SQL Server 2000 SP3 (default instance) on two node MS
Windows 2000 Advanced Server with MS Cluster servcie working for over a year
now.
The shared Q: and E: drives are actually two seperate LUNS from a SAN
connected via Host Bus Adapters..
Recently, needed another logical drive. Added another LUN from the SAN.
Configured it as basic disk, drive letter F: It show up in Disk Manager
exactly like the pre-existing E: drive in terms of attribute values.
It is a working a normal drive on the primary node.
Using the cluster administrator, it was added as a physical disk resource
and added to the iManage SQL Cluster Group, the same as the E: drive.
It shows up as online and owned by the primary node where the SQL Server is
active.
However, it does not show up with the Enterprise Manage for the SQL Server.
Only E: is showing. Have even rebooted the nodes for the cluster.
The logical drive F: is available for all other uses, but it cannot be seen
by the SQL Server.
Scott Lord
Willkie Farr & Gallagher LLP
New York, New York
slord@.willkie.com
|||Thanks to Tom Moreau and yvr4 for their answers. I am pretty sure they both
identified the problem, I haven't been able to successfully test their
solution.
I had not added it as a dependency for the SQL Server. I only had it added
it to the SQL Cluster Group. I just tried adding it as a dependency to the
SQL Server.
FYI: The disk resource F: is online to the primary node and the SQL Server
is and online to the primary node.
In the cluster administrator, simply pulling up the properties to the SQL
Server and going to the Dependencies tab showed the F: drive on the left as
an available resource to be added as a dependency. I highlighted the F:
drive and tried to add it. I got a slightly ambiguous error message which I
will also type the text from the error message box.
Message box title: Cluster Administrator
Yellow triangle with exclamation point icon.
An error occurred attempting to add 'Disk F:' as a dependency of 'SQL
Server':
The operation could not be completed because the cluster resource is online.
Error ID: 5019 (0000139b).
This is a production environment: I am concerned about two things. Which
resource, the F: drive or the SQL Server is the error message meaning when
it say 'because the cluster resource is online' I know that I would like
for it to mean the disk drive, but I am modifying the SQL Server dependency
tree, so it is possible it means the SQL Server. I can't take that offline
except during a maintenance window. Also, if I simply take the disk drive
F: resource offline in the cluster administrator window in order to add it
to the SQL Server dependency tree, will it cause a fail-over of the SQL
Server since the newly defined dependency, F: disk drive resource, is not
online. That is what the dependency tree is for, isn't it.
I hope the answer is simply, take the disk drive F: resource offline in
cluster administrator, add it as a dependency to the SQL Server, and it will
either come online itself or I can manually bring it online, without the SQL
Server failing over.
Alternatively, I can accept needing to take the SQL Server offline in the
cluster administrator, having the disk drive F: resource online, adding it
as a dependency to the SQL Server and bringing the SQL Server online.
Sorry about these additional questions, but I thought the error message was
a little ambiguous, and I am concerned about adding an offline resource as a
dependency if it will cause a fail-over in our production environment. We
do not have a test cluster to try this on.
Scott Lord
Willkie Farr & Gallagher LLP
New York, New York
slord@.willkie.com
"yvr4" <yvr4.1jt3a0@.mail.mcse.ms> wrote in message
news:yvr4.1jt3a0@.mail.mcse.ms...
> you have to add new drive as a SQL Server Dependency in cluster
> administrator. once you added into sqlserver dependency then enterprise
> mangager see the drive.
> Regards
> Ram
> Systems SQL DBA
>
> --
> yvr4
> Posted via http://www.mcse.ms
> View this thread: http://www.mcse.ms/message1393040.html
>
|||That error message is correct. You must take the SQL Server resource
offline to change its dependencies.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Scott Lord" <nomail@.nomail.com> wrote in message
news:e97ziSUCFHA.2384@.TK2MSFTNGP14.phx.gbl...
> Thanks to Tom Moreau and yvr4 for their answers. I am pretty sure they
both
> identified the problem, I haven't been able to successfully test their
> solution.
> I had not added it as a dependency for the SQL Server. I only had it
added
> it to the SQL Cluster Group. I just tried adding it as a dependency to
the
> SQL Server.
> FYI: The disk resource F: is online to the primary node and the SQL
Server
> is and online to the primary node.
> In the cluster administrator, simply pulling up the properties to the SQL
> Server and going to the Dependencies tab showed the F: drive on the left
as
> an available resource to be added as a dependency. I highlighted the F:
> drive and tried to add it. I got a slightly ambiguous error message which
I
> will also type the text from the error message box.
> --
> Message box title: Cluster Administrator
> Yellow triangle with exclamation point icon.
> An error occurred attempting to add 'Disk F:' as a dependency of 'SQL
> Server':
> The operation could not be completed because the cluster resource is
online.
> Error ID: 5019 (0000139b).
> --
> This is a production environment: I am concerned about two things. Which
> resource, the F: drive or the SQL Server is the error message meaning when
> it say 'because the cluster resource is online' I know that I would like
> for it to mean the disk drive, but I am modifying the SQL Server
dependency
> tree, so it is possible it means the SQL Server. I can't take that
offline
> except during a maintenance window. Also, if I simply take the disk drive
> F: resource offline in the cluster administrator window in order to add it
> to the SQL Server dependency tree, will it cause a fail-over of the SQL
> Server since the newly defined dependency, F: disk drive resource, is not
> online. That is what the dependency tree is for, isn't it.
> I hope the answer is simply, take the disk drive F: resource offline in
> cluster administrator, add it as a dependency to the SQL Server, and it
will
> either come online itself or I can manually bring it online, without the
SQL
> Server failing over.
> Alternatively, I can accept needing to take the SQL Server offline in the
> cluster administrator, having the disk drive F: resource online, adding it
> as a dependency to the SQL Server and bringing the SQL Server online.
> Sorry about these additional questions, but I thought the error message
was
> a little ambiguous, and I am concerned about adding an offline resource as
a
> dependency if it will cause a fail-over in our production environment. We
> do not have a test cluster to try this on.
> Scott Lord
> Willkie Farr & Gallagher LLP
> New York, New York
> slord@.willkie.com
> "yvr4" <yvr4.1jt3a0@.mail.mcse.ms> wrote in message
> news:yvr4.1jt3a0@.mail.mcse.ms...
>
|||Thank you Geoff.
I will schedule the change to the dependency tree for SQL Server to occur
during a maintenance window.
Scott
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:%23mHvubUCFHA.4004@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> That error message is correct. You must take the SQL Server resource
> offline to change its dependencies.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Scott Lord" <nomail@.nomail.com> wrote in message
> news:e97ziSUCFHA.2384@.TK2MSFTNGP14.phx.gbl...
> both
> added
> the
> Server
SQL[vbcol=seagreen]
> as
which[vbcol=seagreen]
> I
> online.
Which[vbcol=seagreen]
when[vbcol=seagreen]
like[vbcol=seagreen]
> dependency
> offline
drive[vbcol=seagreen]
it[vbcol=seagreen]
not[vbcol=seagreen]
> will
> SQL
the[vbcol=seagreen]
it[vbcol=seagreen]
> was
as[vbcol=seagreen]
> a
We[vbcol=seagreen]
enterprise
>