Linked Server query

I have set up a link server to the AS400and I can see the tables in the Enterprise Manager, How do I query it so I can use the data in my SQL Server database.
*****************************************
* This message was posted via http://sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://sqlmonster.com/Uwe/Abuse.aspx...b79e73dd024921
*****************************************
Take a look at OPENQUERY in the BOL
Rick Sawtell
MCT, MCSD, MCDBA
"Vince Lassiter via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:66558ca34dd7468082b79e73dd024921@.SQLMonster.c om...
> I have set up a link server to the AS400and I can see the tables in the
Enterprise Manager, How do I query it so I can use the data in my SQL Server
database.
> *****************************************
> * This message was posted via http://sqlmonster.com
> *
> * Report spam or abuse by clicking the following URL:
> *
http://sqlmonster.com/Uwe/Abuse.aspx...b79e73dd024921
> *****************************************
sql

linked server query

Hi,
I have added a linked server. Now , how can I query the tables and also I
only see one of the few databases only on the linked server. How can I see
all the databases of linked server.
Thanks,
Via the linked server you will see your 'default database' objects on that
target database. Although you cannot graphically browse the multiple
databases that you have access to on the target linked server, you will be
able to access those other databases and tables via querying.
When querying the databases on the linked server, make sure to set your ANSI
settings and issue the queries using the 4-part object context:
ex:
USE ANSI_NULLS ON
GO
USE ANSI_WARNINGS ON
GO
SELECT name FROM <LINKEDSERVERNAME>.Northwind.dbo.Customers
Replace <LINKEDSERVERNAME> with the name of the linked server
"dotnettester" wrote:

> Hi,
> I have added a linked server. Now , how can I query the tables and also I
> only see one of the few databases only on the linked server. How can I see
> all the databases of linked server.
> Thanks,
>
|||Hi,
Before u query to linked server table see in linked server property
that rpc option are checked.
use the select * from [linked
servername].databasename.username.tablename
hope this help
from
killer

linked server query

Hi,
I have added a linked server. Now , how can I query the tables and also I
only see one of the few databases only on the linked server. How can I see
all the databases of linked server.
Thanks,Via the linked server you will see your 'default database' objects on that
target database. Although you cannot graphically browse the multiple
databases that you have access to on the target linked server, you will be
able to access those other databases and tables via querying.
When querying the databases on the linked server, make sure to set your ANSI
settings and issue the queries using the 4-part object context:
ex:
USE ANSI_NULLS ON
GO
USE ANSI_WARNINGS ON
GO
SELECT name FROM <LINKEDSERVERNAME>.Northwind.dbo.Customers
Replace <LINKEDSERVERNAME> with the name of the linked server
"dotnettester" wrote:
> Hi,
> I have added a linked server. Now , how can I query the tables and also I
> only see one of the few databases only on the linked server. How can I see
> all the databases of linked server.
> Thanks,
>|||Hi,
Before u query to linked server table see in linked server property
that rpc option are checked.
use the select * from [linked
servername].databasename.username.tablename
hope this help
from
killer

Linked Server query

I have set up a link server to the AS400and I can see the tables in the Enterprise Manager, How do I query it so I can use the data in my SQL Server database.
*****************************************
* This message was posted via http://sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://sqlmonster.com/Uwe/Abuse.aspx?aid=66558ca34dd7468082b79e73dd024921
*****************************************Take a look at OPENQUERY in the BOL
Rick Sawtell
MCT, MCSD, MCDBA
"Vince Lassiter via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:66558ca34dd7468082b79e73dd024921@.SQLMonster.com...
> I have set up a link server to the AS400and I can see the tables in the
Enterprise Manager, How do I query it so I can use the data in my SQL Server
database.
> *****************************************
> * This message was posted via http://sqlmonster.com
> *
> * Report spam or abuse by clicking the following URL:
> *
http://sqlmonster.com/Uwe/Abuse.aspx?aid=66558ca34dd7468082b79e73dd024921
> *****************************************

Linked Server query

I have set up a link server to the AS400and I can see the tables in the Ent
erprise Manager, How do I query it so I can use the data in my SQL Server da
tabase.
****************************************
*
* This message was posted via http://droptable.com
*
* Report spam or abuse by clicking the following URL:
* http://droptable.com/Uwe/Abuse.asp...2b79e73dd024921
****************************************
*Take a look at OPENQUERY in the BOL
Rick Sawtell
MCT, MCSD, MCDBA
"Vince Lassiter via droptable.com" <forum@.droptable.com> wrote in message
news:66558ca34dd7468082b79e73dd024921@.SQ
droptable.com...
> I have set up a link server to the AS400and I can see the tables in the
Enterprise Manager, How do I query it so I can use the data in my SQL Server
database.
> ****************************************
*
> * This message was posted via http://droptable.com
> *
> * Report spam or abuse by clicking the following URL:
> *
http://droptable.com/Uwe/Abuse.asp...2b79e73dd024921
> ****************************************
*sql

linked server query

Hi,
I have added a linked server. Now , how can I query the tables and also I
only see one of the few databases only on the linked server. How can I see
all the databases of linked server.
Thanks,Via the linked server you will see your 'default database' objects on that
target database. Although you cannot graphically browse the multiple
databases that you have access to on the target linked server, you will be
able to access those other databases and tables via querying.
When querying the databases on the linked server, make sure to set your ANSI
settings and issue the queries using the 4-part object context:
ex:
USE ANSI_NULLS ON
GO
USE ANSI_WARNINGS ON
GO
SELECT name FROM <LINKEDSERVERNAME>.Northwind.dbo.Customers
Replace <LINKEDSERVERNAME> with the name of the linked server
"dotnettester" wrote:

> Hi,
> I have added a linked server. Now , how can I query the tables and also I
> only see one of the few databases only on the linked server. How can I see
> all the databases of linked server.
> Thanks,
>|||Hi,
Before u query to linked server table see in linked server property
that rpc option are checked.
use the select * from [linked
servername].databasename.username.tablename
hope this help
from
killer

Linked Server Object Listing

In SQL 2000's Enterprise Manager, upon selecting a linked server the accessible tables and views would be displayed in the right pane. This does not seem to be the case with SQL 2005's Management Studio. I'm seeing this behavior (or lack thereof) with links to a variety of platforms: Sybase ASA, Oracle, and MSS 2000.

To rule out a permission problem, I've confirmed the ablity to query sysobjects, ALL_TABLES, etc. Is this a driver or configuration problem, or is this functionality simply absent?

Try manually issuing

select * from sys.servers

while running under the same security context you use while in SSMS. You should be able to see rows for any linked server you have a remote or linked login to (sys.remote_logins or sys.linked_logins).

If you have the ALTER ANY LINKED SERVER or ALTER ANY LOGIN permision, or are a member of the setupadmin or sysadmin groups, then you should see all rows.

If you are not seeing rows, then this is "by design" behavior. See http://msdn2.microsoft.com/en-us/library/ms187113.aspx for more info.

If you are seeing rows, then repost or consider filing a bug.

Thanks

Linked Server Issues

Good Morning All,
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
--
EleciaAre You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
--
Elecia
"saradhi" wrote:
> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>sql

Linked Server in SQL(2005) Job

I have a linked server to Sybase using MSDASQL. I can query the tables from the linked server using SQL Management Studio (windows login). When I put the same query into a job, it failed to work. The query is simple:

SELECT * from MyLinkedSvr.RemotDB.dbo.RemoteTable

The linked server setting include the login mapping, which maps my windows login and sa to the remote sybase login.

I'm using SQL 2005 9.0.1339 on Windows2000 Pro w/ SP4. SQL Agent starts as local system in the Services panel. Shared Memory/TCPIP/Named Pipes are enabled for Server/Client Network.

Thanks!

The problem is solved by switching the log on account of SQL Agent to a windows log in account. Seems SQL Jobs is running under the account that start SQL Agent service.

|||No exactly sure where to switch the logon account of SQL Agent. Help help?

Linked Server in SQL(2005) Job

I have a linked server to Sybase using MSDASQL. I can query the tables from the linked server using SQL Management Studio (windows login). When I put the same query into a job, it failed to work. The query is simple:

SELECT * from MyLinkedSvr.RemotDB.dbo.RemoteTable

The linked server setting include the login mapping, which maps my windows login and sa to the remote sybase login.

I'm using SQL 2005 9.0.1339 on Windows2000 Pro w/ SP4. SQL Agent starts as local system in the Services panel. Shared Memory/TCPIP/Named Pipes are enabled for Server/Client Network.

Thanks!

The problem is solved by switching the log on account of SQL Agent to a windows log in account. Seems SQL Jobs is running under the account that start SQL Agent service.

|||No exactly sure where to switch the logon account of SQL Agent. Help help?

Linked Server Error Between SQL Server and Oracle

I have used Linked Servers to hook up to an Oracle 9i database in SQL
Server 2000. I can see all the Tables and Views when I look in the
linked server section. I can also run the following SQL statement :

Select *
from Orcldb..SYSTEM.CrossTabSaveSites

and get a return result. The problem I realize is that some of the
tables are going to be quite larger and I can't wait for SQL Server to
download all the data and parse it itself, so I tried to use a
passthrough query like so :

SELECT * FROM OPENQUERY(OrclDB, 'SELECT * FROM
SYSTEM.CrossTabSaveSites')

Unfortunately this gives me the error message :

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00942: table or view does not
exist
]

There is also the issue of speed. It takes about 15 seconds to come
back to me to tell me that the table/view doesn't exist, but sometimes
it comes back immediately. Is this something with the user
permissions in Oracle? (Don't really know much about Oracle) It just
seems like a very long time to wait just to let me know that the item
doesn't exist.

Any help would be greatly appreciated,

-Jevon Thurlow"Jevon" <jevonthurlow@.hotmail.com> wrote in message
news:34e7f100.0404080933.2e40e19e@.posting.google.c om...
> I have used Linked Servers to hook up to an Oracle 9i database in SQL
> Server 2000. I can see all the Tables and Views when I look in the
> linked server section. I can also run the following SQL statement :
> Select *
> from Orcldb..SYSTEM.CrossTabSaveSites
> and get a return result. The problem I realize is that some of the
> tables are going to be quite larger and I can't wait for SQL Server to
> download all the data and parse it itself, so I tried to use a
> passthrough query like so :
> SELECT * FROM OPENQUERY(OrclDB, 'SELECT * FROM
> SYSTEM.CrossTabSaveSites')
> Unfortunately this gives me the error message :
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'MSDAORA'.
> [OLE/DB provider returned message: ORA-00942: table or view does not
> exist
> ]
> There is also the issue of speed. It takes about 15 seconds to come
> back to me to tell me that the table/view doesn't exist, but sometimes
> it comes back immediately. Is this something with the user
> permissions in Oracle? (Don't really know much about Oracle) It just
> seems like a very long time to wait just to let me know that the item
> doesn't exist.
> Any help would be greatly appreciated,
> -Jevon Thurlow

I can't tell you anything about the performance issue, but the ORA-00942
error is probably because Oracle is case-sensitive, and object names are
usually in upper case:

http://support.microsoft.com/defaul...kb;EN-US;240340
http://support.microsoft.com/defaul...kb;EN-US;280106

Simon

Linked server error

Hi friends,

I have written an openquery that selects data scattered over two servers in multiple tables. It uses a cursor to read the rows and then performs validations to get other data.

while running it runs for around 4000 lines of table and then fails giving following error--

Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
[OLE/DB provider returned message: Deferred prepare could not be completed.]

any help will be appreciated

regards

anurag

My assumption is that you are constructing dynamic strings. So, that would mean that you are hitting a piece of data that when concatenated into your string is throwing an error. Instead of executing the dynamic queries, have you just tried dumping them out as strings so that you can find the one that is not generating properly?|||Another possible cause, the table which you are accessing doesn't have valid permission.

Linked server DTS issue

Hi need help, I am using Linked server to connect to a oracle server and transferring the data to the local tables, it is working fine if the join transaction is not selected, if we select it it fails with the following error: "MSDASQL was unable to run distributed transaction", the join transaction unit is required as I need to rollback the deletion of data from the tables in the first step of package. Any help will be appreciated. Transaction coordinator is running.My Oracle knowledge is pretty thin but I am betting the MSDTC does not work with Oracle. I would google it.

linked server doesn't return all rows for some tables

Hello,

I created a linked server in sql server 2005 which links to a AS400 DB. I use ODBC driver.

For some tables, it return all data but for another tables, it only return part of the rows.

How it may happen?

Thanks

After I applied AS400 client patch, this problem is fixed.

Thanks

linked server doesn't return all rows for some tables

Hello,

I created a linked server in sql server 2005 which links to a AS400 DB. I use ODBC driver.

For some tables, it return all data but for another tables, it only return part of the rows.

How it may happen?

Thanks

After I applied AS400 client patch, this problem is fixed.

Thanks

|||Hello,
I'm having the same problem with some queries, could you please tell me which patch to install so as to solve my problem ? I would appreciate a url for the download. Thank you very much.

Linked server communication

Hi everyone,

I've got problem querying remote tables via a specific linked server.
The server from which I execute the query is an SQLServer2005 and the linked server is an SQLServer2000.

If I do select * from <linked server>.<database name>.<db owner>.<Table1> after a while I get the following error message
"Server: Msg 10054, Level 16, State 1, Line 0, TCP Provider: An existing connection was forcibly closed by the remote host."

I execute the query using profiler and I got the following profiler error: 'OLE DB provider "Unknown" for linked server "(null)" supported the schema lock interface, but returned "0x80040e96" for "ReleaseSchemaLock".' (The query returns a subset of records before it is forcibly stopped)

The strange thing is that i used to run the same query a week or two ago and faced no problem.

Please help.

It apears that the 2000 server closes the connection for some reason. You may find answer to in engine forum.

Linked server communication

Hi everyone,

I've got problem querying remote tables via a specific linked server.
The server from which I execute the query is an SQLServer2005 and the linked server is an SQLServer2000.

If I do select * from <linked server>.<database name>.<db owner>.<Table1> after a while I get the following error message
"Server: Msg 10054, Level 16, State 1, Line 0, TCP Provider: An existing connection was forcibly closed by the remote host."

I execute the query using profiler and I got the following profiler error: 'OLE DB provider "Unknown" for linked server "(null)" supported the schema lock interface, but returned "0x80040e96" for "ReleaseSchemaLock".' (The query returns a subset of records before it is forcibly stopped)

The strange thing is that i used to run the same query a week or two ago and faced no problem.

Please help.

It apears that the 2000 server closes the connection for some reason. You may find answer to in engine forum.

Linked Server and unable to begin a distributed transaction

I have a database containing my own tables and data and I wanted to
be able to query this against an accountancy program which has an ODBC
driver. This was never a problem with MS Access and Jet but I hit Jet's
limitations and have moved to SQL.
Creating my own SQL database was no problem, but I was unsure of the
best way to be able to be able to have my SQL tables and my accountancy
software tables appearing in the same Access front end.
I created a linked server to the accountancy program. This was
successful in that I could see all the tables below the linked server in
enterprise manager.
My problem occurs when I try to bring the data from these tables
into my SQL database.
I create a new view in my database:-

SELECT *
FROM OPENQUERY(SAGE_SERVER, 'SELECT * FROM STOCK')

(My linked server is called 'SAGE_SERVER' and I am trying to retrieve
all columns from the STOCK table.)

I then try to save this view and get the following errors.

ODBC Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'MSDASQL' was unable
to begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB Error Trace[OLE/DB
Provider 'MSDASQL' ITransactionJoi JoinTransaction returned 0x8004d00a].

Thanks in advance,

Marcus Thornton."Marcus" <marcus@.automint.co.uk> wrote in message
news:MPG.19748ac86a8335b3989681@.192.168.1.50...
> I have a database containing my own tables and data and I wanted to
> be able to query this against an accountancy program which has an ODBC
> driver. This was never a problem with MS Access and Jet but I hit Jet's
> limitations and have moved to SQL.
> Creating my own SQL database was no problem, but I was unsure of the
> best way to be able to be able to have my SQL tables and my accountancy
> software tables appearing in the same Access front end.
> I created a linked server to the accountancy program. This was
> successful in that I could see all the tables below the linked server in
> enterprise manager.
> My problem occurs when I try to bring the data from these tables
> into my SQL database.
> I create a new view in my database:-
> SELECT *
> FROM OPENQUERY(SAGE_SERVER, 'SELECT * FROM STOCK')
> (My linked server is called 'SAGE_SERVER' and I am trying to retrieve
> all columns from the STOCK table.)
> I then try to save this view and get the following errors.
> ODBC Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> could not be performed because the OLE DB provider 'MSDASQL' was unable
> to begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB Error Trace[OLE/DB
> Provider 'MSDASQL' ITransactionJoi JoinTransaction returned 0x8004d00a].
> Thanks in advance,
> Marcus Thornton.

You might find this post (and the rest of the thread) useful - it has some
quite detailed information on troubleshooting distributed transaction
errors:

http://groups.google.com/groups?hl=...%40cpmsftngxa07

Simon

Linked Server [Oracle] error

Hi,
I've created a linked server to an Oracle 8.1.7 database
successfully, and can see the list of tables and views.
However, when I try to issue a select against one of the
tables I get the following error:
============================================ Server: Msg 7356, Level 16, State 1, Line 1
...
OLE DB error trace [Non-interface error:
Column 'TRANS_ID' (compile-time ordinal 1) of
object '"KOP_ADMIN"."PP_TRANSPORT"' was reported to have a
DBTYPE of 130 at compile time and 5 at run time].
============================================
Has anyone successfully solved this error?
TIA,
SJTYou might check out MSKB 280106:
http://support.microsoft.com/default.aspx?scid=kb;en-us;280106
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"SJT" <scott.taylor@.pwcs.com.au> wrote in message
news:054b01c36ad9$f41489a0$a501280a@.phx.gbl...
> Hi,
> I've created a linked server to an Oracle 8.1.7 database
> successfully, and can see the list of tables and views.
> However, when I try to issue a select against one of the
> tables I get the following error:
> ============================================> Server: Msg 7356, Level 16, State 1, Line 1
> ...
> OLE DB error trace [Non-interface error:
> Column 'TRANS_ID' (compile-time ordinal 1) of
> object '"KOP_ADMIN"."PP_TRANSPORT"' was reported to have a
> DBTYPE of 130 at compile time and 5 at run time].
> ============================================>
> Has anyone successfully solved this error?
> TIA,
> SJT

Linked Server / OLE DB problem

Hi,

I've connected 2 servers using the linked server option.

Tables in Server A contains some triggers that enforces changes in tables in Server B.

When making changes in server A from the enterprise manager (and invoking the triggers) everything works fine. But when an outside application is responsible for the changes the following message appears:

"Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query."

I've tried setting the ANSI_NULLS ON 1.inside the triggers, 2. in the script that creates the triggers and 3.in the servers' properties tab but it doesn't help.

Any ideas?

kigelThese set statments need to be made by the application doing the modifications. You can try to force this by Using EM, right click on the server name, select properties and go to the Connections tab.|||It worked! thanks!

I tried your first suggestion - enabling the "enforce distibuted transactions" under server properties --> connections tab. In addition i enabled the ANSI_NULLS and ANSI_WARNINGS again under server properties --> connections tab. I repeated the operation on both servers, stopped them and started them again and it worked.

One thing: on one of the sessions (in the interface application) i got the message: "OLE DB provider 'SQLOLEDB' reported an error". The message appeared only once, in one of the sessions and i believe/hope it is not related.

thanks again,

kigel