Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618

    Unanswered: DTS Package Execution Context

    I have some DTS Packages that are no longer executing successfully. They were yesterday they are not today.

    They are reporting an error when this line is called...

    Set objConn = CreateObject("ADODB.Connection")

    The error is...

    The remote server machine does not exist or is unavailable.

    Now this error occurs when I execute the package, but if I execute the individual task that makes this call no error is raised. There is only one task in the package and all previous lines in the task are just setting up variables and the like.

    I suspect that the problem has occured due to the installation of MS Office on the machine running SQL Server. I think that perhaps a dll was overwritten that should not have been.

    Has anyone else had this problem and found a solution? What is the difference in the execution context when you execute task instead of execute package?

    Anyone able to help me out?

    Cheers,
    Rokslide

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy

    Seach for this phrase within Books On Line :

    package execution permissions

    I think it will point you in the right direction....

    Cheers,

    SG.

  3. #3
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    I have had a look through the online books. They tell you about the permissions that the package executes with but not the task,..

    I would have thought they would be the same, but from what I am seeing it would appear that they are not....

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    A couple of questions:

    Is this a vb dts package or a sql dts package ? If it is a sql dts package, is this occuring in an activex scripting task ? Have you tried recreating a package from scratch to see if the error appears ? Did the office installation update your mdac ?

  5. #5
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    The package is a sql dts package and the code is in an activex scripting task.

    If I recreate the package the problem still appears.

    I think it is possible that the installation of office did update mdac, but I don't know for sure as I didn't do the install. The MDAC version tool tells me that we are using MDAC 2.7 at the moment...

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    Have you restarted sql server since the installation ? Have you installed the service pack for mdac ? Will this error appear in any attempt to CreateObject() such as Recordset or Scripting.FileSystemObject ? If you have visual basic - save this package as a visual basic module and try it from the vb ide.

  7. #7
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    I can create any object except for ADODB objects from the looks of things.

    I can restart the server but I believe it has been rebooted after the office install...

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    I would apply the service pack for mdac. When you are attempting to execute the package what login are you using (and with what permissions) ? When you execute the task (and it succeeds) and you execute the package (and it fails) are you using the same login ? What is the login used for the sql server and agent services ? Also, whoever installed office, under what login was it installed ?

  9. #9
    Join Date
    Feb 2002
    Posts
    2,232
    Also, try to execute the vbscript code in office and see if the same error occurs. And if you have vb installed, try creating a connection object using early binding first (setting the reference for the ado object) and if that succeeds try the late-binding method (createobject). Also, try logging in as an Administrator (or Domain Admin) and as a last resort temporarily change the login for sql service to the Administrator account (to eliminate any permissions issues).

  10. #10
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    If I open the Package and click the execute package button it fail, if I then right click on the task and go "Execute Task" it succeeds....

    I will try out some of the other things you have suggested and get back to you.

  11. #11
    Join Date
    Feb 2002
    Posts
    2,232
    If the mdac sp does not help, then I would start to test permissions issues (also turn on logging for the package) - modifying the sqlagent/sql server services to run as administrator and creating the package as administrator. Lastly reinstalling the mdac.

  12. #12
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618

    Cool

    It looks like it was the MDAC installation that was corrupted.

    INstalled MDAC 2.8 and all my troubles have gone away. Of course I had to prove to support that this was the problem before they would let me install it...

    Thanks for all the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •