July 6, 2016 at 9:53 am
Firstly Apologies I'm very new to this.
I have created a Linked server on my Sql Express 2008 Machine. The Linked server is to a Fox Pro DBC. I am able to see the Linked server under Server Objects and from the SSMS console I can query all the tables within the Server.
I now need to connect to the linked server on my Laptop via ODBC SQL Native Client. I can see the server and I can connect to the default/standard System DB's but I cannot see the Linked Server Database or Tables. I need to create a link 3 tables within the Linked Server connection is this possible, if so how.
Any advise would be welcomed
July 6, 2016 at 9:58 am
Not enough information there, I'm afraid. Please will you post a script to create the linked server, and an example of a query that doesn't work, together with any error message you get?
Thanks
John
July 6, 2016 at 10:07 am
I'm not getting any error message, I just cannot see the tables when I try to connect via ODBC manager on my laptop AND if I simply connect to the server I cannot see any tables within the connector in MS Access 2016
Create Script below
EXEC master.dbo.sp_addlinkedserver @server = N'OPERALIVE', @srvproduct=N'Microsoft Visual FoxPro OLD DB Data Provider', @provider=N'VFPOLEDB', @datasrc=N'\\UNCPATH\comp_1.dbc', @provstr=N'VFPOLDDB.1'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'OPERALIVE',@useself=N'False',@locallogin=NULL,@rmtuser=N'Domain\Usr',@rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'OPERALIVE',@useself=N'True',@locallogin=N'Domain\Usr',@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'OPERALIVE',@useself=N'True',@locallogin=N'Domain\Usr2',@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'OPERALIVE',@useself=N'True',@locallogin=N'sa',@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'OPERALIVE', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'OPERALIVE', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'OPERALIVE', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'OPERALIVE', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'OPERALIVE', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'OPERALIVE', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'OPERALIVE', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'OPERALIVE', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'OPERALIVE', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'OPERALIVE', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'OPERALIVE', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'OPERALIVE', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'OPERALIVE', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
July 7, 2016 at 2:07 am
I take it the Sql Express 2008 Machine and your laptop are different computers? Have a look at the Security tab of the linked server object and work out which login is being used to make the connection. If it's a Windows login, it's possible that you're suffering from the double hop issue, in which case you'll either need to use Kerberos or to connect with a SQL login instead.
John
July 7, 2016 at 6:13 am
SQL express 2008 on a Win2008r2 Server. Laptop on same Domain but different machine.
I can connect to the SQL Server via ODBC DSN using SQL Client using my domain credentials and all is ok, BUT I cannot see my LINKED SERVER tables or database. I'm sure there is something I've missed but I've now looked for so long I wouldn't see it if it jumped out and it me
Hope this makes sense.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply