I was going through comments on various posts to see if I have missed to answer any comments. I realized that there are quite a few times I have answered question which discuss about how to call stored procedure or query on linked server or another server. This is very detailed topic, I will keep it very simple.
I am making assumptions that remote server is already set up as linked server with proper permissions in application and network is arranged.
Method 1 : Remote Stored Procedure can be called as four part name:
Syntax:
EXEC [RemoteServer] .DatabaseName.DatabaseOwner.StoredProcedureName
‘Params’
Example:
EXEC [DEVSQL] .AdventureWorks.dbo.uspGetEmployeeManagers ’42’
Method 2 : Use Distributed Query functions (OpenQuery())
Syntax: SELECT *
FROM OPENQUERY([RemoteServer],DatabaseName.DatabaseOwner.StoredProcedureName)
Example: SELECT *
FROM OPENQUERY([DEVSQL],AdventureWorks.dbo.spAdmin_GetAllUsers)
Let me know if you want me write about above two methods in detail.
Reference : Pinal Dave (https://blog.sqlauthority.com)
133 Comments. Leave new
As soon as I ask the question I figure out what’s causing the problem.
We were using the 2005 varchar(max) variable to a text variable parameter on 2000. Changed it to varchar(8000) and it works correctly.
do we require system admin rigths to create stored procedures in sql 2005.. please answer my question
I am trying to run the following stored procedure using link server. My other non parameter SP works fine with this following ways but the parameter SP gives me the following error:
DECLARE @vTechnicianName Varchar(25)
DECLARE @cmd Varchar(8000)
SET @vTechnicianName = ‘AMY GAMBLE’
SET @CMD = ‘AGY_REC_RPT.AA_Test.dbo.AA_Legal_Agents_Report ‘+””+@vTechnicianName+””
Print @cmd
EXEC @CMD
Error:
AGY_REC_RPT.AA_Test.dbo.AA_Legal_Agents_Report ‘AMY GAMBLE’
OLE DB provider “MSDASQL” for linked server “AGY_REC_RPT” returned message “[Microsoft][SQL Native Client][SQL Server]Could not find stored procedure ‘AA_Test.dbo.AA_Legal_Agents_Report ‘AMY GAMBLE”.”.
Msg 7212, Level 17, State 1, Line 8
Could not execute procedure ‘AA_Legal_Agents_Report ‘AMY GAMBLE” on remote server ‘AGY_REC_RPT’.
Is there a limit to the number of linked servers that can be attached to a single SQL 2005 Server?
Read about Maximum Capacity Specifications in SQL Server help file
Please let me know what i have to do in the case of linked server is not created…I have the 100 server..i want to create the job on 100 server which will call to the 1 server.so it does n’t make sense to create the linked server for all 100 servers..
@Manish,
In SQL Server 2000 and 2005, we have something called Master and Target Server.
Right click on SQL Server Agent Properties -> Make this Server Master or Target.
Make a Master Server and then also define what will be target servers for this master server.
Use of this is, if you have a job on Master server and you want this to run in 100 different target servers, you can, you just need to create this job only on master Server.
I don’t know what type of job you are running what script does it contain, so I can’t really tell you if this has to be a linked server for in order to work.
Please explore more about this Master Server and Target Server, its a very good concept.
After you sucessfully implement this, please share here how did you do this, it will be helpful for all of us.
Regards,
IM.
Hi, I have a Sybase linked server on SQL 2005, I need execute a store procedures, when the sp has no parameters all work fine, but with parameters bring up the next error:
Mens. 7357, Nivel 16, Estado 2, LÃnea 1
Cannot process the object “exec finmes..sp_testsql ‘a'”. The OLE DB provider “MSDASQL” for linked server “SPC” indicates that either the object has no columns or the current user does not have permissions on that object.
So, this is the query:
SELECT * FROM OPENQUERY(SLS, ‘exec database..sp_testsql ”a”’)
Thanks a lot.
Hello, I have a Sybase linked server toward SQL 2005 and I need execute a stored procedure with parameters. If I try the sp without paramaters all works fine, but when I add the parameters bring me up the next error:
Mens. 7357, Nivel 16, Estado 2, LÃnea 1
Cannot process the object “exec finmes..sp_testsql ‘a'”. The OLE DB provider “MSDASQL” for linked server “SPC” indicates that either the object has no columns or the current user does not have permissions on that object.
This is my sql, executed in SQL:
SELECT * FROM OPENQUERY(SPC, ‘exec sybasedb..sp_testsql ”a”’)
This is my sp created in Sybase:
create procedure sp_testsql @test char(1)
as
select @test + ‘I AM IN SYBASE’
Thanks a lot for help.
how can i call a stored procedure (which receives parameters and returns a value) from another stored proedure
thanks
Luis Alberto,
I am in a similar situation and I get the same error message when i run a insert query using OPENQUERY.
Were you able to solve your problem. I would really appreciate it if you can post it here.
Thanks
Luis,
I’ve a problem with a table which is getting wrong data and i need to create a trigger to track it, but I want to know which stored procedures is sending bad data and I tried with @@PROCID and it returns the trigger ID. How can I get the ID of the stored procedure filling the table?
Thanks
Y
At the end of each procedure, use a variable that gets assigned
set @var=@@PROCID
You can return that variable to know the procid
I created a utility database (UTIL) on the same server as my test database (TEST). The production database (PROD) is on another server.
I had hoped that I could put procedures, functions, etc. on the UTIL server and run them remotely from TEST and PROD.
However, it looks like the procedure looks at its own database and not on the one where the call originated.
Example:
Table1 resides on DEV
Table2 resides on UTIL
Proc1Sp resides on UTIL and has the following command:
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name
I connect to DEV and execute the proc on UTIL as follows:
UTIL.dbo.Proc1Sp Table1 – It cannot find Table1.
UTIL.dbo.Proc1Sp Table2 – I get a resultset.
Is my thinking wrong? Does the procedure only look at the database where it resides? If I have many databases and want to build a collection of utilities, do I have to copy them to all the databases or can I put them all in one place?
Thanks… mpv
Thank you Patrick for your great response on 4th April 2008. I was having terrible trouble trying to get a linked server to work on SQL 2005 from SQL 2000. I was creating it in Enterprise Manager to no avail but when I created it as you said in your steps it works perfectly! Thanks again!
Hello Pinal,
You sight is wonderful. I’ve found so much information here. I have a question for you.
Our company has 13 SQL Server Databases. Currently each database has a duplicate copies of all of our Stored Procedures (SP). I would like to centralize these SP into one database then perhaps add the database name to the arguments to get data from the other database. This central database would not contain the tables just the SP while the other database would contain the data but not the SP.
Example:
CentralDB.SP1(‘TestDB’, …)
{…
select * from Table … table does not exist in this database
}
called from
TestDB.SP1(…) … table exists in this database
Hi Pinal,
Some One told me that “The new table created with the SELECT INTO clause in linked servers does not support 4 part names.
But I am not sure, Do we have this thing documented any where ?
As in BOl they have just mentioned that SELECT INTO is supported in linked servers as well.
Thank You,
Mithalesh
Hiiii
I want to execute one SP available at Remote Server.
when i am executing it as you told:
Execute [192.168.1.82\SQL2005].[PCFileParser].[dbo].[usp_RemoteCalling_Test]
It is giving Error:
Msg 7202, Level 11, State 2, Line 1
Could not find server ‘192.168.1.82\SQL2005’ in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
Please solve my problem as soon as possible.
My client don’t want to use linked Server, so can’t use OPENQUERY clause.
Hi Pinal,
I am looking for a T-SQL script that automatically script out all the linked server. is it possible in SQL server. Please guide me.
Regards
Priyaa
Yes Priya, if you go object explorer details and select the Linked Servers that you want to script –> Right Click –> Script to New Query Window and you will have all Linked Servers in a new query window. This applies to SQL 2005 and onwards. for SQL 2000, there is a script that scripts all Linked Servers.
hi how i call Stored Procedure in asp.net
YRAIS,
You may want to have each stored-procedure store a static value or some sort of identifier into one of the fields of the table. That way that field in the table holds the source of the data. When you run a query you see where the data came from.
RJM
Hi,
I Have the same problem reported by Luis Alberto, calling stored procedures with parameters.
Please If you have some notice post it.
Thanks.