SQL tool for MSDE: system

Showing posts with label system. Show all posts

Tuesday, March 20, 2012

Alert fails to notify Operator (SQL Server 2005)

I am trying to set up a new alert which will notify me when a particular
error is raised. I have configured the SQL Server Agent Alert System to use
Database Mail, and confirmed that this works with Job notifications (I have
successfully notified myself by email on completion of a job).
I have added a new user-defined error with sp_addmessage and have created an
alert to notify me when this error is raised. I have then generated the error
with the raiserror statement. The error message is returned in the query
result window, but I do not get the email.Hi Jon
"Jon Ley" wrote:
> I am trying to set up a new alert which will notify me when a particular
> error is raised. I have configured the SQL Server Agent Alert System to use
> Database Mail, and confirmed that this works with Job notifications (I have
> successfully notified myself by email on completion of a job).
> I have added a new user-defined error with sp_addmessage and have created an
> alert to notify me when this error is raised. I have then generated the error
> with the raiserror statement. The error message is returned in the query
> result window, but I do not get the email.
Is there anything in the SQL Server error log?
If you have email blocking as part of the antivirus suite you may have
exceptions logged there.
John|||How did you define the alert notification? By error number?
--
TheSQLGuru
President
Indicium Resources, Inc.
"Jon Ley" <JonLey@.discussions.microsoft.com> wrote in message
news:29B92338-9259-489C-BFBA-C3D75B72B901@.microsoft.com...
>I am trying to set up a new alert which will notify me when a particular
> error is raised. I have configured the SQL Server Agent Alert System to
> use
> Database Mail, and confirmed that this works with Job notifications (I
> have
> successfully notified myself by email on completion of a job).
> I have added a new user-defined error with sp_addmessage and have created
> an
> alert to notify me when this error is raised. I have then generated the
> error
> with the raiserror statement. The error message is returned in the query
> result window, but I do not get the email.|||On May 19, 1:18 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> How did you define the alert notification? By error number?
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Jon Ley" <Jon...@.discussions.microsoft.com> wrote in message
> news:29B92338-9259-489C-BFBA-C3D75B72B901@.microsoft.com...
>
> >I am trying to set up a new alert which will notify me when a particular
> > error is raised. I have configured the SQL Server Agent Alert System to
> > use
> > Database Mail, and confirmed that this works with Job notifications (I
> > have
> > successfully notified myself by email on completion of a job).
> > I have added a new user-defined error with sp_addmessage and have created
> > an
> > alert to notify me when this error is raised. I have then generated the
> > error
> > with the raiserror statement. The error message is returned in the query
> > result window, but I do not get the email.- Hide quoted text -
> - Show quoted text -
Are you on service pak 2? There are problems with database mail that
are corrected with service SP2.
Worth a shot anyway.
Kristina|||John,
Thanks for your response. There does not appear to be anything in the error
log.
Jon.
"John Bell" wrote:
> Hi Jon
> "Jon Ley" wrote:
> > I am trying to set up a new alert which will notify me when a particular
> > error is raised. I have configured the SQL Server Agent Alert System to use
> > Database Mail, and confirmed that this works with Job notifications (I have
> > successfully notified myself by email on completion of a job).
> >
> > I have added a new user-defined error with sp_addmessage and have created an
> > alert to notify me when this error is raised. I have then generated the error
> > with the raiserror statement. The error message is returned in the query
> > result window, but I do not get the email.
> Is there anything in the SQL Server error log?
> If you have email blocking as part of the antivirus suite you may have
> exceptions logged there.
> John|||TheSQLGuru: Yes, I defined the alert notification by error number (50001)
Kristina: I have just applied sp2 and retried. Still not working.
Regards,
Jon.
"Kristina" wrote:
> On May 19, 1:18 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> > How did you define the alert notification? By error number?
> >
> > --
> > TheSQLGuru
> > President
> > Indicium Resources, Inc.
> >
> > "Jon Ley" <Jon...@.discussions.microsoft.com> wrote in message
> >
> > news:29B92338-9259-489C-BFBA-C3D75B72B901@.microsoft.com...
> >
> >
> >
> > >I am trying to set up a new alert which will notify me when a particular
> > > error is raised. I have configured the SQL Server Agent Alert System to
> > > use
> > > Database Mail, and confirmed that this works with Job notifications (I
> > > have
> > > successfully notified myself by email on completion of a job).
> >
> > > I have added a new user-defined error with sp_addmessage and have created
> > > an
> > > alert to notify me when this error is raised. I have then generated the
> > > error
> > > with the raiserror statement. The error message is returned in the query
> > > result window, but I do not get the email.- Hide quoted text -
> >
> > - Show quoted text -
> Are you on service pak 2? There are problems with database mail that
> are corrected with service SP2.
> Worth a shot anyway.
> Kristina
>|||Hi Jon
"Jon Ley" wrote:
> John,
> Thanks for your response. There does not appear to be anything in the error
> log.
> Jon.
>
Under the Alert Properties, there is a history dialog which should show you
if the alert has been triggered. If this is not updated when you cause the
error then you are not satisfying the criteria described in the remarks
section of http://msdn2.microsoft.com/en-us/library/ms189531.aspx. Check that
the errors appears in the windows event log.
John|||Thanks, John, I have got it working now using the WITH LOG option on the
RAISERROR statement. This appears to be a change from SQL Server 2000 - I had
exactly the same alert configured with a severity level of 10 and without it
being logged, and it would quite happily notify me by email when this error
was raised.
Regards,
Jon Ley.
"John Bell" wrote:
> Hi Jon
> "Jon Ley" wrote:
> > John,
> >
> > Thanks for your response. There does not appear to be anything in the error
> > log.
> >
> > Jon.
> >
> Under the Alert Properties, there is a history dialog which should show you
> if the alert has been triggered. If this is not updated when you cause the
> error then you are not satisfying the criteria described in the remarks
> section of http://msdn2.microsoft.com/en-us/library/ms189531.aspx. Check that
> the errors appears in the windows event log.
> John|||Did you configure the alert to always log to windows eventlog? You can check this in sys.messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jon Ley" <JonLey@.discussions.microsoft.com> wrote in message
news:54D9247D-1915-41DB-AA98-09D1E1E7B4DF@.microsoft.com...
> Thanks, John, I have got it working now using the WITH LOG option on the
> RAISERROR statement. This appears to be a change from SQL Server 2000 - I had
> exactly the same alert configured with a severity level of 10 and without it
> being logged, and it would quite happily notify me by email when this error
> was raised.
> Regards,
> Jon Ley.
> "John Bell" wrote:
>> Hi Jon
>> "Jon Ley" wrote:
>> > John,
>> >
>> > Thanks for your response. There does not appear to be anything in the error
>> > log.
>> >
>> > Jon.
>> >
>> Under the Alert Properties, there is a history dialog which should show you
>> if the alert has been triggered. If this is not updated when you cause the
>> error then you are not satisfying the criteria described in the remarks
>> section of http://msdn2.microsoft.com/en-us/library/ms189531.aspx. Check that
>> the errors appears in the windows event log.
>> Johnsql

Friday, February 24, 2012

Agent failures and custom alerting

I need to have a reliable alerting system for my merge replications I have setup on my MSSQL 2005 server.

The problem is that the build-in alert system the 'agent failure' alert only triggers when 'all databases' for that specific alert is selected. There is no failure alert triggered when I select a database and force a failure. The 'agent succes' alert does never get triggered at all.

I need an reliable succes and failure alert per database because I need to do specific actions per database.

Can someone help me out here?

I'm thinking of building my own alerting system if the help here is insufficient. In that case I need to know in what tables to look. Maybe someone can give me some pointers?

Thanks anyway,

Edward

Have you tried through "Configure Replication Alerts" dialog?

Launch Sqlmonitor.exe --> Expanding publisher and publications --> L-Click a publication --> Warning and Agents Page > Configure Replication Alerts Dialog

It can configure all kinds of alerts, including agent success alert etc. You can try.

Thanks

Yunjing

|||

