Timing Problem with ODBCOpen in TI

[Steve Rowe]

I seem to be getting a strange timing issue with ODBCOpen in TI.

Basically what I'm doing is
Step1. Deleting a working copy of an Access DB
Step2. Copying the live Access DB to the working area
Step3. Executing a stored procedure on a SQL DB that references the working copy of the Access DB

My code to do this in the prolog of a TI process is shown below.

Code: Select all

#Step 1 Step 1 Step 1 Step 1 Step 1 Step 1 Step 1 Step 1
#Step one duplicate the Access DB, the paths will be held in the server properties cube in the final version.

SourceFile = ' \\starst1p\Wayfarer\MDBinform160608\Live.mdb ';
Destn='\\starst1p\Wayfarer\CurrentMDB\';
DeleteDestn='\\starst1p\Wayfarer\CurrentMDB\Live.mdb';
BatchFileDelete='DeleteLiveAccess.bat ';
BatchFile='CopyLiveAccess.bat';

DatasourceASCIIQuoteCharacter='';

#Delete the access DB in the Destn directory if it exists
ASCIIOutput ('DeleteLiveAccess.bat','Erase ' | DeleteDestn);
ExecuteCommand ( 'DeleteLiveAccess.bat' , 1);

#EOF File Deletion

#Create the batch file in the data directory
#Step 2 Step 2 Step 2 Step 2 Step 2 Step 2 Step 2 Step 2
ASCIIOutput ('CopyLiveAccess.bat','XCopy %1 %2 /R /Y');

ExecuteCommand ( BatchFile | SourceFile | Destn, 1);

#File should now be copied across

#Step 3 Step 3 Step 3 Step 3 Step 3 Step 3 Step 3 Step 3
#Open ODBC connection to InformSQL and launch the stored procedure to refresh InformSQL from InformAccess

Source='InformSQL';
ClientName='TM1User';
Password='';
SQLQuery='Exec spUpdateMaxKey';

ODBCOpen(Source, ClientName, Password);
ODBCOutput(Source, SQLQuery);
ODBCClose(Source);

All fairly straight forward stuff.... except it doesn't work.
When the ODBCOpen tries to execute I get "Unable to open data source: "InformSQL"" and the process aborts. Now I could sort of understand this if the ODBC connection was pointing at the loaction of the access DB, but it isn't it points at a SQL DB.

I’ve tried putting “dummy” code between steps 2 and 3 getting the same error.
I've tested putting step 3 in the epilog and get the same error.
I’ve tried putting step 3 in a separate TI and calling it from the end of the prolog and get the same error.
I’ve tried putting step 3 in a separate TI and calling it from the end of the epilog and get the same error.
I’ve tried putting the two TIs processes (1 for step1 and 2, another for step 3) and calling them in sequence using a chore, getting the same error.

I’m happy that the code and logic is correct since if I launch the TIs manually then everything works as expected. I have noticed though that it takes a long time after I press the OK button from the first process executed successfully message for control to return to the server explorer.

My best guess of what is going on is that the server is still busy from the file copy (its>1gig), even though the 1 flag in Execute Process is supposed to make the TI wait until the copy is finished.

Anyway does anyone have an idea of what is causing this to fail or how I can work around it? I really need to schedule this up as an overnight process.

Cheers

[Marcus Scherer]

creating several chores with a time lag in between to consider the time to copy?

[Steve Rowe]

Thanks Marcus, I'd already added a CubeProcessFeeders command which introduces a 20 second delay to the proceedings and seems to solve the problem.

I'm still not clear on what the problem is though

ExecuteCommand ( BatchFile | SourceFile | Destn, 1);

The 1 flag in the statement is supposed to force the TI to wait until the command is executed before continuing with the next statement in the TI. As far as I can tell this works, though it's difficult to be 100% accurate with the test.

In any case even if the Access DB was not yet fully formed before the attempt to open the ODBC connection why should the two actions interfere with each other? The ODBC connection does not point at the Access DB.

I don't really like solving problems when I don't know what the problem is, though I'll take that over not solving a problem I do understand!
Cheers

[dkleist]

ExecuteCommand ( BatchFile | SourceFile | Destn, 1);

The 1 flag in the statement is supposed to force the TI to wait until the command is executed before continuing with the next statement in the TI. As far as I can tell this works, though it's difficult to be 100% accurate with the test.

In any case even if the Access DB was not yet fully formed before the attempt to open the ODBC connection why should the two actions interfere with each other? The ODBC connection does not point at the Access DB.

I saw this a number of years ago - for whatever reason, .bat processing of file commands has a lag, whether it's due to caching, bad code, or whatever. I don't think TI is at fault, WinOS is -- my original research was with a different product. The 1 flag is just a passthrough to a DOS command that's supposed to do the same thing. ("call" is the win command maybe?) IIRC, I ended up using VBScript in the .bat file and that seemed to work.

As for the second error, could be simply a misleading error message

[David Usherwood]

You could try writing out a marker file in the .bat command, then checking for its existence to know that the job has completed.
But it's a nice image to think of CubeProcessFeeders as a TM1 'Vamp until ready' command

[Steve Rowe]

I'll try that David, I already had a loop in the TI that tested for the existence of the file I was copying. What you suggest is a slightly different flavour of this. I’m not sure that it will help though as it’s my suspicion that it is the OS that is busy rather than TI/TM1.
Cheers

[Steve Rowe]

Just an update on this.

I couldn't get David's approach to work, not sure if it it's the same problem as before or not.
It also raises another problem in that I have to get ASCIIDelete to work, so that I can perform the test on repeated occasions. I can't get this to work either possibly due to a user rights issue. Since I have work around using a fixed delay in the process, I'm not going to follow it up any further.
Cheers