This is what i'm doing:
IF EXISTS (SELECT srvname FROM master.dbo.sysservers srv WHERE srv.srvid !=
0 AND srv.srvname = N'ExcelSource')
EXEC master.dbo.sp_dropserver @.server=N'ExcelSource', @.droplogins='droplogins';
-
EXEC master.dbo.sp_addlinkedserver
@.server = 'ExcelSource',
@.srvproduct = 'Excel',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.datasrc = @.Chemin,
@.provstr = 'Excel 8.0';
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname = 'ExcelSource',
@.useself = false,
@.locallogin = NULL,
@.rmtuser ='ADMIN',
@.rmtpassword = NULL;
set @.NomServ = 'ExcelSource';
This create a linkedServer to read my ExcelFile.
Then i'm doing this:
EXEC ('Insert into Elements (No_element, Nom_elem, Desc_elem, Code_grpe_classe, Tps_elem, Code_sgrpe, Code_produit)
Select No_element, Nom_elem, Desc_elem, Code_grpe_classe, Tps_elem, Code_sgrpe, Code_produit
from ' + @.NomServ + '...[Elements$];')
This is where i got an error. The error is:
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelSource" does not contain the table "Elements$". The table either does not exist or the current user does not have permissions on that table.
I can't figure out what i'm missing. I've add permissions for EVERYONE on the file and on the folder just to be sure and i still have the same error. How can i check if the table [Elements$] exist ?
I've forgot to mention that my excel spreadsheet name is Elements.|||I finally found what the problem was. One of my column name had the wrong Column name. Now it's working fine !
