Executing a stored proc on another server from a Scheduled task

Ok, I thought this one would be easy.

I have a stored proc: master.dbo.restore_database_foo

This is on database server B.

Database server A backs up database foo on a daily basis as a scheduled
task.

What I wanted to do was, at the end of the scheduled task is then call the
stored proc on B and restore the database.

If I go into Query Analyzer and log into database A, then exec
b.master.dbo.restore_database_foo works.

But if I take the same command and make it part of the scheduled task it
fails.

Error is:

OLE DB provider 'SQLOLEDB' reported an error. [SQLSTATE 42000] (Error 7399)
[SQLSTATE 01000] (Error 7312). The step failed.

To me this seems like a permissions issue, but nothing I've tried seems to
have helped.

Suggestions?

--
--
Greg D. Moore
President Green Mountain Software
Personal: http://stratton.greenms.comGreg D. Moore (Strider) (mooregr@.greenms.com) writes:
> I have a stored proc: master.dbo.restore_database_foo
> This is on database server B.
> Database server A backs up database foo on a daily basis as a scheduled
> task.
> What I wanted to do was, at the end of the scheduled task is then call the
> stored proc on B and restore the database.
> If I go into Query Analyzer and log into database A, then exec
> b.master.dbo.restore_database_foo works.
> But if I take the same command and make it part of the scheduled task it
> fails.
> Error is:
> OLE DB provider 'SQLOLEDB' reported an error. [SQLSTATE 42000] (Error
> 7399) [SQLSTATE 01000] (Error 7312). The step failed.
> To me this seems like a permissions issue, but nothing I've tried seems to
> have helped.

SELECT * FROM master..sysmessages where error in (7399, 7312) gives me

"Invalid use of schema and/or catalog for OLE DB provider '%ls'. A four-part
name was supplied, but the provider does not expose the necessary interfaces
to use a catalog and/or schema." and "OLE DB provider '%ls' reported an
error. %ls"

Doesn't tell me a whole lot.

Can't you take the easy way out and make the step a command-line
step that invokes OSQL?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93EAE9E441CB8Yazorman@.127.0.0.1...
> SELECT * FROM master..sysmessages where error in (7399, 7312) gives me
> "Invalid use of schema and/or catalog for OLE DB provider '%ls'. A
four-part
> name was supplied, but the provider does not expose the necessary
interfaces
> to use a catalog and/or schema." and "OLE DB provider '%ls' reported an
> error. %ls"
> Doesn't tell me a whole lot.

Yeah, same here. It seems to be one of those things that SHOULD be easy.
:-)

> Can't you take the easy way out and make the step a command-line
> step that invokes OSQL?

I could, but that just seems to "dirty" :-)

But that may be the way I go if I can't find a cleaner solution.

> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||"Greg D. Moore \(Strider\)" <mooregr@.greenms.com> wrote in message news:<wM95b.29715$yG2.21729@.twister.nyroc.rr.com>...
> "Erland Sommarskog" <sommar@.algonet.se> wrote in message
> news:Xns93EAE9E441CB8Yazorman@.127.0.0.1...
> > SELECT * FROM master..sysmessages where error in (7399, 7312) gives me
> > "Invalid use of schema and/or catalog for OLE DB provider '%ls'. A
> four-part
> > name was supplied, but the provider does not expose the necessary
> interfaces
> > to use a catalog and/or schema." and "OLE DB provider '%ls' reported an
> > error. %ls"
> > Doesn't tell me a whole lot.
> Yeah, same here. It seems to be one of those things that SHOULD be easy.
> :-)
> > Can't you take the easy way out and make the step a command-line
> > step that invokes OSQL?
> I could, but that just seems to "dirty" :-)
> But that may be the way I go if I can't find a cleaner solution.
>
> > --
> > Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> > Books Online for SQL Server SP3 at
> > http://www.microsoft.com/sql/techin.../2000/books.asp

If you are restoring database remotely you should give local admin
privileges on target server to the SQLServerAgent from the primary
server. Just executing stored procedure from the Query Analyzer does
not simulate fully your situation. You should assign one Global
Windows-based account to the SQLServerAgent on primary server and give
it local admin privileges on target server instead of using generic
Local System account. If Network Admins are not cooperative you can
create Local Windows accounts with the same name and password on both
servers and add these accounts to both database servers.

Sinisa Catic