![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Connectivity » View Thread |
Messages 1 to 10 of 10 total |
![]() |
Thu, Sep 22 2011 8:15 PM | Permanent Link |
TonyWood | Hi All
I'm trying to develop a tool to help our operators migrate large data sets to ElevateDB. This is a retail application, so the migration is typically carried out overnight, under time pressure. The data has to be converted, cleansed, constrained and inserted into ElevateDB. The tool, written in VB.net) will pick up and parse SQL script files, sending the individual queries to ElevateDB using .NETs OdbcCommand.ExecuteNonQuery and OdbcCommand.ExecuteReader methods. Initially i am testing with a local server, the live tool will use a remote server. This is a single threaded application. Anyway all is well until I try to execute big scripts. The one in question failing after about 17,000 lines. The error I get is : Error processing command : <Valid INSERT statement that looks very similar to the previous few hundred accepted statements>. Attempted to read or write protected memory. This is often an indication that other memory is corrupt. Continue Processing ? [Yes] [No] If i elect to continue, i get the same error on the next statement. I've seen this error elsewhere in this forum, but not sure if it's relevant, because i'm not using DBISAM or (AFAIK) dbodbc driver version 3 (i.m using Elevate 2 ODBC Driver edbodbc.dll). The disk partitions are not full and i don't think i have memory issues. Any ideas chaps? Thanks in advance |
Thu, Sep 22 2011 9:18 PM | Permanent Link |
TonyWood | TonyWood wrote:
I tried inserting a 200ms delay between query lodgements. The script progressed further to line 31930 ( query # 8609) which was the innocuous : INSERT INTO "Suburb" (Suburb,State,Postcode) VALUES ('DRY CREEK','SA','5094') and then failed with the same error as before |
Thu, Sep 22 2011 10:00 PM | Permanent Link |
Raul ![]() | Haven't used EDB with .net but couple of options using google: - Is the .net framework fully patched? - does stack trace show where does the error originate from more precisely - MS has this KB article that may apply: http://support.microsoft.com/kb/923028 - There are couple of reports that (not with EDB) that mention that once they removed all comments from sql script it ran fine. - finally the obvious question is how easy would it be to break the large script into couple of smaller ones - does it finish execution ok ? Raul |
Fri, Sep 23 2011 12:05 AM | Permanent Link |
TonyWood | Thanks for the quick response, Raul
As you hinted, I now see that the error is returned from the Windows system or .Net, not from ElevateDB With that revelation I tried closing and re-opening the ODBCConnection after every 100 queries and the error went away. Thanks again for your help Tony Wood |
Fri, Sep 23 2011 3:49 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Tony,
<< Anyway all is well until I try to execute big scripts. The one in question failing after about 17,000 lines. The error I get is : >> I suspect that the problem is related to the garbage collection in the .NET memory manager, or just a general issue with the size of the script. What is the total number of lines in the script ? -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Sep 29 2011 3:16 AM | Permanent Link |
TonyWood | Hi Tim
That script has about 50,000 lines and about 16,000 queries and can take up to 2hours to run via EDB manager on a large data set. It's the biggest of several used in data migration, which in total may take up to 6 or 7 hours. Prior to this is a (non-sql) data conversion step which also may take a few hours worse case. I've managed to overcome the original problem by re-opening the ODBCConnection object at least after 1000 queries. Also i needed to re-open it after 'Drop' and 'Create Index' commands to work around locking issues. What i'm ultimately attempting to do is to minimise the total elapsed time by having multiple instances / threads simultaneously loading the target databases, in the cases where parallelism is easy to spot and safe to execute. thanks for your response Tony Wood |
Mon, Oct 3 2011 9:23 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Tony,
<< I've managed to overcome the original problem by re-opening the ODBCConnection object at least after 1000 queries. Also i needed to re-open it after 'Drop' and 'Create Index' commands to work around locking issues. >> What locking issues were those ? There shouldn't be any issues with locking with respect to any DDL statements, at least as related to the ODBCConnection object. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Oct 4 2011 12:15 AM | Permanent Link |
TonyWood | "Tim Young [Elevate Software]" wrote:
>> What locking issues were those ? There shouldn't be any issues with locking >> with respect to any DDL statements, at least as related to the >> ODBCConnection object. i seem to get the "Error #300 Cannot Lock the (temporary)? table <TableName> in the schema Default for exclusive access" after DROP {TRIGGER, COLUMN, FUNCTION, INDEX, TABLE} or CREATE INDEX the (pseudo) code looks like : oODBCConnection.Open() while (more commands to execute) Dim command As New OdbcCommand(sqlCOmmand, oODBCConnection) Dim rowsAffected As Integer = command.ExecuteNonQuery() end while oODBCConnection.Close() As i said, It's not a big deal to me as i can work-around the issue by closing then re-opening the ODBCConnection when an offending query is detected thanks again Tony Wood |
Tue, Oct 11 2011 2:38 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Tony,
<< seem to get the "Error #300 Cannot Lock the (temporary)? table <TableName> in the schema Default for exclusive access" after DROP {TRIGGER, COLUMN, FUNCTION, INDEX, TABLE} or CREATE INDEX >> Is the word "temporary" actually present in the error message ? << the (pseudo) code looks like : >> The code looks fine, but what really matters is what SQL statements are being executed. I just tried altering a table using an ODBCCommand, and then tried to open, and then alter, the same table in the EDB Manager, all without doing anything further with the .NET application after it executed the OdbcCommand.ExecuteNonQuery() method call, and it works fine here with 2.05 B11. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Oct 13 2011 3:04 AM | Permanent Link |
TonyWood | Hi Tim, thanks for your responses. I've changed my client code from VB .Net to Perl on Cygwin which seems to have 'fixed' these locking issues.
The word 'temporary' was only appearing in the error message after an attempt to DROP TABLE on a temp table. In the other cases it didn't appear. Here's an example of the kind of stuff that was provoking the error in VB .Net, each command would be submitted individually thru ODBC: CREATE TEMPORARY TABLE dataCleanTmp(t INT NOT NULL) INSERT INTO dataCleanTmp VALUES(1) INSERT INTO Promotion (BusinessCode , PromotionCode , PromotionName , Description) SELECT '001' , 0 , '' , '' FROM dataCleanTmp WHERE NOT EXISTS(SELECT * FROM Promotion WHERE BusinessCode='001' OR PromotionCode=0) RANGE 1 TO 1 DROP TABLE dataCleanTmp << get locking error here, typically after hundreds of error free queries (this isn't my SQL, i'm trying to write software which will automatically lodge any valid SQL commands it finds in a random script) Also, I no longer have to reopen the connection after every 1000 or whatever queries. thanks again for your help Tony Wood |
This web page was last updated on Wednesday, August 14, 2024 at 03:12 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |