Poor Performance in SQL Server 2000 Database with SP3a 8.00.760

Hello All,

In our production database we have 1 Millions number of records in the database. In one database server we have install Service Pack SP3a 8.00.818 version. Performance of the system seems to be good but performance is poor in another server we have installed Service Pack SP3a 8.00.760 version. Whether this problem is due to the version miss match.

Can any DBA can help in sorting this issue?

I'm moving this thread to the SQL Server Database Engine forum.|||

You have the security patch on your first server. Details can be found here

http://support.microsoft.com/kb/821277/en-us

Poison Message Sample in BOL

Hi,

We've been trying to diagnose a problem with service broker's poison message detection and tried to run the sample script in BOL to see how it solved the problem. It appears the example doesn't seem to work correctly,

IF (@.messageTypeName =
'//Adventure-Works.com/AccountsPayable/ExpenseReport')
BEGIN
DECLARE @.expenseReport NVARCHAR(MAX) ;
SET @.expenseReport = CAST(@.messageBody AS NVARCHAR(MAX)) ;
EXEC AdventureWorks.dbo.AddExpenseReport
@.report = @.expenseReport ;
IF @.@.ERROR <> 0
BEGIN
/* THIS ROLLBACK INCREMENTS THE FAILURE COUNT */
ROLLBACK TRANSACTION UndoReceive ;
EXEC TrackMessage @.conversationHandle ;
END ;
ELSE
BEGIN
EXEC AdventureWorks.dbo.ClearMessageTracking
@.conversationHandle ;
END ;
END ;
ELSE

In the scenario where AddExpenseReport fails it will retry the message the next time round the loop - TrackMessage looks to see how many times the message has failed:

IF @.count > 20
/* @.count WILL NEVER GET ABOVE 5 */
BEGIN
EXEC dbo.ClearMessageTracking @.conversationHandle ;
END CONVERSATION @.conversationHandle
WITH ERROR = 500
DESCRIPTION = 'Unable to process message.' ;
END ;
ELSE

But the counter will not get to 20 as after 5 the queue will be disabled which causes ProcessExpenseReport to fail. Subsequent message cannot then be processed until the queue is re-enabled.

We have changed the sample so that once the message has failed four times it is then written to a dead message table and we can then call commit so that the queue can carry on without being disabled:

[in ProcessExpenseReport]
...
EXEC AdventureWorks.dbo.AddExpenseReport @.report = @.expenseReport ;
IF @.@.error <> 0
BEGIN
-- The message has failed, we need to work out if we are safe to
-- retry or not. If this message has failed four times already
-- we need to recieve it, save it into another table and commit to
-- avoid service broker from disabling the queue. Otherwise we can
-- rollback and try again to see if it works next time.

-- Find out how many times this message has failed
declare @.failCount int
exec GetFailCount @.conversationHandle, @.failCount output
-- If we are about to reach the threshold
if @.failCount >= 4
begin
-- Insert the message into the dead message table. A sql agent
-- job will at some point in the future put the contents of this
-- table back onto the queue for reprocessing
EXEC DeadMessage @.conversationHandle, @.expenseReport
end
else
begin
-- We are safe to rollback and take another run at it
ROLLBACK TRANSACTION UndoReceive ;
EXEC TrackMessage @.conversationHandle ;
end
END ;
ELSE
BEGIN
-- Call to SP worked we can clear down our message tracking
EXEC ClearMessageTracking @.conversationHandle ;
END ;
END ;
...
/* Procedure to find out how many times this message
has failed to be processed */
CREATE PROCEDURE GetFailCount
@.conversationHandle uniqueidentifier,
@.failCount int output as
begin
SELECT @.failCount = [count] FROM ExpenseServiceFailedMessages
WHERE conversation_handle = @.conversationHandle
end
go

/* Procedure to insert the message into the Dead message
table and end the conversation */
create procedure DeadMessage
@.conversation_handle uniqueidentifier,
@.message_body varchar(max)
as
begin
insert into ExpenseServiceDeadMessages ( message_body ) values ( @.message_body )

EXEC dbo.ClearMessageTracking @.conversation_Handle ;
END CONVERSATION @.conversation_Handle
WITH ERROR = 500
DESCRIPTION = 'Unable to process message.' ;

end
go

/* Updated version of TrackMessage which no longer checks the
fail count - it just increments each time */
CREATE PROCEDURE TrackMessage
@.conversationHandle uniqueidentifier
AS
BEGIN
IF @.conversationHandle IS NULL
RETURN ;

DECLARE @.count INT ;
SET @.count = NULL ;
SET @.count = (SELECT count FROM dbo.ExpenseServiceFailedMessages
WHERE conversation_handle = @.conversationHandle) ;

/* TrackMessage no longer checks the failure count */
IF @.count IS NULL
BEGIN
INSERT INTO dbo.ExpenseServiceFailedMessages
(count, conversation_handle)
VALUES (1, @.conversationHandle) ;
END ;
ELSE
BEGIN
UPDATE dbo.ExpenseServiceFailedMessages
SET count=count+1
WHERE conversation_handle = @.conversationHandle ;
END ;
END ;
GO

/* New table to hold the Dead messages */
CREATE TABLE ExpenseServiceDeadMessages (
message_body varchar(max)
) ;

Now this solves the problem. If a message arrives that can't be processed (either becuase the message itself is bad, or because of an system fault with another system that we interact with) the message is moved to the DeadMessage table and the queue carries on and does not get disabled.

In our application however none of this code should be required, as long as we are confident that the data in the messages are "valid". In our case the only reason the processing would fail is if an external system we are dependant fails for some reason (e.g. Maybe writing the message to disk and the disk is full ). In this scenario we want to retry the message indefinately until the error clears. If we could disable the poision message detection this would solve the problem and remove the need for this solution.

Any thoughts on this? Have we missed the point somewhere....

Cheers,

Neil.

The main problem with just disabling the poison message check is that when you roll back a message, it's still at the top of the queue so nothing else gets processed until the message succeeds. You're probably better off commiting the receive and re-sending the meaasge - maybe to some kind of retry queue - so that you don't block other messages on the queue. Keep in mind that if you have multiple messages on the same dialog you'll have to be careful how you handel this so you don't end up changing the message order. I've seen a couple examples of people who use conversation timers to implement a timed retry for cases like this when there is a possibility of the remote resource being unavailable for an extended time.|||

I'd like to add that when a queue is being disabled (e.g. because of a poison message), a BROKER_QUEUE_DISABLED event notification is fired. Here is a small demo to ilustrate this:

use tempdb;
go

create procedure sp_rollback_on_pm
as
begin
set nocount on;
begin transaction;
receive * from q_pm;
rollback;
end;
go

create queue q_pm
with activation (
status = on,
max_queue_readers = 1,
procedure_name = [sp_rollback_on_pm],
execute as owner);
go

create service s_demo_pm on queue q_pm ([DEFAULT]);
go

create queue q_en;
create service s_en on queue q_en ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
go

create event notification ev_qm on queue q_pm for broker_queue_disabled to service 's_en', 'current database';
go

declare @.dh uniqueidentifier;
begin dialog conversation @.dh
from service s_demo_pm
to service 's_demo_pm'
with encryption = off;
send on conversation @.dh;
send on conversation @.dh;
send on conversation @.dh;
send on conversation @.dh;
send on conversation @.dh;
send on conversation @.dh;
go

select cast(message_body as xml),* from q_en
go

An administrator can receive notifications when queues are being disabled as a result of a poison message and it can inspect the queue and fix the issue or remove the problem dialog. Or an activated procedure on the q_en queue can automatically fix the issue and re-enable the q_pm queue (of course, assuming that the issue can be fixed automatically).

HTH,
~ Remus

|||Thanks for the replies - this has allowed us to progress to a past this problem.

I do entirely understand why service broker is doing what it's doing, it will quickly enable you to locate any messages that break the system doe to an application bug. However in the scenario where a message cannot be processed due to a situation beyond your control this features requires you to implement a lot more code then perhaps you should.

In our scenario, if we cannot process message A which is at the front of the queue, we are not going to be able to process message B either, so there's no point trying. The ability to turn of poision message detection would be very useful.

I imagine this has been discussed elsewhere but it also strikes me as odd that I can now write code:

-- About to start a transaction
BEGIN TRANSACTION
RECEIVE * FROM myQueue
ROLLBACK TRANSACTION
-- The database is now in a different state from when begin tran was called!!

I personally have always relied on the premise that EVERYTHING I DO (with SQL) inside a transaction is undone when I call rollback. This no longer seems to be true?

Cheers,

Neil|||

Everything you did in the transaction is undone when the transaction is roled back. The message is back in the queueand any other data updates are reversed. The queue is disabled as a result of the rollback, after the rollback occured.

In the case you describe, since the application is able to detect the problem message, I wouldn't actually call it a poison message. Is more like a message your app understands but is not willing to process. It seems to me that a more appropiate action would be to move the message into a different queue (i.e. forward the message to a secondary service) or end the dialog with an error.
The poison message was indeed thought more for cases when the application crashes when processing the message and the system spins on this message, w/o making any progress.

|||

I can see Neil's issue with the poison message handling, and the inability to turn it off. I guess he's got a queue whose processing ultimately depends on some external system (say a web service call or something). In the scenario where the web service is unavailable, you'd pull a message from the queue and try to call the web service. If the web service fails (for example, due to network issues), then you want to rollback the receive to enable that message to be retried in the future. It's not really possible for an application to know whether a network-based call is going to work in advance.

Moving the message of to another queue merely delays the inevitable - the other queue needs processing, and of course it can fail in the same way. I guess you could just take messages from the second queue and add them back to the first, but this seems like a poor solution (it also causes the messages to be reordered, which could have its own issues).

Much as I understand (and applaud) the reasoning behind poison message detection, it does seem that there are scenarios where it would be useful to just turn it off.

|||For the scenario you describe I'd recommend using conversation timers, not rollback the message. Rolling back would make the message visible again immeadetly, triggering another attempt. Instead, using a timer would allow for a delayed retry, like this:

begin transaction
Receive
if web request message
store the request data in table
else if timer message
read request data from table
begin conversation timer (e.g. 1 minute)
commit
do the web request
if succeeded
begin transaction
reset the conversation timer
send back response
commit

Ultimately, everything in the server is optimized for the commit case. Rollbacks should really be extraordinarily and this is how we approached the problem.

As a last resort, the BROKER_QUEUE_DISABLED event notification can be used to enable back the queue automatically.

HTH,
~ Remussql

Point #2

Information for configuring Service Broker when you have multiple initiators seems to be thin on the ground.
The examples are all point-to-point and seem to require separate logins for each initiator and corresponding exchange of certificates using a multi-pass installation.
E.g. Rushi's ServiceListing API.

We have an application with several hundred workstations which need to send transaction data to department servers and then on to corporate. There is also a need to replicate small amounts of reference data to all levels.

The examples seem to generate an installation and administrative nightmare.

I have a question - would the following scheme work?

1. All our machines are on the same domain so transport security can be handled by Windows Security (One less set of certificates to exchange).

2. Generate 2 Certificates on the Corporate database and export them to the department and workstations
use these pre-installed certificates in each database to handle dialog security for all converations.
Ie. multiple workstations feed into one user id on the
department server using a single certificate. Ditto for all the department servers feeding into corporate.

3. Use Service broker id's to determine where messages are sent (as the same services would be installed at multiple locations).

4. When a database is intalled/restored at a workstation does this invalidate any certificates that are shipped with it?

Carl Berger wrote:

Information for configuring Service Broker when you have multiple initiators seems to be thin on the ground.
The examples are all point-to-point and seem to require separate logins for each initiator and corresponding exchange of certificates using a multi-pass installation.
E.g. Rushi's ServiceListing API.

We are working on an tool that uses the service listing concept that should make this much, much easier. My plan is to release it within days.

Carl Berger wrote:

We have an application with several hundred workstations which need to send transaction data to department servers and then on to corporate. There is also a need to replicate small amounts of reference data to all levels.
The examples seem to generate an installation and administrative nightmare.

You might want to consider a BCN (Broker Configuration Notice) Service for your environment: http://msdn2.microsoft.com/en-us/library/ms166054(SQL.90).aspx
This can take care of the service routes and service security settings, acting as a UDDI or Active Directory service (in fact it can be implemented as a wrapper around AD). The BCN service is interogated whenever a routes is missing or a remote service binding is missing. The BCN service itself can be configured to accept anonymous dialogs and validates the request by out of band means (e.g. validates that the requestor host machine is a machine member of your domain).

Carl Berger wrote:

1. All our machines are on the same domain so transport security can be handled by Windows Security (One less set of certificates to exchange).

Yes, this works. If you plan to use machine accounts instead of a domain user account for the SQL Serverr serviceaccount, my recommendation is to make all machines member of a Windows Group and then grant CONNECT permission on endpoints to this group, instead of individual machines. Note that using machine accounts (domain\machine$) requires (manual) registration of SPNs.

Carl Berger wrote:

2. Generate 2 Certificates on the Corporate database and export them to the department and workstations
use these pre-installed certificates in each database to handle dialog security for all converations.
Ie. multiple workstations feed into one user id on the
department server using a single certificate. Ditto for all the department servers feeding into corporate.

I believe the BCN service can implement this in a more manageable way. When it will receive a request for a missing REMOTE SERVICE BINDING it can create and deploy a certificate for the requesting service as part of processing the request.

Carl Berger wrote:

3. Use Service broker id's to determine where messages are sent (as the same services would be installed at multiple locations).

That's exactly the purpose of the broker id.

Carl Berger wrote:

4. When a database is intalled/restored at a workstation does this invalidate any certificates that are shipped with it?

No, certificates are not affected. However, private keys associated with certificates are stored encrypted with the database master key. The Service Broker background threads can access the database master key only if is encrypted with the service master key (since a background thread cannot have access to the password). So as part of the install/restore, these steps are necessary:
- open database master key using the password
- add the service master key encryption to the database master key

One note is that if you plan to use this install/restore to create new instances of a service, you'll have to generate a new broker id ( ALTER DATABASE ... SET NEW_BROKER).

HTH,
~ Remus

|||

The BCN service seems to be a good solution for setting up the route.

My proposal here was to create only two certificates to handle all dialog security and to preship them with an installation package for each workstation. There would be only a single certificate/remote-user at the target and a single predefined remote-user and certificate which is reused on every workstation.

The "Can you hear me now?" type of installation would be avoided, new workstations could be installed without a single change at the corporate end.

Comments?

|||

The BCN also is used for querying missing REMOTE SERVICE BINDINGs, so it can trigger all the steps for a security configuration deployment (certs). The BCN is not queried continously, a query is sent when an ROUTE or RSB is missing, and the BCN service is expected to actually create the missing route or RSB. So the BCN should normally be interogated only once per new workstation. I think the more appropiate analogy is plug-n-play configuration, rather than 'can you hear me now'.

Shiping a pre-packaged database, with all services, keys, code (including CLR assemblies) and permissions it sure is an attractive deployment solution. Unless you care about things like nonrepudiation of dialogs/messages (workstation cannot later deny that it sent a given message), having once cert/private key duplicated for each workstation works fine. Another thing to consider is that a private key shared by hundreds of workstation cannot be really considered private anymore, so you might as well use anonymous dialogs (they don't require private keys on the initiator side, traffic is encrypted).
Also, many of the problems avoided by shipping the database will come back when the certificates start expiring. Just to keep in mind...

HTH,
~ Remus

|||

We don't need repudiation. The messages are generated by a trusted sales application.

It looks like anonymous dialogs might be the best way to go.

Many thanks.

PLS help: SqlserverAgent service could not be started

Anyone can help me:
I get windows events like this:
"Could not start serveragent on local computer. The
service did not return an error. This could be an internal
windows error. If problem persists, contact your system
administrator"
SqlserverAgent error log:
[000]SQLServerAgent could not be started (reason:
SQLServerAgent must be able to connect to SQLServer as
SysAdmin, but '(Unknown)' is not a member of the SysAdmin
role)
[298]SQLServer error: 3621，statement stopped。 [SQLSTATE
01000] (ConnIsLoginSysAdmin)
[298] SQLServer error: 535，2 datetime columns'
difference made runtime overflow。 [SQLSTATE 22003]
(ConnIsLoginSysAdmin)
SqlserverAgent service start account set to administrator
and in SqlserverAgent connection properties, i have
tried "sql server connection" use sql server and NT
authentication, but both failed to start the service.
thanks a lot.> SqlserverAgent service start account set to administrator
Is this start account a member of he local administrators group?
What security authentication is the SQL instance configured to use? Is it
Windows or Mixed? I'd set the service startup account for SQLServerAgent to
the local system account, and try to start the SQL Agent service.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"brooks" <anonymous@.discussions.microsoft.com> wrote in message
news:0b0701c3a8c3$ed09aff0$a101280a@.phx.gbl...
> Anyone can help me:
> I get windows events like this:
> "Could not start serveragent on local computer. The
> service did not return an error. This could be an internal
> windows error. If problem persists, contact your system
> administrator"
> SqlserverAgent error log:
> [000]SQLServerAgent could not be started (reason:
> SQLServerAgent must be able to connect to SQLServer as
> SysAdmin, but '(Unknown)' is not a member of the SysAdmin
> role)
> [298]SQLServer error: 3621，statement stopped。 [SQLSTATE
> 01000] (ConnIsLoginSysAdmin)
> [298] SQLServer error: 535，2 datetime columns'
> difference made runtime overflow。 [SQLSTATE 22003]
> (ConnIsLoginSysAdmin)
> SqlserverAgent service start account set to administrator
> and in SqlserverAgent connection properties, i have
> tried "sql server connection" use sql server and NT
> authentication, but both failed to start the service.
> thanks a lot.|||> > SqlserverAgent service start account set to administrator
> Is this start account a member of he local administrators group?
Also, is the BUILTIN\Administraors windows account's Login in SQL Server
member of the sysadmin server role?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:eB44wpOqDHA.1496@.TK2MSFTNGP11.phx.gbl...
> > SqlserverAgent service start account set to administrator
> Is this start account a member of he local administrators group?
> What security authentication is the SQL instance configured to use? Is it
> Windows or Mixed? I'd set the service startup account for SQLServerAgent
to
> the local system account, and try to start the SQL Agent service.
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "brooks" <anonymous@.discussions.microsoft.com> wrote in message
> news:0b0701c3a8c3$ed09aff0$a101280a@.phx.gbl...
> > Anyone can help me:
> >
> > I get windows events like this:
> > "Could not start serveragent on local computer. The
> > service did not return an error. This could be an internal
> > windows error. If problem persists, contact your system
> > administrator"
> >
> > SqlserverAgent error log:
> > [000]SQLServerAgent could not be started (reason:
> > SQLServerAgent must be able to connect to SQLServer as
> > SysAdmin, but '(Unknown)' is not a member of the SysAdmin
> > role)
> > [298]SQLServer error: 3621，statement stopped。 [SQLSTATE
> > 01000] (ConnIsLoginSysAdmin)
> > [298] SQLServer error: 535，2 datetime columns'
> > difference made runtime overflow。 [SQLSTATE 22003]
> > (ConnIsLoginSysAdmin)
> >
> > SqlserverAgent service start account set to administrator
> > and in SqlserverAgent connection properties, i have
> > tried "sql server connection" use sql server and NT
> > authentication, but both failed to start the service.
> >
> > thanks a lot.
>|||Thanks a lot for your warmhearted reply, i tried
administrator account, sa(in connection properties of
sqlserveragent service), and bulltin/administrators, the
started account both in the sysadmin group, but it can't
start the service.
sqlserver authentication set to mixed
>--Original Message--
>> SqlserverAgent service start account set to
administrator
>Is this start account a member of he local administrators
group?
>What security authentication is the SQL instance
configured to use? Is it
>Windows or Mixed? I'd set the service startup account for
SQLServerAgent to
>the local system account, and try to start the SQL Agent
service.
>--
>Linchi Shea
>linchi_shea@.NOSPAMml.com
>
>"brooks" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0b0701c3a8c3$ed09aff0$a101280a@.phx.gbl...
>> Anyone can help me:
>> I get windows events like this:
>> "Could not start serveragent on local computer. The
>> service did not return an error. This could be an
internal
>> windows error. If problem persists, contact your system
>> administrator"
>> SqlserverAgent error log:
>> [000]SQLServerAgent could not be started (reason:
>> SQLServerAgent must be able to connect to SQLServer as
>> SysAdmin, but '(Unknown)' is not a member of the
SysAdmin
>> role)
>> [298]SQLServer error: 3621，statement stopped。
[SQLSTATE
>> 01000] (ConnIsLoginSysAdmin)
>> [298] SQLServer error: 535，2 datetime columns'
>> difference made runtime overflow。 [SQLSTATE 22003]
>> (ConnIsLoginSysAdmin)
>> SqlserverAgent service start account set to
administrator
>> and in SqlserverAgent connection properties, i have
>> tried "sql server connection" use sql server and NT
>> authentication, but both failed to start the service.
>> thanks a lot.
>
>.
>|||As I mentioned in my other post, you need to verify that the startup
account's login in SQL Server is member of the sysadmin server role.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"brooks" <brooks_xiao@.hotmail.com> wrote in message
news:1f4801c3a8fa$22560920$a601280a@.phx.gbl...
> Thanks a lot for your warmhearted reply, i tried
> administrator account, sa(in connection properties of
> sqlserveragent service), and bulltin/administrators, the
> started account both in the sysadmin group, but it can't
> start the service.
> sqlserver authentication set to mixed
> >--Original Message--
> >> SqlserverAgent service start account set to
> administrator
> >
> >Is this start account a member of he local administrators
> group?
> >
> >What security authentication is the SQL instance
> configured to use? Is it
> >Windows or Mixed? I'd set the service startup account for
> SQLServerAgent to
> >the local system account, and try to start the SQL Agent
> service.
> >
> >--
> >Linchi Shea
> >linchi_shea@.NOSPAMml.com
> >
> >
> >"brooks" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0b0701c3a8c3$ed09aff0$a101280a@.phx.gbl...
> >> Anyone can help me:
> >>
> >> I get windows events like this:
> >> "Could not start serveragent on local computer. The
> >> service did not return an error. This could be an
> internal
> >> windows error. If problem persists, contact your system
> >> administrator"
> >>
> >> SqlserverAgent error log:
> >> [000]SQLServerAgent could not be started (reason:
> >> SQLServerAgent must be able to connect to SQLServer as
> >> SysAdmin, but '(Unknown)' is not a member of the
> SysAdmin
> >> role)
> >> [298]SQLServer error: 3621，statement stopped。
> [SQLSTATE
> >> 01000] (ConnIsLoginSysAdmin)
> >> [298] SQLServer error: 535，2 datetime columns'
> >> difference made runtime overflow。 [SQLSTATE 22003]
> >> (ConnIsLoginSysAdmin)
> >>
> >> SqlserverAgent service start account set to
> administrator
> >> and in SqlserverAgent connection properties, i have
> >> tried "sql server connection" use sql server and NT
> >> authentication, but both failed to start the service.
> >>
> >> thanks a lot.
> >
> >
> >.
> >|||Hi, Tibor Karaszi,thank you very much
I searched your articles in the group.sql...., but it
does not do any help, my problems is, i add a test account
(in NT administrators group), then add this to sql server
sysadmin group, but it could not make the agent service
start.
i think the following error messages is difference from
the others refered:
>> >> [298]SQLServer error: 3621，statement stopped。
>> [SQLSTATE
>> >> 01000] (ConnIsLoginSysAdmin)
>> >> [298] SQLServer error: 535，2 datetime columns'
>> >> difference made runtime overflow。 [SQLSTATE 22003]
>> >> (ConnIsLoginSysAdmin)
>> >>
thanks a lot for your help.|||You need to find out which Service Account Agent is using (EM, Management,
Properties).
Then you need to go to the security tab in SQL Server, logins, and for that
account (or a group that the account is a member of), edit it and on the
server roles tab, make it member of syadmins.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"brooks" <brooks_xiao@.hotmail.com> wrote in message
news:0e6501c3a8fd$49b3ac90$a101280a@.phx.gbl...
>
> Hi, Tibor Karaszi,thank you very much
> I searched your articles in the group.sql...., but it
> does not do any help, my problems is, i add a test account
> (in NT administrators group), then add this to sql server
> sysadmin group, but it could not make the agent service
> start.
> i think the following error messages is difference from
> the others refered:
> >> >> [298]SQLServer error: 3621，statement stopped。
> >> [SQLSTATE
> >> >> 01000] (ConnIsLoginSysAdmin)
> >> >> [298] SQLServer error: 535，2 datetime columns'
> >> >> difference made runtime overflow。 [SQLSTATE 22003]
> >> >> (ConnIsLoginSysAdmin)
> >> >>
> thanks a lot for your help.

PLS HELP! SQLServerAgent Service could not be started

Hi,
SQLServerAgent Service stopped and could not be re-startated. I kept getting
the following meassge:
"Could not start serveragent on local computer. The service did not return
an error. This could be an internal windows error. If problem persists,
contact your system administrator"
It has been working before it stopped and couldn't restart after server
re-boot. It is using the same domain account with Admin rights as the
SQLServer Service which is has restarted and running.
This is very urgent please because it is a Production Server.
Thanks for your help.
Egbon.Anything in the Agent errorlog?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Egbon" <vnjowusi@.gosps.com> wrote in message news:eFx2%23dhmDHA.1084@.tk2msftngp13.phx.gbl...
> Hi,
> SQLServerAgent Service stopped and could not be re-startated. I kept getting
> the following meassge:
> "Could not start serveragent on local computer. The service did not return
> an error. This could be an internal windows error. If problem persists,
> contact your system administrator"
> It has been working before it stopped and couldn't restart after server
> re-boot. It is using the same domain account with Admin rights as the
> SQLServer Service which is has restarted and running.
> This is very urgent please because it is a Production Server.
> Thanks for your help.
> Egbon.
>
>|||I have this message in the Application event log.:
"SQLServerAgent could not be started (reason: Unable to connect to server
'(local)'; SQLServerAgent cannot start). "
Thanks
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:u0eAUfhmDHA.2404@.TK2MSFTNGP12.phx.gbl...
> Anything in the Agent errorlog?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Egbon" <vnjowusi@.gosps.com> wrote in message
news:eFx2%23dhmDHA.1084@.tk2msftngp13.phx.gbl...
> > Hi,
> > SQLServerAgent Service stopped and could not be re-startated. I kept
getting
> > the following meassge:
> >
> > "Could not start serveragent on local computer. The service did not
return
> > an error. This could be an internal windows error. If problem persists,
> > contact your system administrator"
> >
> > It has been working before it stopped and couldn't restart after server
> > re-boot. It is using the same domain account with Admin rights as the
> > SQLServer Service which is has restarted and running.
> > This is very urgent please because it is a Production Server.
> >
> > Thanks for your help.
> >
> > Egbon.
> >
> >
> >
>|||I think Tibor is talking about the SQL Server Agent error log and not the
Windows Application event log...
look at this topic in BOL...
'Using the SQL Server Agent Error Log'
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Egbon" <vnjowusi@.gosps.com> wrote in message
news:OSSuabimDHA.1960@.TK2MSFTNGP12.phx.gbl...
> I have this message in the Application event log.:
> "SQLServerAgent could not be started (reason: Unable to connect to server
> '(local)'; SQLServerAgent cannot start). "
> Thanks
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:u0eAUfhmDHA.2404@.TK2MSFTNGP12.phx.gbl...
> > Anything in the Agent errorlog?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Egbon" <vnjowusi@.gosps.com> wrote in message
> news:eFx2%23dhmDHA.1084@.tk2msftngp13.phx.gbl...
> > > Hi,
> > > SQLServerAgent Service stopped and could not be re-startated. I kept
> getting
> > > the following meassge:
> > >
> > > "Could not start serveragent on local computer. The service did not
> return
> > > an error. This could be an internal windows error. If problem
persists,
> > > contact your system administrator"
> > >
> > > It has been working before it stopped and couldn't restart after
server
> > > re-boot. It is using the same domain account with Admin rights as the
> > > SQLServer Service which is has restarted and running.
> > > This is very urgent please because it is a Production Server.
> > >
> > > Thanks for your help.
> > >
> > > Egbon.
> > >
> > >
> > >
> >
> >
>

