SQL Server Linked Server on local PC

Copper Contributor

Hello folks,
Before the version below, which I have now

 

Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64)   Oct  8 2022 05:58:25   Copyright (C) 2022 Microsoft Corporation  Express Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 19044: ) 

 

I used this command/script to create a linked server for .csv file and everything worked fine, but today it creates a linked server without a table.

 

EXEC sp_addlinkedserver 
	@server ='test_server',
	@srvproduct='',
	@provider ='Microsoft.ACE.OLEDB.12.0',
	@datasrc='C:\Users\xxx123123\OneDrive\Desktop\Folder1\file1',     
	@provstr='Text'

 

I tried both OLEDB versions but still nothing.

Microsoft.ACE.OLEDB.12.0
Microsoft.ACE.OLEDB.16.0

The final output looks like the picture below

tocy80_0-1683541930609.png

 

Also, I tried a command, but without success. 

 

EXEC sp_addlinkedserver test_server, N'Jet 4.0',   
   N'Microsoft.Jet.OLEDB.4.0',  
   N'C:\Users\xx123123\OneDrive\Desktop\Folder1\file1',  
   NULL,  
   N'Text'; 

 

Any idea how to create a linked server in the latest version of SQL Server? 

Thanks.

 

2 Replies
 N'C:\Users\xx123123\OneDrive\Desktop\Folder1\file1',  

 

@tocy80 , C:\Users\ are the user profile folders, where only the user do have access for, but not the SQL Server service account.

Use a different folder, where SQL Server service account have at least read access for.

The other process you are doing is right while creating a linked server. You just need to change this
The linked server you are adding is a user-specific folder but Not with an SQL server account.
You need to add it to a different folder where the SQL server could read and access it.
After this you can check it here:
SELECT *
FROM test_server.database.schema.table;

I hope this will help.