I am trying to use the data warehouse functions provided with OI to extract some LH data into a SQL 6.5 database. All works fine until I come to a decimal field. I have tried setting the data type mappings as per the white paper "Data Warehousing: SQL Server Specifics" but it always fails to update. All rows in the LH table have valid data in the field and I tried debugging the procedure. The data would appear to be correctly formated. I changed the conversion from MD4 to MD2 but this made no difference. I am going to use a stored procedure as suggested but I would like to know what I am doing wrong.

Many thanks

Simon Bowyer

Simon,

A quick question .. have you been able to:

a) Enter the data through the CS Workspace?

b) Using the CS Spy you should be able to see the update/insert command. Can you cut and paste this through CS Workspace (note I have not tried this .. just an idea)

c) Can you send the data through the use of XOQuery?

All of our coding was done throught the use of the connection object not data sets. I had no problem with dollars or percentages.

Let me know ..

dsig@teleport.com

David Tod Sigafoos ~ SigSolutions

David

Thanks for your response - knowing that someone else has achieved this gives me hope!!

I can enter the data through the CS workspace using both the standard SQL insert command and calling a stored procedure. I tried cutting and pasting the insert script from the CS spy and having replaced the arguments, it updated fine. I have tried creating my own warehouse style procedure without any success at all. I have tried sending a script, using DSSetProperty using the row, default row and argument properties without any success. Most of the time, I get a true result (idicating the procedure worked I assume?) but no database updates occur. Is there any other help examples to show how you are supposed to use the CS procedures? To be honest, the whole thing is driving me mad and proving very frustrating. I am currently using a SQL Server dataset but I may try using ODBC instead - don't know why particulary but I'm not sure what else to try!

I really need the stndard warehousing wizard to work as this will be much quicker - I have a large number of tables to warehouse.

Any help would be greatfully received

Simon

Simon,

First question, what version of OpenInsight? There were some SQL Server specific fixes in a recent OI version.

Second, what is the table creation script? Does it work? What is the definition of the DataSet columns? At what point does the warehousing fail? (You may need to step through in a debugger to determine which DS/XO command is failing.)

Cameron Purdy

Revelation Software

Cameron

Thanks for your reply.

In answer to your questions:

OI Version is 3.6. I have downloaded the patch from the web and applied it.

The table creation script is as follows. I had to adjust the Unit_id colum as OI gave it a datatype of just varchar. SQL Server 6.5 defaults this to varchar(1).

create table Unit_Test

       (

       Unit_id Varchar(3) not null primary key,

       Third_party_cheque_max_value Money null

       )

I have also created another warehouse routine that does not include the money columns and this works fine. I don't know where the warehouse routine fails. Every call that I have debugged returns true from its execution but the manager returns errors as follows:

WP_Unit_Test: Error commiting Dataset 1 for 01

(Part of the data may have been warehoused)

This error occurs for every row that is being warehoused and no data updates to the SQL tables occur.

Also, if I select the "Let the warehouse procedure clear the tables" it crashes because it is using the wrong SQL statement. I think I read this somewhere in the OI documentation so you probably already know this. It also crahses if I select the "Do not clear the tables" option in the following subroutine:

* delete existing data

if Info then

