SQL Server Stored Procedure problems

[robsant]

Hi,

I am having problems trying to execute a stored sql procedure.

It gives me an error 3065 Cannot execute a select query.

The vba code is

Dim qdef As DAO.QueryDef
Set qdef = CurrentDb.CreateQueryDef("")
qdef.Connect = CurrentDb.TableDefs("dbo_sites").Connect
qdef.SQL = "EXEC CreateNewInHeader " & Me.TrailerID
Debug.Print qdef.SQL
 qdef.Execute

the sql server procedure is

USE [TRGOPS]
GO
/****** Object:  StoredProcedure [dbo].[CreateNewInHeader]    Script Date: 06/01/2015 17:16:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CreateNewInHeader] 
	-- Add the parameters for the stored procedure here
	@TrailerID int = 0

--	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
--	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
--	SET NOCOUNT ON;

    -- Insert statements for procedure here
	---SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
	INSERT INTO dbo.InHeader ( TrailerID,StatusID, OutDriverID, OutHaulierID, OutHGVReg, InTransToCustEmpID, InTransToCustTime, InTransToCustUserID, InTransToCustSysTime, ArrAtCustEmpID, ArrAtCustTime, ArrAtCustUserID, ArrAtCustSysTime )
	SELECT dbo.Trailers.TrailerID, dbo.Trailers.StatusID, dbo.Trailers.OutDriverID, dbo.Trailers.OutHaulierID, dbo.Trailers.OutHGVReg, 
	dbo.Trailers.InTransToCustEmpID, dbo.Trailers.InTransToCustTime, dbo.Trailers.InTransToCustUserID, dbo.Trailers.InTransToCustSysTime, 
	dbo.Trailers.ArrAtCustEmpID, dbo.Trailers.ArrAtCustTime, dbo.Trailers.ArrAtCustUserID, dbo.Trailers.ArrAtCustSysTime
	FROM dbo.Trailers
	WHERE (((dbo.Trailers.TrailerID)=@TrailerID));

	UPDATE dbo.Trailers
	SET dbo.trailers.TrgNum=SCOPE_IDENTITY()
	WHERE dbo.trailers.TrailerID=@TrailerID;
END

can anyone help please?

 

[Galaxiom]

CurrentDb cannot handle Stored Procedures.

Run the Stored Procedure with an ADODB Command over an ADODB Connection. The parameters are passed via the Command's Parameters Collection.

 

[cjman]

You forgot the @SqlServervariable and the two' ' similar to "q.SQL = "execute createNewInHeader" & " @TrailerID=" & "'" & Me.TrailerID & "'"

 

[Galaxiom]

Ah. (Light turns on.) Pass Through query.

 

[pbaldy]

You forgot the @SqlServervariable and the two' ' similar to "q.SQL = "execute createNewInHeader" & " @TrailerID=" & "'" & Me.TrailerID & "'"

FYI, the @Variable is optional, you can simply pass them in order:

EXEC ProcName 123, 'abc', '1/6/2015'

Also note that the apostrophes would not be appropriate for a numeric data type (or at least not required; I don't actually know if it would still work with them, but my gut is not).

 

[robsant]

Many thanks for the help, I have rewritten it using ADODB.

I now have another problem with returning paramaters, but I assume its best to post this as a new thread.