'Microsoft OLE DB Provider for SQL Server, ErrorMsg: Invalid column name' at given SQL variable

qlikmark1990 — Wed, 22 Dec 2021 20:32:06 GMT

I have an SQL script that has to run in QlikSense to fetch the needed data for my application. First I defined some SQL variables to use in the script. When I run the script:

SET vgl = 'MATE1';
LET vglId = 1;
LET startTS = 'null';
LET endTS = 'null';

LIB CONNECT TO 'db02';

SQL

select a.id, a.patientNummer, a.achternaam, a.tussenvoegsel, a.voorletters, a.geboortedatum, a.geslacht, a.email, a.opmerkingx,
       a.geblokkeerd, a.startDatum, a.bsn, a.meettrajectId, a.afgebroken, a.typeTraject, a.trajectStatus, a.taal, a.moment, a.startDatumMoment, a.ingestuurd,
       cast(a.score as numeric(5,2)) as score, a.eenheid, a.opmerking, a.schaalid, a.schaalnaam, a.volgnrInSchaalInt, a.volgnrInLijst, a.vglnaam, a.momentNr
       from
(
select *, null as opmerkingx
       from rom.vwOutputForAnalyseRom
where vglnaam = $(vgl) and startDatumMoment between isnull($(startTS), '2000-01-01') and isnull($(endTS), '2040-01-01')) as a;

I receive the error:

De volgende fout is opgetreden:

Connector reply error: ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Invalid column name 'MATE1'.

When I change the line:

where vglnaam = $(vgl) and startDatumMoment between isnull($(startTS), '2000-01-01') and isnull($(endTS), '2040-01-01')

where vglnaam = 'MATE1' and startDatumMoment between isnull($(startTS), '2000-01-01') and isnull($(endTS), '2040-01-01')

I don't get an error anymore but by this way I don't use the variable $(vgl). How do I use the variable properly in this script?

Re: 'Microsoft OLE DB Provider for SQL Server, ErrorMsg: Invalid column name' at given SQL variable

Anil_Babu_Samineni — Thu, 29 Nov 2018 05:12:47 GMT

Whenever, We are working with SQL and Qlik. They will work there own way not altogether. So, You can't use SET and LET variables into SQL queries. Instead using Resident load will deserve.

Re: 'Microsoft OLE DB Provider for SQL Server, ErrorMsg: Invalid column name' at given SQL variable

jonathandienst — Thu, 29 Nov 2018 05:57:20 GMT

You need to include the quotes:

where vglnaam = '$(vgl)' and startDatumMoment between isnull($(startTS), ....

topic 'Microsoft OLE DB Provider for SQL Server, ErrorMsg: Invalid column name' at given SQL variable in App Development

'Microsoft OLE DB Provider for SQL Server, ErrorMsg: Invalid column name' at given SQL variable

Re: 'Microsoft OLE DB Provider for SQL Server, ErrorMsg: Invalid column name' at given SQL variable

Re: 'Microsoft OLE DB Provider for SQL Server, ErrorMsg: Invalid column name' at given SQL variable