Linked Table

Hi all,
is it possible to have a linked table in sql 2000 in the tables section of e
nterprise manager - the same painless way its done in access?
Many thanks
IGNope. Different technology. SQL Server uses the concept of linked
servers, not linked tables. See the "distributed queries" topics in
SQL BooksOnline for more info.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Wed, 14 Jan 2004 03:46:08 -0800, "Igloo"
<anonymous@.discussions.microsoft.com> wrote:

quote:

---

>Hi all,
> is it possible to have a linked table in sql 2000 in the tables sect
ion of enterprise manager - the same painless way its done in access?
>Many thanks
>IG

---

sql

servers and MDAC 2.8

I am currently configuring and testing a new SQL server:
SQL 2000 Enterprise Edition SP3a
Windows 2003 Enterprise Edition
MDAC 2.8
I am experiencing some strange behaviour with linked SQL
servers from the new server.
This 1st SQL stmt below works OK from the new server
select contact_id from ls.lat_prod_ed.dbo.contact
This 2nd SQL stmt fails on the new server with general
network error after approx 25 seconds.
select * from ls.lat_prod_ed.dbo.contact
Server: Msg 11, Level 16, State 1, Line 5
General network error. Check your network documentation.
A SQL profiler trace shows the following error being
raised:
OLE/DB Provider 'Unknown' ::ReleaseSchemaLock returned
0x80040e96: OLE DB provider Unknown supported the Schema
Lock interface, but returned0x80040e96 for
ReleaseSchemaLock .
The target linked server (ls) is running SQL 2000 sp3a
Standard Edition on Windows 2000 sp3. It was on MDAC 2.7
and I've now upgraded it to MDAC 2.8 but get the same
error.
I've run component checker on both servers and it reports
everything OK.
The SQL statement that fails, works OK when run from
another SQL server (SQL 2000 Enterprise Edition sp3a,
Windows 2000 Advanced server sp3 and MDAC 2.7 sp1 refresh)
and pointing to the same target linked server (ls).
Thanks in advance
Greg DaviesDoes openquery work? Does this happen only for '*' or for other columns of
the table as well? How was this linked server created? Through enterprise
manager?
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.

servers and MDAC 2.8

I am currently configuring and testing a new SQL server:
SQL 2000 Enterprise Edition SP3a
Windows 2003 Enterprise Edition
MDAC 2.8
I am experiencing some strange behaviour with linked SQL
servers from the new server.
This 1st SQL stmt below works OK from the new server
select contact_id from ls.lat_prod_ed.dbo.contact
This 2nd SQL stmt fails on the new server with general
network error after approx 25 seconds.
select * from ls.lat_prod_ed.dbo.contact
Server: Msg 11, Level 16, State 1, Line 5
General network error. Check your network documentation.
A SQL profiler trace shows the following error being
raised:
OLE/DB Provider 'Unknown' ::ReleaseSchemaLock returned
0x80040e96: OLE DB provider Unknown supported the Schema
Lock interface, but returned0x80040e96 for
ReleaseSchemaLock .
The target linked server (ls) is running SQL 2000 sp3a
Standard Edition on Windows 2000 sp3. It was on MDAC 2.7
and I've now upgraded it to MDAC 2.8 but get the same
error.
I've run component checker on both servers and it reports
everything OK.
The SQL statement that fails, works OK when run from
another SQL server (SQL 2000 Enterprise Edition sp3a,
Windows 2000 Advanced server sp3 and MDAC 2.7 sp1 refresh)
and pointing to the same target linked server (ls).
Thanks in advance
Greg Davies
Does openquery work? Does this happen only for '*' or for other columns of
the table as well? How was this linked server created? Through enterprise
manager?
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.
sql

servers - Postgres 8.0.2

Hi,

I have: (WinXP+SP2+MSSQL2k+Postgres 8.0.2)
I'm trying to link the PostgreSQL 8.0.2 server
in Enterprise Manager Tree
and don't know some of the parameters.
Has someone of you a Postgres server linked to MSSQL?
Could you tell me, what data you have, please?

Security / Linked Servers / New Linked Server...

Linked Server:.........?
Provider Name: PostgreSQL OLE DB Provider (do you us this one?)
Product name:..........?
Data source:...........?
Provider string:........?
Location:.............?

Distributed Transaction Coordinator i running.
What else should I do?

Thanks a lot if you could help,
Maniek.Maniek (nospam@.nospam.pl) writes:
> I have: (WinXP+SP2+MSSQL2k+Postgres 8.0.2)
> I'm trying to link the PostgreSQL 8.0.2 server
> in Enterprise Manager Tree
> and don't know some of the parameters.
> Has someone of you a Postgres server linked to MSSQL?

I don't know Postgres, but it's not really that much different
from connecting from a client.

> Linked Server:.........?

This is just an alias, so you can put any name here.

> Provider Name: PostgreSQL OLE DB Provider (do you us this one?)
> Product name:..........?

Try Postgres.

> Data source:...........?

You can leave this blank, if you include the data source in the
provider srting.

> Provider string:........?

The connect string you would use in an application using the
PostgreSQL OLE DB Provider.

> Location:.............?

Odds are good that you can leave this blank, although it depends
on the OLE DB provider.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:

>> Linked Server:.........?
> This is just an alias, so you can put any name here.
[...]
Thank you very much you've tried to help me.
Finally I've solved the problem using ODBC.
With OLE DB it was very difficult - it's not very stable.

servers

I am trying to Link an Excel 2003 spreadsheet to a Enterprise Ed of SQL
Server running Windows 2000 AS.
I add the spreadsheet as a Linked server with the following configuration:
linked server: TEST
server Type: Microsoft Jet 4.0 OLD DB Provider
Product name: Excel
Data Source: c:\temp\new.xls
Security
--
Be made without using a security context - CHECKED
It then makes the connection and I can see the tables and views. When I
click on the table in Enterprise manager i get:
Error 7399: OLE DB provider 'microsoft.Jet.OLEDB.4.0' reported an error.
OLE DB Trace [OLE/DB Provider 'microsoft.Jet.OLEDB.4.0'IDBInitialize
returned 0x80004005: ].
PLEASE HELP !!!May I ask what you are trying to achieve by linking to an Excel
spreadsheet? It is more customary to go the other way and use SQL
Server as the data store, exporting data to Excel as needed.
--Mary
On Tue, 23 Aug 2005 08:48:48 -0700, Sajjad Rizvi
<SajjadRizvi@.discussions.microsoft.com> wrote:

>I am trying to Link an Excel 2003 spreadsheet to a Enterprise Ed of SQL
>Server running Windows 2000 AS.
>I add the spreadsheet as a Linked server with the following configuration:
>linked server: TEST
>server Type: Microsoft Jet 4.0 OLD DB Provider
>Product name: Excel
>Data Source: c:\temp\new.xls
>Security
>--
>Be made without using a security context - CHECKED
>It then makes the connection and I can see the tables and views. When I
>click on the table in Enterprise manager i get:
>Error 7399: OLE DB provider 'microsoft.Jet.OLEDB.4.0' reported an error.
>OLE DB Trace [OLE/DB Provider 'microsoft.Jet.OLEDB.4.0'IDBInitialize
>returned 0x80004005: ].
>PLEASE HELP !!!

server to Postgres

Hi

Can anyone help me with this.. driving me nuts.
when setting up a linked server to Postgres 7.3.1 (specifics*) I can see the tables in Enterprise Manager but in Query Analyzer i get this error when doing the below queries

SELECT * FROM OPENQUERY(POSTGRES_1,'select * from dbo.TestTable')

OR

SELECT * FROM OPENQUERY(POSTGRES_1,'select * from TestTable')

Error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'select id,value_char from dbo.Test_Default_1'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='MSDASQL', Query=select id,value_char from dbo.Test_Default_1'].http://support.microsoft.com/default.aspx?scid=kb;EN-US;270119 to followup the issue and resolve.|||Hi Satya,

I have seen this article before and it does not help with this issue as I cannot select data even using the pass through query syntax shown. Is there anyone you know that has any light on this problem. Getting data through the ODBC connection is fine it's just that the OPENQUERY() returns the error.

Regards...|||The only option to suspect is about compatible/proper driver from Postgres, have you tried on Postgres website for any information.|||I have and I downloaded the version of the ODBC which should be compatible with the current version of Postgres which is 7.3.1. It just keeps bugging me that this is possible an issue with OLEDB for ODBC rather than the ODBC Driver itself but I can't pin point it.|||Try with latest version of MDAC and OLEDB provider.|||Will try...