Link to home
Avatar of ggilal
ggilal

asked on

Export to excel from SQL Server 2005 using OPENROWSET

Hello,
I am using :
INSERT INTO
        OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
                    'Excel 8.0;Database=D:\Test.xls',
                    'SELECT UserId, LastName,FirstName FROM [Sheet1$]')
       SELECT top 100 [User_Id], Last_Name, First_Name FROM dbo.Users
I realize that the text.xls must exist with the column names beforehand. However, I need to generate an excel file that has a different name that contains the data exported and empty the test.xls so that it will be able to be ready to receive data when a new request comes in with different parameters.
How do I do this?
Thanks!
     
Avatar of udaya kumar laligondla
udaya kumar laligondla
Flag of India image

use EXEC sp_makewebtask
      @outputfile = 'd:\testing.xls',
      @query = 'Select * from Database_name..SQLServerTable',
      @colheaders =1,
      @FixedFont=0,@lastupdated=0,@resultstitle='Testing details'

this topic will help http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
Avatar of ggilal
ggilal

ASKER

Sorry, but using sp_makewebtask  is outdated.
I would like to export data so that some columns are hyperlinks - is that possible?
Thanks!
Avatar of Mark Wills
The only real way is to either use OA procedures, or, easier to copy across a "blank template" to your appropriately named xls file and update that. Not too many choices.

As far as making a cell a hyperlink, then you would definitely need OA procedures.
Avatar of ggilal

ASKER

I am really having a problem with the insert of rows into excel. The insert begans only on the 24th row-all the rows above are empty! In addition to this the Order by clause is totally ignored!
Is there a better way of doing this export? The point of it all is that upon an ASP (classic)request the stored procedure will insert the data to excel and the user will download the file.

Thanks!
What is the hyperlink ?

Know how to do things like e-mail links (suppose it depends on what the hyperlink really is) :

ActiveSheet.Hyperlinks.Add Anchor:=Range("D4"), Address:= "mailto:it_is_me@somewhere.com", TextToDisplay:="my email"

Is there anything "special" about the spreadsheet ? Do you have a shared filespace that the server can write to and the users can read from ?
If it is ASP, have you also seen : http://www.spreadsheetgear.com/?ac=DG
Avatar of ggilal

ASKER

I am using OPENROWSET in SQL Server in order to insert the data to the excel sheet. Are you saying that I should manipulate the excel file from ASP after it is created?
It is an option that is available - but at a price, and if you are doing a fair bit of that type of work, then it could be a viable consideration...

In the meantime, using a "template" xls, you can copy it across to build a new spreadsheet. In the example below, I have used the adventureworks database...

declare @doscommand varchar(1000)
declare @workfile varchar(1000)
set @workfile =  '"c:\ee\employee_list_work.xls"'
set @doscommand = 'copy c:\ee\Template_Employee_List.xls, '+@workfile
 
print @doscommand
exec master..xp_cmdshell @doscommand --and then work on it from a "safe" place, referring to @workfile as needed below
GO
 
EXEC sp_addlinkedserver MyExcel,'Jet 4.0','Microsoft.Jet.OLEDB.4.0','c:\ee\employee_list_work.xls', NULL, 'Excel 5.0;'
GO
 
--Set up login mappings (just ADMIN - jet wants something).
EXEC sp_addlinkedsrvlogin MyExcel, FALSE, NULL, Admin, NULL
GO
 
-- the insert adds rows to the bottom of the spreadsheet (so do not have blank lines in your template)
insert myexcel...Sheet1$                                                    -- Sheet1$ is the name of the worksheet 
select p.contactid as userID, p.lastname, p.firstname, p.emailaddress
from Adventureworks.Person.contact p
Go
 
select * from myexcel...Sheet1$     -- make sure it is all there...
GO
 
sp_dropserver 'MyExcel', 'droplogins';  -- remove server

Open in new window

Template-employee-list.xls
Avatar of ggilal

ASKER

I am trying to avoid the xp_cmdshell  because of security issues. Could you tell me if this is really an issue or can I use it without worrying my employers?

Thanks
If you are running it from a secured user perspective, then you do not have to worry. If you enable "mere mortals" to get access, or use the proxy account, then it can be a very big worry. By default on sysadmin get access, so, to use it, there is already a potential security breach because it is a sysadmin role. In that case, the xp_cmdshell is least of your problems. There is a potential risk, but if you control and manage properly then it can be quite safe to use. Again, this example is from a secured perspective, with out any user input to copy a file that you decide how and what to call it and you alone have access to create the actual command - there is no interaction with the command from a user perspective - that is, if you wrap it up in a Stored Procedure and if needed, enable in the procedure, then disable after use...
Avatar of ggilal

ASKER

ok. I see your point. I also don't see anyway of getting around it all since the excel file must be created from the server. Otherwise it will just time out.
I will disable the permissions after it runs in the SP, though.
All this is supposed to run from an ASP (classic) page.
Do you have any suggestions as to why the sort order in the excel file is not as in the select...order by in the SP? It should be [id] DESC.

Thanks!
Sort Order ? Well, need to use something that tells it to actually sort... something like :

select top 64000 p.contactid as userID, p.lastname, p.firstname, p.emailaddress
from Adventureworks.Person.contact p order by contactid desc

cannot use top 100 percent because the order by is ignored for whatever reason in the above... and come to think of it there is a row limit in excel anyway so probably should select top 64K

wouldn't disable permissions, just disable xp_cmdshell via sp_configure (xp_cmdshell is an advanced option as well, so, have to enable advanced)

ie:
sp_configure 'xp_cmdshell',1 ;
reconfigure;

...


sp_configure 'xp_cmdshell',0 ;
reconfigure;
Avatar of ggilal

ASKER

Why do I still get those 23 blank rows at the beginning of the excel sheet - even with the adventureworks table?
They must be populated somewhere somehow - even if just blanks... highlight the entire rows and right click and then DELETE rather than clear contents.
If you have specific rows that need to be addressed, then create a column and populate with unique values, then you can update where column = nn rather than insert, and hide that column in your spreadsheet. e.g.

insert a new column (ie a new column a) give it the heading seq in row 1, then insert the numbers from 1 to 20 the next 20 rows in that column, hide the column. Then instead of doing an insert, do an update:

update x  set userid = p.contactid, lastname=p.lastname, firstname=p.firstname, email=p.emailaddress
from myexcel...Sheet1$ x
inner join Adventureworks.Person.contact p on x.seq = p.contactid
Avatar of ggilal

ASKER

OK. I am using a CTE in order to get the data ( I needed to use OVER and ROW_NUMBER()).
One can't use CTE and then give a whole bunch of commands and inserts after it is created. It's good for only one select afterwards... so I can't even delcare a tbl variable or do an insert into a temp table and take the data from there to excel.
Any ideas on how to get the data into the excel if I have to use the above?
Avatar of ggilal

ASKER

I see the problem now - it is because of all the "go"s in the proc. But without them the server can't be linked...
Avatar of ggilal

ASKER

OK. Sorry about the above complications. I got it all fixed but I did have to separate the linking and the dropping from the main SP. I will then have to run it all in a series of steps from a job. Looks like I will have to go thru SSIS since I need to send parameters the user will provide. Is it possible to send parameters to an sp from a step in a job?
It can all be done within a stored proc - the GO's aren't needed... Have a look at :


alter proc usp_update_spreadsheets 
as
begin
 
 
declare @sql varchar(1000)
declare @doscommand varchar(1000)
declare @workfile varchar(1000)
set @workfile =  '"c:\ee\employee_list_work.xls"'
set @doscommand = 'copy c:\ee\Template_Employee_List.xls, '+@workfile
 
print @doscommand
exec master..xp_cmdshell @doscommand; --and then work on it from a "safe" place, referring to @workfile as needed below
 
EXEC sp_addlinkedserver MyExcel,'Jet 4.0','Microsoft.Jet.OLEDB.4.0','c:\ee\employee_list_work.xls', NULL, 'Excel 5.0;';
 
--Set up login mappings (just ADMIN - jet wants something).
EXEC sp_addlinkedsrvlogin MyExcel, FALSE, NULL, Admin, NULL;
 
-- the insert adds rows to the bottom of the spreadsheet (so do not have blank lines in your template)
set @sql = '
insert myexcel...Sheet1$ (userid,lastname,firstname,email)
select p.contactid as userID, p.lastname, p.firstname, p.emailaddress
from Adventureworks.Person.contact p'
exec(@SQL)
 
set @sql = 'select * from myexcel...Sheet1$'
exec(@SQL)
 
exec sp_dropserver 'MyExcel', 'droplogins';  -- remove server
 
end

Open in new window

Avatar of ggilal

ASKER

Well, it all worked well last night but this morning I am receiving this error:
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "myexcel" reported an error. The provider did not give any information about the error.

What changed?
could be a number of things... That error is kind of a generic error meaning "for what ever reason I cannot open that file". do a search and see if you can locate the file, and can open it manually, quickly check permissions on that file, then try the SP again...
Avatar of ggilal

ASKER

I  just can't figure it out. It was working well the day before. Certainly I can't allow this type of inconsistent behavior with the "live" database.
I must find a solution to exporting to excel from the asp(classic ) page without having it timeout. This is the reason I opted for the sp and linked server route.
Any help would be greatly appreciated!
Well, did you get to the root of the problem ? It does work reliably, but also needs a reliable environment - anything that might interfere with that will stop it connecting to the excel spreadsheet, including not dropping the linked server before creating a new spreadsheet, or otherwise having it unavailable (maybe even security settings). For it to work one day and not the next indicates something else is going on...

I have just finished running that SP 8 times in a row without failure - only difference is I copied to a new name at the end of the procedure so I had a new version each time.

If it is currently failing, try running it interactively to find the step it is failing on...

Avatar of ggilal

ASKER

I copied it over to a new SP and first made it simple then I got the "real" sql going and it is not giving me the error again.
I tried running it from the asp page BUT:
the xp_cmdshell section is not allowed - no way my bosses will let me give more permissions on that.
after deleting the xp_cmdshell section and creating the copy of the excel template file manually I ran it again and received:
permission denied for user on the line-conn.execute sql.

I must think up something that will improve the performance of exporting to excel from the asp itself without using the above because of all the security issues.
Any ideas would be greatly appreciated...
I certainly learned a lot from the above and I thank you for that.
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of ggilal

ASKER

The whole subject began because the data was exported using asp and was constantly timing out on them because there where over 10000 rows to export... Any ideas on which plan would have the best performance from the web?