Sql query from Access returns no records, but SQL Query Analyser d

M

MikeB in WB

I want to populate a temporary table '#tablename' with the results of
serveral queries and then pass all the records back to a recordset for
calculations and display. The database is stored on an SQL server and all
the coding is in Access 2003 database linked to the SQL.

The following is just a test query that builds a temporary table, populates
it with so numbers and then returns the results. If I run it from SQL Query
Analyser I get the desired results, however from Access linked to the SQL it
says the query run but returns no records. Can anyone help

ALTER PROCEDURE dbo.StoredProcedure_kb3

AS

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

CREATE TABLE #tmpTable(test numeric)

DECLARE @counter int
DECLARE @test AS numeric

SET @counter = 1
WHILE @counter <= 10

BEGIN
INSERT #tmpTable (test)
VALUES (25)
SET @counter = @counter + 1
END

INSERT #tmpTable (test)
VALUES (26)

SET @counter = 1
WHILE @counter <= 10
BEGIN
INSERT #tmpTable (test)
VALUES (25)
SET @counter = @counter + 1
END


SELECT * FROM #tmpTable


SELECT MAX(test)as Answer
FROM #tmpTable

PRINT @@RowCount

DROP TABLE #tmpTable

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON
 
Top