Call SP and need to save in to NAV Table

subhadynamics

Dear All,

I need to Call the Stored Procedures from another server using C/AL Code in Navision. Also i need to store the datas in to tables.

I have written the code below for Calling SP in Navision.


CREATE(myConnection);
CREATE(myRecordSet);
sSQL:= 'Usp_GetPunchInfo';
myConnection.ConnectionString :=
'Driver={SQL Server};Server=PP\Sql2005;Database=Pplus_Repl;Uid=pplus_repl;Pwd=1234567;';
myConnection.Open();
sSQL:= STRSUBSTNO('EXEC Usp_GetPunchInfo ''%1','%2','2010/03/25','2010/04/24');
myRecordSet := myConnection.Execute(sSQL);
myConnection.Close;

How can i save the datas (which i got using SP from another DB )in to Navision Tables using the above RecordSet.

Thanks in Advance
Subha

kriki

[Topic moved from 'Entrepreneur Solution' forum to 'NAV/Navision Classic Client' forum]

Check this : http://dynamicsuser.net/blogs/waldo/archive/2008/01/06/using-stored-procedures-in-c-side.aspx

subhadynamics

I got some information as i go through the above site.

GetConnectionString(...) : Text[1024]

ltxtConnectionString:='Driver={SQL Server};'
+ 'Server='+pcodServerName+';'
+ 'Database='+pcodDatabaseName+';'
+ 'Uid='+pcodUserID+';'
+ 'Pwd='+pcodPassword+';';

EXIT(ltxtConnectionString

How can i bring the connection details in Navision from outside .May be future we will change the detatils. So this need to be dynamic.

Hi can i achieve this in Navision.

Thanks

Subha

Luc_VanDyck

subhadynamics wrote:

How can i bring the connection details in Navision from outside .May be future we will change the detatils. So this need to be dynamic.

Then you'll have to store these details in a txt-file, and read this file through NAV.

You can't store these details into your SQL db, as you need this info to connect to that SQL server ;-)

subhadynamics

Hi,

Thanks for your Suggetions
please let me know which method i follow to read the text file through Navision.

Thanks
subh

Luc_VanDyck

Follow this link: http://lmsmfy.com/?q=read+text+file

subhadynamics

Hi,

ltxtConnectionString:='Driver={SQL Server};'
+ 'Server='+pcodServerName+';'
+ 'Database='+pcodDatabaseName+';'
+ 'Uid='+pcodUserID+';'
+ 'Pwd='+pcodPassword+';';

The string is retrun a wrongly.So While i was trying to make connection , I got the following error.


The call to member Open failed. Microsoft OLE DB Provider for ODBC Drivers returned the Error message:
can u help me .

subhadynamics

The string is wronly taken like this..


"Driver={SQL Server};Server=Sql2005;Database=PP_Repl;Uid=PP_repl;Pwd=1234567;"

So it is getting error.

actually i want the string like this


'Driver={SQL Server};Server=Sql2005;Database=PP_Repl;Uid=PP_repl;Pwd=1234567;';

Luc_VanDyck

Search this forum on connectionstring and you'll find some working examples.

subhadynamics

Hi All

how to store a database connection string in the application's like (asp.net Web.Config file. )
Is there any way to store the file in Navision dynamically.

Thanks
Subh

Luc_VanDyck

You can store the details in some fields in a NAV table. That's the only option you got if you don't want to hardcode these values in your codeunits.