slxdeveloper.com Community Forums

The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!

Data & Imports Forums - T-SQL & Queries
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.

Forums RSS Feed

Back to Forum List | Back to T-SQL & Queries | New Thread

View: Search:

Author

Thread: how to call stored proc

Nick Iorio
Posts: 2

how to call stored proc Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 05 Jul 07 5:37 PM

I got this to work using VBScript and ADO but it will not work in SL. I'm substituting a call to a stored proc (usp_checklist_header) for the equivalent SQL and I get an error on the objRS.open command:

set objSLXDB = new SLX_DB
Set objRS = objSLXDB.GetNewRecordSet
' strSQL = "Select o.ACCOUNTMANAGERID, fo.CREDIT_ANALYST, fo.FUNDER, fo.LEASE_NUMBER, o.DESCRIPTION, fo.APPROVAL_AMOUNT, fo.APPROVAL_DATE From OPPORTUNITY o inner join FPC_OPPORTUNITY fo on o.OPPORTUNITYID = fo.OPPORTUNITYID Where o.OPPORTUNITYID = '" & strOpportunityID & "'"
strSQL = "exec usp_checklist_header @opporid '" & strOpportunityID & "'"
objRS.Open strSQL, objSLXDB.Connection

TIA - nick

[Reply][Quote]

John Gundrum
Posts: 632

Re: how to call stored proc Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 06 Jul 07 6:28 AM

What are the details of the error message you get in SLX?

What version of SLX?

I'm assuming the code you show here is a partial listing and strOpportunityID's value is set somehow before so. Have you checked the value of strOpportunityID before the strSQL = statement? I will usually do a MsgBox() right before the statement to verify a value.

Did you have to make 'minor' changes to the code between your working example outside of SLX and the non-working code in SLX? What might seem like a minor change could be the root of the problem.

John G.

John G.

[Reply][Quote]

John Gundrum
Posts: 632

Re: how to call stored proc Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 06 Jul 07 6:32 AM

Sorry... message was repeated...

[Reply][Quote]

John Gundrum
Posts: 632

Re: how to call stored proc Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 06 Jul 07 6:37 AM

Yep, another repeat...

[Reply][Quote]

Bob (RJ)Ledger
Posts: 1103

Re: how to call stored proc Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 06 Jul 07 7:03 AM

Quote:

Originally posted by Nick Iorio

I got this to work using VBScript and ADO but it will not work in SL. I'm substituting a call to a stored proc (usp_checklist_header) for the equivalent SQL and I get an error on the objRS.open command:

set objSLXDB = new SLX_DB
Set objRS = objSLXDB.GetNewRecordSet
' strSQL = "Select o.ACCOUNTMANAGERID, fo.CREDIT_ANALYST, fo.FUNDER, fo.LEASE_NUMBER, o.DESCRIPTION, fo.APPROVAL_AMOUNT, fo.APPROVAL_DATE From OPPORTUNITY o inner join FPC_OPPORTUNITY fo on o.OPPORTUNITYID = fo.OPPORTUNITYID Where o.OPPORTUNITYID = '" & strOpportunityID & "'"
strSQL = "exec usp_checklist_header @opporid '" & strOpportunityID & "'"
objRS.Open strSQL, objSLXDB.Connection

TIA - nick

The SalesLogix provider does not support stored procs.
--
rjl

[Reply][Quote]

John Gundrum
Posts: 632

Re: how to call stored proc Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 06 Jul 07 8:38 AM

I use SPs successfully through the 7.0.1 provider in several Crystal Reports (XI R2) I've created. Are they being called differently from Crystal?

I just checked the SQL for one of the reports and this is how it's structured

"sysdba"."c_SalesForecastSummary";1 75

This stored procedure has one parameter (the 75).

John G.

[Reply][Quote]

Bob (RJ)Ledger
Posts: 1103

Re: how to call stored proc Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 09 Jul 07 5:04 AM

If you have been "fortunate" to hav an sp work.. you are "lucky".. notice I said Not Supported.. not.. does not work..

SP's do not execute "back thru" the provider so any datetimes will be incorrect and typically will not generate tefs for remotes.

SP's are not remote friendly..

--
rjl

[Reply][Quote]

John Gundrum
Posts: 632

Re: how to call stored proc Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 09 Jul 07 6:17 AM

True, you did say 'supported' and I have run into the datetime differences. I'm usually just doing SELECTs and no modifications of data, so not creating TEFs should not be an issue.

John G.

[Reply][Quote]

Nick Iorio
Posts: 2

Re: how to call stored proc Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 09 Jul 07 12:30 PM

Thank you for all the input. I was out Friday and just got back in the office.
First off, I am working with SLX 6.2.6.1007. Assuming that, and assuming I am only doing SELECTs:
1) can I do SPs in this version?
2) if so, what is the complete syntax? (give an example with one input param)
thanks - nick

[Reply][Quote]

Page 1 of 1

You can subscribe to receive a daily forum digest in your user profile. View the site code of conduct for posting guidelines.

Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!

slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation. This site, and all contents herein, are Copyright © 2025 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.	code of conduct \|

page cache (param): 2/19/2025 4:58:09 PM