I have been getting this error message a lot lately.. I have no idea where
the problem is.
When it happens, I have been rebooting the cold fusion server and sql 7
server. Then it works for a while and then stops.
ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC
SQL Server Driver][SQL Server]Database 'thethin' is already open and can
only have one user at a time.
The most commonly executed querry is for a banner ad rotation I wrote,
which calls a stored procedure. I am pretty new at stored procedures... so
I may have blew this badly:)
<cfquery name="nextad" datasource="thethin" dbtype="ODBC">
{ call getad(#cat_id#) }
</cfquery>
The procedure is:
create procedure getad
@cat_id integer
as
begin transaction
declare @lastad_id int
declare @nextad_id int
declare @shown int
set @lastad_id = (select lastad_id from category where category_id =
@cat_id)
set @nextad_id = (select min(ad_link) from ad_category_link where
((category_link = @cat_id) and (ad_link > @lastad_id)))
set @shown = (select shown from ads where ad_id = @nextad_id) +1
if @nextad_id is NULL
begin
set @nextad_id = (select min(ad_link) from ad_category_link
where (category_link = @cat_id) )
end
update category set lastad_id = @nextad_id where category_id= @cat_id
update ads set shown= @shown where ad_id = @nextad_id
select * from ads where ad_id = @nextad_id
commit transaction
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
