C/ODBC + Change Company Name

GeneralPlanet

Hello,

I'm connecting to a Navision Database with C/ODBC driver. That works fine!

But than I try to change the company and get the following error message:

[Navision Software a/s][Navision Financials ODBC Driver]Data source rejected establishment of connection

First I set the Company Name in my connection string like this:

' Company1
SET DB = Server.CreateObject("ADODB.Connection")
DB.Open "Provider=MSDASQL.1;Extended Properties=""DSN=Navision;CN=Company1"""
DB.Close
Set DB = Nothing

' Company2
SET DB = Server.CreateObject("ADODB.Connection")
DB.Open "Provider=MSDASQL.1;Extended Properties=""DSN=Navision;CN=Company2"""
DB.Close
Set DB = Nothing

All other params I defined in the System-DSN.

Second I try to use two different DSN entries:
SET DB = Server.CreateObject("ADODB.Connection")
DB.Open "Navision_C1"
DB.Close
Set DB = Nothing

SET DB = Server.CreateObject("ADODB.Connection")
DB.Open "Navision_C2"
DB.Close
Set DB = Nothing

But it's the same. For the second connect I get the error message.

Anyone know a solution?

MattRowe

Hi Sebastian,

C\ODBC doesn't allow you to change companies programmatically while an existing connection to another company already exists.

I found that killing a connection via VBScript in an ASP page (which is what I assume is what you are trying to do) impossible, but then I'm no expert when it comes to web programming.

Interestingly enough, SQL Server's Data Transformation Services can quite happily extract data from two different companies at the same time (providing you use separate DTS Packages for the extractions), so it must be possible.

My solution centre has been able to create web based content through ASP pages, but you have to log on and off to each company from a separate web page.

Probably the best solution to your problems would be to upgrade your Navision solution to one that actually runs on MS SQL Server. In the implementation that I had a look at, all the tables are prefixed with the company's name (effectivley doubling etc up the number of tables). This will allow you to use OLE DB for SQL Server, which is a better solution than C\ODBC and you don't get the multi-company problems associated with Navision Server and C\ODBC.

Alternativley, Visual Basic 6 Professional allows you to create IIS applications, which in turn gives you all the power of a full programming language (rather than a subset language like VBScript) to use in your web applications. Using VB6 will give you greater control over opening and closing connections to different companies.

Sorry I can't be of more help. In the end I went for a Data Warehousing solution using SQL Server's built in tools to extract the data from Navison Server, create the Marts or Warehouses and then Crystal Reports and Crystal Analysis to provide the information to users over our intranet. This works really well because it also allows me clean up the data as part of the transformation process.

If you want some tips on how to do this I'd be happy to help.

Regards

Matt

GeneralPlanet

Hi Matt,

thank you for your detailed reply. It is really the fact, that ADODB didn't kill the connection, after the 'close' command! I spoke with someone from Navision Germany and he confirmed that.

Unfortunatly we can't upgrade to the SQL-Version because some features didn't work their.

Now I use DTS-Packages to transform/copy the Data form Navision-Server to a SQL-Server. It's a cool feature and everything works fine! Connecting to these DB's from ASP is no problem and I was able to use all the nice SQL-Server stuff like: Views, Diagrams, Stored Procedures,...

Another way I thought about was to use c/front. Implementing all DB and Table functions I'll need and build a Servercomponent, that I can use with regsrv32 in ASP. But that's a hard way...

Thx again for your help. I really appreciate it!

Sebastian