Execute Stored Procedure into a temporary table problem

Hello,
I was wondering if anyone can help me with this little problem using
INSERT and EXECUTE within a stored procedure. I would be very grateful
if anyone can solve the problem.
I have a simple table (tblEmployees) to hold all the employees details
empID int -- contains the employee id
firstname varchar -- contains first name
surname varchar -- contains surname
unitID int -- contains unit id
I also have a simple stored procedure (sp_get_employeeByUnitID) that is
designed to get all the employees at a certain unit. This procedure
returns data with only the empID column.
CREATE PROCEDURE [sp_get_employeeByUnitID]
@.unitID int
AS
SELECT empID FROM tblEmployees
WHERE unitID=@.unitID
GO
In another stored procedure, what I am doing (or at least I am trying
to!) is to execute a stored procedure (sp_getEmployees) into a temporary
table so that I may perform other calculations.
CREATE PROCEDURE [sp_get_Employees]
@.unitID as int
AS
create TABLE #MATCHEDEMPLOYEES_TABLE (empID int)
insert into #MATCHEDEMPLOYEES_TABLE (empID)
EXECUTE sp_get_employeeByUnitID @.unitID
-- other calculations goes here - remove to keep it simple
Select * FROM #MATCHEDEMPLOYEES_TABLE
GO
When I execute the stored procedure (sp_get_Employees) via the "Query
Analyzer" on MS SQL server 2k, it displays the correct results.
However, if I run it via an asp file it comes up with a error:-
"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Insert Error: Column name
or number of supplied values does not match table definition."
--
so, the error is on the insert command within sp_get_Employees, and I
think the error is trying to say that the result from
sp_get_employeeByUnitID contains extra columns that cannot be insert
into the #MATCHEDEMPLOYEES_TABLE. But it is - or so I though! i have
tested out sp_get_employeeByUnitID and it definitely returns a single
empID column.
The asp code is as follows:-
Function getAllEmployees(unitID)
Set cmdSP = CreateSPObject( cnDB, "sp_get_Employees" )
cmdSP.Parameters.Append cmdSP.CreateParameter ( "unitID",
adInteger, adParamInput, 8, unitID )
Set empRs = cmdSP.Execute
Set cmdSP.ActiveConnection = Nothing
Set cmdSP = Nothing
End Function
-- I just can't understand why it works okay in the query Query Analyzer
but not within an ASP page (the asp is definitely pass the correct unit
id number to the stored procedure)
Another question, it is possible that in the future, the stored
procedure (sp_get_employeeByUnitID) could return more then one column
(ie the empID and lastname). Is there any way, that I can do an insert
into #MATCHEDEMPLOYEES_TABLE from EXECUTING another stored procedure
without having to define any additional columns in the
#MATCHEDEMPLOYEES_TABLE? Ie, something like
INSERT sp_get_employeeByUnitID.empID
INTO #MATCHEDEMPLOYEES_TABLE (empID)
EXECUTE sp_get_employeeByUnitID @.unitID
Any help or suggestions would be appreciated.
Thanks
Darren Tweedale
darrentweedale@.@.hotmail.comUse SET NOCOUNT ON in every sp, to avoid this.
Example:
CREATE PROCEDURE [sp_get_employeeByUnitID]
@.unitID int
AS
set nocount on
SELECT empID FROM tblEmployees
WHERE unitID=@.unitID
GO
AMB
"Darren Tweedale" wrote:

> Hello,
> I was wondering if anyone can help me with this little problem using
> INSERT and EXECUTE within a stored procedure. I would be very grateful
> if anyone can solve the problem.
> I have a simple table (tblEmployees) to hold all the employees details
> empID int -- contains the employee id
> firstname varchar -- contains first name
> surname varchar -- contains surname
> unitID int -- contains unit id
> I also have a simple stored procedure (sp_get_employeeByUnitID) that is
> designed to get all the employees at a certain unit. This procedure
> returns data with only the empID column.
> CREATE PROCEDURE [sp_get_employeeByUnitID]
> @.unitID int
> AS
> SELECT empID FROM tblEmployees
> WHERE unitID=@.unitID
> GO
> In another stored procedure, what I am doing (or at least I am trying
> to!) is to execute a stored procedure (sp_getEmployees) into a temporary
> table so that I may perform other calculations.
> CREATE PROCEDURE [sp_get_Employees]
> @.unitID as int
> AS
> create TABLE #MATCHEDEMPLOYEES_TABLE (empID int)
> insert into #MATCHEDEMPLOYEES_TABLE (empID)
> EXECUTE sp_get_employeeByUnitID @.unitID
> -- other calculations goes here - remove to keep it simple
> Select * FROM #MATCHEDEMPLOYEES_TABLE
> GO
> When I execute the stored procedure (sp_get_Employees) via the "Query
> Analyzer" on MS SQL server 2k, it displays the correct results.
> However, if I run it via an asp file it comes up with a error:-
> --
> "Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Insert Error: Column name
> or number of supplied values does not match table definition."
> --
> so, the error is on the insert command within sp_get_Employees, and I
> think the error is trying to say that the result from
> sp_get_employeeByUnitID contains extra columns that cannot be insert
> into the #MATCHEDEMPLOYEES_TABLE. But it is - or so I though! i have
> tested out sp_get_employeeByUnitID and it definitely returns a single
> empID column.
> The asp code is as follows:-
> Function getAllEmployees(unitID)
> Set cmdSP = CreateSPObject( cnDB, "sp_get_Employees" )
> cmdSP.Parameters.Append cmdSP.CreateParameter ( "unitID",
> adInteger, adParamInput, 8, unitID )
> Set empRs = cmdSP.Execute
> Set cmdSP.ActiveConnection = Nothing
> Set cmdSP = Nothing
> End Function
> -- I just can't understand why it works okay in the query Query Analyzer
> but not within an ASP page (the asp is definitely pass the correct unit
> id number to the stored procedure)
> Another question, it is possible that in the future, the stored
> procedure (sp_get_employeeByUnitID) could return more then one column
> (ie the empID and lastname). Is there any way, that I can do an insert
> into #MATCHEDEMPLOYEES_TABLE from EXECUTING another stored procedure
> without having to define any additional columns in the
> #MATCHEDEMPLOYEES_TABLE? Ie, something like
> INSERT sp_get_employeeByUnitID.empID
> INTO #MATCHEDEMPLOYEES_TABLE (empID)
> EXECUTE sp_get_employeeByUnitID @.unitID
>
> Any help or suggestions would be appreciated.
> Thanks
> Darren Tweedale
> darrentweedale@.@.hotmail.com
>|||Alejandro Mesa wrote:
> Use SET NOCOUNT ON in every sp, to avoid this.
> Example:
> CREATE PROCEDURE [sp_get_employeeByUnitID]
> @.unitID int
> AS
> set nocount on
> SELECT empID FROM tblEmployees
> WHERE unitID=@.unitID
> GO
>
> AMB
> "Darren Tweedale" wrote:
>
Thanks Alejandro Mesa for replying, that was the correct answer!
I can't believe I missed that!
regards
darren tweedale