SQL – Linked Server xp_prop_oledb_provider error

 

When you create a Linked Server on SQL Server, some users may get this error:
EXECUTE denied on object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’, schema ‘sys’.

image

 

So, the only thing you need (if you have set up your permissions correctly on Linked Server) is to run the following code:

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO LOGIN;

If you use domain user, change login to [DOMAIN\username]

 

And this should solve your problem.

 

Good Luck

SQL- Creating linked server to AS400

Have you ever tried to connect to AS400. It’s been a hassle for me. But finally I got it to work, so let me share little of my wisdom with you.

First create linked server, then change options to it.

EXEC master.dbo.sp_addlinkedserver @server = N’AS400′, @srvproduct=N’i520′, @provider=N’IBMDA400′, @datasrc=N’AS400′, @provstr=N’User Id=USER;Password=PWD;Default Collection=DAT;’

Change option:

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N’IBMDA400′, N’AllowInProcess’, 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N’IBMDA400′, N’NonTransactedUpdates’, 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N’IBMDA400′, N’SqlServerLIKE’, 1
GO
EXEC master.dbo.sp_serveroption @server=N’ASUHRP12′, @optname=N’collation compatible’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’ASUHRP12′, @optname=N’rpc’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’ASUHRP12′, @optname=N’rpc out’, @optvalue=N’true’
GO

So, you should now have functional linked server. Only, in my case its very slow, so it could be in your case to.

Good Luck