ODBC ASE with PowerBI

Discussion of open issues, suggestions and bugs regarding usage of ODBC Drivers
Post Reply
captzulu
Posts: 4
Joined: Thu 21 Jun 2018 15:34

ODBC ASE with PowerBI

Post by captzulu » Thu 21 Jun 2018 15:42

I can't seem to load the SAP table 'VBAP' in power BI. I can load many other tables in the same Schema but some inexplicably give me this error :

Code: Select all

DataSource.Error : ODBC: ERROR [HY000] [Devart][ODBC][Adaptive Server Enterprise]Incorrect syntax near '/'.
This is the Query code in PowerBI, the same as with any other table :

Code: Select all

let
    Source = Odbc.DataSource("dsn=EQ1", [HierarchicalNavigation=true]),
    EQ1_Database = Source{[Name="EQ1",Kind="Database"]}[Data],
    SAPSR3_Schema = EQ1_Database{[Name="SAPSR3",Kind="Schema"]}[Data],
    VBAP_Table = SAPSR3_Schema{[Name="VBAP",Kind="Table"]}[Data]
in
    VBAP_Table
There is no slash in this query and so i have no idea where to even begin...

Stellar
Devart Team
Posts: 122
Joined: Tue 03 Oct 2017 11:00

Re: ODBC ASE with PowerBI

Post by Stellar » Fri 22 Jun 2018 13:51

In order for us to analyze the issue, please provide the script for creating the table on which the specified issue is reproduced.

captzulu
Posts: 4
Joined: Thu 21 Jun 2018 15:34

Re: ODBC ASE with PowerBI

Post by captzulu » Fri 22 Jun 2018 14:51

Here is the script. Thx for the quick answer!

Code: Select all

CREATE TABLE EQ1.SAPSR3.VBAP (
	MANDT varchar(9) DEFAULT '000' NOT NULL,
	VBELN varchar(30) DEFAULT ' ' NOT NULL,
	POSNR varchar(18) DEFAULT '000000' NOT NULL,
	MATNR varchar(54) DEFAULT ' ' NOT NULL,
	MATWA varchar(54) DEFAULT ' ' NOT NULL,
	PMATN varchar(54) DEFAULT ' ' NOT NULL,
	CHARG varchar(30) DEFAULT ' ' NOT NULL,
	MATKL varchar(27) DEFAULT ' ' NOT NULL,
	ARKTX varchar(120) DEFAULT ' ' NOT NULL,
	PSTYV varchar(12) DEFAULT ' ' NOT NULL,
	POSAR varchar(3) DEFAULT ' ' NOT NULL,
	LFREL varchar(3) DEFAULT ' ' NOT NULL,
	FKREL varchar(3) DEFAULT ' ' NOT NULL,
	UEPOS varchar(18) DEFAULT '000000' NOT NULL,
	GRPOS varchar(18) DEFAULT '000000' NOT NULL,
	ABGRU varchar(6) DEFAULT ' ' NOT NULL,
	PRODH varchar(54) DEFAULT ' ' NOT NULL,
	ZWERT decimal(13,2) DEFAULT 0 NOT NULL,
	ZMENG decimal(13,3) DEFAULT 0 NOT NULL,
	ZIEME varchar(9) DEFAULT ' ' NOT NULL,
	UMZIZ decimal(5,0) DEFAULT 0 NOT NULL,
	UMZIN decimal(5,0) DEFAULT 0 NOT NULL,
	MEINS varchar(9) DEFAULT ' ' NOT NULL,
	SMENG decimal(13,3) DEFAULT 0 NOT NULL,
	ABLFZ decimal(13,3) DEFAULT 0 NOT NULL,
	ABDAT varchar(24) DEFAULT '00000000' NOT NULL,
	ABSFZ decimal(13,3) DEFAULT 0 NOT NULL,
	POSEX varchar(18) DEFAULT ' ' NOT NULL,
	KDMAT varchar(105) DEFAULT ' ' NOT NULL,
	KBVER decimal(3,0) DEFAULT 0 NOT NULL,
	KEVER decimal(3,0) DEFAULT 0 NOT NULL,
	VKGRU varchar(9) DEFAULT ' ' NOT NULL,
	VKAUS varchar(9) DEFAULT ' ' NOT NULL,
	GRKOR varchar(9) DEFAULT '000' NOT NULL,
	FMENG varchar(3) DEFAULT ' ' NOT NULL,
	UEBTK varchar(3) DEFAULT ' ' NOT NULL,
	UEBTO decimal(3,1) DEFAULT 0 NOT NULL,
	UNTTO decimal(3,1) DEFAULT 0 NOT NULL,
	FAKSP varchar(6) DEFAULT ' ' NOT NULL,
	ATPKZ varchar(3) DEFAULT ' ' NOT NULL,
	RKFKF varchar(3) DEFAULT ' ' NOT NULL,
	SPART varchar(6) DEFAULT ' ' NOT NULL,
	GSBER varchar(12) DEFAULT ' ' NOT NULL,
	NETWR decimal(15,2) DEFAULT 0 NOT NULL,
	WAERK varchar(15) DEFAULT ' ' NOT NULL,
	ANTLF decimal(1,0) DEFAULT 0 NOT NULL,
	KZTLF varchar(3) DEFAULT ' ' NOT NULL,
	CHSPL varchar(3) DEFAULT ' ' NOT NULL,
	KWMENG decimal(15,3) DEFAULT 0 NOT NULL,
	LSMENG decimal(15,3) DEFAULT 0 NOT NULL,
	KBMENG decimal(15,3) DEFAULT 0 NOT NULL,
	KLMENG decimal(15,3) DEFAULT 0 NOT NULL,
	VRKME varchar(9) DEFAULT ' ' NOT NULL,
	UMVKZ decimal(5,0) DEFAULT 0 NOT NULL,
	UMVKN decimal(5,0) DEFAULT 0 NOT NULL,
	BRGEW decimal(15,3) DEFAULT 0 NOT NULL,
	NTGEW decimal(15,3) DEFAULT 0 NOT NULL,
	GEWEI varchar(9) DEFAULT ' ' NOT NULL,
	VOLUM decimal(15,3) DEFAULT 0 NOT NULL,
	VOLEH varchar(9) DEFAULT ' ' NOT NULL,
	VBELV varchar(30) DEFAULT ' ' NOT NULL,
	POSNV varchar(18) DEFAULT '000000' NOT NULL,
	VGBEL varchar(30) DEFAULT ' ' NOT NULL,
	VGPOS varchar(18) DEFAULT '000000' NOT NULL,
	VOREF varchar(3) DEFAULT ' ' NOT NULL,
	UPFLU varchar(3) DEFAULT ' ' NOT NULL,
	ERLRE varchar(3) DEFAULT ' ' NOT NULL,
	LPRIO varchar(6) DEFAULT '00' NOT NULL,
	WERKS varchar(12) DEFAULT ' ' NOT NULL,
	LGORT varchar(12) DEFAULT ' ' NOT NULL,
	VSTEL varchar(12) DEFAULT ' ' NOT NULL,
	ROUTE varchar(18) DEFAULT ' ' NOT NULL,
	STKEY varchar(3) DEFAULT ' ' NOT NULL,
	STDAT varchar(24) DEFAULT '00000000' NOT NULL,
	STLNR varchar(24) DEFAULT ' ' NOT NULL,
	STPOS decimal(5,0) DEFAULT 0 NOT NULL,
	AWAHR varchar(9) DEFAULT '000' NOT NULL,
	ERDAT varchar(24) DEFAULT '00000000' NOT NULL,
	ERNAM varchar(36) DEFAULT ' ' NOT NULL,
	ERZET varchar(18) DEFAULT '000000' NOT NULL,
	TAXM1 varchar(3) DEFAULT ' ' NOT NULL,
	TAXM2 varchar(3) DEFAULT ' ' NOT NULL,
	TAXM3 varchar(3) DEFAULT ' ' NOT NULL,
	TAXM4 varchar(3) DEFAULT ' ' NOT NULL,
	TAXM5 varchar(3) DEFAULT ' ' NOT NULL,
	TAXM6 varchar(3) DEFAULT ' ' NOT NULL,
	TAXM7 varchar(3) DEFAULT ' ' NOT NULL,
	TAXM8 varchar(3) DEFAULT ' ' NOT NULL,
	TAXM9 varchar(3) DEFAULT ' ' NOT NULL,
	VBEAF decimal(5,2) DEFAULT 0 NOT NULL,
	VBEAV decimal(5,2) DEFAULT 0 NOT NULL,
	VGREF varchar(3) DEFAULT ' ' NOT NULL,
	NETPR decimal(11,2) DEFAULT 0 NOT NULL,
	KPEIN decimal(5,0) DEFAULT 0 NOT NULL,
	KMEIN varchar(9) DEFAULT ' ' NOT NULL,
	SHKZG varchar(3) DEFAULT ' ' NOT NULL,
	SKTOF varchar(3) DEFAULT ' ' NOT NULL,
	MTVFP varchar(6) DEFAULT ' ' NOT NULL,
	SUMBD varchar(3) DEFAULT ' ' NOT NULL,
	KONDM varchar(6) DEFAULT ' ' NOT NULL,
	KTGRM varchar(6) DEFAULT ' ' NOT NULL,
	BONUS varchar(6) DEFAULT ' ' NOT NULL,
	PROVG varchar(6) DEFAULT ' ' NOT NULL,
	EANNR varchar(39) DEFAULT ' ' NOT NULL,
	PRSOK varchar(3) DEFAULT ' ' NOT NULL,
	BWTAR varchar(30) DEFAULT ' ' NOT NULL,
	BWTEX varchar(3) DEFAULT ' ' NOT NULL,
	XCHPF varchar(3) DEFAULT ' ' NOT NULL,
	XCHAR varchar(3) DEFAULT ' ' NOT NULL,
	LFMNG decimal(13,3) DEFAULT 0 NOT NULL,
	STAFO varchar(18) DEFAULT ' ' NOT NULL,
	WAVWR decimal(13,2) DEFAULT 0 NOT NULL,
	KZWI1 decimal(13,2) DEFAULT 0 NOT NULL,
	KZWI2 decimal(13,2) DEFAULT 0 NOT NULL,
	KZWI3 decimal(13,2) DEFAULT 0 NOT NULL,
	KZWI4 decimal(13,2) DEFAULT 0 NOT NULL,
	KZWI5 decimal(13,2) DEFAULT 0 NOT NULL,
	KZWI6 decimal(13,2) DEFAULT 0 NOT NULL,
	STCUR decimal(9,5) DEFAULT 0 NOT NULL,
	AEDAT varchar(24) DEFAULT '00000000' NOT NULL,
	EAN11 varchar(54) DEFAULT ' ' NOT NULL,
	FIXMG varchar(3) DEFAULT ' ' NOT NULL,
	PRCTR varchar(30) DEFAULT ' ' NOT NULL,
	MVGR1 varchar(9) DEFAULT ' ' NOT NULL,
	MVGR2 varchar(9) DEFAULT ' ' NOT NULL,
	MVGR3 varchar(9) DEFAULT ' ' NOT NULL,
	MVGR4 varchar(9) DEFAULT ' ' NOT NULL,
	MVGR5 varchar(9) DEFAULT ' ' NOT NULL,
	KMPMG decimal(13,3) DEFAULT 0 NOT NULL,
	SUGRD varchar(12) DEFAULT ' ' NOT NULL,
	SOBKZ varchar(3) DEFAULT ' ' NOT NULL,
	VPZUO varchar(3) DEFAULT ' ' NOT NULL,
	PAOBJNR varchar(30) DEFAULT '0000000000' NOT NULL,
	PS_PSP_PNR varchar(24) DEFAULT '00000000' NOT NULL,
	AUFNR varchar(36) DEFAULT ' ' NOT NULL,
	VPMAT varchar(54) DEFAULT ' ' NOT NULL,
	VPWRK varchar(12) DEFAULT ' ' NOT NULL,
	PRBME varchar(9) DEFAULT ' ' NOT NULL,
	UMREF float DEFAULT 0 NOT NULL,
	KNTTP varchar(3) DEFAULT ' ' NOT NULL,
	KZVBR varchar(3) DEFAULT ' ' NOT NULL,
	SERNR varchar(24) DEFAULT ' ' NOT NULL,
	OBJNR varchar(66) DEFAULT ' ' NOT NULL,
	ABGRS varchar(18) DEFAULT ' ' NOT NULL,
	BEDAE varchar(12) DEFAULT ' ' NOT NULL,
	CMPRE decimal(11,2) DEFAULT 0 NOT NULL,
	CMTFG varchar(3) DEFAULT ' ' NOT NULL,
	CMPNT varchar(3) DEFAULT ' ' NOT NULL,
	CMKUA decimal(9,5) DEFAULT 0 NOT NULL,
	CUOBJ varchar(54) DEFAULT '000000000000000000' NOT NULL,
	CUOBJ_CH varchar(54) DEFAULT '000000000000000000' NOT NULL,
	CEPOK varchar(3) DEFAULT ' ' NOT NULL,
	KOUPD varchar(3) DEFAULT ' ' NOT NULL,
	SERAIL varchar(12) DEFAULT ' ' NOT NULL,
	ANZSN int DEFAULT 0 NOT NULL,
	NACHL varchar(3) DEFAULT ' ' NOT NULL,
	MAGRV varchar(12) DEFAULT ' ' NOT NULL,
	MPROK varchar(3) DEFAULT ' ' NOT NULL,
	VGTYP varchar(3) DEFAULT ' ' NOT NULL,
	PROSA varchar(3) DEFAULT ' ' NOT NULL,
	UEPVW varchar(3) DEFAULT ' ' NOT NULL,
	KALNR varchar(36) DEFAULT '000000000000' NOT NULL,
	KLVAR varchar(12) DEFAULT ' ' NOT NULL,
	SPOSN varchar(12) DEFAULT ' ' NOT NULL,
	KOWRR varchar(3) DEFAULT ' ' NOT NULL,
	STADAT varchar(24) DEFAULT '00000000' NOT NULL,
	EXART varchar(6) DEFAULT ' ' NOT NULL,
	PREFE varchar(3) DEFAULT ' ' NOT NULL,
	KNUMH varchar(30) DEFAULT ' ' NOT NULL,
	CLINT varchar(30) DEFAULT '0000000000' NOT NULL,
	CHMVS varchar(9) DEFAULT '000' NOT NULL,
	STLTY varchar(3) DEFAULT ' ' NOT NULL,
	STLKN varchar(24) DEFAULT '00000000' NOT NULL,
	STPOZ varchar(24) DEFAULT '00000000' NOT NULL,
	STMAN varchar(3) DEFAULT ' ' NOT NULL,
	ZSCHL_K varchar(18) DEFAULT ' ' NOT NULL,
	KALSM_K varchar(18) DEFAULT ' ' NOT NULL,
	KALVAR varchar(12) DEFAULT ' ' NOT NULL,
	KOSCH varchar(54) DEFAULT ' ' NOT NULL,
	UPMAT varchar(54) DEFAULT ' ' NOT NULL,
	UKONM varchar(6) DEFAULT ' ' NOT NULL,
	MFRGR varchar(24) DEFAULT ' ' NOT NULL,
	PLAVO varchar(12) DEFAULT ' ' NOT NULL,
	KANNR varchar(105) DEFAULT ' ' NOT NULL,
	CMPRE_FLT float DEFAULT 0 NOT NULL,
	ABFOR varchar(6) DEFAULT ' ' NOT NULL,
	ABGES float DEFAULT 0 NOT NULL,
	J_1BCFOP varchar(30) DEFAULT ' ' NOT NULL,
	J_1BTAXLW1 varchar(9) DEFAULT ' ' NOT NULL,
	J_1BTAXLW2 varchar(9) DEFAULT ' ' NOT NULL,
	J_1BTXSDC varchar(6) DEFAULT ' ' NOT NULL,
	WKTNR varchar(30) DEFAULT ' ' NOT NULL,
	WKTPS varchar(18) DEFAULT '000000' NOT NULL,
	SKOPF varchar(54) DEFAULT ' ' NOT NULL,
	KZBWS varchar(3) DEFAULT ' ' NOT NULL,
	WGRU1 varchar(54) DEFAULT ' ' NOT NULL,
	WGRU2 varchar(54) DEFAULT ' ' NOT NULL,
	KNUMA_PI varchar(30) DEFAULT ' ' NOT NULL,
	KNUMA_AG varchar(30) DEFAULT ' ' NOT NULL,
	KZFME varchar(3) DEFAULT ' ' NOT NULL,
	LSTANR varchar(3) DEFAULT ' ' NOT NULL,
	TECHS varchar(36) DEFAULT ' ' NOT NULL,
	MWSBP decimal(13,2) DEFAULT 0 NOT NULL,
	BERID varchar(30) DEFAULT ' ' NOT NULL,
	PCTRF varchar(30) DEFAULT ' ' NOT NULL,
	LOGSYS_EXT varchar(30) DEFAULT ' ' NOT NULL,
	J_1BTAXLW3 varchar(9) DEFAULT ' ' NOT NULL,
	J_1BTAXLW4 varchar(9) DEFAULT ' ' NOT NULL,
	J_1BTAXLW5 varchar(9) DEFAULT ' ' NOT NULL,
	STOCKLOC varchar(60) DEFAULT ' ' NOT NULL,
	SLOCTYPE varchar(12) DEFAULT ' ' NOT NULL,
	MSR_RET_REASON varchar(9) DEFAULT ' ' NOT NULL,
	MSR_REFUND_CODE varchar(9) DEFAULT ' ' NOT NULL,
	MSR_APPROV_BLOCK varchar(3) DEFAULT ' ' NOT NULL,
	NRAB_KNUMH varchar(30) DEFAULT ' ' NOT NULL,
	TRMRISK_RELEVANT varchar(6) DEFAULT ' ' NOT NULL,
	SGT_RCAT varchar(48) DEFAULT ' ' NOT NULL,
	HANDOVERLOC varchar(30) DEFAULT ' ' NOT NULL,
	HANDOVERDATE varchar(24) DEFAULT '00000000' NOT NULL,
	HANDOVERTIME varchar(18) DEFAULT '000000' NOT NULL,
	TC_AUT_DET varchar(6) DEFAULT ' ' NOT NULL,
	MANUAL_TC_REASON varchar(6) DEFAULT ' ' NOT NULL,
	FISCAL_INCENTIVE varchar(12) DEFAULT ' ' NOT NULL,
	TAX_SUBJECT_ST varchar(3) DEFAULT ' ' NOT NULL,
	FISCAL_INCENTIVE_ID varchar(12) DEFAULT ' ' NOT NULL,
	SPCSTO varchar(6) DEFAULT '00' NOT NULL,
	[/BEV1/SRFUND] varchar(6) DEFAULT ' ' NOT NULL,
	AUFPL_OLC varchar(30) DEFAULT '0000000000' NOT NULL,
	APLZL_OLC varchar(24) DEFAULT '00000000' NOT NULL,
	FERC_IND varchar(12) DEFAULT ' ' NOT NULL,
	KOSTL varchar(30) DEFAULT ' ' NOT NULL,
	FONDS varchar(30) DEFAULT ' ' NOT NULL,
	FISTL varchar(48) DEFAULT ' ' NOT NULL,
	FKBER varchar(48) DEFAULT ' ' NOT NULL,
	GRANT_NBR varchar(60) DEFAULT ' ' NOT NULL,
	IUID_RELEVANT varchar(3) DEFAULT ' ' NOT NULL,
	PRS_OBJNR varchar(66) DEFAULT ' ' NOT NULL,
	PRS_SD_SPSNR varchar(24) DEFAULT '00000000' NOT NULL,
	PRS_WORK_PERIOD varchar(21) DEFAULT '0000000' NOT NULL,
	PARGB varchar(12) DEFAULT ' ' NOT NULL,
	AUFPL_OAA varchar(30) DEFAULT '0000000000' NOT NULL,
	APLZL_OAA varchar(24) DEFAULT '00000000' NOT NULL
) go
CREATE UNIQUE INDEX [VBAP~0] ON EQ1.SAPSR3.VBAP (MANDT,VBELN,POSNR) go
CREATE INDEX [VBAP~Z01] ON EQ1.SAPSR3.VBAP (VBELV,POSNV) go;

