LINKED SERVER - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

LINKED SERVER

Author

Topic

akmi_fray
Starting Member

16 Posts

Posted - 2009-02-16 : 06:04:07

Hi

i was trying to create a linked server to orcale
but i am not able to see the "microsoft oledb provider for oracle"
in the dropdown of providers

i have mdac installed and msdaora.dll is registered
please help !!!!!!

AKM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-16 : 06:14:54

http://support.microsoft.com/kb/280106

akmi_fray
Starting Member

16 Posts

Posted - 2009-02-16 : 07:27:08

Since i was not able to see the provider in the dropdown.

I Created the linked server with query ( sp_addlinkedserver )
but now its returning error msg : 7308

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 in the registry

Threading model is "BOTH"

I have done all the possible way to get rid of the problem still facing the same issue

AKM

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-16 : 07:47:40

quote:
Originally posted by akmi_fray

Since i was not able to see the provider in the dropdown.

I Created the linked server with query ( sp_addlinkedserver )
but now its returning error msg : 7308

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 in the registry

Threading model is "BOTH"

I have done all the possible way to get rid of the problem still facing the same issue

AKM

What version of oledb provider, windows and sql are you using ?

akmi_fray
Starting Member

16 Posts

Posted - 2009-02-16 : 08:39:48

I am using msdaora.dll - 2.8.1117.0
64 bit os
and

sql server 2005 Enterprise edition --9.0.3042

AKM

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-16 : 08:45:34

I think you should use 64 bit Oracle OLEDB Provider. 64 bit MSOLEDB for oracle is not available yet.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-16 : 08:46:07

Also, Go through the last post in this link,

http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/dfd61545-1cb3-4353-9d70-b93387f65c8c/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-16 : 09:07:00

quote:
Originally posted by sakets_2000

I think you should use 64 bit Oracle OLEDB Provider. 64 bit MSOLEDB for oracle is not available yet.

Download MSDASQL provider from microsoft for 64-bit.

akmi_fray
Starting Member

16 Posts

Posted - 2009-02-16 : 09:11:12

I got the solution to above threading problem

now facing a new issue

Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider

please help

AKM

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-16 : 09:13:42

Do you have oracle client installed ?

akmi_fray
Starting Member

16 Posts

Posted - 2009-02-16 : 09:18:05

yes i have oracle client on the machine

AKM

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-16 : 09:44:02

try registering it again.

akmi_fray
Starting Member

16 Posts

Posted - 2009-02-16 : 09:54:42

Registered it using RegSvr32.exe

still the problem persist
is the problem with connectivity to oracle (credentials)

or may be some seeting of provider to be done ?
(like allowinprogress=1)

AKM

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-16 : 10:01:47

allowinprogress has to be 1, It could also have to do with permissions I think on the source table etc.

akmi_fray
Starting Member

16 Posts

Posted - 2009-02-16 : 10:02:52

i have checked the permission its given

am i missing some registries ?

AKM

akmi_fray
Starting Member

16 Posts

Posted - 2009-02-16 : 10:08:27

shall i restart the server ?

AKM

akmi_fray
Starting Member

16 Posts

Posted - 2009-02-16 : 10:13:25

I had removed the earlier error of threading enabling trace 7300

#1 Start SQL Server Configuration Manager and Select the "SQL Server 2005 Services"
#2 Right click on "SQL Server (MSSQLSERVER)" and choose properties
#3 Choose the Advanced tab and then the "Startup Parameters" in the list. You should have some existing command line parameters -d -e -l.
#4 Go to the end of those parameters and add a
;T7300
note that is the number 7300 and there should be no space before the semi colon.
#5 Restart the SQL Server service or reboot the machine.

You can also check that the trace flag is enabled by doing this in a query window of Management Studio
DBCC TRACESTATUS(-1)
and you should see traceflag 7300 in the results, with status=1, global=1, and session=0

is this the cause for the new error msg 7302 ?

AKM

akmi_fray
Starting Member

16 Posts

Posted - 2009-02-16 : 10:15:45

sorry its ;-T7300
and not ;T7300

AKM

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-16 : 11:07:19

Did you restart services ?

akmi_fray
Starting Member

16 Posts

Posted - 2009-02-17 : 00:30:40

Yes i re-started all the services.

AKM

akmi_fray
Starting Member

16 Posts

Posted - 2009-02-17 : 00:34:57

I am now using "oracle provider for oledb"
now it is fetching data from oracle
only i need to changed registry

hkey_local_machine-software-oracle-key_oraclient10g_home1-oledb

--fetchsize

increased the fetchsize to the records to be fetched at a time
by default it was 100

AKM

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources