-
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?
-
You mean your procedure works on one pc but doesn't on other three pcs? Any more details?
-
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?
-
Post your procedure.
Are you using xp_cmdshell? If so, do all users on all PC's have sa permissions?
-
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!)
-
Where is the dts package? On all three pcs?
-
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?
-
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.
-
Or try using "(local)" instead of "localhost".
-
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?
-
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
-
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?
-
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...
-
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.
-
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
-
Forum Rules
|
|