One Database or More

A bit of advice needed, as I'm having trouble figuring out whether I need to create multiple SQL Server databases or just one...or if it is a matter of choice.

Say I'm creating Website A that does one thing.

And Website B that does another thing.

But each one has common core underlying tables (customers, cargos, ports, etc) that they both use.

Am I best just creating one database or several? Then am I best creating two or three (A,B and core tables)?

Not that used to SQL server at the moment, and this will be a complete backend for the companies main core business, crm, quality, etc - all distinct apps so to speak, but the data has common underlying tables and they will want to cross-reference data.::Am I best just creating one database or several?

This absolutly and 100% depends on whether you want them or not.

In this case:

::Not that used to SQL server at the moment, and this will be a complete backend for the
::companies main core business, crm, quality, etc - all distinct apps so to speak,

No, these are NOT distinct apps. They are all parts of one suite running the company. In this case it is absolutly best to run all this:

* From one database
* with ONE SET OF BUSINESS OBJECTS.|||Yes - part of one suite. Thanks.

Oledb/Data Access Problems

Hi all,

I am going a bit crazy here. To give you my back ground I have done quite some work on SQL server 2000, VFP, VB6 and done quite a few web pages using frontpage. Now I am venturing into aspnet and have what seems to be some data access problems.

I got a win2k professional web server and its running/has been running my frontpage web pages quite nicely either accessing them as //localhost/myweb or www.mydomain/myweb.

Now I made some web pages in Visual Web Developer express to get my "feet wet" So I installed SQL 5.0 express on that machine, .net2.0 and VWD express and vfpoledb. I got 2 web sites designed. One to explore the login feature. Made a default web page and 2 more pages on which I display some SQL grid views from a local db I set up. Site works fine both in the test environment and also on the webserver accessed over the web. Then I added a login page with a login control and configured the site for "web Access", set up 3 test users and pw, no roles and configured the access rules to annonymous denie and all the users allow. Works great out of the VWD environment (view in browser) but when run from the web no PW is ever correct.

2nd problem on 2nd site.

Access to a VFP9.0 table via OLEDB works great again out of the design environment (View in browser) but then run from the wwroot./vfptest directory it comes up with an OLEDB error indicating it cannot find the file

any ideas?

Could you provide more details, exact error message, connection string, etc.?

Thanks

|||

I found the solution to all the problems above

Go to IIS and go to the ASPNET configuration and application tab and set the "Local impersonation" check mark and put in a user that can access the SQL or other files

OLEDB connection error with (win 64 bit server)

Hi

Im trying to connecting from SQL 2005 (win 64bit server) to a Oracle database via OLEDB but get the following message. The OraOLEDB.Oracle.1 provider is not registered on the local machine.

If we try ODBC we get the following message "error in initializing provider. Attemt to load Oracle client libraries threw BadImageFormatException.This problem will occur when running in 64bit mode whit the 32 bit Oracle Client components installed."

It should be the 64 bit client that is installed.

In the test environment (32 bit win) both works.

Pleas help

Rickard

One of the following two should work -

1) Get a Oracle client installed that is 64-bit compliant (http://www.oracle.com/technology/tech/windows/faq.html#x86-64)

2) Compile your application as "x32" instead of "anycpu" or "x64" and it will work

Useful link : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=457875&SiteID=1

Let me know if you require more information

Best

Sriram

OLEDB connection error with (win 64 bit server)

Hi

Im trying to connecting from SQL 2005 (win 64bit server) to a Oracle database via OLEDB but get the following message. The OraOLEDB.Oracle.1 provider is not registered on the local machine.

If we try ODBC we get the following message "error in initializing provider. Attemt to load Oracle client libraries threw BadImageFormatException.This problem will occur when running in 64bit mode whit the 32 bit Oracle Client components installed."

It should be the 64 bit client that is installed.

In the test environment (32 bit win) both works.

Pleas help

Rickard

One of the following two should work -

1) Get a Oracle client installed that is 64-bit compliant (http://www.oracle.com/technology/tech/windows/faq.html#x86-64)

2) Compile your application as "x32" instead of "anycpu" or "x64" and it will work

Useful link : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=457875&SiteID=1

Let me know if you require more information

Best

Sriram

OLE DB Provider Oracle

english:

Hello,

I have got a problem with the a linked server in SQL server 2005 (64 bit) to Oracle 8.0.5. (32 bit).

Further details:

The old existing SQL server 2000 (32 bit) has a linked server to Oracle 8.0.5. The works with the OLE DB provider from Microsoft. The new SQL server 2005 (64 bit) should also have a linked server to this Oralce database. I have installed the Oracle 8.0 clients on the new server. The connection to oracle works with the client tools from Oracle.

But the SQL server 2005 couldn't connect to the oracle database.

The error message from the SQL server sounds (translated form a german error message):

A instance of the OLE DB provider "OraOLEDB.Oracle" for the linked server "xxxxxx" can't create.

Is the Oracle client software / OLE DB provider the problem?

Exist an other OLE DB provider for Oracle, may be from Microsoft, which works?

Thanks for your help

german:

Hallo Zusammen,

ích habe ein Problem bei der Migration von SQL Server 2005 auf ein x64 Windows System.

Problem ist folgendes:
Auf dem alten Server Windows x32 lief SQL Server 2000 mit einem Verbindungsserver zum Oracle Server 8.05. Das funktionierte mit dem OLE DB treiber von Microsoft hervoragend. Nun haben wir aber eine neue Windows x64 Maschine und ich m?chte die Verbindung zum Oracle Server wieder nutzen. Hatte die Oracle Client Software installiert und bekomme eine Fehlermeldung:

Eine Instanz des OLE DB Anbieters "ORAOLEDB.ORACLE" für den Verbindungsserver "xxxxx" kann nicht erstellt werden.

Ist das ein Problem mit der Oracle Client Software bzw. dem zugrunde liegenden OLE DB Treiber?
Gibt es einen anderen OLE DB Treiber für Oracle, z.B. von Microsoft, der funktionieren k?nnte?

Bin schon l?nger damit besch?ftigt, das zum Laufen zu bekommen. Die Oracle Version ist nicht die neueste, ich wei?.
Was kann ich tun, damit das endlich l?uft.

Für Hilfen w?re ich sehr dankbar.

If you are running 64-bit SQL, you need to use a 64-bit OLEDB provider with linked servers.

So you need to get Oracle's 64-bit OLEDB Provider for Oracle, Microsoft does not provide a 64-bit OLEDB Provider for Oracle.

|||

I got it mostly working with the latest oracle client (10.2.0.3) which you can download from Oracle if you have support with them. If you don't have the .3 version, you'll encounter a bug with their client if you make connections in DTS / intergration services packages to the Oracle DB server. I'm still running into an issue with a remote link server (see my other post).

Here are the steps I did to make the client work. Hope this helps you.

1) To get it working you need to do a custom installation and select at minimum:

Oracle Windows Interfaces (all)

Oracle Database Utilities

SQL*Plus

Oracle Net

2) You then need to copy the files: tnsnames.ora AND sqlnet.ora from another DB server to where ever the Oracle client is installed. In my case it's located at: D:\oracle\product\10.2.0\client\NETWORK\ADMIN

3) You then need to modify the registry to change the dll's that MDAC uses to connect to Oracle:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI

OracleXaLib=oraclient10.dll

OracleSqlLib=orasql10.dll

OracleOciLib=oci.dll

4) You then need to reboot to have the changes take effect.

5a) Browse to: /Server Objects/Linked Servers/Providers and right click on OraOLEDB.Oracle and select properties. Then check the following boxes: Dynamic parameter, Allow inprocess

5b) Then you need to create the link server in the Microsoft SQL Server Management Studio by doing the following:

Browse to: Server Objects/Linked Servers, right click and select "New Linked Server"

Enter the linked server name

Under provider select "Oracle Provider for OLE DB"

Enter a product name IE Oracle...

Enter the data source name (the same name in the tnsnames.ora file)

Select "Security" on the left side and select the last radio button "Be made using this security context" and enter the user name and password to connect to the remote db.

6) Then click OK :)

|||

Hello Arron,

thanks for the information.

I have installed the oracle version 10.2.0.1.0. That was the latest that I found on http://www.oracle.com/technology/software/products/database/oracle10g/index.html for Windows with 64-bit.

We haven't got a support contract with oracle, because it is an old version.

We wouldn't use DTS or integration services (would be nice if it's working also).
We need this only in stored procedures with SQL statements (SELECT, INSERT and UPDATE).

Stefan and MediaK

OLE DB Provider Oracle

english:

Hello,

I have got a problem with the a linked server in SQL server 2005 (64 bit) to Oracle 8.0.5. (32 bit).

Further details:

The old existing SQL server 2000 (32 bit) has a linked server to Oracle 8.0.5. The works with the OLE DB provider from Microsoft. The new SQL server 2005 (64 bit) should also have a linked server to this Oralce database. I have installed the Oracle 8.0 clients on the new server. The connection to oracle works with the client tools from Oracle.

But the SQL server 2005 couldn't connect to the oracle database.

The error message from the SQL server sounds (translated form a german error message):

A instance of the OLE DB provider "OraOLEDB.Oracle" for the linked server "xxxxxx" can't create.

Is the Oracle client software / OLE DB provider the problem?

