Changing Column and Row Heading in MDX

I am trying to change the default row and column heading to make it more meaningful to the user in MDX. Can you please help me.

This is done easily in SQL like

SELECT the_actual_cost AS cost......

Thanks in advance.

Riju

You cannot use aliases in MDX. There are a couple of options though:
1) Select a "friendlier" member property to display instead of the name of the member.
2) Calculated members can be named on the fly.

.. there may be others in 2005 that im not aware of, but these are the ones i've traditionally used.

|||Hi,

Please try WITH SET/ WITH MEMBER option to name column headers.

Niyas|||AFAIK its impossible to retrieve the name of the named set (at least in adomd).|||Thank you all for responding to my question. I was able to rename the column header using calcualted member. However, i am little concerned about the efficiency of this technique. For example i have a measure [date of transaction] and if i want it to be named simply as [date] i have to do [date] as '[date of transaction]'. I am wondering if this will result in slower queries response time?

Thanks
Riju
|||

Why dont you just rename your measure (provided you dont already have a lot of stuff running against this measure)?

|||Hi Peter,

I am using one measure in multiple reports and trying to give them different names in different reports to make it more meaningful.

Thanks
Riju|||

Then calculated members may be the only way..

Changing Column and Row Heading in MDX

I am trying to change the default row and column heading to make it more meaningful to the user in MDX. Can you please help me.

This is done easily in SQL like

SELECT the_actual_cost AS cost......

Thanks in advance.

Riju

You cannot use aliases in MDX. There are a couple of options though:
1) Select a "friendlier" member property to display instead of the name of the member.
2) Calculated members can be named on the fly.

.. there may be others in 2005 that im not aware of, but these are the ones i've traditionally used.

|||Hi,

Please try WITH SET/ WITH MEMBER option to name column headers.

Niyas|||AFAIK its impossible to retrieve the name of the named set (at least in adomd).|||Thank you all for responding to my question. I was able to rename the column header using calcualted member. However, i am little concerned about the efficiency of this technique. For example i have a measure [date of transaction] and if i want it to be named simply as [date] i have to do [date] as '[date of transaction]'. I am wondering if this will result in slower queries response time?

Thanks
Riju
|||

Why dont you just rename your measure (provided you dont already have a lot of stuff running against this measure)?

|||Hi Peter,

I am using one measure in multiple reports and trying to give them different names in different reports to make it more meaningful.

Thanks
Riju|||

Then calculated members may be the only way..

Changing Agent User

I have to change the Sql Server 7 startup account from LocalSystem to a
domain account. This domain account has the same permissions that SA does.
As long as the new domain account has the same set of permissions, there
should be no problem with executing jobs, accessing objects, etc.? Can
anyone recommend a reference/article on the implications behind changing the
user?
Thank youSearch Books Online for "level token" and you'll find the attributed you need to give the new
service account. If you change the account using EM, these permissions will be set by EM.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Ick" <nospamplease> wrote in message news:%23bEf7r4gDHA.2248@.TK2MSFTNGP09.phx.gbl...
> I have to change the Sql Server 7 startup account from LocalSystem to a
> domain account. This domain account has the same permissions that SA does.
> As long as the new domain account has the same set of permissions, there
> should be no problem with executing jobs, accessing objects, etc.? Can
> anyone recommend a reference/article on the implications behind changing the
> user?
> Thank you
>|||Thank you Tibor. This is very helpful (although I would never have thought
to search under that phrase lol - I guess that is why you are the MVP and I
am the lowly n00b)
;-)
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OdHNz2AhDHA.2296@.TK2MSFTNGP09.phx.gbl...
> Search Books Online for "level token" and you'll find the attributed you
need to give the new
> service account. If you change the account using EM, these permissions
will be set by EM.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Ick" <nospamplease> wrote in message
news:%23bEf7r4gDHA.2248@.TK2MSFTNGP09.phx.gbl...
> > I have to change the Sql Server 7 startup account from LocalSystem to a
> > domain account. This domain account has the same permissions that SA
does.
> >
> > As long as the new domain account has the same set of permissions, there
> > should be no problem with executing jobs, accessing objects, etc.? Can
> > anyone recommend a reference/article on the implications behind changing
the
> > user?
> >
> > Thank you
> >
> >
>|||I'm glad it helped. Reason I remember this is from me training, wanting to have as few characters to
write on the whiteboard. I.e., all about being lazy :-).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Ick" <nospamplease> wrote in message news:eczFhMChDHA.4468@.TK2MSFTNGP12.phx.gbl...
> Thank you Tibor. This is very helpful (although I would never have thought
> to search under that phrase lol - I guess that is why you are the MVP and I
> am the lowly n00b)
> ;-)
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:OdHNz2AhDHA.2296@.TK2MSFTNGP09.phx.gbl...
> > Search Books Online for "level token" and you'll find the attributed you
> need to give the new
> > service account. If you change the account using EM, these permissions
> will be set by EM.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "Ick" <nospamplease> wrote in message
> news:%23bEf7r4gDHA.2248@.TK2MSFTNGP09.phx.gbl...
> > > I have to change the Sql Server 7 startup account from LocalSystem to a
> > > domain account. This domain account has the same permissions that SA
> does.
> > >
> > > As long as the new domain account has the same set of permissions, there
> > > should be no problem with executing jobs, accessing objects, etc.? Can
> > > anyone recommend a reference/article on the implications behind changing
> the
> > > user?
> > >
> > > Thank you
> > >
> > >
> >
> >
>

Changing a SQL login from DBO to SA ( and back )

Hi,
Looking for a proper way to change a SQL login ( not a domain user ) from
DBO to SA ( and back if a curtain action has finished ) using TSQL.
This user is NOT connected to any role therefor sp_droprolemember/
sp_addrolemember is not appropriate.
Can you help me out guys?
G Kramerwould SETUSER or EXECUTE AS do?
Peter

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

changeobjectowner permission

Hi
Is it possible that an user database (without server functions) own permissi
on on sp_changeobjectowner?Antonio,
The BOL says the following: Only members of sysadmin fixed server role, the
db_owner fixed database role, or a member of both the db_ddladmin and
db_securityadmin fixed database roles can execute sp_changeobjectowner.
As far as I know, this is correct. Are you having an experience that
suggests otherwise?
Russell Fields
"Antonio" <anonymous@.discussions.microsoft.com> wrote in message
news:C79A1502-34D7-4665-8BBE-489170481915@.microsoft.com...
> Hi
> Is it possible that an user database (without server functions) own
permission on sp_changeobjectowner?
>

changeobjectowner permission

Hi
Is it possible that an user database (without server functions) own permission on sp_changeobjectowner?
Antonio,
The BOL says the following: Only members of sysadmin fixed server role, the
db_owner fixed database role, or a member of both the db_ddladmin and
db_securityadmin fixed database roles can execute sp_changeobjectowner.
As far as I know, this is correct. Are you having an experience that
suggests otherwise?
Russell Fields
"Antonio" <anonymous@.discussions.microsoft.com> wrote in message
news:C79A1502-34D7-4665-8BBE-489170481915@.microsoft.com...
> Hi
> Is it possible that an user database (without server functions) own
permission on sp_changeobjectowner?
>

Changed user p/w, can't start Sql Server

Hi;
I am running Sql Server dev edition on my home system - not on a domain.
When I installed I selecgted mixed security so can have user/pw ad well as
SSPI. I told it to run the server using my login.
I changed the password for my Windows login and Sql Server will now not
start. If I change back to the old pw it will. How do I get it to update the
pw for my username for when it starts?
thanks - dave"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
the
> pw for my username for when it starts?
> --
> thanks - dave
Open up AdminTools -> Services
Find the MSSQLServer and SQLServerAgent service and update the passwords
there. Restart the services.
Rick Sawtell
MCT, MCSD, MCDBA|||Administrative Tools, Services, there you select the SQL Server service and
change the password for
the login. this applies for all services in windows, not only SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update t
he
> pw for my username for when it starts?
> --
> thanks - dave|||Change the password for the MSSQLSERVER service and the SQLSERVERAGENT
service using the control panel service control applet. Next time, use
Enterprise Mangler to change the password for the service before you change
it at the Windows level.
Geoff N. Hiten
Microsoft SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
> the
> pw for my username for when it starts?
> --
> thanks - dave|||Hi,
After changing the Service startup account password, you need to change that
in Control panel -- admin tools -- services.
How to do:-
1. Control Panel - Admin tools -- Services
2. Double click above the MSSQL Server service
3. In the Log on tab, change the password, Click ok and restart the
service.
Note:
Do the same for SQL Agent service.
Thanks
Hari
SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
> the
> pw for my username for when it starts?
> --
> thanks - dave|||Thank you everyone. As soon as I saw that answer it was "of course - stupid
me."
thanks - dave
"David Thielen" wrote:

> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update t
he
> pw for my username for when it starts?
> --
> thanks - dave

Changed user p/w, can't start Sql Server

Hi;
I am running Sql Server dev edition on my home system - not on a domain.
When I installed I selecgted mixed security so can have user/pw ad well as
SSPI. I told it to run the server using my login.
I changed the password for my Windows login and Sql Server will now not
start. If I change back to the old pw it will. How do I get it to update the
pw for my username for when it starts?
--
thanks - dave"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
the
> pw for my username for when it starts?
> --
> thanks - dave
Open up AdminTools -> Services
Find the MSSQLServer and SQLServerAgent service and update the passwords
there. Restart the services.
Rick Sawtell
MCT, MCSD, MCDBA|||Administrative Tools, Services, there you select the SQL Server service and change the password for
the login. this applies for all services in windows, not only SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update the
> pw for my username for when it starts?
> --
> thanks - dave|||Change the password for the MSSQLSERVER service and the SQLSERVERAGENT
service using the control panel service control applet. Next time, use
Enterprise Mangler to change the password for the service before you change
it at the Windows level.
Geoff N. Hiten
Microsoft SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
> the
> pw for my username for when it starts?
> --
> thanks - dave|||Hi,
After changing the Service startup account password, you need to change that
in Control panel -- admin tools -- services.
How to do:-
1. Control Panel - Admin tools -- Services
2. Double click above the MSSQL Server service
3. In the Log on tab, change the password, Click ok and restart the
service.
Note:
Do the same for SQL Agent service.
Thanks
Hari
SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
> the
> pw for my username for when it starts?
> --
> thanks - dave|||Thank you everyone. As soon as I saw that answer it was "of course - stupid
me."
--
thanks - dave
"David Thielen" wrote:
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update the
> pw for my username for when it starts?
> --
> thanks - dave

Changed user p/w, can't start Sql Server

Hi;
I am running Sql Server dev edition on my home system - not on a domain.
When I installed I selecgted mixed security so can have user/pw ad well as
SSPI. I told it to run the server using my login.
I changed the password for my Windows login and Sql Server will now not
start. If I change back to the old pw it will. How do I get it to update the
pw for my username for when it starts?
thanks - dave
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
the
> pw for my username for when it starts?
> --
> thanks - dave
Open up AdminTools -> Services
Find the MSSQLServer and SQLServerAgent service and update the passwords
there. Restart the services.
Rick Sawtell
MCT, MCSD, MCDBA
|||Administrative Tools, Services, there you select the SQL Server service and change the password for
the login. this applies for all services in windows, not only SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update the
> pw for my username for when it starts?
> --
> thanks - dave
|||Change the password for the MSSQLSERVER service and the SQLSERVERAGENT
service using the control panel service control applet. Next time, use
Enterprise Mangler to change the password for the service before you change
it at the Windows level.
Geoff N. Hiten
Microsoft SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
> the
> pw for my username for when it starts?
> --
> thanks - dave
|||Hi,
After changing the Service startup account password, you need to change that
in Control panel -- admin tools -- services.
How to do:-
1. Control Panel - Admin tools -- Services
2. Double click above the MSSQL Server service
3. In the Log on tab, change the password, Click ok and restart the
service.
Note:
Do the same for SQL Agent service.
Thanks
Hari
SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
> the
> pw for my username for when it starts?
> --
> thanks - dave
|||Thank you everyone. As soon as I saw that answer it was "of course - stupid
me."
thanks - dave
"David Thielen" wrote:

> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update the
> pw for my username for when it starts?
> --
> thanks - dave

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
>

changed admin password (was "help reqd")

hi all,

i have installed mssql2k in win2000 server. since it was for my personal use, i did not give a password for the 'administrator' user in win2k. later on i cahnged the admin password,but my sqlserver did not start due to login failure.

is ther any solutions to tackle this?What account did you change the password for?hi all,

i have installed mssql2k in win2000 server. since it was for my personal use, i did not give a password for the 'administrator' user in win2k. later on i cahnged the admin password,but my sqlserver did not start due to login failure.

is ther any solutions to tackle this?|||hi

i changed the password 4 Administrator User of win2k server|||hi

i changed the password 4 Administrator User of win2k server

what is ur service log on account?
I believe u are using administrator account..... if so then u need to to update the password here too.

if it is for personal use u can use the option local system account.|||you need to change the password in the services

start->settings->control panel->administrative tools->services

find mssql( & sqlagent if your automatically starting that one as well for backups etc...). Double click... go on the log on tab, and change the password on the "this account" to the new password.|||thanks for the information

:-)

Changed a parameter to multi-value and now it doesn't work

