We have set up an ODBC adapter to poll a stored proc.
We found that if the stored proc has a temporary table defined the rows returned are always 0.
Any idea why this would be and what we can do to get around it?
Which vendor is the database? What is the stored procedure code? Does this code work outside of the ODBC adapter, like in SQL Server Management Studio?
We are using SQL server 2012. The procedure works in SSMS and in other .net applications.
I tried a simple code like this in the procedure -
DECLARE @temp TABLE( DataColumn int)
INSERT INTO @temp (DataColumn) VALUES (1);
SELECT DataColumn FROM @temp
Try adding this to the beginning of your stored procedure:
SET NOCOUNT ON;
I created a stored proc with your exact code above and it didn't work. Adding that line fixed my issue.