Changing connections (Does't work with "Script Task"...)

Hi,

I've a simple package which basically copies a few record from a Source connection to a Destination connection.
This package must run ten times, every time with a different Source and Destination.

I dont want to duplicate the tasks inside the package and set the right connections at design time. I'm looking for a way to parametrize the connections.

In DTS2000 I could use an "Activex Script" to access the Package's Connections and change them at design time. The new "Script Task" does not allow access to others task, so it can't be done anymore. (There's is still the "Activex Script" task on my toolbox, but i get errors trying to place it on the design surface).

Package Configurations also does not seems the right way: to use multiple configurations I should invoke the package from the command line multiple times with different command line parameters, while I wuold like to have everything in one place (ie 1 package file instead of 1 batch file, 1 package file and 10 package configurations files).

Regards,
CorradoYou are no longer allowed to access task and package properties at runtime and change things. Part of the rational is that it makes for potentially unstable and unpredicatble packages. So you know have a structured way of doing this through configurations and expressions.

Expressions can be set on connection, task and some component properties. Most UI's expose connections, or they are also available in the VS properties grid.

For example you could use an Exec SQL Task to get a recordset of connection information. You could then use the For Each Loop container to crack this, and for each iteration assign the value(s) to a variable. You can then use the variable(s) within an expression on the connection property(s).

Think expressions instead of ActiveX Script Tasks for glue code.

Changing column values on table insert trigger

Hello,

i have a table and if a record is inserted i will test a numeric value
in this table. If the this value is greather as 1 million, than an
status column should be changed from 'A' to 'B'.

Yes and sorry, this is a newbie question.

On Oracle this work simple:

create trigger myTrigger on tableX
as
begin
if :old.x 100000 then
:new.y:='B'
end if;
end;

Thanks
MaikYou can look up SQL Server Books Online to learn how to write a trigger in
t-SQL. You'll need to utilize the inserted/deleted virtual tables to
accomodate multi-row inserts. The update statement will be something along
the lines of:

UPDATE tableX
SET y = B
WHERE EXISTS ( SELECT *
FROM inserted i
WHERE i.key_col = tableX.key_col
AND inserted.x 1000000 ) ;

Here key_col is any column or set of columns that can uniquely identify a
row in tableX.

--
Anith|||Hi,

Quote:

Originally Posted by

i have a table and if a record is inserted i will test a numeric value
in this table. If the this value is greather as 1 million, than an
status column should be changed from 'A' to 'B'.
>
Yes and sorry, this is a newbie question.
>
On Oracle this work simple:
>
create trigger myTrigger on tableX
as
begin
if :old.x 100000 then
:new.y:='B'
end if;
end;

MS SQL Server doesn't have "before action" triggers, so you
have to update the table and use the INSERTED virtual table
to know what records were inserted into the table.

--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.comsql

Changing a record after comparing one database table to another

I'm using SQL Server 2005 Express.

In my main database table I have many fields but the two following fields are my main concern.

1) email_address

2) unsubscribe

In my secondary database table I have one record only.

1) email_address

