SQL Server Performance

Mimicing a View with an extended stored proc

Discussion in 'SQL Server 2005 General Developer Questions' started by ehausig, Apr 19, 2006.

  1. ehausig New Member

    I have an extended stored procedure I wrote in C# that queries a web service, builds out a SqlDataRecord object, and returns the results to SQL Server via the SqlContext.Pipe.SendResultsStart(), SqlContext.Pipe.SendResultsRow(), and SqlContext.Pipe.SendResultsEnd() methods.<br /><br />It seems to work beautifully in SQL Server Management Studio and Visual Studio 2005. However, now I would like to access it via a SQL View, so that end users can use it in SELECT statements. I tried accessing it via OPENQUERY() and OPENROWSET(), but both return the following error message:<br /><br /><font face="Courier New">Msg 7357, Level 16, State 2, Line 1<br />Cannot process the object "EXEC dbo.GetAPList". The OLE DB<br />provider "SQLNCLI" for linked server "REPORTING" indicates that either<br />the object has no columns or the current user does not have permissions<br />on that object. </font id="Courier New"> <br /><br />I am an sa and can successfully execute dbo.GetAPList in a new query window. It seems that all the metadata is coming back fine in my query windows -- it looks just like a normal resultset.<br /><br />I can run queries like <font face="Courier New">SELECT * FROM OPENQUERY(REPORTING, 'EXEC sp_who')</font id="Courier New"> without a problem.<br /><br />I tried wrapping it in another stored proc called dbo.GetAPListCaller and executed it with sp_executesql to try to trick OPENQUERY() and OPENROWSET() into thinking the datasource was a "normal" stored procedure, but I received the same error message. (The wrapper did enable Crystal Reports 10 to recognize it.) [<img src='/community/emoticons/emotion-5.gif' alt=';)' />] <br /><br />I would like to join on the data, otherwise I'd just use the stored proc. Any insights or suggests would be greatly appreciated. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />
  2. cmdr_skywalker New Member

    who is the end user? sa? check out the privs/permission for the "end user".
  3. ehausig New Member

    quote:Originally posted by cmdr_skywalker

    who is the end user? sa? check out the privs/permission for the "end user".

    Yes. I'm an sa and am using a trusted connection when executing it.
    I can execute the stored proc fine in a new query window with EXEC dbo.GetAPList. The error message only occurs when calling it from within OPENQUERY() or OPENROWSET().
  4. ehausig New Member

    It seems there really isn't an elegant way to do this, so I guess I'll just have to push data to temp tables and read them from there. [xx(]
  5. cmdr_skywalker New Member

    Here are few suggestions you might want to try:
    1. provide the database and parenthesis when you call the function (i.e. EXEC ("database.dbo.GetAPList")
    2. Check the security context of the calling procedure, not the Query Analyzer user that you are using (what is used in the ADO connection object to log in?)
    3. Yes, use a temporary table or use a function instead.

    Hope this helps.
  6. ehausig New Member

    Interesting. I haven't explored using a CLR Table-Valued Function yet, but it's worth a try. (I've never written one before.)

    Thanks for the idea!

Share This Page