Post reply

Creating view thru stored proc ;-)

  • December 23, 2004 at 1:58 am

    #66654

    -- is it possible to create a view like this...

    create view dbo.myview

    as

    select * from openquery([server], '{call DB..sp1}')

    --or by using openrowset()

    --tried, but not working.

    --anybody done it?

    --i'm using sql7

  • December 23, 2004 at 2:03 am

    #534865

    No, haven't done it.

    Can you expand on more precisely what you're trying to do?

    /Kenneth

  • December 23, 2004 at 5:47 am

    #534879

    I have done it before.  You have to write your entire statement to a variable and then use sp_executeSQL to process it.

    With that said IF I knew then what I know now I would not do it.  I would instead have used Table UDFs that have parameters passed into them and avoid the whole dynamic SQL portion.

    Unfortunately, I have left that company, a friend of mine still calls the procedure during their month end and it works nicely, but, I'm still unhappy about it.



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • December 28, 2004 at 6:23 am

    #535128

    Hi,

    Try this:

    create view vw_View_SP
    as

    select *

    from openrowset('SQLOLEDB','mySERVER';'SQLUser';'password',

      'myDataBase.dbo.usp_MyStoredProc @param=1')

     

    In my case this work fine.

    Regards

  • December 28, 2004 at 7:26 am

    #535141

    Yes it is possible.

    For some system sp you will have to feed the query with some tricks like: Select NULL, or SET FMTONLY ON, etc  but if your sp is just a user defined sp you can definetly use the above (Luciano Leston) script it WORKS

     

    HTH

     


    * Noel

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply