Sql Linked Server: win

Showing posts with label win. Show all posts

Wednesday, March 28, 2012

linked server problem

Hi,
I have server A and B with SQL ent sp3 / Win Adv sp4. On server A I created
a linked server to B, I also selected Collation compatible. My problem,
It is ok to run on Server A (returning records instantly):
select * from b.master.dbo.sysdatabases
But it hangs when I run this on A:
begin tran
select * from b.master.dbo.sysdatabases
commit
I have checked MSDTC is running on both servers.
Did I miss anything?
Thanks.
When you say "It is ok to run on Server A (returning records instantly)",
does that mean,
on ServerB, if you run
select * from a.master.dbo.sysdatabases
it returns records instantly?
If so, and the problem is when you run
select * from b.master.dbo.sysdatabases
OR
begin tran
select * from b.master.dbo.sysdatabases
commit
your best bet would be to,
1. make a connection directly to Server B and run
begin tran
select * from master.dbo.sysdatabases
commit
-- And see if that hangs.
If it doesnt, and it seems teh problem is with Linked servers, then you
might want to look at sysprocesses and see waht the spid is doing - is it
sleeping, waiting on a resource, etc.
Also, when you say it "hangs", what's the longest you've waited for?
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Monday, March 26, 2012

linked server problem

Hi,
I have server A and B with SQL ent sp3 / Win Adv sp4. On server A I created
a linked server to B, I also selected Collation compatible. My problem,
It is ok to run on Server A (returning records instantly):
select * from b.master.dbo.sysdatabases
But it hangs when I run this on A:
begin tran
select * from b.master.dbo.sysdatabases
commit
I have checked MSDTC is running on both servers.
Did I miss anything?
Thanks.When you say "It is ok to run on Server A (returning records instantly)",
does that mean,
on ServerB, if you run
select * from a.master.dbo.sysdatabases
it returns records instantly?
If so, and the problem is when you run
select * from b.master.dbo.sysdatabases
OR
begin tran
select * from b.master.dbo.sysdatabases
commit
your best bet would be to,
1. make a connection directly to Server B and run
begin tran
select * from master.dbo.sysdatabases
commit
-- And see if that hangs.
If it doesnt, and it seems teh problem is with Linked servers, then you
might want to look at sysprocesses and see waht the spid is doing - is it
sleeping, waiting on a resource, etc.
Also, when you say it "hangs", what's the longest you've waited for?
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Linked Server not working

I have a SQL Server 2000 running on win 2003 where there is Linked Sever.
Recently we have upgrade the SQL server 2000 to SQL 2005 and things were
fine but suddenly one day things stopped working when I have tested the
connection for link server. It failed and gave me this error. What could be
the possible error . The server which is linked is a remote server running
Informix Database.
"The test connection to the linked server failed."
===================================
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
Program Location:
at
Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
at
Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String
cmd)
at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
at
Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
===================================
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "INFORMIX_LIBRARY".
OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
failed".
OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
failed".
OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
message "[INTERSOLV][ODBC Informix driver][Informix]Unable to load locale
categories.". (.Net SqlClient Data Provider)Nadia
http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
"Nadia Amir" <nadia.amir@.aku.edu> wrote in message
news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>I have a SQL Server 2000 running on win 2003 where there is Linked Sever.
> Recently we have upgrade the SQL server 2000 to SQL 2005 and things were
> fine but suddenly one day things stopped working when I have tested the
> connection for link server. It failed and gave me this error. What could
> be the possible error . The server which is linked is a remote server
> running Informix Database.
>
>
> "The test connection to the linked server failed."
> ===================================> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Program Location:
> at
> Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
> sqlCommand, ExecutionTypes executionType)
> at
> Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String
> cmd)
> at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
> ===================================> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "INFORMIX_LIBRARY".
> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
> failed".
> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
> failed".
> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
> message "[INTERSOLV][ODBC Informix driver][Informix]Unable to load locale
> categories.". (.Net SqlClient Data Provider)
>
>|||i tried to open the page and read the script. I am a novice to this SQL
2005. I tried to create a query and paste your code into it and tried to
execute it. it gave me the error. I also wanted to know where is stored
procedure located which you were talking about ?..
Kindly help thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
> Nadia
> http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
> news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>>I have a SQL Server 2000 running on win 2003 where there is Linked Sever.
>> Recently we have upgrade the SQL server 2000 to SQL 2005 and things were
>> fine but suddenly one day things stopped working when I have tested the
>> connection for link server. It failed and gave me this error. What could
>> be the possible error . The server which is linked is a remote server
>> running Informix Database.
>>
>>
>> "The test connection to the linked server failed."
>> ===================================>> An exception occurred while executing a Transact-SQL statement or batch.
>> (Microsoft.SqlServer.ConnectionInfo)
>> --
>> Program Location:
>> at
>> Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
>> sqlCommand, ExecutionTypes executionType)
>> at
>> Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String
>> cmd)
>> at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
>> at
>> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
>> ===================================>> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
>> linked server "INFORMIX_LIBRARY".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
>> failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
>> failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[INTERSOLV][ODBC Informix driver][Informix]Unable to load locale
>> categories.". (.Net SqlClient Data Provider)
>>
>>
>|||Nadia
What's error?
>. I also wanted to know where is stored procedure located which you were
>talking about ?..
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e63ca7d4-47d6-455e-9aac-421f9683dadc.htm
"Nadia Amir" <nadia.amir@.aku.edu> wrote in message
news:OEd0tpngIHA.4164@.TK2MSFTNGP05.phx.gbl...
>i tried to open the page and read the script. I am a novice to this SQL
>2005. I tried to create a query and paste your code into it and tried to
>execute it. it gave me the error. I also wanted to know where is stored
>procedure located which you were talking about ?..
> Kindly help thanks
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
>> Nadia
>> http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>>I have a SQL Server 2000 running on win 2003 where there is Linked Sever.
>> Recently we have upgrade the SQL server 2000 to SQL 2005 and things were
>> fine but suddenly one day things stopped working when I have tested the
>> connection for link server. It failed and gave me this error. What could
>> be the possible error . The server which is linked is a remote server
>> running Informix Database.
>>
>>
>> "The test connection to the linked server failed."
>> ===================================>> An exception occurred while executing a Transact-SQL statement or batch.
>> (Microsoft.SqlServer.ConnectionInfo)
>> --
>> Program Location:
>> at
>> Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
>> sqlCommand, ExecutionTypes executionType)
>> at
>> Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String
>> cmd)
>> at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
>> at
>> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
>> ===================================>> Cannot initialize the data source object of OLE DB provider "MSDASQL"
>> for linked server "INFORMIX_LIBRARY".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
>> failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
>> failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[INTERSOLV][ODBC Informix driver][Informix]Unable to load
>> locale categories.". (.Net SqlClient Data Provider)
>>
>>
>>
>|||Thanks for a prompt reply Uri.
I also wanted to add the the linkied server is linked to a source .The
source it is linked is a System DSN which is linking is a remote server
running Informix Database. The driver is Informix CLI-2.5 (32 bit). Is it
the driver that is the cause of error or what ?
And the error is
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'IF@.statusval'.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uVSTrungIHA.536@.TK2MSFTNGP06.phx.gbl...
> Nadia
> What's error?
>>. I also wanted to know where is stored procedure located which you were
>>talking about ?..
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e63ca7d4-47d6-455e-9aac-421f9683dadc.htm
>
> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
> news:OEd0tpngIHA.4164@.TK2MSFTNGP05.phx.gbl...
>>i tried to open the page and read the script. I am a novice to this SQL
>>2005. I tried to create a query and paste your code into it and tried to
>>execute it. it gave me the error. I also wanted to know where is stored
>>procedure located which you were talking about ?..
>> Kindly help thanks
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
>> Nadia
>> http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>>I have a SQL Server 2000 running on win 2003 where there is Linked
>>Sever.
>> Recently we have upgrade the SQL server 2000 to SQL 2005 and things
>> were fine but suddenly one day things stopped working when I have
>> tested the connection for link server. It failed and gave me this
>> error. What could be the possible error . The server which is linked is
>> a remote server running Informix Database.
>>
>>
>> "The test connection to the linked server failed."
>> ===================================>> An exception occurred while executing a Transact-SQL statement or
>> batch. (Microsoft.SqlServer.ConnectionInfo)
>> --
>> Program Location:
>> at
>> Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
>> sqlCommand, ExecutionTypes executionType)
>> at
>> Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String
>> cmd)
>> at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
>> at
>> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
>> ===================================>> Cannot initialize the data source object of OLE DB provider "MSDASQL"
>> for linked server "INFORMIX_LIBRARY".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
>> failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
>> failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY" returned
>> message "[INTERSOLV][ODBC Informix driver][Informix]Unable to load
>> locale categories.". (.Net SqlClient Data Provider)
>>
>>
>>
>>
>|||Nadia
I have not played with Informix database, but have you tried DTS/SSIS (I
assume you have installed Client Access)
See , if you get the error right now
"Nadia Amir" <nadia.amir@.aku.edu> wrote in message
news:uJI%2302ngIHA.5348@.TK2MSFTNGP03.phx.gbl...
> Thanks for a prompt reply Uri.
>
> I also wanted to add the the linkied server is linked to a source .The
> source it is linked is a System DSN which is linking is a remote server
> running Informix Database. The driver is Informix CLI-2.5 (32 bit). Is it
> the driver that is the cause of error or what ?
>
> And the error is
> Msg 102, Level 15, State 1, Line 9
> Incorrect syntax near 'IF@.statusval'.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uVSTrungIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Nadia
>> What's error?
>>. I also wanted to know where is stored procedure located which you were
>>talking about ?..
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e63ca7d4-47d6-455e-9aac-421f9683dadc.htm
>>
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OEd0tpngIHA.4164@.TK2MSFTNGP05.phx.gbl...
>>i tried to open the page and read the script. I am a novice to this SQL
>>2005. I tried to create a query and paste your code into it and tried to
>>execute it. it gave me the error. I also wanted to know where is stored
>>procedure located which you were talking about ?..
>> Kindly help thanks
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
>> Nadia
>> http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>>I have a SQL Server 2000 running on win 2003 where there is Linked
>>Sever.
>> Recently we have upgrade the SQL server 2000 to SQL 2005 and things
>> were fine but suddenly one day things stopped working when I have
>> tested the connection for link server. It failed and gave me this
>> error. What could be the possible error . The server which is linked
>> is a remote server running Informix Database.
>>
>>
>> "The test connection to the linked server failed."
>> ===================================>> An exception occurred while executing a Transact-SQL statement or
>> batch. (Microsoft.SqlServer.ConnectionInfo)
>> --
>> Program Location:
>> at
>> Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
>> sqlCommand, ExecutionTypes executionType)
>> at
>> Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String
>> cmd)
>> at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
>> at
>> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
>> ===================================>> Cannot initialize the data source object of OLE DB provider "MSDASQL"
>> for linked server "INFORMIX_LIBRARY".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY"
>> returned message "[Microsoft][ODBC Driver Manager] Driver's
>> SQLSetConnectAttr failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY"
>> returned message "[Microsoft][ODBC Driver Manager] Driver's
>> SQLSetConnectAttr failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY"
>> returned message "[INTERSOLV][ODBC Informix driver][Informix]Unable to
>> load locale categories.". (.Net SqlClient Data Provider)
>>
>>
>>
>>
>>
>|||The linked server is a part of DTS package . I came to know about the linked
server only after the DTS execution was failed and i came to know that the
linked server is not wroking that is why the error is coming. and rest you
know . i just thought that might i used a 64 bit driver and i could do the
trick what you say ? Actually i also havent palyed with the informix
server and the DTS package was doing it tranpsarently. but some how this
linked server is stopped and i came here for support. And i am still clue
less that what could be the case ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O6wSjBogIHA.1132@.TK2MSFTNGP06.phx.gbl...
> Nadia
> I have not played with Informix database, but have you tried DTS/SSIS (I
> assume you have installed Client Access)
> See , if you get the error right now
> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
> news:uJI%2302ngIHA.5348@.TK2MSFTNGP03.phx.gbl...
>> Thanks for a prompt reply Uri.
>>
>> I also wanted to add the the linkied server is linked to a source .The
>> source it is linked is a System DSN which is linking is a remote server
>> running Informix Database. The driver is Informix CLI-2.5 (32 bit). Is
>> it the driver that is the cause of error or what ?
>>
>> And the error is
>> Msg 102, Level 15, State 1, Line 9
>> Incorrect syntax near 'IF@.statusval'.
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uVSTrungIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Nadia
>> What's error?
>>. I also wanted to know where is stored procedure located which you were
>>talking about ?..
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e63ca7d4-47d6-455e-9aac-421f9683dadc.htm
>>
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OEd0tpngIHA.4164@.TK2MSFTNGP05.phx.gbl...
>>i tried to open the page and read the script. I am a novice to this SQL
>>2005. I tried to create a query and paste your code into it and tried to
>>execute it. it gave me the error. I also wanted to know where is stored
>>procedure located which you were talking about ?..
>> Kindly help thanks
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uHTIjingIHA.5280@.TK2MSFTNGP02.phx.gbl...
>> Nadia
>> http://dimantdatabasesolutions.blogspot.com/2007/06/is-linked-server-available.html
>> "Nadia Amir" <nadia.amir@.aku.edu> wrote in message
>> news:OhulQengIHA.5296@.TK2MSFTNGP05.phx.gbl...
>>I have a SQL Server 2000 running on win 2003 where there is Linked
>>Sever.
>> Recently we have upgrade the SQL server 2000 to SQL 2005 and things
>> were fine but suddenly one day things stopped working when I have
>> tested the connection for link server. It failed and gave me this
>> error. What could be the possible error . The server which is linked
>> is a remote server running Informix Database.
>>
>>
>> "The test connection to the linked server failed."
>> ===================================>> An exception occurred while executing a Transact-SQL statement or
>> batch. (Microsoft.SqlServer.ConnectionInfo)
>> --
>> Program Location:
>> at
>> Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
>> sqlCommand, ExecutionTypes executionType)
>> at
>> Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String
>> cmd)
>> at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
>> at
>> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
>> ===================================>> Cannot initialize the data source object of OLE DB provider "MSDASQL"
>> for linked server "INFORMIX_LIBRARY".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY"
>> returned message "[Microsoft][ODBC Driver Manager] Driver's
>> SQLSetConnectAttr failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY"
>> returned message "[Microsoft][ODBC Driver Manager] Driver's
>> SQLSetConnectAttr failed".
>> OLE DB provider "MSDASQL" for linked server "INFORMIX_LIBRARY"
>> returned message "[INTERSOLV][ODBC Informix driver][Informix]Unable
>> to load locale categories.". (.Net SqlClient Data Provider)
>>
>>
>>
>>
>>
>>
>

Friday, March 9, 2012

Linked server and MDAC driver.

...
Thanks very much Steen.
D.
""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:uKBKiD8iHHA.2272@.TK2MSFTNGP02.phx.gbl...
> Dahab wrote:
> Is it by any change a 64-bit installation you are using? The MSDAORA
> provider doesn't exists on the 64-bit version. Instead you should use the
> Oracle ODAC which is the OraOledb.Oracle provider. You should also be
> aware that the MSDAORA hasn't been enhanced since Oracle 8 client and
> there are a number of things that isn't supported in this provider.
> You can take a look at the link below which is a document that gives a
> pretty good overview over the various connectivity options.
> http://msdn2.microsoft.com/en-us/library/bb332055.aspx
> --
> Regards
> Steen Schlter Persson
> Database Administrator / System Administrator
|||Dahab wrote:
> Hi Steen,
> Yes it's a 64bit server.
> So I have to figure out how to use the Oracle driver then. Gave it a try a
> few days ago with no luck

...
> Thanks very much Steen.
> D.
It's not that big a problem to use the Oracle driver. You'll have to
download the ODAC (
http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html)
and then make the entries in the tnsnames.ora file. Then you can use the
OraOledb.Oracle provider for setting up your Linked Server.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

Linked server and MDAC driver.

Hi,
I have developed a database on my local Win Xp machine, using Express
version of SQL server.
The database is using a linked server to an Oracle database. On the XP
machin I have used the "Microsoft OLE DB Provider for Oracle" as the driver
for the linked server.
Now I'm trying to port this version to full SQL server 2005 on a Windows
2003 Server, but it seems that the driver is not installed(only the Oracle
driver). On MSDN I read that MDAC is installed with the OS on windows 2003
server.
Anyone knows how to enable the Microsoft OLE DB Provider for Oracle on
Windows Server 2003?
Thanks!
D.Hi
"Dahab" wrote:
> Hi,
> I have developed a database on my local Win Xp machine, using Express
> version of SQL server.
> The database is using a linked server to an Oracle database. On the XP
> machin I have used the "Microsoft OLE DB Provider for Oracle" as the driver
> for the linked server.
> Now I'm trying to port this version to full SQL server 2005 on a Windows
> 2003 Server, but it seems that the driver is not installed(only the Oracle
> driver). On MSDN I read that MDAC is installed with the OS on windows 2003
> server.
> Anyone knows how to enable the Microsoft OLE DB Provider for Oracle on
> Windows Server 2003?
> Thanks!
> D.
>
I would expect these to be part of the Connectivity Components which you can
add from the command prompt using the ADDLOCAL parameter.
John|||Dahab wrote:
> Hi,
> I have developed a database on my local Win Xp machine, using Express
> version of SQL server.
> The database is using a linked server to an Oracle database. On the XP
> machin I have used the "Microsoft OLE DB Provider for Oracle" as the driver
> for the linked server.
> Now I'm trying to port this version to full SQL server 2005 on a Windows
> 2003 Server, but it seems that the driver is not installed(only the Oracle
> driver). On MSDN I read that MDAC is installed with the OS on windows 2003
> server.
> Anyone knows how to enable the Microsoft OLE DB Provider for Oracle on
> Windows Server 2003?
> Thanks!
> D.
>
Is it by any change a 64-bit installation you are using? The MSDAORA
provider doesn't exists on the 64-bit version. Instead you should use
the Oracle ODAC which is the OraOledb.Oracle provider. You should also
be aware that the MSDAORA hasn't been enhanced since Oracle 8 client and
there are a number of things that isn't supported in this provider.
You can take a look at the link below which is a document that gives a
pretty good overview over the various connectivity options.
http://msdn2.microsoft.com/en-us/library/bb332055.aspx
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Hi Steen,
Yes it's a 64bit server.
So I have to figure out how to use the Oracle driver then. Gave it a try a
few days ago with no luck :(...
Thanks very much Steen.
D.
""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:uKBKiD8iHHA.2272@.TK2MSFTNGP02.phx.gbl...
> Dahab wrote:
>> Hi,
>> I have developed a database on my local Win Xp machine, using Express
>> version of SQL server.
>> The database is using a linked server to an Oracle database. On the XP
>> machin I have used the "Microsoft OLE DB Provider for Oracle" as the
>> driver for the linked server.
>> Now I'm trying to port this version to full SQL server 2005 on a Windows
>> 2003 Server, but it seems that the driver is not installed(only the
>> Oracle driver). On MSDN I read that MDAC is installed with the OS on
>> windows 2003 server.
>> Anyone knows how to enable the Microsoft OLE DB Provider for Oracle on
>> Windows Server 2003?
>> Thanks!
>> D.
> Is it by any change a 64-bit installation you are using? The MSDAORA
> provider doesn't exists on the 64-bit version. Instead you should use the
> Oracle ODAC which is the OraOledb.Oracle provider. You should also be
> aware that the MSDAORA hasn't been enhanced since Oracle 8 client and
> there are a number of things that isn't supported in this provider.
> You can take a look at the link below which is a document that gives a
> pretty good overview over the various connectivity options.
> http://msdn2.microsoft.com/en-us/library/bb332055.aspx
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator|||Dahab wrote:
> Hi Steen,
> Yes it's a 64bit server.
> So I have to figure out how to use the Oracle driver then. Gave it a try a
> few days ago with no luck :(...
> Thanks very much Steen.
> D.
It's not that big a problem to use the Oracle driver. You'll have to
download the ODAC (
http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html)
and then make the entries in the tnsnames.ora file. Then you can use the
OraOledb.Oracle provider for setting up your Linked Server.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Linked server and MDAC driver.

...
Thanks very much Steen.
D.
""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:uKBKiD8iHHA.2272@.TK2MSFTNGP02.phx.gbl...
> Dahab wrote:
> Is it by any change a 64-bit installation you are using? The MSDAORA
> provider doesn't exists on the 64-bit version. Instead you should use the
> Oracle ODAC which is the OraOledb.Oracle provider. You should also be
> aware that the MSDAORA hasn't been enhanced since Oracle 8 client and
> there are a number of things that isn't supported in this provider.
> You can take a look at the link below which is a document that gives a
> pretty good overview over the various connectivity options.
> http://msdn2.microsoft.com/en-us/library/bb332055.aspx
> --
> Regards
> Steen Schlter Persson
> Database Administrator / System Administrator|||Dahab wrote:
> Hi Steen,
> Yes it's a 64bit server.
> So I have to figure out how to use the Oracle driver then. Gave it a try a
> few days ago with no luck

...
> Thanks very much Steen.
> D.
It's not that big a problem to use the Oracle driver. You'll have to
download the ODAC (
[url]http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html[/u
rl])
and then make the entries in the tnsnames.ora file. Then you can use the
OraOledb.Oracle provider for setting up your Linked Server.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

Wednesday, March 28, 2012

linked server problem

Monday, March 26, 2012

linked server problem

Linked Server not working

Linked Server not working

Friday, March 9, 2012

Linked server and MDAC driver.

Linked server and MDAC driver.

Linked server and MDAC driver.

Sql Linked Server

Blog Archive

About Me