change MSDB owner: working

Showing posts with label working. Show all posts

Thursday, March 22, 2012

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

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

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

where tb.calculated='yes'

|||

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

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

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

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

where tb.calculated='yes'

|||

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?

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.

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...

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.

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.

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

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

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

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

where tb.calculated='yes'

|||

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?

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.

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...

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.

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.

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

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

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

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

where tb.calculated='yes'

|||

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?

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.

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...

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.

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.

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

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

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

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

where tb.calculated='yes'

|||

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?

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.

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...

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.

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.

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

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

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

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

where tb.calculated='yes'

|||

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?

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.

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...

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.

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.

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

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

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

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

where tb.calculated='yes'

|||

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?

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.

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...

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.

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.

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

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

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

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

where tb.calculated='yes'

|||

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?

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.

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...

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.

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.

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

Tuesday, March 20, 2012

Changes to clustered sql server

Hi
I need to change some settings on a clustered sql server 2000. I need to
use sp_configure to change the "set working set size" option and also
increase the memtoleave area using the -g flag.
How is this best accomplished in a cluster with 2 nodes?
- failover to node2, do the changes to node1 and restart the service?
- fail back to node1 and update node2 with a restart of service?
This way I will avoid downtime on the server.
Is this the way to do it?The settings follow the virtual server so once you stop and restart the
instance, you don't have to modify settings for any other nodes. Be sure
and make the startup flag changes using Enterprise Manager so it updates the
cluster properly. It is typically faster to move an instance to a new node
if you have a lot of AWE memory. Otherwise it is usually better to stop and
restart on the same node.
Geoff N. Hiten
Microsoft SQL Server MVP
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.65...
> Hi
> I need to change some settings on a clustered sql server 2000. I need to
> use sp_configure to change the "set working set size" option and also
> increase the memtoleave area using the -g flag.
> How is this best accomplished in a cluster with 2 nodes?
> - failover to node2, do the changes to node1 and restart the service?
> - fail back to node1 and update node2 with a restart of service?
> This way I will avoid downtime on the server.
> Is this the way to do it?|||Thanks,
does this mean that e.g. the -g flag should not be added to the
imagepath key in the registry for the sql server service? Will
parameters to the service not be taken into account?
The reason I ask is that the cluster is set up this way today.
We don't use AWE, so I understand your advise as
- add the -g parameter in EM for the active instance
- restart the service
My goal is to minimize downtime.
Regards,
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
> The settings follow the virtual server so once you stop and restart
> the instance, you don't have to modify settings for any other nodes.
> Be sure and make the startup flag changes using Enterprise Manager so
> it updates the cluster properly. It is typically faster to move an
> instance to a new node if you have a lot of AWE memory. Otherwise it
> is usually better to stop and restart on the same node.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.65...
>> Hi
>> I need to change some settings on a clustered sql server 2000. I need
>> to use sp_configure to change the "set working set size" option and
>> also increase the memtoleave area using the -g flag.
>> How is this best accomplished in a cluster with 2 nodes?
>> - failover to node2, do the changes to node1 and restart the service?
>> - fail back to node1 and update node2 with a restart of service?
>> This way I will avoid downtime on the server.
>> Is this the way to do it?
>|||Generally on a cluster we are all trying to minimize downtime.
I am curious why you need more memtoleave. Are you running a lot of
third-party extended stored procedures? What specific symptoms are you
seeing? Before tweaking that parameter, I would open a case with PSS to try
and diagnose whatever the underlying problem really is.
EM is supposed to do the registry writes "under the covers" correctly for
clustered and non-clustered SQL instances.
Geoff N. Hiten
Microsoft SQL Server MVP
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns967E70926DD36gurbaohotmailcom@.129.250.171.68...
> Thanks,
> does this mean that e.g. the -g flag should not be added to the
> imagepath key in the registry for the sql server service? Will
> parameters to the service not be taken into account?
> The reason I ask is that the cluster is set up this way today.
> We don't use AWE, so I understand your advise as
> - add the -g parameter in EM for the active instance
> - restart the service
> My goal is to minimize downtime.
> Regards,
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
> news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
>> The settings follow the virtual server so once you stop and restart
>> the instance, you don't have to modify settings for any other nodes.
>> Be sure and make the startup flag changes using Enterprise Manager so
>> it updates the cluster properly. It is typically faster to move an
>> instance to a new node if you have a lot of AWE memory. Otherwise it
>> is usually better to stop and restart on the same node.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> "Gurba" <gurbao@.hotmail.com> wrote in message
>> news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.65...
>> Hi
>> I need to change some settings on a clustered sql server 2000. I need
>> to use sp_configure to change the "set working set size" option and
>> also increase the memtoleave area using the -g flag.
>> How is this best accomplished in a cluster with 2 nodes?
>> - failover to node2, do the changes to node1 and restart the service?
>> - fail back to node1 and update node2 with a restart of service?
>> This way I will avoid downtime on the server.
>> Is this the way to do it?
>>
>|||Hi,
We are seeing "WARNING: Failed to reserve contiguous memory ..."
messages in the errorlog. I've already been in contact with MS PSS and
they have advised us to increase the memtoleave area to see if this
resolves our problems.
This is not as a result of 3rdparty xps, but rather some "extreme"
queries that are submitted from time to time.
Thanks for your help.
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:
> Generally on a cluster we are all trying to minimize downtime.
> I am curious why you need more memtoleave. Are you running a lot of
> third-party extended stored procedures? What specific symptoms are
> you seeing? Before tweaking that parameter, I would open a case with
> PSS to try and diagnose whatever the underlying problem really is.
> EM is supposed to do the registry writes "under the covers" correctly
> for clustered and non-clustered SQL instances.
>
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E70926DD36gurbaohotmailcom@.129.250.171.68...
>> Thanks,
>> does this mean that e.g. the -g flag should not be added to the
>> imagepath key in the registry for the sql server service? Will
>> parameters to the service not be taken into account?
>> The reason I ask is that the cluster is set up this way today.
>> We don't use AWE, so I understand your advise as
>> - add the -g parameter in EM for the active instance
>> - restart the service
>> My goal is to minimize downtime.
>> Regards,
>> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
>> news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
>> The settings follow the virtual server so once you stop and restart
>> the instance, you don't have to modify settings for any other nodes.
>> Be sure and make the startup flag changes using Enterprise Manager
>> so it updates the cluster properly. It is typically faster to move
>> an instance to a new node if you have a lot of AWE memory.
>> Otherwise it is usually better to stop and restart on the same node.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> "Gurba" <gurbao@.hotmail.com> wrote in message
>> news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.65...
>> Hi
>> I need to change some settings on a clustered sql server 2000. I
>> need to use sp_configure to change the "set working set size"
>> option and also increase the memtoleave area using the -g flag.
>> How is this best accomplished in a cluster with 2 nodes?
>> - failover to node2, do the changes to node1 and restart the
>> service? - fail back to node1 and update node2 with a restart of
>> service?
>> This way I will avoid downtime on the server.
>> Is this the way to do it?
>>
>
>|||Sorry for being "slow" here;
if I add the parameter (using EM) on the node owning the sql resource,
everything will be ok also when I failover and the sql server service
starts on the new node? EM took care of updating the registry also on
the second node?
If I add the parameter on the node _not_ owning the sql resource, EM
will update the registry on this node but not the other, so that I will
have to do the same on that after failover to this (phew)?
I have a feeling I'm being too complicated here, or is this stuff
complicated?
TIA
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:
> Generally on a cluster we are all trying to minimize downtime.
> I am curious why you need more memtoleave. Are you running a lot of
> third-party extended stored procedures? What specific symptoms are
> you seeing? Before tweaking that parameter, I would open a case with
> PSS to try and diagnose whatever the underlying problem really is.
> EM is supposed to do the registry writes "under the covers" correctly
> for clustered and non-clustered SQL instances.
>
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Gurba" <gurbao@.hotmail.com> wrote in message
> news:Xns967E70926DD36gurbaohotmailcom@.129.250.171.68...
>> Thanks,
>> does this mean that e.g. the -g flag should not be added to the
>> imagepath key in the registry for the sql server service? Will
>> parameters to the service not be taken into account?
>> The reason I ask is that the cluster is set up this way today.
>> We don't use AWE, so I understand your advise as
>> - add the -g parameter in EM for the active instance
>> - restart the service
>> My goal is to minimize downtime.
>> Regards,
>> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
>> news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
>> The settings follow the virtual server so once you stop and restart
>> the instance, you don't have to modify settings for any other nodes.
>> Be sure and make the startup flag changes using Enterprise Manager
>> so it updates the cluster properly. It is typically faster to move
>> an instance to a new node if you have a lot of AWE memory.
>> Otherwise it is usually better to stop and restart on the same node.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> "Gurba" <gurbao@.hotmail.com> wrote in message
>> news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.65...
>> Hi
>> I need to change some settings on a clustered sql server 2000. I
>> need to use sp_configure to change the "set working set size"
>> option and also increase the memtoleave area using the -g flag.
>> How is this best accomplished in a cluster with 2 nodes?
>> - failover to node2, do the changes to node1 and restart the
>> service? - fail back to node1 and update node2 with a restart of
>> service?
>> This way I will avoid downtime on the server.
>> Is this the way to do it?
>>
>|||Comments Inline
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns9680AAAF4D1gurbaohotmailcom@.129.250.171.65...
> Sorry for being "slow" here;
I have no problem with you asking careful questions. NNTP postings are
cheap. Downtime gets expensive.
> if I add the parameter (using EM) on the node owning the sql resource,
> everything will be ok also when I failover and the sql server service
> starts on the new node? EM took care of updating the registry also on
> the second node?
Technically, EM updates the clustered registry keys and MSCS takes care of
copying them around where needed, but yes, that is essentially what happens.
> If I add the parameter on the node _not_ owning the sql resource, EM
> will update the registry on this node but not the other, so that I will
> have to do the same on that after failover to this (phew)?
>
Depends. If you manually hack the registry and/or startup parameters on a
non-owner node, it gets worse. Since that node doesn't own the resource
group, any changes made to the clustered keys/parameters get overwritten the
next time the resource group shifts to that node. EM actually has the SQL
Service write everything to the registry so it always happens on the correct
node.
> I have a feeling I'm being too complicated here, or is this stuff
> complicated?
Yes, it is complicated, but the fine programmers at Microsoft (cough, cough)
handle the complexity for you, at least in this case.
GNH
> TIA
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in
> news:OUR8aU$dFHA.1920@.tk2msftngp13.phx.gbl:
>> Generally on a cluster we are all trying to minimize downtime.
>> I am curious why you need more memtoleave. Are you running a lot of
>> third-party extended stored procedures? What specific symptoms are
>> you seeing? Before tweaking that parameter, I would open a case with
>> PSS to try and diagnose whatever the underlying problem really is.
>> EM is supposed to do the registry writes "under the covers" correctly
>> for clustered and non-clustered SQL instances.
>>
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>>
>> "Gurba" <gurbao@.hotmail.com> wrote in message
>> news:Xns967E70926DD36gurbaohotmailcom@.129.250.171.68...
>> Thanks,
>> does this mean that e.g. the -g flag should not be added to the
>> imagepath key in the registry for the sql server service? Will
>> parameters to the service not be taken into account?
>> The reason I ask is that the cluster is set up this way today.
>> We don't use AWE, so I understand your advise as
>> - add the -g parameter in EM for the active instance
>> - restart the service
>> My goal is to minimize downtime.
>> Regards,
>> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in
>> news:uzNxL#5dFHA.3012@.tk2msftngp13.phx.gbl:
>> The settings follow the virtual server so once you stop and restart
>> the instance, you don't have to modify settings for any other nodes.
>> Be sure and make the startup flag changes using Enterprise Manager
>> so it updates the cluster properly. It is typically faster to move
>> an instance to a new node if you have a lot of AWE memory.
>> Otherwise it is usually better to stop and restart on the same node.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> "Gurba" <gurbao@.hotmail.com> wrote in message
>> news:Xns967E413E39AEgurbaohotmailcom@.129.250.171.65...
>> Hi
>> I need to change some settings on a clustered sql server 2000. I
>> need to use sp_configure to change the "set working set size"
>> option and also increase the memtoleave area using the -g flag.
>> How is this best accomplished in a cluster with 2 nodes?
>> - failover to node2, do the changes to node1 and restart the
>> service? - fail back to node1 and update node2 with a restart of
>> service?
>> This way I will avoid downtime on the server.
>> Is this the way to do it?
>>
>>
>

