There was a solution posted for a SIMILAR problem, but it isn't working for my situation.
I need to import several Exel files with a stored procedure into a table variable.
This stored procedure is being called from the web
@zfile is a parameter
I declare the table variable and the variable for the source file
Declare @Zip table(Zip3 varchar(3), zGroup varchar(25));
Declare @srcFile as varchar(200)
I set the string (the file names will change very time)
Set @srcFile = 'Excel 8.0;Database=' + @zFile + ';HDR=Yes'
And try to import to the table variable...
INSERT INTO @Zip (Zip3, zGroup)
SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', @srcFile)...[Sheet1$]
Then I get this error
Msg 102, Level 15, State 1, Procedure CreateCPTReport, Line 33
Incorrect syntax near '@srcFile'.
Is there any way you can change the file name each time, this doesn't seem to be working
and I have to do this with 4 tables every SP run