TSQL to create Excel 2013 file

Question

Post reply

TSQL to create Excel 2013 file

vinu512

SSCoach

Points: 15729
More actions
August 24, 2014 at 2:37 am

#397251

Hi Everyone,
The company I work in was using sp_makewebtask for creating an excel file, but since we have moved to sql server 2008, there are some procedures which we are trying to change because sp_makewebtask has been deprecated.
We are using Excel 2013.
Following is what I have been able to put up as an experimental code, but this is not working for some reason I have not been able to figure out:
DECLARE @objExcel INT,@hr INT,@command VARCHAR(255),@strErrorMessage VARCHAR(255),@objErrorObject INT,@objConnection INT,@bucket INT,
@DDL VARCHAR(2000),@DataSource VARCHAR(100),@Worksheet VARCHAR(100)=NULL,@ConnectionString VARCHAR(255), @document int
Select @ConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%DataSource;Extended Properties="Excel 12.0 Xml;HDR=YES"',
@DDL='Create table CambridgePubs(Pubname Text, Address Text, Postcode Text)',
@DataSource ='C:\CambridgePubs.xlsx'
SELECT @ConnectionString = REPLACE (@ConnectionString, '%DataSource', @DataSource)
print @ConnectionString
EXEC @hr = sp_OACreate 'ADODB.Connection', @objconnection OUT
print @hr
EXEC @hr=sp_OASetProperty @objconnection,'ConnectionString', @ConnectionString
print @hr
print @objconnection
EXEC @hr=sp_OAMethod @objconnection, 'Open'
print @hr
EXEC @hr=sp_OAMethod @objconnection, 'Execute',@Bucket out , @DDL
print @hr
The above code has been taken from the following link:
https://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/
I am sure that a lot of suggestions would be to use SSIS or other tools.
But, that is a limitation that we currently have.
So, I need to do this only using TSQL.
This is definitely doable and with a little help from you guys, I am sure this can be tweaked.
Please help guys.
Vinu Vijayan
For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url]

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182946 More actions · Answer 1

Quick thought, looks to me that the only thing missing is to enable Ole Automation Procedures

Enable Ole Automation Procedures in T-SQL

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO

vinu512 SSCoach Points: 15729 More actions · Answer 2

Eirikur Eiriksson (8/24/2014)
Quick thought, looks to me that the only thing missing is to enable Ole Automation Procedures
Enable Ole Automation Procedures in T-SQL
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Thanks for the reply Eirikur.

OLE Automation Procedures were disabled earlier, and were throwing and error.

So, I had enabled them.

Hence, that is not the issue. Its something else.

But

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 3

this is not working for some reason

doesn't include any details what the issue might be.

Is there any error message?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Eirikur Eiriksson SSC Guru Points: 182946 More actions · Answer 4

Further on Lutz's question, what is the output of the code in your initial post? Note that all the sp_OA* procedure return 0 if successful, otherwise a non-zero value.

vinu512 SSCoach Points: 15729 More actions · Answer 5

LutzM (8/24/2014)
this is not working for some reason
doesn't include any details what the issue might be.
Is there any error message?

Hi LutzM,

Following is the error message:

Operation is not allowed when the object is closed

So, technically, the "EXEC @hr=sp_OAMethod @objconnection, 'Open'" is the part of the code that isn't working.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url]

vinu512 SSCoach Points: 15729 More actions · Answer 6

Eirikur Eiriksson (8/24/2014)
Further on Lutz's question, what is the output of the code in your initial post? Note that all the sp_OA* procedure return 0 if successful, otherwise a non-zero value.

Right on target Eirikur.

The sp_oamethod is not able to open the object.

But the connection string that I am using is for ms excel 2007+ versions.

And its pretty straight forward, this is why I am not able to figure out what the problem is.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url]

Eirikur Eiriksson SSC Guru Points: 182946 More actions · Answer 7

vinu512 (8/24/2014)
Eirikur Eiriksson (8/24/2014)
Further on Lutz's question, what is the output of the code in your initial post? Note that all the sp_OA* procedure return 0 if successful, otherwise a non-zero value.
Right on target Eirikur.
The sp_oamethod is not able to open the object.
But the connection string that I am using is for ms excel 2007+ versions.
And its pretty straight forward, this is why I am not able to figure out what the problem is.

Next step would be to create the file C:\CambridgePubs.xlsx and test the code again.

vinu512 SSCoach Points: 15729 More actions · Answer 8

Eirikur Eiriksson (8/24/2014)
vinu512 (8/24/2014)
Eirikur Eiriksson (8/24/2014)
Further on Lutz's question, what is the output of the code in your initial post? Note that all the sp_OA* procedure return 0 if successful, otherwise a non-zero value.
Right on target Eirikur.
The sp_oamethod is not able to open the object.
But the connection string that I am using is for ms excel 2007+ versions.
And its pretty straight forward, this is why I am not able to figure out what the problem is.
Next step would be to create the file C:\CambridgePubs.xlsx and test the code again.

The above code is to create the file. Isn't it?....I am assuming that I don't need to create the file in that case.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url]

Eirikur Eiriksson SSC Guru Points: 182946 More actions · Answer 9

vinu512 (8/24/2014)
Eirikur Eiriksson (8/24/2014)
vinu512 (8/24/2014)
Eirikur Eiriksson (8/24/2014)
Further on Lutz's question, what is the output of the code in your initial post? Note that all the sp_OA* procedure return 0 if successful, otherwise a non-zero value.
Right on target Eirikur.
The sp_oamethod is not able to open the object.
But the connection string that I am using is for ms excel 2007+ versions.
And its pretty straight forward, this is why I am not able to figure out what the problem is.
Next step would be to create the file C:\CambridgePubs.xlsx and test the code again.
The above code is to create the file. Isn't it?....I am assuming that I don't need to create the file in that case.

Just a normal debugging, testing one thing at the time. Possible cause could be i.e. permissions.

vinu512 SSCoach Points: 15729 More actions · Answer 10

Permissions don't seem to be the issue either. I am doin this on my laptop. There is only one user on my laptop. So, permissions are also fine.

Please help guys. This is makin me rip off my hair. :crazy::sick::crazy:

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 11

What permission does the user have who's running that code?

Based on MS Technet

Only members of the sysadmin fixed server role can execute sp_OASetProperty.

If you're not a member of the sysadmin role, this might be the reason for not creating the file.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

vinu512 SSCoach Points: 15729 More actions · Answer 12

LutzM (8/24/2014)
What permission does the user have who's running that code?
Based on MS Technet
Only members of the sysadmin fixed server role can execute sp_OASetProperty.
If you're not a member of the sysadmin role, this might be the reason for not creating the file.

As I mentioned earlier, I am using the admin account on my Lappy.

And I am logged into sql server using Windows Authentication.

And I do have the sys admin role checked for this login.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url]

Eirikur Eiriksson SSC Guru Points: 182946 More actions · Answer 13

vinu512 (8/24/2014)
LutzM (8/24/2014)
What permission does the user have who's running that code?
Based on MS Technet
Only members of the sysadmin fixed server role can execute sp_OASetProperty.
If you're not a member of the sysadmin role, this might be the reason for not creating the file.
As I mentioned earlier, I am using the admin account on my Lappy.
And I am logged into sql server using Windows Authentication.
And I do have the sys admin role checked for this login.

This is not a question about your credentials on the box but the SQL server's, don't think that process is running as admin.

sqldriver SSChampion Points: 10440 More actions · Answer 14

Just a thought, if you can't get the permissions for the SQL Service account sorted out. You can always raise makewebtask from the dead, if you still have a 2008 instance with the .dll sitting around somewhere:

--EXEC dbo.sp_addextendedproc N'xp_makewebtask', 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\xpweb90.dll'

--EXECUTE master..xp_makewebtask

TSQL to create Excel 2013 file

Cookies on SQLServerCentral