How to Run SQL Stored Procedure?

[ryansdv]

Greetings!

I made a procedure in ms sql stored procedure. example.
create procedure getname (@vkeyword varchar(128)) ...

How can I call or use this procedure in MS Access?

Thanks!

 

[easyit]

You should use a pass trough query for this. Build your query in sql (change the type thru the menu in a passtrough query).

 

[easyit]

...should add something. The statement you use in sqlserver to execute the storedprocedure is the sql statement you should use in the passtroughquery (ptq). In this ptq check the properties because you need the right (ODBC) connection to the database.

 

[ryansdv]

thanks easyit, I get some idea from u, but not totally understand it. anyway i'll make further research on this.

again thanks!

 

[easyit]

...you know what a passtrough query is?

 

[ryansdv]

actually it's my first time.

 

[cmmrfrds]

What and how depends on your environment. What version of Access and SQL Server? Also, may need to do something different depending on using within a Form, Report, Recordset, or VBA code. To get the best information give an example of how you intend to use.

 

[ryansdv]

Right now, Im using SQL 2000 and Access 2002. Anyway, I make further research on this "about pass-through query.

I wish, you can make a sample code. Let say Calling a simple sql stored procedure in access code.

Thanks a lot for your time. Have a nice day!

 

[cmmrfrds]

See example in thread958-1043645

 

[ryansdv]

Once again thanks. This will help a lot.

 

[ryansdv]

This is the test program I made.

SQL Server 2000 side.

CREATE PROCEDURE dbo.gensp_GetSearchResults1 (@p_vkeyword varchar(128)
AS
declare @SQL varchar (400)
Set ...
exec (@SQL)
GO

Based on SQL Syntax Checking, the above code has no error.

The code below, I made in Access Code 2000

Dim adoConnection As ADODB.Connection
Dim adoCommand As ADODB.Command
Dim adoParm As ADODB.Parameter
Dim rsSet As ADODB.Recordset
Dim strName As String

Set adoConnection = New ADODB.Connection
adoConnection.Open "ODBC;DATABASE=cdiSeiko;UID=sa;PWD=;DSN=cdiSeiko"

Set adoCommand = New ADODB.Command

adoCommand.CommandText = gensp_GetSearchResults1
adoCommand.CommandType = adCmdStoredProc

strName = Trim(InputBox("Enter Name : "))
Set adoParm = adoCommand.CreateParameter("@p_vkeyword", adVariant, adParamInput, , strName)
adoCommand.Parameters.Append adoParm

Set adoCommand.ActiveConnection = adoConnection
Set rsSet = adoCommand.Execute

The Error -> "Parameter type is not supported" in "Set rsSet = adoCommand.Execute"

Your kind help is very much appreciated.

 

[RoyVidar]

Try advarwchar or advarchar in stead of adVariant as parameter data type, and specify the length:

[tt] Set adoParm = adoCommand.CreateParameter("@p_vkeyword", advarchar, adParamInput, 128, strName)[/tt]

Roy-Vidar

 

[ryansdv]

New error occured:

Run-time error '-2147217900(80040e14)':
[Microsoft][ODBC SQL Server Driver] Syntax error or access violation.

 

[PHV]

CREATE PROCEDURE dbo.gensp_GetSearchResults1 (@p_vkeyword varchar(128)[highlight])[/highlight]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ryansdv]

thanks for your time PHV.
actually, the stored procedure in sql is actually correct.

 

[cmmrfrds]

I am not sure of the error, but here are a couple of suggestions to help in debugging.

Check this site for native OLEDB connections - avoid ODBC since it is just extra overhead.

http://www.carlprothman.net/Default.aspx?tabid=81

The ADO connection has an Error collection. Set up a loop ON ERROR to go through this collection since there can be multiple errors and you will get the bottom line description not as in this case something ODBC probably is giving you and does not get to the problem.

One more thing. Prefix the objects with dbo. otherwise when a non database owner runs there will be permissions problem.

 

[cmmrfrds]

Here is an example of error handling in a function.

Public Function SomeFunction()
On Error GoTo ErrHandler

Your code to deal with ADO recordsets etc.......

Exit Function
ErrHandler:
Dim er As ADODB.Error
Debug.Print " In Error Handler "; Err.Description
For Each er In cn.Errors
Debug.Print "err num = "; Err.Number
Debug.Print "err desc = "; Err.Description
Debug.Print "err source = "; Err.Source
Next
End Function

 

[ryansdv]

Thank you very much cmmrfrds and to all of you who help to solve this problem. I will try your suggestions and will be back to inform you how I solve this problem. Once again, Thank you and have a nice day!