Solved

Hi, We’re using CData ADO.NET Provider for Snowflake 2019 [Build 7362] and we’re facing a problem working with UNPIVOT. We’re targeting the Snowflake_Sample_Data with this command:SELECT CC_CALL_CENTER_SK, CC_CALL_CENTER_ID, CC_CLOSED_DATE_SK, CC_OPEN_DATE_SK, Attribute, ValueFROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CALL_CENTERUNPIVOT ( Value FOR Attribute IN ( CC_NAME, CC_CLASS, CC_HOURS, CC_MANAGER, CC_MKT_CLASS, CC_MKT_DESC, CC_MARKET_MANAGER )) AS unpivoted_data;The query is working fine on Snowflake instance but not with CDATA Snowflake driver. We’re getting the error below:Malformed SQL Statement: Expected token ')' but found Identifier with value 'FOR' instead Is UNPIVOT syntax supported for this version of CDATA?

Hi all,Our team is using CData Sync to extract and load data from SQL Server to Snowflake. The source MSSQL table is CDC-enabled, and we have created a CData Job with Change Data Capture enabled. We started testing the task with smaller tables (containing 100K - 300K records), and the task was successful without any issues. However, when trying with larger tables (containing 10M - 35M records), we faced failures every time.I have attached the error output for your reference. Has anyone come across this scenario? Any replies or help would be much appreciated. Thanks in advance.Error output: "2024-11-14T02:25:19.555+0000 2 [32|Q-Id] Failed to cache row [501097] The query was canceled."We tried running the same task with a few manipulations, like reducing the source table records to 1M over 10M but had no luck. We are not facing any other issues besides "Failed to cache row." What does caching mean in a CData task? Does this have something to do with the AWS AMIs? Thanks heaps,S.

Hi Team, kindly help me on Jira custom fields are not visible in Cdata connector. ThanksChari

How to remove the last line in flat file using Arc script ??The below script is to add the “END” on the last line but I need to remove the END which is present in the last line of a Flat-file??<arc:set attr="file.file" value="[filepath]" /><arc:call op="fileRead" item="file">  <arc:set attr="tmp.originalBody">[file.file:data]</arc:set></arc:call><arc:unset attr="output.data">[tmp.originalBody]END</arc:set><arc:unset attr="output.filename" value="940_[now()]" /><arc:push item="output" />

how to remove the first five lines in flat file using Arc script 

Hey All,I am trying to adhere to the principle of least permissions. I created a SQL Server Authenticated Login for cdata to use as a service account to SQL Server. I’m not sure what permissions it needs at the database level though? I wasn’t able to find any documentation on the site about minimum permissions cdata sync service account for SQL Server would need.I allow the cdata sync service account to drop and create tables. My guess is that I only need to add the cdata sync service account user to the following database level roles in my data lake.ddl_admindb_datareaderdb_datawriterWhat I am not sure about is whether or not Cdata Sync Service Account needs to have permissions to create Schemas.Thanks

I have a flow where I need to create two csv files and email them using the Email Send connector.I’ve done these many times and it’s super easy...until now.  I need to create and email the first CSV then set a delay (Schedule Connector?) and use a tracked header value from the first part of the flow to create and email another CSV with data from a stored procedure.In other words, once the CSV hits the Input folder of the first Email Notify it sends the email and stops.  Nothing ever shows up in the Output of the Email Notify.I suppose I could use the After Send event to trigger the Schedule, but is their another way to send the first email and continue to follow the flow and send the next email.  The key to this is that I have a tracked header value that must remain through the entire flow, as it’s my stored procedures incoming parameter.Appreciate any input!

I just noticed the EOL announcement for  CData DBAmp versions prior to v22 on this page.  My organization is still on version 4.1.8. I’m interpreting this announcement to mean that version 4.1.8 will continue to work as long as we have a valid license key.  So it will still be running on say, 1/31/2025.  Is this correct? I keep getting surprised by these announcements.  Besides coming to the CDATA site and browsing around, how can I stay up to date on important news?I WILL be talking to my manager about upgrading to the most current version ASAP, but we will need time for lots of testing.

What are the suggestion in how to load tables over 15 millions rows using CData Sync? The data source is ServiceNow and the destination is SQL Server. I changed the sync.exe.config to<?xml version="1.0" encoding="utf-8" ?><configuration>  <startup>    <supportedRuntime version="v4.0" />   </startup>  <!-- You can add some Java JVM parameters here. This is for Java application only.   <appSettings>    <add key="JAVA_OPTS" value="" />  </appSettings>  --><appSettings>     <add key="JAVA_OPTS" value="-Xms2g -Xmx50g"/> </appSettings>    </configuration>Also, I changed the timeout of the jobs to 6200.

Is it possible to create multiple schedules for a single Sync job?The use case here is that I'd like to run a job only once per day on some days of a month but then multiple times per day for some other days.One idea that comes to my mind is that I can create two jobs with different schedules, but the problem here is that if a destination table is incrementally synced (which is required for large tables), two jobs will not be able to coordinate.

How do I use the REST API to enable the automatic job retry option? I’m not seeing that option in the API REST documentation.I am using CData Sync cross platform.

I created a Power BI data model using the QuickBooks Online connector and successfully built some basic reports. However, I noticed that certain transactions are missing from the data. I queried all available tables but couldn’t locate these transactions. Interestingly, when I used the 'TransactionsList' stored procedure, I was able to retrieve all transactions, including the missing ones.I suspect that some transaction types, such as Payroll Checks, may not be accessible through the CData connector or Intuit Accounting API. Has anyone else encountered this issue? Is there a known limitation or a workaround to include these transaction types in the data model?

I need to copy specific data from the SAP ECC 6.0 tables listed below and paste it into the matching SAP S/4 HANA tables. ADMI_FILES ADMI_RUN  ADMI_STATS ADMI_VARIAI attempted to copy the data manually, and it worked. However, could you please advise if CData JDBC Driver could be used to accomplish this?

Hi, I’m confused on how to select the dynamic fields. When I look in the driver ui, I can see the dynamic fields like “createdbyname” which are populated based on the values in “createdby” - but if I try to query using `select createdbyname from account` I get an error: “Query failed: [0x80060888] Could not find a property named ‘createdbyname’ on type ‘Microsoft.Dynamics.CRM.account’” Is there something special I need to do to be able to do this? In TSQL with ssms I can select these fields without issue. Reading through the docs, I don’t see a driver setting that seems like it would enable this feature. Can anyone tell me what I’m missing? 

I’m using an Excel connector to pass a spreadsheet into a Script connector for further processing.The Excel input contains columns of the form:  A: B CIn the Script connector, I try to reference it in an xpath like this:  xpath([A:_B_C])But I get the “attribute does not exist” error. I need to deal with or escape that colon character somehow, I just haven’t been able to find where in the docs how to do that yet.Any suggestions?Thank you!

I’ve been using _log.info as a way to debug scripts. This makes log entries I’m wondering if it’s possible to insert log entries in the individual message logs? It would be more convenient to do it that way than going over to the application log every time I want to inspect the data. 

Hi, Where can we find the Changelog of the different Arc versions?

I’m trying to do a transformation after a dataload to a SQL server.The query used is: (the query is tested and works on the SQL server)MERGE INTO [S_Material_Data] AS tgtUSING ( SELECT HASHBYTES('SHA2_256', mat.MatNr) AS HSH_Material, CR_date AS Creation_Date, CASE WHEN mat.CH_Date IS NOT NULL THEN mat.CH_Date ELSE mat.CR_date END AS Changed_On, mat.Mat_Type AS Material_Type, mat.Mat_Grp AS Material_Group, mat.PRD_Hier AS Product_Hierarchy, Mat_Disc AS Material_Description, Mat_DiscU AS Material_DescriptionUp FROM Materials mat LEFT OUTER JOIN Descriptions dsc ON mat.MatNr = dsc.MatNr AND dsc.Lang = 'E' ) AS src ON tgt.HSH_Material = src.HSH_MaterialWHEN MATCHED THEN UPDATE SET tgt.Creation_Date = src.Creation_Date, tgt.Changed_On = src.Changed_On, tgt.Material_Type = src.Material_Type, tgt.Material_Group = src.Material_Group, tgt.Product_Hierarchy = src.Product_Hierarchy, tgt.Material_Description = src.Material_Description, tgt.Material_DescriptionUp = src.Materi

Hi, Original question:When creating a simple webhook and calling it using curl, the webhook returns an error about “application/x-www-form-urlencoded”.This is resolved by setting the correct Content-Type header to either application/json or application/xml as configured in the Connector. Follow-up question (am not able to update the title of this post...): Can the webhook return to the requestor immediatly with a 200 upon creating the Output message, so that the requester doesnt have to wait for the full flow behind the wehbook to complete?

I have a CData job which pulls the data from Jira incrementally. In todays execution one of the table out of 21 failed with following error. [3] [500] Could not execute the specified command: Failed to cache row [220920] Object reference not set to an instance of an object. StackTrace: at aso230l.nT.Next() at aso230l.mH.Next() at aso230l.mb.Next() at aso230l.mY.Next() at aso230l.mQ.Next() at aso230l.Kz.q(DbCommand , DataTable , IResultSet , ColumnInfo[] , Int32[] , jsb ) Just wanted to understand what is the reason for the failure and also wanted to understand how CData job syncs the data incrementally, if this table succeed tomorrow will it bring in all the changes from the last successful pull. Considering that we have a failure today.

Can anyone explain to me the difference in the two timeout settings in the SQL Server db connector advanced tab? 

Using the ODBC driver, I am trying to query ChoiceLabels - this simple query works in SSMS but not using the ODBC driver in my reporting application: select * from choicelabels The odbc driver returns:A table could not be found that matches choicelabels.Is there something else I need to do to query this table? I need to join it against an entity table in order to decode the choice value.   

When specifying the name of a table in the Tables connection property (or the name of a view in the Views property), you may receive the following error when querying a table in your Oracle connection (even if the connection was successfully tested):The catalog needs to be specified in the TABLE or VIEW property.However, Oracle does not make use of catalogs. With this in mind, what should be done to work around the error?

Is it possible to create DBAmp linked server for two salesforce instance in single SQL server with latest DBAmp setup? Seems like there is no option to configure two product key in DBAmp Config2. There was option to set it up in DBAmp v_59109. 

is DBAmp v_50109 supported on SQL Server 2022 with Windows OS 2022? The linked server is not working on it. Same is working fine on SQL Server 2016.