OLE Automation help
-
Hi Guys
Hope you all had a very nice christmas.
I am working on a project at the mo that will query my database and put the results in to a spreadsheet and email them out. I have set this up on a local instance and i can run the whole process from the same machine with no problems.
I am however encountering problems when i try and run the same code on from my local machine on a remote instance. It seems that there may be a security issue when creating the worksheet on the remote machine.
I believe that it is a security issue but i cannot figure out what to change.
Any pointers would be appreciated.
Thanks
Steve
-
The server you are working on, whether local or remote, must be logged in as a user that can "see" the spreadsheet using a UNC instead of a mapped drive.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks for the reply.
I have changed the path from 'C:\severname\test\ExcelExport.xls' to '\\Servername\Test\ExcelExport.xls' and still the same issue. I can access that from both the server and the local machine.
The user that i am logged into the instance as has the sysadmin fixed role. Does it need anything else?
-
Not the login you're using... the login the SQL SERVER is using when it starts up on the network...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
sperry (12/26/2008)
Thanks for the reply.I have changed the path from 'C:\severname\test\ExcelExport.xls' to '\\Servername\Test\ExcelExport.xls' and still the same issue. I can access that from both the server and the local machine.
The user that i am logged into the instance as has the sysadmin fixed role. Does it need anything else?
what Jeff was saying is that when you use something that goes outside of a database...OLE, xp_cmdshell, whatever, the user you logged in with is not used....the user that is used to run the service is used instead:
THAT IS account is used...so if it doesn't have access to a folder the Administrator created locally, or does not login to the network, and this cannot go to a \\UNC\share, you will get a failure with your OLE.
I don't know about others, but I usually create a local or domain Admin named SQLAdmin, with a password that does not expire, and use that to run the service...that way I'm sure it has access we are talking about.
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! -
Thanks for the backup Lowell... I was thinking that I need to include a graphic and a better explanation... you saved me some work. Thanks, ol' friend.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks again guys. I have already found an article pointing to the user rights. I have added the SQL service user into the security group for that server with full control. Still no luck.
The code that i am using is below (it is stolen from the simple talk workbench)
alter PROCEDURE [dbo].[usp_spDMOExportToExcel] (
@SourceServer VARCHAR(30),
@SourceUID VARCHAR(30)=NULL,
@SourcePWD VARCHAR(30)=NULL,
@QueryText VARCHAR(200),
@filename VARCHAR(100),
@WorksheetName VARCHAR(100)='Worksheet1',
@RangeName VARCHAR(80)='MyRangeName'
)
AS
DECLARE @objServer INT,
@objQueryResults INT,
@objCurrentResultSet INT,
@objExcel INT,
@objWorkBooks INT,
@objWorkBook INT,
@objWorkSheet INT,
@objRange INT,
@hr INT,
@Columns INT,
@Rows INT,
@Output INT,
@currentColumn INT,
@currentRow INT,
@ResultSetRow INT,
@off_Column INT,
@off_Row INT,
@command VARCHAR(255),
@ColumnName VARCHAR(255),
@value VARCHAR(255),
@strErrorMessage VARCHAR(255),
@objErrorObject INT,
@Alphabet VARCHAR(27)
SELECT @Alphabet='ABCDEFGHIJKLMNOPQRSTUVWXYZ'
IF @QueryText IS NULL
BEGIN
RAISERROR ('A query string is required for spDMOExportToExcel',16,1)
RETURN 1
END
-- Sets the server to the local server
IF @SourceServer IS NULL SELECT @SourceServer = @@servername
SET NOCOUNT ON
SELECT @strErrorMessage = 'instantiating the DMO',
@objErrorObject=@objServer
EXEC @hr= sp_OACreate 'SQLDMO.SQLServer', @objServer OUT
IF @SourcePWD IS NULL OR @SourceUID IS NULL
BEGIN
--use a trusted connection
IF @hr=0 SELECT @strErrorMessage=
'Setting login to windows authentication on '
+@SourceServer, @objErrorObject=@objServer
IF @hr=0 EXEC @hr=sp_OASetProperty @objServer, 'LoginSecure', 1
IF @hr=0 SELECT @strErrorMessage=
'logging in to the requested server using windows authentication on '
+@SourceServer
IF @SourceUID IS NULL AND @hr=0 EXEC @hr=sp_OAMethod @objServer,
'Connect', NULL, @SourceServer
IF @SourceUID IS NOT NULL AND @hr=0
EXEC @hr=sp_OAMethod
@objServer, 'Connect', NULL, @SourceServer ,@SourceUID
END
ELSE
BEGIN
IF @hr=0
SELECT @strErrorMessage = 'Connecting to '''+@SourceServer+
''' with user ID '''+@SourceUID+'''',
@objErrorObject=@objServer
IF @hr=0
EXEC @hr=sp_OAMethod @objServer, 'Connect', NULL,
@SourceServer, @SourceUID, @SourcePWD
END
--now we execute the query
IF @hr=0 SELECT @strErrorMessage='executing the query "'
+@querytext+'", on '+@SourceServer,
@objErrorObject=@objServer,
@command = 'ExecuteWithResults("' + @QueryText + '")'
IF @hr=0
EXEC @hr=sp_OAMethod @objServer, @command, @objQueryResults OUT
IF @hr=0
SELECT @strErrorMessage='getting the first result set for "'
+@querytext+'", on '+@SourceServer,
@objErrorObject=@objQueryResults
IF @hr=0 EXEC @hr=sp_OAMethod
@objQueryResults, 'CurrentResultSet', @objCurrentResultSet OUT
IF @hr=0
SELECT @strErrorMessage='getting the rows and columns "'
+@querytext+'", on '+@SourceServer
IF @hr=0
EXEC @hr=sp_OAMethod @objQueryResults, 'Columns', @Columns OUT
IF @hr=0
EXEC @hr=sp_OAMethod @objQueryResults, 'Rows', @Rows OUT
--so now we have the queryresults. We start up Excel
IF @hr=0
SELECT @strErrorMessage='Creating the Excel Application, on '
+@SourceServer, @objErrorObject=@objExcel
IF @hr=0
EXEC @hr=sp_OACreate 'Excel.Application', @objExcel OUT
IF @hr=0 SELECT @strErrorMessage='Getting the WorkBooks object '
IF @hr=0
EXEC @hr=sp_OAGetProperty @objExcel, 'WorkBooks',
@objWorkBooks OUT
--create a workbook
IF @hr=0
SELECT @strErrorMessage='Adding a workbook ',
@objErrorObject=@objWorkBooks
IF @hr=0
EXEC @hr=sp_OAGetProperty @objWorkBooks, 'Add', @objWorkBook OUT
--and a worksheet
IF @hr=0
SELECT @strErrorMessage='Adding a worksheet ',
@objErrorObject=@objWorkBook
IF @hr=0
EXEC @hr=sp_OAGetProperty @objWorkBook, 'worksheets.Add',
@objWorkSheet OUT
IF @hr=0
SELECT @strErrorMessage='Naming a worksheet as "'
+@WorksheetName+'"', @objErrorObject=@objWorkBook
IF @hr=0
EXEC @hr=sp_OASetProperty @objWorkSheet, 'name', @WorksheetName
SELECT @currentRow = 1
--so let's write out the column headings
SELECT @currentColumn = 1
WHILE (@currentColumn <= @Columns AND @hr=0)
BEGIN
IF @hr=0
SELECT @strErrorMessage='getting column heading '
+LTRIM(STR(@currentcolumn)) ,
@objErrorObject=@objQueryResults,
@Command='ColumnName('
+CONVERT(VARCHAR(3),@currentColumn)+')'
IF @hr=0 EXEC @hr=sp_OAGetProperty @objQueryResults,
@command, @ColumnName OUT
IF @hr=0
SELECT @strErrorMessage='assigning the column heading '+
+ LTRIM(STR(@currentColumn))
+ ' from the query string',
@objErrorObject=@objExcel,
@command='Cells('+LTRIM(STR(@currentRow)) +', '
+ LTRIM(STR(@CurrentColumn))+').value'
IF @hr=0
EXEC @hr=sp_OASetProperty @objExcel, @command, @ColumnName
SELECT @currentColumn = @currentColumn + 1
END
--format the headings in Bold nicely
IF @hr=0
SELECT @strErrorMessage='formatting the column headings in bold ',
@objErrorObject=@objWorkSheet,
@command='Range("A1:'
+SUBSTRING(@alphabet,@currentColumn/26,1)
+SUBSTRING(@alphabet,@currentColumn % 26,1)
+'1'+'").font.bold'
IF @hr=0 EXEC @hr=sp_OASetProperty @objWorkSheet, @command, 1
--now we write out the data
SELECT @currentRow = 2
WHILE (@currentRow <= @Rows+1 AND @hr=0)
BEGIN
SELECT @currentColumn = 1
WHILE (@currentColumn <= @Columns AND @hr=0)
BEGIN
IF @hr=0
SELECT
@strErrorMessage=
'getting the value from the query string'
+ LTRIM(STR(@currentRow)) +','
+ LTRIM(STR(@currentRow))+')',
@objErrorObject=@objQueryResults,
@ResultSetRow=@CurrentRow-1
IF @hr=0
EXEC @hr=sp_OAMethod @objQueryResults, 'GetColumnString',
@value OUT, @ResultSetRow, @currentColumn
IF @hr=0
SELECT @strErrorMessage=
'assigning the value from the query string'
+ LTRIM(STR(@CurrentRow-1)) +', '
+ LTRIM(STR(@currentcolumn))+')' ,
@objErrorObject=@objExcel,
@command='Cells('+STR(@currentRow) +', '
+ STR(@CurrentColumn)+').value'
IF @hr=0
EXEC @hr=sp_OASetProperty @objExcel, @command, @value
SELECT @currentColumn = @currentColumn + 1
END
SELECT @currentRow = @currentRow + 1
END
--define the name range
--Cells(1, 1).Resize(10, 5).Name = "TheData"
IF @hr=0 SELECT @strErrorMessage='assigning a name to a range '
+ LTRIM(STR(@CurrentRow-1)) +', '
+ LTRIM(STR(@currentcolumn-1))+')' ,
@objErrorObject=@objExcel,
@command='Cells(1, 1).Resize('+STR(@currentRow-1) +', '
+ STR(@CurrentColumn-1)+').Name'
IF @hr=0 EXEC @hr=sp_OASetProperty @objExcel, @command, @RangeName
--Now autofilt the columns we've written to
--IF @hr=0 SELECT @strErrorMessage='Auto-fit the columns ',
-- @objErrorObject=@objWorkSheet,
-- @command='Columns("A:'
-- +SUBSTRING(@alphabet,(@Columns / 26),1)
-- +SUBSTRING(@alphabet,(@Columns % 26),1)+
-- '").autofit'
--
--
--IF @hr=0 --insert into @bucket(bucket)
-- EXEC @hr=sp_OAMethod @objWorkSheet, @command, @output out
execute @hr = sp_OAMethod @objWorkSheet, 'Columns.AutoFit'
IF @hr=0 SELECT @command ='del "' + @filename + '"'
IF @hr=0 EXECUTE master..xp_cmdshell @Command, no_output
IF @hr=0
SELECT @strErrorMessage='Saving the workbook as "'+@filename+'"',
@objErrorObject=@objRange,
@command = 'SaveAs("' + @filename + '")'
IF @hr=0 EXEC @hr=sp_OAMethod @objWorkBook, @command
IF @hr=0 SELECT @strErrorMessage='closing Excel ',
@objErrorObject=@objExcel
EXEC @hr=sp_OAMethod @objWorkBook, 'Close'
EXEC sp_OAMethod @objExcel, 'Close'
IF @hr<>0
BEGIN
DECLARE
@Source VARCHAR(255),
@Description VARCHAR(255),
@Helpfile VARCHAR(255),
@HelpID INT
EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,
@Helpfile output,@HelpID output
SELECT @hr, @source, @Description,@Helpfile,@HelpID output
SELECT @strErrorMessage='Error whilst '
+COALESCE(@strErrorMessage,'doing something')
+', '+COALESCE(@Description,'')
RAISERROR (@strErrorMessage,16,1)
END
EXEC sp_OADestroy @objServer
EXEC sp_OADestroy @objQueryResults
EXEC sp_OADestroy @objCurrentResultSet
EXEC sp_OADestroy @objExcel
EXEC sp_OADestroy @objWorkBooks
EXEC sp_OADestroy @objWorkBook
EXEC sp_OADestroy @objRange
RETURN @hr
When i call this from another stored proc i get this error message.
Msg 50000, Level 16, State 1, Procedure usp_spDMOExportToExcel, Line 250
Error whilst Creating the Excel Application, on C6SERVER13, sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval OUT [, additional IN, OUT, or BOTH params]]
Msg 22051, Level 16, State 1, Line 0
The error handeling being returned by the stored proc leads me to believe that that it is an issue creating the worksheet. Any pointers would be great, i have been going over this for days now.
Thanks
-
I believe that I'd post that exact same thing on Simple Talk and let Phil and Robyn have a crack at it... they're the ones that wrote the method to begin with... they should be able to troubleshoot it for you in a jiffy.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I will do that. Thanks again.
-
Hi again.
I didnt manage to get a response from anyone at simple talk. I have narrowed the problem down to security rights.
The account that we use as the service account is in the power user domain group. When i run it i get an error telling me that the excel file could not be created.
If i add that user into the administrators domain group it works.
I have tested this on a number of machines and get the same results on all of them.
i do not want to give this account full administrator rights. Does anyone have any idea which rights i would need to give this ser to allow it to create the file?
Thanks
-
sperry (1/7/2009)
Hi again.I didnt manage to get a response from anyone at simple talk. I have narrowed the problem down to security rights.
The account that we use as the service account is in the power user domain group. When i run it i get an error telling me that the excel file could not be created.
If i add that user into the administrators domain group it works.
I have tested this on a number of machines and get the same results on all of them.
i do not want to give this account full administrator rights. Does anyone have any idea which rights i would need to give this ser to allow it to create the file?
Thanks
Hmmmm.... you could make sure that the server itself is logged in as such a user, create a "job" on SQL Server having it log it as that user, and then have the sproc run that job effectively forming a "proxy". That means the ultimate user would have the rights to run the "job" but not the code... that would keep the user from having the rights you want to keep away while still letting them do this one very very specific thing.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
No dice.
I have the service account set as the domain user with limited rights. I then set up a job that calls the procedure in question and set the Run As User as the domain administrator (An account that i know works).
Was this correct?
-
So as Administrator, it can access the folder with Read/Write permissions, but as it's normal domain rights it can't...I think it goes back to Security permissions on the Folder.
If the folder was created by an administrator login, that user probably can't access it...just check the specific folder(right click...security...note the groups with permission)
if your power user login CREATES the folder, I think your issue goes away.
via TSQL, since it would use your power user rights, make a new directory,so you know that user is the owner/creator
try exec xp_cmdshell 'mkdir C:\ExcelTest'
then use that new folder for the testing of creating the Exceldocument.
I think since the pwer user created it instead of administrator, your issue might go away.
sperry (1/7/2009)
Hi again.I didnt manage to get a response from anyone at simple talk. I have narrowed the problem down to security rights.
The account that we use as the service account is in the power user domain group. When i run it i get an error telling me that the excel file could not be created.
If i add that user into the administrators domain group it works.
I have tested this on a number of machines and get the same results on all of them.
i do not want to give this account full administrator rights. Does anyone have any idea which rights i would need to give this ser to allow it to create the file?
Thanks
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! -
Still the same error i am afraid.
The folder was created fine and the service account user was down as the owner.
The original folder that i was using had full control assigned to everybody.
It works for local users, local admins and domain admins but nothing else.
Thanks for the suggestion.
-
I have a question about methodology. Why not, instead of OLE automation, use SSIS? It's designed to do this kind of thing. It has no problem whatsoever with creating and e-mailing spreadsheets from SQL data. I've done that dozens of times.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply