August 4, 2005 at 4:06 pm
Help Please,
I am trying to replace MS Access code with Stored Procedures. As part of a regular monthly process I need to upload to the server up 21 excel files.
In Access I can use the transferspreadsheet command - but I cant find an equivalent method with Stored Procedures. Can anyone offer a solution.
August 4, 2005 at 4:22 pm
I usually use DTS for that type of thing. Can you handle it that way?
August 4, 2005 at 4:24 pm
My problem is the number of files, each with a different filename. Thought about using DTS, but can this be run by non DB admin people
August 5, 2005 at 3:45 am
Not if you protect the DTS package with a password.
August 7, 2005 at 4:40 pm
Hi,
I understand that DTS is the way to go, but I'm not sure how to go about it. I have up to 7 different sets of excel templates that need to be loaded for monthly/quarterly/yearly processing. My problem is that there could be anywhere from 1 to 22 files to load at any point in time. DO I need to have a dts package for each file, which is what it looks like at the moment, then a job to run each dts package from a stored procedure. I was hoping to use a generic DTS package that could handle all 22 files and each of the 7 steps.
Thoughts please
August 8, 2005 at 11:16 am
You can do this with open query as well, I believe. You will need your dbas to create linked servers to the excel files. Check the BOL on this.
HTH
Mathew J Kulangara
sqladventures.blogspot.com
August 8, 2005 at 9:54 pm
Here's somthing I wrote that may help you...I mispoke above ..you don't need to have a linked server to open an excel file from t-sql if you use openrowset or opendatasource..
create procedure dbo.p_excel_data @type varchar(50)=Null,@version varchar(50)=Null,
@source varchar(50)=Null,@spreadsheet_name varchar(50)=Null
as
if ((@type is NULL) or (@version is null) or (@source is null) or (@spreadsheet_name is null))
begin
print 'INDETERMINED @TYPE,@version,@source,@spreadsheet_name REQUIRED'
end
else
begin
declare @dyn_query varchar(1000)
set @dyn_query = 'Select * from OPENROWSET ('''+@type+''','''+@version+';Database='+@source+''','+@spreadsheet_name+'$)'
exec(@dyn_query)
end
What I would do is create table that has all the parameters redquired for the sp above..then use a cursor to go through each one.
HTH
Mathew J Kulangara
sqladventures.blogspot.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply