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,@
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\share\archive\I
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,@
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\share\archive\I
ASKER
Do you see 'Data Source="C:\share\archive\I
SELECT *from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\sampledata.xls"
sorry ..
Exec('SELECT * FROM OpenDataSource(''Microsoft
''Data Source="'+@filepath+'";Ext
Exec('SELECT * FROM OpenDataSource(''Microsoft
''Data Source="'+@filepath+'";Ext
ASKER
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
from OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'
''Data Source="'+@filenam+'";Exte
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
from OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'
''Data Source="'+@filenam+'";Exte
ASKER
HELP
ASKER
works with select * but not with my variables Now I can't select @effdat for example
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you need to convert the variables other than varchar to varchar
for example CAST(varchar,@effDat)
for example CAST(varchar,@effDat)
ASKER
Did both Still I get
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '4'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '4'.
can u post the query ?
ASKER
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'
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'
*/
Exec(@SQL)
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'
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'
*/
Exec(@SQL)
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'
ASKER
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.
The column prefix 'INV10k' does not match with a table name or alias name used in the query.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
fantastic! That was it!
http://www.sqlknowledge.com/index.php?option=com_content&task=view&id=57&Itemid=45
Cheers!