I'm using SQL Server and have a stored procedure and need to pass a session variable to the stored procedure. The stored procedure has "@CityID" declared. In DMX, I'm not sure how to do this via the GUI so I modified the code to look like this:

CurrentSales.CommandText = "dbo.getcurrentsales @CityID=" & Session("CityID"

But this is returning the following error:

<i>Microsoft OLE DB Provider for SQL Server error '80040e14'

Syntax error or access violation </i>

Any idea what I'm doing wrong? How can I pass a variable with the GUI for running stored procedures?

When I write out the command text I see:

<i>{ ? = call dbo.GetCurrentSales @CityID=1 }</i>

ANY help would be appreciated. This is my first time using Stored Procedures. Can you tell? <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle> THANKS GANG!

-Grimps


Phil Grimpo
Executive Director
Inspirmedia

OK for an SP that returns a recordset you should set up the SP in SQL Server, for example:

--------------------------
CREATE PROCEDURE spSimpleSelect

@CityID int

AS

SET NOCOUNT ON

SELECT Whatever, WheteverElse FROM TableName WHERE CityID = @CityID

SET NOCOUNT OFF

Go
------------------------------

Now, in DWMX Use Server Behaviours &gt;&gt; Recordset(Query) &gt;&gt; Advanced..., You should see the DataBase Items at the bottom of the dialogue box, click the + symbol to expand the Stored Procedures, now Click on the stored procedure you want to use, in this case dbo.spSimpleSelect, you should now see the code: {call dbo.spSimpleSelect('CityID')} in the SQL window and the Variable CityID in the variables windows, set up the defaullt value and the run-time value, Session("CityID" in this case, that should be it.


If you are using an INSERT, UPDATE or DELETE SP, Something like:

--------------------------
CREATE PROCEDURE spSimpleInsert

@CityID int

AS

SET NOCOUNT ON

INSERT INTO tablName(CityID) VALUES (@CityID)

SET NOCOUNT OFF

Go
------------------------------

Now in DWMX, use Command &gt;&gt; Stored Procedure &gt;&gt; Click the + symbol next to Stored Procedures in the Database Items window, click on dbo.spSimpleInsert. You should now see: dbo.spSimpleInsert in the SQL Window
and in the variables window:
@RETURN - which you can ignore
@CityID - Type = Integer - Direction = In, now you need to enter the size, 4 for an integer data type, the default value, depending on the datatype of the db field this can be empty or whatever you want and finally the Run-Time value, which will be: Session("CityID"

When providing the Size, look at the Table in design view in SQL Server, the size of the field varies according to the data type, e.g, Int = 4, varchar(50) = 50, datetime = 8 etc.

Hope that helps.

Regards

Owen.

-------------------------------------------------------------------------------------------
Used programming books and web development software for sale (UK only):
www.tdsf.co.uk/tdsfdemo/Shop.htm


Multiple Parameter UD4 / Access 2000 Search Tutorial:
www.tdsf.co.uk/tdsfdemo/