I have a report with multiple parameters that the user can specifiy before
running the report. One of them is a field that the user can type in a value
for. It's defined as a string and allows a blank value in the event the user
does not want to enter anything. It works fine when not multi-value. But
when I change it to multi-value, the report brings back nothing when the user
does not enter any values.
The related dataset code for this field (tracking_number) is
...
WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
...
Any help would be appreciated.
StephanieRemove the multi-value property. that is not really meant for a text field,
only for instances where one might choose from multiple values of a drop down
list.
"Stephanie" wrote:
> I have a report with multiple parameters that the user can specifiy before
> running the report. One of them is a field that the user can type in a value
> for. It's defined as a string and allows a blank value in the event the user
> does not want to enter anything. It works fine when not multi-value. But
> when I change it to multi-value, the report brings back nothing when the user
> does not enter any values.
> The related dataset code for this field (tracking_number) is
> ...
> WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
> AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
> AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
> ...
> Any help would be appreciated.
> Stephanie|||When I do that, I cannot add multple values for the field.
I've tried:
x,y
x y
'x','y'
Suggestions?
"Carl Henthorn" wrote:
> Remove the multi-value property. that is not really meant for a text field,
> only for instances where one might choose from multiple values of a drop down
> list.
> "Stephanie" wrote:
> > I have a report with multiple parameters that the user can specifiy before
> > running the report. One of them is a field that the user can type in a value
> > for. It's defined as a string and allows a blank value in the event the user
> > does not want to enter anything. It works fine when not multi-value. But
> > when I change it to multi-value, the report brings back nothing when the user
> > does not enter any values.
> >
> > The related dataset code for this field (tracking_number) is
> > ...
> > WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
> > AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
> > AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
> > ...
> >
> > Any help would be appreciated.
> >
> > Stephanie|||Everything that a person enters into that field will be considered one
string. you have to parse out the string in the sproc to break it up into its
component pieces.
Without seeing what you are doing, I dont understand why you "cant add
multiple values for the field." tha field just wraps a single quote around
whatever is there and passes it in as the parameter.
in my test, I put in 1,2,3,4,5 into my text box. what was passed in to my
sproc was '1,2,3,4,5' <note the addition of the single quotes>. If this is
not happening for you, you may want to make things easier on you and just
have a multi-valued dropdown list. At least that way you are not hostage to
your users spelling ability.
"Stephanie" wrote:
> When I do that, I cannot add multple values for the field.
> I've tried:
> x,y
> x y
> 'x','y'
> Suggestions?
> "Carl Henthorn" wrote:
> > Remove the multi-value property. that is not really meant for a text field,
> > only for instances where one might choose from multiple values of a drop down
> > list.
> >
> > "Stephanie" wrote:
> >
> > > I have a report with multiple parameters that the user can specifiy before
> > > running the report. One of them is a field that the user can type in a value
> > > for. It's defined as a string and allows a blank value in the event the user
> > > does not want to enter anything. It works fine when not multi-value. But
> > > when I change it to multi-value, the report brings back nothing when the user
> > > does not enter any values.
> > >
> > > The related dataset code for this field (tracking_number) is
> > > ...
> > > WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
> > > AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
> > > AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
> > > ...
> > >
> > > Any help would be appreciated.
> > >
> > > Stephanie|||The problem is that this is a string, not an integer. So a single quote at
the beginning and the end is not helpful. The user does not what a drop-down
list because the number of values that would be there would be very large.
They want to type in something like: CIM070524001,CIM070522002. They want to
be able to type in one or multiple values.
Can you test with a string and let me know how that goes? I just can't get
it to work.
"Carl Henthorn" wrote:
> Everything that a person enters into that field will be considered one
> string. you have to parse out the string in the sproc to break it up into its
> component pieces.
> Without seeing what you are doing, I dont understand why you "cant add
> multiple values for the field." tha field just wraps a single quote around
> whatever is there and passes it in as the parameter.
> in my test, I put in 1,2,3,4,5 into my text box. what was passed in to my
> sproc was '1,2,3,4,5' <note the addition of the single quotes>. If this is
> not happening for you, you may want to make things easier on you and just
> have a multi-valued dropdown list. At least that way you are not hostage to
> your users spelling ability.
> "Stephanie" wrote:
> > When I do that, I cannot add multple values for the field.
> >
> > I've tried:
> >
> > x,y
> > x y
> > 'x','y'
> >
> > Suggestions?
> >
> > "Carl Henthorn" wrote:
> >
> > > Remove the multi-value property. that is not really meant for a text field,
> > > only for instances where one might choose from multiple values of a drop down
> > > list.
> > >
> > > "Stephanie" wrote:
> > >
> > > > I have a report with multiple parameters that the user can specifiy before
> > > > running the report. One of them is a field that the user can type in a value
> > > > for. It's defined as a string and allows a blank value in the event the user
> > > > does not want to enter anything. It works fine when not multi-value. But
> > > > when I change it to multi-value, the report brings back nothing when the user
> > > > does not enter any values.
> > > >
> > > > The related dataset code for this field (tracking_number) is
> > > > ...
> > > > WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
> > > > AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
> > > > AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
> > > > ...
> > > >
> > > > Any help would be appreciated.
> > > >
> > > > Stephanie