Re: Help with UDF using OPENROWSET to EXECUTE sproc

From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 12/30/04 

Date: Thu, 30 Dec 2004 13:29:12 -0500

OPENROWSET is opening the connection to MDWDATA and executing the following
(literally):

'EXECUTE dbo.AccountFetchCounties NULL, @SourceTable, @UserName '

It has no idea what @SourceTable and @UserName are. Unfortunately, there's
no way I know of to pass values into OPENROWSET within a UDF.

Can you describe what you're trying to do? Maybe there's a better way than
using a UDF. 

-- 
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"JJA" <johna@cbmiweb.com> wrote in message
news:1104430908.817837.179350@z14g2000cwz.googlegroups.com...
> 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

  • Re: Ergebnis einer SP in einer Sicht
    ... Zu Zeiten von UDF sollte man nicht mehr auf solche Konstrukte zurückgreifen müssen;-) ... Schlimmer ist allerdings das das OPENROWSET in einem anderen Scope ...
    (microsoft.public.de.access.clientserver)
  • RE: custom xp_cmdshell on SQL Server
    ... an OPENROWSET statement, that should solve the issue, as the ... SQL Server not always runs as NT ... executing system32 binaries). ... Cenzic Hailstorm finds vulnerabilities fast. ...
    (Pen-Test)
  • Re: Using OPENROWSET and fileds order...
    ... While Jacco is quite right, you should be able to get the columns in ... Dave wrote: ... >I'm trying executing this command on the SQL query analyzer: ... >SELECT * FROM OPENROWSET ...
    (microsoft.public.sqlserver.server)
Loading