Solved

Pass file name variable to select from OpenDataSource

Posted on 2006-11-03
16
1,266 Views
Last Modified: 2008-01-09
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$
0
Comment
Question by:MacNuttin
  • 8
  • 7
16 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17872090
0
 
LVL 11

Author Comment

by:MacNuttin
ID: 17872105
Do you see 'Data Source="C:\share\archive\INV10k.xls";  Can you write for me so 'Data Source="C:\share\archive\' + @filenam +'";
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17872119


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

0
 
LVL 75

Expert Comment

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

0
 
LVL 11

Author Comment

by:MacNuttin
ID: 17872160
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
 
LVL 11

Author Comment

by:MacNuttin
ID: 17872280
HELP
0
 
LVL 11

Author Comment

by:MacNuttin
ID: 17872299
works with select * but not with my variables Now I can't select @effdat for example
0
 
LVL 5

Assisted Solution

by:CIC Admin
CIC Admin earned 100 total points
ID: 17872362
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17872382
you need to convert the variables other than varchar to varchar
for example CAST(varchar,@effDat)
0
 
LVL 11

Author Comment

by:MacNuttin
ID: 17872403
Did both  Still I get
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '4'.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17872417
can u post the query ?
0
 
LVL 11

Author Comment

by:MacNuttin
ID: 17872431
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17872439
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
 
LVL 11

Author Comment

by:MacNuttin
ID: 17872460
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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 400 total points
ID: 17872468
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
 
LVL 11

Author Comment

by:MacNuttin
ID: 17872474
fantastic! That was it!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now