Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Problem with Store Procedure

  1. #1
    Join Date
    May 2005
    Posts
    10

    Unhappy Problem with Store Procedure

    Hello to all, I am new of the forum and I have a problem. I am realizing one store procedures in order launch DTS but this works alone on a PC on three! Which it is the problama? on all and three the PC is installed the same version of SQL Serveur 2000 (SP3) and the same DB is loaded on the PC Someone can aitarmi? is something to shape?

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    You mean your procedure works on one pc but doesn't on other three pcs? Any more details?

  3. #3
    Join Date
    May 2005
    Posts
    10

    Unhappy

    In my office there are three PC with SQL Server 2000. On my PC (notebook) I have a DB called SA2 and on this there is a store procedure that run a dts and it works fine. I have backup the DB and store procedure on other PC but the store procedure loop without end...can you help me?

  4. #4
    Join Date
    Feb 2003
    Posts
    1,048
    Post your procedure.

    Are you using xp_cmdshell? If so, do all users on all PC's have sa permissions?

  5. #5
    Join Date
    May 2005
    Posts
    10
    This is my procedure

    CREATE procedure prova_run as
    declare @hr1 int, @hr2 int, @hr3 int, @hr4 int,
    @oPKG int

    --Creazione del package
    EXEC @hr1 = sp_OACreate 'DTS.Package', @oPKG OUT

    --Caricamento del package
    EXEC @hr2 = sp_OAMethod @oPKG, 'LoadFromSQLServer("localhost", "sa", "sa", 256, , , , "DTS1")', NULL

    --Esecuzione del package
    EXEC @hr3 = sp_OAMethod @oPKG, 'exec'

    --Pulizia dell'ambiente
    EXEC @hr4 = sp_OADestroy @oPKG
    GO

    This procedure load a DTS (DTS1) from my server. DTS1 has only an ActiveX Script that does a MsgBox (Hallo!)

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Where is the dts package? On all three pcs?

  7. #7
    Join Date
    May 2005
    Posts
    10
    Yes. On three PC there are the same: DB, store procedure and DTS.
    There is something to set for use stored procedure to run a DTS?

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    No such settings. But should check package on each box to see if has proper connection setting, and try use sql instance name instead of localhost in your sp.

  9. #9
    Join Date
    Feb 2003
    Posts
    1,048
    Or try using "(local)" instead of "localhost".

  10. #10
    Join Date
    May 2005
    Posts
    10
    I try to rename localhost in local but the problem is that the dts doesn't run!
    I print the value of @h1, @h2, @h3, @h4 and the result are:

    0
    0
    -2147352570 (value of @h3)
    0

    The value of @h3 is't correct?
    I think that if this value is 0 the DTS will run...
    Does it wrong the call of method sp_OAMathod?

  11. #11
    Join Date
    Nov 2002
    Location
    DE
    Posts
    246
    I would recommend the following check:

    1) Turn on logging in your package
    2) Run your SP
    3) Check what's in the log
    ==>
    a) If the log is empty the package did not execute at all
    b) If the log is not empty it might tell you what the actual problem is

  12. #12
    Join Date
    May 2005
    Posts
    10
    Originally posted by andi_g69
    I would recommend the following check:

    1) Turn on logging in your package
    2) Run your SP
    3) Check what's in the log
    ==>
    a) If the log is empty the package did not execute at all
    b) If the log is not empty it might tell you what the actual problem is
    1)I turn on the login in my package
    2)if I execute the package from Enterprise Manager, the log file is empty and the DTS work well
    2bis)if i execute the dts from SP it doesn't work and the log file is empty

    Where is the problem?

  13. #13
    Join Date
    May 2005
    Posts
    10
    I have put some print in the code for monitoring the value of variables. The result is:

    0 --> @hr1 (create package)
    0 --> @hr2 (load package)
    -2147352570 --> @hr3 (execute package)
    *** Execute failed
    OLE Automation Error Information
    HRESULT: 0x80020006
    Source: ODSOLE Extended Procedure
    Description: Nome sconosciuto.

    I think that the value of @hr3 is wrong and there are some problem...

  14. #14
    Join Date
    Feb 2003
    Posts
    1,048
    Do all SQL Servers have the current pack installed? This sounds ominously like a bug that was fixed in SP1 of SQL 2000.

    There was a bug with the initial release of SQL 2000 where DTS had problems saving, loading, and/or executing packages if there was an object in the package that did not have a name. Updating the service packs will resolve the error if that is the case.

  15. #15
    Join Date
    May 2005
    Posts
    10
    I have installed also SP4 but the stored procedure doesn't work.
    I don't think that the problem is in SP..I think that is a problem of autorization to execute a dts from stored procedure...

Posting Permissions

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