January 22, 2008 at 5:12 pm
Hi - hope someone can help.
I've inherited the maintenance and support of a SQL Server 2000 database with an Access 2000 based front-end, running on an SBS 2003 server. All worked fine up until yesterday when trying to login using the usual username/password gives the above 3151 error. The line in the VB which generates this is:
Set rstRoles = sfdb.OpenRecordset("Roles", dbOpenDynaset, dbSeeChanges)
which is opening a simple table of roles to check which level the given user is at. Two things:
1 I've noticed the transaction log is huge (12 GB) and had just run out of space to grow, but having freed up plenty more disk space, I still get the error
2 I can open the database in admin mode and view the tables as normal. Just can't run the front end which the users need.
I noticed one post which recommended running the linked tables utlility, which I've done but no difference.
Any thoughts or suggestions welcome.:)
January 22, 2008 at 11:22 pm
Is it possible to post the SQL Server error message.
Please go to the LOG folder of the SQL Server and read the error log without any extension.
"More Green More Oxygen !! Plant a tree today"
January 23, 2008 at 12:46 am
Hi - thanks for the speedy response - I really appreciate it.
I've extracted the error message plus the contents of the last 2 error logs into the attached Word document. I can't see anything wrong, but hopefully you might!!
Thanks
Pete
January 23, 2008 at 1:53 am
Yes you r right SQl Server is clean. I googled this info.. hope this might help.
http://www.webservertalk.com/message1895352.html
" special this section:to use ADO recordsets instead of DAO recordsets,because DAO seems to be buggy."
"More Green More Oxygen !! Plant a tree today"
January 23, 2008 at 9:10 am
I believe that you need to test your ODBC connection. It may only work for your system admin account.
Also, regarding the log,
SQL Server is terminating due to 'stop' request from service control
there are some discussions in this forum recently. Here is the link,
January 23, 2008 at 3:29 pm
Thanks very much for your replies.
Minaz - I'd seen that too, but my Access help tells me that if I'm using a .mdb database (as I am) then a DAO recordset is returned, with an ADO recordset only returned if it's an Access project (.adp). So I must already be using DAO surely?
SQL ORACLE - the stop was a manual stop by me to try to kick SQL into action once I'd cleared some space on the C: drive for the transaction log to expand into. There is now 14.3GB free which should be ample.
I get the same SQL error whether I log in as a desktop user connected to the server or as the admin user at the server console.
So am still baffled why it's suddenly stopped with no error log hints.
Any other ideas anyone??
Pete
January 24, 2008 at 6:46 am
pete.doyle,
You have checked the SQL Errorlog, which can give spotty information at best. You can also check the Windows Event Logs (Application, Security and System) for the same time frames that the SQL Errorlog gives for your connection errors. You will need to check both the Server and the Client(s) to see if any other information is being gathered and reported by Windows.
In case you need it:
(Windows XP and/or Windows Server 2003) START >> Control Panel >> Administrative Tools >> Event Viewer ... choose the Type of Log, dig around the logs for the times when the error was caused.
Hopefully you will be able to get more meaningful information.
Keep us posted on your findings.
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
January 24, 2008 at 7:13 am
Thanks Damon - really good input.
I've browsed most of the logs looking for anything new that's been reported between last Friday when it was working and Monday when it wasn't, but can't see anything new!!
The only biggie which coincided with all this was the transaction log not being able to expand due to the disk being nearly full. I'd have expected though that as there's now plenty of space that it should be working OK - but still no joy.
Other ideas??
January 24, 2008 at 8:49 am
I am thinking that there may be a problem with the Access 2000 front end. You seem to have exhausted issues with the SQL Server backend.
These are areas I would investigate if this situation fell into my lap ...
(An assumption is made that your VB code is VB6.)
I did a Google search with the following string:
Visual Basic Run-time error 3151 -mysql -oracle -sybase
... This posting *may* be of help ...
http://www.xtremevbtalk.com/archive/index.php/t-159130.html
Check your version of ADO. In the VBA code page of the Access project, select TOOLS --> REFERENCES. See what versions of ADO you have installed by seeing what is checked at the top and scrolling down to Microsoft ActiveX Data Objects 2.x - and if you have less than 2.6 go to the Microsoft web site and download the latest ADO. Keep in mind that Microsoft calls the ADO file MDAC.EXE. Download the latest (2.8?) and it won't hurt to download 2.6, 2.7 too. They can all be installed side-by-side. Just when you save the MDAC.EXE files, rename them with the version (ex. MDAC2-6.EXE).
--
A couple of ideas concerning the Access 2000 Front End.
1) Relink the table connections to SQL Server.
2) MDB Maintenance
Backup the MDB file, just in case. Then Repair then Compact the MDB file. I don't have any Access 2000 systems, if memory serves Compact and Repair are sperate actions, they are a 2-for-1 with Access 2003.
HTH
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
January 25, 2008 at 12:08 am
Thanks Damon
Did the relinking and compact/repair but no change.
Not sure about the ADO bit you mention - is that relevant if I'm using DAO instead? 🙂
Did a lot of searching of other forums/blogs last night and can't see anything similar. Also switched on tracing but that didn't tell me much either. Also rechecked all Windows Server logs from last Friday through to Monday as this feels like a system wide rather than user based error - but apart from the disk space issue, nothing I can see.
My customer is getting very worried...
Pete
January 25, 2008 at 6:37 am
Pete,
There are a lot of things that you have been able to rule out as problems. I have a couple of suggestions below, looking at the issue from a couple of different angles.
VB6:
(Pardon my limited VB knowledge.)
ADO is an acronym for the "ActiveX Data Access" library.
If you are using DAO (Data Access Object) as a connection method in the VB program all I can recommend is to see if you can find anything in VB6 newsgroups/forums/MSDN. If you have access to the source code, you or someone can put it in debug mode and step through the connection process. If you know any VB developers, now would be the time to consider calling in a favor or three.
Connection:
One thing you can try is enabling Tracing with the ODBC Administrator Panel on the database server, and attempt to make a connection from one of the clients, then turn off tracing immediately. You will get a HUGE log file to troll through, but it will give you more information to work with as to what is going on behind the scenes.
As a last resort, you may also want to consider opening a case with Microsoft as well, I realize that it may cost money, but they will be able to help you troubleshoot as well, and have access to tools and knowledge bases that us mere mortals don't.
Keep me posted
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
January 25, 2008 at 6:39 am
Pete,
Just to rule it out, have you run a DBCC CHECKDB against the SQL Server database being used by the application?
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
January 25, 2008 at 7:22 am
Thanks again - and will look closer at suggestion 1. I noticed suggestion 2 on other blogs, but wasn't clear how to run that???
Pete
January 25, 2008 at 7:39 am
How to make an ODBC TRACE
(Quick and Dirty Draft)
CLIENT:
Get a client machine ready to make the connection ...
DATABASE SERVER:
START >> Control Panel >> Administrative Tools >> Data Sources (ODBC) >> Tracing Tab
Log File Path: There is a default path for the log file generated, change as you see fit.
Before you execute the connection on the Client, click the "Start Tracing Now" Button.
... then back to the Client, attempt to make the connection, let it error, and click through any/all errors and such. Once the connection process is completed, back to the Server, you will need to Click the button (I think it gets named to) "Stop Tracing Now". ... if you don't stop the tracing the Trace file will grow until it fills up the hard drive.
I would make a copy of the trace file to work with, chopping out the 99.9% of information you won't need, with your favorite text editor (I use Textpad as mine). There is a dizzying amount of information captured with this collection process, but you do get to see how connections work "under the hood".
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
January 25, 2008 at 7:46 am
Sorry Damon - was typing in a hurry.
I meant the DBCC CHECKDB bit.
I have done some tracing and couldn't see anything odd - but will try again as soon as I can get online to the client.
Thanks 🙂
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply