June 9, 2004 at 4:57 am
The job I have does the following.
It calls a dts package that has vb script that creates a spreadsheet using "createobject".
I get the error message:
----------------------------------------------------------------
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Permission denied: 'CreateObject'
Error on Line 17
Step Error code: 800403FE
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:4500
------------------------------------------------------
The sql server agent runs under the sa account. I can get a job to call a package to add a row into an excel spreadsheet in the same folder that I am trying to create one. The nt folder allows access to anonymous and exveryone so I know it is nothing to do with permissions.
Even if I run the job that calls the package manually rather than scheduled it falls over. However, when I run the dts package directly it works OK.
June 9, 2004 at 7:56 am
Jonathan,
Don't know if this will help you but...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsfctcreateobject.asp
Darrell Parrish
La Crosse, WI
June 9, 2004 at 8:14 am
Thanks Darrell, but my code is OK - I think.
----------------------------------------------------------------
Function Main()
' Initiate variables
Dim appExcel
Dim newBook
Dim oSheet
Dim path
Dim oPackage
Dim oConn
' Set variable values
Set appExcel = CreateObject("Excel.Application")
Set newBook = appExcel.Workbooks.Add
Set oSheet = newBook.Worksheets(1)
'path = "\\server\folder\"
path = "\\server\folder\"
'Specify the column name in the Excel worksheet
oSheet.Range("A1").Value = "Invoice Number"
oSheet.Range("B1").Value = "Purch Ref"
oSheet.Range("C1").Value = "Patient Ref"
oSheet.Range("D1").Value = "Description"
oSheet.Range("E1").Value = "Raised Date"
oSheet.Range("F1").Value = "Type"
'Expand the columns to fit text
oSheet.Columns(1).ColumnWidth = 15
oSheet.Columns(2).ColumnWidth = 15
oSheet.Columns(3).ColumnWidth = 15
oSheet.Columns(4).ColumnWidth = 50
oSheet.Columns(5).ColumnWidth = 15
oSheet.Columns(6).ColumnWidth = 15
'Format the date field
'oSheet.Columns(5).NumberFormat = "dd-mmm-yy"
'Specify the name of the new Excel file to be created
DTSGlobalVariables("fileName").Value = path & Year(Now) & "-" & Month(Now) & "-" & Day(Now) & ".xls"
With newBook
.SaveAs DTSGlobalVariables("fileName").Value
.save
End With
appExcel.quit
'dynamically specify the destination Excel file
set oPackage = DTSGlobalVariables.parent
'connection 2 is to the Excel file
set oConn = oPackage.connections(1)
oConn.datasource = DTSGlobalVariables("fileName").Value
set oPackage = nothing
set oConn = nothing
Main = DTSTaskExecResult_Success
End Function
----------------------------------------------------------------------
June 9, 2004 at 2:08 pm
Hi,
Even i have faced the same problem. When i tried giving the Physical location (F:\Folder) on that server rather than the (\\Server\Folder ) it worked for us.
If you don't have the file on the same server where you run the SQL Server, create an mapping for the folder in that server and then give the path ( like f:\folder or something)
Hope this helps!!
June 10, 2004 at 3:06 am
I did try to write to the c drive and that failed as well. Maybe the account did not have permissions to write to the c drive on the server. I'll try another folder with access to everyone and anonymous and see if it workd.
June 10, 2004 at 7:17 am
Jonathan,
I think you are on the right path about the security. I just tried your code on my local box and a server. As long as the user has permissions to the drive (as well as the box having a mapping to it) the file is created just fine.
Good Luck!
Darrell Parrish
La Crosse, WI
June 13, 2004 at 7:46 pm
Try dcomcnfg.exe. Add the SQL Service account to Default Access Permissions under Default Security tab.
Hope that helps.
Kim
June 14, 2004 at 2:51 am
Darrell. Did you run the sql server agent against a windows authenticated user or sql server sa. I ran mine under sa and I think that is where the problem may lie. I think that you cannot give permissions to an sa account, but I may be wrong.
Kim. I did try your suggestion, but again I cannot give permissions to the sa user as it does not appear in the list to give access to.
Still need help. Im not giving up!
June 14, 2004 at 3:59 am
Thank you to everyone. I now have the job running. It appears that the following must be done to make it work.
1. A drive must be mapped to the file location. Server path does NOT work.
2. You need to run 'dcomcnfg' and add in the account that sql server agent is running under. If running under sql server SA account, then you must choose 'Everyone'.
Thanks again for everyone's help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply