Error Message and (Hopefully) some guidance

I am trying to switch an application from SYBASE ASE12.5.1 to MSSQL 2000.
The application is a Java App running through TOMCAT. I have the JDBC
drivers installed and they are working correctly, since I can log in to the
app (which required DB access). I can also navigate through the app. When
I submit a report I get the following error:
[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid ob
ject name
'#utilization'.
The query that I am submitting runs flawlessly in Query Analyzer. I am
using Velocity to generate the queries and have to utilize temp tables (that
is what #utilization is).
I suspect that my ability to create Tmp tables as the web user is
suspicious, but during the normal operation of the app I am able to create
and drop temp tables as needed.
Thanks in advance
Harry
The actual query is below:
create table #period ( id numeric(6,2))
create table #facility ( id smallint)
insert into #period values ( 2002.00 )
insert into #period values ( 2002.25 )
insert into #period values ( 2002.50 )
insert into #period values ( 2002.75 )
insert into #facility
select hospital_code
from azal..hospital hospital
inner join azal..zip zip on hospital.zip_code = zip.zip_code
where ( (city='Phoenix' and state='AZ') )
create table #utilization
( pri_grp varchar(64) ,
sec_grp varchar(64) ,
ter_grp varchar(64) ,
qua_grp varchar(64) ,
qui_grp varchar(64) ,
cases numeric(16,4) NOT NULL,
days numeric(16,4) NOT NULL,
charges numeric(24,4) NOT NULL,
daystocases numeric(20,4) NOT NULL,
chargestocases numeric(24,4) NOT NULL,
relwght numeric(20,4) NOT NULL,
filtered int)
-- _GO_
INSERT INTO #utilization
SELECT
rdrg_specialty.rdrg_specialty_name
,
'',
'',
'',
'',
count(inpatient.case_id),
sum( inpatient.los ) ,
round(sum(inpatient.charge * 1.0),4),
round(sum(hospital_rdrg_all.st_total_days * 1.0 /
hospital_rdrg_all.st_total_cases ),4),
round(sum(hospital_rdrg_all.st_total_charges * 1.0 /
hospital_rdrg_all.st_total_cases ),4),
round(sum( hospital_rdrg_all.relative_weight ),4) ,0
FROM azal..inpatient inpatient,
azal..hospital_rdrg_all hospital_rdrg_all
, #period, #facility, azal..rdrg rdrg_sl, azal..rdrg_specialty
rdrg_specialty
WHERE hospital_rdrg_all.period_code = inpatient.period_code
AND hospital_rdrg_all.rdrg_code = inpatient.rdrg_code
AND hospital_rdrg_all.hospital_code = inpatient.hospital_code
and inpatient.period_code = #period.id
and inpatient.hospital_code = #facility.id
and inpatient.rdrg_code = rdrg_sl.rdrg_code AND rdrg_sl.rdrg_specialty_code
= rdrg_specialty.rdrg_specialty_code
GROUP BY rdrg_specialty.rdrg_specialty_name
-- ORDER BY rdrg_specialty.rdrg_specialty_name
-- ========================================
============
-- Primary Group Level
select
pri_grp,
round(sum(cases),4),
round(sum(days),4),
round(sum(charges),4),
round(sum(days) / sum(cases),4),
round(sum(daystocases) / sum(cases),4),
round(sum(charges) / sum(cases),4),
round(sum(chargestocases) / sum(cases),4),
round(sum(relwght) / sum(cases),4),
round(sum(days) / sum(daystocases),4),
round(sum(charges) / sum(chargestocases),4)
from #utilization
group by pri_grp
order by
2 desc,
pri_grp
--END Primary Group Level
-- ========================================
============
-- Report Totals, all data including filtered data
select
round(sum(cases),4),
round(sum(days),4),
round(sum(charges),4),
round(sum(days) / sum(cases),4),
round(sum(daystocases) / sum(cases),4),
round(sum(charges) / sum(cases),4),
round(sum(chargestocases) / sum(cases),4),
round(sum(relwght) / sum(cases),4),
round(sum(days) / sum(daystocases),4),
round(sum(charges) / sum(chargestocases),4)
from #utilization
-- _DROP_
truncate table #period
drop table #period
truncate table #facility
drop table #facility
truncate table #utilization
drop table #utilizationPerhaps several connections are opened for the SQL statements? Use Profiler
to trace the SQL actually
submitted to SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Harry" <handersoiii@.msn.com> wrote in message news:uKRocsyFEHA.2580@.TK2MSFTNGP12.phx.gbl..
.
> I am trying to switch an application from SYBASE ASE12.5.1 to MSSQL 2000.
> The application is a Java App running through TOMCAT. I have the JDBC
> drivers installed and they are working correctly, since I can log in to th
e
> app (which required DB access). I can also navigate through the app. Whe
n
> I submit a report I get the following error:
> [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid
object name
> '#utilization'.
> The query that I am submitting runs flawlessly in Query Analyzer. I am
> using Velocity to generate the queries and have to utilize temp tables (th
at
> is what #utilization is).
> I suspect that my ability to create Tmp tables as the web user is
> suspicious, but during the normal operation of the app I am able to create
> and drop temp tables as needed.
> Thanks in advance
> Harry
> The actual query is below:
> create table #period ( id numeric(6,2))
> create table #facility ( id smallint)
> insert into #period values ( 2002.00 )
> insert into #period values ( 2002.25 )
> insert into #period values ( 2002.50 )
> insert into #period values ( 2002.75 )
> insert into #facility
> select hospital_code
> from azal..hospital hospital
> inner join azal..zip zip on hospital.zip_code = zip.zip_code
> where ( (city='Phoenix' and state='AZ') )
>
>
> create table #utilization
> ( pri_grp varchar(64) ,
> sec_grp varchar(64) ,
> ter_grp varchar(64) ,
> qua_grp varchar(64) ,
> qui_grp varchar(64) ,
> cases numeric(16,4) NOT NULL,
> days numeric(16,4) NOT NULL,
> charges numeric(24,4) NOT NULL,
> daystocases numeric(20,4) NOT NULL,
> chargestocases numeric(24,4) NOT NULL,
> relwght numeric(20,4) NOT NULL,
> filtered int)
> -- _GO_
>
> INSERT INTO #utilization
> SELECT
> rdrg_specialty.rdrg_specialty_name
> ,
> '',
> '',
> '',
> '',
> count(inpatient.case_id),
> sum( inpatient.los ) ,
> round(sum(inpatient.charge * 1.0),4),
> round(sum(hospital_rdrg_all.st_total_days * 1.0 /
> hospital_rdrg_all.st_total_cases ),4),
> round(sum(hospital_rdrg_all.st_total_charges * 1.0 /
> hospital_rdrg_all.st_total_cases ),4),
> round(sum( hospital_rdrg_all.relative_weight ),4) ,0
> FROM azal..inpatient inpatient,
> azal..hospital_rdrg_all hospital_rdrg_all
> , #period, #facility, azal..rdrg rdrg_sl, azal..rdrg_specialty
> rdrg_specialty
> WHERE hospital_rdrg_all.period_code = inpatient.period_code
> AND hospital_rdrg_all.rdrg_code = inpatient.rdrg_code
> AND hospital_rdrg_all.hospital_code = inpatient.hospital_code
> and inpatient.period_code = #period.id
> and inpatient.hospital_code = #facility.id
> and inpatient.rdrg_code = rdrg_sl.rdrg_code AND rdrg_sl.rdrg_specialty_cod
e
> = rdrg_specialty.rdrg_specialty_code
> GROUP BY rdrg_specialty.rdrg_specialty_name
> -- ORDER BY rdrg_specialty.rdrg_specialty_name
>
> --
>
> -- ========================================
============
> -- Primary Group Level
> select
> pri_grp,
> round(sum(cases),4),
> round(sum(days),4),
> round(sum(charges),4),
> round(sum(days) / sum(cases),4),
> round(sum(daystocases) / sum(cases),4),
> round(sum(charges) / sum(cases),4),
> round(sum(chargestocases) / sum(cases),4),
> round(sum(relwght) / sum(cases),4),
> round(sum(days) / sum(daystocases),4),
> round(sum(charges) / sum(chargestocases),4)
> from #utilization
> group by pri_grp
> order by
> 2 desc,
> pri_grp
> --END Primary Group Level
>
>
>
> -- ========================================
============
> -- Report Totals, all data including filtered data
> select
> round(sum(cases),4),
> round(sum(days),4),
> round(sum(charges),4),
> round(sum(days) / sum(cases),4),
> round(sum(daystocases) / sum(cases),4),
> round(sum(charges) / sum(cases),4),
> round(sum(chargestocases) / sum(cases),4),
> round(sum(relwght) / sum(cases),4),
> round(sum(days) / sum(daystocases),4),
> round(sum(charges) / sum(chargestocases),4)
> from #utilization
>
> -- _DROP_
>
> truncate table #period
> drop table #period
> truncate table #facility
> drop table #facility
> truncate table #utilization
> drop table #utilization
>|||Thanks, it does appear that the query when being submitted is not staying
contained within 1 session, and spans 3 sessions, I am using connection
pooling with TOMCAT and will go look into that.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23SDBVyyFEHA.3252@.TK2MSFTNGP11.phx.gbl...
> Perhaps several connections are opened for the SQL statements? Use
Profiler to trace the SQL actually
> submitted to SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Harry" <handersoiii@.msn.com> wrote in message
news:uKRocsyFEHA.2580@.TK2MSFTNGP12.phx.gbl...
2000.
the
When
name
(that
create
rdrg_sl.rdrg_specialty_code
>