Call Sql Server Stored Procedure Using dynamics ax 2012

Finance and Operations/AX Developer Forum
1

Hi Everyone,

Here I am in Trouble, [:(] I cant able to connect with the SQL server Using Dynamics Axapta 2012. I need to call Stored Procedure which is already written in the Sql Server 2008 using x++ Class and methods (or) JOBS.

I have already Googled and tried with those codes which is not helpful…

So I want the particular scenarios which is given below,

  1. I need to give a connection to the Sql server using X++ Job (or) Class & methods

  2. And then, I need to Execute the Stored Procedure using My Passed Parameter Values(like Dates(from date, to date)) from the class or Job.

Please Help and share me If you already got familiar with this code…[:(]

You will be Appreciated…

Thanks in Advance…

2

Where is the procedure? In AX database, on the same server or somewhere completely else?

What exactly is the problem that the existing code (e.g. the snippet provided by myself in Calling SQL Stored Procedure Using X++ CLASS) doesn’t address it for you?

3

Hi Martin,

Many Thanks to you…[:D] I can Able to Execute the Stored Procedure using X++ class.

But, Still one thing I need to do… [:(] How to pass one or more Parameter as a date value to that query.

For Example I have two date variables which is having the date like 2010-01-01 and 2014-03-31.

In stored procedure I have using this date parameters.

The Final thing is How to Pass the two Parameter(date values) to the Stored Procedure using the x++ class sql Query. Please do let me know, if you know about this… And Please send me the query syntax in x++ for passing parameters.

Thanks In Advance…

4

If you’re using the Statement class, you’re sending a single string there, not any additional parameters. All what you need to do is to create a string with the SQL command you want to execute.

5

Hi Martin,

Thank You for your Reply [:D],

I have tried these things differently. Using string Delimiter’s, I got a solution. I can able to pass the parameters like values and variables into Sql Server Stored Procedure,

UserConnection userConnection;

Statement statement;

str sqlStatement;

SqlSystem sqlSystem;

SqlStatementExecutePermission sqlPermission;

ResultSet resultSet;

str 10 strForStartDate,strForEndDate;

strForStartDate =

strFmt(“2010-01-01”****);

strForEndDate =

strFmt(“2014-03-31”****);

sqlSystem =

new SqlSystem();

sqlStatement =

strFmt(‘Exec ImosReport ‘%1’,’%2’’,strForStartDate,strForEndDate) ;

userConnection =

new UserConnection();

statement = userConnection.createStatement();

sqlPermission =

new SqlStatementExecutePermission(sqlStatement);

sqlPermission.assert();

statement.executeUpdate(sqlStatement);

CodeAccessPermission::revertAssert();

Try This [Y]

[:D] [H]

6

Hi ,

I am also working same kind of requirement.

Did you get the solution?

Regards,

krishna

7

Hi,

You can connect SQL Procedure or View or any Query made in PLSQL by creating a class for the same in X++, which will cater data for your report.

How to do it? You can visit my blog on the same.

http://msdax12.blogspot.in/2015/11/how-to-create-ssrs-report-with-using.html

If any thing you find to elaborate more please ask.

Somnath Mukherjee

8

Hi Murali,
Yes I got it. :slight_smile:
Regards,