Changes to clustered sql server

Monday, March 19, 2012

Changes related to PDF in SQL Server 2005 Reporting Services

Hi All,

I am working on SQL Server 2005 reporting services. In reports I am required to make changes related to exporting report to PDF. Following are those changes:

1) Change PDF export page format to letter from legal

2) Fix page break to ensure that all details related to a specific issue are viewable on the same page

In reports exporting is done through the control given by services itself. So can any one please guide me regarding how this changes can be done. Please help me ASAP...

Thanks.... Smile

For (1), it seems like you should be able to just change the page size of the report. Or do you need to change the size just for PDF? If you are using URL access or SOAP, you can specify page sizes via device info settings. See http://msdn2.microsoft.com/en-us/library/ms154682.aspx

For (2), you will need to modify the RDL; try looking into the KeepTogether and RepeatWith settings on report items.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSRDL/htm/rsp_ref_rdl_elements_ae_4ak3.asp|||

With regard to page breaks I can see a KeepTogether property that refers to the whole report but not to individual groups. There is no RepeatWith setting within my rdl (SQL2005).

I would like my report to group on a particular field but don't want it to break before or after the report header / footer. Any idea how I can do this?

Changed sa password, db maint plan not working

Our in-house developer changed the "sa" password on the Sql 2000 server and now the database maintenance plan that I created for backups to disk isn't working. The day it stopped I received the following errors in the log:

