AseBulkCopy.WriteToServer error: Incorrect syntax near ','

[Former Member]

Is it possible to populate a temp table using AseBulkCopy.WriteToServer?

I am calling the below method twice in my test app: firstly with a non-temp table and secondly with a temp table. The code runs fine with the non-temp table, but when trying to populate a temp table the error:

    Incorrect syntax near ','.

is raised.

In both cases the target and source tables have just a single column, defined as an INT with the same name.

I have tried using a DataTable and an IDataReader as the source of the data and both result in the same error being raised.

I have tried using both "EnableBulkLoad=1" and "EnableBulkLoad=2" in the connection string.

I have tried using both the raw temp table name and the name prefixed with "dbo."

The data to be loaded is a single int value (ie, 1 row, 1 column) although it also happens if have longer rows or multiple rows.

It's worth noting that I can insert data into the temp table (using AseCommand.ExecuteNonQuery) and can execute a 'SELECT COUNT (1)' from the temp table (using AseCommand.ExecuteScalar) successfully.

Here is the code:

  private static void BulkCopyInsertIntoTable(string tableName)

  {

    IDataReader dataSource = null;

    SqlConnection sourceConnection = null;

    MssqlCommand.GetDataReader(SqlServerConnectionString, out sourceConnection, out dataSource);

    AseConnection targetConnection = new AseConnection(SybaseConnectionString);

    try

    {

      targetConnection.Open();

      AseCommand cmd = new AseCommand();

      AseBulkCopy blk = new AseBulkCopy(targetConnection);

      blk.DestinationTableName = tableName;

      //blk.ColumnMappings.Clear();

      //blk.ColumnMappings.Add(new AseBulkCopyColumnMapping(0, 0));//Doesn't make any difference with a datasource, causes an error to be raised with a datatable.

      Console.WriteLine("bulkcopy insert into the table " + tableName + " ..starting: datasource");

      //blk.WriteToServer(dataSource);

      Console.WriteLine("bulkcopy insert into the table " + tableName + " ..starting: datatable");

      blk.ColumnMappings.Clear();

      DataTable dt = SybaseCommand.GetFakeDataTable(); ;

      blk.WriteToServer(dt);

    }

    catch (AseException ex)

    {

      Console.WriteLine(ex.Message);

    }

    finally

    {

      targetConnection.Dispose();

      Console.WriteLine("bulkcopy insert into the table " + tableName + " ..ended");

    }

  }

Firstly, is it possible to populate a temp table using WriteToServer?

Assuming it is, what might I being doing wrong?

Thanks

[dawn_kim]

Hi,

This actually sounds like a known ASE CR dealing with dynamic statements and temp tables.

SAP CR 815056. Its description reads,

" A 208 error, "#<temporary_table_name> not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).", may be reported during the recompilation of a prepared statements batch which creates a temporary table.".

Fixed in ASE 16.0 SP03 PL05 and ASE 16.0 SP02 PL08.

Thanks,
Dawn K.

[dawn_kim]

The above is for the SDK, but I am sure you can find the 30 day trial for ASE.

[dawn_kim]

you need to trials version.

1. You can just go directly to the Developers Trials and Downloads: https://developers.sap.com/trials-downloads.html
2. In the Type here to filter type SDK for Adaptive Server Enterprise

[former_member951114]

The "Trials and downloads" for developers do not contain the version you mention.

I am trying to experiment and study the SDK at home and I don't have any SAP licenses.

Is there a way I could get the SP03 PL08 SDK ? I see it exists on the versions page but I can't seem to find a download link for it anywhere.

Thanks

[former_member358098]

https://launchpad.support.sap.com/#/softwarecenter/search/sdkase

[former_member951114]

I don't think I can download anything from there

Actually, I get a screen flash for 0.1 second that shows this error message :

[former_member951114]

Hi,

I'm trying to perform the same thing this thread's original poster was doing.

-Open a DB connection (opens a session)

-Create a temporary table using the ado.net method "ExecuteNonQuery"

-Populate temporary table using the "AseBulkCopy.WriteToServer" method.

-Do some processing with the temporary table

-Close DB connection (closes session, dropping temp db)

Version of the ASE database I'm running (runs in a docker container)

Adaptive Server Enterprise/16.0 SP02 PL02/EBF 25320 SMP/P/x86_64/Enterprise Lin
ux/ase160sp02plx/2492/64-bit/FBO/Sat Nov 21 04:05:39 2015

Version of the ADO.NET SDK I'm using : SDK 16.0 SP03 PL04

Here is a snippet of the problematic code :

            string bulkConstring = "Data Source=localhost;Port=5000;Database=testdb;Uid=sa;Pwd=myPassword;EnableBulkLoad=2";
            using (var connection = new AseConnection(bulkConstring))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                using (var aseBulkCopy = new AseBulkCopy(connection))
                {
                    command.CommandText = "CREATE TABLE #accounts (AccountId binary(16),AccountTotalValue money,AccountOwner nvarchar(1000),OwnerAddress nvarchar(1000))";
                    command.ExecuteNonQuery();
                    
                    aseBulkCopy.DestinationTableName = $"#accounts";
                    aseBulkCopy.BulkCopyTimeout = 1200;

                    //Columns mapping, which DataTable columns are mapped to which SQL table columns
                    aseBulkCopy.ColumnMappings.Add(new AseBulkCopyColumnMapping(0, 0));
                    aseBulkCopy.ColumnMappings.Add(new AseBulkCopyColumnMapping(1, 1));
                    aseBulkCopy.ColumnMappings.Add(new AseBulkCopyColumnMapping(2, 2));
                    aseBulkCopy.ColumnMappings.Add(new AseBulkCopyColumnMapping(3, 3));

                    aseBulkCopy.WriteToServer(myDataTable);// crash happens here
                }
            }

This is the exception the code above generates :

Sybase.Data.AseClient.AseException: 'Incorrect syntax near ','.

If I change the table name from "#accounts" to "accounts" this code works.

I tried changing the DestinationTableName as advised in this thread and received the following error

aseBulkCopy.DestinationTableName ="tempdb.dbo.#accounts";

Generates the following exception :

tempdb.dbo.#accounts00000510021882346 not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).

I do realise this is a super old thread.. But please advise what else I could try.

[Former Member]

When I change the line

blk.DestinationTableName = tableName;

to

blk.DestinationTableName = "XXXX";

I get the same error, so are there rules about how the temp table is named when using WriteToServer? The value of tableName is what I was using for the direct INSERT and SELECT COUNT(1) queries so I expected it to be correct and, as I said, I have tried it with & without the owner.

[dawn_kim]

Hi Dave,

I remember having an issue with this issue CR 665506 Setting DestinationTableName as catalog..tablename fails with syntax error

This was fixed in SDK 15.7 ESD#5 or later.

You can try these workarounds with the older driver and see if they work.

- Set the DestinationTableName to just the table name

- Add conString2 to the code, using same ASE, but set Database to the destination database

- On execution the code will create the connection to the destination database and bulk load will succeed.

Thanks,
Dawn Kim

[Former Member]

Dawn,

Thanks for your reply. I have a few questions as a result:

1.  Where can I get the SDK 15.7 ESD#5 ?

2.   Was the fix applied to any version of the 16.0 SDK?

3.  "Set the DestinationTableName to just the table name"  I tried this but it made no difference,

4.  "Add conString2 to the code, using same ASE," I don't understand what you mean. There is no overload of the AseBulkCopy constructor that takes a parameter called conString2; nor is there an overload of WriteToServer that takes such a parm.

Thanks

Dave

[Former Member]

1.  Where can I get the SDK 15.7 ESD#5 ?

There's a link here

I'd suggest not suing this version - So for a minimum of SP120 - I'm currently using SP122 which is pretty good.

[Former Member]

Thanks Mike.

Did you mean in the post "Ryan Hansen 05-Jun-2014 15:36 (in response to H. Splinter)"?

If so that process leads me to a SAP Service Marketplace login prompt. I don't have a SAP account and I don't think I can get one as my firm doesn't have a contract with SAP (so, no "Customer or Installation Number ⁽¹⁾ *" to complete in the SAP Service Marketplace registration). We write software for clients that are SAP customers.

The only SDK I have been able to get hold of came with the Sybase install of 16.0, which was the only version I get when I go to https://global.sap.com/campaign/ne/sybase/ase_developer_edition_download/index.epx to download Sybase Developer edition.

Am I on the right track? If so is there a way to get round not being a direct SAP customer?

Thanks

[former_member185199]

Hello,

i am getting a little confusesd by your code, where do you adress the "temp table" ?

i hope the following ist still true because i havent used ASE for a while:

In ASE a temp table is any table prefixed with "#" and is stored in tempdb.

The name of this table follows the rules for normal tablenames( prefixed # ) , but is internally changed to avoid problems with other processes using the same tablename. Nevertheless the namechange ist transparent in your session of isql or whatever.

some more information on temptables you can get from Rob Verschoor:

Reading temporary tables that aren't yours

cheers

dj

[Former Member]

I'm afraid I can't help there - I'm a developer so don't have access to the SDK's.

I have to through our DBA's which isn't ideal - so I have a lot of SDK's that I've tested.

Maybe things have changed and its easy to download the SDK's now ?

If not, perhaps SAP could make them more readily available.

[Former Member]

BTW, We do have bcp working into tempdb's but its not something I'd recommend if you have multiple tempdb's.

I think we have to specify the tempdb we were using - db_name(tempdb_id())

Personally, we're moving away from using bcp table in tempdb.

[Former Member]

HI,

I am calling the method with the name of the temp table that has already been created and populated,

eg:

BulkCopyInsertIntoTable("#DL_123");

[former_member185199]

Hello Dave

Created and popuplated in  the SAME SESSION ?

Otherwise you will not use the same object!

Maybe you should follow Mikes advise:

and trying to avoid temptables here.

Regards

dj

[Former Member]

Hi,
Yes, it's in the same session.

[Former Member]

Did you try doing a "use <tempdb> " first ?

Are you using multiple tempdb ? select tempdb_id() ?

[Former Member]

I was not sure how I would go about sending "use tempdb" to the server before calling AseBulkCopy.WriteToServer. However I have sent it through using AseCommand.ExecuteNonQuery and it did not alter the result of calling AseBulkCopy.WriteToServer.

I am only using a single tempdb, the default one.

I was able to create, insert, select and drop the temp table using a connection string which specified my application db (ie, not tempdb). For the call to AseBulkCopy.WriteToServer I have tried using both this connection string and one which contains ";Initial Catalog=tempdb" instead of the app's DB. No difference.

[Former Member]

I've checked our code and we have to set the Database on the connection string to be the tempdb that is being used.

I think the code would be (I'm not a C# coder)

cmd = AseCommand("use <whatever-tempdb-is-default>")

cmd.ExecuteNonQuery();

[Former Member]

Yes, that's what I tried.

[Former Member]

Are you using multiple tempdb's ? What does select tempdb_id() give you ?

[Former Member]

I am only using a single tempdb, the default one.

What does select tempdb_id() give you ?

Result: 2