Linked Server problem when querying

I have a linked SQL Server on another machine which is created using a stored procedure that executes the following:

EXEC sp_addlinkedserver @.server = 'Achilles\Mixed', @.srvproduct = ' ', @.provider = 'SQLNCLI', @.datasrc = 'Archilles\Mixed', @.catalog = 'DB_INTRANET'

The stored procedure executes successfully and I can also succesfully DROP the linked server. However when I try to query tables in linked databases using:

SELECT * FROM DB_INTRANET...Employees

I get the following error:

OLE DB provider "SQLNCLI" for linked server "DB_Intranet" returned message "Communication link failure".

Msg 10054, Level 16, State 1, Line 0 TCP Provider: An existing connection was forcibly closed by the remote host.

Msg 18452, Level 14, State 1, Line 0 Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

The logins are Windows Authentication and even if I use sp_addlinkedsrvlogin to map logins it still gives me the same error.

I have no problems linking and querying linked Access Databases but can't do it for the SQL Server DBs.

Does anyone have any suggestions please.

Hi,

the mapping of the logins did not work. What command did you use to use the WIndows login on the other server ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Please check two things:
1. make sure SQL 2005 allow remote access
2. make sure you set right userid/password for linked server.

Thanks

|||

Hi,

I have the same problem. But the distributed query seems to work on the management studio of the server but bot accross the network on other management studio with the same impersonated logins.

|||

I had to create a SQL authenticated login on the remote server (loginname) and then all local logins are mapped to the one remote SQL login using the Security page of the Linked Servers Properties dialogue.

I could not get it to pass Windows authenticated logins to the remote server at all.

In the Security Page of Linked Servers Properties dialogue.

For a Login not defined in the above list Connections will:

Be made using this security context:

Remote Login: Loginname

With Password: LoginPassword

Linked Server PROBLEM For MySQL Database

I created a ODBC DSN on my Windows Server for a remote UNIX box MySQL database. The TEST button returns the 'successful' message for the MySQL databaser. Also, when I use an ODBC DSNless string I found, that also successfully connects when I use it in some ASP pages.

However, I can not get a successful connection in the Enterprise Manager trying to use the DSN I created nor the DSNless string I used in the ASP pages. Can anyone tell me how to config the Enterprise Manager Linked Server settings listed below to connect to the MySQL database(s).

I added a new linked server, and selected the Microsoft OLEDB Provider for ODBC Drivers as the Provider name.

Does anyone know how do I configure the items below:

On the General tab:" option.
Product Name: xxxxxxxxxxxxxxxxxx
Data Source: xxxxxxx
Provider String: xxxxxxxx
Location: xxxxxxx
Catalog: xxxxx

On the Security tab:" option.
Remote Login: xxxxxx
With password: xxxxxx

Thanks.I found a wonderful solution to this problem...

http://bside.typepad.com/lifebsideben/2003/08/mysql_mssql_lin.html

In case the link is gone, here is the way to create a Linked Server to a MySQL database, in a nutshell...

1) Create a System DSN on the server running MSSQL, connecting to the MySQL database. Let's call it <the_new_mysql_dsn>.

2) Run the following stored procedure, with appropriate arguments...

EXEC sp_addlinkedserver @.server='<linked_server_label>', @.srvproduct='MySQL', @.provider='MSDASQL', @.datasrc='<the_new_mysql_dsn>'

-------

You can then open up ol' Enterprise Manager and see the new linked server added in. Right click on it to see the Properties. On mine, the settings showed up as...

Linked server: <linked_server_label>
Provider name: Microsoft OLE DB Provider for ODBC Drivers
Product name: MySQL
Data source: <the_new_mysql_dsn>

Everything else is blank.

-------

Incidentally, just to try it out, I deleted my new linked server, and then added a new one, using the following settings...

Linked server: <linked_server_label>
Provider name: Microsoft OLE DB Provider for ODBC Drivers
Provider string: DRIVER={MySQL ODBC 3.51 Driver};DESC=;DATABASE=<mysql_database>;SERVER=<mysql_server>;UID=;PORT=;OPTION=0;STMT=;

And it works as well (the connection string was generated within Visual Interdev). I am using no passwords yet, I'm still in the early development phases, but this should handle the basics of connecting to a MySQL database from SQL Server.

Linked Server Problem

Hi!

