August 24, 2014 at 2:37 am
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.
August 24, 2014 at 2:48 am
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
August 24, 2014 at 2:57 am
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
August 24, 2014 at 3:02 am
this is not working for some reason
doesn't include any details what the issue might be.
Is there any error message?
August 24, 2014 at 3:14 am
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.
😎
August 24, 2014 at 3:15 am
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.
August 24, 2014 at 3:18 am
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.
August 24, 2014 at 3:58 am
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.
😎
August 24, 2014 at 5:02 am
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.
August 24, 2014 at 5:24 am
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.
😎
August 24, 2014 at 5:32 am
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:
August 24, 2014 at 5:32 am
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.
August 24, 2014 at 5:37 am
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.
August 24, 2014 at 6:52 am
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.
😎
August 25, 2014 at 9:00 am
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
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply