G
Guest
Server = SQL Server 2000 SP3
Client = Access 2000 SP3 .adp
I asked this question in sqlserver.programming, but only got one reply which
was to suggest trying something I had already stated that I'd tried.
I have a sproc which if run from Query Analyser it returns two tables and
270 rows, if I run the sproc in Access (just double clicking) it returns
table_a twice and only 80 records. This is bad. QA returns the correct rows,
Access doesn't.
I've tried tweaking the set nocount on and off, as with the other set
_whatever_ on/off at the start of the sproc.
I've also had this problem on a different database, where a similar situaton
exists ie [create tmp1 and tmp2 insert data into both from linked_server then
select data back from both]
Rather than place a psuedo version, this time I've pased in the actual
thing, not sure which is more useful, but anyway hope someone could help me
maybe.
Many thanks for any suggestions.
The sproc is:
CREATE procedure sp_YE
as
set nocount on
set ansi_nulls on
set ansi_warnings on
create table #tmpYE (
gl_year char(4) not null,
gl_period char(2) not null,
gl_cost_centre varchar(6) not null,
gl_account varchar(8) not null,
gl_amount money not null,
gl_description varchar(50),
gl_analysis_1_id varchar(15),
gl_analysis_2_id varchar(15),
gl_analysis_3_id varchar(15) )
create clustered index idx_1 on #tmpYE (
gl_year,
gl_period,
gl_cost_centre,
gl_account )
with fillfactor = 100
create table #tmpEFIN (
gl_year char(4) not null,
gl_period char(2) not null,
gl_cost_centre varchar(6) not null,
gl_account varchar(8) not null,
gl_amount money not null,
gl_description varchar(50),
gl_analysis_1_id varchar(15),
gl_analysis_2_id varchar(15),
gl_analysis_3_id varchar(15) )
create clustered index idx_2 on #tmpEFIN (
gl_year,
gl_period,
gl_cost_centre,
gl_account )
with fillfactor = 100
insert into #tmpYE (
gl_year,
gl_period,
gl_cost_centre,
gl_account,
gl_amount,
gl_description,
gl_analysis_1_id,
gl_analysis_2_id,
gl_analysis_3_id )
select * from openquery(linked_server,'select
gl_year,
gl_period,
gl_cost_centre,
gl_account,
gl_amount,
gl_description,
gl_analysis_1_id,
gl_analysis_2_id,
gl_analysis_3_id
from linked_server.gl_transactions
where
gl_year = 2004 and
gl_period = 12 and
gl_analysis_1_id like ''Y/E%'' ')
insert into #tmpEFIN (
gl_year,
gl_period,
gl_cost_centre,
gl_account,
gl_amount,
gl_description,
gl_analysis_1_id,
gl_analysis_2_id,
gl_analysis_3_id )
select * from openquery(linked_server,'select
gl_year,
gl_period,
gl_cost_centre,
gl_account,
gl_amount,
gl_description,
gl_analysis_1_id,
gl_analysis_2_id,
gl_analysis_3_id
from linked_server.gl_transactions
where
gl_year = 2004 and
gl_period = 12 and
gl_analysis_1_id not like ''Y/E%'' ')
select distinct
b.*,
a.*
from #tmpEFIN a inner join #tmpYE b on
a.gl_year = b.gl_year and
a.gl_period = b.gl_period and
a.gl_cost_centre = b.gl_cost_centre and
a.gl_account = b.gl_account and
a.gl_amount = b.gl_amount
Client = Access 2000 SP3 .adp
I asked this question in sqlserver.programming, but only got one reply which
was to suggest trying something I had already stated that I'd tried.
I have a sproc which if run from Query Analyser it returns two tables and
270 rows, if I run the sproc in Access (just double clicking) it returns
table_a twice and only 80 records. This is bad. QA returns the correct rows,
Access doesn't.
I've tried tweaking the set nocount on and off, as with the other set
_whatever_ on/off at the start of the sproc.
I've also had this problem on a different database, where a similar situaton
exists ie [create tmp1 and tmp2 insert data into both from linked_server then
select data back from both]
Rather than place a psuedo version, this time I've pased in the actual
thing, not sure which is more useful, but anyway hope someone could help me
maybe.
Many thanks for any suggestions.
The sproc is:
CREATE procedure sp_YE
as
set nocount on
set ansi_nulls on
set ansi_warnings on
create table #tmpYE (
gl_year char(4) not null,
gl_period char(2) not null,
gl_cost_centre varchar(6) not null,
gl_account varchar(8) not null,
gl_amount money not null,
gl_description varchar(50),
gl_analysis_1_id varchar(15),
gl_analysis_2_id varchar(15),
gl_analysis_3_id varchar(15) )
create clustered index idx_1 on #tmpYE (
gl_year,
gl_period,
gl_cost_centre,
gl_account )
with fillfactor = 100
create table #tmpEFIN (
gl_year char(4) not null,
gl_period char(2) not null,
gl_cost_centre varchar(6) not null,
gl_account varchar(8) not null,
gl_amount money not null,
gl_description varchar(50),
gl_analysis_1_id varchar(15),
gl_analysis_2_id varchar(15),
gl_analysis_3_id varchar(15) )
create clustered index idx_2 on #tmpEFIN (
gl_year,
gl_period,
gl_cost_centre,
gl_account )
with fillfactor = 100
insert into #tmpYE (
gl_year,
gl_period,
gl_cost_centre,
gl_account,
gl_amount,
gl_description,
gl_analysis_1_id,
gl_analysis_2_id,
gl_analysis_3_id )
select * from openquery(linked_server,'select
gl_year,
gl_period,
gl_cost_centre,
gl_account,
gl_amount,
gl_description,
gl_analysis_1_id,
gl_analysis_2_id,
gl_analysis_3_id
from linked_server.gl_transactions
where
gl_year = 2004 and
gl_period = 12 and
gl_analysis_1_id like ''Y/E%'' ')
insert into #tmpEFIN (
gl_year,
gl_period,
gl_cost_centre,
gl_account,
gl_amount,
gl_description,
gl_analysis_1_id,
gl_analysis_2_id,
gl_analysis_3_id )
select * from openquery(linked_server,'select
gl_year,
gl_period,
gl_cost_centre,
gl_account,
gl_amount,
gl_description,
gl_analysis_1_id,
gl_analysis_2_id,
gl_analysis_3_id
from linked_server.gl_transactions
where
gl_year = 2004 and
gl_period = 12 and
gl_analysis_1_id not like ''Y/E%'' ')
select distinct
b.*,
a.*
from #tmpEFIN a inner join #tmpYE b on
a.gl_year = b.gl_year and
a.gl_period = b.gl_period and
a.gl_cost_centre = b.gl_cost_centre and
a.gl_account = b.gl_account and
a.gl_amount = b.gl_amount