Exist an other OLE DB provider for Oracle, may be from Microsoft, which works?

Thanks for your help

german:

Hallo Zusammen,

ích habe ein Problem bei der Migration von SQL Server 2005 auf ein x64 Windows System.

Problem ist folgendes:
Auf dem alten Server Windows x32 lief SQL Server 2000 mit einem Verbindungsserver zum Oracle Server 8.05. Das funktionierte mit dem OLE DB treiber von Microsoft hervoragend. Nun haben wir aber eine neue Windows x64 Maschine und ich m?chte die Verbindung zum Oracle Server wieder nutzen. Hatte die Oracle Client Software installiert und bekomme eine Fehlermeldung:

Eine Instanz des OLE DB Anbieters "ORAOLEDB.ORACLE" für den Verbindungsserver "xxxxx" kann nicht erstellt werden.

Ist das ein Problem mit der Oracle Client Software bzw. dem zugrunde liegenden OLE DB Treiber?
Gibt es einen anderen OLE DB Treiber für Oracle, z.B. von Microsoft, der funktionieren k?nnte?

Bin schon l?nger damit besch?ftigt, das zum Laufen zu bekommen. Die Oracle Version ist nicht die neueste, ich wei?.
Was kann ich tun, damit das endlich l?uft.

Für Hilfen w?re ich sehr dankbar.

If you are running 64-bit SQL, you need to use a 64-bit OLEDB provider with linked servers.

So you need to get Oracle's 64-bit OLEDB Provider for Oracle, Microsoft does not provide a 64-bit OLEDB Provider for Oracle.

|||

I got it mostly working with the latest oracle client (10.2.0.3) which you can download from Oracle if you have support with them. If you don't have the .3 version, you'll encounter a bug with their client if you make connections in DTS / intergration services packages to the Oracle DB server. I'm still running into an issue with a remote link server (see my other post).

Here are the steps I did to make the client work. Hope this helps you.

1) To get it working you need to do a custom installation and select at minimum:

Oracle Windows Interfaces (all)

Oracle Database Utilities

SQL*Plus

Oracle Net

2) You then need to copy the files: tnsnames.ora AND sqlnet.ora from another DB server to where ever the Oracle client is installed. In my case it's located at: D:\oracle\product\10.2.0\client\NETWORK\ADMIN

3) You then need to modify the registry to change the dll's that MDAC uses to connect to Oracle:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI

OracleXaLib=oraclient10.dll

OracleSqlLib=orasql10.dll

OracleOciLib=oci.dll

4) You then need to reboot to have the changes take effect.

5a) Browse to: /Server Objects/Linked Servers/Providers and right click on OraOLEDB.Oracle and select properties. Then check the following boxes: Dynamic parameter, Allow inprocess

5b) Then you need to create the link server in the Microsoft SQL Server Management Studio by doing the following:

Browse to: Server Objects/Linked Servers, right click and select "New Linked Server"

Enter the linked server name

Under provider select "Oracle Provider for OLE DB"

Enter a product name IE Oracle...

Enter the data source name (the same name in the tnsnames.ora file)

Select "Security" on the left side and select the last radio button "Be made using this security context" and enter the user name and password to connect to the remote db.

6) Then click OK :)

|||

Hello Arron,

thanks for the information.

I have installed the oracle version 10.2.0.1.0. That was the latest that I found on http://www.oracle.com/technology/software/products/database/oracle10g/index.html for Windows with 64-bit.

We haven't got a support contract with oracle, because it is an old version.

We wouldn't use DTS or integration services (would be nice if it's working also).
We need this only in stored procedures with SQL statements (SELECT, INSERT and UPDATE).

Stefan and MediaK

OLE DB drivers and Vista 64 bit

I am trying to load a Fox Pro file which I used to be able to load before I installed 64 bit Vista. I'm using ver 9 of the fox pro driver. I don't know if it's a driver problem or something else I am doing wrong.

Here's the error msg:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154.

An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

Error: 0xC020801C at Load Country, OLE DB Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "C:\Users\john.church.LLC\Desktop\MaxMin\AccountingSystem Database\AccountingSystem Database\AccountingSystem.DBC" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

But then it also says something about registering a class. I'm a .net moron so I'm not sure what that means.

Any thoughts?

Thanks.

(Vista 64, SQL Svr 2005 64, visual studio - with all known updates/patches installed)

Hi John,The VFP OLE DB data provider isn't 64-bit compatible.|||"Does Microsoft have a 64 bit driver?" he asked, laughing and shaking his head.|||Not for Visual FoxPro, and there are no plans to create one.|||Kinda what I figured. Now my only decision is whether to go back to Vista 32 or XP.........has MS announced when they're going to cancel Vista yet?