Call a Stored Procedure from Navision Code

Holly
Holly Member Posts: 18
edited 2006-10-13 in NAV/Navision Classic Client
Hi all

Version 4, SQL Server option

I am working with a colleague on an external app for use in our company. This App will need to get data from Navision and Navision will have to get data from the App.

The App is being written in VB.Net and Sql Server. We want to write a Stored Procedure that selects from both the new App's db and from the Navision db. Then both Navision and the App can call the same Stored Procedure to get information.

Does anyone have any idea how I would go about calling a SQL Stored Procedure from within Navision? Any help would be greatly appreciated.

Comments

  • Miklos_Hollender
    Miklos_Hollender Member Posts: 1,598
    Just search for VB6 examples on the Internet and transform it to Navi similar way as basic VB6 ADO is transformed to Navi here: http://www.mibuso.com/dlinfo.asp?FileID=589
  • Holly
    Holly Member Posts: 18
    Thank You very much for your quick reply. I'll look at it right now.

    Thanks again.
  • Toddy_Boy
    Toddy_Boy Member Posts: 232
    I'm trying to achieve something similar but with ASP.NET calling a stored procedure to extract data from navision and display it on the intranet. I've written a stored procedure to return the data and saved it in the Navision database.

    The stored procedure works when called from Query Analyser and from a VB6 program, it also works as a data source in the ASP.NET IDE.

    However the stored procedure doesn't seem to execute when the website runs in a browser.

    Any ideas whilst I have hair left ? ](*,)
    Life is for enjoying ... if you find yourself frowning you're doing something wrong
  • Phenno
    Phenno Member Posts: 630
    Toddy Boy wrote:
    I'm trying to achieve something similar but with ASP.NET calling a stored procedure to extract data from navision and display it on the intranet. I've written a stored procedure to return the data and saved it in the Navision database.

    The stored procedure works when called from Query Analyser and from a VB6 program, it also works as a data source in the ASP.NET IDE.

    However the stored procedure doesn't seem to execute when the website runs in a browser.

    Any ideas whilst I have hair left ? ](*,)

    chech Profiler to see what errors it gives back. My guess is Permissions. Do you know what is the username for connection that you make through web?
  • Toddy_Boy
    Toddy_Boy Member Posts: 232
    Im awaiting a little quiet time before I can see what the profiler is giving me :)

    This is getting more bizarre, if I call the stored procedure in asp.net i.e. not as part of the binding but in the page load) it works !! :( Surely that runs under the same security as if the stored procedure is called from the data bound object.
    Life is for enjoying ... if you find yourself frowning you're doing something wrong
  • Toddy_Boy
    Toddy_Boy Member Posts: 232
    Cracked it \:D/

    The stored procedure has 3 parameters 2 of which are optional. In the .NET IDE the parameters for the 2 optionals have default values of a blank string. When this is executed the stored procedure failed to run.

    However, if I include the ConvertEmptyStringToNull="false" on the 2 optional select parameters then shockingly the procedure works. =D> =D> =D>

    Does Navisions SQL Server installation have some magic that stops NULLs beign passed to stored procedures? :-k

    Cheers
    Life is for enjoying ... if you find yourself frowning you're doing something wrong

Categories