You can translate the question and the replies:

Cache bulk load onto sql server using bcp is failing with bcp error

I'm trying to do a bulkload cache, and facing the below error. If run manually the bcp command we can put a file to write the errors during execution. But since this is triggered by Denodo how do catch the errors during bcp execution. Below is the error message that I'm getting. CACHE PROCESS( cacheMode = PRELOAD bulkLoad = true cacheException = Error executing bcp command numOfReceivedRows = 9617577 numOfInsertedRows = 9617577 sqlViewName = C_TABLEA7973580659255336306938657486974027058853517537454 ) Data from Error logs Timestamp 2017/09/06 05:46:18.146 Error Priority ERROR Category com.denodo.vdb.util.tablemanagement.sql.insertion.DFInsertWorker Nested Diagnostic Context [] Message Error closing resource: Error executing bcp command Error Trace com.denodo.vdb.util.tablemanagement.TableManagerException: Error executing bcp command com.denodo.vdb.util.tablemanagement.TableManagerException: Error executing bcp command at com.denodo.vdb.util.tablemanagement.sql.insertion.h.a.onBlockFinish(Unknown Source) at com.denodo.vdb.util.tablemanagement.sql.insertion.DFInsertWorker.b3(Unknown Source) at com.denodo.vdb.util.tablemanagement.sql.insertion.DFInsertWorker.doRun(Unknown Source) at com.denodo.vdb.util.tablemanagement.sql.insertion.AbstractInsertionWorker.run(Unknown Source) at com.denodo.vdb.cache.jdbc.ViewDataStorer.run(Unknown Source) at com.denodo.vdb.engine.thread.g.a(Unknown Source) at com.denodo.vdb.engine.thread.ReusableThread.run(Unknown Source)
user
06-09-2017 06:57:53 -0400

4 Answers

Hi, In this case, I was able to load the cache configured with ‘Use Bulk Load API’ option in the Microsoft SQL Server by doing the following steps: 1. Click on the 'Administration' > 'Server Configuration'. 1. Under the 'Cache' > 'Connection' tab, configure as below: * Database Adapter : Microsoft SQL Server <VERSION> (MS driver) * Database URI: jdbc:jtds:sqlserver://<HOST>:<PORT>/<DATABASE> * Authentication: Use login and password(Make sure to provide the correct credentials) 1. Under the ‘Read & Write’ tab, Check on ‘Use Bulk Data Load APIs’ option and browse the ‘Bcp executable location’ path as below: * <SQL_SERVER_HOME>/Client SDK/ODBC/130/Tools/Binn/bcp.exe 1. Click ‘Ok’. After doing the above steps, I was able to load the cache with Bulk data load API option successfully. You can have a look at the sections [Configuring the Cache](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/server_administration_-_configuring_the_server/configuring_the_cache/configuring_the_cache#configuring-the-cache) and [SQL Server](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/bulk_data_load/sql_server#sql-server) of the Virtual DataPort Administration Guide to know more information. Hope this helps!!
Denodo Team
07-09-2017 09:27:02 -0400
I have done all the cache configuration setup properly and then triggered the cache pre load and facing the issue. May be I have to give you more details in specific to this error. Denodo application server name: server1 Cache DB server name: server2 ( SQL Server ) Both of them are in same network and server1 does not have any SQL instance on it. The bcp executable file location that I have mentioned is residing on server2 where we have cache db setup on sql instance. UserID that is triggering the pre load cache on Denodo has all necessary access onto server2.Do not have any access issues. When we run bcp utility through command prompt we have an option to write errors during execution into a file. Do we have any such option on Denodo to write the errors of bcp execution ? Without bulk load api cache pre loads wre working fine in the same setup, but we now have requirement to cache huge datasets.
user
08-09-2017 06:52:43 -0400
Denodo Team, Any suggestions on this ?
user
14-09-2017 06:51:10 -0400
Hi, For this scenario, I would do the following checks: * To use the SQL Server API to perform bulk data loads, it is needed to install the SQL Server client bcp on the host where the Virtual DataPort server runs. * After this, enable the “Use bulk data load API” on the “Read & Write” tab of the cache data source, enter the path of bcp executable file in the ‘Bcp executable location’ box. For more information have a look at the section [SQL Server](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/bulk_data_load/sql_server) of Virtual DataPort Administration Guide. Hope this helps!
Denodo Team
20-09-2017 03:42:45 -0400
You must sign in to add an answer. If you do not have an account, you can register here