Skip to main content

[Collect / DataGarage] Error: Incorrect syntax near ‘,’

Rosemary Schatz
  • Updated

Product(s):

Collect - all versions prior to 7.0.2

DataGarage - all 5.x versions

Issue:

When refreshing a table in Collect/DataGarage, an error is returned that mentions Incorrect syntax near ','.

An example of a full error (minus the Stack Trace):

Error In Download:Run - External Process Exception in ExecutePackage Class in D:\Program Files (x86)\BOA\DSP\Web\UserArea\00be69ce-b45a-4704-ae2f-c3378e71baa3\Processes\cranport\cranportplugin.dll. Error Message: The service was unable to evaluate the non-query statement. Incorrect syntax near ','.Incorrect syntax near ','.The service was unable to evaluate the non-query statement.Incorrect syntax near ','.Incorrect syntax near ','.Job Task Parameters:Key.CPPackageID = 3F1938FF-1560-4CED-A2A7-54EDD485105C

Cause(s):

This is caused by a RAW datatype in the source table being converted to an invalid datatype in the target table. Often this is related to a GUID field.

Resolution:

In DSP:

  1. Go to the Collect > Targets page and select your target.
  2. In the Target Source pane, note the Connection Type value of your source.
  3. Go to the Collect > Administrative > Connection Types page.
  4. Click the Sources icon.
  5. Select the record that matches the Connection Type found in Step 2.
  6. Click the Data Type Conversions icon.
  7. Double-click the record with a Data Type value of 'RAW'
  8. Click Edit and make the following changes:
    1. New Data Type Format - VARBINARY(50)
    2. New Data Type - VARBINARY
    3. New Data Length - 50
  9. If you don't want to download any RAW datatypes then you can just make the following change:
    1. Process Flag – Ignore

In CranSoft 5.x:

  1. Go to the DataGarage > Target (Setup) page and select your target.
  2. Click the Sources button.
  3. Note the Connection Type value of your source.
  4. Go to the DataGarage > Configuration > Connection Types page.
  5. Select the record that matches the Connection Type found in step 3.
  6. Click the Data Type Conversions button.
  7. Double-click the record with a Data Type value of 'RAW'
  8. Click Edit and make the following changes:
    1. New Data Type Format - VARBINARY(50)
    2. New Data Type - VARBINARY
    3. New Data Length - 50
  9. If you don't want to download any RAW datatypes then you can just make the following change:
    1. Process Flag – Ignore

After the datatype conversion has been changed:

  1. Go to the vertical of the Source record in Collect/DataGarage.
  2. Go to the Advanced Settings tab and make sure Delete Target Table On Build is checked on.As an alternative to the previous 2 steps, you can just manually drop the table in the target database.
  3. Go to the Table record in Collect/DataGarage and rebuild the package.

 

Related articles