SQL Server database (OpenInsight) [Revelation On-Line Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
{{tag>category:"OpenInsight" author:"j Vaughan" author:"Bob Carten, WinWin / RevTech"}}

[[https://www.revelation.com/the-works|Join The Works program to have access to the most current content, and to be able to ask questions and get answers from Revelation staff and the Revelation community]]


==== SQL Server database (OpenInsight) ====
=== At 09 JAN 2002 07:12:16PM j Vaughan wrote: ===
 
 
<QUOTE>
How can I access an SQL Server database from within OI? I need to read and write data. 



Is my only choice ODBC?
</QUOTE>
----

=== At 11 JAN 2002 11:56AM Bob Carten, WinWin / RevTech wrote:  ===

<QUOTE>Jim:



ODBC is the most straightforward method, as long as your data is already normalized.  JOI, our Java product, speaks XML to  and from OI quite nicely and is comfortable talking to SQL server.  If you need a 'smart' agent to connect OI and SQL server, check out JOI.  If you are doing 'Real-time' stuff, e.g writing data to sql upon a save in OI, or filling popups from SQL then ODBC or JOI are probably the best choices.



If you are replicating a lot of data on an intermittent or scheduled basis then you have more options.



Check out the Data Warehousing wizard in the Client Server workspace.

It will do a lot of the work to get you going.



You could also look at using Data Transformation Services (DTS) in SQL7 or better.  DTS lets you define "smart" imports and exports that can move data in or out of sql and edit it on the way.  DTS provides a command line ("rundts" i think) that you can call from OI or Arev to cause SQLServer to run one of the scripts.  You can also set them to run on schedule.



Bulk copy program, or BCP, is an older process that will import or export data.  It too can be command line driven.  It is not as flexible as DTS, but works well with tab delimited data and is very fast.  You can drive BCP  from DOS. SQL server 6.5 ships with SQL_DMO objects which can be driven from VB and probably from vbscript.



SQL2000 can handle XML in and out.  XML is just delimited data, so OI loves it. You can write your own routines exchange XML data.   Agin, JOI already does the XML work for you, so it can help here too.







Hope this helps,



Bob

</QUOTE>

----

=== At 11 JAN 2002 07:22PM j Vaughan wrote:  ===

<QUOTE>It seems I need to look at ODBC, thanks for the help. 

</QUOTE>


[[https://www.revelation.com/revweb/oecgi4p.php/O4W_HANDOFF?DESTN=O4W_RUN_FORM&INQID=WORKS_READ&SUMMARY=1&KEY=89C9056C2B2E464488256B3D00011FE0|View this thread on the Works forum...]]