Call Stored Procedure?

AlexRO

AlexRO

Well Known Member
Hello all,

Does anyone know if it is possible to call a stored procedure from a business function?

Alex.
 
Thanx, Peter. Indeed that thread was very helpful. I will try to do it somehow like that.

Regards,
Alex.
 
We do call a Oracle stored procedures

One way is using B34A1030 (Execute external program)

We build up the string to call the procedure, and pass this into the bsfn and execute using an op$login:

eg: echo "EXECUTE proddta.procedure('<parameter1>', '<parameter2')"|sqlplus-s/@proddta

JDE 8.10, 8.97.1.2, Oracle 10.2, Unix
 
Matt,

Come to think of it, I have done that too, and recently
blush.gif
. Sorry I didn't mention it as well. It was my first day back after a week and a half off - well that's my excuse.
 
[ QUOTE ]
We do call a Oracle stored procedures

One way is using B34A1030 (Execute external program)

We build up the string to call the procedure, and pass this into the bsfn and execute using an op$login:

eg: echo "EXECUTE proddta.procedure('<parameter1>', '<parameter2')"|sqlplus-s/@proddta

JDE 8.10, 8.97.1.2, Oracle 10.2, Unix

[/ QUOTE ]

Hey guys, I know this topic is old and has probably been regurgitated many times over, but I need some help with this.

I have a MSSQL 2000 server that is collecting production metrics, i.e. a custom application OUTSIDE of JDE is monitoring the production floor, collecting data into non-JDE tables, and displaying the data to the production floor.

I have a requirement where I need to use some of this data in JDE. My JDE data server is on a different server (MSSQL 2005) and I have a stored procedure that will populate the tables correctly and it sits on a scheduler on the MSSQL 2K server. In an application, I need to allow the user to repopulate the data manually if necessary (re-run the Stored Procedure). What I do not understand how to do is write the interface of the business function to do this. Would creating a batch file on the SQL 2K server and then calling it from the JDE App be the best route to go? Or call the SQL SP directly? Since this business function will run on the ENTERPRISE server, how do I configure the system to run the SQL 2K command line? I know I am missing something basic but can't seem to figure this one out.

I need to pass in 3 parameters: branch, shift, and data (JULIAN DATE!)

The command should look like this:
c:\osql.exr /U xxx /P xxx /Q "EXECUTE server.db.dbo.shiftSummary branch, shift, date"

OR should it be like this:
\\server\c$\osql.exr /U xxx /P xxx /Q "EXECUTE server.db.dbo.shiftSummary branch, shift, date"

Any help is greatly appreciated.
 
Hello,

Have you tried either command line?

I did not work with win but with linux and I gave the path on the server from the root /path/to/JDE and it worked.

I think the best way is to create a batch file in which you put the command with all you parameters and call that file. That is in case you modify the parameters you do not need to recompile a package for the server.

Cheers,
Alex.
 
I found that I needed to use a batch file, because when I use the string option, I get the 4135 error (File not found).

BUT... I am getting an SQL error that I am not sure about:
c:\>osql.exe /U jdesql /P snac674 /Q "EXECUTE poller.dbo.JDE_EOS_Summary 3,1002,
111076"
[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
[SQL Native Client]Login timeout expired [SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

I checked both the SQL 2K (holds production data) and also the SQL 2K5 server (JDE DB Server) and both appear to allow remote connections. If I run the batch directly from the server command line (SQL2K box) it works fine.

Are there any SQL DBA's who may shed some light on this?
 
If you are running the UBE on your enterprise server, you will need to make sure that your enterprise server has access to the MSSQL database and server under the user id under which the JDE services are running on the enterprise server.
 
Currently I am able to replicate the issue on my local machine as well.

The BATCH file is sitting in a shared directory (with proper security for the client to read and execute it) on the SQL 2K server where the Stored Procedure resides.

I can run the batch via an RDP session from the servers command line directly and it works.

When I call the procedure from my local machine (on internal network) I get the message detailed above.

I checked the MS MSSQL support technet and followed the instruction on firewall configuration, but the SQL2K5 server's firewall is disabled (no real idea why).

I also verified that the remote connections option were enabled on the DB server and also that the browser services were enabled.

Also: I have a stand alone application running at all my plants (PA - corp, FL, CA) that monitors the shop floor production and it calls this stored procedure at shift end and it works fine from them. Obviously these stand alone apps are not using the command line utility to launch the batch file.
 
OK, I think I figured it out. I needed to add the /S flag to the osql.exe command line. Once I did this, it worked without issue. It works on my local dev E1 machine, but will try it on the Enterprise server here soon.

So, the windows command string for the SQL 2K server ended up being (showing string from batch file):

osql.exe /S <server> /U <UN> /P <PW> /Q "EXECUTE poller.dbo.JDE_EOS_Summary %1,%2,%3"

Thanks to all for your help and support. I knew there was a solution, and you all helped guide/push me in the right direction.
 
Isn't it always FUN to submit those External Commands?

Another way to do this 'thing' - is to create the Batch Command via Flat File Operations (B34A1010).

You can create a batch file on the destination machine, with the necessary syntax. Then use the B34A1030 to call the batch file.

REMINDER: If you are on a Unicode system - Make sure you set the Flat-File to Non-Unicode, or you will end up with 'spaces' between each character you write to the flat/batch file.

Creating th Batch File is easier, from a debugging standpoint - you can log into the OS and run the UBE-Created Batch process, and see the error messages, then go back into the UBE and edit/revise...

(db)
 
Create a table to hold the parameters and insert a record into it. In the table, have a native database insert trigger that reads the parameters and executes the procedure passing the parameters. Then have the procedure mark the record as processed. The procedure could also return data into the record.
 
Back
Top