Microsoft Dynamics GP (Archived)

Unable to Execute SQL Server Stored procedures

(0) Share

Report

Posted on by Mohammed Irfan

185

I am trying to get Customer name from RM00101 using Stored Procedures.

Here is my SQL Server Stored procedure

CREATE PROCEDURE [dbo].[GET_CUSTOMER_NAME]
(
@IN_customer_number char(21),
@INOUT_customer_name varchar(50) output
)

As
Begin

select @INOUT_customer_name = CUSTNAME from RM00101 where CUSTNMBR = @IN_customer_number

My Prototype Script named "GET_CUSTOMER_NAME"

sproc returns long l_ReturnCode;

in string IN_customer_number;
inout string INOUT_customer_name;

call sproc "GET_CUSTOMER_NAME",
l_ReturnCode,
IN_customer_number,
INOUT_customer_name;

I have called the procedure in field script

local long l_ReturnCode;
local string INOUT_customer_name;

call GET_CUSTOMER_NAME, l_ReturnCode, 'Customer Number' of window CUSTOMER_WINDOW,INOUT_customer_name;

set 'Customer Name' of window CUSTOMER_WINDOW to INOUT_customer_name;

My Question is ,

1. I am not able to get the customer name through stored procedures.

2. Am i following the currect process for implementing stored procedures.

3. Can anyone share some Reference documents of dexterity .

Thanks ,
Mohammed Irfan.

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (2)

Tim W 2,925 on at

Like (0)

Report

Are you going to call this from Dexterity? Is that the issue you are having? Or are you executing this procedure from elsewhere and nothing returns?

Was this reply helpful? Yes No
Tim W 2,925 on at

Like (0)

Report

Reading this closer.... looks like it should work.... so thinking you need to go to your stored procedure and set read and execute permissions to DYNGRP. One other question would be if the field you are returning the name to is a field in a scrolling window, if so, just won't work this way.

Assuming you have access to a Dexterity install you should have a documentation folder under the client root. In there the PRGV1 pdf will have a section on Stored Procedure use, starts on chapter 48.

Here's a Dexterity function to do the same thing, code gets contained in the GP logic so has less moving parts.

function returns 'Customer Name' o_Cust_Name;

in 'Customer Number' l_cust;

clear table RM_Customer_MSTR;

range clear table RM_Customer_MSTR;

release table RM_Customer_MSTR;

set 'Customer Number' of table RM_Customer_MSTR to l_cust;

get table RM_Customer_MSTR;

if err() = OKAY then

o_Cust_Name = 'Customer Name' of table RM_Customer_MSTR;

end if;

You would call it from your field script as:

set 'Customer Name' of window CUSTOMER_WINDOW to TWO_Return_Cust_Name(INOUT_customer_name);

Was this reply helpful? Yes No
Mohammed Irfan 185 on at

Like (0)

Report

Hi Tim ,

Thanks for ur reply

I am calling the stored procedure from dexterity i.e. customer number field .

I am getting nothing customer name empty from the stored procedure.

I need this script to work.

I haven't tried your solution yet.I will get back to you.

Thanks ,

Mohammed Irfan.

Was this reply helpful? Yes No
Verified answer

Tim W 2,925 on at

Like (0)

Report

Hi Mohammad,

It's probably easier to put that function I gave you in Dexterity and just call it as a general rule but I'm a Dex programmer so clearly that's easiest for me. That's how we do what you are looking to do, I usually only read from DB directly if table is not a proper GP table.

Couple things on your Stored Procedure though I'd check since the code looks correct to me (I did not set it up to test it though).

Check the permissions on the stored procedure and be sure the SP exists in the company you are logged into.

Right click on the SP, open permissions. For the Dexterity client to call it (unless you are logged in as 'sa') you need to have execute set for DYNGRP as below.

Second thing I'd try would be to execute the SP directly from SQL putting a customer you know exists and see if it returns the name. If it does and permissions are set to DYNGRP then somewhere in your code is the issue and could easily be where and how you are executing the calling script. Now you kind of need to use the debugger etc....

Good luck on this.

Tim

Was this reply helpful? Yes No
Verified answer

Jeyakumar 592 on at

Like (0)

Report

Hello Irfan,

Your Script is looking good.Just make sure Customer Number is passing Correctly.Let's put a warning message like below or debug it is in test mode.Only mistake is,You have to put END Statement to last line of Stored Procedure instead of GO.

local long l_ReturnCode;

local string INOUT_customer_name;

warning "CustomerNumber " + 'Customer Number' of window CUSTOMER_WINDOW;

call GET_CUSTOMER_NAME, l_ReturnCode, 'Customer Number' of window CUSTOMER_WINDOW,INOUT_customer_name;

warning "CustomerName" + INOUT_customer_name;

set 'Customer Name' of window CUSTOMER_WINDOW to INOUT_customer_name;

Also Execute the below script against the database for DYNGRP Permission

GRANT ALL ON TWO.dbo.GET_CUSTOMER_NAME TO DYNGRP

Hope this helps!!!

Was this reply helpful? Yes No
Mohammed Irfan 185 on at

Like (0)

Report

Thank you Tim for your suggestions , i gave execute permissions to my DB. I have executed my stored procedure several times,it was working fine for me, it was returning customer name .I think it was the issue related to permissions.

As Jeya mentioned, i have included warning statements and it is working for me.

Thank you Tim and jeya for your time.

Was this reply helpful? Yes No