Hello:
In SQL Server 2000 database I have a table MyTable which is updated daily
with previous day data. In some reason data could be missing for particular
day. To control the situation I'd like to create alert message to be sent to
me by e-mail.
The verification SQL statement is:
IF (select DATEDIFF ( day , max(datadate) , GetDate() )from MyTable) >1
print 'ALERT!'
How can I get the verification result by e-mail?
Thanks,
GBThere are generally three ways to send email from SQL 2000:
1. Install Outlook on the server and configure SQL Mail. You can then
use xp_sendmail to send emails.
2. Use xp_smtp_sendmail: http://www.sqldev.net/xp/xpsmtp.htm
3. Use a command-line mailer such as "Blat" in combination with
xp_cmdshell
I've used all three, and all three work well.
GB wrote:
> Hello:
> In SQL Server 2000 database I have a table MyTable which is updated daily
> with previous day data. In some reason data could be missing for particula
r
> day. To control the situation I'd like to create alert message to be sent
to
> me by e-mail.
> The verification SQL statement is:
> IF (select DATEDIFF ( day , max(datadate) , GetDate() )from MyTable) >1
> print 'ALERT!'
> How can I get the verification result by e-mail?
> Thanks,
> GB|||Thank you, xp_sendmail is working OK.
GB
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1149797645.683037.46670@.i39g2000cwa.googlegroups.com...
> There are generally three ways to send email from SQL 2000:
> 1. Install Outlook on the server and configure SQL Mail. You can then
> use xp_sendmail to send emails.
> 2. Use xp_smtp_sendmail: http://www.sqldev.net/xp/xpsmtp.htm
> 3. Use a command-line mailer such as "Blat" in combination with
> xp_cmdshell
> I've used all three, and all three work well.
>
> GB wrote:
daily
particular
sent to
>
Showing posts with label previous. Show all posts
Showing posts with label previous. Show all posts
Tuesday, March 20, 2012
Sunday, February 19, 2012
After SQL 2005 SP2, Query to Apartment Linked Servers fails
We have a linked server that is an Apartment model OLE DB Provider. It works fine on SQL 2005 SP1 and previous versions.
After applying SP2, we get the following error message when running a query against the linked server.
I cannot find anything in the SP2 documentation that indicates a change of behavior for linked servers.
Any ideas ?
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'XXX.XXXXX' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
TobyHi, I have the same problem with MSDAORA when I try to execute a query using a Linked Server with Oracle Server."Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
"
Someone can help us?
Thanks
Denis
|||
I'm not in a position to answer about the change in the linked server code, but perhaps it is possible to reconfigure the appartment model.
What do you have under HKEY_CLASSES_ROOT\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32\ThreadingModel?
Can you try setting it to "Both" and see if you can connect?|||Ditto,
Did you find a solution?
Thanks.
|||Did you try changing apartment model? Does it not work for you?|||No, Basically I found out that the 32 bit OLEDB Provider will not work correctly on 64Bit Server 2003 with SQL 2005 SP2 64Bits. I ended up installed the Oracle 64Bit Provider and use OraOLEDB.Oracle provider.
Here's the complete thread:
I'm trying to get SQLServer 2005 to link to an Oracle 10g Database.
The OS is Server 2003 SP2 64Bit.
The Microsoft OLE DB For Oracle driver is installed (MSDAORA) but doesn't appear in SQL Server Providers.
It's a 32 Bit provider.
Since it didn't appear, I used T-SQL sp_addlinkedserver (http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.
When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.
After that, when executing some T-SQL against Oracle, I got a new error:
http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.
When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.
After that, when executing some T-SQL against Oracle, I got a new error:
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
But the registry shows:
[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA"
"OLEDB_SERVICES"=dword:ffffffff[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors]
@.="Extended Error Service"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors\{e8cc4cbf-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA ErrorLookup"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32]
@.="C:\\Program Files (x86)\\Common Files\\System\\Ole DB\\msdaora.dll"
"ThreadingModel"="Both"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\OLE DB Provider]
@.="Microsoft OLE DB Provider for Oracle"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ProgID]
@.="MSDAORA.1"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\VersionIndependentProgID]
@.="MSDAORA"
>>>
Looks like SQL 2005 SP2 may have caused the problem.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1433118&SiteID=1
>>>
Use Oracle 64Bit drivers : http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html
>>>
But after creating the Linked Server I got:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".
>>>
http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html
>>>
But after creating the Linked Server I got:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".
>>>
For 64-bit Distributors, Oracle publishing uses the Oracle OLEDB Provider for Oracle (OraOLEDB.Oracle). SQL Server creates a registry entry to allow the Oracle provider to run in process with SQL Server. If there is a problem reading or writing this registry entry, the following error message is shown:
"Unable to update the registry of distributor '%s' to allow Oracle OLEDB provider OraOLEDB.Oracle to run in process with SQL Server. Make certain that current login is authorized to modify SQL Server owned registry keys."
Oracle publishing requires the registry entry to exist and to be set to 1 for 64 bit Distributors. If the entry does not exist, SQL Server will attempt to create it. If the entry exists, but is set to 0, the setting will not be changed; the configuration of the Oracle Publisher will fail.
To view and modify the registry setting:
1. Click Start, and then click Run.
2. In the Run dialog box, type regedit, and then click OK.
3. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\Providers.
Included under Providers should be a folder named OraOLEDB.Oracle. Within this folder should be the DWORD value name AllowInProcess, with a value of 1.4. If you determine that AllowInProcess is set to 0, update the registry entry to 1:
a. Right-click the entry, and then click Modify.
b. In the Edit String dialog box, type 1 in the Value data field.
So in conclusion:
To create a linked server on SQL Server 2005 SP2 64Bits,
- Install 64Bit Oracle Client
- Install 64Bit Oracle OLEDB Provider
- Set the AllowInProcess value in the registry
After SQL 2005 SP2, Query to Apartment Linked Servers fails
We have a linked server that is an Apartment model OLE DB Provider. It works fine on SQL 2005 SP1 and previous versions.
After applying SP2, we get the following error message when running a query against the linked server.
I cannot find anything in the SP2 documentation that indicates a change of behavior for linked servers.
Any ideas ?
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'XXX.XXXXX' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
TobyHi, I have the same problem with MSDAORA when I try to execute a query using a Linked Server with Oracle Server."Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
"
Someone can help us?
Thanks
Denis
|||
I'm not in a position to answer about the change in the linked server code, but perhaps it is possible to reconfigure the appartment model.
What do you have under HKEY_CLASSES_ROOT\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32\ThreadingModel?
Can you try setting it to "Both" and see if you can connect?|||Ditto,
Did you find a solution?
Thanks.
|||Did you try changing apartment model? Does it not work for you?|||No, Basically I found out that the 32 bit OLEDB Provider will not work correctly on 64Bit Server 2003 with SQL 2005 SP2 64Bits. I ended up installed the Oracle 64Bit Provider and use OraOLEDB.Oracle provider.
Here's the complete thread:
I'm trying to get SQLServer 2005 to link to an Oracle 10g Database.
The OS is Server 2003 SP2 64Bit.
The Microsoft OLE DB For Oracle driver is installed (MSDAORA) but doesn't appear in SQL Server Providers.
It's a 32 Bit provider.
Since it didn't appear, I used T-SQL sp_addlinkedserver (http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.
When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.
After that, when executing some T-SQL against Oracle, I got a new error:
http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.
When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.
After that, when executing some T-SQL against Oracle, I got a new error:
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
But the registry shows:
[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA"
"OLEDB_SERVICES"=dword:ffffffff[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors]
@.="Extended Error Service"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors\{e8cc4cbf-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA ErrorLookup"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32]
@.="C:\\Program Files (x86)\\Common Files\\System\\Ole DB\\msdaora.dll"
"ThreadingModel"="Both"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\OLE DB Provider]
@.="Microsoft OLE DB Provider for Oracle"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ProgID]
@.="MSDAORA.1"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\VersionIndependentProgID]
@.="MSDAORA"
>>>
Looks like SQL 2005 SP2 may have caused the problem.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1433118&SiteID=1
>>>
Use Oracle 64Bit drivers : http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html
>>>
But after creating the Linked Server I got:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".
>>>
http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html
>>>
But after creating the Linked Server I got:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".
>>>
For 64-bit Distributors, Oracle publishing uses the Oracle OLEDB Provider for Oracle (OraOLEDB.Oracle). SQL Server creates a registry entry to allow the Oracle provider to run in process with SQL Server. If there is a problem reading or writing this registry entry, the following error message is shown:
"Unable to update the registry of distributor '%s' to allow Oracle OLEDB provider OraOLEDB.Oracle to run in process with SQL Server. Make certain that current login is authorized to modify SQL Server owned registry keys."
Oracle publishing requires the registry entry to exist and to be set to 1 for 64 bit Distributors. If the entry does not exist, SQL Server will attempt to create it. If the entry exists, but is set to 0, the setting will not be changed; the configuration of the Oracle Publisher will fail.
To view and modify the registry setting:
1. Click Start, and then click Run.
2. In the Run dialog box, type regedit, and then click OK.
3. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\Providers.
Included under Providers should be a folder named OraOLEDB.Oracle. Within this folder should be the DWORD value name AllowInProcess, with a value of 1.4. If you determine that AllowInProcess is set to 0, update the registry entry to 1:
a. Right-click the entry, and then click Modify.
b. In the Edit String dialog box, type 1 in the Value data field.
So in conclusion:
To create a linked server on SQL Server 2005 SP2 64Bits,
- Install 64Bit Oracle Client
- Install 64Bit Oracle OLEDB Provider
- Set the AllowInProcess value in the registry
After SQL 2005 SP2, Query to Apartment Linked Servers fails
We have a linked server that is an Apartment model OLE DB Provider. It works fine on SQL 2005 SP1 and previous versions.
After applying SP2, we get the following error message when running a query against the linked server.
I cannot find anything in the SP2 documentation that indicates a change of behavior for linked servers.
Any ideas ?
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'XXX.XXXXX' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
TobyHi, I have the same problem with MSDAORA when I try to execute a query using a Linked Server with Oracle Server."Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
"
Someone can help us?
Thanks
Denis
|||
I'm not in a position to answer about the change in the linked server code, but perhaps it is possible to reconfigure the appartment model.
What do you have under HKEY_CLASSES_ROOT\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32\ThreadingModel?
Can you try setting it to "Both" and see if you can connect?|||Ditto,
Did you find a solution?
Thanks.
|||Did you try changing apartment model? Does it not work for you?|||
No, Basically I found out that the 32 bit OLEDB Provider will not work correctly on 64Bit Server 2003 with SQL 2005 SP2 64Bits. I ended up installed the Oracle 64Bit Provider and use OraOLEDB.Oracle provider.
Here's the complete thread:
I'm trying to get SQLServer 2005 to link to an Oracle 10g Database.
The OS is Server 2003 SP2 64Bit.
The Microsoft OLE DB For Oracle driver is installed (MSDAORA) but doesn't appear in SQL Server Providers.
It's a 32 Bit provider.
Since it didn't appear, I used T-SQL sp_addlinkedserver (http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.
When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.
After that, when executing some T-SQL against Oracle, I got a new error:
http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.
When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.
After that, when executing some T-SQL against Oracle, I got a new error:
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
But the registry shows:
[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA"
"OLEDB_SERVICES"=dword:ffffffff[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors]
@.="Extended Error Service"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors\{e8cc4cbf-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA ErrorLookup"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32]
@.="C:\\Program Files (x86)\\Common Files\\System\\Ole DB\\msdaora.dll"
"ThreadingModel"="Both"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\OLE DB Provider]
@.="Microsoft OLE DB Provider for Oracle"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ProgID]
@.="MSDAORA.1"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\VersionIndependentProgID]
@.="MSDAORA"
>>>
Looks like SQL 2005 SP2 may have caused the problem.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1433118&SiteID=1
>>>
Use Oracle 64Bit drivers : http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html
>>>
But after creating the Linked Server I got:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".
>>>
http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html
>>>
But after creating the Linked Server I got:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".
>>>
For 64-bit Distributors, Oracle publishing uses the Oracle OLEDB Provider for Oracle (OraOLEDB.Oracle). SQL Server creates a registry entry to allow the Oracle provider to run in process with SQL Server. If there is a problem reading or writing this registry entry, the following error message is shown:
"Unable to update the registry of distributor '%s' to allow Oracle OLEDB provider OraOLEDB.Oracle to run in process with SQL Server. Make certain that current login is authorized to modify SQL Server owned registry keys."
Oracle publishing requires the registry entry to exist and to be set to 1 for 64 bit Distributors. If the entry does not exist, SQL Server will attempt to create it. If the entry exists, but is set to 0, the setting will not be changed; the configuration of the Oracle Publisher will fail.
To view and modify the registry setting:
1. Click Start, and then click Run.
2. In the Run dialog box, type regedit, and then click OK.
3. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\Providers.
Included under Providers should be a folder named OraOLEDB.Oracle. Within this folder should be the DWORD value name AllowInProcess, with a value of 1.4. If you determine that AllowInProcess is set to 0, update the registry entry to 1:
a. Right-click the entry, and then click Modify.
b. In the Edit String dialog box, type 1 in the Value data field.
So in conclusion:
To create a linked server on SQL Server 2005 SP2 64Bits,
- Install 64Bit Oracle Client
- Install 64Bit Oracle OLEDB Provider
- Set the AllowInProcess value in the registry
After SQL 2005 SP2, Query to Apartment Linked Servers fails
We have a linked server that is an Apartment model OLE DB Provider. It works fine on SQL 2005 SP1 and previous versions.
After applying SP2, we get the following error message when running a query against the linked server.
I cannot find anything in the SP2 documentation that indicates a change of behavior for linked servers.
Any ideas ?
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'XXX.XXXXX' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
TobyHi, I have the same problem with MSDAORA when I try to execute a query using a Linked Server with Oracle Server."Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
"
Someone can help us?
Thanks
Denis
|||
I'm not in a position to answer about the change in the linked server code, but perhaps it is possible to reconfigure the appartment model.
What do you have under HKEY_CLASSES_ROOT\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32\ThreadingModel?
Can you try setting it to "Both" and see if you can connect?|||Ditto,
Did you find a solution?
Thanks.
|||Did you try changing apartment model? Does it not work for you?|||
No, Basically I found out that the 32 bit OLEDB Provider will not work correctly on 64Bit Server 2003 with SQL 2005 SP2 64Bits. I ended up installed the Oracle 64Bit Provider and use OraOLEDB.Oracle provider.
Here's the complete thread:
I'm trying to get SQLServer 2005 to link to an Oracle 10g Database.
The OS is Server 2003 SP2 64Bit.
The Microsoft OLE DB For Oracle driver is installed (MSDAORA) but doesn't appear in SQL Server Providers.
It's a 32 Bit provider.
Since it didn't appear, I used T-SQL sp_addlinkedserver (http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.
When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.
After that, when executing some T-SQL against Oracle, I got a new error:
http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.
When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.
After that, when executing some T-SQL against Oracle, I got a new error:
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
But the registry shows:
[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA"
"OLEDB_SERVICES"=dword:ffffffff[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors]
@.="Extended Error Service"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors\{e8cc4cbf-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA ErrorLookup"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32]
@.="C:\\Program Files (x86)\\Common Files\\System\\Ole DB\\msdaora.dll"
"ThreadingModel"="Both"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\OLE DB Provider]
@.="Microsoft OLE DB Provider for Oracle"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ProgID]
@.="MSDAORA.1"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\VersionIndependentProgID]
@.="MSDAORA"
>>>
Looks like SQL 2005 SP2 may have caused the problem.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1433118&SiteID=1
>>>
Use Oracle 64Bit drivers : http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html
>>>
But after creating the Linked Server I got:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".
>>>
http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html
>>>
But after creating the Linked Server I got:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".
>>>
For 64-bit Distributors, Oracle publishing uses the Oracle OLEDB Provider for Oracle (OraOLEDB.Oracle). SQL Server creates a registry entry to allow the Oracle provider to run in process with SQL Server. If there is a problem reading or writing this registry entry, the following error message is shown:
"Unable to update the registry of distributor '%s' to allow Oracle OLEDB provider OraOLEDB.Oracle to run in process with SQL Server. Make certain that current login is authorized to modify SQL Server owned registry keys."
Oracle publishing requires the registry entry to exist and to be set to 1 for 64 bit Distributors. If the entry does not exist, SQL Server will attempt to create it. If the entry exists, but is set to 0, the setting will not be changed; the configuration of the Oracle Publisher will fail.
To view and modify the registry setting:
1. Click Start, and then click Run.
2. In the Run dialog box, type regedit, and then click OK.
3. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\Providers.
Included under Providers should be a folder named OraOLEDB.Oracle. Within this folder should be the DWORD value name AllowInProcess, with a value of 1.4. If you determine that AllowInProcess is set to 0, update the registry entry to 1:
a. Right-click the entry, and then click Modify.
b. In the Edit String dialog box, type 1 in the Value data field.
So in conclusion:
To create a linked server on SQL Server 2005 SP2 64Bits,
- Install 64Bit Oracle Client
- Install 64Bit Oracle OLEDB Provider
- Set the AllowInProcess value in the registry
Thursday, February 9, 2012
Advice?
hellooo
I have an Accounting system(vb.net 2003, SQL server 2000), every new year data is cleared, but i may use some data from previous years (such as liabilities)?
whats the best way to that ?
-Shall I create programmatically a new clone DB every year (new DB with same structure as previous year)
OR
-Shall I add a "year field" for tables in DB?
knowing that data will keep growing every year?
whats the best solution, knowing that i dont want the end user of my application to do anything manually, such as creating DB ......
Thank you
Hi,
I would add the year field and provide a tool to delete entries older than a given amount of years. Most liability rules are defined to keep data for a fixes amount of time (e.g. 1, 3, 10 years). That helps to limit the growth of the DB.
--
SvenC
as you did not mention which edition of SQL Server you are using, you should take a look at partitioning. Vertical Partitioning will help you based on the date the data was entered (or should be archived) to autimatically let SQL Server move this data to another partition (SAN Storage area or another disk). You will not need to worry about picking the data then from some other database or changing your logic, because of the differentiation of the relational and the storage engine. E.g.: As the relational engine will get a command to retrieve all data from the years 2000 (which are already "outdated") it will query the storage engine which has information about where the data is stored (partition).
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Man thanks alot, but can you praphrase 
or give me a link to an article tht might help me
I need a brief A_Z solution ......
Thanks
|||ohh sorry i forgot to tell you SQL Server 2000 Enterprise Edition|||The answer is you NEVER delete anything in an accounting system, the accountants will not be happy if you delete things at year end.The accounting system logic should always be date range specific. As soon as you hard code in Jan - Dec, someone will want to change to July-June.|||
I dont wanna delete anything, I want at each beginning year to create a new DB(same structure as previous year) and may use some Data from previous DB (such as liabilities of previous year).
That all wht I need, can you direct me on the way to so that?
Thank you
|||That would make quering the data of several years a nightmare.
--
SvenC
hi,
have a new database per year.the advantage:
1. you can put the entire database (when everything has been balanced)
on a readonly mode and its readonly no matter who logged into the system
2. you're not going to delete anything
3. in this way your scaling out your enterprise database
in the front end:
give the user a dropdown to choose which database he is going to connect
behind the scene he/she is just choosing a connection string
store the connection string in a class
and let every sql connection object to use the connection string stored in the class.
regards,
joey
|||
thanks man,
but how to clone my DB2006 to DB2007?
and still access DB2006?
hi,
use the copy database wizard.
open enterprise manager.
drill down to the database you want to copy.
click on tools>wizard>management>copy database wizard.
youll be fine from there
regards,
joey
|||thanks for these replies
I need to do this cloning programmatically|||Isnt there a way to do this programmatically?|||
Have a look at SQL-DMO.
You find the SQLServer object which has a DetachDB and AttachDB method which you can use to detach a db, copy the files and attach it on another server or the same server as a different DB. You might need the Database object prior to that to find the database files.
MSDN has information about the class library and I guess BOL also.
--
SvenC
Advice?
hellooo
I have an Accounting system(vb.net 2003, SQL server 2000), every new year data is cleared, but i may use some data from previous years (such as liabilities)?
whats the best way to that ?
-Shall I create programmatically a new clone DB every year (new DB with same structure as previous year)
OR
-Shall I add a "year field" for tables in DB?
knowing that data will keep growing every year?
whats the best solution, knowing that i dont want the end user of my application to do anything manually, such as creating DB ......
Thank you
Hi,
I would add the year field and provide a tool to delete entries older than a given amount of years. Most liability rules are defined to keep data for a fixes amount of time (e.g. 1, 3, 10 years). That helps to limit the growth of the DB.
--
SvenC
as you did not mention which edition of SQL Server you are using, you should take a look at partitioning. Vertical Partitioning will help you based on the date the data was entered (or should be archived) to autimatically let SQL Server move this data to another partition (SAN Storage area or another disk). You will not need to worry about picking the data then from some other database or changing your logic, because of the differentiation of the relational and the storage engine. E.g.: As the relational engine will get a command to retrieve all data from the years 2000 (which are already "outdated") it will query the storage engine which has information about where the data is stored (partition).
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Man thanks alot, but can you praphrase
or give me a link to an article tht might help me
I need a brief A_Z solution ......
Thanks
|||ohh sorry i forgot to tell you SQL Server 2000 Enterprise Edition|||The answer is you NEVER delete anything in an accounting system, the accountants will not be happy if you delete things at year end.The accounting system logic should always be date range specific. As soon as you hard code in Jan - Dec, someone will want to change to July-June.|||
I dont wanna delete anything, I want at each beginning year to create a new DB(same structure as previous year) and may use some Data from previous DB (such as liabilities of previous year).
That all wht I need, can you direct me on the way to so that?
Thank you
|||That would make quering the data of several years a nightmare.
--
SvenC
hi,
have a new database per year.the advantage:
1. you can put the entire database (when everything has been balanced)
on a readonly mode and its readonly no matter who logged into the system
2. you're not going to delete anything
3. in this way your scaling out your enterprise database
in the front end:
give the user a dropdown to choose which database he is going to connect
behind the scene he/she is just choosing a connection string
store the connection string in a class
and let every sql connection object to use the connection string stored in the class.
regards,
joey
|||
thanks man,
but how to clone my DB2006 to DB2007?
and still access DB2006?
hi,
use the copy database wizard.
open enterprise manager.
drill down to the database you want to copy.
click on tools>wizard>management>copy database wizard.
youll be fine from there
regards,
joey
|||thanks for these replies
I need to do this cloning programmatically|||Isnt there a way to do this programmatically?|||
Have a look at SQL-DMO.
You find the SQLServer object which has a DetachDB and AttachDB method which you can use to detach a db, copy the files and attach it on another server or the same server as a different DB. You might need the Database object prior to that to find the database files.
MSDN has information about the class library and I guess BOL also.
--
SvenC
Subscribe to:
Posts (Atom)