• set DataSet arguments (the base key fields)

  Args     ="

  Args=Unit_id_arg"

  Vals     ="

  Vals  =Val_Unit_Id

  Count=count(Handle, @fm) + (Handle # "")

  for i=1 to Count

    Ret=DSSetProperty(Handle[i], DS_ARG$, Vals, Args)

  next i

• execute DataSets

  for i=1 to Count

    Ret=DSMethod(Handle[i], DS_EXECUTE$)

  while Ret

  next i

The last DSMethod call returns false.

I have tried various half brained ideas to try and get it to work without success. I am sure I'm doing something wrong somewhere as other people would appear to be using the warehouse routines fine.

The dataset definitions are as the SQL Server white paper suggests although I have also tried changing them without any success. The "ARG Quoted" column is set to "No" if this is relavent.

Hope this helps!

Thanks

Simon

Cameron

I have also found a similar problem with dates I think. I tried to warehouse a table that hadno decimals but did have dates and it gave the same error. If I warehouse the table with out the date fields (just varchar) it works fine. Although, yesterday, it did crash while selecting (53000 ish rows) but it has not done so since??

Simon

Simon,

Just to be clear .. I have tried the Warehouse system and at that time it did seem to work. Be we wrote a whole different system using the XO system instead of the dataset system.

Okay .. I will fire up my Sql server today and get the processes restarted .. then try some Data warehousing with both dates and decimals ..

dsig@teleport.com

David Tod Sigafoos ~ SigSolutions

dsig@teleport.com voice: 503-639-8080

Simon,

Just got my server back up and running .. normally use Oracle but thought it best to test with SqlServer.

Using OI 3.7 and SqlServer 6.5 .. dates work great. I will cook some decimals tomorrow and report ..

by the way .. my SqlServer files Dates/times are

dbnmp3.dll 1/26/94 12:00am

dbnmpntw.dll 5/11/95 12:00am

netapi.dll 7/11/95 09:50am

wedblib.dll 2/26/92 12:00am

dsig@teleport.com

David Tod Sigafoos ~ SigSolutions

voice: 503-639-8080

Simon,

In your last posting you stated that you were performing the Update and getting a success flag, but no changes were hitting the database.

Are you doing a 'commit' after the updates?

We are using the Qry method to perform direct Sql Updates to SQL server 6.5, and have the same issue if we dont issue a commit after the update.

So i have included three lines that are executed after our scripts that perform the updates. ( I left out the boring bits about checking flag, etc)

Script=commit'

Flag=QryMethod(h_Qry, QRY_EXECUTE$, Script)

Flag=QryMethod(h_Qry, QRY_CANCEL$)

This does the trick for us, and i am sure that the commit would need to be performed if using the dataset method.

Im sure that the same technique would apply to using the datasets.

Hope this helps

Sam

Sam@tristalee.com.au

David,

I managed to get dates to update using the warehouse procedure by removing the date conversion from the options button in the create warehouse dialog. I tried it with numbers but it had no effect.

Thanks for your continued assistance

Simon

Simon,

Did you see Sams posting about transactions? I had *assumed* that you had checked this .. but you know what happens when you *assume* :-0

You can either execute a 'commit' as Sam mentions OR you can set the connection object to avoid transactions (push the 'advance' button on the connection object.

It would be nice if it was this simple ..

What exactly are you attempting to do? Do you need the functions of the warehouse as it is OR …

dsig@teleport.com

David Tod Sigafoos ~ SigSolutions

voice: 503-639-8080

David,

The posting about commit was from a program that I wrote myself not the warehouse procedure. As I already mentioned, if I remove the decimal columns from the warehouse template, all works fine. It only goes wrong if decimal columns are included. I am using the warehouse procedures for speed (!) and ease of use(!!!!). I have a number of tables that I need to warehouse and I don't really want to do it all myself when the tools are there to do it for me….if only!

thanks

Simon

Simon (and cameron)

There does seem to be a problem with decimal in the warehousing process (yeah .. i know .. big surprise to you simon).

As soon as I put a decimal into the warehousing routine no data was saved. Take it back out and pow .. data.

Cameron .. would it be possible to expand the log file to

a) show the actual message this should always be returning from the server ..

b) expand the log file to show all errors. I realize that a real process would not have many errors .. but the log file truncated and so I don't know if there would be as many errors as records I tried to post.

Also .. would it be possible to let csspy see what was going on.

dsig@teleport.com

David Tod Sigafoos ~ SigSolutions

voice: 503-639-8080

David

Hooray! I can take off the straight jacket now as I am not going mad after all! We will have to see what the RevSoft guys say

Thanks

Simon

Cameron,

I know that you are very busy with jrev and heading to the end of the year .. did you get a chance to look at this and verify that it is a problem.

A HREF=http://www.revelation.com/WebSite/discuss.nsf/f12696d31000b22a8525652b00831bb2/d17afd4084d87971852566dd00826d19?OpenDocument]Try Here

tia ..

dsig@teleport.com onmouseover=window.status=you have seen the rest .. now try the best!;return(true)"

David Tod Sigafoos ~ SigSolutions

voice: 503-639-8080

View this thread on the forum...