Procedure doesn't work in ADP, does in SQL Mgt Studio

M

mattdube

Hello,

I finished hacking a moderately complicated procedure to select data
joined on 2 tables - the text of which follows. For some reason, this
query will not run properly on my .adp. It works fine in SQL Mgt
Studio, and all other queries and procedures continue to work fine in
my .adp, its just this one query that doesnt work in Access. When I
run the procedure (either by my form I created or clicking on it in the
Queries container) I get "The stored procedure executed succesfully but
did not return records." To retierate: everything about this query
works except when it is called through my .adp. Maybe there's a
permission error or something - or maybe it has something to do with
nested Selects? Please offer advice.

Thanks!
Matt

--------------------------------------------------------------
Use db_ProdMeasurables;
go
create procedure sp_ShiftLaborJoined
@inDate smalldatetime,
@Shift Int
as

select distinct tbl_ExtrusionLaborData.EmployeeID,
sum(tbl_ExtrusionLaborData.Hours) as DirectHours
into #DirectHours
from tbl_ExtrusionLaborData
where
tbl_ExtrusionLaborData.Date = @inDate
and tbl_ExtrusionLaborData.Shift = @Shift
group by tbl_ExtrusionLaborData.EmployeeID

select distinct tbl_IndirectTime.EmployeeID,
sum(tbl_IndirectTime.Hours) as IndirectHours
into #InDirectHours
from tbl_IndirectTime
where
tbl_IndirectTime.Date = @inDate
and tbl_IndirectTime.Shift = @Shift
group by tbl_IndirectTime.EmployeeID

Select d.EmployeeID, DirectHours, IndirectHours,
sum(DirectHours) + sum(IndirectHours) as TotalHours
from #DirectHours d
join #InDirectHours i on i.EmployeeID = d.EmployeeID
group by d.EmployeeID, DirectHours, IndirectHours
 
M

mcnews

When I
run the procedure (either by my form I created or clicking on it in the
Queries container) I get "The stored procedure executed succesfully but
did not return records." To retierate: everything about this query
works except when it is called through my .adp. Maybe there's a
permission error or something - or maybe it has something to do with
nested Selects? Please offer advice.
Click to expand...

i have a couple of queries that give me the same message, but they work
just fine.
are you checking the results?
 
M

mattdube

Nevermind Usenet, I was able to fix my own problem. The seperated
select statements and the # identifier must have been the problem.
Here is how I fixed it:

----------------------------------------------


Select d.EmployeeID, d.DirectHours, i.IndirectHours,
sum(d.DirectHours) + sum(i.IndirectHours) as TotalHours
from
(select distinct tbl_ExtrusionLaborData.EmployeeID,
sum(tbl_ExtrusionLaborData.Hours) as DirectHours
from tbl_ExtrusionLaborData
where
tbl_ExtrusionLaborData.Date = '9/15/2006'
and tbl_ExtrusionLaborData.Shift = '1'
group by tbl_ExtrusionLaborData.EmployeeID) d

join
(select distinct tbl_IndirectTime.EmployeeID,
sum(tbl_IndirectTime.Hours) as IndirectHours
from tbl_IndirectTime
where
tbl_IndirectTime.Date = '9/15/2006'
and tbl_IndirectTime.Shift = '1'
group by tbl_IndirectTime.EmployeeID) i

on i.EmployeeID = d.EmployeeID
group by d.EmployeeID, DirectHours, IndirectHours
 
D

dbahooker

well I believe that he's expecting to SEE the results

right?

you're expecting that when you execute the results; you SEE the results
from the 3rd subquery right?

I personally don't use temp tables because they seem buggy as hell..
and with .NET you can't even friggin keep a conneciton open; so a temp
table is pretty worthless.

if you know anything about eccommerce-- do you know how they sometimes
make a database table with SESSIONID as a FK?

you can do something simliar; but I guess the bottom line-- why use a
temp table at all? ?

can't you do this as a simple derived query?
 
D

dbahooker

ps bud; you shouldn't ever call a sproc sp_ for one reason-- when you
name it that; SQL Server has to iterate through the sprocs in the
master database; I believe that SQL Server automagically looks in
master when it's got a sproc named sp_mySproc

(in other words; it looks in master then in your database-- there is
some performance overhead in other words)

-Aaron
 
S

Sylvain Lafontaine

When using multtiple select statement and temporary tables, you must use the
SET NOCOUNT ON option at the beginning of your stored procedure.
 
M

mattdube

mcnews said:
i have a couple of queries that give me the same message, but they work
just fine.
are you checking the results?
Click to expand...

There were no results in Access, just a blank form/query
window/whathaveyou. I presume Access can not handle the seperated
selects. It runs the first one and, since there is no real data to
return, it does not return anything.

Making one select as below solved my problem. Thx for the input.
 
M

mattdube

Thx for the insite, all of you. I am a fairly noob SQL developer and
learning on the job as I go. I will rename my sprocs.

Question about temp tables. Is i.whatever a temp table or does the #
identifiter create a temp table?
 
A

aaron.kempf

using the # identifier creates a temp table with the scope of the
current connection.

using ## identifier makes a globally accessible temp table

-Aaron
 
V

Vadim Rapp

m> I finished hacking a moderately complicated procedure to select data
m> joined on 2 tables - the text of which follows. For some reason, this
m> query will not run properly on my .adp. It works fine in SQL Mgt
m> Studio, and all other queries and procedures continue to work fine in
m> my .adp, its just this one query that doesnt work in Access. When I
m> run the procedure (either by my form I created or clicking on it in the
m> Queries container) I get "The stored procedure executed succesfully but
m> did not return records." To retierate: everything about this query
m> works except when it is called through my .adp. Maybe there's a
m> permission error or something - or maybe it has something to do with
m> nested Selects? Please offer advice.

Access expects one recordset returned by the query. You returned 3, and
Access did not know what to do.

Vadim Rapp
 
A

aaron.kempf

he DIDNT return 3 recordsets

he selected 2 recordsets into temp tables and then tried to pull them
out in the 3rd select statement.

note the 'INTO' keyword for the first 2 statements?

-Aaron
 
Top