0

I am trying to connect to a PDW instance from an SMP server. The linked server is established and I can view the PDW databases from SMP. But when I try to select data from PDW, here's the error that I get:

OLE DB provider "SQLNCLI11" for linked server "x.x.x.x,x" returned message "Unspecified error".
Msg 104386, Level 16, State 1, Line 1
Stored procedure sp_tables_info_90_rowset_64 does not exist or is not supported.

Could this be related to permissions on my ID. I am connected to SMP server using windows authentication

Triumph Spitfire
  • 663
  • 15
  • 38

1 Answers1

2

The PDW documentation states:

SQL statements must be submitted using the linked server EXECUTE statement. Using the EXECUTE statement avoids using four-part names for objects, which is not supported by SQL Server PDW. For example:

Use this: EXEC ('SELECT * FROM AdventureWorksPDW2012.dbo.dimCustomer') AT PDWLINK;

Don’t use this: SELECT * FROM PDWLINK.AdventureWorksPDW2012.dbo.dimCustomer

So I wouldn't expect to be able to browse the linked server and get a list of databases. You can run queries though.

For the reference of others, the PDW/APS documentation explains how to create a SQL Server linked server that connects to the PDW:

EXEC master.dbo.sp_MSset_oledb_prop N'SQLNCLI', N'AllowInProcess', 1
GO

EXEC master.dbo.sp_addlinkedserver
@server = N'PDWLINK', @srvproduct=N'PDW', 
@provider=N'SQLNCLI', @provstr=N'Server=<ip_control_node_cluster>,17001', @catalog=N'<database>';
GO

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'PDWLINK',@useself=N'False',
@rmtuser=N'<PDW_login_name>',@rmtpassword='<PDW_login_password>'
GO

EXEC master.dbo.sp_serveroption
@server=N'PDWLINK', @optname=N'rpc',
@optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption
@server=N'PDWLINK', @optname=N'rpc out',
@optvalue=N'true'
GO

-- The options below are not required
EXEC master.dbo.sp_serveroption
@server=N'PDWLINK', @optname=N'collation compatible',
@optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption
@server=N'PDWLINK', @optname=N'data access',
@optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption
@server=N'PDWLINK', @optname=N'dist',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption
@server=N'PDWLINK', @optname=N'pub',
@optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption
@server=N'PDWLINK', @optname=N'sub',
@optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption
@server=N'PDWLINK', @optname=N'connect timeout',
@optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption
@server=N'PDWLINK', @optname=N'collation name',
@optvalue=null
GO

EXEC master.dbo.sp_serveroption
@server=N'PDWLINK', @optname=N'lazy schema validation',
@optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption
@server=N'PDWLINK', @optname=N'query timeout',
@optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption
@server=N'PDWLINK', @optname=N'use remote collation',
@optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption
@server=N'PDWLINK', @optname=N'remote proc transaction promotion',
@optvalue=N'false'
GO
GregGalloway
  • 11,355
  • 3
  • 16
  • 47