Sybase convert to SQL; SP #temp table

[timmoser]

I have an SP that does exactly what it is supposed to do in Sybase (return a set of records). I am trying to get the same results in SQL2000. But when I run the SP from CF or ASP both are throwing an error alluding to the recordset does not exist. The SP is kind of long so I started to break it down and found it will only work (return records) if I run only the first SELECT without an INTO #temptable statement. Has anyone ever run into something like this?

If you look at the full SP it creates a temp table then manipulates the temp table and does a final SELECT. It returns records when ran directly in SQL2000 but when ran in ASP or CF it gets an error.

This is the SP with all but the first SELECT commented out:

CREATE PROCEDURE dbo.s_it_tt_getsumweek2
@uid VARCHAR(10),
@weekending smalldatetime
AS
BEGIN

SELECT week_date AS week_date,
ISNULL(SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday),0) AS total,
holiday = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity a
WHERE a.activity = 'Holiday' AND a.uid = @uid AND a.week_date = z.week_date),0),
vacation = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity b
WHERE b.activity = 'Vacation' AND b.uid = @uid AND b.week_date = z.week_date),0),
ato = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity c
WHERE c.activity = 'Authorized Time Off' AND c.uid = @uid AND c.week_date = z.week_date),0),
mato = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity d
WHERE d.activity = 'Management Time Off' AND d.uid = @uid AND d.week_date = z.week_date),0),
personal = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity e
WHERE e.activity = 'Floating Holiday' AND e.uid = @uid AND e.week_date = z.week_date),0),
illness = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity f
WHERE f.activity = 'Illness' AND f.uid = @uid AND f.week_date = z.week_date),0),
internal = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity g
WHERE g.acttype = 'DecisionOne Work' AND g.uid = @uid AND g.week_date = z.week_date),0),
externl = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity h
WHERE h.acttype = 'Customer Work' AND h.uid = @uid AND h.week_date = z.week_date),0),
admin = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity i
WHERE i.acttype = 'Administrative' AND i.uid = @uid AND i.week_date = z.week_date),0),
dif = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity j
WHERE j.activity = 'Death in Family' AND j.uid = @uid AND j.week_date = z.week_date),0),
divday = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity k
WHERE k.activity = 'Diversity Day' AND k.uid = @uid AND k.week_date = z.week_date),0),
juryduty = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity l
WHERE l.activity = 'Jury Duty' AND l.uid = @uid AND l.week_date = z.week_date),0),
lofab = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity m
WHERE m.activity = 'Leave of Absence' AND m.uid = @uid AND m.week_date = z.week_date),0),
sterm = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity o
WHERE o.activity = 'Short Term Disability' AND o.uid = @uid AND o.week_date = z.week_date),0),
unauthab = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity a
WHERE a.activity = 'Unauthorized Absence' AND a.uid = @uid AND a.week_date = z.week_date),0),
nopay = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity a
WHERE a.activity = 'No Pay' AND a.uid = @uid AND a.week_date = z.week_date),0),
milduty = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity a
WHERE a.activity = 'Military Duty' AND a.uid = @uid AND a.week_date = z.week_date),0),
other = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity b
WHERE b.acttype = 'Other' AND b.uid = @uid AND b.week_date = z.week_date),0),

lterm = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity c
WHERE c.activity = 'Long Term Disability' AND c.uid = @uid AND c.week_date = z.week_date),0),

itwork = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity c
WHERE c.acttype = 'IT Work' AND c.uid = @uid AND c.week_date = z.week_date),0),

presaleswork = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity c
WHERE c.acttype = 'Presales Work' AND c.uid = @uid AND c.week_date = z.week_date),0)
-- INTO #tempAct
FROM it_tt_activity z
WHERE z.uid=@uid
GROUP BY z.week_date

--- ****** ASP and CF see a result if I comment out the "INTO #tempAct" above and comment out the rest of the code below...


