Calling a Stored Procedure in Cache thru SQL Statement

[spaceinvaders]

Hi All. I'm new here. I've been trying to find a solution for my
problem but haven't been very successful. I hope somebody can show me
what I'm doing wrong.

I create a linked server on our SQL Server 2000 directed to our Cache
System using InterSystems ODBC.

My boss created a stored procedure (classmethod) in our Cache system
that returns a dataset/table. He wants me to create a stored procedure
in SQL Server 2000 to call his stored procedure and store the dataset/
table returned by his stored procedure to a table. Now my question is
how do I call his stored procedure? (classmethod)

I can successfully query a class that returns a table using the
statement below
SELECT * FROM linkedserver..class.name

How do you call a stored procedure (classmethod) under a class defined
in Cache systems?
EXECUTE linkedserver..class.name_classmethod(parameters)

Please help. thank you.

[John@TDAMTD]

try 'call class.name_classmethod(parameters)'

On Mar 29, 12:10 am, spaceinvaders <christianray.mo...@gmail.com>
wrote:

[spaceinvaders]

Hi John. That didn't work either.

For some methods, I was able to make this work
SELECT * FROM OPENQUERY(LinkedServerName, 'CALL
Class.name_classmethod()')

but for some reason, the classmethod that involves parameter passing
doesn't work. Here's my error
OLE DB error trace [Non-interface error: OLE DB provider unable to
process object, since the object has no columnsProviderName='MSDASQL',
Query=call class.name_classmethod(parameter)'].
Msg 7357, Level 16, State 2, Line 1
Could not process object 'call class.name_classmethod(parameter)'. The
OLE DB provider 'MSDASQL' indicates that the object has no columns.

by the way, the parameter being passed is an odbcType,varchar named
job.

He sent me a vb code and this works. I need to do this via SQL so I
can create an sql job agent that works once a day.
Dim remOdbcCommand As New OdbcCommand("call
class.name_classmethod(?)", con)
remOdbcCommand.Parameters.Add(New
Odbc.OdbcParameter("Job", OdbcType.VarChar)).Value = Job
'
'Identify the type of command we’re using
remOdbcCommand.CommandType = CommandType.StoredProcedure
'
'create a new OdbcDataAdapter using the comm OdbcCommand
we’ve created
Dim da As New OdbcDataAdapter(comEDIRemits)

'Use the data adapter’s Fill method to run the query and
fill the dataset
daE.Fill(ds)
dvE = New DataView(ds.Tables(0))

> > Please help. thank you.- Hide quoted text -
>
> - Show quoted text -

[smundara43]

This is an answer for a very old post:  To call an Intersystems Cache Method via SQL Sever

SELECT * FROM OPENQUERY([LinkedServerName],'select NameSpace.Class.Method("abc","123","1abc","0")')

From.net code there are several ways of calling a method : 1) a select query, 2) a Call command inside a query 3) using the Class object the Cache Method signature, the last two methods use the Cache Managed Provider for .Net.