SQL SERVER – Executing Remote Stored Procedure – Calling Stored Procedure on Linked Server

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 (http://blog.SQLAuthority.com)

About these ads

121 thoughts on “SQL SERVER – Executing Remote Stored Procedure – Calling Stored Procedure on Linked Server

  1. The servers are set up as linked servers, server A is sql 2005 and the remote server B is sql 2000.

    The procedure has to be run on B, it gets values from a procedure running on A and returns a result to the proc on A.

    I used the four part naming convention.

    When i execute the following
    EXEC @result = Dino.Prod.[dbo].[Proc_MCC_Payroll] @rows, @rows_retran;

    I get an error Server ‘Dino’ is not configured for RPC.

    both servers run on windows 2003 server enterprise edition SP2
    How do I fix this?

  2. Is there a way to execute the call to the remote stored procedure dynamically where the remote stored procedure accespts 2 input parameter and one output parameter?

    Remote Stored procedure Header
    Create Procedure usp_BillBroadcast (
    @intFileFormat int,
    @intFileType int,
    @intBroadcast int out)

    Since I cannot hardcode the link server name in my code, is there any way that I may be able to call the above stored procedure? Can you please cover it in detail?

    Your help is greatly appreciated.

  3. About the error “is not configured for RPC”. We have fix it, chaning ( on local server), linked server configuration to remote server setting “outbound RPC” as True.

    We use:
    EXECUTE (‘ select …etc… ‘ ) at

    To Pinal Dave: Tanks, This blog is very interesting. I’m learning a lot.

  4. Sorry, Problems with puntuaction shadown command line on my post. This is complete:

    About the error “is not configured for RPC”: We have fix it, chaning ( on local server), linked server configuration to remote server setting “outbound RPC” as True.

    We use:
    EXECUTE (’ select …etc… ‘ ) at remoteserver

    To Pinal Dave: Tanks, This blog is very interesting. I’m learning a lot.

  5. Have you ever come across a situation in SQL 2005 where a query in the form of a stored proc, comes across as a llinked server call and gets hung, often causing it to be a lead block?

    I have a few sprocs that are called frequently, and every so often they become hung, blocking other processes requiring the spid to be killed manually…Because we are a 24/7 shop we have gone as far as setting up jobs that look for these spids and KILL them when they meet the criteria that we have identified.

    Any ideas what could be causing this? Anyone else experience this in their org? Your thoughts & experiences are very much appreciated!

    C~

  6. To follow up on RAM’s question, if you are using 2005, setup a linked server and a synonym. The synonym will allow you to “hard code” the server name and database instance name once. The synonym will act as an alias to the remote database. If you change the synonym to point somewhere else, any code referencing the synonym requires no changes. I only wish 2000 had synonym…

  7. I am thankful for sites like this that really try to help. I have been struggling to call a stored proc from a linked server when the calling server is SQL 2005 and the linked server is SQL 2000. After a lot of reading and experimenting I found the solution, which I have laid out in simple steps below. Good Luck!

    – Add a linked server
    EXEC sp_addlinkedserver
    @server=’BOB’,
    @srvproduct=”,
    @provider=’SQLOLEDB’,
    @datasrc=’SERVER’,
    @catalog = ‘Northwind’

    – get a list of linked servers
    EXEC sp_linkedservers

    – add linked server login (this user and password has already been created on linked server)
    EXEC sp_addlinkedsrvlogin ‘BOB’, ‘false’, NULL, ‘userlogin’, ‘password’

    EXEC sp_serveroption @server=’BOB’, @optname = ‘rpc’, @optvalue=’true’
    EXEC sp_serveroption @server=’BOB’, @optname=’rpc out’, @optvalue=’true’

    –see all linked servers and status
    EXEC sp_helpserver

    – perform query against linked server
    SELECT * FROM OPENQUERY (BOB, ‘SELECT * FROM customers’)

    – execute stored proc on Linked Server
    declare @CustomerID char(50)
    EXEC BOB.Northwind.dbo.CustOrderHist ‘ALFKI’

    – drop linked server logins (must be done prior to droping linked server)
    EXEC sp_droplinkedsrvlogin ‘BOB’, NULL

    – drop linked server
    EXEC sp_dropserver ‘BOB’

  8. I would like to run the following on a server that is linked in.

    backup database @sourcedb to disk = @backup_file

    Can you help with the syntax. When I specify
    [remote server]…backup datebase [remotedb] to disk = [file]

    Thanks

  9. Tony,

    You can’t use a linked server to address the database you want to backup.

    You can use ” sp_executesql ” to execute an SQL string on the remote server

    EXEC .master.dbo.sp_executesql N’BACKUP DATABASE pubs TO DISK = ”c:\pubs.bak”’

  10. Hi, problem is re Linked Servers and rpc calls.

    Using SQL Server 7 (A) rpc to SQL Server 2005 (B).

    A is setup with a linked server B and can replicate to B – NO problems. (The replication was setup with sa connection)

    I have a linked server setup with a standard user and appropriate access rights. Below 1 query works the other fails. Any ideas? It used to work until I had to recreate everything due to firewall failure.

    Query fails
    1. select * from SERVERNAME.dbname.dbo.tablename
    > Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider ‘SQLOLEDB’ reported an error. The provider did not give any information about the error.

    Query Suceeds
    2. select * from openquery(SERVERNAME,’select * from tablename’)

    I have also created said same linked server on another SQL server 7 (C) db and both queries work NO PROBLEMS. It’s hard to crack the problem. Any ideas?

  11. For extra info. using Patrick example above. All works for OpenQuery but same error when executing

    select * from SERVERNAME.dbname.dbo.tablename

  12. Sorry fellas you took too long. ;-)

    to solve the problem go the registry
    (HKLM\Software\Microsoft\Mssqlserver\providers\SQLOLEDB ) and make sure all
    except “AllowInProcess” have a value of 0.

  13. Dave

    I have an intresting situation whereby a stored procedure called via a 2005 linked server connection to an SQL 2000 database fails to complete but does not raise an error. The execution of the Stored procedure is aborted and the calling SQL procedure continues from the next statement.

    If I run the stored procedure in management studio in completes correctly.

    If I run the SQL job that calls the stored procedure it aborts without error as previously mentioned.

    Finally this behaviour is unpredictable in that this problem has only started to happen recently. No changes have been made to any of the stored procedures

  14. Since this thread is the closest I’ve found to my problem I was hoping someone may be able to help me with the folllowing issue:

    I have a stored procedure on a SQL 2005 server exec’ing a stored procedure on a SQL 2000 server through a linked server. I receive the following error on this exec:

    The attempt by the provider to pass remote stored procedure parameters to remote server ‘TEMP_TOPTAGGING’ failed. Verify that the number of parameters, the order, and the values passed are correct.

    This exact same scenario works correctly going from SQL 2005 to SQL 2005…just not from SQL 2005 to 2000.

    I have verified that the parameters, orders and values are correct.

    Has anyone had a similar issue?

    Thanks,

    Bill

  15. 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.

  16. 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’.

  17. 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..

  18. @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.

  19. 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.

  20. 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.

  21. 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

  22. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 1 Journey to SQL Authority with Pinal Dave

  23. 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

  24. 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

  25. 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!

  26. 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

  27. 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

  28. 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.

  29. 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.

  30. 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

  31. Hi,
    I Have the same problem reported by Luis Alberto, calling stored procedures with parameters.
    Please If you have some notice post it.
    Thanks.

  32. Query with parameters
    —————————-
    SELECT * FROM OPENQUERY([ServerInstance],’exec Test.dbo.USP_Process ”1234567890123456”’)

    Dynamically pass parameters
    =====================
    declare @sql varchar(1000),@param varchar(16)

    set @param=’1234567890123456′

    set @sql=”” +’exec Test.dbo.USP_Process ‘ + ””” +@param+ ””””

    EXEC (‘SELECT * FROM OPENQUERY([ServerInstance],’+@sql+’)’)

    Also,you can insert the results into a table.
    Hope this helps…

  33. Hi Expert

    i m learining to sql server2005 initialy . so i have one doute in store procedure how can i use the month and year in store procedure

    regrds by
    karthik

  34. Hello all. I am a newbee to Link Servers and this blog is coming pretty handy to get started, so thanks everyone for the info.

    Up to the moment we have been using one sql server containing all the DB of our proyects but now we are changing to linked servers. The problem is that we are going to have to change all the Stored Procedures of all our databases, which really is going to be a lot of work, so I am trying to find a way of doing this that doesn´t imply changing by hand every single SP, considering also that the location of a particular database may change from time to time and we´ll have to do the same operation more times.

    Any ideas would be nice,

    Thanks.

  35. when changing all the stored procedures I meant where before we had DB.Table we´ll obiously we´ll have to change it toDB.Table we´ll have to change it to Server.DB.Table

  36. hello,

    i am trying to call a mysql 5.1.26 procedure from sql server 2000 procedure.
    the server is linked properly
    when i do a select statement using openquery for just getting the remote data it works.
    but i am getting this error..
    my remote server name is TOMySql
    and the schema belongs to root.

    Could not execute procedure ‘myproc’ on remote server ‘TOMySql’.
    [OLE/DB provider returned message: [MySQL][ODBC 3.51 Driver][mysqld-5.1.29-rc-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘?=call “myschema”.”myproc”;1′ at line 1]

    the sql server 2000 procedure is

    alter proc mydistproc
    –@name varchar(50),
    –@address varchar(50)
    as
    begin
    exec TOMySql..myschema.myproc

    –EXEC TOMySql.root.myschema.myproc()
    –select * from OPENQUERY(TOMySql, ‘call myschema.myproc’)
    –exec(OPENQUERY(TOMySql, myschema.myproc))
    –insert into OPENQUERY(TOMySql, ‘SELECT name, address FROM myschema.temp’)
    –select @name, @address
    –ROWLOCK

    end

    ————————————————————————

    the my sql procedure is

    DELIMITER $$

    DROP PROCEDURE IF EXISTS `myproc` $$
    CREATE DEFINER=`root`@`%` PROCEDURE `myproc`()
    BEGIN
    select ‘hi’ as greetings;

    END $$

    DELIMITER ;

    ———————————————————————-

    Kindly help
    i dont know where i am going wrong..

    Regards
    Nitesh

    • Hi Nitesh

      My name is Ludwig and I’m getting exactly the same error from MS Server 2008 when calling a stored procedure (SP) on MySQL 5.2.34.

      I can’t seem to call the SP directly:
      EXEC ERA.fswd_eras.dbo.RunMySP;

      ERA is the linked server and like you I can also use the Openquery to perform selects, inserts and updates, but openquery does not support calling functions on MySQL.

      Have you managed to solve the problem as yet?

      Regards
      Ludwig

  37. Hi all,

    I created a linked server using below syntax
    – Stored Procedure to Create Linked Server
    EXEC sp_addlinkedserver
    @server = ‘TEST1′,
    @srvproduct = ‘SQLServer OLEDB Provider’,
    @provider = ‘SQLOLEDB’,
    @datasrc = ’192.168.20.45\ls’

    It is successful

    I am trying to execute stored procedure created in ”192.168.20.45\ls”

    EXEC TEST1.IELS_SIT_SUB_7Jan.dbo.Spu_Poc_ShowHearingDetails ‘null’,’null’

    but it is throwing below error

    Msg 7357, Level 16, State 2, Line 1
    Cannot process the object “exec IELS_SIT_SUB_7Jan.dbo.sp_test”. The OLE DB provider “SQLNCLI” for linked server “TEST1″ indicates that either the object has no columns or the current user does not have permissions on that object.

    Please some one help me in this.

    Thanks in advance

    • For executing a stored procedure that requires parameters and it’s on a linked server I suggest you try the AT version of exec … For example:

      exec (‘exec IELS_SIT_SUB_7Jan.dbo.Spu_Poc_ShowHearingDetails null, null’) AT TEST1

      Details in SQL Server BOL. Hope this helps. Regards Nik.

  38. need help, i am using sql server 2005, i have to schedule a job in which i have to call a stored procedure that is made in Oracle 10g….is this possible? ho0w can we do this?

  39. If the SQL Server service account does not have execute rights on remote stored procedure than create a Credential using the user who has rights, then on that credential create a Proxy and use that proxy in agent job step.

    Regards,
    Pinal Dave

  40. Hi.
    I am passing table value paramter from one server to another server but I am getting the following error.

    valued parameters are not allowed in remote calls between servers.

    I am using sql server 2008 so do you have any solution for this ??

    • @Shailesh,

      Is it possible to share SQL you are using. It would be helpful to resolve this issue.

      Can;t you create Type data type in remote server and use it locally.

      ~IM.

  41. I hope this thread may still be alive? I am adding to an application that uses SQl2005. I have used Dynamic SQL in a Stored Procedure to retrieve data. Because of the way the application is structured I need to select the result set into a View. I have tried many ways to do this – including OpenRowSet (needs Ad Hoc Queries on (which they are not) – and accessing the Stored Procedure via a Linked Server using OpenQuery – which returns errors

    SELECT * FROM OPENQUERY([SERVER-1].Test1DB.dbo.[SP_RFTA])

    gives error:
    Incorrect syntax near ‘.’.

    any thoughts – and thank you in advance,
    PJ

  42. Hi pinal,

    I am working with sql server 2005. My company db is very huge. so i am planning to divide the db with help of linked server concept. Previous 7 years datas put into separate machine and forcoming datas are put into separate machine.so the we can fetch the data very fast manner. i don’t know how to do.. plz guide me..

    Thanks,
    Vinoth

  43. I have a weird one…
    what if the column is named something like [name.name]?

    I get the following error:
    Msg 7357, Level 16, State 2, Line 1
    Cannot process the object “”MyDB”.”dbo”.”test””. The OLE DB provider “SQLNCLI” for linked server “MylinkServer” indicates that either the object has no columns or the current user does not have permissions on that object.

    Table name is TEST
    Column name is [Ship.Address1]

    I can get around this by creating a view and renaming my columns as Address1, but is there another solution?

  44. Hi Pinal Dave,
    My name is Metin Gümüş and I m from Turkiye. I m really proud of learning you and letting us know about your knowledge.

    Thanks Dave.

  45. can anyone help us to execute mysql stored procedure from mssql trigger. or please provide some useful link which describes how to do it.

    Regards
    V.S.Jain

  46. Hi,

    How can I call a remote Oracle 10g procedure from SQL Server 2000?

    We tried the following sentences:
    exec

    [LINKSERVER].Chema.Package.Procedure param1, param2

    We received the following error message:

    [OLE/DB provider returned message: One or more errors occurred during processing of command.]

    Msg 7212, Level 17, State 1, Line 1
    Could not execute procedure ‘XXXX’ on remote server ‘LINKSERVER’.

    We also tried testing teh LINKSERVER with SELECT * FROM OpenQUERY (LINKSERVER, ‘SELECT * FROM TABLE’) and it worked fine. We know that we cannot use OPENQUERY for execute procedures.

    Please, could you help us?
    Thanks in advance
    Regards,
    manager

    • Hi,
      in Oracle 10g one out parameter will be defined as SYS_REFCURSOR. when the parameters are passed to stored procedure, OUT parameter is not taken care.

      i am also facing the same problem. as described by you. if you find the answer please post it to site.

      Thanks
      Regards,
      Mohan

  47. how can i call a SP through below way. And where i have to mention the parameters?

    SELECT * FROM OPENQUERY
    ([DEVSQL],AdventureWorks.dbo.spAdmin_GetAllUsers)

      • Hey madhivanan

        I got an error in this syntax.
        My statement is like this :
        ([DEVSQL],AdventureWorks.dbo.spAdmin_GetAllUsers ‘parameter value’)

        it gives error like : “Incorrect syntax near ‘AdventureWorks’.”

  48. Using method #1, is there a way to keep the ‘remote server’ name variable ? It will really help lot of issues. I am using method #1 to insert record into Oracle from SQL Server using linked server. The Oracle table has a time stamp column and this is the only way I could actually do so. Issue is that Linked Server (remote server) could be variable. Any suggestions ??

    Thanks in advance.

  49. Here is the SQL syntax that I am working with:

    declare @currentDateToUse datetime ;
    set @currentDateToUse = convert ( datetime, getdate());

    Exec (‘ insert into SchemaName.TableName( col1, col2 ) values (?,?)’, @var1, @currentDateToUse ) at OracleLinkedServer1

  50. Hello
    I want to call linked server stored procedure in trigger.
    But it’ll be given me an error.

    Error :
    Msg 7391, The operation could not be performed because OLE DB provider “SQLNCLI10″ for linked server “ServerName” was unable to begin a distributed transaction.

    Thanks in advance

  51. Hi,
    I am getting the following error when I am trying to connect to a remote database via stored proc.

    Login failed for user(null) Not a trusted SQL Connection.

    I am trying to access a remote database.

    I did the following settings on the machine:
    1)I added the server which I am trying to coonect in the Linkedserver using sp_addlinkedserver
    2)I checked the ‘Enforced Distriuted transactions’ in Connection tab is sqlserver properties.
    3)I am using sql2000 and installed the service pack 4 on tht machine.
    4)I have also set the allow inbound,outbound connections in the Component services in Administrative tools of Control panel.

    Do I need to do something more?

  52. I have one stored procedure in one database and its inserting values to three tables simultaneously. Two tables are in the same database and another one in different DB but in the same server.

    When i was trying to insert into table of second database it was not inserting and i used OpenDatasource() for inserting that.
    But what happens now is; i am missing some data as it takes more time for executing the stored procedure. This procedure have to execute 4 times in every second.

    Please suggest me a solution.

    Regards,
    Shenu Lal

  53. Hi ALL,
    Can link server be setup between sql server 2005 and sql server 2008. The data will be fetched from sql server 2008 database. The link server is defined in sql server 2005. I get following error:

    OLE DB provider “SQLNCLI” for linked server “xxxx” returned message “Login timeout expired”.
    OLE DB provider “SQLNCLI” for linked server “xxxx” returned message “An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.”.
    Msg 53, Level 16, State 1, Line 0
    Named Pipes Provider: Could not open a connection to SQL Server [53].

    Thanks a lot,
    Amit

  54. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 3 of 31 Journey to SQLAuthority

  55. I’m a newbie and just starting to use linked servers. I created the linked servers and have no problem calling stored procedures on the linked servers. I need to be able to call the same stored procedure on all the linked servers setup (a little less than 100). I am using a vbscript to execute the stored procedure from one server. I need to be able to call the stored procedure when needed for the needed linked server. My question is…Do I need to create the stored procedure on each individual linked server database in order to call it from a ‘centralized’ program/server? Thanks

  56. Question: I have a main database and a sattelite database in a SQL 2008 linked configuration. I’m running the following query in my sattelite database and want to know where would the actual query execute?

    select C1,C2,C3, A1,A2,A3, A4 from MainDatabase..dbo.Customers inner join Accounts on Customers.id = Accounts.CustomerId and Customers.lastName=’Smith’

    Will data first be copied from the MainDatabase to my local database and then the Join/Search Will occurr? Or the Whole Query will be shipped to the Main Database for execution.

  57. Hi,Pinal,
    Using spAddLinkServer i can add remote machine and execute remote sql commands.
    I have few questions:
    1) when it adds the remote server , all the databases of the remote server are visible.Can’t it be only the required database?
    2) In some cases the command allows to add the link server . while exploring the catalog it shows error: login fail for user sa. Error code is:18456

  58. Guys!!
    Is there any way to copy data of one or more tables from your local database to a remote database , without using LinkedServer Concept.

  59. I need to copy data of one or more table from a local SQL database to a hosted server database. Most of the data in the local database is copied to the live one, though optimisations are made to reduce the amount of actual data required to be sent.

    What is the best way of sending this data from one database to the other?

  60. hi,
    got a problem on how to confine server name into a local variable.

    Example:
    declare @Server varchar(100),
    @Database varchar(100)

    SET @Server = ‘server’
    SET @Database = ‘Database’

    Exec [@Server].[@Database].[dbo].[sp_Storeprocedure]

    with example above, i’ve got error 7202
    any idea how to handle this?
    thanx

  61. I have a problem where we are migrating from Sybase to SQL Server. I need to be able to invoke a stored procedure in Sybase from Sql Server. The issue is that these STPs have output paramteters. I have a link set up but can not figure out how I can get to the output parameters. I ended up basically building lot of code in the OPENQUERY but while it seems to run it also does not work

    execute (‘use tord_ord
    declare @priceed datetime,
    @error_code int,
    @error_message varchar(255),
    @a char(10),
    @b varchar(10),
    @c varchar(10),
    @d varchar(10),
    @e int,
    @f char(2),
    @x varchar(300)
    set @priceed = CAST(”2007-05-08 12:35:29.123” AS datetime)
    set @a =”1234567890”
    set @b = ”1234567890”
    set @c = ”1234567890”
    set @e = -1 /* should cause error with TN set below”
    set @d = ”1234567890”
    set @f = ”TN”
    set @error_code = 101010
    set @error_message = ”startng mmessage”

    exec STP_SIP_OA_close @a,@b,@c,@d,@e,@f,@priceed,@error_code,@error_message
    set @x = rtrim(cast(@error_code as char(20)))
    select @error_message as error_message
    select @error_code as error_code
    select @a
    select @x
    select @f
    select @a
    ‘) at WPOALINK

    And though no errors were reported I do not think it really invoked the stored procedure the values of error_code and error_message stayed the same. One of the first things the STP does is set them to 0 and an empty string.

  62. hi madhivanan ,is there a way to call remote procedure in hibernate.is it can be done.can you give some suggestions

  63. Hi Pinal,
    I want in detail about Linked Servers in sql server 2008. I want to connect Oracle 10g in SQL Server 2008. Pls help me in this issue.

  64. do you guys know how to execute a sotred procedure (in a linked server, Sybase this time) which needs an output parameter with openquery o any other option? I´m trying this:
    sp in sybase:
    CREATE PROCEDURE dbo.usp_genera_saldo (@anio smallint, @periodo varchar(1), @cuenta int, @cve_con int, @importe_pagar decimal(30,2), @fecha_limpago datetime, @clave_carrera int, @opcion smallint, @salida int output )…….. end

    calling it in sql server :
    execute (‘Execute usp_genera_saldo (?,?,?,?,?,?,?,?,?)’ ,2012,’0′,171394,4,100,’2012/06/22′,904000,3,) AT SYBASE1
    or
    SELECT * FROM OPENQUERY(SYBASE1,’{EXECUTE usp_genera_saldo({ 2012, ”0”, 171394, 4,100,”2012/06/22”,904000,3,{? , ReturnVal})’)
    or
    declare @r int
    EXECUTE ( ‘begin usp_genera_saldo (?,?,?,?,?,?,?,?,?); end;’, 2012, ’0′, 171394, 4,100,’2012/06/22′,904000,3, @r output) AT SYBASE1;
    nothing works

  65. Hi all,

    I have a stored proc on server A, have linking from server B to server A,
    wanted to view server A text using sp_helptext as don’t have access to serverA.
    Ho to to do this..
    Select * from opequery(serverA, ‘sp_helptext spname’)
    not working throwing error…invalid object name #comments( this is an temp table whcih is created in sp_helptext procedure )

    Any idea.?
    Thanks,
    RK

  66. Hi all,
    I have 2 SQL 2012 servers that are linked. One contains a table of user, server, database and dates. I’m trying to create a process that will find expiring user by date and then drop them from the specific server and table (leaving the SQL login intact). I searched for days and it appears that this can’t happen. Can anyone make any suggestions?

    CED

  67. Pingback: SQL SERVER – Weekly Series – Memory Lane – #050 | Journey to SQL Authority with Pinal Dave

  68. hi
    I can’t execute stored procedure in sybase from sql server using Linked server.

    It throws below error if i use OpenQuery

    Cannot process the object “EXECUTE PROCEDURE”. The OLE DB provider “TEST ” for linked server “LNKSERVER” indicates that either the object has no columns or the current user does not have permissions on that object.

    When I use Four Part Name it throws below error

    OLE DB provider “TEST ” for linked server “LNKSERVER” returned message “Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.”.

    Could you please help me?

    Thanks in advance
    Jaya

  69. I am trying to Exectue sybase stored procedure using linked server in Sql server 2008 R2 but throws errors:

    Below are the methods i am using

    EXECUTE [LinkedServer].[DB].[dbo].[StoredProcedure] 1, ‘F’, NULL, NULL, NULL, NULL

    OR

    declare @Parameter1 NUMERIC(38) declare @Parameter2 NUMERIC(6) declare @Parameter3 VARCHAR(250) EXECUTE [LinkedServer].[DB].[dbo].[StoredProcedure] 1, ‘F’, NULL, NULL, NULL, NULL, @Parameter1 Output, @Parameter2 Output, @Parameter3 Output

    OR

    EXECUTE (‘EXECUTE [DB].[dbo].[StoredProcedure] ?,?,?,?,?,? ‘, 1, ‘F’, Null , NULL, NULL, NULL ) AT [LinkedServer]

    Below is the error :

    The attempt by the provider to pass remote stored procedure parameters to remote server ‘[LinkedServer]‘ failed. Verify that the number of parameters, the order, and the values passed are correct.

    I have set the RPC out values to true in Linked server properties. But I am not sure if I am missing any other properties.

    Could someone please help me

    Many thanks in advance

    S

    • This is solution
      Declare @Parameter1 NUMERIC(4)
      Declare @Parameter2 VARCHAR(1)
      Declare @Parameter3 DATETIME
      Declare @Parameter4 NUMERIC(8)
      Declare @Parameter5 VARCHAR(12)
      Declare @Parameter6 VARCHAR(28)
      Declare @Parameter7 NUMERIC(38)
      Declare @Parameter8 NUMERIC(6)
      Declare @Parameter9 VARCHAR(250)

      select @Parameter1 = 1
      select @Parameter2 = ‘A’
      select @Parameter3 = NULL
      select @Parameter4 = NULL
      select @Parameter5 = NULL
      select @Parameter6 = NULL
      select @Parameter7 = NULL
      select @Parameter8 = NULL
      select @Parameter9 = NULL

      EXEC (‘EXEC [DB].[dbo].[StoredProcedure] ?,?,?,?,?,?,? ,? ,? ‘,@Parameter1,@Parameter2,@Parameter3,@Parameter4,@Parameter5,
      @Parameter6,@Parameter7 Output,@Parameter8 Output,@Parameter9 output) AT [LINKSERVER]

  70. Hi Pinal,

    I am facing a issue regarding linked server. I have a SP which uses synonyms and fetch data using dynamic SQL from linked server. SP has lot of joins to the linked server tables but it doesn’t have any join with local tables. When I run the SP for some of the columns I am getting incorrect data(getting null value) but when i run the query directly(query which exec command will execute) I see correct data. I am unable to find any ans for the same.

    Any help is appreciated.

    Thanks,
    Punit Singhi

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s