I have written a Perl Program, which works on a MS SQL SERVER 2000 database. It should import some data from an Oracle8i database. so i created a linked server and a linked server login. There were no problems when i created these things. But if i execute a "select ..."-Statement at the linked server(Oracle), i get the following message:

"[Microsoft][ODBC SQL Server Driver][SQL Server]Die Operation konnte nicht ausgefhrt werden, da der OLE DB-Provider 'MSDAORA' keine verteilte Transaktion beginnen konnte. (SQL-42000)
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB-Fehlertrace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b]. (SQL-01000)(DBD: st_execute/SQLExecute err=-1),ERROR = -1"

But the select statement, i used is ok. i started it in the Query Analyzer and everything was fine! This is the statement:

"select * from openquery(SRCLINKWORLD,'SELECT COUNT(*) AS ANZAHL FROM DBADMIN.ABSCHLUSSABR')"

Does anyone know , what the problem is?

NickWell...I know very little German (it is German isn't it), but what login did you use to connect with?

Is it a remote login in Oracle?

This is purely a shot in the dark...|||Strange SELECT you use,

why

"select * from openquery(SRCLINKWORLD,'SELECT COUNT(*) AS ANZAHL FROM DBADMIN.ABSCHLUSSABR')"

instead of

SELECT COUNT(*) AS ANZAHL FROM DBADMIN.ABSCHLUSSABR

Linked Server problem

Hi,

I have created a linked server with oracle database. Iam using SQL Server 200 and Oracle 8.0 client. I have made the registry settings as mentioned in KB280106. But when i I try to access the tables from the oracle database i get the following error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error. Authentication failed.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Any ideas how this problem can be solved.

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

"This error message indicates that the linked server does not have correct login mapping. You can execute the sp_helplinkedsrvlogin stored procedure to set the login information correctly. Also, verify that you have specified the correct parameters for the linked server configuration."

HTH

Linked Server problem

Hi,

I have created a linked server with oracle database. Iam using SQL Server 200 and Oracle 8.0 client. I have made the registry settings as mentioned in KB280106. But when i I try to access the tables from the oracle database i get the following error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error. Authentication failed.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Any ideas how this problem can be solved.

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

"This error message indicates that the linked server does not have correct login mapping. You can execute the sp_helplinkedsrvlogin stored procedure to set the login information correctly. Also, verify that you have specified the correct parameters for the linked server configuration."

HTH

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 performance

I have created a Linked Server(UnixServer - Progress DB) and that access is
thru an ODBC(Progress driver) connection. Then I do a
Select * from OpenQuery(LinkedServerName,'Select * from pub.tablename').
The table I'm reading from has around 2.5 million records.
I then created a VIEW from the above select statement. Now If I try to use
that view with any criteria "where" date_shipped = 02/06/07.
The query times-out.Is there anything I can do to improve performance?
Thanks.
It sounds like the WHERE condition is not being passed through
to the Progress DB server. There could be various reasons for
this, and two things you might try are
1) Try setting this up so you can use a 4-part name for the table:
SELECT * FROM LinkedServerName.<the database>.pub.tablename
and either add the WHERE clause to that or use that for the view
definition.
2) Try using WHERE date_shipped = cast('070206' as smalldatetime).
Perhaps when you pass a string (or the number 0, which is what you
are passing if you have no quotes around the 02/06/07), the comparison
can't be done on the server site.
SQL Server 2005 allows you to write
EXECUTE (@.querystring) AT LinkedServerName. Whether this is
likely to be faster, I don't know.
You could look at the query plan for your slow SELECT query and
see if all the rows of the table are being passed to the client to
accomplish the WHERE clause filtering. (not that would alone give
you an alternative).
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
Andy wrote:

>I have created a Linked Server(UnixServer - Progress DB) and that access is
>thru an ODBC(Progress driver) connection. Then I do a
> Select * from OpenQuery(LinkedServerName,'Select * from pub.tablename').
>The table I'm reading from has around 2.5 million records.
>I then created a VIEW from the above select statement. Now If I try to use
>that view with any criteria "where" date_shipped = 02/06/07.
>The query times-out.Is there anything I can do to improve performance?
>
> Thanks.
>
>

linked server performance

I have created a Linked Server(UnixServer - Progress DB) and that access is
thru an ODBC(Progress driver) connection. Then I do a
Select * from OpenQuery(LinkedServerName,'Select * from pub.tablename').
The table I'm reading from has around 2.5 million records.
I then created a VIEW from the above select statement. Now If I try to use
that view with any criteria "where" date_shipped = 02/06/07.
The query times-out.Is there anything I can do to improve performance?
Thanks.It sounds like the WHERE condition is not being passed through
to the Progress DB server. There could be various reasons for
this, and two things you might try are
1) Try setting this up so you can use a 4-part name for the table:
SELECT * FROM LinkedServerName.<the database>.pub.tablename
and either add the WHERE clause to that or use that for the view
definition.
2) Try using WHERE date_shipped = cast('070206' as smalldatetime).
Perhaps when you pass a string (or the number 0, which is what you
are passing if you have no quotes around the 02/06/07), the comparison
can't be done on the server site.
SQL Server 2005 allows you to write
EXECUTE (@.querystring) AT LinkedServerName. Whether this is
likely to be faster, I don't know.
You could look at the query plan for your slow SELECT query and
see if all the rows of the table are being passed to the client to
accomplish the WHERE clause filtering. (not that would alone give
you an alternative).
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
Andy wrote:
>I have created a Linked Server(UnixServer - Progress DB) and that access is
>thru an ODBC(Progress driver) connection. Then I do a
> Select * from OpenQuery(LinkedServerName,'Select * from pub.tablename').
>The table I'm reading from has around 2.5 million records.
>I then created a VIEW from the above select statement. Now If I try to use
>that view with any criteria "where" date_shipped = 02/06/07.
>The query times-out.Is there anything I can do to improve performance?
>
> Thanks.
>
>sql

linked server performance

I have created a Linked Server(UnixServer - Progress DB) and that access is
thru an ODBC(Progress driver) connection. Then I do a
Select * from OpenQuery(LinkedServerName,'Select * from pub.tablename').
The table I'm reading from has around 2.5 million records.
I then created a VIEW from the above select statement. Now If I try to use
that view with any criteria "where" date_shipped = 02/06/07.
The query times-out.Is there anything I can do to improve performance?
Thanks.It sounds like the WHERE condition is not being passed through
to the Progress DB server. There could be various reasons for
this, and two things you might try are
1) Try setting this up so you can use a 4-part name for the table:
SELECT * FROM LinkedServerName.<the database>.pub.tablename
and either add the WHERE clause to that or use that for the view
definition.
2) Try using WHERE date_shipped = cast('070206' as smalldatetime).
Perhaps when you pass a string (or the number 0, which is what you
are passing if you have no quotes around the 02/06/07), the comparison
can't be done on the server site.
SQL Server 2005 allows you to write
EXECUTE (@.querystring) AT LinkedServerName. Whether this is
likely to be faster, I don't know.
You could look at the query plan for your slow SELECT query and
see if all the rows of the table are being passed to the client to
accomplish the WHERE clause filtering. (not that would alone give
you an alternative).
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
Andy wrote:

>I have created a Linked Server(UnixServer - Progress DB) and that access is
>thru an ODBC(Progress driver) connection. Then I do a
> Select * from OpenQuery(LinkedServerName,'Select * from pub.tablename').
>The table I'm reading from has around 2.5 million records.
>I then created a VIEW from the above select statement. Now If I try to use
>that view with any criteria "where" date_shipped = 02/06/07.
>The query times-out.Is there anything I can do to improve performance?
>
> Thanks.
>
>

Linked Server Not Displaying Table Details

I have created a linked server using my local SQL2005. The linked server can be seen as a linked server, but the database can not been expanded to see the tables, stored procedures, views, and other details regarding the linked server. The only method that I have found that will allow me to see all the details is to use XP's Remote Desktop Connection to access the database. Has anyone else experienced this problem, if so, how did you resolve it?

Hi,

you need SQL Server 2k5 SP2 to browse through databases, tables etc from UI.

Code Snippet

EXEC sp_addlinkedserver
@.server = N'LONDON2',
@.srvproduct = N'',
@.provider = N'SQLNCLI',
@.provstr = N'SERVER=192.168.100.150;Integrated Security=True'

EXEC sp_tables_ex @.table_server = 'LONDON2',
@.table_catalog = 'AdventureWorks',
@.table_schema = 'HumanResources',
@.table_type = 'TABLE'

linked server login problem... with a particular login... on a particular box

