TSQL to create Excel 2013 file

  • 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!

  • 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:

    😎

  • 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] 😉

  • 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] 😉

  • 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

  • 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"

  • 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]

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

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