Linking a text file to SQL Server

Hi all.
I need to create a Linked Server in SQL 2000 to a text file. Importing the file is not an option. It has to be linked. I tried creating a ODBC link to it but I must be doing something wrong because it didn't work.
Please help.
Thanks,
ODanielsif you have sucessfully created the linkserver, you can use
EXEC sp_tables_ex <link server name> to query the tables (csv/txt files) of that link.

a sample query to retrive rows from a file can look like
select * from [txtLinkSrv].[d:\folder]..[File1.csv]

here "txtLinkSrv" is the link server name, "d:\folder" is the folder name that contains the files, "File1.csv" is the name of a csv file.|||Thanks upalsen.

Can this be used with a network path? Or does it have to be local?
I tried it and I keep getting:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Text Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].

servers

Hello,
I am having trouble with creating a linked server (as simple as it should
be).
My Setup:
Server A: runs under a specific account, the account can be delegated, I
have used setspn on this machine (as per BOL), I have also created a linked
server to Server B (mapping each user context as they connect)
Server B: runs under the same account (which can be delegated), I have not
used setspn as I only want people to connect remotely from Server A.
If I log onto Server A, run a query like select * from
serverb.database.owner.table everything runs fine, I can connect remotely,
now, here is the hitch:
I work on Client machine C, when I try and run the same query that worked on
server A, I get:
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
What am I doing wrong?
Appreciate any insight into this
Malcolm.Mapping each user context to what? Sounds like the users are
authenticated using their Windows credentials and mapping is
set to their credentials so for their AD accounts, the
setting for:
Account is sensitive and cannot be delegated
needs to be turned off or deselected.
The server also needs to be trusted for delegation.
You also need to check your protocols and listening ports.
The books online article:
Security Account Delegation
has all of the requirements. If you are running on SP3 or
higher, make sure you are referencing the updated help
topic. You can also find it here:
http://msdn.microsoft.com/library/d...>
ity_2gmm.asp
-Sue
On Tue, 8 Nov 2005 12:21:17 -0500, "Malcolm Klotz"
<nonesuch23@.online.nospam> wrote:

>Hello,
>I am having trouble with creating a linked server (as simple as it should
>be).
>My Setup:
>Server A: runs under a specific account, the account can be delegated, I
>have used setspn on this machine (as per BOL), I have also created a linked
>server to Server B (mapping each user context as they connect)
>Server B: runs under the same account (which can be delegated), I have not
>used setspn as I only want people to connect remotely from Server A.
>If I log onto Server A, run a query like select * from
>serverb.database.owner.table everything runs fine, I can connect remotely,
>now, here is the hitch:
>I work on Client machine C, when I try and run the same query that worked o
n
>server A, I get:
>Msg 18452, Level 14, State 1, Line 1
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL
>Server connection.
>What am I doing wrong?
>Appreciate any insight into this
>Malcolm.
>|||Hello,
To narrow down the issue, I suggest that you perform the following steps:
1. I assume both servers are SQL server 2000. If not, please provide more
detailed information about the linked server.
2. Set boh SQL server startup accounts to be a Domain account. Use SA to
connect to the SQL server A from the client, then check the issue again.
If it works fine, the cause is related to Security Account Delegation. If
so, please continue with the following steps:
3. Follow the steps on the following web site to set Delegation
Security Account Delegation
<http://msdn.microsoft.com/library/d...n-us/adminsql/a
d_security_2gmm.asp>
Run the "setspn -l" to verify it. For example, if you create an SPN for SQL
Server:
setspn -A MSSQLSvc/w2k3sqlsp4.test.com:1433
test\administrator
You can verify it by running
setspn -l test\administrator
It will return:
Registered ServicePrincipalNames for
CN= administrator, CN=users, DC=test, DC=com:
MSSQLSvc/envm-w2k3entsp1.test.com:1061
MSSQLSvc/w2k3sqlsp4:1433
MSSQLSvc/w2k3sqlsp4.test.com:1433
Please post here how you create the SPN for SQL Server and the results of
the "setspn -l".
4. Refer to the following article to troublshoot the issue:
http://support.microsoft.com/?id=319723#1
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you, this worked, I had used the wrong port for one of my instances.
Much appreciated.
Malcolm
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:O4UWYKR5FHA.1240@.TK2MSFTNGXA02.phx.gbl...
> Hello,
> To narrow down the issue, I suggest that you perform the following steps:
> 1. I assume both servers are SQL server 2000. If not, please provide more
> detailed information about the linked server.
> 2. Set boh SQL server startup accounts to be a Domain account. Use SA to
> connect to the SQL server A from the client, then check the issue again.
> If it works fine, the cause is related to Security Account Delegation. If
> so, please continue with the following steps:
> 3. Follow the steps on the following web site to set Delegation
> Security Account Delegation
> <[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a[/ur
l]
> d_security_2gmm.asp>
> Run the "setspn -l" to verify it. For example, if you create an SPN for
> SQL
> Server:
> setspn -A MSSQLSvc/w2k3sqlsp4.test.com:1433
> test\administrator
> You can verify it by running
> setspn -l test\administrator
> It will return:
> Registered ServicePrincipalNames for
> CN= administrator, CN=users, DC=test, DC=com:
> MSSQLSvc/envm-w2k3entsp1.test.com:1061
> MSSQLSvc/w2k3sqlsp4:1433
> MSSQLSvc/w2k3sqlsp4.test.com:1433
> Please post here how you create the SPN for SQL Server and the results of
> the "setspn -l".
> 4. Refer to the following article to troublshoot the issue:
> http://support.microsoft.com/?id=319723#1
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
>

servers

We are creating a newServer grabbing data from an extrnal data source.

We need to perform regular audits to verify the record counts (for now) are the same.

We have 2 physically separate SQL servers 1 with old 1 with new.

I have created a linked server but when i do a query on the linked server the performace is pathetic (2mins 12 secs) to do a simple count.

Is there a better way, or can improv the perf?

Thanks
Greg CSomething is grieviously broken, but I don't have enough information to tell you what is broken or how it should be fixed.

The first step would be to attempt the same query the new server using Query Analyzer. The purpose of this step is to determine if the problem lies in the connection between the two machines or within the SQL Server itself. If the query runs poorly in Query Analyzer, then the problem is in the connection between the machines (something is impeding SQL communications between them). If the query runs acceptably in Query Analyzer but does not using the linked server, then the problem is in the linked server.

-PatP|||Yep thanks, field i was counting wasnt indexed.. thanks|||That would be what we call a "bad thing" for lack of a better term! I'm glad to know that the answer was so simple.

-PatP

server/DTC Error in WinXP Vs Win2003Server

Client side: WinXP Pro with MSDE 2000

Server side: Win2003 Server with SQL Server 2000

I've got the error message when creating linked server and running the DTC across two database servers, ""Error 18452: Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection."

But, orginially, if the Server side is Win2000 Server with SQL Server 2000, everything is okay. When I migrate it to the Win2003Server platform, this thing happened.

I've also applied the following solution but it still doesn't work.

http://support.microsoft.com/default.aspx?scid=kb;en-us;827805http://support.microsoft.com/default.aspx?scid=kb;en-us;827805
and

http://support.microsoft.com/?kbid=873160

and the login mode is set to Mixed mode.

Many thanks.

You're possibly facing a "double-hop" issue, and you can read more about that here:

http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

This provides a good starting point for tracing this down.

server/DTC Error in WinXP Vs Win2003Server

Client side: WinXP Pro with MSDE 2000

Server side: Win2003 Server with SQL Server 2000

I've got the error message when creating linked server and running the DTC across two database servers, ""Error 18452: Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection."

But, orginially, if the Server side is Win2000 Server with SQL Server 2000, everything is okay. When I migrate it to the Win2003Server platform, this thing happened.

I've also applied the following solution but it still doesn't work.

http://support.microsoft.com/default.aspx?scid=kb;en-us;827805http://support.microsoft.com/default.aspx?scid=kb;en-us;827805
and

http://support.microsoft.com/?kbid=873160

and the login mode is set to Mixed mode.

Many thanks.

You're possibly facing a "double-hop" issue, and you can read more about that here:

http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

This provides a good starting point for tracing this down.

server to Visual Foxpro

I am on a LAN and having trouble creating a linked server to a FoxPro
data source located on a server. When I use create a new linked server
and set the Provider String to use local directory things work fine -
that is I see a list of tables in the Tables view. If I specifiy a
directory located on a server in the provider string I see no tables.
This works:
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=c:\foxdbf.dbc;
These do not work:
(Network Drive)
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=G:\foxdbf.dbc;
(Network Path)
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=\\server\volume\foxdbf.dbc;
Similarly, when I run the following T-SQL statement it fails against a
server directory, is successful against a local directory!
Select * from openrowset('MSDASQL','Driver={Microsoft Visual FoxPro
Driver};SourceDB=G:\foxdbf.dbc;SourceType=DBF','select * from master')
Any Suggestions
Message posted via http://www.droptable.comHi Rameshwar,
I don't have a lot of experience with linked servers, but many times with
questions like this it's a permissions issue. The account that the server is
running under needs permissions to the external directory.
Also, I read your other post where you said "I get the ODBC driver error as
it cannot open the .dbc file." (BTW, you're better off keeping all your
questions in one thread.) All VFP tables for version 6 and below are
readable via the FoxPro and Visual FoxPro ODBC driver; the latest is
downloadable from http://msdn.microsoft.com/vfoxpro/downloads/updates/ .
However, new data features were added in versions 7 and above (the latest is
VFP9) and tables with those features can only be accessed via the VFP OLE DB
data provider, downloadable from the same page as above. From your question
in this post I'm assuming that you were able to read the tables, at least
locally. Is that the case?
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"rameshwar elka via droptable.com" <forum@.droptable.com> wrote in message
news:9a658f3fe1834473ac99f66118dd2cbe@.SQ
droptable.com...
>I am on a LAN and having trouble creating a linked server to a FoxPro
> data source located on a server. When I use create a new linked server
> and set the Provider String to use local directory things work fine -
> that is I see a list of tables in the Tables view. If I specifiy a
> directory located on a server in the provider string I see no tables.

server to Visual Foxpro

I am on a LAN and having trouble creating a linked server to a FoxPro
data source located on a server. When I use create a new linked server
and set the Provider String to use local directory things work fine -
that is I see a list of tables in the Tables view. If I specifiy a
directory located on a server in the provider string I see no tables.
This works:
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=c:\foxdbf.dbc;
These do not work:
(Network Drive)
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=G:\foxdbf.dbc;
(Network Path)
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=\\server\volume\foxdbf.dbc;
Similarly, when I run the following T-SQL statement it fails against a
server directory, is successful against a local directory!
Select * from openrowset('MSDASQL','Driver={Microsoft Visual FoxPro
Driver};SourceDB=G:\foxdbf.dbc;SourceType=DBF','se lect * from master')
Any Suggestions
Message posted via http://www.sqlmonster.com
Hi Rameshwar,
I don't have a lot of experience with linked servers, but many times with
questions like this it's a permissions issue. The account that the server is
running under needs permissions to the external directory.
Also, I read your other post where you said "I get the ODBC driver error as
it cannot open the .dbc file." (BTW, you're better off keeping all your
questions in one thread.) All VFP tables for version 6 and below are
readable via the FoxPro and Visual FoxPro ODBC driver; the latest is
downloadable from http://msdn.microsoft.com/vfoxpro/downloads/updates/ .
However, new data features were added in versions 7 and above (the latest is
VFP9) and tables with those features can only be accessed via the VFP OLE DB
data provider, downloadable from the same page as above. From your question
in this post I'm assuming that you were able to read the tables, at least
locally. Is that the case?
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"rameshwar elka via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:9a658f3fe1834473ac99f66118dd2cbe@.SQLMonster.c om...
>I am on a LAN and having trouble creating a linked server to a FoxPro
> data source located on a server. When I use create a new linked server
> and set the Provider String to use local directory things work fine -
> that is I see a list of tables in the Tables view. If I specifiy a
> directory located on a server in the provider string I see no tables.

server to Visual Foxpro

I am on a LAN and having trouble creating a linked server to a FoxPro
data source located on a server. When I use create a new linked server
and set the Provider String to use local directory things work fine -
that is I see a list of tables in the Tables view. If I specifiy a
directory located on a server in the provider string I see no tables.
This works:
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=c:\foxdbf.dbc;
These do not work:
(Network Drive)
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=G:\foxdbf.dbc;
(Network Path)
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=\\server\volume\foxdbf.dbc;
Similarly, when I run the following T-SQL statement it fails against a
server directory, is successful against a local directory!
Select * from openrowset('MSDASQL','Driver={Microsoft Visual FoxPro
Driver};SourceDB=G:\foxdbf.dbc;SourceType=DBF','select * from master')
Any Suggestions
--
Message posted via http://www.sqlmonster.comHi Rameshwar,
I don't have a lot of experience with linked servers, but many times with
questions like this it's a permissions issue. The account that the server is
running under needs permissions to the external directory.
Also, I read your other post where you said "I get the ODBC driver error as
it cannot open the .dbc file." (BTW, you're better off keeping all your
questions in one thread.) All VFP tables for version 6 and below are
readable via the FoxPro and Visual FoxPro ODBC driver; the latest is
downloadable from http://msdn.microsoft.com/vfoxpro/downloads/updates/ .
However, new data features were added in versions 7 and above (the latest is
VFP9) and tables with those features can only be accessed via the VFP OLE DB
data provider, downloadable from the same page as above. From your question
in this post I'm assuming that you were able to read the tables, at least
locally. Is that the case?
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"rameshwar elka via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:9a658f3fe1834473ac99f66118dd2cbe@.SQLMonster.com...
>I am on a LAN and having trouble creating a linked server to a FoxPro
> data source located on a server. When I use create a new linked server
> and set the Provider String to use local directory things work fine -
> that is I see a list of tables in the Tables view. If I specifiy a
> directory located on a server in the provider string I see no tables.

server to Oracle 9.2 database

I've read KB Article 280106 about creating a Linked Server to an Oracle
database. This article does reference any Oracle database higher than 8.1.*
.
I want to link to a 9.2.0.1 database.
The article references loading the Oracle client software for 8.1 on the
Sqlserver machine.
Is this possible to create the link server if I load the client software for
9.2 on the Sqlserver machine? If it is, is it as easy as changing to
registry settings referenced in the Article for the 8.1 database from using
the oraclient8.dll and orasql8.dll to the oraclient9.dll and orasql9.dll tha
t
come with the 9.2 client software?
The other option is if I load the 8.1 client software on the Sqlserver
machine to connect to the 9.2 database, would this work?"Paul R" <Paul R@.discussions.microsoft.com> wrote in message
news:47FF64BC-973E-43A3-B25B-7D2A32AFCE38@.microsoft.com...
> I've read KB Article 280106 about creating a Linked Server to an Oracle
> database. This article does reference any Oracle database higher than
> 8.1.*.
> I want to link to a 9.2.0.1 database.
> The article references loading the Oracle client software for 8.1 on the
> Sqlserver machine.
> Is this possible to create the link server if I load the client software
> for
> 9.2 on the Sqlserver machine?
Yes.

>If it is, is it as easy as changing to
> registry settings referenced in the Article for the 8.1 database from
> using
> the oraclient8.dll and orasql8.dll to the oraclient9.dll and orasql9.dll
> that
> come with the 9.2 client software?
Not necessary to change the registry.. The 9i client, or the 10g client
will work for linked server.

> The other option is if I load the 8.1 client software on the Sqlserver
> machine to connect to the 9.2 database, would this work?
>
It should work, yes. But you should probably use the 9i client.
David|||Thanks for your response
You are telling me that with the 9.2 client software that there is no
registry changes needed.
When I look at the registry setting for
& #123;HKEY_LOCAL_MACHINE\SOFTWARE\Microso
ft\MSDTC\MTxOCI}, the file for
OracleXaLib value that is set by default is a file that don't exist in the
ORACLE_HOME/bin directory. Is this file not needed?
The OracleXaLib by default is xa73.dll. This looks like a DLL for the 7.X
database, should it be oraclient9.dll for the 9.2 database?
"David Browne" wrote:

> "Paul R" <Paul R@.discussions.microsoft.com> wrote in message
> news:47FF64BC-973E-43A3-B25B-7D2A32AFCE38@.microsoft.com...
> Yes.
>
> Not necessary to change the registry.. The 9i client, or the 10g client
> will work for linked server.
>
> It should work, yes. But you should probably use the 9i client.
> David
>
>|||"Paul R" <Paul R@.discussions.microsoft.com> wrote in message
news:9BE76BF3-045E-4913-AFDD-6357E74A12B8@.microsoft.com...
> Thanks for your response
> You are telling me that with the 9.2 client software that there is no
> registry changes needed.
> When I look at the registry setting for
> & #123;HKEY_LOCAL_MACHINE\SOFTWARE\Microso
ft\MSDTC\MTxOCI}, the file for
> OracleXaLib value that is set by default is a file that don't exist in the
> ORACLE_HOME/bin directory. Is this file not needed?
> The OracleXaLib by default is xa73.dll. This looks like a DLL for the 7.X
> database, should it be oraclient9.dll for the 9.2 database?
>
Those settings should probably match your Oracle Client install version (not
the database version). Note that for the 8i client and 9 client, the OCI
library has the same name (oci.dll). This should be enough for basic
connectivity. For distributed transactions (XA and DTC) you may need to
configure the other registry keys. For basic linked server functionality
you do not need to change the registry.
David

server to Oracle 9.2 database

I've read KB Article 280106 about creating a Linked Server to an Oracle
database. This article does reference any Oracle database higher than 8.1.*.
I want to link to a 9.2.0.1 database.
The article references loading the Oracle client software for 8.1 on the
Sqlserver machine.
Is this possible to create the link server if I load the client software for
9.2 on the Sqlserver machine? If it is, is it as easy as changing to
registry settings referenced in the Article for the 8.1 database from using
the oraclient8.dll and orasql8.dll to the oraclient9.dll and orasql9.dll that
come with the 9.2 client software?
The other option is if I load the 8.1 client software on the Sqlserver
machine to connect to the 9.2 database, would this work?
"Paul R" <Paul R@.discussions.microsoft.com> wrote in message
news:47FF64BC-973E-43A3-B25B-7D2A32AFCE38@.microsoft.com...
> I've read KB Article 280106 about creating a Linked Server to an Oracle
> database. This article does reference any Oracle database higher than
> 8.1.*.
> I want to link to a 9.2.0.1 database.
> The article references loading the Oracle client software for 8.1 on the
> Sqlserver machine.
> Is this possible to create the link server if I load the client software
> for
> 9.2 on the Sqlserver machine?
Yes.

>If it is, is it as easy as changing to
> registry settings referenced in the Article for the 8.1 database from
> using
> the oraclient8.dll and orasql8.dll to the oraclient9.dll and orasql9.dll
> that
> come with the 9.2 client software?
Not necessary to change the registry.. The 9i client, or the 10g client
will work for linked server.

> The other option is if I load the 8.1 client software on the Sqlserver
> machine to connect to the 9.2 database, would this work?
>
It should work, yes. But you should probably use the 9i client.
David
|||Thanks for your response
You are telling me that with the 9.2 client software that there is no
registry changes needed.
When I look at the registry setting for
{HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOC I}, the file for
OracleXaLib value that is set by default is a file that don't exist in the
ORACLE_HOME/bin directory. Is this file not needed?
The OracleXaLib by default is xa73.dll. This looks like a DLL for the 7.X
database, should it be oraclient9.dll for the 9.2 database?
"David Browne" wrote:

> "Paul R" <Paul R@.discussions.microsoft.com> wrote in message
> news:47FF64BC-973E-43A3-B25B-7D2A32AFCE38@.microsoft.com...
> Yes.
>
> Not necessary to change the registry.. The 9i client, or the 10g client
> will work for linked server.
>
> It should work, yes. But you should probably use the 9i client.
> David
>
>
|||"Paul R" <Paul R@.discussions.microsoft.com> wrote in message
news:9BE76BF3-045E-4913-AFDD-6357E74A12B8@.microsoft.com...
> Thanks for your response
> You are telling me that with the 9.2 client software that there is no
> registry changes needed.
> When I look at the registry setting for
> {HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOC I}, the file for
> OracleXaLib value that is set by default is a file that don't exist in the
> ORACLE_HOME/bin directory. Is this file not needed?
> The OracleXaLib by default is xa73.dll. This looks like a DLL for the 7.X
> database, should it be oraclient9.dll for the 9.2 database?
>
Those settings should probably match your Oracle Client install version (not
the database version). Note that for the 8i client and 9 client, the OCI
library has the same name (oci.dll). This should be enough for basic
connectivity. For distributed transactions (XA and DTC) you may need to
configure the other registry keys. For basic linked server functionality
you do not need to change the registry.
David

server to Oracle 9.2 database

I've read KB Article 280106 about creating a Linked Server to an Oracle
database. This article does reference any Oracle database higher than 8.1.*.
I want to link to a 9.2.0.1 database.
The article references loading the Oracle client software for 8.1 on the
Sqlserver machine.
Is this possible to create the link server if I load the client software for
9.2 on the Sqlserver machine? If it is, is it as easy as changing to
registry settings referenced in the Article for the 8.1 database from using
the oraclient8.dll and orasql8.dll to the oraclient9.dll and orasql9.dll that
come with the 9.2 client software?
The other option is if I load the 8.1 client software on the Sqlserver
machine to connect to the 9.2 database, would this work?"Paul R" <Paul R@.discussions.microsoft.com> wrote in message
news:47FF64BC-973E-43A3-B25B-7D2A32AFCE38@.microsoft.com...
> I've read KB Article 280106 about creating a Linked Server to an Oracle
> database. This article does reference any Oracle database higher than
> 8.1.*.
> I want to link to a 9.2.0.1 database.
> The article references loading the Oracle client software for 8.1 on the
> Sqlserver machine.
> Is this possible to create the link server if I load the client software
> for
> 9.2 on the Sqlserver machine?
Yes.
>If it is, is it as easy as changing to
> registry settings referenced in the Article for the 8.1 database from
> using
> the oraclient8.dll and orasql8.dll to the oraclient9.dll and orasql9.dll
> that
> come with the 9.2 client software?
Not necessary to change the registry.. The 9i client, or the 10g client
will work for linked server.
> The other option is if I load the 8.1 client software on the Sqlserver
> machine to connect to the 9.2 database, would this work?
>
It should work, yes. But you should probably use the 9i client.
David|||Thanks for your response
You are telling me that with the 9.2 client software that there is no
registry changes needed.
When I look at the registry setting for
{HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI}, the file for
OracleXaLib value that is set by default is a file that don't exist in the
ORACLE_HOME/bin directory. Is this file not needed?
The OracleXaLib by default is xa73.dll. This looks like a DLL for the 7.X
database, should it be oraclient9.dll for the 9.2 database?
"David Browne" wrote:
> "Paul R" <Paul R@.discussions.microsoft.com> wrote in message
> news:47FF64BC-973E-43A3-B25B-7D2A32AFCE38@.microsoft.com...
> > I've read KB Article 280106 about creating a Linked Server to an Oracle
> > database. This article does reference any Oracle database higher than
> > 8.1.*.
> > I want to link to a 9.2.0.1 database.
> >
> > The article references loading the Oracle client software for 8.1 on the
> > Sqlserver machine.
> >
> > Is this possible to create the link server if I load the client software
> > for
> > 9.2 on the Sqlserver machine?
> Yes.
> >If it is, is it as easy as changing to
> > registry settings referenced in the Article for the 8.1 database from
> > using
> > the oraclient8.dll and orasql8.dll to the oraclient9.dll and orasql9.dll
> > that
> > come with the 9.2 client software?
> Not necessary to change the registry.. The 9i client, or the 10g client
> will work for linked server.
> >
> > The other option is if I load the 8.1 client software on the Sqlserver
> > machine to connect to the 9.2 database, would this work?
> >
> It should work, yes. But you should probably use the 9i client.
> David
>
>|||"Paul R" <Paul R@.discussions.microsoft.com> wrote in message
news:9BE76BF3-045E-4913-AFDD-6357E74A12B8@.microsoft.com...
> Thanks for your response
> You are telling me that with the 9.2 client software that there is no
> registry changes needed.
> When I look at the registry setting for
> {HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI}, the file for
> OracleXaLib value that is set by default is a file that don't exist in the
> ORACLE_HOME/bin directory. Is this file not needed?
> The OracleXaLib by default is xa73.dll. This looks like a DLL for the 7.X
> database, should it be oraclient9.dll for the 9.2 database?
>
Those settings should probably match your Oracle Client install version (not
the database version). Note that for the 8i client and 9 client, the OCI
library has the same name (oci.dll). This should be enough for basic
connectivity. For distributed transactions (XA and DTC) you may need to
configure the other registry keys. For basic linked server functionality
you do not need to change the registry.
David