Please suggest me any helpful article on Sql Server 2005 Reporting service

I am working on yukon.

can anyone suggest me any helpful material about the same

Sorry for simple query.

Aa lot of material was found on msdn.Microsoft.com

|||

same can be found on http://asp.net

or by searching on google.com

please I need this!

hi all

Any body knows where I can get the SQL Versión: 8.0 Service Pack 3 ?.
this SP3 allow create, edit, delete tables from VS.NET 2003.

or I'm wrong?

please anybody?SQL Server 2000 SP3 is downloadable from the MS SQL server website.

Please Help: SQL service cannot start

Hi
I created an account to run MS SQL services and give logon
locally and log on as service permission. Installation was
successful with this service account.
After a few hours later, SQL Agent service hang and cannot
restart. Error message is applicaion fail to initialize.
Therefore MS SQL server service also cannot restart.
When I use LocalSystem to start SQL services, everything
is fine.But I need to use service account because of
security reasons.
When I login with this service account, it immedialy
logout. I cannot login at all.
MS SQL 2000 standard edition with SP3.
Any suggestion is appreciated.
ThanksHi,
Add the user to "power users" group and try.
Thanks
Hari
MCDBA
"Ben" <anonymous@.discussions.microsoft.com> wrote in message
news:00a101c3b6a0$e3136eb0$a301280a@.phx.gbl...
> Hi
> I created an account to run MS SQL services and give logon
> locally and log on as service permission. Installation was
> successful with this service account.
> After a few hours later, SQL Agent service hang and cannot
> restart. Error message is applicaion fail to initialize.
> Therefore MS SQL server service also cannot restart.
> When I use LocalSystem to start SQL services, everything
> is fine.But I need to use service account because of
> security reasons.
> When I login with this service account, it immedialy
> logout. I cannot login at all.
> MS SQL 2000 standard edition with SP3.
> Any suggestion is appreciated.
> Thanks
>|||If when you login using thi sacount your session is terminated there is =clearly something wrong withthe account in some way. have you checked =the event log after your failed loginattempt - any error message =displayed?
Mike john
"Ben" <anonymous@.discussions.microsoft.com> wrote in message =news:00a101c3b6a0$e3136eb0$a301280a@.phx.gbl...
> Hi
> > I created an account to run MS SQL services and give logon > locally and log on as service permission. Installation was > successful with this service account.
> > After a few hours later, SQL Agent service hang and cannot > restart. Error message is applicaion fail to initialize.
> Therefore MS SQL server service also cannot restart.
> When I use LocalSystem to start SQL services, everything > is fine.But I need to use service account because of > security reasons.
> > When I login with this service account, it immedialy > logout. I cannot login at all. > > MS SQL 2000 standard edition with SP3.
> > Any suggestion is appreciated.
> > Thanks
> >|||Hi ben,
If i understand properly, this is what you need... please
refer the article -
http://support.microsoft.com/default.aspx?kbid=283811
it clearly says what permissions u need for the os level
id to start sqlserver.
but to have a short cut - please have an id in the
administrator's group to start mssql server if u do not
want to start with localsystem
regards,
bharath
mcdba
>--Original Message--
>Hi,
>Add the user to "power users" group and try.
>Thanks
>Hari
>MCDBA
>
>"Ben" <anonymous@.discussions.microsoft.com> wrote in
message
>news:00a101c3b6a0$e3136eb0$a301280a@.phx.gbl...
>> Hi
>> I created an account to run MS SQL services and give
logon
>> locally and log on as service permission. Installation
was
>> successful with this service account.
>> After a few hours later, SQL Agent service hang and
cannot
>> restart. Error message is applicaion fail to initialize.
>> Therefore MS SQL server service also cannot restart.
>> When I use LocalSystem to start SQL services, everything
>> is fine.But I need to use service account because of
>> security reasons.
>> When I login with this service account, it immedialy
>> logout. I cannot login at all.
>> MS SQL 2000 standard edition with SP3.
>> Any suggestion is appreciated.
>> Thanks
>>
>
>.
>

