February 8, 2008 at 9:42 am
I'd like to pass a variable into the following:
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=(path);',
'SELECT * FROM [Sheet1$]')
Sheet1$ needs to be the variable which I will pass in when I execute a stored procedure. I need to be able to change the worksheet.
I've searched the web, but what I have found is all too confusing. I've tried some of what I've read with no success. I think I may be using the wrong number of single quotes and N' incorrectly.
February 8, 2008 at 10:31 am
You have to use dynamic SQL
DECLARE @Sheet NVARCHAR(25),
@sql NVARCHAR(500)
SET @Sheet = '[Sheet1$]'
SET @sql = N'
INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database=c:\test.xls;'',
''SELECT * FROM ' + @Sheet + ''')
SELECT * FROM MyTable
'
EXEC sp_executesql @sql
February 8, 2008 at 10:59 am
Thanks Adam. Where do I pass in the worksheet name? This is a portion of my sp:
Create procedure spAttendanceWDA (@WDA smallint)
as
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=(path)\TEST.XLS;',
'SELECT * FROM [Sheet1$]')
Select PY, 'First Appointment' as Category,
[7] AS JUL, [8] AS AUG, [9] AS SEP, [10] AS OCT, [11] AS NOV, [12] AS DEC,
[1] AS JAN, [2] AS FEB, [3] AS MAR, [4] AS APR, [5] AS MAY, [6] AS JUN
FROM (select PY,
Month(Appointment_date) [Month], RegionID, Seeker_Id
from (Table Name)) AS FLTABLE
PIVOT
(
count(Seeker_Id)
FOR [MONTH] IN ([7],[8],[9],[10],[11],[12],[1],[2],[3],[4],[5],[6])
) AS PVT
where regionid=@WDA
Works great when I just run the code without the @Worksheet variable. I need to put each WDA on a seperate worksheet.
February 8, 2008 at 12:13 pm
Thanks Adam. Where do I pass in the worksheet name? This is a portion of my sp:
Create procedure spAttendanceWDA
@WDA smallint,
@Sheet NVARCHAR(25)
AS
BEGIN TRY
BEGIN TRANSACTION
DECLARE @sql NVARCHAR(500)
SET @sql = N'
INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database=c:\test.xls;'',
''SELECT * FROM ' + @Sheet + ''')
Select PY, ''First Appointment'' as Category,
[7] AS JUL, [8] AS AUG, [9] AS SEP, [10] AS OCT, [11] AS NOV, [12] AS DEC,
[1] AS JAN, [2] AS FEB, [3] AS MAR, [4] AS APR, [5] AS MAY, [6] AS JUN
FROM (select PY, Month(Appointment_date) [Month], RegionID, Seeker_Id
from (Table Name)
) AS FLTABLE
PIVOT
(
count(Seeker_Id)
FOR [MONTH] IN ([7],[8],[9],[10],[11],[12],[1],[2],[3],[4],[5],[6])
) AS PVT
where regionid=' + @WDA
EXEC sp_executesql @sql
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Error Detected'
SELECT
ERROR_NUMBER() ERNumber,
ERROR_SEVERITY() Error_Severity,
ERROR_STATE() Error_State,
ERROR_PROCEDURE() Error_Procedure,
ERROR_LINE() Error_Line,
ERROR_MESSAGE() Error_Message
ROLLBACK TRANSACTION
END CATCH
END
You shold execute it like this:
EXEC spAttendance_WDA 'Myregion', '[Sheet1$]'
February 8, 2008 at 1:17 pm
Thanks again. I got it working, but unfortunately, my sql statements exceeded what nvarchar will hold for the @sql parameter. I just gave you a portion of the sql code. The code has many union statements. Each union ends up a row with a different category in the output. So, it works when I remove some of the unions and code. When it gets too long, it gets chopped off and gives me:
Msg 105, Level 15, State 1, Line 102
Unclosed quotation mark after the character string 'MONTH'.
Msg 102, Level 15, State 1, Line 102
Incorrect syntax near 'MONTH'.
February 8, 2008 at 1:45 pm
Change the variable to nvarchar(max) and that will hold up to 2gb of string.
February 8, 2008 at 1:49 pm
I did set it up that way. Still got chopped. Thanks.
February 8, 2008 at 1:58 pm
Dump your union statements into temp tables. Then you can union a select *, which will drastically cut down on the size of your sql string.
select 1,2,3 union all
select * from #temp
February 8, 2008 at 2:09 pm
Sounds good. I'll have to play with it. Probably won't have anything until late next week. I'll be out of the office M-W.
Not sure I get the select 1,2,3 union all part. I understand union all, but not 1,2,3.
February 8, 2008 at 2:14 pm
Not sure I get the select 1,2,3 union all part. I understand union all, but not 1,2,3.
I was just showing an example of how to union column data with data from a temp table. 1,2,3 is literal data I made up.
February 8, 2008 at 3:10 pm
It didn't like @WDA smallint, so I had to change to nvarchar(2). I ended up taking out the 'region_id= ' in all the sql statements and dumped all regions into one table. I then set my sp like this:
Create procedure spAttendanceWDA @WDA nvarchar(2),@WorkSheet NVARCHAR(max)
As
Declare @sql nvarchar(500)
set @sql= N' insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database=(path)\TEST.XLS;'',
''SELECT * FROM ' + @WorkSheet + ''')
Select Num, PY, Category,
JUL, AUG, SEP, OCT, NOV, DEC,
JAN, FEB, MAR, APR, MAY, JUN
from Temp
where RegionID=' + @WDA + '
ORDER BY PY DESC, Num'
EXEC sp_executesql @sql
Works great except the sort doesn't work on Num. I put the NUM in so that the rows would come out in the order I wanted; 'Select 1 as NUM, ...'
Well, thanks to your help I've conquered half the solution. Now I'm going to play with some sort of loop so that I don't have to change the region and worksheet everytime I execute for each region.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply