Anaplan - Export via jdbc

Highlighted
New Contributor

Anaplan - Export via jdbc

Hi Team , 

 

As i am planning to Export data into sqlserver table i am getting the below error can any one please suggest me whats went wrong . 

 

Export Code below 

---------------------------------

 

@Echo off
rem For Windows OS : This example explains the usage of basic Authentication with Anaplan Connect.
rem It also loads a source text file and runs an Anaplan import into a module.
rem For details of how to configure this script visit https://help.anaplan.com/anapedia/Content/Downloads/DL_Downloads.html

set AnaplanUser="Username:Password"
set WorkspaceId="My Workspace"
set ModelId="My Model"

set ServiceUrl="https://api.anaplan.com"
set AuthUrl="https://auth.anaplan.com"

rem *** to continue the command to the next line put ^ at the end of the line. No spaces after the ^.

set Operation=-maxretrycount 5 -retrytimeout 30 ^
-export "Export Action" -execute -jdbcproperties "C:\jdbc_insert_sqlserver.properties"

rem *** End of settings - Do not edit below this line ***

setlocal enableextensions enabledelayedexpansion || exit /b 1
cd %~dp0
if not %AnaplanUser% == "" set Credentials=-user %AnaplanUser%
set Command=.\AnaplanClient.bat -service %ServiceUrl% -auth %AuthUrl% -workspace %WorkspaceId% -model %ModelId% %Credentials% %Operation%
@Echo %Command%
cmd /c %Command%
pause

 

JDBC Insert property code 

--------------------------------------

 

# JDBC Connection string (Oracle, Mysql, H2, etc.)
jdbc.connect.url=jdbc:sqlserver://servername:port;databaseName=dbname

# JDBC login username
jdbc.username=user

#JDBC login password
jdbc.password=password

# Use INSERT queries to export your data into database
# Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't
# duplicate an existing record, then SQL inserts it as usual. If the record is a
# duplicate, then the IGNORE keyword tells SQL to discard it silently without generating an error.
# Use the REPLACE command rather than the INSERT command. If the record is new, it is inserted
# just as with INSERT. If it is a duplicate, the new record replaces the old one.
# SQL query example
jdbc.query=insert into anaplan.dbo.py_sales (REGION, PY_SALES) values (?,?)

# -----NEW PARAMETER----
# JDBC parameters for parametrized SQL write in {jdbc.query} above.
# NOTE: The number of parameters must be equal to the number of parameters provided in the {jdbc.query} above.
# The parameter names must match column names from Anaplan export action.
# The JDBC parameters below will be mapped starting from left to right to the '?' in {jdbc.query} above.
# In this example, the exported data has columns names column1 and column2. These will be mapped
# starting from left to right to each of the '?' above.
jdbc.params="Sales Region","PY_Sales"

 

the above values are not original just i changed . please find the error which i am getting it  . please help me 

 

Feign: status 500 reading AnaplanAPI#getExport(String,String,String); content:
{"status":{"code":500,"message":"Internal Server Error"},"path":"/2/0/workspaces/8a81b09b5ef8a64201/models/B65FB1B0415/exports/116000000565","timestamp":"2020-05-07T12:34:02.722446Z

 

13 REPLIES 13
Highlighted
Contributor

Re: Anaplan - Export via jdbc

Hi,

 

this is happening because of the last release... all my interfaces are broken (through python, mulesoft, and the api to power bi)

Highlighted
New Contributor

Re: Anaplan - Export via jdbc

what is the solution to over come this issue
Highlighted
Community Boss

Re: Anaplan - Export via jdbc

Are there any rows in the view you are exporting? There is a long-standing bug that is triggered when there are no rows.

Highlighted
New Contributor

Re: Anaplan - Export via jdbc

yes we have around 5 to 6 rows are there in exporting file
Highlighted
Community Boss

Re: Anaplan - Export via jdbc

So you ran the same export as the same user etc but to a file and there were data (not header) rows?

Are there any filters, sorting, and/or nesting on the rows axis? Does it change if you remove one?

Highlighted
Contributor

Re: Anaplan - Export via jdbc

Hi,

also check if you have duplicated values:

ABerenguela_0-1589203123814.png

 

try the insert with IGNORE

ABerenguela_1-1589203186180.png

 

Regards

 

 

Highlighted
New Contributor

Re: Anaplan - Export via jdbc

i have change the export file now i am getting different error message please help me .

2020-05-11 19:14:42 INFO 18268 |-- Initializing Service...
2020-05-11 19:14:42 INFO 18268 |-- Authenticating via Basic...
2020-05-11 19:14:48 INFO 18268 |-- = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
2020-05-11 19:14:48 INFO 18268 |-- Running Export: EXPORT: Project Impact Assessment - Export (id=116000000563)
2020-05-11 19:14:51 INFO 18268 |-- Run status: Complete. (100.0%)
2020-05-11 19:14:51 INFO 18268 |-- -----------------------------------------
2020-05-11 19:14:51 INFO 18268 |-- <<< The operation was successful >>> 😃
2020-05-11 19:14:51 INFO 18268 |-- - - - - - - - - - - - - - - - - - - - - -
2020-05-11 19:14:51 INFO 18268 |--
2020-05-11 19:14:51 INFO 18268 |-- fileSize - 3013
2020-05-11 19:14:51 INFO 18268 |-- serverAlert - Completed successfully!
2020-05-11 19:14:51 INFO 18268 |-- exportName - EXPORT: Project Impact Assessment - Export
2020-05-11 19:14:56 INFO 18268 |-- Export EXPORT: Project Impact Assessment - Export to database started successfully
2020-05-11 19:14:56 INFO 18268 |-- Attempt 1 : Could not connect to the database! Will retry in 30 seconds
2020-05-11 19:15:26 INFO 18268 |-- Attempt 2 : Could not connect to the database! Will retry in 45 seconds
2020-05-11 19:16:11 INFO 18268 |-- Attempt 3 : Could not connect to the database! Will retry in 60 seconds
2020-05-11 19:17:11 INFO 18268 |-- Attempt 4 : Could not connect to the database! Will retry in 60 seconds
Highlighted
Contributor

Re: Anaplan - Export via jdbc

The Anaplan export ran correctly, you need to fix the connection with the Database 

2020-05-11 19:14:56 INFO 18268 |-- Attempt 1 : Could not connect to the database! Will retry in 30 seconds

first need to be sure that you can reach it, try an ODBC to check. Also check the access of the SQL user, need to have "connect" and "insert" rights 

Regards

Highlighted
New Contributor

Re: Anaplan - Export via jdbc

Hi ABerenguela,
Thanks for providing the reply , the sql user which i am using having the permission to insert the records into Table . To test this one i have inserted a record manually in table . i dont know what i have missed it .
jdbc.connect.url=jdbc:sqlserver://databasename1433;databaseName=Staging
is this statement is correct .
if possible can you share me the property file once