topic SQL Error when Loading a qvd from qvw in QlikView

SQL Error when Loading a qvd from qvw

Wed, 07 Jan 2015 23:03:21 GMT

Hi ,

I am a qlikview novice and I would need your suggestion.In one of our reports. We are using a qvw file which internally creates a temporary table and then inserts the records of the temp table into a sales table.

When I execute the load ,the load happens for sometimes and then an error pops up which says

"SQL State:07002,Errorcode:32, ErrorMsg: The # of binded parameters <the # of the parameters markers

The interesting part is when I change the database connection from production to quality and development , the load happens properly. I am not sure why this is happening only in production environment.

Any help in this matter would be appreciated.

Thanks,

Abhay

Re: SQL Error when Loading a qvd from qvw

rwunderlich — Wed, 07 Jan 2015 23:17:58 GMT

You are INSERTing data from Qlikview into a SQL table using a "SQL INSERT ..." statement? What database server?

-Rob

Re: SQL Error when Loading a qvd from qvw

Thu, 08 Jan 2015 00:24:34 GMT

Hi Rob,

Thanks for the prompt response. The database server we are using is Greenplum.

Thanks,

Abhay

Re: SQL Error when Loading a qvd from qvw

djsampat — Thu, 08 Jan 2015 00:59:37 GMT

Please upload a sample QVW file or the script.

From what you have mentioned, it would be advisable to set your script to get all the data from multiple tables and then use Resident Load to create your Combined Fact table and then you can drop your temp tables.

Alternatively, you can load all the information into qvd files with required data and then create your fact table.

If this helped you, please mark as Helpful. If it solves your issue, please mark as Answer

If you are viewing this topic and found it informative, please like my comment.

Regards

Dhruv

Re: SQL Error when Loading a qvd from qvw

rwunderlich — Thu, 08 Jan 2015 01:14:32 GMT

I just want to confirm that you are INSERTing back into the GreenPlum database? The reason I ask is that 99.99% of users have never used Qlikview to update a SQL database. It's an unusual case. I just want to confirm that you trying to update SQL, not trying to create a Qlikview in memory table or QVD.

-Rob

Re: SQL Error when Loading a qvd from qvw

Thu, 08 Jan 2015 01:55:53 GMT

Hi Rob,

My apologies. I am trying to insert data into qvd.

Thanks,

Abhay

Re: SQL Error when Loading a qvd from qvw

jagan — Thu, 08 Jan 2015 02:09:09 GMT

Hi,

Check this thread hope it helps you.

Inserting records into Database using Qlikview Script

Regards,

Jagan.

Re: SQL Error when Loading a qvd from qvw

Thu, 08 Jan 2015 02:17:11 GMT

The following is a simple example of how you would read data from your SQL Server and then save the data that you've read into a QVD file that you can reuse by other QVW's.

Step 1: Use the Connect wizard to create the connection to your database.

Step 2: Load the data

TableSizes:
LOAD DatabaseName,
IndexName,
"Measured_Date",
NumberRows,
ServerName,
TableName;
SQL SELECT DatabaseName,
IndexName,
"Measured_Date",
NumberRows,
ServerName,
TableName
FROM ServerDatabaseSizes.dbo.TableSizes
where ServerName = 'DB1'
and DatabaseName = 'NotifyXP';

Step 3: Save the table you loaded into the desired QVD

Store TableSizes into TableSizes.qvd (QVD);

STORE is the keyword you want to search on in the QlikView help to understand more about the options.

Hope this helps you out.

Re: SQL Error when Loading a qvd from qvw

Thu, 08 Jan 2015 04:48:10 GMT

Thanks Dalton. The place where the script is failing is :

SQL State:07002,Errorcode:32, ErrorMsg: The # of binded parameters <the # of the parameters markers

sql

sql create temp table Nirvana_Cancel as

Select

Extract(Month From Air_Itinerary.Cancelation_Date) BOOKEDMONTH

,Extract(Year From Air_Itinerary.Cancelation_Date) BOOKEDYEAR

,Dim_DemandChain.dmdchainposcode as AGENCYCODE

...........................................................................................

..............................................................

Any clue why this would happen?

Thanks,

Abhay

Re: SQL Error when Loading a qvd from qvw

Thu, 08 Jan 2015 04:49:07 GMT

Thanks Dhruv. The place where the script is failing is :

SQL State:07002,Errorcode:32, ErrorMsg: The # of binded parameters <the # of the parameters markers

sql

sql create temp table Nirvana_Cancel as

Select

Extract(Month From Air_Itinerary.Cancelation_Date) BOOKEDMONTH

,Extract(Year From Air_Itinerary.Cancelation_Date) BOOKEDYEAR

,Dim_DemandChain.dmdchainposcode as AGENCYCODE

...........................................................................................

..............................................................

Any clue why this would happen?

Thanks,

Abhay

Re: SQL Error when Loading a qvd from qvw

Thu, 08 Jan 2015 04:50:18 GMT

Thanks Jagan. I am getting "Access to this place or content is restricted." when I click on this link.

Thanks,

Abhay

Re: SQL Error when Loading a qvd from qvw

Anonymous — Thu, 08 Jan 2015 05:53:13 GMT

Inserting records into Database using Qlikview Script

created by jagan on 29 May, 2014 2:11 AM, last modified by jagan on 10 Aug, 2014 1:08 PM

Version 2

Hi, this is the details in that link check the below

============================

The below script helps to insert data from Qlikview to Database using Script.

//Connection Sring

ODBC Connect to trDatabase;

//Load Data from Excel file

TABLE:

LOAD transaction_date,

location,

item,

site

FROM

[Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

// Getting number of records in Table

LET V_ROWS = NoOfRows('TABLE');

// Loop through the records and insert into Database table

FOR V_ROW = 0 TO V_ROWS - 1

LET V_FIELD1 = Peek('transaction_date',V_ROW);

LET V_FIELD2 = Peek('location',V_ROW);

LET V_FIELD3 = Peek('item',V_ROW);

LET V_FIELD4 = Peek('site',V_ROW);

SQL INSERT INTO dbo.temp_Table(transaction_date, location,item, site) VALUES('$(V_FIELD1)','$(V_FIELD2)', '$(V_FIELD3)','$(V_FIELD4)');

NEXT;

Regards,

Jagan.

Re: SQL Error when Loading a qvd from qvw

djsampat — Thu, 08 Jan 2015 06:43:44 GMT

If it works in Quality and Development, then it may be an access issue. Check your SQL security access.

Is the user you used on the Q&D database granted access to the Production DB?

If this helped you, please mark as Helpful. If it solves your issue, please mark as Answer

If you are viewing this topic and found it informative, please like my comment.

Regards

Dhruv

Re: SQL Error when Loading a qvd from qvw

Thu, 08 Jan 2015 07:29:17 GMT

Hi Abhay,

You may check two things here.

1) What is the "user-name" your QV app has, when it strikes the Production Database? (You my change the pointer to Dev or QA DB where I assume you should have the read access to system views and from there get the user-name. Because it might be possible that the user-id with which you are connecting to QA or DEV, through QV app, is a Unique ID and not a generic one, thus have a different privilege)

2) Then once you get that "QV user", please find if it has privilege to create tables in Production DB

Thanks.

Re: SQL Error when Loading a qvd from qvw

Thu, 08 Jan 2015 14:39:38 GMT

Just a guess but your SQL command isn't "just" a select statement. You are telling your database to create TEMPORARY table with the results of your select command. To read the data into QlikView simply remove the extra "sql create temp table Nirvana_Cancel as" and just go with the "sql .. select Extract..."