question

ernarsmagul91 avatar image
ernarsmagul91 asked

linked excel to sql

Hi there

I have task to connect sql server to excel

use linked server to excel

Operators enter data to excel, I must create linked to excel from sql server and with procedure import data to my table in sql.

I use this script:

--Создание связанного сервера

EXEC sp_addlinkedserver @server = 'TEST_EXCEL',

@srvproduct = 'Excel',

@provider = 'Microsoft.ACE.OLEDB.12.0',

@datasrc = 'C:\Users\1.xls',

@provstr = 'Excel 12.0;IMEX=1;HDR=YES;';


--Настройки безопасности (авторизации)

EXEC dbo.sp_addlinkedsrvlogin @rmtsrvname='TEST_EXCEL',

@useself= 'True',

@locallogin=NULL,

@rmtuser=NULL,

@rmtpassword=NULL;


--Обращение к связанному серверу

SELECT * FROM OPENQUERY (TEST_EXCEL, 'SELECT * FROM [l1$]');

--или

SELECT * FROM TEST_EXCEL...[l1$];


1675827964591.png


1675828005767.png

Please help in this case

sql_
1675827964591.png (25.7 KiB)
1675828005767.png (12.5 KiB)
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image anthony.green commented ·
Could you please copy the error message as text and or translate this to English, so the specific error can be noted. We cannot convert the picture text.


But generally a 7303 error is an issue with the driver, I would guess you have installed the 32bit version of the ACE driver when you need to use the 64bit, or the driver isn't installed.

0 Likes 0 ·
Jeff Moden avatar image Jeff Moden commented ·
Either that or the spreadsheet that you're testing isn't on the C:drive of the server and/or the spreadsheet is still open in Excel.
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.