Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Posts
    5

    Question Unanswered: cannot run DTS Package from SQL Query Analyzer

    I attemped to run dts package to transform data from AS400/DB2 by using Client Access ODBC ( Ms Windows 2000 Server, Ms SQL 2K, Client Access Driver 4.5)

    but the result of testing has messed me up

    because when i used this command via SQL Query Analyzer

    exec master..xp_cmdshell 'DTSRun /S "MySVR" /U "sa" /N "dtsTEST" /G "{482209FD-0891-11D8-868C-00E0299B4BCF}" /A "G1":"8"="200310" /W "0"'

    but it doesn't work


    I wonder why.. because it can be executed in MS-Dos prompt successfully.
    if I type

    DTSRun /S "MySVR" /U "sa" /N "dtsTEST" /G "{482209FD-0891-11D8-868C-00E0299B4BCF}" /A "G1":"8"="200310" /W "0"

    in the command line.


    If somebody know please give me some advice (urgently!!).

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    What is the error?

    When run from dos it will be run on your client m/c. When from query analyser on the server using the sql server service account (probably)

    Try logging on to the server as the sql server service account and running it in the designer and see what happens.

  3. #3
    Join Date
    Nov 2003
    Posts
    5
    I didn't run dos-command on Client M/C, I tested on Server M/C itself
    and it success.

    But when I tried to run by query analyzer, it takes much time (> 20 min and nothing happens) for executing and there was no error show on the screen. so I had to kill the process because it seemed not work .
    And this process I tested on Server M/C also, by using Sql Admin (sa).

    How could I solve this problem?

  4. #4
    Join Date
    Nov 2003
    Posts
    5

    Red face

    It can be run properly in DTS Designer, but cannot run in Query analyzer.

  5. #5
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Does it have any user interaction - have you left in a debug msgbox?

    I meant the account that the sql server service uses to logon - see services.
    Maybe it needs access to something that this account doesn't have permission on.

  6. #6
    Join Date
    Nov 2003
    Posts
    5
    I checked already that the log on user-id has full permission, and the sign on user name and password for AS400 has been specify in DSN that use via client access express driver by setting the sign on information as 'use windows user name and password, no promping'.

    If this problem is related to the security of sql server, why it can be run in DTS Designer(which has no pop up message box for user interface) with no popup error message and the result of transformation is correct.

  7. #7
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Because dts is a client application.
    When you run it from query analyser the client is the server which gives it a different environment.

    If you have already run it from the sql server service account then that limits the things that can be wrong.
    Try turning on logging to see if any steps are run and what the error is.

  8. #8
    Join Date
    Nov 2003
    Posts
    5
    why i can run DTS Package in query analyzer if i change the source of DTS to be MsAccess file, on the server m/c not client. run in command line, no problem also.

    why i cannot run in query analyzer if i specified source DSN as AS400/DB2, but run properly in command line by using same script.

Posting Permissions

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