Robert,
A simple suggestion – would be to query the SysObject table
in the SQL Database – to help
limit the number of tables you are looking at. I.e. narrow the table set
list.
Then use this narrowed result to do an open table script call for
the specific tables you are interested in
in the DB.
Using the connection tool – relies on the JMP software to
be capable of keeping up with changing
bandwidth capabilities in the SQL Server … which is not likely since JMP
will certainly lag with respect
to Microsoft’s rollout of new features and bandwidth capability.
But … if you know a limited set of tables … then the
script command for opening a database resulting in
a single data table in JMP will work effectively. Wanting to get a full
list of all tables with the DB connect
tool is asking the tool to do more than it was designed to do – when you
are talking about 100k of tables.
A starting place for what the SQL Server SysObject table is can
be found here:
http://articles.techrepublic.com.com/5100-10878_11-6142579.html
Also – I would ask the question as to what the referential
structure for your large DB looks like.
In other words – you say that you have 1000s of tables …
how are they related and how can you limit
the scope of tables you would like to look at or use ?
If you need an example of a script to simply open one table –
you can find it in the JMP literature.
Else someone can certainly post one here.
Here is the command from the scripting guide:
JMP supports ODBC access to SQL
databases through JSL with the Open Database command.
dt=Open Database(
"Connect Dialog" |
"DSN=...", // data source
"sqlStatement" |
"dataTableName" | "SQLFILE=...", // SQL statement
"outputTableName" //
new table name
);
Regards,
Don Palahnuk
ImClone Systems, Wholly owned by E Lilly.
From:
jmp-l-owner@lists.biostat.wustl.edu
[mailto:jmp-l-owner@lists.biostat.wustl.edu] On Behalf Of robert.clark-phelps@us.tel.com
Sent: Monday, September 21, 2009 10:26 AM
To: jmp-l@lists.biostat.wustl.edu
Subject: [jmp-l] Issue opening database connection
Good
Day:
I
am having problems connecting to SQL databases with JMP. I have done it
successfully in the past, and I suspect that the issue may have to do with the
databases having grown in size or complexity.
Our
DBs are generated by a semiconductor processing tool. Currently, the
process tool generates one table per day (with a name that corresponds to the
date) and also one table per wafer processed (with a names based on sequence of
the wafers processed). The table for each wafer means that hundreds of
tables can be added in a single day, and over months or years, this can grow to
tens of thousands or even 100,000 tables.
I
think my DB connection issue is caused by this (absurdly) large number of
tables. When JMP connects to my SQL DBs, it automatically loads all the
tables in the DB. When the number of tables grows as large as tens of
thousands, JMP chokes and stalls. We have noticed that when we delete one
of these large DBs and start over, JMP connects successfully.
I
am working with our SW group to greatly reduce the number of tables produced in
our database (by limiting it to one per day only) but in the meantime, I am
looking for a way to fetch only those tables whose name meets a given
criterion. Is there a way to do this? Is it possible to not have
any tables loaded when connecting the the DB and still execute a SQL query
which provides a specific table name to be queried?
Would
welcome any suggestions or input. Some technical details of the software
and drivers we are using are listed below.
Best regards,
Bob Clark-Phelps
1) JMP Version: 8.0.1
2) Operating System: Windows XP Professional
Version 2002 Service Pack 2
3) Release of SQL DB: 2008 SQL Server
4) Release of SQL ODBC Driver: 2008.85.1117.00
from Microsoft
__________________________
Bob Clark-Phelps,
Ph.D.
Product
Development Manager
TEL Epion
Inc.
37 Manning Road
Billerica, MA
01821
Company Phone:
978.436.2300
Direct Line:
978.436.2480
FAX: 978.436.2301
Email:
robert.clark-phelps@us.tel.com
Confidentiality Note:
This e-mail, and any attachment to it, contains privileged and confidential
information intended only for the use of the individual(s) or entity named on
the e-mail. If the reader of this e-mail is not the intended recipient, or the
employee or agent responsible for delivering it to the intended recipient, you
are hereby notified that reading it is strictly prohibited. If you have
received this e-mail in error, please immediately return it to the sender and
delete it from your system.
Thank you.