July 18, 2012 at 6:54 am
Could use some help on this one:
I have a SQL Server 2008 64bit and try to put data in excel 2010.
The excel sheet exists, it has header data in it. I'm trying to put the output of the stored procedure into excel.
This is the code I have but it doesn't work.
[Code="SQL"]
set @Xls ='d:\test\filename.xlsx'
set @Sheet = 'sheet1'
SET @Openrowset1 = '''Microsoft.ACE.OLEDB.12.0'''
SET @Openrowset2 = '''Excel 12.0;Database=' + rtrim(@Xls) + ''''
SET @Openrowset3 = '''Select * from [' + rtrim(@Sheet) + '$]'''
SET @Openrowset = 'insert into openrowset('+ @Openrowset1 + ',' + @Openrowset2 + ',' + @Openrowset3 + ') ' +
'SELECT *
FROM Northwind.dbo.Employees
ORDER BY EmployeeID
'
EXEC (@Openrowset)
[/Code]
July 18, 2012 at 7:07 am
Isnt this trying to use the Jet drivers which arent available on x64 environments.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
July 18, 2012 at 7:14 am
Have you considered using SSIS?
July 18, 2012 at 7:21 am
Hi, thanks for the replies.
Yes, we considered using SSIS, but depending on the content of the excel sheet, we send a mail or not.
July 18, 2012 at 7:35 am
Vera-428803 (7/18/2012)
Hi, thanks for the replies.Yes, we considered using SSIS, but depending on the content of the excel sheet, we send a mail or not.
Hi,
Looks like the send mail task in SSIS is what you need. If you give some details what exactly you are trying to achieve, I'll try to help.
July 18, 2012 at 7:35 am
Vera-428803 (7/18/2012)
Hi, thanks for the replies.Yes, we considered using SSIS, but depending on the content of the excel sheet, we send a mail or not.
SSIS can send mail. http://msdn.microsoft.com/en-us/library/ms142165.aspx Seems like SSIS would be a good fit for this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 17, 2018 at 7:40 pm
Need help on this one:
I have a SQL Server 2016 32bit and try to put data in excel 2016.
The excel sheet exists, it has header data in it. I'm trying to put the output of the stored procedure into excel.
This is the code I have but it doesn't work and get an error message-
Msg 7403, Level 16, State 1, Line 14
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.
---------------------------------------------------------------------------------------
statement is -
insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 16.0;
Database=C:\Users\09793491\Desktop\Tara\test\TestLand_Revaluation.xlsx',
'SELECT * FROM [Sheet1$A1:AR1]')
select * from dbo.reval_vg_land_data
May 18, 2018 at 12:02 am
Hi,
have a look at these 2 posts which helped me with the problem.
http://blogs.msdn.com/b/farukcelik/archive/2010/06/04/accessing-excel-files-on-a-x64-machine.aspx
http://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx
You probably also need this:
USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
And don't forget that the user under which the stored procedure runs needs permissions on the path where the xls files are located.
Our experience is that you cannot test this from the stored procedure itself, only from a job that runs the procedure.
When run with a job, the agent that runs the job needs full permission on C:\Users\<name user that runs the engine>\AppData\Local\Temp.
It's complicated, I know 🙂
May 20, 2018 at 6:12 pm
When trying to run that scripts,getting some permission error messages,need to fix that now.
is there any other option apart from openrowset, basically I want to create a procedure and import data from my tables to an excel template.
Hence me not opting for import;/export or SSIS package options.
Could you advise me of any other method which can be written via scripts import data from my tables to an excel template.
May 22, 2018 at 10:11 am
tara.ajith1 - Sunday, May 20, 2018 6:12 PMWhen trying to run that scripts,getting some permission error messages,need to fix that now.
is there any other option apart from openrowset, basically I want to create a procedure and import data from my tables to an excel template.
Hence me not opting for import;/export or SSIS package options.
Could you advise me of any other method which can be written via scripts import data from my tables to an excel template.
Have you considered establishing a data connection within Excel?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply