MSDE & SQL 7 running, backups fail

I have a W2K Server with SQL Server 7 and an instance of MSDE 2000 installed.
SQL Server 7 and MSDE 2000 both run OK together (I can make ODBC connections
to their databases from other clients, Enterprise Manager and dbamgr2k work)
and the MSDE 2000 backup jobs run successfully.
However, the SQL Server 7 maintenance jobs do not run successfully. SQL
Agent is running.
The same jobs DO run if MSDE 2000 is not running.
SQL Server connection is NT authentication.
SQL Agent Service startup account is system account.
SQL Agent SQLServer connection is Windows NT authentication.
MSDE connection is trusted NT authentication.
The Event log shows:
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan Test'' (0xA15AFA82EC43BD4FBA057E1ECA0E2E61) - Status: Failed
- Invoked on: 29/07/2005 15:22:23 - Message: The job failed. Unable to
determine if the owner (sa) of job DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan Test' has server access (reason: Unable to connect to server
- check SQL Server and SQL Server Agent errorlogs).
SQLAgent.OUT:
29/07/2005 15:21:32 - ! [298] SQLServer Error: 14, Invalid connection.
[SQLSTATE 08001]
29/07/2005 15:21:32 - ! [298] SQLServer Error: 14, ConnectionOpen (Invalid
Instance()). [SQLSTATE 01000]
29/07/2005 15:21:32 - ! [382] Logon to server '(local)' failed
(ConnCachePerfCounterValues)
29/07/2005 15:22:23 - ! [298] SQLServer Error: 14, Invalid connection.
[SQLSTATE 08001]
29/07/2005 15:22:23 - ! [298] SQLServer Error: 14, ConnectionOpen (Invalid
Instance()). [SQLSTATE 01000]
29/07/2005 15:22:23 - ! [382] Logon to server '(local)' failed
(ConnCachePerfCounterValues)
Interestingly, I have to start the services in a particular order - SQL
Server and SQL Server Agent start automatically with Windows. I then have to
manually start MSDE and MSDE Agent otherwise SQL Server Agent cannot start.
I have recreated the same problem on a second server.
Any ideas why my SQL Server 7 jobs will not run?
Has anyone else achieved this?
hi,
Swaffs wrote:
> I have a W2K Server with SQL Server 7 and an instance of MSDE 2000
> installed. SQL Server 7 and MSDE 2000 both run OK together (I can
> make ODBC connections to their databases from other clients,
> Enterprise Manager and dbamgr2k work) and the MSDE 2000 backup jobs
> run successfully.
> However, the SQL Server 7 maintenance jobs do not run successfully.
> SQL Agent is running.
> The same jobs DO run if MSDE 2000 is not running.
I've tried reproducing the problem as indicated, but it seems I'm more lucky
then you
on a virtual machine, where both SQL Server 7.0 Dev Edition sp4 and MSDE Rel
A (sp3a) and relative Agent are running under LocalSystem account...
searching fo your reported exceptions I only found problems related to the
account running the service, but this should not be the case as SQL Server
7.0 Agent runs ok when MSDE is not running...
but I'll keep searching..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea - after reading that you tried it with SP3a without any issues,
I installed the latest Service Pack for MSDE, rebooted and started the MSDE
services before the SQL Services and the SQL Agent jobs work fine now.
Amazing what a difference a weekend makes!