Prev: query perfomance issue with Select statment in the Select list
Next: view issues
From: Matt Williamson on 1 Jul 2008 10:06
SQL2000 standard SP4. I've given a user exec permission on a stored
procedure that I've created but she gets an error when running it. The
output through QA when I run the stored proc with exec is:

(8062 row(s) affected)
(8732 row(s) affected)
(1 row(s) affected)
Server: Msg 208, Level 16, State 1, Procedure Compare_APX_UDA, Line 38
Invalid object name 'fullCompare1'.
Server: Msg 208, Level 16, State 1, Procedure Compare_APX_UDA, Line 38
Invalid object name 'fullCompare2'.

fullCompare1 and fullCompare2 are tables that I create in the stored
procedure. That's the 8062 rows and 8732 rows in the output. The odd thing
is I can run all of the code blocks in the stored procedure from QA using
windows auth as her without any issues. The procedure itself is very simple,
just a couple of delete if exists statements and then about 84 select
statements.
I'm executing it using ADO in Excel. When I trap the error with a msgbox for
err.source, err.description and err.number I get:

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259
Desc: [Microsoft][ODBC SQL Server Driver]Error in row

Since the error occurs at the first "Real" select query that Selects from
the tables I just created, here is the proc up to that point with line
numbers for reference:

[1] Create Procedure Compare_APX_UDA
[2] AS
[3] BEGIN
[4] -- Drop tables if they already exist
[5] if object_id('fullcompare1', 'u') is not null drop table fullcompare1
[6] if object_id('fullcompare2', 'u') is not null drop table fullcompare2
[7] --Create Comparison Tables
[8] select code, active_inactive, proxy_voting, cl_lst_revwd, nso.obj_agmt,
taxable_nontaxable,
[9] rr,
custodian,family_group,dist_freq,dist_day,dist_amt,employee,pmt_type,billing_short_name,
[10]
how_est,why_closed,source,destination,comments_in,comments_out,projected_value,closing_value,
[11]
fee_schedule,discount_exp,alt_debit_acct,invoiced,alt_bill_name,alt_bill_address1,alt_bill_address2,alt_bill_address3,
[12]
alt_bill_address4,notes,custody,contact,exempt_min,no_payout,COD_flag,Debit_HCB_COD,custodian_acct,
[13] exempt_min_comm,adj_payout,Sales_office
[14] into fullcompare1
[15] from server1.db1.dbo.view1 uda
[16] join server2.db2.dbo.table1 nso on nso.portcode = uda.code
[17] order by code
[18] select p.portfoliocode,
[19] e.status, e.proxy_voting, b.closedate, e.obj_agmt, p.taxstatus, e.rr,
e.custodian,
[20]
e.family_group,e.dist_freq,e.dist_day,e.dist_amt,e.employee,e.pmt_type,e.billing_short_name,
[21]
e.how_est,e.why_closed,e.Acct_source,e.Acct_destination,e.comments_in,e.comments_out,
[22]
e.projected_value,e.closing_value,e.fee_schedule,e.discount_exp,e.alt_debit_acct,e.invoiced,
[23]
e.alt_bill_name,e.alt_bill_address1,e.alt_bill_address2,e.alt_bill_address3,e.alt_bill_address4,
[24]
e.notes,e.custody,e.contact,e.exempt_min,e.no_payout,e.COD,e.Debit_HCB_COD,
[25] e.custodian_acct,e.exempt_min_comm,e.adj_payout,e.Salesoffice
[26] into fullcompare2
[27] from Portfolio p
[28] join PortfolioBase b on p.portfolioid = b.portfoliobaseid
[29] join PortfolioBaseExt e on e.portfoliobaseid = p.portfolioid
[30] order by p.portfoliocode
[31] -- Active/Inactive
[32] select 'Active / Inactive Status';
[33] select 0 as sort, 'code' as a, 'portfoliocode' as b,
'UDA_active_inactive' as c, 'APX_status' as d, 'Custodian' as e
[34] into #fc1
[35] union
[36] SELECT 1, code, portfoliocode, active_inactive as UDA_active_inactive,
status as APX_status, custodian
[37] FROM (SELECT C1.code, C2.portfoliocode, C1.active_inactive, C2.status,
c2.custodian
[38] FROM fullCompare1 AS C1
[39] full JOIN fullCompare2 AS C2
[40] ON C1.code = C2.portfoliocode) AS T
[41] WHERE active_inactive <> status
[42] and T.custodian not in ('ETH','AF')
[43] --or (T.active_inactive is not null and t.Status is null)
[44] --or (T.active_inactive is null and t.Status is not null)
[45] --or (T.active_inactive is null and t.Status is null)
[46] order by 1;
[47] select a,b,c,d,e from #fc1
[48] drop table #fc1

TIA

Matt


From: Aaron Bertrand [SQL Server MVP] on 1 Jul 2008 10:24
Try using a proper schema prefix on permanent objects.

EXEC sp_helptext Compare_APX_UDA;
GO
DROP PROCEDURE Compare_APX_UDA;
GO

CREATE PROCEDURE dbo.Compare_APX_UDA
AS
BEGIN
IF OBJECT_ID('dbo.fullCompare1', 'U') IS NOT NULL
DROP TABLE dbo.fullCompare1;

IF OBJECT_ID('dbo.fullCompare2', 'U') IS NOT NULL
DROP TABLE dbo.fullCompare2;

SELECT ... INTO dbo.fullCompare1 ...

SELECT ... INTO dbo.fullCompare2 ...

etc. etc.
END
GO



On 7/1/08 10:06 AM, in article uP871O42IHA.1428(a)TK2MSFTNGP06.phx.gbl, "Matt
Williamson" <ih8spam(a)spamsux.org> wrote:

> SQL2000 standard SP4. I've given a user exec permission on a stored
> procedure that I've created but she gets an error when running it. The
> output through QA when I run the stored proc with exec is:
>
> (8062 row(s) affected)
> (8732 row(s) affected)
> (1 row(s) affected)
> Server: Msg 208, Level 16, State 1, Procedure Compare_APX_UDA, Line 38
> Invalid object name 'fullCompare1'.
> Server: Msg 208, Level 16, State 1, Procedure Compare_APX_UDA, Line 38
> Invalid object name 'fullCompare2'.
>
> fullCompare1 and fullCompare2 are tables that I create in the stored
> procedure. That's the 8062 rows and 8732 rows in the output. The odd thing
> is I can run all of the code blocks in the stored procedure from QA using
> windows auth as her without any issues. The procedure itself is very simple,
> just a couple of delete if exists statements and then about 84 select
> statements.
> I'm executing it using ADO in Excel. When I trap the error with a msgbox for
> err.source, err.description and err.number I get:
>
> Source: Microsoft OLE DB Provider for ODBC Drivers
> Number: -2147467259
> Desc: [Microsoft][ODBC SQL Server Driver]Error in row
>
> Since the error occurs at the first "Real" select query that Selects from
> the tables I just created, here is the proc up to that point with line
> numbers for reference:
>
> [1] Create Procedure Compare_APX_UDA
> [2] AS
> [3] BEGIN
> [4] -- Drop tables if they already exist
> [5] if object_id('fullcompare1', 'u') is not null drop table fullcompare1
> [6] if object_id('fullcompare2', 'u') is not null drop table fullcompare2
> [7] --Create Comparison Tables
> [8] select code, active_inactive, proxy_voting, cl_lst_revwd, nso.obj_agmt,
> taxable_nontaxable,
> [9] rr,
> custodian,family_group,dist_freq,dist_day,dist_amt,employee,pmt_type,billing_s
> hort_name,
> [10]
> how_est,why_closed,source,destination,comments_in,comments_out,projected_value
> ,closing_value,
> [11]
> fee_schedule,discount_exp,alt_debit_acct,invoiced,alt_bill_name,alt_bill_addre
> ss1,alt_bill_address2,alt_bill_address3,
> [12]
> alt_bill_address4,notes,custody,contact,exempt_min,no_payout,COD_flag,Debit_HC
> B_COD,custodian_acct,
> [13] exempt_min_comm,adj_payout,Sales_office
> [14] into fullcompare1
> [15] from server1.db1.dbo.view1 uda
> [16] join server2.db2.dbo.table1 nso on nso.portcode = uda.code
> [17] order by code
> [18] select p.portfoliocode,
> [19] e.status, e.proxy_voting, b.closedate, e.obj_agmt, p.taxstatus, e.rr,
> e.custodian,
> [20]
> e.family_group,e.dist_freq,e.dist_day,e.dist_amt,e.employee,e.pmt_type,e.billi
> ng_short_name,
> [21]
> e.how_est,e.why_closed,e.Acct_source,e.Acct_destination,e.comments_in,e.commen
> ts_out,
> [22]
> e.projected_value,e.closing_value,e.fee_schedule,e.discount_exp,e.alt_debit_ac
> ct,e.invoiced,
> [23]
> e.alt_bill_name,e.alt_bill_address1,e.alt_bill_address2,e.alt_bill_address3,e.
> alt_bill_address4,
> [24]
> e.notes,e.custody,e.contact,e.exempt_min,e.no_payout,e.COD,e.Debit_HCB_COD,
> [25] e.custodian_acct,e.exempt_min_comm,e.adj_payout,e.Salesoffice
> [26] into fullcompare2
> [27] from Portfolio p
> [28] join PortfolioBase b on p.portfolioid = b.portfoliobaseid
> [29] join PortfolioBaseExt e on e.portfoliobaseid = p.portfolioid
> [30] order by p.portfoliocode
> [31] -- Active/Inactive
> [32] select 'Active / Inactive Status';
> [33] select 0 as sort, 'code' as a, 'portfoliocode' as b,
> 'UDA_active_inactive' as c, 'APX_status' as d, 'Custodian' as e
> [34] into #fc1
> [35] union
> [36] SELECT 1, code, portfoliocode, active_inactive as UDA_active_inactive,
> status as APX_status, custodian
> [37] FROM (SELECT C1.code, C2.portfoliocode, C1.active_inactive, C2.status,
> c2.custodian
> [38] FROM fullCompare1 AS C1
> [39] full JOIN fullCompare2 AS C2
> [40] ON C1.code = C2.portfoliocode) AS T
> [41] WHERE active_inactive <> status
> [42] and T.custodian not in ('ETH','AF')
> [43] --or (T.active_inactive is not null and t.Status is null)
> [44] --or (T.active_inactive is null and t.Status is not null)
> [45] --or (T.active_inactive is null and t.Status is null)
> [46] order by 1;
> [47] select a,b,c,d,e from #fc1
> [48] drop table #fc1
>
> TIA
>
> Matt
>
>

 | 
Pages: 1
Prev: query perfomance issue with Select statment in the Select list
Next: view issues