😉 What a very nice post. I tried this very same query in SQL Server 2005 because I used to have the same problem with the # tables. I did not even use the exec part of your query and it worked famously. I only had to some very minor changes.
ALTER PROCEDURE myproc
AS
begin
declare @mytable table (controlaccount tinyint, controldescription varchar(255))
INSERT INTO @mytable(controlaccount, controldescription)
SELECT controlaccount, controldescription FROM [asset types]
SELECT * FROM @mytable
end
But do not try this in MSAccess connected to SQL Server 2005. All you get back is "The stored procedure executed succesfully." even when you exec the myproc stored procedure.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)