Help with update forms: dbo

Showing posts with label dbo. Show all posts

Friday, March 23, 2012

HELP! Reset dbo login id password, cannot get company website up

Our company website runs off of Microsoft SQL Server. Someone tried changing the 'administrative' password but the way that they did it locks us out of the database.

Under Enterprise Manager there is a Security group, underneath here is a login. The person reset the password here on the login id that is the DBO for our website's database. Underneath the database in Enterprise Manager the dbo uses this login id. Where else does the password need to get reset in order for that login id to access the database? We cannot set it back to the previous password because it is unknown.

When we go to our website we get the following error:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect

Please try the following:
Enable Robust Exception Information to provide greater detail about the source of errors. In the Administrator, click Debugging & Logging > Debugging Settings, and select the Robust Exception Information option.
Check the ColdFusion documentation to verify that you are using the correct syntax.
Search the Knowledge Base to find a solution to your problem.

Browser Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)Also- when trying to login to SQL Query Analyzer using the login id that was changed under security it doesn't work with the new password. This is where I'm assuming the problem is (different password for login id vs. dbo).

SQL Server authentication- login id and password method.|||You have a bigger problem than I'd care to tackle with only support via a web site. You really need professional help on problems like this, it will be far cheaper in the long run than trying to work it out this way once you figure in your own time into the equation.

When you change the password using SQL Enterprise Manager, it will change the password. If you change it there, then can't log in using Query Analyzer, then you almost certainly mis-typed the password in one place or the other.

The error being reported doesn't have anything to do with the password. That error message is telling you that the "client" machine (probably your web server) can't "see" your SQL Server machine. My first guess would be due to a configuration issue with Windows Firewall, but that is only a guess.

You have more than one problem. The problems are complex, and can interfere with one another in "interesting" ways. You really need professional help with this!

-PatP|||We got it figured out. It was on the cold fusion side where the password needed to be changed.

Friday, February 24, 2012

Help with WHERE Clause in Stored Procedure

Hi,

I have an sp with the following WHERE clause

@.myqarep varchar(50)

SELECT tblCase.qarep FROM dbo.tblCase

WHERE dbo.tblCase.qarep = CASE @.myqarep WHEN '<All>' THEN
dbo.tblCase.qarep ELSE @.myqarep

@.myqarep is returned from a combo box (ms access)...the user either
picks a qarep from the combo box or they leave the default which is
'<All>'

they problem i'm having is that if the record's value for
dbo.tblCase.qarep is null...the record does not show up in the
results...but i need it to

any help is appreciated.

thanks
Paul... WHERE qarep = @.myqarep
OR @.myqarep = '<All>'

--
David Portas
SQL Server MVP
--|||thanks for the quick response...i'll give it a try!

David Portas wrote:
> ... WHERE qarep = @.myqarep
> OR @.myqarep = '<All>'
> --
> David Portas
> SQL Server MVP
> --

Sunday, February 19, 2012

Help with user defined function

I have the following function:
create function dbo.AttributesList (@.Customerno varchar(10),
@.sold_to_sales_grp varchar(3), @.attr varchar(2))
returns varchar(15)
as
begin
declare @.sOut varchar(15)
set @.sOut = ''
if @.attr = '1'
begin
-- attribute table 1
select @.sOut = @.sOut + ', ' + x.distr_channel
from product1_channels x
where x.cust_no = @.Customerno and x.sold_to_sales_grp =
@.sold_to_sales_grp
end
if @.attr = '2'
begin
-- attribute table 2
-- attribute table 2
select @.sOut = @.sOut + ', ' + y.distr_channel
from product2_channels y
where y.cust_no = @.Customerno and y.sold_to_sales_grp =
@.sold_to_sales_grp
end
if @.attr = '3'
begin
-- attribute table 3
-- attribute table 3
select @.sOut = @.sOut + ', ' + z.distr_channel
from product3_channels z
where z.cust_no = @.Customerno and z.sold_to_sales_grp =
@.sold_to_sales_grp
end
-- previous
if @.attr = '4'
begin
-- attribute table 1
select @.sOut = @.sOut + ', ' + x.distr_channel
from product1_channels_prev x
where x.cust_no = @.Customerno and x.sold_to_sales_grp =
@.sold_to_sales_grp
end
if @.attr = '5'
begin
-- attribute table 2
-- attribute table 2
select @.sOut = @.sOut + ', ' + y.distr_channel
from product2_channels_prev y
where y.cust_no = @.Customerno and y.sold_to_sales_grp =
@.sold_to_sales_grp
end
if @.attr = '6'
begin
-- attribute table 3
-- attribute table 3
select @.sOut = @.sOut + ', ' + z.distr_channel
from product3_channels_prev z
where z.cust_no = @.Customerno and z.sold_to_sales_grp =
@.sold_to_sales_grp
end
if len(@.sOut) > 2
set @.sOut = substring(@.sOut, 3, len(@.sOut) - 2)
return @.sOut
end
which you call with a customer number, a sales group, and an attribute.
The tables it queries are built in a job that gets run overnight.
The results of the query are supposed to string together a distribution
channel if there are more than 1 returned.
product1 could return CH
product2 could return CH, DL
product3 could return nothing
The function as it is works properly. I get the anticipated results
when I run a query that calls this user function.
However, I want to write an online app that will allow the user to
select a channel from a dropdown box, and the query will then return any
data with that distribution channel anywhere in the current or
previous year's product areas.
The problem is - I can't make it work all the time.
If I put in the where clause:
'DL' in (dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1')) and so
on for each of the 6 possibilities, it'll return ones with a
Distribution channel = DL but only ones which START with a DL. If DL is
the second one in the list, it doesn't return anything.
I have also tried this in the HAVING clause, to see if I can get it to
come out there but can't.
How can I take the distribution channel (in this case DL) and return
records where DL is in any of the 6 product groups?
Any ideas/suggestions appreciated. I'm stumped.
BCBlasting Cap (goober@.christian.net) writes:
> -- attribute table 1
> select @.sOut = @.sOut + ', ' + x.distr_channel
> from product1_channels x
> where x.cust_no = @.Customerno and
>...
> The function as it is works properly. I get the anticipated results
> when I run a query that calls this user function.
> However, I want to write an online app that will allow the user to
> select a channel from a dropdown box, and the query will then return any
> data with that distribution channel anywhere in the current or
> previous year's product areas.
> The problem is - I can't make it work all the time.
This is because the correct behaviour of this query is undefined. See
http://support.microsoft.com/default.aspx?scid=287515.
If you are on SQL 2000, you will need to run a cursor to get this
right.
If you are on SQL 2005, there is built-in syntax for this thanks to
the FOR XML construct, here demonstrated by a sample query that I
keep around:
select CustomerID,
substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarchar(30), OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList)
go
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||<<This is because the correct behaviour of this query is undefined. See
http://support.microsoft.com/default.aspx?scid=287515.
If you are on SQL 2000, you will need to run a cursor to get this
right. >>
I am using SQL 2000.
The place I'm running the report is just a straight select, with the
functions being called for 6 columns being returned.
select
a.cust_no,
a.cust_name,
a.sold_to_sales_grp,
a.ship_to_sales_grp,
a.sold_to_sales_rep_cd,
a.ship_to_sales_rep_cd,
a.csr,
a.credit_mgr,
a.sales_region,
'Prod1_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1'),
'Prod2_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2'),
'Prod3_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3'),
'Prod1_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4'),
'Prod2_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5'),
'Prod3_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')
from sales_customer_list as a
where cust_no in ('R1411600','R6713155')
group by
a.cust_no,
a.cust_name,
a.sold_to_sales_grp,
a.ship_to_sales_grp,
a.sold_to_sales_rep_cd,
a.ship_to_sales_rep_cd,
a.csr,
a.credit_mgr,
a.sales_region
having
-- if they have bought things in any of the last 3 years
(len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')) > 0 )
order by
a.ship_to_sales_grp,
a.ship_to_sales_rep_cd,
cust_name
How would you work a cursor into that select?
DECLARE @.AuthorID char(11)
DECLARE c1 CURSOR FOR
SELECT au_id
FROM authors
OPEN c1
FETCH NEXT FROM c1
INTO @.AuthorID
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.AuthorID
FETCH NEXT FROM c1
INTO @.AuthorID
END
CLOSE c1
DEALLOCATE c1
Also - is there a more efficient way to do this?
Thanks,
BC

> Blasting Cap (goober@.christian.net) writes:
when I run a query that calls this user function.
select a channel from a dropdown box, and the query will then return any
data with that distribution channel anywhere in the current or
previous year's product areas.
> This is because the correct behaviour of this query is undefined. See
> http://support.microsoft.com/default.aspx?scid=287515.
> If you are on SQL 2000, you will need to run a cursor to get this
> right.
> If you are on SQL 2005, there is built-in syntax for this thanks to
> the FOR XML construct, here demonstrated by a sample query that I
keep around:
> select CustomerID,
> substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
> -- strip the last ',' from the list
> from
> Customers c cross apply
> (select convert(nvarchar(30), OrderID) + ',' as [text()]
> from Orders o
> where o.CustomerID = c.CustomerID
> order by o.OrderID
> for xml path('')) as Dummy(OrdIdList)
> go
>
>|||On Thu, 25 May 2006 16:33:30 -0400, Blasting Cap wrote:

>I have the following function:
(snip)
Erland is correct - the UDF depends on undocumented behaviour. Even
though it works today, it might break tomorrow.
But Erland apparently missed the question you asked near the end of your
post:

>If I put in the where clause:
>'DL' in (dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1')) and so
>on for each of the 6 possibilities, it'll return ones with a
>Distribution channel = DL but only ones which START with a DL. If DL is
>the second one in the list, it doesn't return anything.
And yet, I'll let Erland answer that, since he has authored an excellent
page about this problem, and lots of possible solutions:
http://www.sommarskog.se/arrays-in-sql.html
However, in your case there might be a better way. Instead of first
using undocumented techniques to get a comma-seperated list and then
using a second technique to split those to tables, why not rewrite the
AttributesList to a table-valued function?
Hugo Kornelis, SQL Server MVP|||Blasting Cap (goober@.christian.net) writes:
> The place I'm running the report is just a straight select, with the
> functions being called for 6 columns being returned.
>...
> How would you work a cursor into that select?
In your UDF.

> Also - is there a more efficient way to do this?
Yes. Two options:
1) Upgrade to SQL 2005.
2) Do it client-side.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:
> On Thu, 25 May 2006 16:33:30 -0400, Blasting Cap wrote:
> And yet, I'll let Erland answer that, since he has authored an excellent
> page about this problem, and lots of possible solutions:
> http://www.sommarskog.se/arrays-in-sql.html
> However, in your case there might be a better way. Instead of first
> using undocumented techniques to get a comma-seperated list and then
> using a second technique to split those to tables, why not rewrite the
> AttributesList to a table-valued function?
For the WHERE clause why not simply use an EXISTS against the underlying
table:
WHERE EXISTS (SELECT *
FROM product1_channels p
WHERE p.cust_no = a.cust_no
AND p.distr_channel = 'DL')
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Blasting Cap (goober@.christian.net) writes:
> select
> a.cust_no,
> a.cust_name,
> a.sold_to_sales_grp,
> a.ship_to_sales_grp,
> a.sold_to_sales_rep_cd,
> a.ship_to_sales_rep_cd,
> a.csr,
> a.credit_mgr,
> a.sales_region,
> 'Prod1_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1'),
> 'Prod2_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2'),
> 'Prod3_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3'),
> 'Prod1_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4'),
> 'Prod2_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5'),
> 'Prod3_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')
> from sales_customer_list as a
> where cust_no in ('R1411600','R6713155')
> group by
> a.cust_no,
> a.cust_name,
> a.sold_to_sales_grp,
> a.ship_to_sales_grp,
> a.sold_to_sales_rep_cd,
> a.ship_to_sales_rep_cd,
> a.csr,
> a.credit_mgr,
> a.sales_region
> having
> -- if they have bought things in any of the last 3 years
> (len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')) > 0 )
> order by
> a.ship_to_sales_grp,
> a.ship_to_sales_rep_cd,
> cust_name
It seems unnecessary to call the UDFs a second time in the HAVING clause.
I'm a little uncertain of the effect of the GROUP BY in this SELECT as
there is no aggregate functions, but assuming that it works the way you
want I retain them. However you could use a derived table:
SELECT cust_no, cust_name, ...
FROM (select
a.cust_no,
a.cust_name,
a.sold_to_sales_grp,
a.ship_to_sales_grp,
a.sold_to_sales_rep_cd,
a.ship_to_sales_rep_cd,
a.csr,
a.credit_mgr,
a.sales_region,
'Prod1_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1'),
'Prod2_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2'),
'Prod3_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3'),
'Prod1_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4'),
'Prod2_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5'),
'Prod3_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')
from sales_customer_list as a
where cust_no in ('R1411600','R6713155')
group by
a.cust_no,
a.cust_name,
a.sold_to_sales_grp,
a.ship_to_sales_grp,
a.sold_to_sales_rep_cd,
a.ship_to_sales_rep_cd,
a.csr,
a.credit_mgr,
a.sales_region) AS x
WHERE len(Prod1_curr) > 0 OR
len(Prod2_curr) > 0 OR
len(Prod3_curr) > 0 OR
len(Prod1_prev) > 0 OR
len(Prod2_prev) > 0 OR
len(Prod3_prev) > 0
order by
a.ship_to_sales_grp,
a.ship_to_sales_rep_cd,
cust_name
A derived table is a logical temp table within the query, but not materialis
ed,
and SQL Server recast computation order for the best query plan.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Help with UDF using OPENROWSET to EXECUTE sproc

Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||-- 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
(
statechar(2),
countychar(3)
, SMSA_CDvarchar(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
, @.CustomID = @.CustomID-- 09/20/2004 JJA: Add support for Custom
Area Definitions as a source for populating #Areas table
, @.UserName = @.UserName-- 12/29/2004 JJA: Make Custom Area
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,
@.SourceTablevarchar(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
declare @.err int, @.rows int-- 03/01/2004 JJA - capture essentials for
debugging
declare @.sepid varchar(80)
declare @.time varchar(30)
select @.sepid = ' - AMS_I_GetAreasV2: '
DECLARE @.SMSAV VARCHAR(10)
SELECT @.SMSAV = RTRIM(@.SMSA)
If @.SMSAV IS NULL
begin
SELECT @.SMSAV = '';-- this will enable PRINT below to occur and
DATALENGTH(@.SMSAV) to be 0 so CASE logic below works
end
select @.time = convert(varchar(30), getdate(), 109)
print @.time + @.sepid + ' entered. SMSA = ' + @.SMSAV + '; SourceTable =
' + @.SourceTable + '; CustomID = ' + Convert(Varchar(9),@.CustomID)
if @.CustomID > 0-- In this mode, a custom area definition is
translated -- 09/20/2004 JJA
BEGIN-- 09/20/2004 JJA
print @.time + @.sepid + ' Custom-Area Definition Mode is in
effect.'-- 09/20/2004 JJA
INSERT INTO #Areas-- 09/20/2004 JJA
EXECUTE @.ERR = dbo.AMS_I_GetAreasV2_CustomAreaTranslator @.CustomID--
09/20/2004 JJA
,@.Debug-- 09/20/2004 JJA
-- , @.SourceTable, @.UserName
-- I had to abandon these new arguments when nesting complaint
showed up
SELECT @.ROWS = @.@.ROWCOUNT-- 09/20/2004 JJA
GOTO CommonExitPoint-- 09/20/2004 JJA
END
--SMSA:
IF datalength(@.SMSAV) = 4 --SMSA
BEGIN
print @.time + @.sepid + ' SMSA Mode in effect.'
If @.SourceTable = 'P'
Begin
INSERT INTO #Areas
SELECT C.State_cd, C.County_Cd, @.SMSAV AS SMSA_CD
FROM County C INNER JOIN SMSA M ON C.SMSA_CD = M.SMSA_CD
WHERE C.SMSA_CD = @.SMSAV AND C.PDATA = 'YES'
..........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
----*
-- EXECUTE dbo.AMS_I_GetAreasV2_CustomAreaTranslator 1,@.Debug=1
(
@.CustomIDint-- key to parent table (i.e. has name of this
definition, etc.)
,@.Debugint= 0-- set default to 1 for Query-Analyzer debugging
-- ,@.SourceTable char(1)
-- ,@.UserName varchar(30)
)
as
DECLARE @.err int
DECLARE @.rows int
DECLARE @.pid varchar(100)
SELECT @.pid = 'AMS_I_GetAreasV2_CustomAreaTranslator: '
SET NOCOUNT ON
if @.Debug = 1 PRINT @.pid + ' entered for CustomID = ' +
CONVERT(VARCHAR(9),@.CustomID)
-- Here, I tried to do:
Create Table #AllowedCounties
(
County_CDCHAR(3),
TypeCountyVARCHAR(60),
State_CDCHAR(2)
)
INSERT INTO #AllowedCounties
EXECUTE dbo.AccountFetchCounties (NULL, @.CategoryCode = @.SourceTable,
@.UserName = @.UserName)
-- 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
X.CustomID = @.CustomID
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
|||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.
|||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
, @.CustomID = @.CustomID
, @.UserName = @.UserName
-- 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
declare @.err int, @.rows int
DECLARE @.SMSAV VARCHAR(10)
SELECT @.SMSAV = RTRIM(@.SMSA)
If @.SMSAV IS NULL
begin
SELECT @.SMSAV = '';
end
if @.CustomID > 0
BEGIN
INSERT INTO #Areas
EXECUTE @.ERR = dbo.AMS_I_GetAreasV2_CustomAreaTranslator
@.CustomID
,@.Debug
-- , @.SourceTable
--, @.UserName
-- I had to abandon these new arguments when nesting complaint
-- showed up
SELECT @.ROWS = @.@.ROWCOUNT
GOTO CommonExitPoint
END
--SMSA:
IF datalength(@.SMSAV) = 4
BEGIN
If @.SourceTable = 'P'
Begin
INSERT INTO #Areas
SELECT C.State_cd, C.County_Cd, @.SMSAV AS SMSA_CD
FROM County C INNER JOIN SMSA M ON C.SMSA_CD = M.SMSA_CD
WHERE C.SMSA_CD = @.SMSAV AND C.PDATA = 'YES'
..........etc. etc.
-- here is the 3rd fragment EXECUTED from just above
ALTER procedure dbo.AMS_I_GetAreasV2_CustomAreaTranslator
(
@.CustomID int
,@.Debug int = 0
-- ,@.SourceTable char(1)
-- ,@.UserName varchar(30)
)
as
DECLARE @.err int
DECLARE @.rows int
SET NOCOUNT ON
-- Here, I tried to do:
Create Table #AllowedCounties
(
County_CDCHAR(3),
TypeCountyVARCHAR(60),
State_CD CHAR(2)
)
INSERT INTO #AllowedCounties
EXECUTE dbo.AccountFetchCounties
NULL
, @.CategoryCode = @.SourceTable
, @.UserName = @.UserName
-- 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
X.CustomID = @.CustomID
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
|||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.

Help with UDF using OPENROWSET to EXECUTE sproc

Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||-- 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
, @.CustomID = @.CustomID -- 09/20/2004 JJA: Add support for Custom
Area Definitions as a source for populating #Areas table
, @.UserName = @.UserName -- 12/29/2004 JJA: Make Custom Area
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
declare @.err int, @.rows int -- 03/01/2004 JJA - capture essentials for
debugging
declare @.sepid varchar(80)
declare @.time varchar(30)
select @.sepid = ' - AMS_I_GetAreasV2: '
DECLARE @.SMSAV VARCHAR(10)
SELECT @.SMSAV = RTRIM(@.SMSA)
If @.SMSAV IS NULL
begin
SELECT @.SMSAV = ''; -- this will enable PRINT below to occur and
DATALENGTH(@.SMSAV) to be 0 so CASE logic below works
end
select @.time = convert(varchar(30), getdate(), 109)
print @.time + @.sepid + ' entered. SMSA = ' + @.SMSAV + '; SourceTable =
' + @.SourceTable + '; CustomID = ' + Convert(Varchar(9),@.CustomID)
if @.CustomID > 0 -- In this mode, a custom area definition is
translated -- 09/20/2004 JJA
BEGIN -- 09/20/2004 JJA
print @.time + @.sepid + ' Custom-Area Definition Mode is in
effect.' -- 09/20/2004 JJA
INSERT INTO #Areas -- 09/20/2004 JJA
EXECUTE @.ERR = dbo.AMS_I_GetAreasV2_CustomAreaTranslator @.CustomID --
09/20/2004 JJA
,@.Debug -- 09/20/2004 JJA
-- , @.SourceTable, @.UserName
-- I had to abandon these new arguments when nesting complaint
showed up
SELECT @.ROWS = @.@.ROWCOUNT -- 09/20/2004 JJA
GOTO CommonExitPoint -- 09/20/2004 JJA
END
--SMSA:
IF datalength(@.SMSAV) = 4 --SMSA
BEGIN
print @.time + @.sepid + ' SMSA Mode in effect.'
If @.SourceTable = 'P'
Begin
INSERT INTO #Areas
SELECT C.State_cd, C.County_Cd, @.SMSAV AS SMSA_CD
FROM County C INNER JOIN SMSA M ON C.SMSA_CD = M.SMSA_CD
WHERE C.SMSA_CD = @.SMSAV AND C.PDATA = 'YES'
.........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
----
--*
-- EXECUTE dbo.AMS_I_GetAreasV2_CustomAreaTranslator 1,@.Debug=1
(
@.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
-- ,@.SourceTable char(1)
-- ,@.UserName varchar(30)
)
as
DECLARE @.err int
DECLARE @.rows int
DECLARE @.pid varchar(100)
SELECT @.pid = 'AMS_I_GetAreasV2_CustomAreaTranslator: '
SET NOCOUNT ON
if @.Debug = 1 PRINT @.pid + ' entered for CustomID = ' +
CONVERT(VARCHAR(9),@.CustomID)
-- 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, @.CategoryCode = @.SourceTable,
@.UserName = @.UserName)
-- 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
X.CustomID = @.CustomID
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|||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.|||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
, @.CustomID = @.CustomID
, @.UserName = @.UserName
-- 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
declare @.err int, @.rows int
DECLARE @.SMSAV VARCHAR(10)
SELECT @.SMSAV = RTRIM(@.SMSA)
If @.SMSAV IS NULL
begin
SELECT @.SMSAV = '';
end
if @.CustomID > 0
BEGIN
INSERT INTO #Areas
EXECUTE @.ERR = dbo.AMS_I_GetAreasV2_CustomAreaTranslator
@.CustomID
,@.Debug
-- , @.SourceTable
-- , @.UserName
-- I had to abandon these new arguments when nesting complaint
-- showed up
SELECT @.ROWS = @.@.ROWCOUNT
GOTO CommonExitPoint
END
--SMSA:
IF datalength(@.SMSAV) = 4
BEGIN
If @.SourceTable = 'P'
Begin
INSERT INTO #Areas
SELECT C.State_cd, C.County_Cd, @.SMSAV AS SMSA_CD
FROM County C INNER JOIN SMSA M ON C.SMSA_CD = M.SMSA_CD
WHERE C.SMSA_CD = @.SMSAV AND C.PDATA = 'YES'
.........etc. etc.
-- here is the 3rd fragment EXECUTED from just above
ALTER procedure dbo.AMS_I_GetAreasV2_CustomAreaTranslator
(
@.CustomID int
,@.Debug int = 0
-- ,@.SourceTable char(1)
-- ,@.UserName varchar(30)
)
as
DECLARE @.err int
DECLARE @.rows int
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
, @.CategoryCode = @.SourceTable
, @.UserName = @.UserName
-- 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
X.CustomID = @.CustomID
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|||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.

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',
'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.]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.]
>|||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.|||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
--
"JJA" <johna@.cbmiweb.com> wrote in message
news:1104433457.065710.65650@.z14g2000cwz.googlegroups.com...
> 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,
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
Adam Machanic wrote:
>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...
>
>|||"Steve Kass" <skass@.drew.edu> wrote in message
news:uU38dyu7EHA.2196@.TK2MSFTNGP14.phx.gbl...
> 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
--|||-- 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
, @.CustomID = @.CustomID -- 09/20/2004 JJA: Add support for Custom
Area Definitions as a source for populating #Areas table
, @.UserName = @.UserName -- 12/29/2004 JJA: Make Custom Area
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
declare @.err int, @.rows int -- 03/01/2004 JJA - capture essentials for
debugging
declare @.sepid varchar(80)
declare @.time varchar(30)
select @.sepid = ' - AMS_I_GetAreasV2: '
DECLARE @.SMSAV VARCHAR(10)
SELECT @.SMSAV = RTRIM(@.SMSA)
If @.SMSAV IS NULL
begin
SELECT @.SMSAV = ''; -- this will enable PRINT below to occur and
DATALENGTH(@.SMSAV) to be 0 so CASE logic below works
end
select @.time = convert(varchar(30), getdate(), 109)
print @.time + @.sepid + ' entered. SMSA = ' + @.SMSAV + '; SourceTable =' + @.SourceTable + '; CustomID = ' + Convert(Varchar(9),@.CustomID)
if @.CustomID > 0 -- In this mode, a custom area definition is
translated -- 09/20/2004 JJA
BEGIN -- 09/20/2004 JJA
print @.time + @.sepid + ' Custom-Area Definition Mode is in
effect.' -- 09/20/2004 JJA
INSERT INTO #Areas -- 09/20/2004 JJA
EXECUTE @.ERR = dbo.AMS_I_GetAreasV2_CustomAreaTranslator @.CustomID --
09/20/2004 JJA
,@.Debug -- 09/20/2004 JJA
-- , @.SourceTable, @.UserName
-- I had to abandon these new arguments when nesting complaint
showed up
SELECT @.ROWS = @.@.ROWCOUNT -- 09/20/2004 JJA
GOTO CommonExitPoint -- 09/20/2004 JJA
END
--SMSA:
IF datalength(@.SMSAV) = 4 --SMSA
BEGIN
print @.time + @.sepid + ' SMSA Mode in effect.'
If @.SourceTable = 'P'
Begin
INSERT INTO #Areas
SELECT C.State_cd, C.County_Cd, @.SMSAV AS SMSA_CD
FROM County C INNER JOIN SMSA M ON C.SMSA_CD = M.SMSA_CD
WHERE C.SMSA_CD = @.SMSAV AND C.PDATA = 'YES'
.........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
----*
-- EXECUTE dbo.AMS_I_GetAreasV2_CustomAreaTranslator 1,@.Debug=1
(
@.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
-- ,@.SourceTable char(1)
-- ,@.UserName varchar(30)
)
as
DECLARE @.err int
DECLARE @.rows int
DECLARE @.pid varchar(100)
SELECT @.pid = 'AMS_I_GetAreasV2_CustomAreaTranslator: '
SET NOCOUNT ON
if @.Debug = 1 PRINT @.pid + ' entered for CustomID = ' +
CONVERT(VARCHAR(9),@.CustomID)
-- 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, @.CategoryCode = @.SourceTable,
@.UserName = @.UserName)
-- 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
X.CustomID = @.CustomID
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|||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.|||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
, @.CustomID = @.CustomID
, @.UserName = @.UserName
-- 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
declare @.err int, @.rows int
DECLARE @.SMSAV VARCHAR(10)
SELECT @.SMSAV = RTRIM(@.SMSA)
If @.SMSAV IS NULL
begin
SELECT @.SMSAV = '';
end
if @.CustomID > 0
BEGIN
INSERT INTO #Areas
EXECUTE @.ERR = dbo.AMS_I_GetAreasV2_CustomAreaTranslator
@.CustomID
,@.Debug
-- , @.SourceTable
-- , @.UserName
-- I had to abandon these new arguments when nesting complaint
-- showed up
SELECT @.ROWS = @.@.ROWCOUNT
GOTO CommonExitPoint
END
--SMSA:
IF datalength(@.SMSAV) = 4
BEGIN
If @.SourceTable = 'P'
Begin
INSERT INTO #Areas
SELECT C.State_cd, C.County_Cd, @.SMSAV AS SMSA_CD
FROM County C INNER JOIN SMSA M ON C.SMSA_CD = M.SMSA_CD
WHERE C.SMSA_CD = @.SMSAV AND C.PDATA = 'YES'
.........etc. etc.
-- here is the 3rd fragment EXECUTED from just above
ALTER procedure dbo.AMS_I_GetAreasV2_CustomAreaTranslator
(
@.CustomID int
,@.Debug int = 0
-- ,@.SourceTable char(1)
-- ,@.UserName varchar(30)
)
as
DECLARE @.err int
DECLARE @.rows int
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
, @.CategoryCode = @.SourceTable
, @.UserName = @.UserName
-- 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
X.CustomID = @.CustomID
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|||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.

Friday, March 23, 2012

HELP! Reset dbo login id password, cannot get company website up

Friday, February 24, 2012

Help with WHERE Clause in Stored Procedure

Sunday, February 19, 2012

Help with user defined function

Help with UDF using OPENROWSET to EXECUTE sproc

Help with UDF using OPENROWSET to EXECUTE sproc

Help with UDF using OPENROWSET to EXECUTE sproc

Help with update forms

Blog Archive

About Me