Help with UDF using OPENROWSET to EXECUTE sproc

From: JJA (johna_at_cbmiweb.com)
Date: 12/30/04 

Date: 30 Dec 2004 10:21:48 -0800

Here is the UDF I am trying to create:

ALTER FUNCTION dbo.TieredAccessCounties
(
@State CHAR(2)
, @SourceTable CHAR(1)
, @UserName VARCHAR(30)
)
RETURNS TABLE
AS
RETURN
SELECT A.* FROM
OPENROWSET('SQLOLEDB','MDWDATA';'sa';'passwordX',
'EXECUTE dbo.AccountFetchCounties NULL, @SourceTable, @UserName ')
AS A

I cannot get this to work. I am getting these messages:

Server: Msg 8180, Level 16, State 1, Procedure TieredAccessCounties,
Line 10
Statement(s) could not be prepared.
Server: Msg 137, Level 15, State 1, Procedure TieredAccessCounties,
Line 10
Must declare the variable '@SourceTable'.
[OLE/DB provider returned message: Deferred prepare could not be
completed.]

Relevant Pages

  • display View output in DataGridView
    ... a fixed name UDF dragged onto the DBML referencing the fixed name VIEW; ... Even though there is no change in its code ALTER FUNCTION also has to be run ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Record Level Security
    ... any other user via the UDF that doesn't qualify while reading data. ... ALTER FUNCTION fn_ShowMyEntries ... -- Logged in as dbo, ... insert into fn_ShowMyEntries(Item, OtherAttribute, User_Name) ...
    (microsoft.public.sqlserver.security)