Hello All I have a stored proced

[EriRobert]

Hello All

I have a stored procedure with the following code
(extract):

CREATE TABLE #TmpForm29Part4 (F4_Type char (1),
F4_BankCode int, F4_RegionCode int, F4_RegionSubCode int)

INSERT #TmpForm29Part4
EXECUTE usp_ShowForm29Part4Base @intBankCode = 0, @datStartDate = @datStartDate,
@datEndDate = @datEndDate

SELECT * FROM #TmpForm29Part4Check

When I Run the Stored_Procedure from within the Access Project Database Window I get the following error:

Insert Error: Column name or number of supplied values does
not match table definition.

Fair enough you might say. But I have checked and rechecked the output from usp_ShowForm29Part4Base and it does agree with the table definition.

However the clincher here I think is the fact that the same stored procedure works OK when run from SQL Server Query Analyser and works OK when that stored procedure is set in the RecordSource of a form. This is only a problem when I Run the sp from the Database Window.

Has anyone else had this sort of problem?

Thanks in advance


Robert

 

[cmmrfrds]

How about ownership and permissions on the stored procedure?

Try.
EXECUTE dbo.usp_ShowForm29Part4Base

 

[EriRobert]

Sorry for the delay in replying.

Adding dbo to the front of the sp name didn't make any difference.

 

[cmmrfrds]

The parameters being passed for @datStartDate,
@datEndDate = @datEndDate
probably result in no records being returned.

 

[EriRobert]

cmmrfrds

I'm not sure you're correct there:

INSERT #TmpForm29Part4
EXECUTE usp_ShowForm29Part4Base
@intBankCode = 0,
@datStartDate = @datStartDate,
@datEndDate = @datEndDate

works fine 99% of the time (apart from above code):

@datEndDate = @datEndDate

First @datEndDate is the name of the input parameter in usp_ShowForm29Part4Base. Second @datEndDate is an input parameter in this stored procedure. Confusing that I have used the same name, maybe, but not incorrect.

I use this format sucessfully in many places.

Anyway thanks for the interest. Incidentally for the original problem I striped down the code to the minimum (returning one column, no input parameters etc) and still got the original error. What seemed to be happening was it was returning a second column consisting of some sort of system information (hence the error) that I hadn't asked for.

I'll put it down to one of those things.

Robert

 

[Norris68]

Can you show us some of the results from the usp_ShowForm29Part4Base, and possibly it's definition (at least the I/O parameters)?

 

[EriRobert]

Here are full details (stripped down version):

Called SP:

---
Alter PROCEDURE usp_ShowTestCalled
AS
SET NOCOUNT ON

SELECT GL_BankCode
FROM tblGL
WHERE
GL_ID = 760

RETURN
---

Note: GL_BankCode is int datatype
This sp returns single record - answer: 2

Calling Sp:

---
Alter PROCEDURE usp_ShowTest
AS
SET NOCOUNT ON

CREATE TABLE #Tmp (F4_BankCode int);

INSERT #Tmp (F4_BankCode)
EXECUTE usp_ShowTestCalled

SELECT * FROM #TmpForm29Part4Check

RETURN
---

When usp_ShowTest is run from Database Window - Error:

Insert Error: Column name or number of supplied values does
not match table definition.

I use this sort of code in many places but this instance fails.
SQL Server's Query Analyzer processes the sp ok. Putting the sp in a Form or Report recordsource is ok, but running from Database Window is not.

Now you know everything!

And by the way I apologise for the lack of a proper title on this thread.