What I want to accomplish... I want to compare the email_address of the secondary database table to the email_address of the main database table and if it exist, change the value of the unsubscribe field. (or if I can't do that, then delete the record within the main database table completely.)

I'd really appreciate any help I can get.

Thanks,

Bill

Something like this should work for you.

Code Snippet

UPDATE MyMainTable
SET UnSubscribe = {Put Your Value Here}

FROM MyMainTable m
JOIN MySecondaryTable s
ON m.eMail_Address = s.eMail_Address

If there is a match, [UnSubscribe] is changed, if there is NO match, nothing happens. This will work with multiple rows in the secondary table.

|||

I'll try this tonight. Thanks a lot Arnie.

Bill

changing a field''s value when user updates data

Hello,
I am working on a project that involves one part where a field's value needs to be changed when the user updates the record. Here is the situation in detail:
There is an InputData table where the user enters new records or changes existing records. There is a field called "calculated" in this table which has a default value of 'no'. A stored procedure runs math calculations on all the InputData records where the calculated field = 'no'. At the end of this stored procedure, it sets the calculated field = 'yes'. When new records are added by the user their "calculated" field value is 'no' by default so that the next time the stored procedure is executed, it only runs the math calculations on the new records. The problem is, if a user changes an existing record, the "calculated" field needs to be changed from 'yes' to 'no' so that the stored procedure recalculates the math for the modified record. How do I change the value from 'yes' to 'no' on records that the user modifies?
Thanks.

For changing the field value from ‘Yes’ no ‘No’ you can use the trigger (for update).

|||

Mani is correct. Here is an example of the update trigger.

e.g.

Code Snippet

create trigger _tr on InputData

for update

as

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

from InputData tb join Inserted i on tb.[pk] = i.[pk]

where tb.calculated='yes'

go

|||

Hi!

You can directly include "Calculated ='no'" statement within the your update command itself only, based on the condition only. otherwise all the records will get updated.

eg:

Update InsertData
Set
Column1 = value1,
Column2 = value2,
...,
Calculated ='no'
WHERE <Condition>

I hope it'll solve your problem and please let me know if I'm wrong.

Thanks & Regards,
Kiran.Y

|||I have some questions about using a trigger before I attempt to implement one...

1. Will the code snippet, using the Inserted table, work in SQL version 8?

2. Which database holds the table called Inserted? Is it the one I'm working in or the tempdb or one of the other system databases?

3. Do I store the trigger as a stored procedure?

4. My project consists of SQL tables with an MS Access front end. When the user updates the record via MS Access will the stored procedure containing the trigger automatically run? (This question assumes the trigger code will be stored as a stored procedure...if this is wrong, please do correct this assumption).

5. What does if @.@.rowcount = 0 return; do?

Thanks in advance.|||

1. Yes. Sql2k does support trigger.

2. Inserted (or deleted) table is a special memory-resident table that can only be accessed in a trigger.

3. Trigger is just another object in sqlserver, similar to stored procedure.

4. Yes. Trigger is an event based procedure. It's tied to an event of the table (insert/update/delete).

5. This line tells the system to go ahead and exit and skip the rest of the code within the trigger because there is no row affected by the event.

http://msdn2.microsoft.com/en-us/library/aa258254(SQL.80).aspx

|||Your reply has helped to clarify things for me...thank-you.

I put the trigger in place on the InputData table and it works beautifully to set calculated = 'no' when the user makes an update to the data. However, when I attempt to assign 'yes' to the calculated field after the calculation stored procedures run, it will not. I am assuming this is because trying to set calculated = 'yes' constitutes an update to the table and per the trigger, any update to the row says to make calculated = 'no'. How do I get the trigger to fire when the update is made to any field in the table except for the calculated field?
|||

You can use IF UPDATE() clause inside the trigger to check.

e.g.

Code Snippet

IF UPDATE(calculated)

PRINT ('column [calculated] is modified')

|||As a follow up to this thread, here is how it worked out...

It seems I could not get this to work as I'd pictured using the calculated field (alas, I am a beginner so these things happen). I thought of a different approach to handle this problem. It works as follows:

I removed the calculated field from the InputData table, then created the following trigger:

Code Snippet

create trigger removeoldOutputData on InputData
for update
as
if @.@.rowcount=0 return;

delete from OutputData
where primarykey in (select primarykey from Inserted)

This trigger fires whenever a record is edited in the table InputData.

Then, the stored procedures that perform the math calculations specify which InputData records need to have calculations performed on them by comparing which primary keys in InputData do not yet have any records in OutputData. Here is a generic sample of the code:

Code Snippet

select
field1, field2, field3, ...., fieldn

into #inputdataforcalculations

from InputData
where primarykey not in (select primarykey from OutputData)

--more code follows to perform calculations on those records that were put into --#inputdataforcalculations

This where clause prevents the calculation stored procedures from recalculating existing data that has not been changed.

On the Access front end the calculation stored procedures are called in a pass-through query. There is a macro that performs an OpenQuery on the pass-through query, then this macro is automatically run when the user closes the form (event: On Close) that's used for entering/editing/deleting records in the underlying InputData SQL table.

I've run some tests on this approach and it appears to be functioning OK.

Thanks to all contributors above for my first lesson on triggers.

changing a field''s value when user updates data

Hello,
I am working on a project that involves one part where a field's value needs to be changed when the user updates the record. Here is the situation in detail:
There is an InputData table where the user enters new records or changes existing records. There is a field called "calculated" in this table which has a default value of 'no'. A stored procedure runs math calculations on all the InputData records where the calculated field = 'no'. At the end of this stored procedure, it sets the calculated field = 'yes'. When new records are added by the user their "calculated" field value is 'no' by default so that the next time the stored procedure is executed, it only runs the math calculations on the new records. The problem is, if a user changes an existing record, the "calculated" field needs to be changed from 'yes' to 'no' so that the stored procedure recalculates the math for the modified record. How do I change the value from 'yes' to 'no' on records that the user modifies?
Thanks.

For changing the field value from ‘Yes’ no ‘No’ you can use the trigger (for update).

|||

Mani is correct. Here is an example of the update trigger.

e.g.

Code Snippet

create trigger _tr on InputData

for update

as

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

from InputData tb join Inserted i on tb.[pk] = i.[pk]

where tb.calculated='yes'

go

|||

Hi!

You can directly include "Calculated ='no'" statement within the your update command itself only, based on the condition only. otherwise all the records will get updated.

eg:

Update InsertData
Set
Column1 = value1,
Column2 = value2,
...,
Calculated ='no'
WHERE <Condition>

I hope it'll solve your problem and please let me know if I'm wrong.

Thanks & Regards,
Kiran.Y

|||I have some questions about using a trigger before I attempt to implement one...

1. Will the code snippet, using the Inserted table, work in SQL version 8?

2. Which database holds the table called Inserted? Is it the one I'm working in or the tempdb or one of the other system databases?

3. Do I store the trigger as a stored procedure?

4. My project consists of SQL tables with an MS Access front end. When the user updates the record via MS Access will the stored procedure containing the trigger automatically run? (This question assumes the trigger code will be stored as a stored procedure...if this is wrong, please do correct this assumption).

5. What does if @.@.rowcount = 0 return; do?

Thanks in advance.|||

1. Yes. Sql2k does support trigger.

2. Inserted (or deleted) table is a special memory-resident table that can only be accessed in a trigger.

3. Trigger is just another object in sqlserver, similar to stored procedure.

4. Yes. Trigger is an event based procedure. It's tied to an event of the table (insert/update/delete).

5. This line tells the system to go ahead and exit and skip the rest of the code within the trigger because there is no row affected by the event.

http://msdn2.microsoft.com/en-us/library/aa258254(SQL.80).aspx

|||Your reply has helped to clarify things for me...thank-you.

I put the trigger in place on the InputData table and it works beautifully to set calculated = 'no' when the user makes an update to the data. However, when I attempt to assign 'yes' to the calculated field after the calculation stored procedures run, it will not. I am assuming this is because trying to set calculated = 'yes' constitutes an update to the table and per the trigger, any update to the row says to make calculated = 'no'. How do I get the trigger to fire when the update is made to any field in the table except for the calculated field?
|||

You can use IF UPDATE() clause inside the trigger to check.

e.g.

Code Snippet

IF UPDATE(calculated)

PRINT ('column [calculated] is modified')

|||As a follow up to this thread, here is how it worked out...

It seems I could not get this to work as I'd pictured using the calculated field (alas, I am a beginner so these things happen). I thought of a different approach to handle this problem. It works as follows:

I removed the calculated field from the InputData table, then created the following trigger:

Code Snippet

create trigger removeoldOutputData on InputData
for update
as
if @.@.rowcount=0 return;

delete from OutputData
where primarykey in (select primarykey from Inserted)

This trigger fires whenever a record is edited in the table InputData.

Then, the stored procedures that perform the math calculations specify which InputData records need to have calculations performed on them by comparing which primary keys in InputData do not yet have any records in OutputData. Here is a generic sample of the code:

Code Snippet

select
field1, field2, field3, ...., fieldn

into #inputdataforcalculations

from InputData
where primarykey not in (select primarykey from OutputData)

--more code follows to perform calculations on those records that were put into --#inputdataforcalculations

This where clause prevents the calculation stored procedures from recalculating existing data that has not been changed.

On the Access front end the calculation stored procedures are called in a pass-through query. There is a macro that performs an OpenQuery on the pass-through query, then this macro is automatically run when the user closes the form (event: On Close) that's used for entering/editing/deleting records in the underlying InputData SQL table.

I've run some tests on this approach and it appears to be functioning OK.

Thanks to all contributors above for my first lesson on triggers.

changing a field''s value when user updates data

Hello,
I am working on a project that involves one part where a field's value needs to be changed when the user updates the record. Here is the situation in detail:
There is an InputData table where the user enters new records or changes existing records. There is a field called "calculated" in this table which has a default value of 'no'. A stored procedure runs math calculations on all the InputData records where the calculated field = 'no'. At the end of this stored procedure, it sets the calculated field = 'yes'. When new records are added by the user their "calculated" field value is 'no' by default so that the next time the stored procedure is executed, it only runs the math calculations on the new records. The problem is, if a user changes an existing record, the "calculated" field needs to be changed from 'yes' to 'no' so that the stored procedure recalculates the math for the modified record. How do I change the value from 'yes' to 'no' on records that the user modifies?
Thanks.

For changing the field value from ‘Yes’ no ‘No’ you can use the trigger (for update).

|||

Mani is correct. Here is an example of the update trigger.

e.g.

Code Snippet

create trigger _tr on InputData

for update

as

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

from InputData tb join Inserted i on tb.[pk] = i.[pk]

where tb.calculated='yes'

go

|||

Hi!

You can directly include "Calculated ='no'" statement within the your update command itself only, based on the condition only. otherwise all the records will get updated.

eg:

Update InsertData
Set
Column1 = value1,
Column2 = value2,
...,
Calculated ='no'
WHERE <Condition>

I hope it'll solve your problem and please let me know if I'm wrong.

Thanks & Regards,
Kiran.Y

|||I have some questions about using a trigger before I attempt to implement one...

1. Will the code snippet, using the Inserted table, work in SQL version 8?

2. Which database holds the table called Inserted? Is it the one I'm working in or the tempdb or one of the other system databases?

3. Do I store the trigger as a stored procedure?

4. My project consists of SQL tables with an MS Access front end. When the user updates the record via MS Access will the stored procedure containing the trigger automatically run? (This question assumes the trigger code will be stored as a stored procedure...if this is wrong, please do correct this assumption).

5. What does if @.@.rowcount = 0 return; do?

Thanks in advance.|||

1. Yes. Sql2k does support trigger.

2. Inserted (or deleted) table is a special memory-resident table that can only be accessed in a trigger.

3. Trigger is just another object in sqlserver, similar to stored procedure.

4. Yes. Trigger is an event based procedure. It's tied to an event of the table (insert/update/delete).

5. This line tells the system to go ahead and exit and skip the rest of the code within the trigger because there is no row affected by the event.

http://msdn2.microsoft.com/en-us/library/aa258254(SQL.80).aspx

|||Your reply has helped to clarify things for me...thank-you.

I put the trigger in place on the InputData table and it works beautifully to set calculated = 'no' when the user makes an update to the data. However, when I attempt to assign 'yes' to the calculated field after the calculation stored procedures run, it will not. I am assuming this is because trying to set calculated = 'yes' constitutes an update to the table and per the trigger, any update to the row says to make calculated = 'no'. How do I get the trigger to fire when the update is made to any field in the table except for the calculated field?
|||

You can use IF UPDATE() clause inside the trigger to check.

e.g.

Code Snippet

IF UPDATE(calculated)

PRINT ('column [calculated] is modified')

|||As a follow up to this thread, here is how it worked out...

It seems I could not get this to work as I'd pictured using the calculated field (alas, I am a beginner so these things happen). I thought of a different approach to handle this problem. It works as follows:

I removed the calculated field from the InputData table, then created the following trigger:

Code Snippet

create trigger removeoldOutputData on InputData
for update
as
if @.@.rowcount=0 return;

delete from OutputData
where primarykey in (select primarykey from Inserted)

This trigger fires whenever a record is edited in the table InputData.

Then, the stored procedures that perform the math calculations specify which InputData records need to have calculations performed on them by comparing which primary keys in InputData do not yet have any records in OutputData. Here is a generic sample of the code:

Code Snippet

select
field1, field2, field3, ...., fieldn

into #inputdataforcalculations

from InputData
where primarykey not in (select primarykey from OutputData)

--more code follows to perform calculations on those records that were put into --#inputdataforcalculations

This where clause prevents the calculation stored procedures from recalculating existing data that has not been changed.

On the Access front end the calculation stored procedures are called in a pass-through query. There is a macro that performs an OpenQuery on the pass-through query, then this macro is automatically run when the user closes the form (event: On Close) that's used for entering/editing/deleting records in the underlying InputData SQL table.

I've run some tests on this approach and it appears to be functioning OK.

Thanks to all contributors above for my first lesson on triggers.

changing a field''s value when user updates data

Hello,
I am working on a project that involves one part where a field's value needs to be changed when the user updates the record. Here is the situation in detail:
There is an InputData table where the user enters new records or changes existing records. There is a field called "calculated" in this table which has a default value of 'no'. A stored procedure runs math calculations on all the InputData records where the calculated field = 'no'. At the end of this stored procedure, it sets the calculated field = 'yes'. When new records are added by the user their "calculated" field value is 'no' by default so that the next time the stored procedure is executed, it only runs the math calculations on the new records. The problem is, if a user changes an existing record, the "calculated" field needs to be changed from 'yes' to 'no' so that the stored procedure recalculates the math for the modified record. How do I change the value from 'yes' to 'no' on records that the user modifies?
Thanks.

For changing the field value from ‘Yes’ no ‘No’ you can use the trigger (for update).

|||

Mani is correct. Here is an example of the update trigger.

e.g.

Code Snippet

create trigger _tr on InputData

for update

as

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

from InputData tb join Inserted i on tb.[pk] = i.[pk]

where tb.calculated='yes'

go

|||

Hi!

You can directly include "Calculated ='no'" statement within the your update command itself only, based on the condition only. otherwise all the records will get updated.

eg:

Update InsertData
Set
Column1 = value1,
Column2 = value2,
...,
Calculated ='no'
WHERE <Condition>

I hope it'll solve your problem and please let me know if I'm wrong.

Thanks & Regards,
Kiran.Y

|||I have some questions about using a trigger before I attempt to implement one...

1. Will the code snippet, using the Inserted table, work in SQL version 8?

2. Which database holds the table called Inserted? Is it the one I'm working in or the tempdb or one of the other system databases?

3. Do I store the trigger as a stored procedure?

4. My project consists of SQL tables with an MS Access front end. When the user updates the record via MS Access will the stored procedure containing the trigger automatically run? (This question assumes the trigger code will be stored as a stored procedure...if this is wrong, please do correct this assumption).

5. What does if @.@.rowcount = 0 return; do?

Thanks in advance.|||

1. Yes. Sql2k does support trigger.

2. Inserted (or deleted) table is a special memory-resident table that can only be accessed in a trigger.

3. Trigger is just another object in sqlserver, similar to stored procedure.

4. Yes. Trigger is an event based procedure. It's tied to an event of the table (insert/update/delete).

5. This line tells the system to go ahead and exit and skip the rest of the code within the trigger because there is no row affected by the event.

http://msdn2.microsoft.com/en-us/library/aa258254(SQL.80).aspx

|||Your reply has helped to clarify things for me...thank-you.

I put the trigger in place on the InputData table and it works beautifully to set calculated = 'no' when the user makes an update to the data. However, when I attempt to assign 'yes' to the calculated field after the calculation stored procedures run, it will not. I am assuming this is because trying to set calculated = 'yes' constitutes an update to the table and per the trigger, any update to the row says to make calculated = 'no'. How do I get the trigger to fire when the update is made to any field in the table except for the calculated field?
|||

You can use IF UPDATE() clause inside the trigger to check.

e.g.

Code Snippet

IF UPDATE(calculated)

PRINT ('column [calculated] is modified')

|||As a follow up to this thread, here is how it worked out...

It seems I could not get this to work as I'd pictured using the calculated field (alas, I am a beginner so these things happen). I thought of a different approach to handle this problem. It works as follows:

I removed the calculated field from the InputData table, then created the following trigger:

Code Snippet

create trigger removeoldOutputData on InputData
for update
as
if @.@.rowcount=0 return;

delete from OutputData
where primarykey in (select primarykey from Inserted)

This trigger fires whenever a record is edited in the table InputData.

Then, the stored procedures that perform the math calculations specify which InputData records need to have calculations performed on them by comparing which primary keys in InputData do not yet have any records in OutputData. Here is a generic sample of the code:

Code Snippet

select
field1, field2, field3, ...., fieldn

into #inputdataforcalculations

from InputData
where primarykey not in (select primarykey from OutputData)

--more code follows to perform calculations on those records that were put into --#inputdataforcalculations

This where clause prevents the calculation stored procedures from recalculating existing data that has not been changed.

On the Access front end the calculation stored procedures are called in a pass-through query. There is a macro that performs an OpenQuery on the pass-through query, then this macro is automatically run when the user closes the form (event: On Close) that's used for entering/editing/deleting records in the underlying InputData SQL table.

I've run some tests on this approach and it appears to be functioning OK.

Thanks to all contributors above for my first lesson on triggers.

sql

changing a field''s value when user updates data

Hello,
I am working on a project that involves one part where a field's value needs to be changed when the user updates the record. Here is the situation in detail:
There is an InputData table where the user enters new records or changes existing records. There is a field called "calculated" in this table which has a default value of 'no'. A stored procedure runs math calculations on all the InputData records where the calculated field = 'no'. At the end of this stored procedure, it sets the calculated field = 'yes'. When new records are added by the user their "calculated" field value is 'no' by default so that the next time the stored procedure is executed, it only runs the math calculations on the new records. The problem is, if a user changes an existing record, the "calculated" field needs to be changed from 'yes' to 'no' so that the stored procedure recalculates the math for the modified record. How do I change the value from 'yes' to 'no' on records that the user modifies?
Thanks.

For changing the field value from ‘Yes’ no ‘No’ you can use the trigger (for update).

|||

Mani is correct. Here is an example of the update trigger.

e.g.

Code Snippet

create trigger _tr on InputData

for update

as

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

from InputData tb join Inserted i on tb.[pk] = i.[pk]

where tb.calculated='yes'

go

|||

Hi!

You can directly include "Calculated ='no'" statement within the your update command itself only, based on the condition only. otherwise all the records will get updated.

eg:

Update InsertData
Set
Column1 = value1,
Column2 = value2,
...,
Calculated ='no'
WHERE <Condition>

I hope it'll solve your problem and please let me know if I'm wrong.

Thanks & Regards,
Kiran.Y

|||I have some questions about using a trigger before I attempt to implement one...

1. Will the code snippet, using the Inserted table, work in SQL version 8?

2. Which database holds the table called Inserted? Is it the one I'm working in or the tempdb or one of the other system databases?

3. Do I store the trigger as a stored procedure?

4. My project consists of SQL tables with an MS Access front end. When the user updates the record via MS Access will the stored procedure containing the trigger automatically run? (This question assumes the trigger code will be stored as a stored procedure...if this is wrong, please do correct this assumption).

5. What does if @.@.rowcount = 0 return; do?

Thanks in advance.|||

1. Yes. Sql2k does support trigger.

2. Inserted (or deleted) table is a special memory-resident table that can only be accessed in a trigger.

3. Trigger is just another object in sqlserver, similar to stored procedure.

4. Yes. Trigger is an event based procedure. It's tied to an event of the table (insert/update/delete).

5. This line tells the system to go ahead and exit and skip the rest of the code within the trigger because there is no row affected by the event.

http://msdn2.microsoft.com/en-us/library/aa258254(SQL.80).aspx

|||Your reply has helped to clarify things for me...thank-you.

I put the trigger in place on the InputData table and it works beautifully to set calculated = 'no' when the user makes an update to the data. However, when I attempt to assign 'yes' to the calculated field after the calculation stored procedures run, it will not. I am assuming this is because trying to set calculated = 'yes' constitutes an update to the table and per the trigger, any update to the row says to make calculated = 'no'. How do I get the trigger to fire when the update is made to any field in the table except for the calculated field?
|||

You can use IF UPDATE() clause inside the trigger to check.

e.g.

Code Snippet

IF UPDATE(calculated)

PRINT ('column [calculated] is modified')

|||As a follow up to this thread, here is how it worked out...

It seems I could not get this to work as I'd pictured using the calculated field (alas, I am a beginner so these things happen). I thought of a different approach to handle this problem. It works as follows:

I removed the calculated field from the InputData table, then created the following trigger:

Code Snippet

create trigger removeoldOutputData on InputData
for update
as
if @.@.rowcount=0 return;

delete from OutputData
where primarykey in (select primarykey from Inserted)

This trigger fires whenever a record is edited in the table InputData.

Then, the stored procedures that perform the math calculations specify which InputData records need to have calculations performed on them by comparing which primary keys in InputData do not yet have any records in OutputData. Here is a generic sample of the code:

Code Snippet

select
field1, field2, field3, ...., fieldn

into #inputdataforcalculations

from InputData
where primarykey not in (select primarykey from OutputData)

--more code follows to perform calculations on those records that were put into --#inputdataforcalculations

This where clause prevents the calculation stored procedures from recalculating existing data that has not been changed.

On the Access front end the calculation stored procedures are called in a pass-through query. There is a macro that performs an OpenQuery on the pass-through query, then this macro is automatically run when the user closes the form (event: On Close) that's used for entering/editing/deleting records in the underlying InputData SQL table.

I've run some tests on this approach and it appears to be functioning OK.

Thanks to all contributors above for my first lesson on triggers.

changing a field''s value when user updates data

Hello,
I am working on a project that involves one part where a field's value needs to be changed when the user updates the record. Here is the situation in detail:
There is an InputData table where the user enters new records or changes existing records. There is a field called "calculated" in this table which has a default value of 'no'. A stored procedure runs math calculations on all the InputData records where the calculated field = 'no'. At the end of this stored procedure, it sets the calculated field = 'yes'. When new records are added by the user their "calculated" field value is 'no' by default so that the next time the stored procedure is executed, it only runs the math calculations on the new records. The problem is, if a user changes an existing record, the "calculated" field needs to be changed from 'yes' to 'no' so that the stored procedure recalculates the math for the modified record. How do I change the value from 'yes' to 'no' on records that the user modifies?
Thanks.

For changing the field value from ‘Yes’ no ‘No’ you can use the trigger (for update).

|||

Mani is correct. Here is an example of the update trigger.

e.g.

Code Snippet

create trigger _tr on InputData

for update

as

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

from InputData tb join Inserted i on tb.[pk] = i.[pk]

where tb.calculated='yes'

go

|||

Hi!

You can directly include "Calculated ='no'" statement within the your update command itself only, based on the condition only. otherwise all the records will get updated.

eg:

Update InsertData
Set
Column1 = value1,
Column2 = value2,
...,
Calculated ='no'
WHERE <Condition>

I hope it'll solve your problem and please let me know if I'm wrong.

Thanks & Regards,
Kiran.Y

|||I have some questions about using a trigger before I attempt to implement one...

1. Will the code snippet, using the Inserted table, work in SQL version 8?

2. Which database holds the table called Inserted? Is it the one I'm working in or the tempdb or one of the other system databases?

3. Do I store the trigger as a stored procedure?

4. My project consists of SQL tables with an MS Access front end. When the user updates the record via MS Access will the stored procedure containing the trigger automatically run? (This question assumes the trigger code will be stored as a stored procedure...if this is wrong, please do correct this assumption).

5. What does if @.@.rowcount = 0 return; do?

Thanks in advance.|||

1. Yes. Sql2k does support trigger.

2. Inserted (or deleted) table is a special memory-resident table that can only be accessed in a trigger.

3. Trigger is just another object in sqlserver, similar to stored procedure.

4. Yes. Trigger is an event based procedure. It's tied to an event of the table (insert/update/delete).

5. This line tells the system to go ahead and exit and skip the rest of the code within the trigger because there is no row affected by the event.

http://msdn2.microsoft.com/en-us/library/aa258254(SQL.80).aspx

|||Your reply has helped to clarify things for me...thank-you.

I put the trigger in place on the InputData table and it works beautifully to set calculated = 'no' when the user makes an update to the data. However, when I attempt to assign 'yes' to the calculated field after the calculation stored procedures run, it will not. I am assuming this is because trying to set calculated = 'yes' constitutes an update to the table and per the trigger, any update to the row says to make calculated = 'no'. How do I get the trigger to fire when the update is made to any field in the table except for the calculated field?
|||

You can use IF UPDATE() clause inside the trigger to check.

e.g.

Code Snippet

IF UPDATE(calculated)

PRINT ('column [calculated] is modified')

|||As a follow up to this thread, here is how it worked out...

It seems I could not get this to work as I'd pictured using the calculated field (alas, I am a beginner so these things happen). I thought of a different approach to handle this problem. It works as follows:

I removed the calculated field from the InputData table, then created the following trigger:

Code Snippet

create trigger removeoldOutputData on InputData
for update
as
if @.@.rowcount=0 return;

delete from OutputData
where primarykey in (select primarykey from Inserted)

This trigger fires whenever a record is edited in the table InputData.

Then, the stored procedures that perform the math calculations specify which InputData records need to have calculations performed on them by comparing which primary keys in InputData do not yet have any records in OutputData. Here is a generic sample of the code:

Code Snippet

select
field1, field2, field3, ...., fieldn

into #inputdataforcalculations

from InputData
where primarykey not in (select primarykey from OutputData)

--more code follows to perform calculations on those records that were put into --#inputdataforcalculations

This where clause prevents the calculation stored procedures from recalculating existing data that has not been changed.

On the Access front end the calculation stored procedures are called in a pass-through query. There is a macro that performs an OpenQuery on the pass-through query, then this macro is automatically run when the user closes the form (event: On Close) that's used for entering/editing/deleting records in the underlying InputData SQL table.

I've run some tests on this approach and it appears to be functioning OK.

Thanks to all contributors above for my first lesson on triggers.

changing a field's value when user updates data

Hello,
I am working on a project that involves one part where a field's value needs to be changed when the user updates the record. Here is the situation in detail:
There is an InputData table where the user enters new records or changes existing records. There is a field called "calculated" in this table which has a default value of 'no'. A stored procedure runs math calculations on all the InputData records where the calculated field = 'no'. At the end of this stored procedure, it sets the calculated field = 'yes'. When new records are added by the user their "calculated" field value is 'no' by default so that the next time the stored procedure is executed, it only runs the math calculations on the new records. The problem is, if a user changes an existing record, the "calculated" field needs to be changed from 'yes' to 'no' so that the stored procedure recalculates the math for the modified record. How do I change the value from 'yes' to 'no' on records that the user modifies?
Thanks.

For changing the field value from ‘Yes’ no ‘No’ you can use the trigger (for update).

|||

Mani is correct. Here is an example of the update trigger.

e.g.

Code Snippet

create trigger _tr on InputData

for update

as

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

from InputData tb join Inserted i on tb.[pk] = i.[pk]

where tb.calculated='yes'

go

|||

Hi!

You can directly include "Calculated ='no'" statement within the your update command itself only, based on the condition only. otherwise all the records will get updated.

eg:

Update InsertData
Set
Column1 = value1,
Column2 = value2,
...,
Calculated ='no'
WHERE <Condition>

I hope it'll solve your problem and please let me know if I'm wrong.

Thanks & Regards,
Kiran.Y

|||I have some questions about using a trigger before I attempt to implement one...

1. Will the code snippet, using the Inserted table, work in SQL version 8?

2. Which database holds the table called Inserted? Is it the one I'm working in or the tempdb or one of the other system databases?

3. Do I store the trigger as a stored procedure?

4. My project consists of SQL tables with an MS Access front end. When the user updates the record via MS Access will the stored procedure containing the trigger automatically run? (This question assumes the trigger code will be stored as a stored procedure...if this is wrong, please do correct this assumption).

5. What does if @.@.rowcount = 0 return; do?

Thanks in advance.|||

1. Yes. Sql2k does support trigger.

2. Inserted (or deleted) table is a special memory-resident table that can only be accessed in a trigger.

3. Trigger is just another object in sqlserver, similar to stored procedure.

4. Yes. Trigger is an event based procedure. It's tied to an event of the table (insert/update/delete).

5. This line tells the system to go ahead and exit and skip the rest of the code within the trigger because there is no row affected by the event.

http://msdn2.microsoft.com/en-us/library/aa258254(SQL.80).aspx

|||Your reply has helped to clarify things for me...thank-you.

I put the trigger in place on the InputData table and it works beautifully to set calculated = 'no' when the user makes an update to the data. However, when I attempt to assign 'yes' to the calculated field after the calculation stored procedures run, it will not. I am assuming this is because trying to set calculated = 'yes' constitutes an update to the table and per the trigger, any update to the row says to make calculated = 'no'. How do I get the trigger to fire when the update is made to any field in the table except for the calculated field?
|||

You can use IF UPDATE() clause inside the trigger to check.

e.g.

Code Snippet

IF UPDATE(calculated)

PRINT ('column [calculated] is modified')

|||As a follow up to this thread, here is how it worked out...

It seems I could not get this to work as I'd pictured using the calculated field (alas, I am a beginner so these things happen). I thought of a different approach to handle this problem. It works as follows:

I removed the calculated field from the InputData table, then created the following trigger:

Code Snippet

create trigger removeoldOutputData on InputData
for update
as
if @.@.rowcount=0 return;

delete from OutputData
where primarykey in (select primarykey from Inserted)

This trigger fires whenever a record is edited in the table InputData.

Then, the stored procedures that perform the math calculations specify which InputData records need to have calculations performed on them by comparing which primary keys in InputData do not yet have any records in OutputData. Here is a generic sample of the code:

Code Snippet

select
field1, field2, field3, ...., fieldn

into #inputdataforcalculations

from InputData
where primarykey not in (select primarykey from OutputData)

--more code follows to perform calculations on those records that were put into --#inputdataforcalculations

This where clause prevents the calculation stored procedures from recalculating existing data that has not been changed.

On the Access front end the calculation stored procedures are called in a pass-through query. There is a macro that performs an OpenQuery on the pass-through query, then this macro is automatically run when the user closes the form (event: On Close) that's used for entering/editing/deleting records in the underlying InputData SQL table.

I've run some tests on this approach and it appears to be functioning OK.

Thanks to all contributors above for my first lesson on triggers.

changing a field's value when user updates data

Hello,
I am working on a project that involves one part where a field's value needs to be changed when the user updates the record. Here is the situation in detail:
There is an InputData table where the user enters new records or changes existing records. There is a field called "calculated" in this table which has a default value of 'no'. A stored procedure runs math calculations on all the InputData records where the calculated field = 'no'. At the end of this stored procedure, it sets the calculated field = 'yes'. When new records are added by the user their "calculated" field value is 'no' by default so that the next time the stored procedure is executed, it only runs the math calculations on the new records. The problem is, if a user changes an existing record, the "calculated" field needs to be changed from 'yes' to 'no' so that the stored procedure recalculates the math for the modified record. How do I change the value from 'yes' to 'no' on records that the user modifies?
Thanks.

For changing the field value from ‘Yes’ no ‘No’ you can use the trigger (for update).

|||

Mani is correct. Here is an example of the update trigger.

e.g.

Code Snippet

create trigger _tr on InputData

for update

as

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

from InputData tb join Inserted i on tb.[pk] = i.[pk]

where tb.calculated='yes'

go

|||

Hi!

You can directly include "Calculated ='no'" statement within the your update command itself only, based on the condition only. otherwise all the records will get updated.

eg:

Update InsertData
Set
Column1 = value1,
Column2 = value2,
...,
Calculated ='no'
WHERE <Condition>

I hope it'll solve your problem and please let me know if I'm wrong.

Thanks & Regards,
Kiran.Y

|||I have some questions about using a trigger before I attempt to implement one...

1. Will the code snippet, using the Inserted table, work in SQL version 8?

2. Which database holds the table called Inserted? Is it the one I'm working in or the tempdb or one of the other system databases?

3. Do I store the trigger as a stored procedure?

4. My project consists of SQL tables with an MS Access front end. When the user updates the record via MS Access will the stored procedure containing the trigger automatically run? (This question assumes the trigger code will be stored as a stored procedure...if this is wrong, please do correct this assumption).

5. What does if @.@.rowcount = 0 return; do?

Thanks in advance.|||

1. Yes. Sql2k does support trigger.

2. Inserted (or deleted) table is a special memory-resident table that can only be accessed in a trigger.

3. Trigger is just another object in sqlserver, similar to stored procedure.

4. Yes. Trigger is an event based procedure. It's tied to an event of the table (insert/update/delete).

5. This line tells the system to go ahead and exit and skip the rest of the code within the trigger because there is no row affected by the event.

http://msdn2.microsoft.com/en-us/library/aa258254(SQL.80).aspx

|||Your reply has helped to clarify things for me...thank-you.

I put the trigger in place on the InputData table and it works beautifully to set calculated = 'no' when the user makes an update to the data. However, when I attempt to assign 'yes' to the calculated field after the calculation stored procedures run, it will not. I am assuming this is because trying to set calculated = 'yes' constitutes an update to the table and per the trigger, any update to the row says to make calculated = 'no'. How do I get the trigger to fire when the update is made to any field in the table except for the calculated field?
|||

You can use IF UPDATE() clause inside the trigger to check.

e.g.

Code Snippet

IF UPDATE(calculated)

PRINT ('column [calculated] is modified')

|||As a follow up to this thread, here is how it worked out...

It seems I could not get this to work as I'd pictured using the calculated field (alas, I am a beginner so these things happen). I thought of a different approach to handle this problem. It works as follows:

I removed the calculated field from the InputData table, then created the following trigger:

Code Snippet

create trigger removeoldOutputData on InputData
for update
as
if @.@.rowcount=0 return;

delete from OutputData
where primarykey in (select primarykey from Inserted)

This trigger fires whenever a record is edited in the table InputData.

Then, the stored procedures that perform the math calculations specify which InputData records need to have calculations performed on them by comparing which primary keys in InputData do not yet have any records in OutputData. Here is a generic sample of the code:

Code Snippet

select
field1, field2, field3, ...., fieldn

into #inputdataforcalculations

from InputData
where primarykey not in (select primarykey from OutputData)

--more code follows to perform calculations on those records that were put into --#inputdataforcalculations

This where clause prevents the calculation stored procedures from recalculating existing data that has not been changed.

On the Access front end the calculation stored procedures are called in a pass-through query. There is a macro that performs an OpenQuery on the pass-through query, then this macro is automatically run when the user closes the form (event: On Close) that's used for entering/editing/deleting records in the underlying InputData SQL table.

I've run some tests on this approach and it appears to be functioning OK.

Thanks to all contributors above for my first lesson on triggers.

sql

Changed fields

In our applications we have a table named ChgMstr (Change
Log) that logs changes made to each record. It includes
the Field Name, Date & Time, the user making the change,
and the Before and After values of the field.
I am trying to setup an Update trigger that will loop
through the Deleted and Inserted fields, find which fields
have changed, and then either INSERT directly into the
ChgMstr table or call a Stored Procedure to do it.
However, I cannot find a way (even with Dynamic SQL) to
build a SELECT string that can be EXECuted. When I do, I
get "Invalid Object" messages for both Deleted and
Inserted. It appears that once the EXEC statement is sent
that Deleted and Inserted go out of scope.
Anyone got any ideas. I can provide the contents of the
trigger, if needed.Bryan,
The inserted and deleted tables will not be available to a stored
procedure. You could copy their contents into a temporary table for use by
the stored procedure, but it seems to me unlikely that you need a stored
procedure just to insert into your ChgMstr table. Can you do something like
this:
insert into ChgMster
select N'ColumnA', getdate(), suser_sname(), d.ColumnA, i.ColumnA
from inserted i, deleted d
where i.primaryKey = d.primaryKey
union all
select N'ColumnB', getdate(), suser_sname(), d.ColumnB, i.ColumnB
from inserted i, deleted d
where i.primaryKey = d.primaryKey
This assumes that the primary key column is not modified. If it is, unless
you have another unique constraint on columns that don't change, the
question of what the original and final values of that column are is not
discernable from the inserted and deleted tables in some multi-row updates,
such as
update T set
pk = case when 1 then 11 when 4 then 12 when 8 then 13 end
from T
where pk in (1, 4, 8)
Then the inserted table will contain rows with pk 11, 12, and 13, and the
deleted table will contain rows with pk 1, 4, and 8, but there is no way to
say which of 1, 4, and 8 because which of 11, 12, 13 by looking at the
inserted and deleted tables.
SK
"Bryan A. Jackson" <bjackson@.focuscmc.com> wrote in message
news:ae5701c40cee$28616dc0$a001280a@.phx.gbl...
> In our applications we have a table named ChgMstr (Change
> Log) that logs changes made to each record. It includes
> the Field Name, Date & Time, the user making the change,
> and the Before and After values of the field.
> I am trying to setup an Update trigger that will loop
> through the Deleted and Inserted fields, find which fields
> have changed, and then either INSERT directly into the
> ChgMstr table or call a Stored Procedure to do it.
> However, I cannot find a way (even with Dynamic SQL) to
> build a SELECT string that can be EXECuted. When I do, I
> get "Invalid Object" messages for both Deleted and
> Inserted. It appears that once the EXEC statement is sent
> that Deleted and Inserted go out of scope.
> Anyone got any ideas. I can provide the contents of the
> trigger, if needed.|||1. Forgive my ignorance, but what does the 'N' in select
N'Column A' do?
2. Will I have to do a union for each field in the table?
I'm looking for a generic way to do this so the trigger
does not need to be changed each time fields are added or
deleted.
Thanks,
Bryan

>--Original Message--
>Bryan,
> The inserted and deleted tables will not be available
to a stored
>procedure. You could copy their contents into a
temporary table for use by
>the stored procedure, but it seems to me unlikely that
you need a stored
>procedure just to insert into your ChgMstr table. Can
you do something like
>this:
>insert into ChgMster
>select N'ColumnA', getdate(), suser_sname(), d.ColumnA,
i.ColumnA
>from inserted i, deleted d
>where i.primaryKey = d.primaryKey
>union all
>select N'ColumnB', getdate(), suser_sname(), d.ColumnB,
i.ColumnB
>from inserted i, deleted d
>where i.primaryKey = d.primaryKey
>This assumes that the primary key column is not
modified. If it is, unless
>you have another unique constraint on columns that don't
change, the
>question of what the original and final values of that
column are is not
>discernable from the inserted and deleted tables in some
multi-row updates,
>such as
>update T set
> pk = case when 1 then 11 when 4 then 12 when 8 then 13
end
>from T
>where pk in (1, 4, 8)
>Then the inserted table will contain rows with pk 11, 12,
and 13, and the
>deleted table will contain rows with pk 1, 4, and 8, but
there is no way to
>say which of 1, 4, and 8 because which of 11, 12, 13 by
looking at the
>inserted and deleted tables.
>SK
>"Bryan A. Jackson" <bjackson@.focuscmc.com> wrote in
message
>news:ae5701c40cee$28616dc0$a001280a@.phx.gbl...
(Change
fields
I
sent
>
>.
>|||Bryan,
The N just signifies that the string is Unicode. Object names in SQL
Server are in Unicode, but it won't hurt to leave this out if the name
only uses ASCII.
I generally don't think it's a great idea to write generic code that
doesn't "know" the table structure. You could generate the code for
triggers like this automatically with something like this:
select 'insert into ChgMster' oneLine, 0 ORDINAL_POSITION
union all
select 'select
'+q_column_name+',
'+' getdate(),
suser_sname(),
d.'+q_column_name+',
'+' i.'+q_column_name+'
from inserted i, deleted d
where i.primaryKey = d.primaryKey
and i.'+q_column_name+'<>d.'+q_column_name+
case when ORDINAL_POSITION = (
select max(ORDINAL_POSITION)
from Northwind.INFORMATION_SCHEMA.Columns
where TABLE_NAME = 'Orders'
) then '' else '
union all'
end, ORDINAL_POSITION
from (
select quotename(COLUMN_NAME) as q_column_name, ORDINAL_POSITION
FROM Northwind.INFORMATION_SCHEMA.Columns
where TABLE_NAME = 'Orders'
) C
order by ORDINAL_POSITION
There are also third-party products to do this kind of logging.
SK
Bryan A. Jackson wrote:
>1. Forgive my ignorance, but what does the 'N' in select
>N'Column A' do?
>2. Will I have to do a union for each field in the table?
>I'm looking for a generic way to do this so the trigger
>does not need to be changed each time fields are added or
>deleted.
>Thanks,
>Bryan
>
>
>to a stored
>
>temporary table for use by
>
>you need a stored
>
>you do something like
>
>i.ColumnA
>
>i.ColumnB
>
>modified. If it is, unless
>
>change, the
>
>column are is not
>
>multi-row updates,
>
>end
>
>and 13, and the
>
>there is no way to
>
>looking at the
>
>message
>
>(Change
>
>fields
>
>I
>
>sent
>

Change the InsertCommand of a datasource before insert

Hi,

I am trying to set the InsertCommand of a SqlDataSource prior to a new record being created in DetailsView. It has to be changed because certain fields are not being used in one scenerio and it is causing Null's to be written to the database.

Here is what I have tried:

I set the Insert command on page Load:

protectedvoid Page_Load(object sender,EventArgs e)

{

SqlDataSource3.InsertCommand =

"Insert into table (name,realName,type,extraValue) VALUES (@.name,@.realName,@.type,@.extraValue)";

}

But I don't want to insert the "extraValue if a certain condition is true so I want to change the insert command to SqlDataSource3.InsertCommand ="Insert into table (name,realName,type) VALUES (@.name,@.realName,@.type)

I tried to do that in the following:

protectedvoid DetailsView1_ItemInserted(Object sender, System.Web.UI.WebControls.DetailsViewInsertedEventArgs e)

{

SqlDataSource3.InsertCommand ="Insert into table (name,realName,type,extraValue) VALUES (@.name,@.realName,@.type,@.extraValue)";

if (e.Exception !=null)

{

ErrorMessageLabel.Text =

"An error occured while entering this record. Please verify you have entered data in the correct format.";

e.ExceptionHandled =

true;

Response.Write(e.Exception);

}

GridView1.DataBind();

GridView1.DataBind();

}

But it doesn't seem to change,

Any Help would be appreciated,

Doug

Excuse me, maybe I missed it, but where did you change the insert command of SqlDataSource3? I saw you assign the same string to the insert command of SqlDataSource3 twice:

The first time:

protected void Page_Load(object sender, EventArgs e)

{

SqlDataSource3.InsertCommand =

"Insert into table (name,realName,type,extraValue) VALUES (@.name,@.realName,@.type,@.extraValue)";

}

And the second time:

protected void DetailsView1_ItemInserted(Object sender, System.Web.UI.WebControls.DetailsViewInsertedEventArgs e)

{

SqlDataSource3.InsertCommand ="Insert into table (name,realName,type,extraValue) VALUES (@.name,@.realName,@.type,@.extraValue)";

|||

Sorry I made the same reply by mistake

|||

Some logic for this:

On you page load event:

IF condition1 is true THEN

SqlDataSource3.SelectCommand=" SELECT name,realName,type,extraValue FROM table"

SqlDataSource3.InsertCommand =

"Insert into table (name,realName,type,extraValue) VALUES (@.name,@.realName,@.type,@.extraValue)"

ELSE

SqlDataSource3.SelectCommand ="SELECT name,realName,type FROM table"

SqlDataSource3.InsertCommand ="Insert into table (name,realName,type) VALUES (@.name,@.realName,@.type)

END IF

}

|||

Sorry, the second should be:

protected void DetailsView1_ItemInserted(Object sender, System.Web.UI.WebControls.DetailsViewInsertedEventArgs e)

{

SqlDataSource3.InsertCommand ="Insert into table (name,realName,type) VALUES (@.name,@.realName,@.type)

That is actually what I tried. But it doesn't seem to change..

|||

Thanks for taking a look,

Unfortunately the condition doesn't change on page load, it changes when a radio button is clicked within this page:

protectedvoid Type_Changed(object sender,EventArgs e)

{

string aha = ((RadioButtonList)DetailsView1.FindControl("type")).SelectedItem.Value;if (aha =="Mortgage")

{

DetailsView1.Fields[6].Visible =

true;

DetailsView1.Fields[7].Visible =

true;

DetailsView1.Fields[8].Visible =

true;

}

else

{

DetailsView1.Fields[6].Visible =

false;

DetailsView1.Fields[7].Visible =

false;

DetailsView1.Fields[8].Visible =

false;

}

}

I tried to change the Sql Insert command here but it doesn't seem to work.

Thanks again,

Doug

Change ntext to nvarchar(max) in a live database

I have a live SQL 2005 database that has ntext fields, when the ntext fields go over 4000 chars the record can no longer be edited. It throws a string or binary data would be truncated error. I tried turning text in row OFF, but it did not work. Can anyone forsee any problems with changing the ntext fields to nvarchar(max) in the live database? Also, I came across sp_tableoption N'MyTable', 'large value types out of row', 'ON', does this work for ntext also? sp_tableoption N'MyTable', 'text in row', 'OFF' did not do anything.

Any help would be appreciated.

Have you set TEXTSIZE option for the connection? Check this using:

DBCC USEROPTIONS

|||

Where and how do I do this? Also I changed the ntext fields in the database to NVarChar(MAX), so now I can modify the values in the database directly, but when I do it through the application and the VarChar(MAX) fields are over 4000 it does not save the changes, but does not return an error either. It returns as if the stored procedure executed successfully.

|||

I'm not sure why you felt the need to create yet another thread, but the answer remains the same (and the 3rd time I'm giving it to you):

The problem is more likely that the parameters are being declared as either the wrong type, or you are not declaring the type at all, and letting it default. Make sure your ntext parameters are declared as such.

|||

Actually it was a problem upgrading a SQL 2000 database to SQL 2005 then modifying it, I had to recreate the table. Once I recreated the table and did a Select Into, the problem was resolved.

And I created another thread because I wanted to change the datatypes from ntext to nvarchar(max) in a live database and wanted to know if it was going to have any ill effects. In my experience, when you ask a different question in an existing thread, you do not get an answer for both questions, so I wanted to keep them separated.