Forum Discussion

tocy80's avatar
tocy80
Copper Contributor
May 08, 2023

SQL Server Linked Server on local PC

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

 

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.

 

Linked Server
sql server
  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    May 09, 2023
     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.

  • Raksha112's avatar
    Raksha112
    Copper Contributor
    May 11, 2023
    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.

Resources