Please help: Can not start Report Server Web Service for 2000

I have spent several days trying to figure it out, I am out of options now.
I have a SQL Server 2000 Standard, patched with SP4, installed on Windows
Server 2003 SP1, I installed Report Server 2000 Standard edition (Report
Server and Report Manager components). At the end of installation, I got a
warning saying that I have to manually initialize Report server for the first
time for it to work. But when I tried somethng like
http://localhost/ReportServer, I received "Service Unavailable". I have made
sure ASP .Net v1.1.4322 is registered with IIS (6.0 SP1), use Network Service
account for ASP .Net, etc. From the event log, itsaid W3Wp encounterd an
application exeption, there was no log for ReportServer.
I really don't know what if going on here as it worked perfectly on another
Windows Server 2003, the difference is that I has Visual Studio .Net 2003
installed on that machine, but I think VS is only necessary if you want to
install Report Designer.
I read through a lot of newsgroup, tried all those suggestions, but still
not be able to fix it.
Thanks in advance for any info.
did you try using RSactivate utility to manually activate report
server?
If yes is it successful, if not what is the error message
|||Yes I did try it, it failed. The message was like " the report server web
service has not generated a public key".
I checked ReportServer database keys table, there is an entry for client =
1, from what I read, it is supposed to have an entry for client = 0 once
http://localhost/ReportServer is invoked or manually activated.
I think the problem is that Report Server web service somehow crashed, that
would explain whay there was no log file like ReportServer_timestamp.txt.
Now I am suspecting it may be because of the patches, Win2003 SP1 or SQL
2000 SP4. The another Win2003 worked has no SP1, and SQL 2000 is SP3.
Unfortunatlly right now rolling back is not an option.
"Shadow" wrote:

> did you try using RSactivate utility to manually activate report
> server?
> If yes is it successful, if not what is the error message
>

Please help: Can not start Report Server Web Service for 2000

I have spent several days trying to figure it out, I am out of options now.
I have a SQL Server 2000 Standard, patched with SP4, installed on Windows
Server 2003 SP1, I installed Report Server 2000 Standard edition (Report
Server and Report Manager components). At the end of installation, I got a
warning saying that I have to manually initialize Report server for the firs
t
time for it to work. But when I tried somethng like
http://localhost/ReportServer, I received "Service Unavailable". I have made
sure ASP .Net v1.1.4322 is registered with IIS (6.0 SP1), use Network Servic
e
account for ASP .Net, etc. From the event log, itsaid W3Wp encounterd an
application exeption, there was no log for ReportServer.
I really don't know what if going on here as it worked perfectly on another
Windows Server 2003, the difference is that I has Visual Studio .Net 2003
installed on that machine, but I think VS is only necessary if you want to
install Report Designer.
I read through a lot of newsgroup, tried all those suggestions, but still
not be able to fix it.
Thanks in advance for any info.did you try using RSactivate utility to manually activate report
server?
If yes is it successful, if not what is the error message|||Yes I did try it, it failed. The message was like " the report server web
service has not generated a public key".
I checked ReportServer database keys table, there is an entry for client =
1, from what I read, it is supposed to have an entry for client = 0 once
http://localhost/ReportServer is invoked or manually activated.
I think the problem is that Report Server web service somehow crashed, that
would explain whay there was no log file like ReportServer_timestamp.txt.
Now I am suspecting it may be because of the patches, Win2003 SP1 or SQL
2000 SP4. The another Win2003 worked has no SP1, and SQL 2000 is SP3.
Unfortunatlly right now rolling back is not an option.
"Shadow" wrote:

> did you try using RSactivate utility to manually activate report
> server?
> If yes is it successful, if not what is the error message
>

Please help: Can not start Report Server Web Service for 2000

I have spent several days trying to figure it out, I am out of options now.
I have a SQL Server 2000 Standard, patched with SP4, installed on Windows
Server 2003 SP1, I installed Report Server 2000 Standard edition (Report
Server and Report Manager components). At the end of installation, I got a
warning saying that I have to manually initialize Report server for the first
time for it to work. But when I tried somethng like
http://localhost/ReportServer, I received "Service Unavailable". I have made
sure ASP .Net v1.1.4322 is registered with IIS (6.0 SP1), use Network Service
account for ASP .Net, etc. From the event log, itsaid W3Wp encounterd an
application exeption, there was no log for ReportServer.
I really don't know what if going on here as it worked perfectly on another
Windows Server 2003, the difference is that I has Visual Studio .Net 2003
installed on that machine, but I think VS is only necessary if you want to
install Report Designer.
I read through a lot of newsgroup, tried all those suggestions, but still
not be able to fix it.
Thanks in advance for any info.did you try using RSactivate utility to manually activate report
server?
If yes is it successful, if not what is the error message|||Yes I did try it, it failed. The message was like " the report server web
service has not generated a public key".
I checked ReportServer database keys table, there is an entry for client =1, from what I read, it is supposed to have an entry for client = 0 once
http://localhost/ReportServer is invoked or manually activated.
I think the problem is that Report Server web service somehow crashed, that
would explain whay there was no log file like ReportServer_timestamp.txt.
Now I am suspecting it may be because of the patches, Win2003 SP1 or SQL
2000 SP4. The another Win2003 worked has no SP1, and SQL 2000 is SP3.
Unfortunatlly right now rolling back is not an option.
"Shadow" wrote:
> did you try using RSactivate utility to manually activate report
> server?
> If yes is it successful, if not what is the error message
>

Please Help! Service Pack update Cannot Verify User with SA login

I have SQL 2000 that has Service Pack 3 installed. So I installed Analysis
Services & Manager,Decision Service Objects and Client Components. Afterwards
I tried to run the update that MS put out for Analysis Services and in the
install you have two options: Use user logged on authentication or use SQL
authentication and provide the SA password. I do this and it always says it
could not verify user even though I have even changed the password and know
that it is correct. I am running SQL in Mixed Mode Authentication if this
makes a difference?
Also, Service Pack 3 was already installed on this server for SQL, But
Analysis Services and Manager was not. So I installed them. So Do I still
need to run the update. There are 3 different files for updateing SQL. 1.
Database Components
2. Analysis Services
3. Desktop Engine
But I get the error that I mentioned when I run the analysis update!
"Jay" wrote:

> I have SQL 2000 that has Service Pack 3 installed. So I installed Analysis
> Services & Manager,Decision Service Objects and Client Components. Afterwards
> I tried to run the update that MS put out for Analysis Services and in the
> install you have two options: Use user logged on authentication or use SQL
> authentication and provide the SA password. I do this and it always says it
> could not verify user even though I have even changed the password and know
> that it is correct. I am running SQL in Mixed Mode Authentication if this
> makes a difference?

Please Help! Service Pack update Cannot Verify User with SA login

I have SQL 2000 that has Service Pack 3 installed. So I installed Analysis
Services & Manager,Decision Service Objects and Client Components. Afterward
s
I tried to run the update that MS put out for Analysis Services and in the
install you have two options: Use user logged on authentication or use SQL
authentication and provide the SA password. I do this and it always says it
could not verify user even though I have even changed the password and know
that it is correct. I am running SQL in Mixed Mode Authentication if this
makes a difference?Also, Service Pack 3 was already installed on this server for SQL, But
Analysis Services and Manager was not. So I installed them. So Do I still
need to run the update. There are 3 different files for updateing SQL. 1.
Database Components
2. Analysis Services
3. Desktop Engine
But I get the error that I mentioned when I run the analysis update!
"Jay" wrote:

> I have SQL 2000 that has Service Pack 3 installed. So I installed Analysis
> Services & Manager,Decision Service Objects and Client Components. Afterwa
rds
> I tried to run the update that MS put out for Analysis Services and in the
> install you have two options: Use user logged on authentication or use SQL
> authentication and provide the SA password. I do this and it always says i
t
> could not verify user even though I have even changed the password and kno
w
> that it is correct. I am running SQL in Mixed Mode Authentication if this
> makes a difference?

Please Help! Service Pack update Cannot Verify User with SA login

I have SQL 2000 that has Service Pack 3 installed. So I installed Analysis
Services & Manager,Decision Service Objects and Client Components. Afterwards
I tried to run the update that MS put out for Analysis Services and in the
install you have two options: Use user logged on authentication or use SQL
authentication and provide the SA password. I do this and it always says it
could not verify user even though I have even changed the password and know
that it is correct. I am running SQL in Mixed Mode Authentication if this
makes a difference?Also, Service Pack 3 was already installed on this server for SQL, But
Analysis Services and Manager was not. So I installed them. So Do I still
need to run the update. There are 3 different files for updateing SQL. 1.
Database Components
2. Analysis Services
3. Desktop Engine
But I get the error that I mentioned when I run the analysis update!
"Jay" wrote:
> I have SQL 2000 that has Service Pack 3 installed. So I installed Analysis
> Services & Manager,Decision Service Objects and Client Components. Afterwards
> I tried to run the update that MS put out for Analysis Services and in the
> install you have two options: Use user logged on authentication or use SQL
> authentication and provide the SA password. I do this and it always says it
> could not verify user even though I have even changed the password and know
> that it is correct. I am running SQL in Mixed Mode Authentication if this
> makes a difference?