August 25, 2014 at 10:46 am
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
August 25, 2014 at 11:25 am
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:
😎
August 31, 2014 at 11:57 pm
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?
September 2, 2014 at 9:39 pm
September 3, 2014 at 1:08 am
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
September 3, 2014 at 1:52 am
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"
September 3, 2014 at 5:36 am
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