မင်္ဂလာပါ!

လှိုက်လှဲစွာကြိုဆိုပါသည်။ ယခု ပထမဆုံးအကြိမ် ရောက်ဖူးခြင်းဖြစ်ပါသလား? ဝင်ရောက် ဆွေးနွေး မေးမြန်းလိုပါလျှင် အောက်တွင်ဖော်ပြထားသော button များမှတဆင့် ဝင်ရောက် ဆွေးနွေးနိုင်သကဲ့သို့ အဖွဲ့ဝင်အသစ်အနေဖြင့်လည်း လျှောက်ထားနိုင်ပါတယ်။

MYSTERY ZILLION တွင် English သို့မဟုတ် Unicode ဖြင့်သာ အသုံးပြုခွင့်ရှိသည်။ ဇော်ဂျီ ၊ ဧရာ စသည်တို့ကို အသုံးပြုခွင့် မရှိ။ Unicode fonts များမှာ Mon3,Yunghkio, Myanamr3 စသည်များ အသုံးပြုနိုင်သည်။ Unicode Guide ကို ဒီမှာ Download ချပါ။ Zawgyi to Unicode Converter
Don't share ebook or software if nobody request. You can find free book websites on here. We are welcome for discussion or asking question instead.

How to select from stored procedure / place stored procedure in view sql server?

One of vendor synchronization system only recognizes the SELECT statement and cannot execute the stored procedure.
So, instead of using EXEC MyProc, I have to find a way to call SELECT with stored procedure.

I query the following SELECT statement.
SELECT * FROM
OPENQUERY(SERVERNAME, 'EXEC MyProc')

To find out what is my SERVERNAME,
(1) I check SELECT name from sys.servers and place the result in SERVERNAME value. But it did not work.

(2) So, I follow to create LOCALSERVER linked server according to http://sqlserverplanet.com/dba/local-linked-server.

I got this error :
Cannot process the object "exec usp_sample". The OLE DB provider "SQLNCLI11" for linked server "LOCALSERVER" indicates that either the object has no columns or the current user does not have permissions on that object.

What I want to do is once I can execute above SELECT statement, I will create it as view table and call the statement like SELECT * FROM MYVIEW.

Please advise if there is any better way. Thank you.

မှတ်ချက်များ

  • edited August 2014 Registered Users

    OpenQuery

    To use OpenQuery, you have to add link server first before using it. Here is the example-

    EXEC sp_addlinkedserver
    server='Link_Server_Name', -- your link server name to be used in later steps
    srvproduct='',     
    @provider='SQLNCLI', -- using SQL Server native client
    datasrc='.\SQL2012',   -- you server name here (IP or Name)
    @location='',
    @provstr='',
    @catalog='Database_Name' --your database name here
    
    --This is how you can drop your linked server
    IF EXISTS (SELECT 1 FROM sys.servers WHERE name = 'Link_Server_Name' AND is_linked = 1)
    BEGIN
        EXEC SP_DROPSERVER Link_Server_Name;
    END
    
    SELECT * 
    FROM 
    OPENQUERY(Link_Server_Name,'EXEC StoredProcedure_Name @parameter=value') --You could also define storedprocedure parameter like this
    

    OpenRowSet

    Alternatively, you could also try "OpenRowSet" to execute storedprocedure from remote server.

    Prior to use OpenRowSet, you need to enable Ad hoc queries on the server.

    *** Don't try this if you are not the Database administrator ***

    --Enable Ad Hoc Queries
    sp_configure 'Show Advanced Options', 1
    GO
    RECONFIGURE
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1
    GO
    RECONFIGURE
    GO
    
    --Execute StoredProcedure from remote server
    SELECT *
    FROM OPENROWSET('SQLNCLI','Server=.\SQL2012;Database=YourDBName;Uid=user_name;Pwd=your_user_password','SET FMTONLY OFF;SET NOCOUNT ON;EXEC dbo.StoredProcedure_Name')
    

    Note: You need to execute first 2 queries ONLY one time. You don't need them after your server was configured to allowed Ad Hoc queries.

    Hope this help!

Sign In or Register to comment.