Stored Procedure

Horse06

Hi expert. I have installed Navison server and SQL server. Now what I need to do is to add the extended store procedure. I tried to locate it, but cannot find it anywhere. Could somebody know where normally it is located? Appreciate it!!!!

DenSter

It's usually located in a folder called 'SQL_ESP', which should also have a readme.txt with instructions.

That folder is on the 5.0 SP1 disc, for other versions it might be in other folders. Search it for "xp_ndo*" and you should find it.

Horse06

Thank you! I have found it in CD and installed it. Do I have to configure it from enterprise manager of SQL Server?
I read the manual like below:
You can use a Microsoft tool such as Enterprise Manager to add the xp_ndo.dll file (Is it necessary to add it?) to the extended stored procedures already installed on SQL Server. The name of the
extended stored procedure must be xp_ndo_enumusersids. Where I can find xp_ndo_enumusersids? Appreciate it!!

Horse06

Hi, expert! I have anothe question. I am installing the navision server on another station and use the same licence file as our production one, does it affect the operations since I am doing it for a testing purpose because we are upgrade the service pack? Appreciate it!!!

DenSter

Horse06 wrote:

Thank you! I have found it in CD and installed it. Do I have to configure it from enterprise manager of SQL Server?
I read the manual like below:
You can use a Microsoft tool such as Enterprise Manager to add the xp_ndo.dll file (Is it necessary to add it?) to the extended stored procedures already installed on SQL Server. The name of the
extended stored procedure must be xp_ndo_enumusersids. Where I can find xp_ndo_enumusersids? Appreciate it!!

The readme.txt really explains every detail on how to do it, so check that. I used to add them by hand, but I'm using a script now, I'd post it but I don't have access to it right now. Search around for this on mibuso. There are a number of posts with scripts that you can run from a query window.

DenSter

Horse06 wrote:

Hi, expert! I have anothe question. I am installing the navision server on another station and use the same licence file as our production one, does it affect the operations since I am doing it for a testing purpose because we are upgrade the service pack? Appreciate it!!!

"The Navision Server"? What do you mean by that? What version are you using?

Horse06

I am using 4.0. But this installation is just for testing purpose only. Appreciate it!!

DenSter

Ah ok so you're asking if you can use your production license for testing purpose on a different machine? Yes I think that is allowed under the license agreement. You only need to purchase another license if you have multiple production servers. This can get a little complicated, so if you need that get in touch with your partner about this.

Horse06

I read the notes in the SQL_ESP folder, but I cannot get it work. Here is what I did as below:

1. Extract xp_ndo.dll to the Binn
2. Manually add the xp_ndo_enumusersids to extended stored procedure. Below is How I add xp_ndo_enumusersids to the Extended stored procedure. Is it correct? Please advise!

Select the Extended Stored Procedures of the Master in the Enterprise Manager. From Action Menu select New Extended Store Procedure.
Enter the Name as xp_ndo_enumusersids, then from the path, select the file XP_NDO.DLL from the BINN of the SQL Server. Is it a correct way to create xp_ndo_enumusersids?

2. Create the new database from the Enterprise Manager and configure the ndf, mdf and ldf
4. When I connect the client to the database, it gives an error as below:
1845s, "28000", [Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user 'admin. Reason: Not associated with a trusted SQL Server Connection.

How to fix it? Appreciate for your help!!!!!

Horse06

When I created a database in the SQL Server Option, I Click File Database, New, but cannot find the Server window, instead I find NEW DATABASE. So I set it up directly form the NEW DATABAE MENU. Why can I not see the Server Window. Maybe the setup in this step is wrong.

Appreciate it!!!

DenSter

Horse06 wrote:

Is it a correct way to create xp_ndo_enumusersids?

You did not follow the instructions in the readme file. You need to follow those instructions to the letter, in the same order. Make sure you create both ESP's, and make sure you set the right permissions, for which there are also instructions in the readme file.

Horse06 wrote:

Create the new database from the Enterprise Manager and configure the ndf, mdf and ldf

If you intend to restore from a NAV backup file, you need to create the new database from the NAV client. If you have a SQL Server backup, then you create it in SSMS.

Horse06 wrote:

When I connect the client to the database, it gives an error as below:
1845s, "28000", [Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user 'admin. Reason: Not associated with a trusted SQL Server Connection.

Your SQL Server looks like it is not set up to allow remote connections. You do this in SQL Server Configuration Manager.

DenSter

Horse06 wrote:

When I created a database in the SQL Server Option, I Click File Database, New, but cannot find the Server window, instead I find NEW DATABASE. So I set it up directly form the NEW DATABAE MENU. Why can I not see the Server Window. Maybe the setup in this step is wrong.

I don't know what you mean by "server window". When you click file, database, new, you have to enter credentials to access a SQL Server instance, and when those are authorized, THEN you get the new database wizard. If you get to the New Database wizard then you have successfully accessed SQL Server, and you are on your way to create a new database.

You need to read the document called 'w1w1isql.pdf' in the doc folder, this will tell you all about how to create and set up NAV databases on SQL Server.

Horse06

Thank you! I follow the instructions of w1w1adg. Any difference?

Savatage

DenSter wrote:

You need to read the document called 'w1w1isql.pdf'

Horse06 wrote:

Thank you! I follow the instructions of w1w1adg. Any difference?

Even tho the Application Designers Guide has some info on Server Options.
I Believe Installation & System Management: SQl Server Option for the C/Side Client will give you more details.

if you dn't have it it can be found here:
http://www.microsoft.com/downloads/deta ... laylang=en

Horse06

Still I cannot login and get the same error.
How to set it up to allow remote connections in SQL Server Configuration Manager? Appreciate it!!

DenSter

Horse06 wrote:

Thank you! I follow the instructions of w1w1adg. Any difference?

Any difference in what? Why don't you open the document and see for yourself.

DenSter

Horse06 wrote:

Still I cannot login and get the same error.

That's because for some reason you refuse to read that document. It has all the answers, I know because I just went through it and I can see all the answers that you need are in there. Do a little research, put some effort in figuring this out.

Horse06

Thank you expert! I did read the manual since yesterday. This is the first time for me. I exactly followed the instructions and I am stuck. I have done everyhthing so far and below is what I have done following the instructions.

Your help will be greatly apprecated!

1. Iinstalled Navision and SQL Server on one station. I have followed the instructions to configure everything.

From your server computer, access the Microsoft Business Solutions-Navision
Installation CD-ROM. In the folder $:\SQL_ESP, where the
$ is the CD-ROM drive, click the file xp_ndo.exe. When
prompted, enter the path to the BINN subfolder of the
SQL Server installation folder. (The unzipped xp_ndo.dll
file must be in this folder on SQL Server.)

When Navision connects to the server for
the first time, the program adds the extended stored
procedure automatically.

If the program detects that the server has been accessed
by Navision, it does not automatically add the
extended stored procedure: you must add the extended stored
procedure manually. To do so, follow this procedure:

Using a Microsoft tool such as Enterprise Manager, add the
file to the extended stored procedures already installed on
SQL Server. (The name of the extended stored procedure must
be xp_ndo_enumusersids.)

After the extended stored procedure has been successfully added
to the server, you must grant execute permissions to the "public"
role by following this procedure:
- Open Enterprise Manager.
- Expand the server and expand the databases.
- Expand the master database and the Extended Stored Procedures.
- In the right side panel, right-click the xp_ndo_enumusersids and
select Properties.
- Select Permissions and grant EXEC rights to
both "public" and "guest.
2. Navision Client.
From the File Database and then New. I added teh ServerName, Authentication (Database Server Authentication), I put my user Name and password (I don't know if I need to set it up on the enterprise Manager since the manual didn't say anthing about it, but I still got the password set up on the the sql Server machine), but got the error message.

18452, "28000", [Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user 'admin. Reason: Not associated with a trusted SQL Server Connection.

Please help, expert!!

bbrown

DenSter wrote:

Horse06 wrote:

Create the new database from the Enterprise Manager and configure the ndf, mdf and ldf

If you intend to restore from a NAV backup file, you need to create the new database from the NAV client. If you have a SQL Server backup, then you create it in SSMS.

If you're restoring from a SQL Server backup, you don't need to create the database.

DenSter

bbrown wrote:

If you're restoring from a SQL Server backup, you don't need to create the database.

Yes I know that you don't create the database first and then restore, but you do 'create the database' in SSMS. I don't want to be bogged down with technical details at this point, we're not even past square one here.

DenSter

Horse06 wrote:

18452, "28000", [Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user 'admin. Reason: Not associated with a trusted SQL Server Connection.

Please help, expert!!

You copied the content of the readme in there, but that's not where the error message comes from. You need to study the installation manual, which is the 'w1w1isql.pdf' document I keep telling you about. This document will tell you how to set up SQL Server for accessing NAV databases. The error you are seeing is not related to the extended stored procedures, your SQL Server needs to be set up to allow remote connections.

And yes this manual DOES explain how to set up users, with Windows authentication as well as database authentication.

I'm not trying to be difficult here, but if you want to take ownership of your installation, you need more than just a "one error at a time" approach by posting them on a forum, and to get there you need to at least study the installation manuals and know what is in there. I think you should also get your NAV partner involved to show you how to set this up.

DenSter

The first entry for a google search for "Not associated with a trusted SQL Server Connection":
http://support.microsoft.com/kb/889615

You are using database logins, but your SQL Server instance is probably set up only for Windows users. This information is all in the 'w1w1isql.pdf' document.

Horse06

Thank you DenSter! I finally got everything set up. All the data and object are successfully imported. Thank you for everyone's input and time! Very appreciate it!!
But I still have one more and it is the last one. Why I can I not see the database from the Enterprise Manager of SQL Server? How to configure it? Appreciate it!!!

Horse06

Thank you! I figured it out!