I am not DB expert but here are some things to check.
First is there a limit to the number of users for that table?
If not then:
The issue here seems to be with commiting the transaction.
If someone is viewing the banner and using the stored procedure then they
would have it locked until the transaction is commited. Therefore if
someone else accessed the script before it was commited you might have a
locking issue.
Mark Johnson
-----------------------
Senior Cold Fusion Developer
Cardinal Communications
-----Original Message-----
From: Al Musella, DPM [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 13, 2000 6:41 PM
To: CF-Talk
Subject: ODBC Error 37000
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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
