The objective of my applications sounds quite simple, but with the old technologies, I am getting stuck everytime. I have a ASP web application which has a page to upload a spreadsheet from the client machine and save it on the server. This ASP
application then calls a method in a DLL, which is developed in VB6 (not VB.Net). This VB DLL would create a excel object to open this spreadsheet (that has been saved on the server) and saves it as a csv file in the same path. This csv format data is then
inserted into the database.
The problem that I am facing is, the VB6 program fails to create the excel object as the server doesnt have Office Excel installed. below is the VB code that I am trying to execute. It fails on thhe second line, where I am trying to create a Excel
Application object and I get an error: "run-time error '429' activex component can't create object". I had tried to run the below VB code by adding reference to "Microsoft Excel 12.0 Object Library" (refers to C:\Program Files\Microsoft Office\Office12\Excel.exe)
and "Microsoft Office 12.0 Object Library" (refers to C:\Program Files\Common Files\Microsoft Shared\Office12\MSO.DLL), creating a package out of it so that the required
View Complete Post
I'm tried SELECT * INTO XXX FROM OPENROWSET alongwith Microsoft.ACE.OLEDB.12.0.
Apparently the query requires the sql account to have SYSADMIN privileges.
Considering that SYSADMIN should not be provided to a database account on a Production Server, I tried using the Linked Server method.
Following is my code.
Exec sp_addlinkedserver 'AB2','Ace 12.0','Microsoft.ACE.OLEDB.12.0','\\22.214.171.124\sagarr\Test1\cpc\c2\AB2.xlsx',NULL,'Excel 12.0;IMEX=1'Exec sp_addlinkedsrvlogin 'AB2','false',NULL,NULL,NULLgoSELECT * INTO [CPCAB2.xlsx] FROM OPENQUERY([AB2] ,'SELECT * FROM [Sheet1$]')Exec sp_dropserver 'AB2','droplogins'
Now i get the following error
Error.15247-User does not have permission to perform this action
My Excel file, Database and Windows Application run on separate machines.
i have provided the following privileges
GRANT ALTER ANY LOGIN TO sqlaccountGRANT ALTER ANY LINKED SERVER TO sqlaccountEXEC sp_configure 'show advanced options', 1RECONFIGUREEXEC sp_configure 'ad hoc distributed queries', 1RECONFIGUREThe DisAllowAdHocProcess in