Hi all,
I am developing a system for a mates workplace... they have an internal third party system based on an oracle backend which is basically out of bounds for me to do any dev on... also being third party, if i was to start playing twith the db they may dissown them!!!
so as they had a sql server kicking its heals we went with using that for the web site of users / client setup.
The problem i have is to deliver one report list i need to remove from list if the corresponding report id is viewed...
part one easy, when report opened create record in sql reportlog table with id and user and any other piece of info that may be relevant later on...
my problem lies with pulling it back into the listings, or not as the case may be...
i did try doing the old
select id from reportlog where customer = 'loggedincustomer'
while not eof then
bldRemove = bldRemove & 'id',
movenext
wend
to give me a string of all id's then on my listing q use this as a not in
select * from report.... where id not in (bldRemove)
which worked, not the most optimised but worked... until it hits 1000... then i get an oracle error saying statement cannot have more than 1000 entries
so i tried adding the oracle db as a linked server in sql server and creating a view to not bring through when in select id from logreport where id = b.id kind of thing... sql just timesout on me.. and had nothing but linked server issues when it returns more than about 100 records on simple queries, not complex ones like the building of this one (i left that bit out... data comes form about 8 tables)
can anyone suggest the best way to do this other than putting my table in oracle.. which as i said is dodgy ground
thankyou
daveJam
even my shrink says its all your f#@/ing fault
I am developing a system for a mates workplace... they have an internal third party system based on an oracle backend which is basically out of bounds for me to do any dev on... also being third party, if i was to start playing twith the db they may dissown them!!!
so as they had a sql server kicking its heals we went with using that for the web site of users / client setup.
The problem i have is to deliver one report list i need to remove from list if the corresponding report id is viewed...
part one easy, when report opened create record in sql reportlog table with id and user and any other piece of info that may be relevant later on...
my problem lies with pulling it back into the listings, or not as the case may be...
i did try doing the old
select id from reportlog where customer = 'loggedincustomer'
while not eof then
bldRemove = bldRemove & 'id',
movenext
wend
to give me a string of all id's then on my listing q use this as a not in
select * from report.... where id not in (bldRemove)
which worked, not the most optimised but worked... until it hits 1000... then i get an oracle error saying statement cannot have more than 1000 entries
so i tried adding the oracle db as a linked server in sql server and creating a view to not bring through when in select id from logreport where id = b.id kind of thing... sql just timesout on me.. and had nothing but linked server issues when it returns more than about 100 records on simple queries, not complex ones like the building of this one (i left that bit out... data comes form about 8 tables)
can anyone suggest the best way to do this other than putting my table in oracle.. which as i said is dodgy ground
thankyou
daveJam
even my shrink says its all your f#@/ing fault