reservation popularity

The problem is that the SS_reservations table does not exist and this command will not create that table for you.

if you change the SQL to

SET ROWCOUNT 50
SELECT RL.TARGET_ID "WORK_ID", COUNT(RL.RESERVATION_ID) "FIGURES"
into #SS_reservations
FROM RESERVED_LINK RL, RESERVATION R
WHERE RL.TYPE = 1
AND RL.RESERVATION_ID = R.RESERVATION_ID
AND R.CREATE_DATE >= DATEADD(DAY, -90, GETDATE())
GROUP BY RL.TARGET_ID
ORDER BY COUNT(RL.RESERVATION_ID) DESC

It will create a temporary table called #SS_reservations (The hash indicates a temporary table.)

By temporary I mean that within an isql session that table is available to be used so

select * from #SS_reservations

will work until you exit from isql and it won't exist for anyone else of for you if you start another isql session.

A temporary table is held in temp_db database so you need to be careful that you don't fill it or the logs up.

The SQL above is unlikely to do that but care needs to be taken.

What you need to do is create the SS_reservations table in prod_talis first.

e.g.
create table SS_reservations (
WORK_ID ID NOT NULL,
FIGURES smallint NULL)
go
grant all on SS_reservations to public
go

The smallint is upto 32,768 so should be big enough. Use INT (2^31) if not.

The trouble is that I now think with more consideration that with the SQL you have you may have to change the options on prod_talis to allow bulk data load but this would cause problems with transaction logging so would have to be done carefully e.g. the options changed, the script run, the options changed back and then a full dump done to allow transaction logging to start.

There are talis scripts that require these changes but this may be more than you want to do.

A different syntax will still log the transactions (the SS_reservations table still needs to be created) so allow the insert to work.

insert into SS_reservations (WORK_ID,FIGURES)
SELECT RL.TARGET_ID "WORK_ID", COUNT(RL.RESERVATION_ID) "FIGURES"
FROM RESERVED_LINK RL, RESERVATION R
WHERE RL.TYPE = 1
AND RL.RESERVATION_ID = R.RESERVATION_ID
AND R.CREATE_DATE >= DATEADD(DAY, -90, GETDATE())
GROUP BY RL.TARGET_ID
ORDER BY COUNT(RL.RESERVATION_ID) DESC
go

Note: you would need to remove any data from the SS_reservations table first.

As I said before all this type of changes to the db need to be done with great care as they are not covered by Support and done at your own risk.

You don't want to fill your transaction logs or tempdb and affect anything the whole system.

Thanks

Brian Crampton
Developer, Talis