TSQL to create Excel 2013 file

Question

TSQL to create Excel 2013 file

Viewing 7 posts - 16 through 22 (of 22 total)

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

Lowell SSC Guru Points: 323522 More actions · Answer 1

correct me if i'm wrong, but I thought noone can write to a file on the root of the C drive unless they are running elevated, admin priviledges:

so C:\CambridgePubs.xlsx would fail, but

C:\Data\CambridgePubs.xlsx would probably be ok, assuming path/file exists and all that stuff.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Eirikur Eiriksson SSC Guru Points: 182947 More actions · Answer 2

Lowell (8/25/2014)
correct me if i'm wrong, but I thought noone can write to a file on the root of the C drive unless they are running elevated, admin priviledges:
so C:\CambridgePubs.xlsx would fail, but
C:\Data\CambridgePubs.xlsx would probably be ok, assuming path/file exists and all that stuff.

My point exactly! Maybe I didn't communicate it well enough:ermm:

vinu512 SSCoach Points: 15729 More actions · Answer 3

Lowell (8/25/2014)
correct me if i'm wrong, but I thought noone can write to a file on the root of the C drive unless they are running elevated, admin priviledges:
so C:\CambridgePubs.xlsx would fail, but
C:\Data\CambridgePubs.xlsx would probably be ok, assuming path/file exists and all that stuff.

True. But it doesn't work even if I change the path to D drive.

Sorry for the late reply, was on a work trip and didnt get time for this.

Any other ideas 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]

vinu512 SSCoach Points: 15729 More actions · Answer 4

Does this work for anyone??....Can someone please test the code on your local box and let me know??...Thanks

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]

Umer Akram SSC-Insane Points: 22209 More actions · Answer 5

i have just added the error message generation in your code. try to execute it and check what error its generate

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

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @objconnection

Return

END;

EXEC @hr=sp_OAMethod @objconnection, 'Execute',@Bucket out , @DDL

print @hr

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @objconnection

Return

END;

If the error is something like this

0x800A0E7AADODB.ConnectionProvider cannot be found. It may not be properly installed.

Then i believe you are 64bit SQL SERVER, with 32 bit access driver you need a 64bit driver.

Download "AccessDatabaseEngine_X64.exe" from http://www.microsoft.com/en-gb/download/details.aspx?id=13255

yingchai SSCommitted Points: 1669 More actions · Answer 6

I had executed twin.devil code and it works on my side. I am using SQL Server 2012.

Apart from that, I am interested to know how to populate few records into the table.

For example:

Pubname | Address | Postcode

"aaa" | "bbb" | "ccc"

Umer Akram SSC-Insane Points: 22209 More actions · Answer 7

yingchai (9/3/2014)
I had executed twin.devil code and it works on my side. I am using SQL Server 2012.
Apart from that, I am interested to know how to populate few records into the table.
For example:
Pubname | Address | Postcode
"aaa" | "bbb" | "ccc"

Just like we use the insert statement for any other table in sql. Like this.

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)',

@DDL = 'insert into CambridgePubs(Pubname,Address,Postcode) values (''The Bird in Hand'',''23, Marshall Road, Cambridge CB4 2DQ'',''CB4 2DQ'')',

@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

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @objconnection

Return

END;

EXEC @hr=sp_OAMethod @objconnection, 'Execute',@Bucket out , @DDL

print @hr

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @objconnection

Return

END;

following is complete article written on this SQL Server Excel Workbench[/url]

TSQL to create Excel 2013 file

Cookies on SQLServerCentral