Pass file name variable to select from OpenDataSource

This works as coded below but I need to pass it the file name each time

DECLARE @effdat datetime,@clicod as varchar(30),@datsetcod as varchar(30),@yeartag as char(2),@filenam as varchar(255),@filepath as varchar(300)
SET @effdat =getdate()
SET @filenam = 'INV10k.xls'
SET @filepath = 'C:\share\archive\' + @filenam
print @filepath
--delete from INVDETAILINPUT where FILENAM = @filenam
--insert INVDETAILINPUT
SELECT @effdat,[Period Code],[Fiscal Year],[Market Price],NULL,NULL,@clicod,@datsetcod,NULL,@yeartag,@filenam
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
      'Data Source="C:\share\archive\INV10k.xls";Extended properties=Excel 8.0')...Sheet1$
LVL 11
MacNuttinAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
MacNuttin,
> what was the point of that?

You need to put the Dates (Nov 04 ) inside the Quotes , samething you need to do with the @fileName tooo

SET @SQL ='SELECT '+''''+ @effDat+''''+',[Period Code],[Fiscal Year],[Market Price],NULL,NULL,'+ @clicod +','+ @datsetcod +',NULL,NULL,'+ @yeartag +','+''''+ @filenam+'''' +' from OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',''Data Source="'+ @filepath +'";Extended properties=Excel 8.0'')...Sheet1$'



0
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
MacNuttinAuthor Commented:
Do you see 'Data Source="C:\share\archive\INV10k.xls";  Can you write for me so 'Data Source="C:\share\archive\' + @filenam +'";
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Aneesh RetnakaranDatabase AdministratorCommented:


SELECT *from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
      'Data Source="C:\sampledata.xls";Extended properties=Excel 8.0')...Sheet1$

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
sorry ..
Exec('SELECT * FROM  OpenDataSource(''Microsoft.Jet.OLEDB.4.0'',
      ''Data Source="'+@filepath+'";Extended properties=Excel 8.0'')...Sheet1$')

0
 
MacNuttinAuthor Commented:
getting error Line 1: Incorrect syntax near '4'.  --doesn't like OLEDB.4.0'',

DECLARE @effdat datetime,@clicod varchar(30),@datsetcod  varchar(30),@yeartag varchar(2),@filenam varchar(255),@filepath varchar(300)
SET @effdat =getdate()
SET @filenam = 'INV10k.xls'
SET @clicod = '20034'
SET @datsetcod = '2003456'
SET @yeartag ='06'
SET @filepath = 'C:\share\archive\' + @filenam
print @filepath
--delete from INVDETAILINPUT where FILENAM = 'filename'
--insert INVDETAILINPUT
EXEC('SELECT '+@effdat+',[Period Code],[Fiscal Year],[Market Price],NULL,NULL,'+@clicod+','+@datsetcod+',NULL,NULL,'+@yeartag+','+@filenam+'
from OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',
      ''Data Source="'+@filenam+'";Extended properties=Excel 8.0'')...Sheet1$')
0
 
MacNuttinAuthor Commented:
HELP
0
 
MacNuttinAuthor Commented:
works with select * but not with my variables Now I can't select @effdat for example
0
 
CIC AdminCommented:
You might want to try creating the SELECT statement as a variable and then just using that variable in the EXEC statment.  I have had similar problems in the past.

SET @sql = 'SELECT '+@effdat+',[Period Code],[Fiscal Year],[Market Price],NULL,NULL,'+@clicod+','+@datsetcod+',NULL,NULL,'+@yeartag+','+@filenam+'
from OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',''Data Source="'+@filenam+'";Extended properties=Excel 8.0'')...Sheet1$'

EXEC(@sql)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you need to convert the variables other than varchar to varchar
for example CAST(varchar,@effDat)
0
 
MacNuttinAuthor Commented:
Did both  Still I get
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '4'.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
can u post the query ?
0
 
MacNuttinAuthor Commented:
DECLARE @effdat varchar(8),@clicod varchar(30),@datsetcod  varchar(30),@yeartag varchar(2),@filenam varchar(255),@filepath varchar(300),@SQL varchar(1000)
SET @effdat =convert(varchar,getdate())
SET @filenam = 'INV10k.xls'
SET @clicod = '20034'
SET @datsetcod = '2003456'
SET @yeartag ='06'
SET @filepath = 'C:\share\archive\' + @filenam
SET @SQL ='SELECT '+ @effDat +',[Period Code],[Fiscal Year],[Market Price],NULL,NULL,'+ @clicod +','+ @datsetcod +',NULL,NULL,'+ @yeartag +','+ @filenam +' from OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',''Data Source="'+ @filepath +'";Extended properties=Excel 8.0'')...Sheet1$'

delete from INVDETAILINPUT where FILENAM = @filenam
--insert INVDETAILINPUT
/*EXEC('SELECT '+ @effdat +',[Period Code],[Fiscal Year],[Market Price],NULL,NULL,'+ @clicod +','+ @datsetcod +',NULL,NULL,'+ @yeartag +','+ @filenam +' from OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',''Data Source="'+ @filepath +'";Extended properties=Excel 8.0'')...Sheet1$')
*/
Exec(@SQL)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SET @SQL ='SELECT '+''''+ @effDat+''''+',[Period Code],[Fiscal Year],[Market Price],NULL,NULL,'+ @clicod +','+ @datsetcod +',NULL,NULL,'+ @yeartag +','+ @filenam +' from OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',''Data Source="'+ @filepath +'";Extended properties=Excel 8.0'')...Sheet1$'
0
 
MacNuttinAuthor Commented:
what was the point of that? any way the error changed:
The column prefix 'INV10k' does not match with a table name or alias name used in the query.
0
 
MacNuttinAuthor Commented:
fantastic! That was it!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.