Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Highlighted
New Member
Posts: 22
Registered: ‎02-15-2010

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

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

New Member
Posts: 22
Registered: ‎02-15-2010

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

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?

Gold Super Contributor
Posts: 3,087
Registered: ‎03-19-2009

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

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

--
RJ Ledger - rjledger@rjlSystems.net +1 603.369.3047 x101

".. Innovators in Mobility - Experts in Workflow Automation..."
http://www.rjlSystems.net - blog: www.rjlSystems.net/blog.html
New Member
Posts: 22
Registered: ‎02-15-2010

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

Ok then, many thanks