Unanswered: How to create a Linked server in SQL2k5 to a SQL2k that uses the SQL OLEDB Provider
I have a SQL Server 2005 Express SP2 Server. By using the SQL Server Management Studio Express, I want to create a Linked Server pointing to a SQL Server 2000 SP4 Server. But I want the configuration to use the OLEDB Provider for SQL instead of the SQL Native Client:
Linked Server: MyRemoteServer
Provider: Microsoft OLE DB Provider for SQL Server
Product Name: SQL Server
Provider String: "Provider=SQLOLEDB; Persist Security Info=True; Integrated Security=SSPI; Trusted_Connection=Yes;"
However, when I press "Ok" the Linked server just created always uses the SQL Native Client.
Is there a way to create a linked server to a SQL Server 2000 that uses the SQL OLEDB Provider?