Q & A – Reporting, Database

This document provides some frequently asked questions about Sandra. Please read the Help File as well!

This document is intended for advanced users that are well versed with database programs and configuring database clients. There is nothing special in the way Sandra connects to database servers, this is just a checklist of things you may encounter.

Q: What should I use? ADO or ODBC?
A:
ADO allows you to connect directly to a server without any client configuration (well, almost). ODBC has smaller memory footprint, is faster and has more varied database support. You can also connect through ADO using the ODBC driver with no DSN set-up.

Q: Where can I set-up the connection settings (type, IP ports, etc) for the client?
A:
There is no place in Sandra to set this. Please set it in the Client Configuration tool for the specific driver.

Q: What databases are officially supported?
Q: What schemas are provided (i.e. supported) by Sandra?
A:
See the Compatibility Document for up-to-date support information.

Q: Do you have a schema/support other databases?
A:
Ask, but you should be able to easily derive a schema for them from one of the supplied schemas.

Q: What should be the extents/size for the database?
A:
Go for a min of 10MB for small reports. Do note that a full report may be ~4MB so we’re taking 100MB for full reports of a reasonable size network. The transaction log will also be quite large, start at 5MB.

Q: Can I add my own indexes, triggers and stuff?
A:
Of course, knock yourself out. If you find a good modification of the schema, please send a copy to us also.

Q: What versions of ODBC are supported (i.e. which version does the driver need to comply to)?
A:
ODBC v3 (3.5x) drivers are strongly recommended; some v2 drivers may work but on your own risk.

Q: How do I connect by ODBC without a DSN?
Q: How do I connect by ADO if no ADO/OLE DB driver is available?
Q: How do I connect by ADO if the ADO driver does not work (for some reason?
A:
Use the ADO-to-ODBC provider bridge, MSDASQL, and connect through it. You need to have ODBC v3 (3.5x) drivers installed.

Q: How do I connect via the ADO-to-ODBC provider bridge?
A:
Have a look in help. In a nutshell, select MSDASQL as provider, enter the ODBC driver name verbatim as driver, server host name and database name if server-based database or the data file name as database if file-based database. And don’t forget the user and password since Windows authentication will not work.

Q: I cannot connect to Access/Jet through ADO!
A:
Due to a “feature” in Jet, some table names are interpreted as reserved words. Since we cannot change the schema now, please either use the ADO-to-ODBC provider or ODBC. We would advise against using a remote file for Jet as substitute for a network database server due to locking issues.

Q: Should auto-increment/identity support be on for MySQL?
A:
No. While MySQL does support auto-increment, it does not support the identity function.

Q: I cannot connect to MySQL through ODBC!
A:
Check the ODBC drivers are compatible with the MySQL server and that they support at least ODBC v3 (3.5x). If that fails, try the old ODBC v2 MySQL driver. Try connecting using Microsoft’s ODBC Test.

Q: Connecting through the ADO-to-ODBC bridge to MySQL fails with “transactions not enabled”!
A:
Make sure you use the version of MySQL that has transactions enabled, i.e. the -Max version. The default version may not have transactions support.

Q: I cannot connect to Oracle through ODBC!
A:
Make sure SQL*Net Client is correctly installed and configured. Check that you can connect from SQL*Plus to the Oracle server. Then try to connect from Oracle ODBC Test using the DSN that you’ve set up. Then make sure you’re using the right user name and password.

Q: I cannot connect to Oracle through ADO!
A:
Make sure MSDTC Config is right for your Oracle installation, i.e. the Oracle OCI, SQL and XA point to the right libraries for your client version. Usually, they’re set for Oracle 7.3 not 8.0 or 8.1! Fix them and then try again.

Q: Which ODBC driver should I use for Oracle?
A:
Use whichever works. Usually the Oracle one works better if you’re using the same version of the driver as the database. If you need to connect to different versions of database servers use the Microsoft one.

Q: I still cannot connect to Oracle. What now?
A:
Either see your friendly Oracle DBA or try Oracle itself.

Q: Why are there 2 versions of Oracle schema?
A:
One is using native Oracle sequences for key generation; the other attempts to simulate the auto-increment keys. It is recommended you use the native one unless you use a mixed environment and need to use database independent configuration scripts.

Q: What about SyBase?
A:
Try using SQL Server if at all possible.

Q: Which database server should I be using?
A:
Use whatever you have a licence for. For a 2000/2003 Server, SQL Server fits in well; for Linux, MySQL works well; for Solaris go for Oracle (don’t forget the patches!); for IBM stuff go for DB2.

Q: Which database server is faster: IBM DB2, SQL Server, Oracle, SyBase or MySQL?
A:
This is beyond the scope of this document. However, informal testing (default install, same dual PIII server, W2K AS, ATA RAID) of SQL Server Enterprise 2K (Eval), Oracle Enterprise 8i (8.1.4.0.0 Eval), MySQL 3.23.xx using the given schemas concluded that Oracle is much faster in ODBC but slower in ADO (as it’s using client cursors). MySQL is great considering the low footprint and works well; for the price (free) is hard to beat. DB2 is cool…

 

Comments are closed.