Im having a problem with one particular Linked Server Login. I've created
two of them. Neither of which has an existing Login on the Linked Server and
both of which are mapped to a member of SA. In the below examply, Login1
will work, but Login2 will fail with:
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
, @.useself = 'false'
, @.locallogin = 'domain\Login1'
, @.rmtuser = 'RmtLogin1'
, @.rmtpassword = 'password'
sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
, @.useself = 'false'
, @.locallogin = 'domain\Login2'
, @.rmtuser = 'RmtLogin1'
, @.rmtpassword = 'password'
Again, please keep in mind that the Login on the Linked Server is in the SA
role, and that the top Login works. Another important tip, both of these
work successfully on another box, just not the one that counts.
All ideas are greatly appreciated as Im quite desperate.
--
SQL2K SP3
TIA, ChrisRI think its related to this issue but Im not sure.
http://support.microsoft.com/default.aspx?scid=kb;en-us;238477
I don't really need a fix for this anyomore as I've found a workaround and
only needed this temporarily. More info: I had opened Query Analyzer under
the failing account on my local box... RClick/ Run as. But if I log directly
onto the box I was trying to run the query from it works fine. Which is why
Im thinking its related to the above article.
"ChrisR" <bla@.noemail.com> wrote in message
news:#uUSqB4EFHA.2608@.TK2MSFTNGP10.phx.gbl...
> Im having a problem with one particular Linked Server Login. I've created
> two of them. Neither of which has an existing Login on the Linked Server
and
> both of which are mapped to a member of SA. In the below examply, Login1
> will work, but Login2 will fail with:
> Server: Msg 18456, Level 14, State 1, Line 1
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
> , @.useself = 'false'
> , @.locallogin = 'domain\Login1'
> , @.rmtuser = 'RmtLogin1'
> , @.rmtpassword = 'password'
> sp_addlinkedsrvlogin @.rmtsrvname = 'server2'
> , @.useself = 'false'
> , @.locallogin = 'domain\Login2'
> , @.rmtuser = 'RmtLogin1'
> , @.rmtpassword = 'password'
> Again, please keep in mind that the Login on the Linked Server is in the
SA
> role, and that the top Login works. Another important tip, both of these
> work successfully on another box, just not the one that counts.
> All ideas are greatly appreciated as Im quite desperate.
> --
> SQL2K SP3
> TIA, ChrisR
>

Linked Server in Management Studio

I have created Linked Servers for Access databases. SPs are able to select data from the Linked Servers.

In Management Studio, the General Properties are greyed out and therefore can't be modified. I need to modify the Data Source to connect to different Access databases. Can't find a way to do this with a SP either. Searches seem to indicate I should be able to modify the properties using Management Studio.

Management Studio does not display the list of tables, etc. in the Access database as Enterprise Manager did for a Linked Server. The data source is on a mapped drive. The definition:

/****** Object: LinkedServer [ACCESS GENERAL] Script Date: 02/05/2007 11:56:43 ******/

EXEC master.dbo.sp_addlinkedserver @.server = N'ACCESS GENERAL',

@.srvproduct=N'OLE DB Provider for Jet',

@.provider=N'Microsoft.Jet.OLEDB.4.0',

@.datasrc=N'\\tacir2k3\Infrastructure\Databases\July 2005 Databases\General\GenDb2006_TR_Db.mdb'

Security is "Be made without a security context".

What is causing this behaviour?

Also, I cannot compile a stored procedure in VS2005 that references the Linked Server. But I can compile it using Management Studio.

Have you tried if the connection is at all made to the linked server. You can do that using the stored procedure sp_testlinkedserver @.servername.
Also, try connecting with the login's current security context unless your datasource is available for all.

Thanks,
Kuntal

|||

After creation of a Linked Server, the General Properties page will be greyed out (hence can't be modified), this is by design. You can delete the existing Linked Server and create a new one for the other Data Source.

Thanks
Arpita

|||

True in this case for the new connection you have to recreate the Linked server connection again.

If you want to import the data from that access database with some tables then you can take help of DTS too.

|||

Is it also by design that it no longer displays a list of the tables in the linked database?

Does anyone know why I can't compile a SP in VS2005 that references a linked database? It will compile in Management Studio and it will execute in Management Studio.

Linked server help: MAS90 URGENT!

Has anyone successfully created a linked server to a MAS90 database? I have
tried creating a UDL file and copying the connection string into the linked
server area, but Im running into errors when trying to access anything in
the database. I have also tried using OPENQUERY with the same connection
string from the UDL. I am able to create a linked table to MAS in access
just fine. I have emailed Sage, they gave me a sample but it didnt work.
select * from
openrowset( 'MSDASQL',
'DSN=SOTAMAS90;UID=7AB;PWD=;DESCRIPTION=MAS 90 32-Bit ODBC Driver;
Directory=\\MAS200\MAS90;Company=D94;SERVER=NotTheServer','select * from
GL5_DetailPosting' ) as A
This is the error message with OPENQUERY, which is more descriptive then
EM's error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider reported an
unexpected catastrophic failure.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 53 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x8000ffff: The provider reported an unexpected catastrophic
failure.].I normally use udl file if I want to create a connection string in the
asp/.net file. rather than hand type the mumbo jumbo, I can just copy
and paste into the file. But never on creating a linked server.
I don't think you can use the connection string the udl file created
(at least not entirely) to create a linked server. Particularly you
hit a problem now, it is better to create it from scratch.
Here is the syntax of OPENROWSET.
OPENROWSET ( 'provider_name'
, { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query' }
)
For your case (with the information above), it should be:
SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=NotTheServer;UID=7AB;PWD=xxx',
GL5_DetailsPosting) AS a
For the driver name, check your ODBC datasource Administrator panel
'drivers' tag.
Melsql

Linked server help: MAS90 URGENT!

Has anyone successfully created a linked server to a MAS90 database? I have
tried creating a UDL file and copying the connection string into the linked
server area, but Im running into errors when trying to access anything in
the database. I have also tried using OPENQUERY with the same connection
string from the UDL. I am able to create a linked table to MAS in access
just fine. I have emailed Sage, they gave me a sample but it didnt work.
select * from
openrowset( 'MSDASQL',
'DSN=SOTAMAS90;UID=7AB;PWD=;DESCRIPTION=
MAS 90 32-Bit ODBC Driver;
Directory=\\MAS200\MAS90;Company=D94;SER
VER=NotTheServer','select * from
GL5_DetailPosting' ) as A
This is the error message with OPENQUERY, which is more descriptive then
EM's error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider reported an
unexpected catastrophic failure.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 53 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x8000ffff: The provider reported an unexpected catastrophic
failure.].I normally use udl file if I want to create a connection string in the
asp/.net file. rather than hand type the mumbo jumbo, I can just copy
and paste into the file. But never on creating a linked server.
I don't think you can use the connection string the udl file created
(at least not entirely) to create a linked server. Particularly you
hit a problem now, it is better to create it from scratch.
Here is the syntax of OPENROWSET.
OPENROWSET ( 'provider_name'
, { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query' }
)
For your case (with the information above), it should be:
SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=NotTheServer;UID=7AB;PWD=
xxx',
GL5_DetailsPosting) AS a
For the driver name, check your ODBC datasource Administrator panel
'drivers' tag.
Mel

Linked server for Oracle on SQL server - query performance

Hi folks,
I have created a linked server on SQL server 2000 for an Oracle 8i
database using Microsoft's OLE provider for ORACLE. The connection
works fine and I am able to run queries (select queries) agaist the
Oracle table from SQL query analyzer.
But the problem is, the query is miserably slower than if I run the
query directly on the Oracle table using either SQL*plus or TOAD. In
the former case (linked table) the query run time is upwards of 7
seconds whereas, in the latter case (directly using SQL*plus) the
query runs in 200 milli seconds. The table is indexed and I am hitting
the index columns.
Any ideas or suggestions? As always thanks in advance and your help
will be much appreciated.
Appu.
Hi everybody,
I am experiencing the same problems as explained below. Help is appriciated.
ETT
"Sat" wrote:

> Hi folks,
> I have created a linked server on SQL server 2000 for an Oracle 8i
> database using Microsoft's OLE provider for ORACLE. The connection
> works fine and I am able to run queries (select queries) agaist the
> Oracle table from SQL query analyzer.
> But the problem is, the query is miserably slower than if I run the
> query directly on the Oracle table using either SQL*plus or TOAD. In
> the former case (linked table) the query run time is upwards of 7
> seconds whereas, in the latter case (directly using SQL*plus) the
> query runs in 200 milli seconds. The table is indexed and I am hitting
> the index columns.
> Any ideas or suggestions? As always thanks in advance and your help
> will be much appreciated.
> Appu.
>
|||Depending on the capabilities of the data provider used for the linked
server and various run-time settings or details of your query, such as
collations, it may not be possible for the SQL Server query processor to
send the relevant part of the WHERE condition to the Oracle server.
Instead, the SQL Server may be requesting an entire table from the
Oracle server and filtering the rows afterwards without the benefit of
the Oracle index. There is no one way to resolve this. The semantics
of the query may mean it is simply wrong to filter the query on the
Oracle side, but if there is a solution, look at data provider settings,
ANSI compliance settings, collations, and exact matching of data types.
You might also post your question in an Oracle group, since Oracle users
might be able to help.
Steve Kass
Drew University
ETT wrote:
[vbcol=seagreen]
>Hi everybody,
>I am experiencing the same problems as explained below. Help is appriciated.
>ETT
>"Sat" wrote:
>

Linked server for Oracle on SQL server - query performance

Hi folks,
I have created a linked server on SQL server 2000 for an Oracle 8i
database using Microsoft's OLE provider for ORACLE. The connection
works fine and I am able to run queries (select queries) agaist the
Oracle table from SQL query analyzer.
But the problem is, the query is miserably slower than if I run the
query directly on the Oracle table using either SQL*plus or TOAD. In
the former case (linked table) the query run time is upwards of 7
seconds whereas, in the latter case (directly using SQL*plus) the
query runs in 200 milli seconds. The table is indexed and I am hitting
the index columns.
Any ideas or suggestions? As always thanks in advance and your help
will be much appreciated.
Appu.Hi everybody,
I am experiencing the same problems as explained below. Help is appriciated.
ETT
"Sat" wrote:

> Hi folks,
> I have created a linked server on SQL server 2000 for an Oracle 8i
> database using Microsoft's OLE provider for ORACLE. The connection
> works fine and I am able to run queries (select queries) agaist the
> Oracle table from SQL query analyzer.
> But the problem is, the query is miserably slower than if I run the
> query directly on the Oracle table using either SQL*plus or TOAD. In
> the former case (linked table) the query run time is upwards of 7
> seconds whereas, in the latter case (directly using SQL*plus) the
> query runs in 200 milli seconds. The table is indexed and I am hitting
> the index columns.
> Any ideas or suggestions? As always thanks in advance and your help
> will be much appreciated.
> Appu.
>|||Depending on the capabilities of the data provider used for the linked
server and various run-time settings or details of your query, such as
collations, it may not be possible for the SQL Server query processor to
send the relevant part of the WHERE condition to the Oracle server.
Instead, the SQL Server may be requesting an entire table from the
Oracle server and filtering the rows afterwards without the benefit of
the Oracle index. There is no one way to resolve this. The semantics
of the query may mean it is simply wrong to filter the query on the
Oracle side, but if there is a solution, look at data provider settings,
ANSI compliance settings, collations, and exact matching of data types.
You might also post your question in an Oracle group, since Oracle users
might be able to help.
Steve Kass
Drew University
ETT wrote:
[vbcol=seagreen]
>Hi everybody,
>I am experiencing the same problems as explained below. Help is appriciated
.
>ETT
>"Sat" wrote:
>
>

Linked Server for Oracle

Hi

I need to migrate data from an Oracle database to a SQL Server 2005 database (Dev Edition),

I created a linked server object using the following : -

exec sp_addlinkedserver 'JAVAX', 'ORACLE','OraOLEDB.Oracle', 'JAVAX.world'

exec sp_addlinkedsrvlogin 'JAVAX','false','XXX','XXX','YYY'

GO

XXX login exists on sql server as well as Oracle.

However I am unable to see any of the tables under the linked server and when I try to access the query like below it returns an error "Invalid object name 'JAVA109.sfmfg.SFCORE_USER'."

Select * from JAVAX.sfmfg.SFCORE_USER

Insert a dot after "JAVAX":

Select * from JAVAX..sfmfg.SFCORE_USER

I'm assuming the server alias is "JAVAX", the schema owner is "sfmfg" and the table or view is "SFCORE_USER".

linked server failed

hi,
i have added a link server but it fails when i try to retrieve data.
SQL server in on the same machine
i have created the another SQL server instance
Main Server: SERVER1
Instance: SERVER1/NewServer
EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
Server'
EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
when i try to run the above query it gives me the error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.
anyone have suggestion?
Adi
Adi
Try
select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
"Adi" <Adi@.discussions.microsoft.com> wrote in message
news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
> hi,
> i have added a link server but it fails when i try to retrieve data.
> SQL server in on the same machine
> i have created the another SQL server instance
> Main Server: SERVER1
> Instance: SERVER1/NewServer
> EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
> Server'
> EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
> select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
> when i try to run the above query it gives me the error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '/'.
>
> anyone have suggestion?
> Adi
>
|||yes i tried this too but it gives me the message:
SQL Server Does not exists or access denied.
i have also checked the sysservers the linked server is exist in it.
when i try to run retrieve data from main server its working fine but when i
try to retrieve data from SQL Server instance it failed.
"Uri Dimant" wrote:

> Adi
> Try
> select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
> "Adi" <Adi@.discussions.microsoft.com> wrote in message
> news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
>
>
|||Try looking up the CORRECT syntax in Books Online.
You are using the wrong slash.
Try [Server1\NewServer].
Sincerely,
Anthony Thomas

"Adi" <Adi@.discussions.microsoft.com> wrote in message news:2C6DA00D-ACDE-44D0-AC42-F6F3DC34D58C@.microsoft.com...
yes i tried this too but it gives me the message:
SQL Server Does not exists or access denied.
i have also checked the sysservers the linked server is exist in it.
when i try to run retrieve data from main server its working fine but when i
try to retrieve data from SQL Server instance it failed.
"Uri Dimant" wrote:

> Adi
> Try
> select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
>
> "Adi" <Adi@.discussions.microsoft.com> wrote in message
> news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
>
>
>|||I Anthony.
I have the same problem of Adi (SQL Server Does not exists or access
denied), but my server name is nsv, so the problem is not the slash.
The likend server it seems to be created correctly.
Any suggestion?
"AnthonyThomas" wrote:
[vbcol=seagreen]
> Try looking up the CORRECT syntax in Books Online.
> You are using the wrong slash.
> Try [Server1\NewServer].
> Sincerely,
>
> Anthony Thomas
>
> --
> "Adi" <Adi@.discussions.microsoft.com> wrote in message news:2C6DA00D-ACDE-44D0-AC42-F6F3DC34D58C@.microsoft.com...
> yes i tried this too but it gives me the message:
> SQL Server Does not exists or access denied.
> i have also checked the sysservers the linked server is exist in it.
> when i try to run retrieve data from main server its working fine but when i
> try to retrieve data from SQL Server instance it failed.
>
> "Uri Dimant" wrote:

linked server failed

hi,
i have added a link server but it fails when i try to retrieve data.
SQL server in on the same machine
i have created the another SQL server instance
Main Server: SERVER1
Instance: SERVER1/NewServer
EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
Server'
EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
when i try to run the above query it gives me the error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.
anyone have suggestion'
AdiAdi
Try
select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
"Adi" <Adi@.discussions.microsoft.com> wrote in message
news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
> hi,
> i have added a link server but it fails when i try to retrieve data.
> SQL server in on the same machine
> i have created the another SQL server instance
> Main Server: SERVER1
> Instance: SERVER1/NewServer
> EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
> Server'
> EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
> select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
> when i try to run the above query it gives me the error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '/'.
>
> anyone have suggestion'
> Adi
>|||yes i tried this too but it gives me the message:
SQL Server Does not exists or access denied.
i have also checked the sysservers the linked server is exist in it.
when i try to run retrieve data from main server its working fine but when i
try to retrieve data from SQL Server instance it failed.
"Uri Dimant" wrote:
> Adi
> Try
> select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
> "Adi" <Adi@.discussions.microsoft.com> wrote in message
> news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
> > hi,
> > i have added a link server but it fails when i try to retrieve data.
> >
> > SQL server in on the same machine
> > i have created the another SQL server instance
> >
> > Main Server: SERVER1
> > Instance: SERVER1/NewServer
> >
> > EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
> > Server'
> > EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
> >
> > select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
> >
> > when i try to run the above query it gives me the error
> > Server: Msg 170, Level 15, State 1, Line 1
> > Line 1: Incorrect syntax near '/'.
> >
> >
> > anyone have suggestion'
> > Adi
> >
>
>|||I Anthony.
I have the same problem of Adi (SQL Server Does not exists or access
denied), but my server name is nsv, so the problem is not the slash.
The likend server it seems to be created correctly.
Any suggestion?
"AnthonyThomas" wrote:
> Try looking up the CORRECT syntax in Books Online.
> You are using the wrong slash.
> Try [Server1\NewServer].
> Sincerely,
>
> Anthony Thomas
>
> --
> "Adi" <Adi@.discussions.microsoft.com> wrote in message news:2C6DA00D-ACDE-44D0-AC42-F6F3DC34D58C@.microsoft.com...
> yes i tried this too but it gives me the message:
> SQL Server Does not exists or access denied.
> i have also checked the sysservers the linked server is exist in it.
> when i try to run retrieve data from main server its working fine but when i
> try to retrieve data from SQL Server instance it failed.
>
> "Uri Dimant" wrote:
> > Adi
> > Try
> > select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
> >
> > "Adi" <Adi@.discussions.microsoft.com> wrote in message
> > news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
> > > hi,
> > > i have added a link server but it fails when i try to retrieve data.
> > >
> > > SQL server in on the same machine
> > > i have created the another SQL server instance
> > >
> > > Main Server: SERVER1
> > > Instance: SERVER1/NewServer
> > >
> > > EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
> > > Server'
> > > EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
> > >
> > > select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
> > >
> > > when i try to run the above query it gives me the error
> > > Server: Msg 170, Level 15, State 1, Line 1
> > > Line 1: Incorrect syntax near '/'.
> > >
> > >
> > > anyone have suggestion'
> > > Adi
> > >
> >
> >
> >

linked server failed

hi,
i have added a link server but it fails when i try to retrieve data.
SQL server in on the same machine
i have created the another SQL server instance
Main Server: SERVER1
Instance: SERVER1/NewServer
EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
Server'
EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
when i try to run the above query it gives me the error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.
anyone have suggestion'
AdiAdi
Try
select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
"Adi" <Adi@.discussions.microsoft.com> wrote in message
news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
> hi,
> i have added a link server but it fails when i try to retrieve data.
> SQL server in on the same machine
> i have created the another SQL server instance
> Main Server: SERVER1
> Instance: SERVER1/NewServer
> EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
> Server'
> EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
> select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
> when i try to run the above query it gives me the error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '/'.
>
> anyone have suggestion'
> Adi
>|||yes i tried this too but it gives me the message:
SQL Server Does not exists or access denied.
i have also checked the sysservers the linked server is exist in it.
when i try to run retrieve data from main server its working fine but when i
try to retrieve data from SQL Server instance it failed.
"Uri Dimant" wrote:

> Adi
> Try
> select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
> "Adi" <Adi@.discussions.microsoft.com> wrote in message
> news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
>
>|||Try looking up the CORRECT syntax in Books Online.
You are using the wrong slash.
Try [Server1\NewServer].
Sincerely,
Anthony Thomas
--
"Adi" <Adi@.discussions.microsoft.com> wrote in message news:2C6DA00D-ACDE-
44D0-AC42-F6F3DC34D58C@.microsoft.com...
yes i tried this too but it gives me the message:
SQL Server Does not exists or access denied.
i have also checked the sysservers the linked server is exist in it.
when i try to run retrieve data from main server its working fine but when
i
try to retrieve data from SQL Server instance it failed.
"Uri Dimant" wrote:

> Adi
> Try
> select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct
>
> "Adi" <Adi@.discussions.microsoft.com> wrote in message
> news:EA624DF8-B02E-4FC6-8E01-347B1AEC933D@.microsoft.com...
>
>
>|||I Anthony.
I have the same problem of Adi (SQL Server Does not exists or access
denied), but my server name is nsv, so the problem is not the slash.
The likend server it seems to be created correctly.
Any suggestion?
"AnthonyThomas" wrote:
[vbcol=seagreen]
> Try looking up the CORRECT syntax in Books Online.
> You are using the wrong slash.
> Try [Server1\NewServer].
> Sincerely,
>
> Anthony Thomas
>
> --
> "Adi" <Adi@.discussions.microsoft.com> wrote in message news:2C6DA00D-ACD
E-44D0-AC42-F6F3DC34D58C@.microsoft.com...
> yes i tried this too but it gives me the message:
> SQL Server Does not exists or access denied.
> i have also checked the sysservers the linked server is exist in it.
> when i try to run retrieve data from main server its working fine but wh
en i
> try to retrieve data from SQL Server instance it failed.
>
> "Uri Dimant" wrote:
>