Sql-server – Extracting data from SQL Server linked server

linked-serverodbcsql serversql-server-2012

I am using MS SQL Server 2012, and I am trying to extract data from a Linked Server database.

In SQL Server management studio, I can see the linked server. I can browse the database on the server. I can see all tables in database.

When I try and query a linked table

select * from openquery(somedatabase,'select top 4 * from test')

I get error:

The OLE DB provider "MSDASQL" for linked server "somedatabase" indicates that either the object has no columns or the current user does not have permissions on that object.

When I try to script the table.. right_click on the linked table, Script table as, Select to, New Query Editor window, I get same error.

Any help would be greatly appreciated.

I referred to following posts but it did not help.

http://www.sqlservercentral.com/Forums/Topic1036585-391-1.aspx

Error Querying MySQL server linked to SQL Server 2008

http://wiki.servicenow.com/index.php?title=Using_ODBC_Driver_in_SQL_Server#gsc.tab=0

Thanks.

Best Answer

Check the link server setup and see HOW the users connect to the link server. This can be done by going to Server Objects in SSMS -> Linked Servers -> [Link server name] -> right click then select Properties -> security tab. It can be done without a security context, using the current login's security context, or it can be done using a specific login. Find out which account is being used and then make sure that account has access to the table on the linked server.

Related Solutions

How to query a server linked via ODBC

Try Open query

BEGIN TRANSACTION;

select top 10  * 
INTO #t
from openquery ("YOUR LINKED SERVER NAME",'SELECT   * FROM [SOURCE TABLE NAME]);

COMMIT

Sql-server – linked server connection error in Sql server

First, install the correct Oracle drivers. You want the latest version of the Oracle Data Access Components (ODAC), and you want the XCopy deployment. They are available here: 64-bit Oracle Data Access Components (ODAC) Downloads http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

One you download and unzip this into a folder run the following command from that folder:

C:\Users\dbrowne\Downloads\ODAC121010Xcopy_x64>.\install.bat oledb c:\oracle\odac64 odac64 true

Then you need to add two folders to your system path:

c:\oracle\odac64 and c:\oracle\odac64\bin

Then you must reboot for the system path change to be visible by services like SQL Server.

After reboot you're ready to create and test the linked server. First configure the Oracle OleDB provider to run inside the SQL Server process, and configure it to accept parameters.

exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1

exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1

Then create the linked server definition. Instead of a TNSNames alias, use an EZConnect identifier. Here I'm specifying an IP address and a SID to connecto to an Oracle Express instance running on a VM:

exec sp_addlinkedserver N'MyOracle', 'Oracle', 'ORAOLEDB.Oracle', N'//172.16.8.119/xe', N'FetchSize=2000', ''
exec master.dbo.sp_serveroption @server=N'MyOracle', @optname=N'rpc out', @optvalue=N'true'
exec sp_addlinkedsrvlogin @rmtsrvname='MyOracle', @useself=N'FALSE', @rmtuser=N'system',@rmtpassword='xxxxxx'

Now you're ready to test. You configured the linked server for 'rpc out' so we can send a simple passthrough query to test connectivity:

exec ('select 1 a from dual') at MyOracle

That's it.

Best Answer

Related Solutions

How to query a server linked via ODBC

Sql-server – linked server connection error in Sql server

Related Question