"2003-02-13 09:20:41.37 spid58 Error: 15457, Severity: 0, State: 1

2003-02-13 09:20:41.37 spid58 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..

2003-02-13 09:20:41.48 spid58 Using 'xplog70.dll' version '2000.80.194' to execute extended stored procedure 'xp_msver'.

2003-02-13 15:54:25.59 spid53 Error: 15457, Severity: 0, State: 1

2003-02-13 15:54:25.59 spid53 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..:"I don't think that changing the sa password has got anything to do with the DBMaintenance plan unless you are using DTS for running the DB Backup etc etc.

Changed machine name after SSRS installation

Hi,
I changed my machine name after installation of SSRS 2005. After the name
change, nothing seems working. To make the reports work, what and where do I
need to make the changes?
Whenever I starts reconfiguration, it says Invalid Namespace error.
Appreciate your help.
Thanks
AtulHave you checked whether the server is getting accessed from "Reporting
Services configuration" from configuration menu.
Amarnath
"Atul Shukla" wrote:
> Hi,
> I changed my machine name after installation of SSRS 2005. After the name
> change, nothing seems working. To make the reports work, what and where do I
> need to make the changes?
> Whenever I starts reconfiguration, it says Invalid Namespace error.
> Appreciate your help.
> Thanks
> Atul
>
>|||If nothing is so imp in your server then reinstall it. One more thing just try
typing http://localhost/reports this is the check recommended by MS to see
whether RS installed properly if you dont get the home page then it is not
installed properly.
Amarnath
"Atul Shukla" wrote:
> No. Whenever I try to access the reporting service configuration, I get the
> error (file attached).
> It seems that I need to re-install SQL Server.
>
> "Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
> news:D6758B6E-17D1-4336-85F0-CF76470D6873@.microsoft.com...
> > Have you checked whether the server is getting accessed from "Reporting
> > Services configuration" from configuration menu.
> >
> > Amarnath
> >
> > "Atul Shukla" wrote:
> >
> >> Hi,
> >>
> >> I changed my machine name after installation of SSRS 2005. After the name
> >> change, nothing seems working. To make the reports work, what and where
> >> do I
> >> need to make the changes?
> >> Whenever I starts reconfiguration, it says Invalid Namespace error.
> >>
> >> Appreciate your help.
> >> Thanks
> >> Atul
> >>
> >>
> >>
>
>

Sunday, March 11, 2012

Change xp_cmdshell working directory?

Hello,
xp_cmdshell seems to be using c:\windows\system32 as some sort of "working"
directory. When we excute commands that create temporary files (such as FTP
for example), they write their temporary files to c:\windows\system32. If
we're ftping a large file, this causes problems.
Can this be changed? When FTP.exe is run from a normal command window, it
seems to respect the TEMP/TMP environment variable settings. When run from
xp_cmdshell, it doesn't seem to respect those settings.
Does anyone have any ideas?You need to set the TEMP/TMP environment variable settings for the user that
SQL Server runs as. SQL Server does not run as *your* account, typically,
so changing your environment variables does absolutely nothing for the SQL
Server process.
A
"Daniel Peterson" <pythas@.hotmail.com> wrote in message
news:B2531A19-F787-4365-9F5E-3B29AE3D7956@.microsoft.com...
> Hello,
> xp_cmdshell seems to be using c:\windows\system32 as some sort of
> "working"
> directory. When we excute commands that create temporary files (such as
> FTP
> for example), they write their temporary files to c:\windows\system32. If
> we're ftping a large file, this causes problems.
> Can this be changed? When FTP.exe is run from a normal command window, it
> seems to respect the TEMP/TMP environment variable settings. When run
> from
> xp_cmdshell, it doesn't seem to respect those settings.
> Does anyone have any ideas?|||Hello,
The environment variables are set properly for the service account.
It looks like this FTP procedure is part of a defined job that's scheduled
to run a couple of times a week. That step that uses xp_cmdshell to call FTP
is set to run as "Self".
Any other ideas?
"Aaron Bertrand [SQL Server MVP]" wrote:
> You need to set the TEMP/TMP environment variable settings for the user that
> SQL Server runs as. SQL Server does not run as *your* account, typically,
> so changing your environment variables does absolutely nothing for the SQL
> Server process.
> A
>
> "Daniel Peterson" <pythas@.hotmail.com> wrote in message
> news:B2531A19-F787-4365-9F5E-3B29AE3D7956@.microsoft.com...
> > Hello,
> >
> > xp_cmdshell seems to be using c:\windows\system32 as some sort of
> > "working"
> > directory. When we excute commands that create temporary files (such as
> > FTP
> > for example), they write their temporary files to c:\windows\system32. If
> > we're ftping a large file, this causes problems.
> >
> > Can this be changed? When FTP.exe is run from a normal command window, it
> > seems to respect the TEMP/TMP environment variable settings. When run
> > from
> > xp_cmdshell, it doesn't seem to respect those settings.
> >
> > Does anyone have any ideas?
>
>|||> It looks like this FTP procedure is part of a defined job that's scheduled
> to run a couple of times a week. That step that uses xp_cmdshell to call
> FTP
> is set to run as "Self".
> Any other ideas?
Oh, you didn't mention this was a scheduled job. What is the proxy/account
in use for the SQL Server Agent service (not SQL Server itself)? Who is the
owner of the job?|||SQL Service Account is running as the same service account as the SQL Server
itself. If I login as that service account user, environment variables look
like they get set properly. Looks like the server isn't configured with a
proxy account.
Didn't realize it was a scheduled job either, our developers left that out
of the email they sent me, and I just noticed it when I was pawing through
the job definition more.
Looks like the owner of the job is sa.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > It looks like this FTP procedure is part of a defined job that's scheduled
> > to run a couple of times a week. That step that uses xp_cmdshell to call
> > FTP
> > is set to run as "Self".
> >
> > Any other ideas?
> Oh, you didn't mention this was a scheduled job. What is the proxy/account
> in use for the SQL Server Agent service (not SQL Server itself)? Who is the
> owner of the job?
>
>|||You can verify the account and the environment variables by
executing a job with the same job owner to execute
xp_cmdshell 'SET' . Have the job step output to a file and
then check that file.
Check the system variables for the temporary directory as
well. Control Panel -> System. Select the Advanced tab - you
can check environment variables there. Not everything
related to a profile is loaded when the account is running a
service.
-Sue
On Fri, 16 Feb 2007 15:07:03 -0800, Daniel Peterson
<pythas@.hotmail.com> wrote:
>SQL Service Account is running as the same service account as the SQL Server
>itself. If I login as that service account user, environment variables look
>like they get set properly. Looks like the server isn't configured with a
>proxy account.
>Didn't realize it was a scheduled job either, our developers left that out
>of the email they sent me, and I just noticed it when I was pawing through
>the job definition more.
>Looks like the owner of the job is sa.
>"Aaron Bertrand [SQL Server MVP]" wrote:
>> > It looks like this FTP procedure is part of a defined job that's scheduled
>> > to run a couple of times a week. That step that uses xp_cmdshell to call
>> > FTP
>> > is set to run as "Self".
>> >
>> > Any other ideas?
>> Oh, you didn't mention this was a scheduled job. What is the proxy/account
>> in use for the SQL Server Agent service (not SQL Server itself)? Who is the
>> owner of the job?
>>

Thursday, March 22, 2012

changing a field''s value when user updates data

changing a field''s value when user updates data

changing a field''s value when user updates data

changing a field''s value when user updates data

changing a field''s value when user updates data

changing a field''s value when user updates data

changing a field's value when user updates data

changing a field's value when user updates data

Tuesday, March 20, 2012

Changes to clustered sql server

Changes to clustered sql server

Changes to clustered sql server

Monday, March 19, 2012

Changes related to PDF in SQL Server 2005 Reporting Services

Changed sa password, db maint plan not working

Changed machine name after SSRS installation

Sunday, March 11, 2012

Change xp_cmdshell working directory?

Change xp_cmdshell working directory?

change MSDB owner

Blog Archive

About Me