I think you got it!
I was in such a rush to fix it, I skimmed right by the correct
article. It is a "known bug" in SQL server 7 SP2. That is why it just
showed up - I recently installed SP2.
The maintanace plan wizard created a maintanace plan that puts the
database into single user mode, runs the test, corrects any errors it
finds, then removes the single user mode. If someone hits the database in
that exact moment between those last 2 steps, the table is in still single
user mode - so the command to remove single user mode can't get through.
That is also why it happens in the middle of the night:) That is when the
plan ran. For some reason, it couldn't even email me that there was a problem.
All of the tables checked out ok with a manual DBCC checkdatabase!
(And I just ordered sql 2000 because of this problem:)
thanks a million - I owe you one!
Al
>-----BEGIN PGP SIGNED MESSAGE-----
>Hash: SHA1
>
>hey Al,
>
>You might try going to the MS support site and running a query for
>single user mode...
>
>http://search.support.microsoft.com/kb/psssearch.asp?SPR=sql&T=B&KT=AL
>L&T1=7d&LQ=single+user+mode&S=F&A=T&DU=C&FR=0&D=sqlserver%2Bor%2Bodbc&
>LPR=%22SQL+Server%22&LNG=ENG&VR=http%3A%2F%2Fsupport.microsoft.com%2Fs
>upport%3Bhttp%3A%2F%2Fsupport.microsoft.com%2Fservicedesks%2Fwebcasts%
>3Bhttp%3A%2F%2Fsupport.microsoft.com%2Fhighlights&CAT=Support&VRL=ENG&
>SA=GN
>
>which I did for you... are you doing maintenance on your DB's?
>....Ton's of things might cause this.
>
>Aaron Johnson, MCSE, MCP+I
>MINDSEYE, Inc.
><phn>617.350.0339
><fax>617.350.8884
><icq>66172567
>[EMAIL PROTECTED]
>
>___________________________________________
>"Never forget that only dead fish swim with
>the stream." -- Malcolm Muggeridge
>___________________________________________
>
>
>- -----Original Message-----
>From: Al Musella, DPM [mailto:[EMAIL PROTECTED]]
>Sent: Wednesday, December 13, 2000 7:20 PM
>To: CF-Talk
>Subject: RE: ODBC Error 37000
>
>
>
>Chris,
> Wow...
> This is going to sound strange....
> When I first got that message, I went into the enterprise
>manager, and strangely about 5 out of the 20 or so databases were
>in
>single user mode. I never selected single user mode in my entire
>life:)
>
> I unchecked the "single user" option, and everything was fine for
>a
>while. And I forgot about it. Today, when I first checked my email,
>I got
>4500 messages from my server saying there is a problem (When I trap a
>database error in CF, I do a cfmail to me to tell me about it)
>
> I just went back in, and 2 of the databases were in single user
>mode. One was the database for my busiest site, thethin.net.
>The other was used infrequently, so I guess it didn't trigger any
>errors.
>
>Nobody other than me has access to that server. I know I did not
>select
>single user mode.
>
>So the question is: How did it go into single user mode?
> Is there a bug in SQL Server 7.0SP2?
> Could it have been a hacker playing around with a cold fusion
>exploit in
>some way?
>
>
>thanks for the reply.. I will watch it closely.
>Al
>
>
>
> >Al,
> >
> >Go into SQLServer Enterprise Manager. Click on the entry for the
> >database, select Database Properties, go to the Options Tab and
> >deselect the Single User option.
> >
> >Chris
> >------------------------------------------------------------------
> >Chris Lomvardias
> >[EMAIL PROTECTED]
> >Syscom, Inc.
> >400 E. Pratt Street, Suite 300
> >Baltimore, MD 21202
> >(410)539-3737 x1722 voice
> >(410)539-7302 fax
> >[EMAIL PROTECTED] (pager via email)
> >http://www.syscom.com/
> >------------------------------------------------------------------
> >
> >-----Original Message-----
> >From: Al Musella, DPM [mailto:[EMAIL PROTECTED]]
> >Sent: Wednesday, December 13, 2000 1: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/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
