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).