September 8, 2008 at 3:40 am
Hi
I am trying to execute a stored procedure of a Link Server from local server. Local server is Rahana and the Linked Server is SHARAGIM. SHARAGIM is configured to accept remote connection using both TCP/IP and named pipes. There is no firewall problem. Linked server works great as well. I have also configured both servers to accept/allow rpc/rpc out using sp_serveroption:
exec sp_helpserver
RAHANARAHANA rpc,rpc out,data access,use remote collation0 NULL00
SHARAGIMSHARAGIM rpc,rpc out,data access,use remote collation3 NULL00
However I still get the error:
Could not execute procedure on remote server 'SHARAGIM' because SQL Server is not configured for remote access. Ask your system administrator to reconfigure SQL Server to allow remote access.
What else my I do to be able to call the procedure?
September 8, 2008 at 4:07 am
"rpc" option only takes effect after restarting SQL Server services, are you sure server got recycled after rpc option was set?
How did you added the server? was it by using old sp_addserver or new sp_addlinkedserver system proc?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 8, 2008 at 4:31 am
Hi
and thank you for your cooperation
I have restarted the MSSQLSERVER service on both servers. But it still doesn't work.
Actually, I cannot remember how I add the linked server. But it seems there is no any problem with the linked server. For example, the below query can be issued from RAHANA server with no error and returns the results:
select * from sharagim.master.dbo.sysobjects
BTW, I don't know it matters or not, but SHARAGIM is actually a SQL Server server on a virtual machine.
September 8, 2008 at 5:08 am
"rpc" option does not affects remote query execution, only affects remote procedure execution.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 8, 2008 at 5:15 am
alright,
So just kindly let me know how can I execute the usp_MyProc which is in the master database on the SHARAGIM server from RAHANA server. The below query should be issued from RAHANA server:
exec SHARAGAIM.master.dbo.usp_MyProc
It seems that all required configuration has been set. But it still does not work.
September 8, 2008 at 5:20 am
1- :w00t: I would never, ever put a custom storedproc in the Master database, that's very bad mojo.
2- Something is wrong with your setup. I would start from scratch setting up a new Linked Server using sp_addlinkedserver system procedure. You can check details in Books Online.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 8, 2008 at 8:37 am
There are cases when putting a proc in master makes for the best solution. We don't have enough details to know wether or not it's needed in this case though.
Do you need to have the same proc executed in any of the databases (which is usually why you put something in master in the first place)?
September 8, 2008 at 10:23 am
I am having a similar problem, and I think I know why - it's just that none of the documentation, nor anything I've found on the 'net, indicates that this should be true.
We've got a process that's been working fine, until we (per a request from our SOX audit team) turned off the "remote access" option for the server using sp_configure. Since then, we've been getting the error.
However, all the documentation seems to indicate that this is only supposed to be necessary if you're setting up the older, deprecated, "remote servers" instead of linked servers.
Can anyone confirm/deny that "remote access" must be on for executing procedures on linked servers?
RD Francis
R David Francis
September 8, 2008 at 11:06 am
I guess there are two questions in one thread. However, to begin with: is it possible to clarify what version of SQL server are we talking about?
What is the nature of sproc? Is it by any chance using "distributed" transactions? If yes, you would have to check your MSDTC settings.
http://blogs.msdn.com/florinlazar/archive/2003/12/04/41371.aspx
September 8, 2008 at 3:04 pm
In my case, I creates a stored procedure that runs a simple query against the other database (select count(*) from a table), returning the result set. I can run the query to my linked server, but cannot execute the stored procedure.
It's beginning to look more and more to me like having "remote access" configured to 1 is required for Linked servers to execute stored procedures - it's just that my reading of the documentation would indicate that this is not supposed to be true.
R David Francis
September 8, 2008 at 6:10 pm
RD,
if the owner of sproc on one server is different from the remote user specified on the linked server and different from a SQL user defined on the second server - you wouldn't receive any results.
In most of the cases I've seen: problem with the linked server is a problem of improperly configured security. For the test purposes
1. Create a new TestLogin on SQLServer1; set it with password.
2. Map it as an owner of DB where you have your sproc.
3. Open remote server and repeat your operation on the remote server for the same SQL user and password.
4. Create a linked server and try the option "Using remote login" and then specify this login and password.
5. Verify that your linked server is working by opening remote catalogs in linked server.
I can assure you that your sproc will work properly (unless you did not specify some additional details, like different versions of databases, different domains etc.)
September 8, 2008 at 7:30 pm
PaulB (9/8/2008)
1- :w00t: I would never, ever put a custom storedproc in the Master database, that's very bad mojo.2- Something is wrong with your setup. I would start from scratch setting up a new Linked Server using sp_addlinkedserver system procedure. You can check details in Books Online.
alright,
I would never put a custom stored procedure in Master database too. It was just an example to show what am I trying to do, no matter which database the procedure resides in.
(However, as you know, in some occasions creating a storedproc in master database is inevitable. Like when we want the procedure to be run when the SQL Server starts, which is achievable using sp_procoption).
I add my linked server using Management Studio. -> Server Objects -> Linked Servers ->...
As I wrote, it seems there is no problem with linked server. I can issue below query from RAHANA server and get the expected result set:
select * from SHARAHIM.master.dbo.sysobjects
Is it possible that linked server is not properly setup, but the mentioned query still returns the results?
Anyhow, I'll drop the linked server and begin from scratch. I'll get back to you in again.
Thank You
September 8, 2008 at 7:36 pm
Ninja's_RGR'us (9/8/2008)
There are cases when putting a proc in master makes for the best solution. We don't have enough details to know wether or not it's needed in this case though.Do you need to have the same proc executed in any of the databases (which is usually why you put something in master in the first place)?
It really doesn't matter which database that stored procedure resides in. It was just an example. The procedure which I want to be executed from RAHANA server may be reside in any other database.
FYI, it's a procedure that is going to create a trace on SHARAGIM server, but should be called from RAHANA server.
September 8, 2008 at 7:40 pm
RD Francis (9/8/2008)
I am having a similar problem, and I think I know why - it's just that none of the documentation, nor anything I've found on the 'net, indicates that this should be true.We've got a process that's been working fine, until we (per a request from our SOX audit team) turned off the "remote access" option for the server using sp_configure. Since then, we've been getting the error.
However, all the documentation seems to indicate that this is only supposed to be necessary if you're setting up the older, deprecated, "remote servers" instead of linked servers.
Can anyone confirm/deny that "remote access" must be on for executing procedures on linked servers?
RD Francis
I even set the "remote access" to 1, But still cannot call the procedure on linked server. 😉
September 8, 2008 at 7:44 pm
Glen (9/8/2008)
What is the nature of sproc? Is it by any chance using "distributed" transactions? If yes, you would have to check your MSDTC settings.
http://blogs.msdn.com/florinlazar/archive/2003/12/04/41371.aspx
I wrote in previous post what this stored procedure is supposed to do (creating a trace on linked server). SHARAGIM (linked server) is on a virtual machine.
Both servers are SQL Server 2005 on Windows XP Professional service pack 2.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply