When a stored procedure using temporary table and external name calls to fill data inside the table is run there seems to be a bug which causes temporary tables to not be found at random times. Using SQL Anywhere 16. I've come up with a simple scenario, the bug seems to happen once every ten times for me.

Stored Procedure:

CREATE PROCEDURE "TestQuery"( ) 
on exception resume
begin
  create table #tempResults(
    "Text1" "text" null,
    "Text2" "text" null,
    "Text3" "text" null,
    "Text4" "text" null,
    );
  insert into #tempResults values(ExternalFunction(), ExternalFunction(), ExternalFunction(), ExternalFunction()) ;
  insert into #tempResults values(ExternalFunction(), ExternalFunction(), ExternalFunction(), ExternalFunction()) ;
  insert into #tempResults values(ExternalFunction(), ExternalFunction(), ExternalFunction(), ExternalFunction()) ;
  insert into #tempResults values(ExternalFunction(), ExternalFunction(), ExternalFunction(), ExternalFunction()) ;
  insert into #tempResults values(ExternalFunction(), ExternalFunction(), ExternalFunction(), ExternalFunction()) ;
  insert into #tempResults values(ExternalFunction(), ExternalFunction(), ExternalFunction(), ExternalFunction()) ;
  insert into #tempResults values(ExternalFunction(), ExternalFunction(), ExternalFunction(), ExternalFunction()) ;
  insert into #tempResults values(ExternalFunction(), ExternalFunction(), ExternalFunction(), ExternalFunction()) ;

Select * from #tempResults end

External Name:

CREATE FUNCTION "ExternalFunction"() 
returns "text"
external name
'C:\\ExternalFunction.dll::ExternalFunction.ExternalFunctionClass.ExternalFunction() string' language "CLR"

External Function (C#):

namespace ExternalFunction
{
    public class ExternalFunctionClass
    {
        public static string ExternalFunction()
        {
            return "Hello World!";
        }
    }
}

asked 12 Dec '16, 12:40

NickArx's gravatar image

NickArx
66237
accept rate: 33%

So what exactly is the error you get?

(12 Dec '16, 14:32) Volker Barth

As a possible workaround, try using DECLARE LOCAL TEMPORARY TABLE instead of CREATE TABLE #.

(12 Dec '16, 15:25) Breck Carter

There are some errors that cause temp tables to be tossed. One of them is "All threads are blocked", but this was appearing when the client is connecting/disconnecting between calls. How is the "call" to TestQuery being executed? Is it through Interactive SQL, or a .NET app, or ...?

If there is an error message, let us know.

Also what build of 16.0.0 are you using?

(13 Dec '16, 10:54) JBSchueler

Exact error is "Table '#tempResults' not found.".

Similar error happens when DECLARE LOCAL TEMPORARY TABLE is used.

Executing the query using Interactive SQL. Similar thing happens when our stored procedures are run through third party application (Sometimes results don't get returned with same error). Running build 16.0.0.2193

Have tried a lot of other approaches, seems to be an actual bug with Sybase.

(13 Dec '16, 13:51) NickArx
Replies hidden
1

There must be more to the story than this. I tried 16.0.0.2193.

I compiled the C# code using the framework64\v4.0.30319\csc.exe compiler.

I ran the 16.0 version of V4\SetupVSPackage to remove all provider instances (/ua) and install the v4.5 provider (/i /v 4.5).

I configured the CLR external environment support for .NET v4.5.

I had 4 JDBC clients running, each executing 10,000 iterations of execute a "call TestQuery" statement and fetch the result set. I also had one Interactive SQL client running, executing occasional calls.

No errors.

In my process list, I see one instance of dbextclr16_v4.5 running.

(13 Dec '16, 15:29) JBSchueler
Replies hidden
Comment Text Removed

Does the exact code shown above exhibit the problem, or is that code different?

(14 Dec '16, 07:21) Breck Carter
Comment Text Removed

I tried configuring CLR for .NET v4.5 using: ALTER EXTERNAL ENVIRONMENT CLR LOCATION 'dbextclr16_v4.5' but I get error "External environment could not be started, 'external executable' could not be found".

I ran "SetupVSPackage.exe /ua" and "SetupVSPackage.exe /i /v 4.5" previous to this..

How do i get dbextclr16_v4.5.exe?? Thanks

(04 Jan '17, 16:09) NickArx

Run this statement. It's version agnostic.

ALTER EXTERNAL ENVIRONMENT CLR LOCATION 'dbextclr[VER_MAJOR]_v4.5';

By the way, do you really have the DLL in the root of your C drive? Try putting it in a subfolder.

(04 Jan '17, 16:51) JBSchueler
Comment Text Removed
Comment Text Removed

Same result. Also dbextclr16_v4.5.exe doesn't exist in Bin32 or Bin64 folder..

Moved DLL to subfolder but still getting table not found error sometimes (holding F5 in interactive SQL)

(10 Jan '17, 11:30) NickArx

Error shows up with the exact code above if you hold F5 in Interactive SQL.

(10 Jan '17, 11:43) NickArx
More comments hidden
showing 5 of 10 show all flat view

Seems to be fixed in v16.0.0.2344

permanent link

answered 01 Mar '17, 17:40

NickArx's gravatar image

NickArx
66237
accept rate: 33%

Your answer
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

Question tags:

×125
×27
×23

question asked: 12 Dec '16, 12:40

question was seen: 2,127 times

last updated: 01 Mar '17, 17:40