SQL Server Performance Forum – Threads Archive

Mimicing a View with an extended stored proc

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. 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: Msg 7357, Level 16, State 2, Line 1 Cannot process the object "EXEC dbo.GetAPList". The OLE DB provider "SQLNCLI" for linked server "REPORTING" indicates that either the object has no columns or the current user does not have permissions on that object. 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. I can run queries like SELECT * FROM OPENQUERY(REPORTING, ‘EXEC sp_who’) without a problem. 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=’

‘ />] 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=’

‘ />]

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

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().

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(]

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.

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!

]]>