Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gchacko
Starting Member
8 Posts |
 Posted - 2002-09-03 : 11:16:10
|
| I have created a package with five SQL Tasks.Four of these SQL Task calls stored procedure to do processing.One SQL Task for error handling.If any of the SQL Task generates error, I want that SQL Task to update the following global fields:DTSGlobalVariables("AppName").valueDTSGlobalVariables("RunStatus").valueDTSGlobalVariables("StepNum").valueDTSGlobalVariables("ErrorNum").valueThe SQL Task for error handling can then read these values from global variables and insert into ERROR table.Any idea how to do this and please be descriptive.Thanks |
|
|
rharmon
Starting Member
41 Posts |
Posted - 2002-09-03 : 11:39:47
|
| I've been in this corner before. From what I've been able to gather, there's two options. One method would be to add a dynamic properties task for each dts task and add an failure precedence between each dts step and the dynamic properties task to set the global variables. Obviously, this seems very over complicated for such a simple task as error trapping.The second method (and the one I use every day) would be to enable logging on your dts package and read the error information from msdb.dbo.sysdtspackagelog and msdb.dbo.sysdtssteplog. To do this in the DTS designer, make sure no steps are selected (click on the white space on the screen) then go to the package menu and select properties. Then click on the logging tab and set the appropriate connection information for the target server.Hope this helps and let me know how it goes.ROb |
 |
|
|
gchacko
Starting Member
8 Posts |
Posted - 2002-09-03 : 11:50:58
|
| Isn't there a better way out there. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-03 : 12:17:07
|
| Decide on what is in control of your system.You seem to like the idea of dts as a controller (which I think is a mistake but looks like you are finding that out).That controller should be in charge of overall error handling. You are here trying to control an SP from the DTS package and then on error allow the SP to control the dts package - this is always going to cause problems.Better to allow the SP to fail and the DTS package to recognise the error and act accordingly.You could have the SP log the error into a table and the DTS package read it.But then why have a package with 5 SPs - why not call a single SP and allow that to control the processing on the server.You might notice from below that I have come across a lot of systems that have got into trouble by deciding that dts should control everything and even replace stored procs with embedded sql.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
gchacko
Starting Member
8 Posts |
Posted - 2002-09-03 : 17:41:26
|
| Is it possible to call SQL INSERT statement within ActiveX script panel with DTS. |
|
|
|
rharmon
Starting Member
41 Posts |
Posted - 2002-09-03 : 18:21:06
|
| This should do it, you may want to copy this out and past into notepad to clear the auto word wrap. If you've got to do a lot of these, you may want to download SSCodeGen. set gConnection = CreateObject("ADODB.Connection") Set cmd = CreateObject("ADODB.Command") gconnection.connectionstring = "Provider=SQLOLEDB.1;Password=webuser;Persist Security Info=True;User ID=web_user;InitialCatalog=qaswain;Data Source=rharmon1ltp\rob;" cmd.ActiveConnection = gConnection cmd.CommandText = "sp_Insert_Answer" cmd.CommandType = adCmdStoredProc cmd.Parameters.Append cmd.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue) cmd.Parameters.Append cmd.CreateParameter("@Question_Template_ID", adInteger, adParamInput) cmd.Parameters.Append cmd.CreateParameter("@Answer_Text", adVarChar, adParamInput, 500) cmd.Parameters.Append cmd.CreateParameter("@Possible_Score", adInteger, adParamInput) cmd.Parameters.Append cmd.CreateParameter("@Score", adInteger, adParamInput) cmd.Parameters.Append cmd.CreateParameter("@Fatal", adInteger, adParamInput) cmd.Parameters.Append cmd.CreateParameter("@Auto_Comment", adVarChar, adParamInput, 500) cmd.Parameters.Append cmd.CreateParameter("@screen_order", adInteger, adParamInput) cmd.Parameters.Append cmd.CreateParameter("@Deleted_date", adDBTimeStamp, adParamInput) <!-- cmd.Parameters("@Question_Template_ID").Value = 3 cmd.Parameters("@Answer_Text").Value = "some answer" cmd.Parameters("@Possible_Score").Value = 10 cmd.Parameters("@Score").Value = 5 cmd.Parameters("@Fatal").Value = 1 cmd.Parameters("@Auto_Comment").Value = null cmd.Parameters("@screen_order").Value = 1 cmd.Parameters("@Deleted_date").Value = null --> cmd.Execute |
|
|
|
gchacko
Starting Member
8 Posts |
Posted - 2002-09-06 : 12:21:21
|
| Can you please explain the following :How to use the code to INSERT INTO error_log_Table VALUES(@Application,@Step,@RunDate,@ErrorNum) |
|
|
|
|
|
|
|
|
Return values from stored proc to Global variables