When I execute an "Online Pack" in ADS 11.10.0.10 I get this error message :

Error in SQL query: Error 7200: AQE Error: State = HY000; NativeError = 5024; [iAnywhere Solutions][Advantage SQL][ASA] Advantage does not have a connection open with the specified handle. execute procedure sp_PackAllTablesOnline();

This is when using the interface in ARC or via an SQL Statement as shown here.

I am trying to schedule the Online Pack to take place as a maintenance task in the Visual Cron tool on Windows 2003.

I have tried setting a password on the database dictionary adssys user, opening the table first in Exclusive mode, changing the connection properties to use the server Ip address instead of a mapped network drive path. Also, a free connection without a database dictionary gives the same error.

I know I have seen the Online Pack work on this server but I don't know what I have to do for it to be reliable.

Thanks

Kim Groves

asked 16 Jun '14, 01:53

Kim%20Anthony%20Groves's gravatar image

Kim Anthony ...
346712
accept rate: 0%

From the ads_err table :

Error_Number DateTime Thread_Num Err_Class Error_Code ADS_Source Src_Line Cnnct_Num Cnnct_Name FileName ADS_Ver Environmnt OS_Version HostName More_Info 51772540 06/16/2014 6:47:14 PM 2968 0 5024 dbf.c 12852 CAPSERV1 11.10.0.10 Windows NT 5.2 SRVADS

(16 Jun '14, 02:01) Kim Anthony ...

This site has several Windows Servers, some are Windows Server 2003 and some are Windows Server 2008. The W2k8 servers are able to run sp_PackAllTablesOnline() in Advantage Data Architect. In other programs, the 5024 error is returned. I feel that this issue could be happening because we are still using ADS version 9.1 for the client drivers. The client applications that we are using are not working or not tested with the ADS version 11.1 client drivers. Does the Client DLL have to be version 11.1 in order to pass that Pack All tables command to the ADS version 11.1 server?

(16 Jun '14, 05:45) Kim Anthony ...

On a server where sp_PackAllTablesOnline() works in ARC, I have written a PHP CLI script to connect using the ADS ODBC driver and run this command. I am getting the 5024 error here also. This server has only the Version 11.1 ODBC client driver installed.

(16 Jun '14, 05:50) Kim Anthony ...

The problem is most likely a difference between the table path(s) and the connection path. The 5024 error is being logged when the server attempts to create a new table using the path of the original table on the connection of the SQL statement. The table path should be a local path, i.e. D:\data\table.adt. If the server cannot match the connection path and the table path, it will think the connection is not to the same server. I would have thought using the IP:PORT connection would work. If that doesn't work, try using any other connection path you can (network share, local directory, UNC).

link

answered 16 Jun '14, 12:26

Peter%20Funk's gravatar image

Peter Funk
4.3k2380
accept rate: 32%

On the Terminal Server that is getting a 5024 error, a UNC Connection path was being used ie \192.168.0.1\Database\Tables\dictionary.add If I change the UNC path to use the server Name instead ie \ads-server\Database\Tables\dictionary.add then the Online Pack will work.

(16 Jun '14, 20:03) Kim Anthony ...

On another Terminal Server where the Online Pack function was known to be working, I checked the connection path there and a Mapped drive was being used ie z:\Tables\Dictionary.add The z:\ drive was mapped to \ads-server\Database I tried mapping Y:\ drive to \192.168.0.1\Database and using Y:\ in the connection string - error 5024 was returned!

(16 Jun '14, 20:03) Kim Anthony ...

So there is definitely something about using the IP address in the UNC path which is not working with the Online Pack. When ADS gets this path in order to create a new file, it really shouldn't matter (in my opinion) if \192.168.0.1\Database or \ads-server\Database is used as they both refer to the same path on that system and the name ads-server resolves to 192.168.0.1 when I check it with ping.

I have a solution so I am happy. Thank you Peter!

(16 Jun '14, 20:03) Kim Anthony ...

Addenda - I use the IP address out of habit in the UNC Path as it is easier to remember than the server name. The server name has been abbreviated ie \srvads\ which I get confused with \svrads\ one is right and one is wrong and I hate having to test and fix it.

(16 Jun '14, 20:05) Kim Anthony ...

I'm glad you were able to get it to work. You should be able to run sp_PackAllTablesOnline from any client. It doesn't have to be run on the server. I added some better logging to the ads_err log file to help figure out this problem when it happens.

(17 Jun '14, 09:51) Peter Funk

Hi Peter, I scheduled the Online Pack to run on several databases this morning at 3am local time. Two of the databases were packed in 1 minute and 7 minutes respectively which is great. Another database the job is still running (9 hours later). If I load ARC and look at the Remote Server Info for that dictionary, it shows the query with Active=True, Percent Complete=100%, Seconds Until Finished=0. I tried using the Cancel Query function with no response. I tried closing down the client application but the query is still running. I tried disconnecting the user with no response.

(17 Jun '14, 19:23) Kim Anthony ...

Can you suggest any reasons that the job would not finish? It may be an artefact of the testing I was doing yesterday. I also noticed I had scheduled the Pack jobs to take place at the same time as some other maintenance tasks. I have moved the Pack jobs back to an earlier time. Hopefully we can get this to be stable in the long term as there are some tables that end up being 80% deleted records which I think slows down the client application.

(17 Jun '14, 19:25) Kim Anthony ...

The legacy solution is to run the database maintenance tool in the client application which rebuilds all of the indexes and packs the tables but it has to be run by a database administrator late at night when nobody else is in the system.

(17 Jun '14, 19:26) Kim Anthony ...

Avoiding the old requirement of exclusive access to the table is the whole point of online maintenance so don't give up! The online maintenance functions (pack, reindex, alter, etc) can only be run one at a time on any single table. So it would be best to stagger the operations so they don't overlap. You should be able to cancel the operation at any time, so I don't know why you had trouble with that. The help documentation describes each operation and what can prevent them from finishing. If you send me a dump of the server when it can't finish I can tell you why.

(18 Jun '14, 09:42) Peter Funk
showing 5 of 9 show 4 more comments
Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×172
×55
×40

Asked: 16 Jun '14, 01:53

Seen: 5,247 times

Last updated: 18 Jun '14, 09:42

Advantage Developer Zone Contact Us Privacy Policy Copyright Info


Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.