Solved

Pass file name variable to select from OpenDataSource

Posted on 2006-11-03
16
1,263 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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

744 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

12 Experts available now in Live!

Get 1:1 Help Now