The SMS provider will update my SQL server when there is incoming SMS
message.
apart from regular manual check on table
how can i know there is incoming new SMS message?
alert by email? any guidance?
Thanks.
TonyHi Tony,
You can have a trigger on the table that sends you an email
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"tony wong" <x34@.netvigator.com> wrote in message
news:%23nRYnw$nFHA.1148@.TK2MSFTNGP12.phx.gbl...
> The SMS provider will update my SQL server when there is incoming SMS
> message.
> apart from regular manual check on table
> how can i know there is incoming new SMS message?
> alert by email? any guidance?
> Thanks.
> Tony
>|||Hi Greg
Could i have some hints on that? i am new on programming.
my primary thought is to write exe to check any new SMS and then send email
via smtp, and run it in Windows Scheduler.
Any better methods? Thanks
Tony
"GregO" <grego@.community.nospam> glsD:edQ1fnAoFHA.1048@.tk2msftngp13.phx.gbl...[co
lor=darkred]
> Hi Tony,
> You can have a trigger on the table that sends you an email
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the firs and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "tony wong" <x34@.netvigator.com> wrote in message
> news:%23nRYnw$nFHA.1148@.TK2MSFTNGP12.phx.gbl...
>[/color]sql
Showing posts with label provider. Show all posts
Showing posts with label provider. Show all posts
Thursday, March 22, 2012
Friday, February 24, 2012
After upgrading to 2005, update and add no longer work in ASP pages.
Upgraded to SQL 2005 and now I get the following error when trying to add or update a record
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server 'NS1' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
/manage/inc_post_events.asp, line 57
The setup is one server running Windows Server 2000 with IIS and SQL 2005 installed. We previously had SQL 2000 and no issues. Now we cant add new records or update records. Anyone have any ideas? When looking in the tables, I notice in one database, some tables have a schema of dbo and some of a username.... HELP!Probably the wrong forum for this but:
Whats the name of the machine that you upgraded?
is it NS1?
If it is. . . did you name 2005 instance, or leave it as default?
Lets see a simple example of some update SQL that doesnt work
as far as schema names, db objects belong to the schema of the user who created them unless the user explictly declares the schema -
user 'foo' logs in
foo executes:
CREATE TABLE [BAR](i int)
CREATE TABLE [dbo].[BAR](i int)
now there are two tables:
[FOO].[BAR] & [DBO].[BAR]
lastly. . . Why are you using ODBC? SLOWWWWWWWWWWW and faulty!
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. After applying SP2, we get the following msg when running a query against the linked server. I cannot find anything in the SP2 doc that indicates a change of behavior for linked servers. Any ideas ?
Bill
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'DBAmp.DBAmp' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
We are having exact same problem
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'xxxx.xxxxxx' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
Does anyone have any idea on how to solve this ?
toby
|||Same here.
Worked fine in SQL Server 2000 (any service pack) and SQL Server 2005 (including SP1) but broke in 2005 with SP2.
Any clues?
Many thanks|||
I confirmed with forceamp support that something in SP2 causes this failure in DBAmp. You need to download the v2.6 beta to resolve this issue or contact support@.forceamp.com and to determine the right build to resolve this issue.
Chad
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. After applying SP2, we get the following msg when running a query against the linked server. I cannot find anything in the SP2 doc that indicates a change of behavior for linked servers. Any ideas ?
Bill
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'DBAmp.DBAmp' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
We are having exact same problem
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'xxxx.xxxxxx' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
Does anyone have any idea on how to solve this ?
toby
|||Same here.
Worked fine in SQL Server 2000 (any service pack) and SQL Server 2005 (including SP1) but broke in 2005 with SP2.
Any clues?
Many thanks|||I confirmed with forceamp support that something in SP2 causes this failure in DBAmp. You need to download the v2.6 beta to resolve this issue or contact support@.forceamp.com and to determine the right build to resolve this issue.
Chad
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
Subscribe to:
Posts (Atom)