/*
UPDATE #tempAct
SET admin=(admin-(unauthab+holiday+vacation+ato+mato+illness+juryduty+milduty+nopay))

DECLARE @lastWeekInQ smalldatetime
DECLARE @addedWeek1 smalldatetime
DECLARE @firstWeekInQ smalldatetime
DECLARE @addedBackWeek1 smalldatetime

SELECT @lastWeekInQ = (SELECT MAX(week_date) FROM #tempAct)
SELECT @firstWeekInQ = (SELECT MIN(week_date) FROM #tempAct)

IF(@lastWeekInQ IS NULL)
BEGIN
-- user has no recs, poss new employee
SELECT @addedWeek1=@weekending
SELECT @addedBackWeek1=DATEADD(dd,-7,@weekending)
END
ELSE
BEGIN
SELECT @addedWeek1 = DATEADD(dd,7,@lastWeekInQ)
SELECT @addedBackWeek1 = DATEADD(dd,-7,@firstWeekInQ)
-- if this week is in the timesheet table then we need to move forward another week
DECLARE @addedWeek2 smalldatetime
DECLARE @ttweek smalldatetime
SELECT @ttweek = (
SELECT a.paydays
FROM it_tt_ts_timesheets a, it_tt_paypds b
WHERE b.week_date=@addedWeek1
AND a.uid=@uid
AND a.paydays=b.paydays
)

IF @ttweek IS NOT NULL
BEGIN
SELECT @addedWeek2 = DATEADD(dd,7,@addedWeek1)
END
END
*/

/*
IF @ttweek IS NOT NULL
BEGIN

SELECT week_date, total, holiday, vacation, ato, mato, personal, illness, internal, externl, admin, dif, divday, juryduty, lofab, sterm, lterm, other, unauthab, itwork, presaleswork, milduty, nopay
FROM #tempAct
UNION
SELECT @addedWeek1, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0
UNION
SELECT @addedBackWeek1, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0
UNION
SELECT @addedWeek2, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0
ORDER BY week_date DESC

END
ELSE
BEGIN

SELECT week_date, total, holiday, vacation, ato, mato, personal, illness, internal, externl, admin, dif, divday, juryduty, lofab, sterm, lterm, other, unauthab, itwork, presaleswork, milduty, nopay
FROM #tempAct
UNION
SELECT @addedWeek1, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0
UNION
SELECT @addedBackWeek1, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0
ORDER BY week_date DESC


END
*/


END
GO



As you can see if all of the code is allowed to run it will return a record set. And it does return one local on SQL2000.

I am aware that the temp tables in an SP are deleted when the SP is complete but since I select from the table before the SP is complete it should still be available.
Is there something special that SQL2000 does with temp tables that will not allow me to select from them even in the same procedure? Actually since it works on the SQL2000 client maybe this is a driver issue.

I'd apreceiate any thoughts on this since I am at a lost to explain it.

(my original post in CF: thread232-737602

 

[SQLBill]

That line isn't SQL Server syntax. There's no INTO tablename command. There is a INSERT INTO command which may be what you are looking for.

Do you have SQL Server installed? If so, as part of the client tools is the Books OnLine which is the HELP tool. Go to Start>Programs>Microsoft SQL Server>Books OnLine. That will give you help with the Transact-SQL that SQL Server uses.

-SQLBill

 

[philhege]

Bill,

Straight from the BOL's mouth:

SELECT
Retrieves rows from the database and allows the selection of one or many rows or columns from one or many tables. The full syntax of the SELECT statement is complex, but the main clauses can be summarized as:

SELECT select_list
[ INTO new_table ] * yes you can
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]

I do it all the time.

Happy Monday!


Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....

 

[timmoser]

Almost got me there, thought I posted to the Oracle SQL site for a second; I've been doing SELECT INTO #temp tables for years.

Well looks like I stumbled onto the fix, but I don't yet fully understand why. Found this on Google:

http://forums.aspfree.com/t21135/s.html

Seems I have to put SET NOCOUNT ON at the beginning of the SP. It works!

Thanks

 

[SQLBill]

You are both right.....I was 'thrown' off by the INTO being after the WHERE statement.

-SQLBill