Thanks for your answer. I know how to configure alerts. Thats not the problem.
The problem is that the 'failure' alert only gets triggered when you select 'all databases' and that the 'Success' alert not gets triggered at all.

I need to trigger the 'Failure' alert per database

I need to trigger the 'success' alert per database

It aint working.

|||

Alert will be triggered per event. It can not be configured per database.

For example, push merge agent run on distributor and pull merge agent run on subscriber. If you set aleart per database, like per subscription db, then some error happened on distribution db won't be catched. It doesn't make sense.

As for some 'Success' alert, replication agent cut some alerts for SQL 2005. LIke if you configure alert for 'Replication Agent Success', these alerts will not be triggered, since some customers complained that too many default alerts are triggered, even for success agent run, and they don't care about success but failure and critical performance etc only.

In the next version of SQL Server, we will address this issue and provide better solution.

Hope the above information will help.

Thanks

Yunjing

|||

Hi Yunjing

Thanks for your help. As you can imagine i'm not really helped. The next version of MSSQL will probably out in 4 years or something.

The strange thing is that everything works fine if the 'merge replication' is NOT in continious mode. Unfortunately this is not the desirable mode.

|||

Hi Yunjing

I was wondering (because of the SP2 update last week) if this issue is adressed?

I cross my fingers..

Thanks anyway

Agent failures and custom alerting

I need to have a reliable alerting system for my merge replications I have setup on my MSSQL 2005 server.

I need an reliable succes and failure alert per database because I need to do specific actions per database.

Can someone help me out here?

I'm thinking of building my own alerting system if the help here is insufficient. In that case I need to know in what tables to look. Maybe someone can give me some pointers?

Thanks anyway,

Edward

Have you tried through "Configure Replication Alerts" dialog?

Launch Sqlmonitor.exe --> Expanding publisher and publications --> L-Click a publication --> Warning and Agents Page > Configure Replication Alerts Dialog

It can configure all kinds of alerts, including agent success alert etc. You can try.

Thanks

Yunjing

|||

I need to trigger the 'Failure' alert per database

I need to trigger the 'success' alert per database

It aint working.

|||

Alert will be triggered per event. It can not be configured per database.

In the next version of SQL Server, we will address this issue and provide better solution.

Hope the above information will help.

Thanks

Yunjing

|||

Hi Yunjing

Thanks for your help. As you can imagine i'm not really helped. The next version of MSSQL will probably out in 4 years or something.

The strange thing is that everything works fine if the 'merge replication' is NOT in continious mode. Unfortunately this is not the desirable mode.

|||

Hi Yunjing

I was wondering (because of the SP2 update last week) if this issue is adressed?

I cross my fingers..

Thanks anyway

Ageing from system date by number of days

Can anyone help with how I write a script that takes a balance and then age
it by its transaction dates into 30, 60 90 days etc?
My script is as follows and the "AS2 on the 2nd line is coming out as
incorrect syntax:
SELECT
GoodsValueInAccountCurrency, TransactionDate,
DATEDIFF(MONTH, TransactionDate, GETDATE()) AS no_of_days_since,
SUM(GoodsValueInAccountCurrency) AS THIRTY WHERE (no_of_days_since BETWEEN 1
AND 30) AND
SUM(GoodsValueInAccountCurrency) AS SIXTY WHERE (no_of_days_since between 31
and 60) AND
SUM(GoodsValueInAccountCurrency) AS NINETY WHERE (no_of_days_since between
61 and 90)AND
SUM(GoodsValueInAccountCurrency) AS MORE WHERE (no_of_days_since > 90)
FROM PLPostedSupplierTran
"WendyUK" wrote:

> Can anyone help with how I write a script that takes a balance and then age
> it by its transaction dates into 30, 60 90 days etc?
|||Wendy,
A couple of things:
1 - DATEDIFF (MONTH... ) does not give you what you want since February 28
and March 1 are one month apart. Use DAY instead of MONTH.
2 - WHERE appears after the FROM and only 1 per query. For your columns,
read about the CASE statement. You will need something like the following
untested code:
SELECT GoodsValueInAccountCurrency, TransactionDate,
SUM (CASE ((DATEDIFF(DAY,TransactionDate, GETDATE()) - 1) / 30)
WHEN(0) THEN GoodsValueInAccountCurrency
ELSE 0
END ) AS THIRTY,
SUM (CASE ((DATEDIFF(DAY,TransactionDate, GETDATE()) - 1) / 30)
WHEN(1) THEN GoodsValueInAccountCurrency
ELSE 0
END ) AS SIXTY,
. . . etc
RLF
"WendyUK" <WendyUK@.discussions.microsoft.com> wrote in message
news:681A829D-F604-41D0-A485-B9B92132180C@.microsoft.com...[vbcol=seagreen]
> My script is as follows and the "AS2 on the 2nd line is coming out as
> incorrect syntax:
> SELECT
> GoodsValueInAccountCurrency, TransactionDate,
> DATEDIFF(MONTH, TransactionDate, GETDATE()) AS no_of_days_since,
> SUM(GoodsValueInAccountCurrency) AS THIRTY WHERE (no_of_days_since BETWEEN
> 1
> AND 30) AND
> SUM(GoodsValueInAccountCurrency) AS SIXTY WHERE (no_of_days_since between
> 31
> and 60) AND
> SUM(GoodsValueInAccountCurrency) AS NINETY WHERE (no_of_days_since between
> 61 and 90)AND
> SUM(GoodsValueInAccountCurrency) AS MORE WHERE (no_of_days_since > 90)
>
> FROM PLPostedSupplierTran
>
> "WendyUK" wrote:

Age calucation

Hai frnds can u tell me the simple syntax using one example how caluclate the aage using system date.i am hanving only DOB field in my table.

Quote:

Originally Posted by chaitanyacrn

Hai frnds can u tell me the simple syntax using one example how caluclate the aage using system date.i am hanving only DOB field in my table.

select datediff(year,DOB,getdate()) from table

Sunday, February 19, 2012

after system restore the agent does not start

Hi group,
we have a server that had a server restore (near mirror, the system admin had to work out a few kinks). After the restore, the sql server agent does not start. When trying start it in service, it appears to be trying to start, but at the end the MMC gives a message
Could not start the SQLSERVERAGENT service on local computer. The service did not return an error. This could be an internal error or an internal service error.
Window event log shows the following error:
The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. The following information is part of the event: 17050, initerrlog: Could not open error log file 'e:\MSSQL\log\ERRORLOG'. Operating system error = 32(The process cannot access the file because it is being used by another process.).
Can someone offer the fix? Thanks.
The following is the configuration:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
Quentin
The log mentioned, is the normal name for the SQL Server error log... It looks like SQL is trying to open it a second time, (as if you have 2 instances of SQL Server trying to write to the same log file)... THis can be changed in SEM startup properties...
However it might be possible that your SQL Agent is trying to open the log ( in SEM right click SQL Agent and go to properties to discover the log name it is using..)
All in all it looks to me that the error you are showing is not related to the SQL Agent not starting...Ensure the agent is using an NT login that is a SQL Server administrator.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's community of SQL Server professionals.
www.sqlpass.org
"Quentin Ran" <ab@.who.com> wrote in message news:OwDGnstQEHA.1960@.TK2MSFTNGP10.phx.gbl...
Hi group,
we have a server that had a server restore (near mirror, the system admin had to work out a few kinks). After the restore, the sql server agent does not start. When trying start it in service, it appears to be trying to start, but at the end the MMC gives a message
Could not start the SQLSERVERAGENT service on local computer. The service did not return an error. This could be an internal error or an internal service error.
Window event log shows the following error:
The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. The following information is part of the event: 17050, initerrlog: Could not open error log file 'e:\MSSQL\log\ERRORLOG'. Operating system error = 32(The process cannot access the file because it is being used by another process.).
Can someone offer the fix? Thanks.
The following is the configuration:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
Quentin
|||>>The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer.
sounds like the sqlagent thinks it's running on a different computer.
does this restored server have a different name than the original
server?
[vbcol=seagreen]
system error = 32(The process cannot access the file because it is being
used by another process.).
this, as the other poster pointed out, sounds like something is using
that log file. that log file is for sqlserver. you should specify a
different log file for the agent, generally should be something like
e:\mssql\log\sqlagent.out
Quentin Ran wrote:

> Hi group, we have a server that had a server restore (near mirror, the
> system admin had to work out a few kinks). After the restore, the sql
> server agent does not start. When trying start it in service, it
> appears to be trying to start, but at the end the MMC gives a
> message Could not start the SQLSERVERAGENT service on local computer.
> The service did not return an error. This could be an internal error
> or an internal service error. Window event log shows the following
> error: The description for Event ID ( 17055 ) in Source ( MSSQLSERVER
> ) cannot be found. The local computer may not have the necessary
> registry information or message DLL files to display messages from a
> remote computer. The following information is part of the event:
> 17050, initerrlog: Could not open error log file
> 'e:\MSSQL\log\ERRORLOG'. Operating system error = 32(The process
> cannot access the file because it is being used by another
> process.). Can someone offer the fix? Thanks. The following is the
> configuration: Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack
> 3) Quentin
|||Thanks for both responses.
The restored server has exactly the same name as before. SQL Server Agent
is running off a server admin account.
SQL Server Service uses the error log file e:\MSSQL\log\ERRORLOG, and SQL
Server Agent Service uses the error log file E:\MSSQL\LOG\SQLAGENT.OUT. The
server service log seems to work at least half way -- when the server
service starts, it records what it was doing like opening the databases. In
fact it even recorded a restore of a database. The fact that it did not
record any failure of launching the server agent may be due to it never was
asked to do so -- Windows may not know to direct such event there. The
agent log actually also contains some entries after the starting of the
server service, but not those ones that agent service start failed.
Any additional comments?
Quentin
"ch" <ch@.dontemailme.com> wrote in message
news:40B4980C.B82ABDFE@.dontemailme.com...
> message DLL files to display messages from a remote computer.
> sounds like the sqlagent thinks it's running on a different computer.
> does this restored server have a different name than the original
> server?
>
> system error = 32(The process cannot access the file because it is being
> used by another process.).
> this, as the other poster pointed out, sounds like something is using
> that log file. that log file is for sqlserver. you should specify a
> different log file for the agent, generally should be something like
> e:\mssql\log\sqlagent.out
>
>
> Quentin Ran wrote:
>
|||maybe there are some registry settings incorrect due to the restore of the
server.
try this.
create an nt account that is local admin on the server.
using enterprise manager, change the sqlserver and sqlagent services to use that
account.
restart server. see if agent starts properly.
now go back using enterprise manager and reset your sqlserver and sqlagent
accounts to whatever account(s) they were using before.
restart server. see if agent starts properly.
if that doesn't work, see if you can get them to run properly using the system
account for the two services.
be sure to use enterprise manager to make changes to the accounts the services
use.
Quentin Ran wrote:
[vbcol=seagreen]
> Thanks for both responses.
> The restored server has exactly the same name as before. SQL Server Agent
> is running off a server admin account.
> SQL Server Service uses the error log file e:\MSSQL\log\ERRORLOG, and SQL
> Server Agent Service uses the error log file E:\MSSQL\LOG\SQLAGENT.OUT. The
> server service log seems to work at least half way -- when the server
> service starts, it records what it was doing like opening the databases. In
> fact it even recorded a restore of a database. The fact that it did not
> record any failure of launching the server agent may be due to it never was
> asked to do so -- Windows may not know to direct such event there. The
> agent log actually also contains some entries after the starting of the
> server service, but not those ones that agent service start failed.
> Any additional comments?
> Quentin
> "ch" <ch@.dontemailme.com> wrote in message
> news:40B4980C.B82ABDFE@.dontemailme.com...
|||Neither a local admin account nor the system account would start them.
There must be wrong registry settings, and we don't know which ones and the
correct values.
"ch" <ch@.dontemailme.com> wrote in message
news:40B4BCFB.1AF8E97B@.dontemailme.com...
> maybe there are some registry settings incorrect due to the restore of the
> server.
> try this.
> create an nt account that is local admin on the server.
> using enterprise manager, change the sqlserver and sqlagent services to
use that
> account.
> restart server. see if agent starts properly.
> now go back using enterprise manager and reset your sqlserver and sqlagent
> accounts to whatever account(s) they were using before.
> restart server. see if agent starts properly.
> if that doesn't work, see if you can get them to run properly using the
system
> account for the two services.
> be sure to use enterprise manager to make changes to the accounts the
services[vbcol=seagreen]
> use.
>
> Quentin Ran wrote:
Agent[vbcol=seagreen]
SQL[vbcol=seagreen]
The[vbcol=seagreen]
In[vbcol=seagreen]
was[vbcol=seagreen]
or[vbcol=seagreen]
being[vbcol=seagreen]
the[vbcol=seagreen]
sql[vbcol=seagreen]
computer.[vbcol=seagreen]
error[vbcol=seagreen]
MSSQLSERVER
>
|||check this article
http://support.microsoft.com/default...b;en-us;283811
Quentin Ran wrote:
[vbcol=seagreen]
> Neither a local admin account nor the system account would start them.
> There must be wrong registry settings, and we don't know which ones and the
> correct values.
> "ch" <ch@.dontemailme.com> wrote in message
> news:40B4BCFB.1AF8E97B@.dontemailme.com...
> use that
> system
> services
> Agent
> SQL
> The
> In
> was
> or
> being
> the
> sql
> computer.
> error
> MSSQLSERVER
|||Thanks.
"ch" <ch@.dontemailme.com> wrote in message
news:40B4D8F9.5C7C10D7@.dontemailme.com...[vbcol=seagreen]
> check this article
> http://support.microsoft.com/default...b;en-us;283811
>
> Quentin Ran wrote:
the[vbcol=seagreen]
the[vbcol=seagreen]
to[vbcol=seagreen]
sqlagent[vbcol=seagreen]
the[vbcol=seagreen]
and[vbcol=seagreen]
E:\MSSQL\LOG\SQLAGENT.OUT.[vbcol=seagreen]
server[vbcol=seagreen]
databases.[vbcol=seagreen]
not[vbcol=seagreen]
never[vbcol=seagreen]
The[vbcol=seagreen]
the[vbcol=seagreen]
information[vbcol=seagreen]
computer.[vbcol=seagreen]
using[vbcol=seagreen]
specify a[vbcol=seagreen]
like[vbcol=seagreen]
mirror,[vbcol=seagreen]
the[vbcol=seagreen]
it[vbcol=seagreen]
following[vbcol=seagreen]
from a[vbcol=seagreen]
the
>

after system restore the agent does not start

Hi group,
we have a server that had a server restore (near mirror, the system admin ha
d to work out a few kinks). After the restore, the sql server agent does no
t start. When trying start it in service, it appears to be trying to start,
but at the end the MMC gives a message
Could not start the SQLSERVERAGENT service on local computer. The service d
id not return an error. This could be an internal error or an internal serv
ice error.
Window event log shows the following error:
The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be f
ound. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. The following
information is part of the event: 17050, initerrlog: Could not open error lo
g file 'e:\MSSQL\log\ERRORLOG'. Operating system error = 32(The process cann
ot access the file because it is being used by another process.).
Can someone offer the fix? Thanks.
The following is the configuration:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
QuentinThe log mentioned, is the normal name for the SQL Server error log... It lo
oks like SQL is trying to open it a second time, (as if you have 2 instances
of SQL Server trying to write to the same log file)... THis can be changed
in SEM startup properties...
However it might be possible that your SQL Agent is trying to open the log (
in SEM right click SQL Agent and go to properties to discover the log name
it is using..)
All in all it looks to me that the error you are showing is not related to t
he SQL Agent not starting...Ensure the agent is using an NT login that is a
SQL Server administrator.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's communi
ty of SQL Server professionals.
www.sqlpass.org
"Quentin Ran" <ab@.who.com> wrote in message news:OwDGnstQEHA.1960@.TK2MSFTNGP
10.phx.gbl...
Hi group,
we have a server that had a server restore (near mirror, the system admin ha
d to work out a few kinks). After the restore, the sql server agent does no
t start. When trying start it in service, it appears to be trying to start,
but at the end the MMC gives a message
Could not start the SQLSERVERAGENT service on local computer. The service d
id not return an error. This could be an internal error or an internal serv
ice error.
Window event log shows the following error:
The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be f
ound. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. The following
information is part of the event: 17050, initerrlog: Could not open error lo
g file 'e:\MSSQL\log\ERRORLOG'. Operating system error = 32(The process cann
ot access the file because it is being used by another process.).
Can someone offer the fix? Thanks.
The following is the configuration:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
Quentin|||>>The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer.
sounds like the sqlagent thinks it's running on a different computer.
does this restored server have a different name than the original
server?

system error = 32(The process cannot access the file because it is being
used by another process.).
this, as the other poster pointed out, sounds like something is using
that log file. that log file is for sqlserver. you should specify a
different log file for the agent, generally should be something like
e:\mssql\log\sqlagent.out
Quentin Ran wrote:
[vbcol=seagreen]
> Hi group, we have a server that had a server restore (near mirror, the
> system admin had to work out a few kinks). After the restore, the sql
> server agent does not start. When trying start it in service, it
> appears to be trying to start, but at the end the MMC gives a
> message Could not start the SQLSERVERAGENT service on local computer.
> The service did not return an error. This could be an internal error
> or an internal service error. Window event log shows the following
> error: The description for Event ID ( 17055 ) in Source ( MSSQLSERVER
> ) cannot be found. The local computer may not have the necessary
> registry information or message DLL files to display messages from a
> remote computer. The following information is part of the event:
> 17050, initerrlog: Could not open error log file
> 'e:\MSSQL\log\ERRORLOG'. Operating system error = 32(The process
> cannot access the file because it is being used by another
> process.). Can someone offer the fix? Thanks. The following is the
> configuration: Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack
> 3) Quentin|||Thanks for both responses.
The restored server has exactly the same name as before. SQL Server Agent
is running off a server admin account.
SQL Server Service uses the error log file e:\MSSQL\log\ERRORLOG, and SQL
Server Agent Service uses the error log file E:\MSSQL\LOG\SQLAGENT.OUT. The
server service log seems to work at least half way -- when the server
service starts, it records what it was doing like opening the databases. In
fact it even recorded a restore of a database. The fact that it did not
record any failure of launching the server agent may be due to it never was
asked to do so -- Windows may not know to direct such event there. The
agent log actually also contains some entries after the starting of the
server service, but not those ones that agent service start failed.
Any additional comments?
Quentin
"ch" <ch@.dontemailme.com> wrote in message
news:40B4980C.B82ABDFE@.dontemailme.com...
> message DLL files to display messages from a remote computer.
> sounds like the sqlagent thinks it's running on a different computer.
> does this restored server have a different name than the original
> server?
>
> system error = 32(The process cannot access the file because it is being
> used by another process.).
> this, as the other poster pointed out, sounds like something is using
> that log file. that log file is for sqlserver. you should specify a
> different log file for the agent, generally should be something like
> e:\mssql\log\sqlagent.out
>
>
> Quentin Ran wrote:
>
>|||maybe there are some registry settings incorrect due to the restore of the
server.
try this.
create an nt account that is local admin on the server.
using enterprise manager, change the sqlserver and sqlagent services to use
that
account.
restart server. see if agent starts properly.
now go back using enterprise manager and reset your sqlserver and sqlagent
accounts to whatever account(s) they were using before.
restart server. see if agent starts properly.
if that doesn't work, see if you can get them to run properly using the syst
em
account for the two services.
be sure to use enterprise manager to make changes to the accounts the servic
es
use.
Quentin Ran wrote:
[vbcol=seagreen]
> Thanks for both responses.
> The restored server has exactly the same name as before. SQL Server Agent
> is running off a server admin account.
> SQL Server Service uses the error log file e:\MSSQL\log\ERRORLOG, and SQL
> Server Agent Service uses the error log file E:\MSSQL\LOG\SQLAGENT.OUT. T
he
> server service log seems to work at least half way -- when the server
> service starts, it records what it was doing like opening the databases.
In
> fact it even recorded a restore of a database. The fact that it did not
> record any failure of launching the server agent may be due to it never wa
s
> asked to do so -- Windows may not know to direct such event there. The
> agent log actually also contains some entries after the starting of the
> server service, but not those ones that agent service start failed.
> Any additional comments?
> Quentin
> "ch" <ch@.dontemailme.com> wrote in message
> news:40B4980C.B82ABDFE@.dontemailme.com...|||Neither a local admin account nor the system account would start them.
There must be wrong registry settings, and we don't know which ones and the
correct values.
"ch" <ch@.dontemailme.com> wrote in message
news:40B4BCFB.1AF8E97B@.dontemailme.com...
> maybe there are some registry settings incorrect due to the restore of the
> server.
> try this.
> create an nt account that is local admin on the server.
> using enterprise manager, change the sqlserver and sqlagent services to
use that
> account.
> restart server. see if agent starts properly.
> now go back using enterprise manager and reset your sqlserver and sqlagent
> accounts to whatever account(s) they were using before.
> restart server. see if agent starts properly.
> if that doesn't work, see if you can get them to run properly using the
system
> account for the two services.
> be sure to use enterprise manager to make changes to the accounts the
services
> use.
>
> Quentin Ran wrote:
>
Agent[vbcol=seagreen]
SQL[vbcol=seagreen]
The[vbcol=seagreen]
In[vbcol=seagreen]
was[vbcol=seagreen]
or[vbcol=seagreen]
being[vbcol=seagreen]
the[vbcol=seagreen]
sql[vbcol=seagreen]
computer.[vbcol=seagreen]
error[vbcol=seagreen]
MSSQLSERVER[vbcol=seagreen]
>|||check this article
http://support.microsoft.com/defaul...kb;en-us;283811
Quentin Ran wrote:
[vbcol=seagreen]
> Neither a local admin account nor the system account would start them.
> There must be wrong registry settings, and we don't know which ones and th
e
> correct values.
> "ch" <ch@.dontemailme.com> wrote in message
> news:40B4BCFB.1AF8E97B@.dontemailme.com...
> use that
> system
> services
> Agent
> SQL
> The
> In
> was
> or
> being
> the
> sql
> computer.
> error
> MSSQLSERVER|||Thanks.
"ch" <ch@.dontemailme.com> wrote in message
news:40B4D8F9.5C7C10D7@.dontemailme.com...
> check this article
> http://support.microsoft.com/defaul...kb;en-us;283811
>
> Quentin Ran wrote:
>
the[vbcol=seagreen]
the[vbcol=seagreen]
to[vbcol=seagreen]
sqlagent[vbcol=seagreen]
the[vbcol=seagreen]
and[vbcol=seagreen]
E:\MSSQL\LOG\SQLAGENT.OUT.[vbcol=seagreen]
server[vbcol=seagreen]
databases.[vbcol=seagreen]
not[vbcol=seagreen]
never[vbcol=seagreen]
The[vbcol=seagreen]
the[vbcol=seagreen]
information[vbcol=seagreen]
computer.[vbcol=seagreen]
using[vbcol=seagreen]
specify a[vbcol=seagreen]
like[vbcol=seagreen]
mirror,[vbcol=seagreen]
the[vbcol=seagreen]
it[vbcol=seagreen]
following[vbcol=seagreen]
from a[vbcol=seagreen]
the[vbcol=seagreen]
>

after system restore the agent does not start

This is a multi-part message in MIME format.
--=_NextPart_000_00E5_01C442B1.3C673D60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi group,
we have a server that had a server restore (near mirror, the system = admin had to work out a few kinks). After the restore, the sql server = agent does not start. When trying start it in service, it appears to be = trying to start, but at the end the MMC gives a message
Could not start the SQLSERVERAGENT service on local computer. The = service did not return an error. This could be an internal error or an = internal service error.
Window event log shows the following error:
The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot = be found. The local computer may not have the necessary registry = information or message DLL files to display messages from a remote = computer. The following information is part of the event: 17050, = initerrlog: Could not open error log file 'e:\MSSQL\log\ERRORLOG'. = Operating system error =3D 32(The process cannot access the file because = it is being used by another process.).
Can someone offer the fix? Thanks.
The following is the configuration:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
Quentin
--=_NextPart_000_00E5_01C442B1.3C673D60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi group,

we have a server that had a server = restore (near mirror, the system admin had to work out a few kinks). After the = restore, the sql server agent does not start. When trying start it in = service, it appears to be trying to start, but at the end the MMC gives a message

Could not start the SQLSERVERAGENT = service on local computer. The service did not return an error. This could be = an internal error or an internal service error.

Window event log shows the following error:

The description for Event ID ( 17055 ) = in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the = necessary registry information or message DLL files to display messages from a = remote computer. The following information is part of the event: 17050, = initerrlog: Could not open error log file 'e:\MSSQL\log\ERRORLOG'. Operating system = error =3D 32(The process cannot access the file because it is being used by = another process.).

Can someone offer the fix? Thanks.

The following is the = configuration:

Microsoft SQL Server 2000 - = 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 = Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: = Service Pack 3)

Quentin

--=_NextPart_000_00E5_01C442B1.3C673D60--This is a multi-part message in MIME format.
--=_NextPart_000_013D_01C442EE.20BD19D0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
The log mentioned, is the normal name for the SQL Server error log... =It looks like SQL is trying to open it a second time, (as if you have 2 =instances of SQL Server trying to write to the same log file)... THis =can be changed in SEM startup properties...
However it might be possible that your SQL Agent is trying to open the =log ( in SEM right click SQL Agent and go to properties to discover the =log name it is using..)
All in all it looks to me that the error you are showing is not related =to the SQL Agent not starting...Ensure the agent is using an NT login =that is a SQL Server administrator.
-- Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's =community of SQL Server professionals.
www.sqlpass.org
"Quentin Ran" <ab@.who.com> wrote in message =news:OwDGnstQEHA.1960@.TK2MSFTNGP10.phx.gbl...
Hi group,
we have a server that had a server restore (near mirror, the system =admin had to work out a few kinks). After the restore, the sql server =agent does not start. When trying start it in service, it appears to be =trying to start, but at the end the MMC gives a message
Could not start the SQLSERVERAGENT service on local computer. The =service did not return an error. This could be an internal error or an =internal service error.
Window event log shows the following error:
The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) =cannot be found. The local computer may not have the necessary registry =information or message DLL files to display messages from a remote =computer. The following information is part of the event: 17050, =initerrlog: Could not open error log file 'e:\MSSQL\log\ERRORLOG'. =Operating system error =3D 32(The process cannot access the file because =it is being used by another process.).
Can someone offer the fix? Thanks.
The following is the configuration:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
Quentin
--=_NextPart_000_013D_01C442EE.20BD19D0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

The log mentioned, is the normal name =for the SQL Server error log... It looks like SQL is trying to open it a second =time, (as if you have 2 instances of SQL Server trying to write to the same log =file)... THis can be changed in SEM startup properties...
However it might be possible that your =SQL Agent is trying to open the log ( in SEM right click SQL Agent and go to =properties to discover the log name it is using..)
All in all it looks to me that the =error you are showing is not related to the SQL Agent not starting...Ensure the agent =is using an NT login that is a SQL Server administrator.
-- Wayne Snyder, MCDBA, SQL Server MVPMariner, =Charlotte, NChttp://www.mariner-usa.com">www.mariner-usa.com(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and =it's community of SQL Server professionals.http://www.sqlpass.org">www.sqlpass.org
"Quentin Ran" wrote in message news:OwDGnstQEHA.1960=@.TK2MSFTNGP10.phx.gbl...
Hi group,

we have a server that had a server =restore (near mirror, the system admin had to work out a few kinks). After the = restore, the sql server agent does not start. When trying start =it in service, it appears to be trying to start, but at the end the MMC =gives a message

Could not start the SQLSERVERAGENT =service on local computer. The service did not return an error. This =could be an internal error or an internal service error.

Window event log shows the following error:

The description for Event ID ( 17055 =) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the =necessary registry information or message DLL files to display messages from a =remote computer. The following information is part of the event: 17050, =initerrlog: Could not open error log file 'e:\MSSQL\log\ERRORLOG'. Operating =system error =3D 32(The process cannot access the file because it is being used by =another process.).

Can someone offer the fix? Thanks.

The following is the =configuration:

Microsoft SQL Server 2000 - =8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 =(Build 2195: Service Pack 3)

Quentin

--=_NextPart_000_013D_01C442EE.20BD19D0--|||>>The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer.
sounds like the sqlagent thinks it's running on a different computer.
does this restored server have a different name than the original
server?
>>Could not open error log file 'e:\MSSQL\log\ERRORLOG'. Operating
system error = 32(The process cannot access the file because it is being
used by another process.).
this, as the other poster pointed out, sounds like something is using
that log file. that log file is for sqlserver. you should specify a
different log file for the agent, generally should be something like
e:\mssql\log\sqlagent.out
Quentin Ran wrote:
> Hi group, we have a server that had a server restore (near mirror, the
> system admin had to work out a few kinks). After the restore, the sql
> server agent does not start. When trying start it in service, it
> appears to be trying to start, but at the end the MMC gives a
> message Could not start the SQLSERVERAGENT service on local computer.
> The service did not return an error. This could be an internal error
> or an internal service error. Window event log shows the following
> error: The description for Event ID ( 17055 ) in Source ( MSSQLSERVER
> ) cannot be found. The local computer may not have the necessary
> registry information or message DLL files to display messages from a
> remote computer. The following information is part of the event:
> 17050, initerrlog: Could not open error log file
> 'e:\MSSQL\log\ERRORLOG'. Operating system error = 32(The process
> cannot access the file because it is being used by another
> process.). Can someone offer the fix? Thanks. The following is the
> configuration: Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack
> 3) Quentin|||Thanks for both responses.
The restored server has exactly the same name as before. SQL Server Agent
is running off a server admin account.
SQL Server Service uses the error log file e:\MSSQL\log\ERRORLOG, and SQL
Server Agent Service uses the error log file E:\MSSQL\LOG\SQLAGENT.OUT. The
server service log seems to work at least half way -- when the server
service starts, it records what it was doing like opening the databases. In
fact it even recorded a restore of a database. The fact that it did not
record any failure of launching the server agent may be due to it never was
asked to do so -- Windows may not know to direct such event there. The
agent log actually also contains some entries after the starting of the
server service, but not those ones that agent service start failed.
Any additional comments?
Quentin
"ch" <ch@.dontemailme.com> wrote in message
news:40B4980C.B82ABDFE@.dontemailme.com...
> >>The local computer may not have the necessary registry information or
> message DLL files to display messages from a remote computer.
> sounds like the sqlagent thinks it's running on a different computer.
> does this restored server have a different name than the original
> server?
>
> >>Could not open error log file 'e:\MSSQL\log\ERRORLOG'. Operating
> system error = 32(The process cannot access the file because it is being
> used by another process.).
> this, as the other poster pointed out, sounds like something is using
> that log file. that log file is for sqlserver. you should specify a
> different log file for the agent, generally should be something like
> e:\mssql\log\sqlagent.out
>
>
> Quentin Ran wrote:
> > Hi group, we have a server that had a server restore (near mirror, the
> > system admin had to work out a few kinks). After the restore, the sql
> > server agent does not start. When trying start it in service, it
> > appears to be trying to start, but at the end the MMC gives a
> > message Could not start the SQLSERVERAGENT service on local computer.
> > The service did not return an error. This could be an internal error
> > or an internal service error. Window event log shows the following
> > error: The description for Event ID ( 17055 ) in Source ( MSSQLSERVER
> > ) cannot be found. The local computer may not have the necessary
> > registry information or message DLL files to display messages from a
> > remote computer. The following information is part of the event:
> > 17050, initerrlog: Could not open error log file
> > 'e:\MSSQL\log\ERRORLOG'. Operating system error = 32(The process
> > cannot access the file because it is being used by another
> > process.). Can someone offer the fix? Thanks. The following is the
> > configuration: Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> > May 31 2003 16:08:15
> > Copyright (c) 1988-2003 Microsoft Corporation
> > Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack
> > 3) Quentin
>|||maybe there are some registry settings incorrect due to the restore of the
server.
try this.
create an nt account that is local admin on the server.
using enterprise manager, change the sqlserver and sqlagent services to use that
account.
restart server. see if agent starts properly.
now go back using enterprise manager and reset your sqlserver and sqlagent
accounts to whatever account(s) they were using before.
restart server. see if agent starts properly.
if that doesn't work, see if you can get them to run properly using the system
account for the two services.
be sure to use enterprise manager to make changes to the accounts the services
use.
Quentin Ran wrote:
> Thanks for both responses.
> The restored server has exactly the same name as before. SQL Server Agent
> is running off a server admin account.
> SQL Server Service uses the error log file e:\MSSQL\log\ERRORLOG, and SQL
> Server Agent Service uses the error log file E:\MSSQL\LOG\SQLAGENT.OUT. The
> server service log seems to work at least half way -- when the server
> service starts, it records what it was doing like opening the databases. In
> fact it even recorded a restore of a database. The fact that it did not
> record any failure of launching the server agent may be due to it never was
> asked to do so -- Windows may not know to direct such event there. The
> agent log actually also contains some entries after the starting of the
> server service, but not those ones that agent service start failed.
> Any additional comments?
> Quentin
> "ch" <ch@.dontemailme.com> wrote in message
> news:40B4980C.B82ABDFE@.dontemailme.com...
> > >>The local computer may not have the necessary registry information or
> > message DLL files to display messages from a remote computer.
> >
> > sounds like the sqlagent thinks it's running on a different computer.
> > does this restored server have a different name than the original
> > server?
> >
> >
> > >>Could not open error log file 'e:\MSSQL\log\ERRORLOG'. Operating
> > system error = 32(The process cannot access the file because it is being
> > used by another process.).
> >
> > this, as the other poster pointed out, sounds like something is using
> > that log file. that log file is for sqlserver. you should specify a
> > different log file for the agent, generally should be something like
> > e:\mssql\log\sqlagent.out
> >
> >
> >
> >
> > Quentin Ran wrote:
> >
> > > Hi group, we have a server that had a server restore (near mirror, the
> > > system admin had to work out a few kinks). After the restore, the sql
> > > server agent does not start. When trying start it in service, it
> > > appears to be trying to start, but at the end the MMC gives a
> > > message Could not start the SQLSERVERAGENT service on local computer.
> > > The service did not return an error. This could be an internal error
> > > or an internal service error. Window event log shows the following
> > > error: The description for Event ID ( 17055 ) in Source ( MSSQLSERVER
> > > ) cannot be found. The local computer may not have the necessary
> > > registry information or message DLL files to display messages from a
> > > remote computer. The following information is part of the event:
> > > 17050, initerrlog: Could not open error log file
> > > 'e:\MSSQL\log\ERRORLOG'. Operating system error = 32(The process
> > > cannot access the file because it is being used by another
> > > process.). Can someone offer the fix? Thanks. The following is the
> > > configuration: Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> > > May 31 2003 16:08:15
> > > Copyright (c) 1988-2003 Microsoft Corporation
> > > Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack
> > > 3) Quentin
> >|||Neither a local admin account nor the system account would start them.
There must be wrong registry settings, and we don't know which ones and the
correct values.
"ch" <ch@.dontemailme.com> wrote in message
news:40B4BCFB.1AF8E97B@.dontemailme.com...
> maybe there are some registry settings incorrect due to the restore of the
> server.
> try this.
> create an nt account that is local admin on the server.
> using enterprise manager, change the sqlserver and sqlagent services to
use that
> account.
> restart server. see if agent starts properly.
> now go back using enterprise manager and reset your sqlserver and sqlagent
> accounts to whatever account(s) they were using before.
> restart server. see if agent starts properly.
> if that doesn't work, see if you can get them to run properly using the
system
> account for the two services.
> be sure to use enterprise manager to make changes to the accounts the
services
> use.
>
> Quentin Ran wrote:
> > Thanks for both responses.
> >
> > The restored server has exactly the same name as before. SQL Server
Agent
> > is running off a server admin account.
> >
> > SQL Server Service uses the error log file e:\MSSQL\log\ERRORLOG, and
SQL
> > Server Agent Service uses the error log file E:\MSSQL\LOG\SQLAGENT.OUT.
The
> > server service log seems to work at least half way -- when the server
> > service starts, it records what it was doing like opening the databases.
In
> > fact it even recorded a restore of a database. The fact that it did not
> > record any failure of launching the server agent may be due to it never
was
> > asked to do so -- Windows may not know to direct such event there. The
> > agent log actually also contains some entries after the starting of the
> > server service, but not those ones that agent service start failed.
> >
> > Any additional comments?
> >
> > Quentin
> >
> > "ch" <ch@.dontemailme.com> wrote in message
> > news:40B4980C.B82ABDFE@.dontemailme.com...
> > > >>The local computer may not have the necessary registry information
or
> > > message DLL files to display messages from a remote computer.
> > >
> > > sounds like the sqlagent thinks it's running on a different computer.
> > > does this restored server have a different name than the original
> > > server?
> > >
> > >
> > > >>Could not open error log file 'e:\MSSQL\log\ERRORLOG'. Operating
> > > system error = 32(The process cannot access the file because it is
being
> > > used by another process.).
> > >
> > > this, as the other poster pointed out, sounds like something is using
> > > that log file. that log file is for sqlserver. you should specify a
> > > different log file for the agent, generally should be something like
> > > e:\mssql\log\sqlagent.out
> > >
> > >
> > >
> > >
> > > Quentin Ran wrote:
> > >
> > > > Hi group, we have a server that had a server restore (near mirror,
the
> > > > system admin had to work out a few kinks). After the restore, the
sql
> > > > server agent does not start. When trying start it in service, it
> > > > appears to be trying to start, but at the end the MMC gives a
> > > > message Could not start the SQLSERVERAGENT service on local
computer.
> > > > The service did not return an error. This could be an internal
error
> > > > or an internal service error. Window event log shows the following
> > > > error: The description for Event ID ( 17055 ) in Source (
MSSQLSERVER
> > > > ) cannot be found. The local computer may not have the necessary
> > > > registry information or message DLL files to display messages from a
> > > > remote computer. The following information is part of the event:
> > > > 17050, initerrlog: Could not open error log file
> > > > 'e:\MSSQL\log\ERRORLOG'. Operating system error = 32(The process
> > > > cannot access the file because it is being used by another
> > > > process.). Can someone offer the fix? Thanks. The following is the
> > > > configuration: Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> > > > May 31 2003 16:08:15
> > > > Copyright (c) 1988-2003 Microsoft Corporation
> > > > Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack
> > > > 3) Quentin
> > >
>|||check this article
http://support.microsoft.com/default.aspx?scid=kb;en-us;283811
Quentin Ran wrote:
> Neither a local admin account nor the system account would start them.
> There must be wrong registry settings, and we don't know which ones and the
> correct values.
> "ch" <ch@.dontemailme.com> wrote in message
> news:40B4BCFB.1AF8E97B@.dontemailme.com...
> > maybe there are some registry settings incorrect due to the restore of the
> > server.
> > try this.
> > create an nt account that is local admin on the server.
> > using enterprise manager, change the sqlserver and sqlagent services to
> use that
> > account.
> > restart server. see if agent starts properly.
> > now go back using enterprise manager and reset your sqlserver and sqlagent
> > accounts to whatever account(s) they were using before.
> > restart server. see if agent starts properly.
> > if that doesn't work, see if you can get them to run properly using the
> system
> > account for the two services.
> > be sure to use enterprise manager to make changes to the accounts the
> services
> > use.
> >
> >
> >
> > Quentin Ran wrote:
> >
> > > Thanks for both responses.
> > >
> > > The restored server has exactly the same name as before. SQL Server
> Agent
> > > is running off a server admin account.
> > >
> > > SQL Server Service uses the error log file e:\MSSQL\log\ERRORLOG, and
> SQL
> > > Server Agent Service uses the error log file E:\MSSQL\LOG\SQLAGENT.OUT.
> The
> > > server service log seems to work at least half way -- when the server
> > > service starts, it records what it was doing like opening the databases.
> In
> > > fact it even recorded a restore of a database. The fact that it did not
> > > record any failure of launching the server agent may be due to it never
> was
> > > asked to do so -- Windows may not know to direct such event there. The
> > > agent log actually also contains some entries after the starting of the
> > > server service, but not those ones that agent service start failed.
> > >
> > > Any additional comments?
> > >
> > > Quentin
> > >
> > > "ch" <ch@.dontemailme.com> wrote in message
> > > news:40B4980C.B82ABDFE@.dontemailme.com...
> > > > >>The local computer may not have the necessary registry information
> or
> > > > message DLL files to display messages from a remote computer.
> > > >
> > > > sounds like the sqlagent thinks it's running on a different computer.
> > > > does this restored server have a different name than the original
> > > > server?
> > > >
> > > >
> > > > >>Could not open error log file 'e:\MSSQL\log\ERRORLOG'. Operating
> > > > system error = 32(The process cannot access the file because it is
> being
> > > > used by another process.).
> > > >
> > > > this, as the other poster pointed out, sounds like something is using
> > > > that log file. that log file is for sqlserver. you should specify a
> > > > different log file for the agent, generally should be something like
> > > > e:\mssql\log\sqlagent.out
> > > >
> > > >
> > > >
> > > >
> > > > Quentin Ran wrote:
> > > >
> > > > > Hi group, we have a server that had a server restore (near mirror,
> the
> > > > > system admin had to work out a few kinks). After the restore, the
> sql
> > > > > server agent does not start. When trying start it in service, it
> > > > > appears to be trying to start, but at the end the MMC gives a
> > > > > message Could not start the SQLSERVERAGENT service on local
> computer.
> > > > > The service did not return an error. This could be an internal
> error
> > > > > or an internal service error. Window event log shows the following
> > > > > error: The description for Event ID ( 17055 ) in Source (
> MSSQLSERVER
> > > > > ) cannot be found. The local computer may not have the necessary
> > > > > registry information or message DLL files to display messages from a
> > > > > remote computer. The following information is part of the event:
> > > > > 17050, initerrlog: Could not open error log file
> > > > > 'e:\MSSQL\log\ERRORLOG'. Operating system error = 32(The process
> > > > > cannot access the file because it is being used by another
> > > > > process.). Can someone offer the fix? Thanks. The following is the
> > > > > configuration: Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> > > > > May 31 2003 16:08:15
> > > > > Copyright (c) 1988-2003 Microsoft Corporation
> > > > > Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack
> > > > > 3) Quentin
> > > >
> >|||Thanks.
"ch" <ch@.dontemailme.com> wrote in message
news:40B4D8F9.5C7C10D7@.dontemailme.com...
> check this article
> http://support.microsoft.com/default.aspx?scid=kb;en-us;283811
>
> Quentin Ran wrote:
> > Neither a local admin account nor the system account would start them.
> > There must be wrong registry settings, and we don't know which ones and
the
> > correct values.
> >
> > "ch" <ch@.dontemailme.com> wrote in message
> > news:40B4BCFB.1AF8E97B@.dontemailme.com...
> > > maybe there are some registry settings incorrect due to the restore of
the
> > > server.
> > > try this.
> > > create an nt account that is local admin on the server.
> > > using enterprise manager, change the sqlserver and sqlagent services
to
> > use that
> > > account.
> > > restart server. see if agent starts properly.
> > > now go back using enterprise manager and reset your sqlserver and
sqlagent
> > > accounts to whatever account(s) they were using before.
> > > restart server. see if agent starts properly.
> > > if that doesn't work, see if you can get them to run properly using
the
> > system
> > > account for the two services.
> > > be sure to use enterprise manager to make changes to the accounts the
> > services
> > > use.
> > >
> > >
> > >
> > > Quentin Ran wrote:
> > >
> > > > Thanks for both responses.
> > > >
> > > > The restored server has exactly the same name as before. SQL Server
> > Agent
> > > > is running off a server admin account.
> > > >
> > > > SQL Server Service uses the error log file e:\MSSQL\log\ERRORLOG,
and
> > SQL
> > > > Server Agent Service uses the error log file
E:\MSSQL\LOG\SQLAGENT.OUT.
> > The
> > > > server service log seems to work at least half way -- when the
server
> > > > service starts, it records what it was doing like opening the
databases.
> > In
> > > > fact it even recorded a restore of a database. The fact that it did
not
> > > > record any failure of launching the server agent may be due to it
never
> > was
> > > > asked to do so -- Windows may not know to direct such event there.
The
> > > > agent log actually also contains some entries after the starting of
the
> > > > server service, but not those ones that agent service start failed.
> > > >
> > > > Any additional comments?
> > > >
> > > > Quentin
> > > >
> > > > "ch" <ch@.dontemailme.com> wrote in message
> > > > news:40B4980C.B82ABDFE@.dontemailme.com...
> > > > > >>The local computer may not have the necessary registry
information
> > or
> > > > > message DLL files to display messages from a remote computer.
> > > > >
> > > > > sounds like the sqlagent thinks it's running on a different
computer.
> > > > > does this restored server have a different name than the original
> > > > > server?
> > > > >
> > > > >
> > > > > >>Could not open error log file 'e:\MSSQL\log\ERRORLOG'. Operating
> > > > > system error = 32(The process cannot access the file because it is
> > being
> > > > > used by another process.).
> > > > >
> > > > > this, as the other poster pointed out, sounds like something is
using
> > > > > that log file. that log file is for sqlserver. you should
specify a
> > > > > different log file for the agent, generally should be something
like
> > > > > e:\mssql\log\sqlagent.out
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > Quentin Ran wrote:
> > > > >
> > > > > > Hi group, we have a server that had a server restore (near
mirror,
> > the
> > > > > > system admin had to work out a few kinks). After the restore,
the
> > sql
> > > > > > server agent does not start. When trying start it in service,
it
> > > > > > appears to be trying to start, but at the end the MMC gives a
> > > > > > message Could not start the SQLSERVERAGENT service on local
> > computer.
> > > > > > The service did not return an error. This could be an internal
> > error
> > > > > > or an internal service error. Window event log shows the
following
> > > > > > error: The description for Event ID ( 17055 ) in Source (
> > MSSQLSERVER
> > > > > > ) cannot be found. The local computer may not have the necessary
> > > > > > registry information or message DLL files to display messages
from a
> > > > > > remote computer. The following information is part of the event:
> > > > > > 17050, initerrlog: Could not open error log file
> > > > > > 'e:\MSSQL\log\ERRORLOG'. Operating system error = 32(The process
> > > > > > cannot access the file because it is being used by another
> > > > > > process.). Can someone offer the fix? Thanks. The following is
the
> > > > > > configuration: Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> > > > > > May 31 2003 16:08:15
> > > > > > Copyright (c) 1988-2003 Microsoft Corporation
> > > > > > Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack
> > > > > > 3) Quentin
> > > > >
> > >
>

Thursday, February 9, 2012

Advise on querying a database daily

I am implementing an application that is looking into another System's database (DBF). I need to query out new orders that have been put in daily. What is the best way to handle this situation? Should I write a service or have a trigger setup in MSDE? I just need to do a simple SELECT statement that grabs orders between a date range and update my SQL database (MSDE). I am not looking for specific code examples, just a solution.

What are my choices?

Thanks,

JasonWindows Scheduled Task is my preference. You could use SQL Tasks as well, I just don't like them.|||Lets say I do use the Windows Scheduled Task what type of project would it be since it does not have an interface?|||I usually just use VBS and then a COM callable .net component. The nice thing about SQL Tasks is that it will just run SQL.|||I have a question about SQL Tasks. I am running MSDE and I do have SQL Server Agent. I am using an Access project (adp) to manage my database. I do not see a utility to take advantage of SQL Server Agent/scheduling jobs. Do you have any advice on what I could use to generate the T-SQL so I can use SQL Server Agent?

Jason|||Sorry no idea, I'm an Enterprise Server user.

Advise on Integration with FoxPro

I have a legacy system based in Fox that I need to generate reports from and I would like to use SQL Server to house some report optimized tables that are derived from the Fox data. I am currently looking at several strategies for manipulating and porting the data.

a. Linked Servers - haven't had much success linking to the OLD fox database (DOS version)
b. DTS - can't find a way around dropping and recreating all the tables every time.
c. Visual Basic App - most control but not sure if the best solution
d. Some Combo of the Above

Does anyone have any advice on what the best approach might be? Are there any other solutions to add to the mix?

Thanks for any assistance,

AllenAt this point of time, I think a good sollution can be to write a DLL in VB/VC++ which will communicate with the FOX data and return the resultset in XML format.

Then your actual app can parse through the XML document using XML DOM or SAX API and provide the data to SQL Server database. If your XML file is going to be big enough, consider using SAX API which is a light-weight alternative to XML DOM.|||I have it in production with win2000 server sp2 + sql server 2000 sp3
by using Microsoft OLEDB Provider for ODBC, so it can be done

I have problems when I want to upgrade to VFPOLEDB, and in the environment of win2003 + sql server 2000 sp3. Running OPENQUERY in QA is fine, but can't be run via SQL Agent job with the error msg

Could not initialize data source object of OLE DB provider 'VFPOLEDB'. [SQLSTATE 42000] (Error 7303) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IDBInitialize::Initialize returned 0x80040e21]. [SQLSTATE 01000] (Error 7300). The step failed.

Advise on Forms Authentication

ok i am doing a system in asp.net and also using reporting services to
generate some reports for user to view.
My system consists of two parts which is the Suggestor and the Evaluator.
Firstly the suggestor will go to a aspx page whereby they can submit new
suggestions by filling up the page and before they submit the suggestion,
they had to choose from a dropdownlist box which contains all the available
Evaluator Full Name, so that this suggestion will only be sent to the choosen
Evaluator to be evaluated. And all data will be store in SQL Server 2000.
So now the suggestions need to be rejected or accepted by the evaluator and
i am using reporting services to generate the page for the evaluator to view
the suggestions that had been submitted to them. and i am not sure of how to
go about using forms authentication to allow the evaluator to login, so that
when they login successfully, they will be allow to view suggestions that is
associated to them. Do i use Active Directory login in asp.net then link to
reporting services? or is there any other method that i can use so that the
Evaluator that had been login can view their own suggestions in the reporting
services.
For your information, all the evaluator profiles are store in Active
Directory server thats why i thought of using AD login to carry out my task.
Ok in order to let u all have a clearer view of what i want to achieved, i
will use the following example.
For example now i am going to submit a suggestions. and i go to the aspx
page and fill up all the necessary data and lastly select a Evaluator from
the dropdownlist box. take for example i selected "Tan Boon Keng" as the
evaluator and i submitted my suggestion. So now "Tan Boon Keng" need to
evaluate my suggestion n inorder to evaluate the suggestion, he need to login
through some forms authentication so that he can only see the suggestions
that is related to him, n not seeing ALL the suggestions that had been
submitted. and the page where they view the suggestions is generate by
reporting services so how should i go about implementing this system to work
as what i want to achieved.
Sorry for the long post and thanks in advance...Hi, is there anybody that can help me or give me advise on how should i do
this?
i tried using forms authentication in asp.net then when evaluator login
through that page, they will be direct to the reporting services page and
they can view their suggestions only. but i not sure how to get the logon
user session id to query the database for the suggestions related to that
particular logon user.
"JiaN" wrote:
> ok i am doing a system in asp.net and also using reporting services to
> generate some reports for user to view.
> My system consists of two parts which is the Suggestor and the Evaluator.
> Firstly the suggestor will go to a aspx page whereby they can submit new
> suggestions by filling up the page and before they submit the suggestion,
> they had to choose from a dropdownlist box which contains all the available
> Evaluator Full Name, so that this suggestion will only be sent to the choosen
> Evaluator to be evaluated. And all data will be store in SQL Server 2000.
> So now the suggestions need to be rejected or accepted by the evaluator and
> i am using reporting services to generate the page for the evaluator to view
> the suggestions that had been submitted to them. and i am not sure of how to
> go about using forms authentication to allow the evaluator to login, so that
> when they login successfully, they will be allow to view suggestions that is
> associated to them. Do i use Active Directory login in asp.net then link to
> reporting services? or is there any other method that i can use so that the
> Evaluator that had been login can view their own suggestions in the reporting
> services.
> For your information, all the evaluator profiles are store in Active
> Directory server thats why i thought of using AD login to carry out my task.
> Ok in order to let u all have a clearer view of what i want to achieved, i
> will use the following example.
> For example now i am going to submit a suggestions. and i go to the aspx
> page and fill up all the necessary data and lastly select a Evaluator from
> the dropdownlist box. take for example i selected "Tan Boon Keng" as the
> evaluator and i submitted my suggestion. So now "Tan Boon Keng" need to
> evaluate my suggestion n inorder to evaluate the suggestion, he need to login
> through some forms authentication so that he can only see the suggestions
> that is related to him, n not seeing ALL the suggestions that had been
> submitted. and the page where they view the suggestions is generate by
> reporting services so how should i go about implementing this system to work
> as what i want to achieved.
> Sorry for the long post and thanks in advance...

Advise needed on Single user to multi-user database change

I am converting a system that is single user to multi-user. My problem is
that I am not familar with SQL Server 2000 and am not sure how to approach
this problem.
The biggest part of this problem is the reports. The reports (Crystal
Reports version 4.X) run from tables that are built in a VB application.
The Crystal Report report form is linked to an odbc connection. Some of the
tables are long running and run the risk of another person starting the same
report and corrupting the data in the table.
The report forms seem to be locked to a table name and accessed via the odbc
connection. I tried to change the table name, but the report stopped
working.
My question is, is there a way I can create a table with the same name, but
only be visible from the current user?Hi,
You can create tables using the user's name.
But this name should be a valid SQL User account that has the permission in
the Database and you need to connect to the SQL Server using that account
or have sufficient credentials to access the other users tables.
How do you connect (authenticate) the user to the SQL server?
How are you planning to connect (authenticate) the different users to the
SQL server?
Do you plan to use the "sa" account or create separate accounts for each
users?
If you are planning to create separate accounts for each user, then you can
definitely create separate tables for each user using
Create table [username].[tablename]
This table will be visible only to the user that has created it, rather,
accessible only with that particular username's credential
Example
Create table [myname].[testtb] (FirstCol int not null default 0)
select * from testtb - would return an error, stating invalid object name.
select * from [myname].testtb - would return the records in that table
So, as long as you are using separate users this will work safely. Each
user will be able to view and access their own table, without the fear of
messing up with other users data.
If you are planning not to use separate username then you can create
temporary tables.
You can create local and global temporary tables. Local temporary tables
are visible only in the current session; global temporary tables are
visible to all sessions.
Prefix local temporary table names with single number sign (#table_name),
and prefix global temporary table names with a double number sign
(##table_name).
SQL statements reference the temporary table using the value specified for
table_name in the CREATE TABLE statement:
CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)
But, since you need to access this tables from Crystal report and that
would be a separate connection, the local temporary tables would not be
visible to the Crystal Report.
This method would work, except for the integration with interaction via a
different connection to the database. As long as you use the same
connection object that created the tables, this
method is gonna work.
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

Tuesday, March 20, 2012

Alert fails to notify Operator (SQL Server 2005)

Alert fails to notify Operator (SQL Server 2005)

Alert fails to notify Operator (SQL Server 2005)

Friday, February 24, 2012

Agent failures and custom alerting

Agent failures and custom alerting

Ageing from system date by number of days

Age calucation

Sunday, February 19, 2012

after system restore the agent does not start

after system restore the agent does not start

after system restore the agent does not start

Thursday, February 9, 2012

Advise on querying a database daily

Advise on Integration with FoxPro

Advise on Forms Authentication

Advise needed on Single user to multi-user database change

SQL tool for MSDE

Blog Archive

About Me