Can execute a stored procedure --> Saleslogix OLE DB Provider : failed to parse SQL

[rtrenado]

Hi there, this is driving me mad.. and i'm sure it's a little thing that, as i'm now frustrated, can't see.. can anyone see my fail please?

I'm trying to execute a stored procedure from within SLX, and return the results to a recordset.

Mine is version 7.5 SP3 LAN Version

The code on the form is this:

Sub Button1Click(Sender)

Dim objSLXDB

Dim strSQL

Dim objRS

Dim Result

Dim strErr

strSQL = "exec kk 'A6UJ9A00000N'"

Set objSLXDB = New SLX_DB

Set objRS = objSLXDB.GetNewRecordset()

objRS.Open strSQL, objSLXDB.Connection

If Not (objRS.BOF And objRS.EOF) then

msgbox "Result " & objRS.Fields("account").value

End if

objRS.Close

Set objRS = Nothing

Set objSLXDB = Nothing

End Sub

And the stored procedure inside the database is this:

USE

[SLXDATABASE]

GO

SET

ANSI_NULLSON

GO

SET

QUOTED_IDENTIFIERON

GO

ALTER

PROCEDURE [sysdba].[KK]

@X

varchar(12)

AS

BEGIN

SELECT account, accountid from [sysdba].[account] where accountid =@X

return

END

if i execute the stored via SQL manager i get results:

exec kk 'A6UJ9A00000N'

returns

TESTACCOUNT | A6UJ9A00000N

What i'm doing wrong?.. the error comes on the line of the code:

objRS.Open strSQL, objSLXDB.Connection

Many thanks in advance

[rtrenado]

I've achieved this using this code:

  Dim objRS
  Dim objCmd
  Dim strConnectionString

  Dim oCn
  Dim StrCn

  Dim DataSource
  Dim InitialCatalog
  Dim UserID
  Dim Password
  Dim Resultado

  DataSource = "SERVER"
  InitialCatalog = "DATABASE"
  UserID = "user"
  Password = "pass"
  '**************************

  set oCn = CreateObject("ADODB.Connection")
  StrCn = "Provider=sqloledb;Data Source=" & DataSource & ";Initial Catalog=" & InitialCatalog & ";User Id=" & UserID & ";Password=" & Password
  oCn.Open StrCn

  If oCn.State = adStateOpen Then

      set objRS = CreateObject("ADODB.RecordSet")
      set objCmd = CreateObject("ADODB.Command")
      strConnectionString = "exec kk 'A6UJ9A00000N'"  'DNL
      objCmd.CommandText = strConnectionString
      objCmd.CommandType = adCmdText
      objCmd.ActiveConnection = oCn

      Set objRS = objCmd.Execute

      If Not (objRS.BOF And objRS.EOF) then
         'TIENE IMPAGOS, CAMBIO EL COLOR
         msgbox "Result = " & objRS.Fields("account").value
      End if

      objRS.Close
      set objRS = Nothing
      set objCmd = Nothing

  end if

  oCn.Close
  set oCn = nothing

is this the only way to achieve this?, what's wrong with the OLEDB provider?

[RJLedger]

This is the corerct way to do it. There's nothing wrong w/the provider.

[rtrenado]

Ok then, many thanks