Stellar
Devart Team
Posts: 122
Joined: Tue 03 Oct 2017 11:00

Re: ODBC ASE with PowerBI

Post by Stellar » Fri 06 Jul 2018 12:15

Thank you for the information. We have fixed the issue, and the fix will be included in the next ODBC Driver for SAP Sybase ASE build.

MaximG
Devart Team
Posts: 1019
Joined: Mon 06 Jul 2015 11:34

Re: ODBC ASE with PowerBI

Post by MaximG » Tue 17 Jul 2018 12:31

On 16-Jul-18 we released a new version 1.4.8 that includes the changes you need. In DSN settings of our driver on the "Advanced settings" tab, we added the "QuotedIdentifier" option used to quote the reserved words. A full list of changes can be found by the link: https://www.devart.com/odbc/ase/revision_history.html

captzulu
Posts: 4
Joined: Thu 21 Jun 2018 15:34

Re: ODBC ASE with PowerBI

Post by captzulu » Tue 17 Jul 2018 21:16

I have downloaded and installed the new release, ticked the "Quoted Identifier" box and restarted my computer. Sadly, I'm still getting the same error message for some important SAP tables like KNA1 and VBAP.

MaximG
Devart Team
Posts: 1019
Joined: Mon 06 Jul 2015 11:34

Re: ODBC ASE with PowerBI

Post by MaximG » Wed 18 Jul 2018 15:16

We tested the behavior of our driver using Microsoft Power BI Desktop 2.59.5135.601 64-bit (June 2018) when retrieving the VBAP table data and found no problems. Please specify the exact version of Microsoft Power BI Desktop, which you used.

captzulu
Posts: 4
Joined: Thu 21 Jun 2018 15:34

Re: ODBC ASE with PowerBI

Post by captzulu » Wed 18 Jul 2018 15:56

wow this is amazing... I had a version from June 2018 also something like (2.59.5135.400) but i went ahead and updated anyways. I'm now on 2.59.5135.781 and it's working. Thank you very much!

Stellar
Devart Team
Posts: 122
Joined: Tue 03 Oct 2017 11:00

Re: ODBC ASE with PowerBI

Post by Stellar » Wed 25 Jul 2018 14:23

It is good to see that the problem has been solved.
Feel free to contact us if you have any further questions about our products.

Post Reply