Aggregate may not appear in an UPDATE statement

I am trying to update a field in a table with the most recent event_date. However, I am receiving the following error:

Msg 157, Level 15, State 1, Line 1

An aggregate may not appear in the set list of an UPDATE statement.

My code appears below:

-

update mraOI

set mraOI.event_history_key = eh.event_history_key,

mraOI.event_date = MAX(eh.event_date),

mraOI.event_time = eh.event_time,

mraOI.event_type_key = eh.event_type_key,

mraOI.item_key = eh.item_key,

mraOI.associated_item_ke = eh.associated_item_ke

FROM reports.MedRecActionOI mraOI INNER JOIN

srm.EVENT_HISTORY eh ON mraOI.CC_episode_key = eh.item_key

--

Can someone suggest a better method or a work around?

You could try this:

Code Snippet

update mraOI

set mraOI.event_history_key = eh.event_history_key,

mraOI.event_date = eh.event_date,

mraOI.event_time = eh.event_time,

mraOI.event_type_key = eh.event_type_key,

mraOI.item_key = eh.item_key,

mraOI.associated_item_ke = eh.associated_item_ke

FROM reports.MedRecActionOI mraOI INNER JOIN

srm.EVENT_HISTORY eh ON mraOI.CC_episode_key = eh.item_key

where eh.event_date = (select max(event_date) from srm.EVENT_HISTORY where item_key = mraOI.CC_episode_key)

I haven't tested it as I don't have your DB structure, but hopefully that'll help you.

One potential issue to be aware of. If there are more than one event with the same event_date you could get more than one row. If you have the time as well as the date it's very unlikely to happen, as times are accurate to 3ms. However if you're using smalldatetime they're only accurate to 1 minute, so the risk of getting more than one with the same value is greater.

Sean

|||This worked great, Sean! Thanks.

Aggregate - Sum with group by

Hi,

I'm trying to use the aggregate transformation to sum my orders table unit price and quantity with a grouping of state but i can't see how to add the sub grouping. My order table has the following fields of interest Unit Price (Money), Quantity (Integer) and State (Varchar)

ID Unit Price Quantity State 1 $2.19 500 AZ 2 $29.99 33 WA 3 $1000.00 1 WA 4 $1.20 7 WA

When i run the aggregate i want the output to be grouped by state

Total Price Quantity Sold State $2.19 500 AZ $1031.19 41 WA

Hope the values are correct

Martin

Add the aggregate to your data flow, and connect an input. Open the properties and check the Unit Price, Quantity, and State on the available input columns. In the grid at the bottom, make sure the State has the Operation set to "Group By", and Operation to "Sum" for the Unit Price and Quantity columns. That should do it.|||In the operation selection for state i can't select "Group By" it only has "Count". Any ideas?|||

Martin Perkins wrote:

In the operation selection for state i can't select "Group By" it only has "Count". Any ideas?

That's strange. What's the data type of the state column? Have you installed service pack 1 at least?|||

The State property is Nvarchar(max) Just installing service pack 2 for SQL Server 2005

|||

It was the length of the state column. If i convert the column from Nvarchar(max) to Nvarchar(20) i can then group by.

Thanks for help

Martin

|||

SSIS treats NVARCHAR(MAX) as BLOB type, and does not support grouping by blobs. You need to convert it to fixed-lenght type, like NVARCHAR(2).

You can do it either

1) at the source - NVARCHAR(MAX) is very inefficient method of storing 2-character data, if you can change the source, I suggest doing it

2) case to fixed-lenght type in the SQL query

3) use SSIS to create another column of fixed lenght (I'm not sure if type convertion transform does it, if not then try derived column transform)

Hope this help,

Michael.

Agent not running?

Hi I am trying to run na backup and when I run the job manually I get
an error SQL-DMO State 42000, Error 200225 SQLServerAgent is currently
not running and cannot be notified. In the event log I get these two,
but the agent is definitely running. Any ideas?
Thanks
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:17052
You do not have sufficient operating system permission to open the
SQLServerAgent status event.
Event Type:Error
Event Source:SQLSERVERAGENT
Event Category:Alert Engine
The data portion of event 17052 from MSSQLSERVER is invalid.
Well the agent has the Green arrow on the icon in EM and when I right
click it, Start is greyed out and Stop is not. In services
SQLSERVERAGENT has the status of started, so Im confused as to why Im
getting this error.
Tibor Karaszi wrote:[vbcol=seagreen]
> Starting the Agent service?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "hals_left" <cc900630@.ntu.ac.uk> wrote in message
> news:1162561943.730620.280980@.h48g2000cwc.googlegr oups.com...
|||Only 1 instance running. It does work on the schedule when not looged
in though, its only when I manually run the job from EM it fails.Will
check the agent error log
Tibor Karaszi wrote:[vbcol=seagreen]
> Did you check Agent for the correct instance? Perhaps you have several instances of SQL Server
> installed?
> Assuming you did, I'd check SQL Server agent's error log for errors as the next step.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "hals_left" <cc900630@.ntu.ac.uk> wrote in message
> news:1162833283.408183.293470@.h54g2000cwb.googlegr oups.com...
|||Hi Can anyone suggest what might be goig wrong here?
I have 4 backup jobs, 1 old one and 3 new-ish. All the settings are the
same on the 3 new jobs, the only difference is the time they run. All
fail when manually run with the errors below, the old one still
succeeds on the schedule when no one is logged in.
The strange thing is that the one that succeeds on the schedule is
actually now running at a different time than is defined, yesterday I
changed it from 00:00 to 03:00 and changed the schedule name from
"Schedule 1" to "3AM" yet it still runs at 00:00 and reports "Schedule
1" I also changed one of the step names yet it still reports the old
step name.
It seems the changes being made in EM are not taking effect, and that
SQL Server cannot see that the agent is running.
I have not had this problem before on other SQL 2000 installs, the only
diference is that I installed this folling best practices and set up
sqlservice and sqlagent on diferent windows accounts. ALl jobs login as
Administrator , I have tried changing thois the SQLAgent & SQLService
but it makes no difference.
The only thing thatr has changed on the server is windows updates.
The version is 8.00.2039 Standard Edition on NT5.2 SP1
Thanks
hals_left wrote:[vbcol=seagreen]
> Only 1 instance running. It does work on the schedule when not looged
> in though, its only when I manually run the job from EM it fails.Will
> check the agent error log
>
> Tibor Karaszi wrote:

Agent not running?

Hi I am trying to run na backup and when I run the job manually I get
an error SQL-DMO State 42000, Error 200225 SQLServerAgent is currently
not running and cannot be notified. In the event log I get these two,
but the agent is definitely running. Any ideas?
Thanks
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
You do not have sufficient operating system permission to open the
SQLServerAgent status event.
Event Type: Error
Event Source: SQLSERVERAGENT
Event Category: Alert Engine
The data portion of event 17052 from MSSQLSERVER is invalid.Starting the Agent service?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1162561943.730620.280980@.h48g2000cwc.googlegroups.com...
> Hi I am trying to run na backup and when I run the job manually I get
> an error SQL-DMO State 42000, Error 200225 SQLServerAgent is currently
> not running and cannot be notified. In the event log I get these two,
> but the agent is definitely running. Any ideas?
> Thanks
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> You do not have sufficient operating system permission to open the
> SQLServerAgent status event.
>
> Event Type: Error
> Event Source: SQLSERVERAGENT
> Event Category: Alert Engine
> The data portion of event 17052 from MSSQLSERVER is invalid.
>|||Well the agent has the Green arrow on the icon in EM and when I right
click it, Start is greyed out and Stop is not. In services
SQLSERVERAGENT has the status of started, so Im confused as to why Im
getting this error.
Tibor Karaszi wrote:[vbcol=seagreen]
> Starting the Agent service?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "hals_left" <cc900630@.ntu.ac.uk> wrote in message
> news:1162561943.730620.280980@.h48g2000cwc.googlegroups.com...|||Did you check Agent for the correct instance? Perhaps you have several insta
nces of SQL Server
installed?
Assuming you did, I'd check SQL Server agent's error log for errors as the n
ext step.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1162833283.408183.293470@.h54g2000cwb.googlegroups.com...
> Well the agent has the Green arrow on the icon in EM and when I right
> click it, Start is greyed out and Stop is not. In services
> SQLSERVERAGENT has the status of started, so Im confused as to why Im
> getting this error.
>
> Tibor Karaszi wrote:
>|||Only 1 instance running. It does work on the schedule when not looged
in though, its only when I manually run the job from EM it fails.Will
check the agent error log
Tibor Karaszi wrote:[vbcol=seagreen]
> Did you check Agent for the correct instance? Perhaps you have several ins
tances of SQL Server
> installed?
> Assuming you did, I'd check SQL Server agent's error log for errors as the
next step.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "hals_left" <cc900630@.ntu.ac.uk> wrote in message
> news:1162833283.408183.293470@.h54g2000cwb.googlegroups.com...

Agent not running?

Hi I am trying to run na backup and when I run the job manually I get
an error SQL-DMO State 42000, Error 200225 SQLServerAgent is currently
not running and cannot be notified. In the event log I get these two,
but the agent is definitely running. Any ideas?
Thanks
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
You do not have sufficient operating system permission to open the
SQLServerAgent status event.
Event Type: Error
Event Source: SQLSERVERAGENT
Event Category: Alert Engine
The data portion of event 17052 from MSSQLSERVER is invalid.Starting the Agent service?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1162561943.730620.280980@.h48g2000cwc.googlegroups.com...
> Hi I am trying to run na backup and when I run the job manually I get
> an error SQL-DMO State 42000, Error 200225 SQLServerAgent is currently
> not running and cannot be notified. In the event log I get these two,
> but the agent is definitely running. Any ideas?
> Thanks
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> You do not have sufficient operating system permission to open the
> SQLServerAgent status event.
>
> Event Type: Error
> Event Source: SQLSERVERAGENT
> Event Category: Alert Engine
> The data portion of event 17052 from MSSQLSERVER is invalid.
>|||Well the agent has the Green arrow on the icon in EM and when I right
click it, Start is greyed out and Stop is not. In services
SQLSERVERAGENT has the status of started, so Im confused as to why Im
getting this error.
Tibor Karaszi wrote:
> Starting the Agent service?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "hals_left" <cc900630@.ntu.ac.uk> wrote in message
> news:1162561943.730620.280980@.h48g2000cwc.googlegroups.com...
> > Hi I am trying to run na backup and when I run the job manually I get
> > an error SQL-DMO State 42000, Error 200225 SQLServerAgent is currently
> > not running and cannot be notified. In the event log I get these two,
> > but the agent is definitely running. Any ideas?
> >
> > Thanks
> >
> > Event Type: Error
> > Event Source: MSSQLSERVER
> > Event Category: (2)
> > Event ID: 17052
> > You do not have sufficient operating system permission to open the
> > SQLServerAgent status event.
> >
> >
> > Event Type: Error
> > Event Source: SQLSERVERAGENT
> > Event Category: Alert Engine
> > The data portion of event 17052 from MSSQLSERVER is invalid.
> >|||Did you check Agent for the correct instance? Perhaps you have several instances of SQL Server
installed?
Assuming you did, I'd check SQL Server agent's error log for errors as the next step.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1162833283.408183.293470@.h54g2000cwb.googlegroups.com...
> Well the agent has the Green arrow on the icon in EM and when I right
> click it, Start is greyed out and Stop is not. In services
> SQLSERVERAGENT has the status of started, so Im confused as to why Im
> getting this error.
>
> Tibor Karaszi wrote:
>> Starting the Agent service?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "hals_left" <cc900630@.ntu.ac.uk> wrote in message
>> news:1162561943.730620.280980@.h48g2000cwc.googlegroups.com...
>> > Hi I am trying to run na backup and when I run the job manually I get
>> > an error SQL-DMO State 42000, Error 200225 SQLServerAgent is currently
>> > not running and cannot be notified. In the event log I get these two,
>> > but the agent is definitely running. Any ideas?
>> >
>> > Thanks
>> >
>> > Event Type: Error
>> > Event Source: MSSQLSERVER
>> > Event Category: (2)
>> > Event ID: 17052
>> > You do not have sufficient operating system permission to open the
>> > SQLServerAgent status event.
>> >
>> >
>> > Event Type: Error
>> > Event Source: SQLSERVERAGENT
>> > Event Category: Alert Engine
>> > The data portion of event 17052 from MSSQLSERVER is invalid.
>> >
>|||Only 1 instance running. It does work on the schedule when not looged
in though, its only when I manually run the job from EM it fails.Will
check the agent error log
Tibor Karaszi wrote:
> Did you check Agent for the correct instance? Perhaps you have several instances of SQL Server
> installed?
> Assuming you did, I'd check SQL Server agent's error log for errors as the next step.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "hals_left" <cc900630@.ntu.ac.uk> wrote in message
> news:1162833283.408183.293470@.h54g2000cwb.googlegroups.com...
> > Well the agent has the Green arrow on the icon in EM and when I right
> > click it, Start is greyed out and Stop is not. In services
> > SQLSERVERAGENT has the status of started, so Im confused as to why Im
> > getting this error.
> >
> >
> >
> > Tibor Karaszi wrote:
> >> Starting the Agent service?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "hals_left" <cc900630@.ntu.ac.uk> wrote in message
> >> news:1162561943.730620.280980@.h48g2000cwc.googlegroups.com...
> >> > Hi I am trying to run na backup and when I run the job manually I get
> >> > an error SQL-DMO State 42000, Error 200225 SQLServerAgent is currently
> >> > not running and cannot be notified. In the event log I get these two,
> >> > but the agent is definitely running. Any ideas?
> >> >
> >> > Thanks
> >> >
> >> > Event Type: Error
> >> > Event Source: MSSQLSERVER
> >> > Event Category: (2)
> >> > Event ID: 17052
> >> > You do not have sufficient operating system permission to open the
> >> > SQLServerAgent status event.
> >> >
> >> >
> >> > Event Type: Error
> >> > Event Source: SQLSERVERAGENT
> >> > Event Category: Alert Engine
> >> > The data portion of event 17052 from MSSQLSERVER is invalid.
> >> >
> >|||Hi Can anyone suggest what might be goig wrong here?
I have 4 backup jobs, 1 old one and 3 new-ish. All the settings are the
same on the 3 new jobs, the only difference is the time they run. All
fail when manually run with the errors below, the old one still
succeeds on the schedule when no one is logged in.
The strange thing is that the one that succeeds on the schedule is
actually now running at a different time than is defined, yesterday I
changed it from 00:00 to 03:00 and changed the schedule name from
"Schedule 1" to "3AM" yet it still runs at 00:00 and reports "Schedule
1" I also changed one of the step names yet it still reports the old
step name.
It seems the changes being made in EM are not taking effect, and that
SQL Server cannot see that the agent is running.
I have not had this problem before on other SQL 2000 installs, the only
diference is that I installed this folling best practices and set up
sqlservice and sqlagent on diferent windows accounts. ALl jobs login as
Administrator , I have tried changing thois the SQLAgent & SQLService
but it makes no difference.
The only thing thatr has changed on the server is windows updates.
The version is 8.00.2039 Standard Edition on NT5.2 SP1
Thanks
hals_left wrote:
> Only 1 instance running. It does work on the schedule when not looged
> in though, its only when I manually run the job from EM it fails.Will
> check the agent error log
>
> Tibor Karaszi wrote:
> > Did you check Agent for the correct instance? Perhaps you have several instances of SQL Server
> > installed?
> >
> > Assuming you did, I'd check SQL Server agent's error log for errors as the next step.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "hals_left" <cc900630@.ntu.ac.uk> wrote in message
> > news:1162833283.408183.293470@.h54g2000cwb.googlegroups.com...
> > > Well the agent has the Green arrow on the icon in EM and when I right
> > > click it, Start is greyed out and Stop is not. In services
> > > SQLSERVERAGENT has the status of started, so Im confused as to why Im
> > > getting this error.
> > >
> > >
> > >
> > > Tibor Karaszi wrote:
> > >> Starting the Agent service?
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >>
> > >>
> > >> "hals_left" <cc900630@.ntu.ac.uk> wrote in message
> > >> news:1162561943.730620.280980@.h48g2000cwc.googlegroups.com...
> > >> > Hi I am trying to run na backup and when I run the job manually I get
> > >> > an error SQL-DMO State 42000, Error 200225 SQLServerAgent is currently
> > >> > not running and cannot be notified. In the event log I get these two,
> > >> > but the agent is definitely running. Any ideas?
> > >> >
> > >> > Thanks
> > >> >
> > >> > Event Type: Error
> > >> > Event Source: MSSQLSERVER
> > >> > Event Category: (2)
> > >> > Event ID: 17052
> > >> > You do not have sufficient operating system permission to open the
> > >> > SQLServerAgent status event.
> > >> >
> > >> >
> > >> > Event Type: Error
> > >> > Event Source: SQLSERVERAGENT
> > >> > Event Category: Alert Engine
> > >> > The data portion of event 17052 from MSSQLSERVER is invalid.
> > >> >
> > >