Help with UDF using OPENROWSET to EXECUTE sproc  
Author Message
SamHill30





PostPosted: Thu Dec 30 12:29:12 CST 2004 Top

SQL Server >> Help with UDF using OPENROWSET to EXECUTE sproc

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',

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

[OLE/DB provider returned message: Deferred prepare could not be
completed.]

SQL Server232  
 
 
Adam





PostPosted: Thu Dec 30 12:29:12 CST 2004 Top

SQL Server >> Help with UDF using OPENROWSET to EXECUTE sproc OPENROWSET is opening the connection to MDWDATA and executing the following
(literally):




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




> Here is the UDF I am trying to create:
>
> ALTER FUNCTION dbo.TieredAccessCounties
> (



> )
> RETURNS TABLE
> AS
> RETURN
> SELECT A.* FROM
> OPENROWSET('SQLOLEDB','MDWDATA';'sa';'passwordX',

> 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

> [OLE/DB provider returned message: Deferred prepare could not be
> completed.]
>


 
 
JJA





PostPosted: Thu Dec 30 13:04:17 CST 2004 Top

SQL Server >> Help with UDF using OPENROWSET to EXECUTE sproc The sproc dbo.AccountFetchCounties is a bit of business logic that
takes 3 parms and returns a set of rows representing state-county areas
that are "allowed" for a given username. I need to add this
functionality inside of a much larger stored procedure and I need to
JOIN the output of this sproc with another SELECT. The first problem I
ran into was that I got the message: INSERT EXEC CANNOT BE NESTED
because I had created a temp table to hold the rows back from
AccountFetchCounties (but this in turn was inside of an outer INSERT
into tableX EXECUTE myOuterSproc structure.

So that is why I thought to use User Defined Function to return a
table. But then I see that a UDF cannot call a stored procedure. So I
researched and discovered OPENROWSET as an alternative. But now I am
stuck with this strange message. I really appreciate your help. Thank
you for your quick reply.

 
 
Adam





PostPosted: Thu Dec 30 16:32:19 CST 2004 Top

SQL Server >> Help with UDF using OPENROWSET to EXECUTE sproc Can you post some code to duplicate the CANNOT BE NESTED error? I've never
seen it before, and was just about to recommend a temp table. There must be
a way around that...

--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--




> The sproc dbo.AccountFetchCounties is a bit of business logic that
> takes 3 parms and returns a set of rows representing state-county areas
> that are "allowed" for a given username. I need to add this
> functionality inside of a much larger stored procedure and I need to
> JOIN the output of this sproc with another SELECT. The first problem I
> ran into was that I got the message: INSERT EXEC CANNOT BE NESTED
> because I had created a temp table to hold the rows back from
> AccountFetchCounties (but this in turn was inside of an outer INSERT
> into tableX EXECUTE myOuterSproc structure.
>
> So that is why I thought to use User Defined Function to return a
> table. But then I see that a UDF cannot call a stored procedure. So I
> researched and discovered OPENROWSET as an alternative. But now I am
> stuck with this strange message. I really appreciate your help. Thank
> you for your quick reply.
>


 
 
Steve





PostPosted: Thu Dec 30 21:55:18 CST 2004 Top

SQL Server >> Help with UDF using OPENROWSET to EXECUTE sproc Adam,

Here's an example:

create table T (
i int
)
go

create proc p as select 4
go

create proc q as
insert into T exec p
go

insert into T exec q
go

drop proc p,q
drop table T

I believe EXEC can be nested, and it's just INSERT .. EXEC that can't be
nested. In other words, the statement INSERT INTO T EXEC q will fail
if the procedure q contains an INSERT .. EXEC construction.


SK



>Can you post some code to duplicate the CANNOT BE NESTED error? I've never
>seen it before, and was just about to recommend a temp table. There must be
>a way around that...
>
>
>
 
 
Adam





PostPosted: Thu Dec 30 23:54:09 CST 2004 Top

SQL Server >> Help with UDF using OPENROWSET to EXECUTE sproc

>
> I believe EXEC can be nested, and it's just INSERT .. EXEC that can't be
> nested. In other words, the statement INSERT INTO T EXEC q will fail
> if the procedure q contains an INSERT .. EXEC construction.

That makes perfect sense. The example you posted doesn't seem to do
anything, whereas at least something like this has some semblance of
purpose:

EXEC ('EXEC (''SELECT 1'')')

... not that I'd do that, but at least it makes more sense than nesting
an INSERT :)

--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--


 
 
JJA





PostPosted: Mon Jan 03 11:35:49 CST 2005 Top

SQL Server >> Help with UDF using OPENROWSET to EXECUTE sproc --- here is the 1st fragment, inside of a large production sproc. It
needs to create and populate #Areas which
--- will be used in JOINS later in the sproc and in the inner sprocs it
calls.

create table #areas
(
state char(2) ,
county char(3)
, SMSA_CD varchar(10) )
Execute dbo.AMS_I_GetAreasV2 -- 12/29/2004 JJA: add
parm for tiered-access support of custom areas using
dbo.AMS_I_GetAreasV2
@SMSA,
@S1,
@C1,
@SourceTable

Area Definitions as a source for populating #Areas table

Definitions conform to tiered access rules and limits

--- here is the 2nd fragment (EXECUTEd from above):

ALTER Procedure dbo.AMS_I_GetAreasV2
(
@SMSA VarChar(10) = NULL,
@S1 Char(2) = NULL,

@C1 Char(3) = NULL,

@SourceTable varchar(1) = 'P' -- P: Purchase-Money R: Refi B:
Both F: FHA V: VA G: All_Govt
, @CustomID INT = 0 -- 09/20/2004 JJA; 12/09/2004 JJA
, @Debug int = 0 -- 03/01/2004 JJA
)

As

Set NOCount ON


debugging







begin


end





translated -- 09/20/2004 JJA
BEGIN -- 09/20/2004 JJA

effect.' -- 09/20/2004 JJA
INSERT INTO #Areas -- 09/20/2004 JJA

09/20/2004 JJA
,@Debug -- 09/20/2004 JJA

-- I had to abandon these new arguments when nesting complaint
showed up

GOTO CommonExitPoint -- 09/20/2004 JJA
END
--SMSA:

BEGIN


Begin
INSERT INTO #Areas

FROM County C INNER JOIN SMSA M ON C.SMSA_CD = M.SMSA_CD

............etc. etc.

--------------------------------------------------------------------------------------
ALTER procedure dbo.AMS_I_GetAreasV2_CustomAreaTranslator
--------------------------------------------------------------------------------*
-- PURPOSE: Translate an ID for a custom area into a record set of
-- areas that the custom area defines. If any metro-area is part
-- of a custom-area definition, its component STATE and COUNTY are
-- included in this record set (otherwise SMSA is left as NULL).
-- USAGE: Called by AMS_I_GetAreasV2 to populate #Areas
-- HISTORY: 09/23/2004 JJA - implement new sproc
--------------------------------------------------------------------------------*

(
@CustomID int -- key to parent table (i.e. has name of this
definition, etc.)
,@Debug int = 0 -- set default to 1 for Query-Analyzer debugging




)
as






SET NOCOUNT ON


--- Here, I tried to do:

Create Table #AllowedCounties
(
County_CD CHAR(3),
TypeCounty VARCHAR(60),
State_CD CHAR(2)
)
INSERT INTO #AllowedCounties



--- SourceTable and Username would have been passed in from callers.
--- Upon successful creation of temporary table #AllowedCounties, it
would have been added to the
--- SELECT just below as another table to JOIN.
--- Yet the INSERT INTO / EXECUTE construct here is disallowed because
at the top #AREAS is being
--- populated and I got the complaint about nesting immediately
trying to compile this "inner" sproc.


SELECT DISTINCT
C.State_CD
, C.County_CD
, SMSA_CD =
CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then C.CBSACode + C.CBSADivision
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then C.CBSACode
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then C.SMSA_CD
END
FROM dbo.AcctCustomArea X
, dbo.GovtCountiesList C
WHERE

AND
CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then 1
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then 1
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then 1
When X.SMSACBSA IS NULL AND X.State = C.State_CD
AND X.County = C.County_CD Then 1
ELSE 0 -- where 0 = 1 rejects record
END = 1 -- where 1 = 1 allows record
ORDER BY
C.State_CD
, C.County_CD

 
 
JJA





PostPosted: Mon Jan 03 11:54:33 CST 2005 Top

SQL Server >> Help with UDF using OPENROWSET to EXECUTE sproc I am sorry I did not chop off all comments from the code posted above
(it is pretty hard to read). I will try to summarize:

In this problem, there are 2 pieces of business logic implemented by
stored procedures and called in numerous places by other stored
procedures.

AMS_I_GetAreasV2 is called to populate a temporary table #AREAS which
is used in JOINS here and is used in reporting elsewhere.

AccountFetchCounties is called to return a set of allowed counties
based on a code and username.

In short, I need to expand the functionality of AMS_I_GetAreasV2 such
that in some cases it calls AccountFetchCounties, joining the allowed
set of rows produced by that sproc with another SELECT.

As I said, when I ran into the complaint about nesting, I tried making
a UDF to EXECUTE AccountFetchCounties but that is disallowed. Then I
tried OPENROWSET with EXECUTE of AccountFetchCounties but that does not
allow parameters to be passed to the sproc. Then I posted the original
question.

To solve this in a semi-ugly way, I have replicated all the
functionality of my sproc, AccountFetchCounties, into a UDF. Then in
the SELECT inside 'AMS_I_GetAreasV2_CustomAreaTranslator', I have
added this UDF which returns a table to my JOIN which works pretty
well.

But I am uncomfortable with the idea of cloning this "business logic"
from SPROC to UDF because of keeping my sanity in the future
maintenance of this code. Thanks in advance for trying to follow my
story and help.

 
 
JJA





PostPosted: Tue Jan 04 10:34:39 CST 2005 Top

SQL Server >> Help with UDF using OPENROWSET to EXECUTE sproc Here is a better post of code (now cleaned up and fewer comments) that
caused the original nesting error:

-- this CREATE is inside a large production sproc
-- here is the first relevant fragment:

create table #areas
(
state char(2)
, county char(3)
, SMSA_CD varchar(10)
)
Execute dbo.AMS_I_GetAreasV2
@SMSA,
@S1,
@C1,
@SourceTable



--- here is the 2nd fragment (EXECUTEd from above):


ALTER Procedure dbo.AMS_I_GetAreasV2
(
@SMSA VarChar(10) = NULL,
@S1 Char(2) = NULL,
@C1 Char(3) = NULL,
@SourceTable varchar(1) = 'P'
, @CustomID INT = 0
, @Debug int = 0
)
As
Set NOCount ON





begin


end


BEGIN
INSERT INTO #Areas

@CustomID
,@Debug


-- I had to abandon these new arguments when nesting complaint
-- showed up


GOTO CommonExitPoint
END
--SMSA:

BEGIN


Begin
INSERT INTO #Areas

FROM County C INNER JOIN SMSA M ON C.SMSA_CD = M.SMSA_CD


............etc. etc.

---- here is the 3rd fragment EXECUTED from just above

ALTER procedure dbo.AMS_I_GetAreasV2_CustomAreaTranslator
(
@CustomID int
,@Debug int = 0



)
as




SET NOCOUNT ON

--- Here, I tried to do:

Create Table #AllowedCounties
(
County_CD CHAR(3),
TypeCounty VARCHAR(60),
State_CD CHAR(2)
)
INSERT INTO #AllowedCounties
EXECUTE dbo.AccountFetchCounties
NULL




-- SourceTable and Username would have been passed in from callers.
-- Upon successful creation of temporary table #AllowedCounties, it
-- would have been added to the SELECT just below as another table to
JOIN.
-- Yet the INSERT INTO / EXECUTE construct here is disallowed because
-- at the top #AREAS is being populated and I got the complaint about
-- nesting immediately trying to compile this "inner" sproc.


SELECT DISTINCT
C.State_CD
, C.County_CD
, SMSA_CD =
CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then C.CBSACode + C.CBSADivision
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then C.CBSACode
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then C.SMSA_CD
END
FROM dbo.AcctCustomArea X
, dbo.GovtCountiesList C
WHERE

AND
CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then 1
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then 1
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then 1
When X.SMSACBSA IS NULL AND X.State = C.State_CD
AND X.County = C.County_CD Then 1
ELSE 0 -- where 0 = 1 rejects record
END = 1 -- where 1 = 1 allows record
ORDER BY
C.State_CD
, C.County_CD

 
 
JJA





PostPosted: Tue Jan 18 08:47:08 CST 2005 Top

SQL Server >> Help with UDF using OPENROWSET to EXECUTE sproc Please reply if you can get a chance. I tried to describe my problem
better - maybe I gave too much detail. Bottom line is that I had to
clone business logic of a stored procedure into a user defined
function. I do not like having to do this but it works. Like I said, in
trying to extend the processing of the stored procedure, I ran into
multiple restrictions which I have tried to describe.