Click here to Skip to main content
14,641,293 members
Rate this:
Please Sign up or sign in to vote.
I am new to IBM DB2 and i am trying to insert into a temp table in SQL with the result set from an IBM DB2 Stored procedure. The stored procedure returns correct data when called from SQL server individually.

But when i create a temp table and try to insert records into the temp table from IBM DB2 SP it says '(0 row(s) affected)'

I am calling the DB2 SP individually like this in SQL server. EXEC ('{CALL SCHEMA.SP_NAME (''PARAM1'', ''PARAM2'', ''PARAM3'')}') AT SERVERNAME; This gives correct output.

Now i create a temp table with the same column names and similar definitions as the SP result set and try to insert to the temp table like this

CREATE TABLE #reorder ( PRODCODE VARCHAR(35), BUYNUM VARCHAR(10), DISCO CHAR(1), QTY_AVAIL INT , RECENT_NBR_DAY INT, RECENT_ORD_QTY INT, PER_DAY_DEMAND_QTY DECIMAL(7,2), AVAIL_NBR_DAY DECIMAL(7,2), YEAR_ORD_QTY INT, YEAR_PER_DAY_DEMAND DECIMAL(7,2), PONUM VARCHAR(10), POQTY INT, ISSUEDAT DATETIME, PRODDESC VARCHAR(25), CUCODE VARCHAR(15), LEADTIME INT, DEMANDPERDAY DECIMAL(7,2), ANNUALUSAGE INT, MAXIMUMDAILYUSAGE INT, SAFETYSTOCK DECIMAL(7,2), REPORDERPOINT DECIMAL(7,2), PRCVNR INT, )

INSERT INTO #reorder EXEC ('{CALL SCHEMA.SP_NAME (''PARAM1'', ''PARAM2'', ''PARAM3'')}') AT SERVERNAME;

Even though the SP returns records it is not getting inserted into the temp table. It gives (0 row(s) affected) message.

I tried increasing the length of the column data type in the temp table and suddenly it started working, but it stopped working again.

I am unable to figure out what exactly is the issue!! Is there anything that needs to be changed in the query?

I tried different ways to execute the query like using OPENROWSET but it gives the error saying 'The OLE DB provider "IBMDASQL" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.' even though the SP returns records i still get this error.

Can anybody help me figure out a solution for this?

Thanks in advance

What I have tried:

I have tried calling the DB2 SP in SQL like this
EXEC ('{CALL SCHEMA.SP_NAME (''PARAM1'', ''PARAM2'', ''PARAM3'')}') AT SERVERNAME;
which gives correct output with some records.
But when i try to insert into temp table it says ((0) rows executed).
Posted
Comments
Santosh kumar Pithani 16-Dec-17 4:05am
   
How many columns returning "EXEC ('{CALL SCHEMA.SP_NAME (''PARAM1'', ''PARAM2'', ''PARAM3'')}') AT SERVERNAME" this query after execution?
Roliking 16-Dec-17 8:37am
   
Same no of columns shown in the temp table that i have created.
Karthik_Mahalingam 27-Dec-17 23:38pm
   
use  Reply  button, to post Comments/query to the user, so that the user gets notified and responds to your text.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100