none
Execute a stored procedure using select statement

    Question

  • I am trying to execute the stored procedure with openquery.

    set @BuildQuery='select * from openquery ('+@LinkServerName +',''exec GetRelationShipOnPolicyIdAndEmployeeId ''''@name'''')';

     

    I am getting the following error:

    Cannot process the object "exec dbo.GetRelationShipOnPolicyIdAndEmployeeId 'CBB0D5C1-07FB-E011-BA16-5452006D8355','9906B59E-F5DC-E011-816B-5452006D8355','A44456CF-CBE9-E011-BA16-5452006D8355','CEDA93E6-09E8-E011-BA16-5452006D8355'". The OLE DB provider "SQLNCLI10" for linked server "172.20.10.198" indicates that either the object has no columns or the current user does not have permissions on that object.

    Tuesday, October 25, 2011 4:13 AM

Answers

All replies

  • Are you able to run this SP with linked server?
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, October 25, 2011 4:19 AM
    Moderator
  • Yes im able to get the output of this SP,BUT not using openquery.
    Tuesday, October 25, 2011 4:22 AM
  • Hi Komal,
    Check these properties are false or true.
    Make them true.
    Linked server properties window.



    Shatrughna.
    • Edited by Shkumar Tuesday, October 25, 2011 8:07 AM
    Tuesday, October 25, 2011 8:05 AM
  • DECLARE @SearchQuery varchar(1024)
    DECLARE @SearchParm varchar(255)
    SELECT @SearchParm = '3389'
    SELECT @SearchQuery = 'SELECT * 
    FROM OPENQUERY(SERVERName, 
    ''exec sp_executesql N''''SELECT *
              FROM   dbname.dbo.tbl  
             WHERE  ComplainId= @Search'''', N''''@Search varchar(20)'''', @Search = ''''' + @SearchParm + ''''''')'
    PRINT @SearchQuery
    EXECUTE(@SearchQuery)

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, October 25, 2011 8:39 AM
    Answerer
  • Im getting following the error while opening properties window:

    You can not create local SQL server as Linked server.

     

    Tuesday, October 25, 2011 9:03 AM
  • Yes im exactly doing same thig but getting error.
    Tuesday, October 25, 2011 9:03 AM
  • I ran the above without problems, how about user permission?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, October 25, 2011 9:08 AM
    Answerer
  • Hi Komal10,

    You can have a try to use EXEC AT or directly execute the remote stored procedure using four-part names. Here is a similar thread addressing this issue: Executing remote stored procedure with OPENQUERY.

    This blog elaborates on how to use dynamic SQL execution on remote SQL Server using EXEC AT: http://www.mssqltips.com/sqlservertip/1757/dynamic-sql-execution-on-remote-sql-server-using-exec-at/.

    Best Regards,
    Stephanie Lv


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by Naomi NModerator Thursday, October 27, 2011 2:24 PM
    • Marked as answer by Stephanie Lv Tuesday, November 01, 2011 6:46 AM
    Thursday, October 27, 2011 7:14 AM