|
Database is used by another user.
Hit List keeps telling me that my Database is in use by
another use, or that it "can't open my database" when I'm
using the Database Manager with a SQL Server or Oracle database.
General Possibilities for this error message:
If you run scheduled reports as an NT service, Hit List
could be running in the backgound and executing an event.
Check to make sure this isn't the case (look in Control
Panel/Services to see if the Hit List Service is running,
and if so, Stop it and test again when it stops.
If you are not running scheduled reports, you may have
canceled an in-progress database update. You should have
seen in the Status bar "Cancel Acknowleged" and a warning
to wait for Hit List to shut down all database conections
(v4 products only). In this situation, Hit List will automatically
lock the database for 7 minutes to ensure the database
does not get corrupted. If after 7 minutes, the database
is still locked, your system may have corrupted the database.
You might simply "kill" the process with the Task Manager
and then try to re-run a report like Executive Summary
that will run quickly and tell you if the database is
still intact. If you have a small database, you might
simply want to re-import your log files.
If you have previously terminated Hit List via the task
manager, or by restarting the computer, then it is possible
that Hit List has temporarily locked the database to prevent
data corruption. Do a Find in Windows for *.mwd (the filename
extension of your Hit List database if using a Hit List
Access database. See if there is a small .ldb file in
the same directory and if so, delete it. Now try again
to run the report as discussed above.
If you have NOT loaded ANY logs or Live data into your
SQL Server or Oracle database, you can't open it with
the Database Manager as there are no tables, indicies,
etc. for Hit List to "grab", thus you need to update the
database with at least ONE log or some Live data (if using
Hit List Live) before you can open the Database Manager.
Finally, the database may have gone over 1 GB in size
(max size of an Access97 database) and you may have to
simply rebuild it from your log files. Again, check this
by doing a Find on *.mwd and see how big your Hitlist.mwd
is - if around 1 GB in size, this may have happened.
Oracle or SQL Server users:
SQL Server Users:
1) First possibility: your ODBC connection
to the database isn't working. What you have to verify
is:
a) You have already set up a database in SQL Server,
meaning you've pre-allocated the space, set up the transaction
log and installed or set up SQL Server per the instructions
in Hit List Help/Index/SQL Server/Using Oracle and SQL
Server. If you haven't done this crucial first step,
you won't get any further until you do.
b) That your ODBC connection (set up in Control Panel/ODBC/System
DSN) to the SQL Server database can in fact connect
to the database. If you haven't set up a System DSN
yet (or before), do it as follows:
Go to the ODBC applet in the Control Panel.
Create a new System DSN for use with Hit List,
by going to the System DSN tab and clicking "Add"
Pick the SQL Server driver, making sure it's 3.60xxxx
or later (if not, you WILL want to update your Data
Access Components first from www.microsoft.com/data/mdac2.htm
- download and install before going any further
- pick the approx 6.2 meg download, that will do
fine).
Use "HitList" for the name of the data source,
and pick Local from the drop down select for the
SQL Server to connect to, and hit Next.
Now use "SQL Server authentication", NOT "NT Authentication",
and check the "connect to SQL Server . . " checkbox,
enter "sa" without quotes as the Login ID and its
password (assuming you have an sa password, some
don't, in which case you can leave this blank) and
hit Next.
Now check the "default database" setting, pick
the Hit List database you've created already (before
today) leave the other settings alone, and then
hit Next.
Now "Let SQL Server ODBC driver choose the translation
method" and hit Next.
Now hit Finish.
Now "test data source" - it should work, meaning
your ODBC connection should be ok. Hit OK, then
OK again.
Now go to Hit List. Under Options/Database, Browse
and pick HitListNew as your data source, "sa" without
quotes as the user and leave the password field
blank or enter the sa password, then hit OK.
c) Make sure SQL Server is RUNNING when you're trying
to update it from Hit List, at least as an NT Service.
Unlike the default Access database Hit List creates,
SQL Server is a separate application and thus must be
running on its own, independent of Hit List. If it's
not, you must start it up. Check the Control Panel/Services
applet to see if the "MSSQL Server" service is running
on the machine where you're building the SQL Server
database (not always on the same machine as Hit List,
also unlike Access).
d) If all of the above has been setup and tested to
work, you should be able to load data into SQL Server
using Hit List.
2) Second possibility: The SQL Server database
seems to not get any bigger or records errors related
to the "transaction log segment being full" in the NT
Application Event log.
This usually means either:
1) You have not expanded your "Master" and "Tempdb"
databases enough (usually Tempdb) and they're full.
What you'll usually have to do in this instance is:
a) Add space to the Master Database Device (not required
for SQL Server 7.0) as this is usually where the tempdb
and master database are located;
b) Go into the SQL Server Enterprise Manager and
run the query "dbcc checkdb" from Tools/SQL Query
Tool (SQL Query Analyzer in SQL Server 7.0) against
the master and tempdb databases to "fix" anything
wrong with them;
c) Add space to the master and tempdb databases (usually
make master at least 75 meg in size, tempdb around
150 mb should be more than adequate);
d) MAKE ABSOLUTELY SURE that both Tempdb and master
are set to "truncate log on checkpoint" under their
respective Edit/Options tabs (in SQL Server 7.0 this
is Properties/Options of the database).
2) You need to do the same for your Hit List database
(it's full). So follow the steps outlined immediately
above for the Hit List SQL Server database, except
that you'll likely have to expand the Hit List Database
Device and Log Device(s) to allow expansion of the
Hit List database.
Oracle users:
Most problems with Oracle databases seem to stem from connectivity
issues, both in initial connection to the Oracle database
via ODBC and then maintaining it. Below is a document that
discusses Hit List/Oracle/ODBC connectivity generally with
some tests you can do to make sure you can connect effectively.
Oracle "connectivity checklist":
IMPORTANT NOTE - all of this only applies if you
are NOT using an Oracle TNSNames server - if you are, you'll
have to talk with your Oracle dba about connectivity issues
directly.
1) Setting up the Oracle Client and checking network
connectivity:
1a) Install the Oracle Client for the NT machine you
are using with Hit List so that Hit List can ultimately
communicate to the Oracle machine where the database
will be stored. This Oracle Client install should include:
SQL*Net (plus "protocol adapters", ie NetBEUI, TCP/IP,
Named Pipes, etc.)
Oracle ODBC driver (despite not probably using it)
SQL*Plus
1b) Next step:
Set up your SQL*Net alias, using "SQL*Net easy config"
required here:
Oracle SID name
machine name or IP address of the Oracle server
machine
port the server is listening on (will be either
1521 (the default) or 1526)
arbitrary alias name, picked by yourself
1c) Next step:
reboot machine
1d) Next step:
Test the alias using TNS ping
A) TNS ping - from dos prompt, do a TNS Ping
example: TNSPing alias.world (as set up above,
include the extension .world after your alias)
what you're doing here is to use your set up alias
to go out and connect to the database, establishing
that your network connectivity is fine. This does
not guarantee that you can do anything once connected,
but it should establish that you can connect.
You should get an error here if this doesn't work.
If you can't get past this, you won't be able to
connect further in, so you'll have to resolve this
first.
Things to look for in the case of an error:
- version of SQL*NET needs to be "higher" than
the database, ie
SQL*NET 2.3.2.1 will NOT work with Oracle server
7.3.2.2
SQL*Net 2.3.4 WILL work with Oracle server 7.3.3.5
(note - the "versions" of SQL*Net all start with
2 instead of 7, but the latter three numbers need
to be higher as seen in the example)
- error message should be relatively informative
as to what might be going on (ie oracle not found)
- if you have set the database to port 1526 but
the Client expects (ie TNS Ping) port 1521, it will
connect using TNS Ping, but then SQL*Plus will not,
because it's expecting port 1521 if you otherwise
don't indicate a port - this information your Oracle
DBA will have, what port the TNS listener is using.
- when testing the SQL*NET alias with TNSPing as
described above, ALWAYS use alias.world, as it may
not otherwise work at all.
- if another machine has connectivity, and yours
still doesn't work, you can pull two files from
the "connectable" machine to yours to try. These
files are SQL*Net.ora and TNSNames.ora - just do
a windows Find on the "connectable" machine - they
should be in (you'll find many, these should be
If using:
WinNT - OraNT\network\admin - for SQL*NET.ora and
TNSNames.ora (if using SQL*NET
OraNT\net80\admin - for SQL*NET.ora and TNSNames.ora
(if using Net8)
Win95 OraWin\network\admin - for SQL*NET.ora and
TNSNames.ora (if using SQL*NET
OraWin\net80\admin - for SQL*NET.ora and TNSNames.ora
(if using Net8)
One caveat here - you can't go from a "Net8" computer
to a "SQL*Net" computer when copying these files,
as it won't work. Only really an issue if you have
multiple Oracle client setups, some of which are
now using Oracle 8 while some use the older Clients
that have older versions of SQL*Net.
Assuming TNSPing has worked, then you've established
network connectivity and can move to (2).
(2) Testing database connection with with SQL*Plus.
2a) Login to the oracle database using SQL*Plus, ie
scott/tiger@alias.world
The key here is to test with the same oracle "user"
that Hit List will eventually use in its ODBC driver
settings. Otherwise, you only prove you can connect
as "scott/tiger" but not as what Hit List will use.
2b) run some test SQL Statements that prove they (and
subsequent Hit List activity) will work, ie:
create table Test(ID number,Name varchar2(255));
select * from Test;
Delete Test;
select * from Test;
Drop table Test;
If all of these work successfully, then you've established
that everything works on the Oracle side and can go
to (3). If they don't work, Hit List won't either,
so you'll have to check:
proper privileges aren't assigned, and you'll have
to talk with your Oracle DBA.
probable privileges you'll need:
Roles - Connect, Resource
If you can't create a table, for example, you likely
either don't have space or don't have permission.
The Resource Role should take care of both of these
issues once granted. If you can't connect, you should
see something like "you do not have connect privileges"
which a Connect Role would allow.
(3) Use Oracle's ODBCTest application that was installed
with the Oracle ODBC driver, should be in a program group.
What you'll want to do is "test" the Microsoft ODBC driver
that you will use shortly with Hit List.
If you haven't already, set up a System DSN in the
Control Panel ODBC applet that uses the Microsoft ODBC
driver for Oracle, MAKE SURE that you have Microsoft
ODBC for Oracle, version 2.573.292700 from May 27, 1998
- (if not, get them from www.microsoft.com/data/mdac2.htm,
download the 6.2 mb components, you don't need the whole
SDK)
start Oracle ODBCTest from the Start menu, and pick
the datasource referenced immediately above as your
datasource, because Hit List will be eventually using
it.
perform the same SQL statements you did in (2) to test
the datasource.
If everything works here, the datasource should work.
What this tells you is that if Hit List were to use
these precise queries they should work.
If this doesn't work - it's likely the ODBC driver
or the way the SQL is getting sent to the database by
ODBC.
|
