March 19, 2018 at 2:57 pm
Title says it all really. I've included the basic script, attached is the extended properties and extended results of sp_configure.
Any and all feedback appreciated!
/****** Object: LinkedServer [WEB_LOAD_DISTRIBUTION] Script Date: 3/19/2018 4:12:54 PM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'WEB_LOAD_DISTRIBUTION', @srvproduct=N'Acess', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'zz:\Access2013\Web Loads Distribution data.accdb'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'WEB_LOAD_DISTRIBUTION',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
March 19, 2018 at 3:02 pm
Added attachments that did not upload properly before
March 19, 2018 at 5:32 pm
Could you please post the complete error message you are getting?
Sue
March 19, 2018 at 6:00 pm
Here is the error. I get this when I drill down into the Linked server to expand the tables in the catalog. Testing the linked server returns success. I get the sense this is as simple as a bit not being flipped.
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "WEB_LOAD_DISTRIBUTION" reported an error. The provider did not give any information about the error.
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "WEB_LOAD_DISTRIBUTION". The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7399)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.5207&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
March 20, 2018 at 1:35 pm
ReReplaced - Monday, March 19, 2018 6:00 PMHere is the error. I get this when I drill down into the Linked server to expand the tables in the catalog. Testing the linked server returns success. I get the sense this is as simple as a bit not being flipped.TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "WEB_LOAD_DISTRIBUTION" reported an error. The provider did not give any information about the error.
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "WEB_LOAD_DISTRIBUTION". The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7399)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.5207&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476
------------------------------
BUTTONS:OK
------------------------------
Thanks for posting that. Good sense on your end - I can't remember which but it is on of the provider settings. I believe it is the Allow In Process setting for the provider. You would also want Dynamic Parameters enabled. I think this should work:
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO
Sue
March 20, 2018 at 2:47 pm
Make sure you install the 64bit driver
March 21, 2018 at 11:48 am
Sue_H - Tuesday, March 20, 2018 1:35 PMReReplaced - Monday, March 19, 2018 6:00 PMHere is the error. I get this when I drill down into the Linked server to expand the tables in the catalog. Testing the linked server returns success. I get the sense this is as simple as a bit not being flipped.TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "WEB_LOAD_DISTRIBUTION" reported an error. The provider did not give any information about the error.
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "WEB_LOAD_DISTRIBUTION". The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7399)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.5207&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476
------------------------------
BUTTONS:OK
------------------------------Thanks for posting that. Good sense on your end - I can't remember which but it is on of the provider settings. I believe it is the Allow In Process setting for the provider. You would also want Dynamic Parameters enabled. I think this should work:
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GOSue
Hi Sue_H, I ran that code, refreshed the SSMS (but did not cycle SQL Server), but it did not work.
Could this be an issue with the AD account that runs the SQLServer service needs permissions to the server/share location of the Access DB's?
March 21, 2018 at 12:09 pm
ReReplaced - Wednesday, March 21, 2018 11:48 AMSue_H - Tuesday, March 20, 2018 1:35 PMReReplaced - Monday, March 19, 2018 6:00 PMHere is the error. I get this when I drill down into the Linked server to expand the tables in the catalog. Testing the linked server returns success. I get the sense this is as simple as a bit not being flipped.TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "WEB_LOAD_DISTRIBUTION" reported an error. The provider did not give any information about the error.
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "WEB_LOAD_DISTRIBUTION". The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7399)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.5207&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476
------------------------------
BUTTONS:OK
------------------------------Thanks for posting that. Good sense on your end - I can't remember which but it is on of the provider settings. I believe it is the Allow In Process setting for the provider. You would also want Dynamic Parameters enabled. I think this should work:
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GOSue
Hi Sue_H, I ran that code, refreshed the SSMS (but did not cycle SQL Server), but it did not work.
Could this be an issue with the AD account that runs the SQLServer service needs permissions to the server/share location of the Access DB's?
Yes that account would need access but if that is also a network location you would want to use a unc path instead of a mapped drive.
Sue
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply