May 25, 2006 at 2:13 pm
i am using a ColdFusion Page to execute a stored procedure on our sql server which runs a DTS package. the cf template allows them to upload a text file to the server for import to a sql table via the DTS package. the problem is that if the DTS package fails - it does not notify the user running the ColdFusion template. is there any way to GET the DTS Package or stored procedure to send an error back to the CF template so i can display a message on the page?
May 25, 2006 at 2:18 pm
You could add a failure constraint to the last step in your package. If it fails you package fails, and if something else fails before that your package fails... You could for example update a table or something that the last step in your sproc checks and then send that data back to your CF page. Use some sort of check like <cfif @myvar isdefined> send you error message... </cfif> or you could perhaps use a try catch block or something.
May 25, 2006 at 2:24 pm
sounds interesting but i have no idea how to do that.
let me just see if i have the concept right. you are saying that i add a command to my stored procedure to see if the DTS package failed (i have no idea how to do that), and if it failed, write something out to a table.
then in my cf template i run a query which checks the table to see if the DTS package ran and if not, output the error?
May 26, 2006 at 7:00 am
I'd just go with the cftry/cfcatch error handling. Hope that this helps. Thanks.
Chris
May 26, 2006 at 7:18 am
unfortunately i dotn think the dctry/cfcatch would work for me because the problem is that the cf template sends the command to the sql server to run the stored proc which runs the dts package, and ad that point the sql server does its thing, and unless we can get an error back to the cf template from the sql server, the cftry /cfcatch would not catch anything - as far as the cf template knows, there was no error, unless it gets something from the SQL server, right?
May 26, 2006 at 7:18 am
Yeah, All I'm saying is that you should have your Sproc return a value 0 for everythign went ok, 1 for a problem or whatever you choose. Then in your output block check it either with cftry/cfcatch or cfif to decide if you need to display the error to the user. If you want to get really creative you could turn on logging for the DTS package and parse the txt file it would create and send all of the error info back to your CF page so that you could display the error messages and such.
May 26, 2006 at 8:28 am
i have no idea how to do that - could you give an example?
May 26, 2006 at 9:03 am
Can I ask a Silly question? How's come you are using DTS to load your data from the text file and not CF? You could use a txt file datasource and import via that into your SQL database. That way you can control the operation from Cf, you'd have to trade off some longer load times, but it might be worth while depending on the size of your data.
If that's not an option. Check out http://www.databasejournal.com/features/mssql/article.php/1459181
It deals in depth with calling DTS jobs from Sprocs and how to get your error messages back and such. You could just make you Sproc sit and wait while the DTS does it's bit. Look at page 3 in the examples he shows how to store the completion of each step in your package in a temp table. The final select statement in your sproc should return a recordset to CF just like and other sproc that returns a recordset and your could then deal with it in a try/catch OR cfif way.
Clear as mud?
Hope this helps/
-Luke.
May 26, 2006 at 9:59 am
hey thanks for the reply. originally i was using CF to control the import but some of the file we need to import are quite large on occasion. and it choked on those larger files. thats why we needed to go to a dts package. i WISH we coudl control it all in CF that woudl make this a whole lot easier.
i will take a look at the link and see if i can make sense of it.
thanks.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply