Calling SQL Server 2005 Stored Procedures

shibily

Hi,
I am new to Navision, I have SQL server Knowledge.
Can anyone please tell me how to call a storedprocedure from
Navision.. Which are the global variables i need to create..



Thanks in advance

Shibily.

garak

Search the forum for "ADO Connection" or take a look into this tool http://www.mibuso.com/forum/viewtopic.php?t=23204

Here you can see, how to connect to SQL Server with C/AL Code and execute SQL Commands (Select, backup, EXEC YourProcedur,etc)

Regards

kriki

[Topic moved from Navision forum to SQL General forum]

Marije_Brummel

Or have a look at www.waldo.be. This explains into details.

Waldo

8)

I have put it on Tips&Tricks on this site as well: Using ADO & Stored Procedures in C/SIDE

But indeed, here is the link to my (identical) blogpost.

Hope it's useful.

shibily

Waldo,

I tried ur code , But i am getting an error message..

I used the follwing code in a button Push event.

---

IF ISCLEAR(LADOConnection) THEN CREATE(LADOConnection);

LADOConnection.ConnectionString:='Driver={SQL Server}; Server=VMWIN2K3\SQLEXPRESS;Database =astDB;Uid=sa;pwd=ast';

LADOConnection.Open;

IF ISCLEAR(lADOCommand) THEN
CREATE(lADOCommand);


lvarActiveConnection := LADOConnection;
lADOCommand.ActiveConnection := lvarActiveConnection;

lADOCommand.CommandText := 'SP_testShib';
lADOCommand.CommandType := 4;
lADOCommand.CommandTimeout := 0;

lADOCommand.Execute;

LADOConnection.Close;
CLEAR(LADOConnection);

I got 2 errors.

Error1 is :- " The Call to member Execute failed. Could not find Stored procedure 'SP_testShib'.
But i have created a stored procdure and is running perfectly thro' backend.

And if i click the button again, another ADODB error comes
" The Call to member Connection String failed.
Operartion is not allowed when the object is open ".

This error message comes every time when i click the button there after.
The first error comes only in the first click .

How can i solve this??

Thanks

shibily

Waldo

The second one is easy. After the error, the connection was not closed. After that, you try to open the connection again, which results into the error.

The first one is something else. Your code looks fine, so I must guess some things ... .
- Is the SP in the database you're connecting to
- try to remove the space after the Database-keyword in the connectionstring
- Are you working case sensitive collation and is your SP name in the right case(s)
- ... ?

shibily

Waldo wrote:

The second one is easy. After the error, the connection was not closed. After that, you try to open the connection again, which results into the error.

The first one is something else. Your code looks fine, so I must guess some things ... .
- Is the SP in the database you're connecting to
- try to remove the space after the Database-keyword in the connectionstring
- Are you working case sensitive collation and is your SP name in the right case(s)
- ... ?

Hi,
I checked everything ..
There is no space in the connection string. i pasted the same SP name . Still i am facing the same issue...

Thanks

Shibily

Waldo

I just copy/pasted fragments from my blog ... and it works .
It is hard to help you on this when I do not have the exact code .. . May be you can share it? SP and C/AL?

shibily

Waldo wrote:

I just copy/pasted fragments from my blog ... and it works .
It is hard to help you on this when I do not have the exact code .. . May be you can share it? SP and C/AL?

Hi,
Finally , I succesfully called the Stored Procedure from navision. I used the same fragments from Waldo 's blog. Dont know why it didnt work earlier. Anyways thanks all for your support.


Shibily

Waldo

Glad to have been confirmed the blog-samples work