RE: OLE exception

[Steve Howard]

What you are running is not a query. It is an ISQL script. It will work fine
in Query analyzer, but DBI can only prepare and execute one statement at a
time (Read Perldoc DBI). If you want to use a script like that, create a
stored procedure then you can execute it and get the results into your Perl
script.

Another option is do the manipulation in Perl, and execute each statement
separately. Forget the SQL variable declaration - handle all the variables
in Perl if you do it like that, and use placeholders to execute each.

Steve H.


-----Original Message-----
From: Konstantin Berman [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 25, 2001 3:23 AM
To: '[EMAIL PROTECTED]'
Subject: OLE exception


Hi.

I try to run the following query with DBD:ADO driver:
-----------------------------------------------------
set nocount on

declare @beforedate datetime,
        @date datetime,
        @casinoid int,
        @playertype INT,
        @gamingserverID int,
        @date2order datetime,
        @Month integer,
        @Year  integer,
        @DAY INT,
        @currdate datetime,
        @strDate varchar(30)

SET @currdate = GetDate()
SET @Month = DATEPART(MM,@currdate)
SET @Year = Year(@currdate)
SET @DAY= DAY(@currdate)
SET @strDate = convert(varchar(2), @Month)+'/' + convert(varchar(2),
@DAY)+'/' + convert(varchar(4), @YeaR) +'  12:00PM'
SET @date=DATEADD(DD,-1, CONVERT(DATETIME,@strDate))


set     @beforedate ='1999-01-01' /* do not change */
set     @date2order = dateadd(d,-44,@date)/* -43*/
set     @casinoid =207
set     @playertype= 0
set     @gamingserverID=32
print 'The @date2order is:'
print @date2order
print @date

select  count(t.PRTIME)[Purchases],
        userid
into #ponly

from tb_purchaserequest t
where t.gamingserverid=@gamingserverID
and t.PRSTATUS=1
and t.PRTIME>=@beforedate
and t.PRTIME<=@date
and t.casinoid= @casinoid

group by t.userid

select  p.userid,
        max(BIDAYMARKER)[LastPlayed],
         p.usdateopened,
        sum(biincome) [Income],
        sum(bipayouts) [Payouts],
        sum(biincome-bipayouts) [Profit],
        p.usaccountno

into #betplayer

from betinfo b,player p

where p.userid=b.userid
and p.gamingserverid=@gamingserverID
and b.gamingserverid=p.gamingserverid
and b.CASINOID=p.CASINOID
and b.CASINOID=@casinoid
and b.PLAYERTYPEID=@playertype
and b.PLAYERTYPEID=p.PLAYERTYPEID
and BIDAYMARKER >=@beforedate
and BIDAYMARKER <=@date

group by p.usaccountno,p.userid,p.usdateopened


select usaccountno,
usdateopened,isnull([Purchases],0)+isnull(tt.counts,0)[Counts],Income,Payout
s, Profit, [LastPlayed]--,tt.account ,tt.aleventid,[Purchases],tt.counts,
from #betplayer left join #ponly
on #betplayer.userid=#ponly.userid
left join (select  player.usaccountno[account],tb_adminlog.casinoid,
        count( tb_adminlog.ALTIME)[counts],
        tb_adminlog.userid
from tb_adminlog inner join player on tb_adminlog.userid=player.userid and
player.gamingserverid=tb_adminlog.gamingserverid  and
player.casinoid=tb_adminlog.casinoid
where (tb_adminlog.ALEVENTID=10036 or tb_adminlog.aleventid = 10002 or
tb_adminlog.aleventid = 5000) and
 tb_adminlog.casinoid=@casinoid

GROUP BY player.usaccountno,
        tb_adminlog.casinoid,
        tb_adminlog.userid ) as tt on #betplayer.userid = tt.userid
where #betplayer.[LastPlayed]>=@date2order
order by [Counts]desc, Profit desc
drop table #betplayer
drop table #ponly
----------------------------------------------------------------------------
-----------------------
I get the OLE exception while query analyzer says the query is ok. Can
anyone please explain me why?
Thanks in advance.