473,320 Members | 1,961 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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.com
Jul 20 '05 #1
3 6995
Greg D. Moore (Strider) (mo*****@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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn**********************@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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #3
"Greg D. Moore \(Strider\)" <mo*****@greenms.com> wrote in message news:<wM*******************@twister.nyroc.rr.com>. ..
"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn**********************@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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...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
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Rizwan | last post by:
i have to write a process for a client. this process will have no visual component and will be scheduled to run as a job from Microsoft SQL Server daily. I want to write this process in java...
1
by: Jagannathan Santhanam | last post by:
Hello I am trying to execute ‘xp_cmdshell' from within a DTS package that was created by another person. When I try to execute that ‘SQL Task' selectively from within the package, I get the...
10
by: Heiko Pliefke | last post by:
Hi NG! I wrote a stored procedure which at first deletes 100.000 rows and then inserts 100.000 new rows. There is a huge difference between executing this SP in the query-analyzer (runtime...
15
by: Chakkaradeep | last post by:
Hi all, i have written a Service,now i want to execute another application (for eg;calc.exe) in the service....how will i perform it??... i tried using this.... /**************Executing a...
3
by: Peter Strøiman | last post by:
Hi. I have a web application that needs to run tasks at regular intervals. E.g. it must send out emails every night to people who subscribe to that service. I have come up with one solution,...
45
by: John | last post by:
Hi When developing vb.bet winform apps bound to sql server datasource, is it preferable to use SELECTs or stored procedure to read and write data from/to SQL Server? Why? Thanks Regards
3
by: comp_databases_ms-sqlserver | last post by:
This post is related to SQL server 2000 and SQL Server 2005 all editions. Many of my stored procedures create temporary tables in the code. I want to find a way to find the query plan for these...
1
by: Myster Edd | last post by:
I have a strange problem that I think deals with security on SQL 2005. I have a scheduled task that runs on a Windows 2000 machine. It calls a vb script which creates a connection to SQL Server. ...
2
by: Carlton Kirby | last post by:
I need to execute a job on a SQL Express 2005 instance (no SQLAgent). The job will be executed manually by a user, so it doesn't need to be scheduled to run automatically. I thought I could...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.