How to retrieve the T-SQL for a particular subscription?

In SQL 2005 NS, I have created a custom subscription management interface that allows for condition actions. I can currently create, update and delete subscriptions without a problem.

However, I would like to be able to retrieve the T-SQL that will be run when the event is fired to test it for correct syntax etc. I am hoping to notify the user of any possible issues as soon as the subscription is created as opposed to waiting for an error when the event fires. Since many matching fields are simply text entries, the user may enter some data values that cause the matching statement to fail and I would like to catch that error and notify the user at subscription creation.

To do this, I plan to create the subscription with a "disabled" status. The next step would be to retrieve and run the actual T-SQL that will be executed when an event is fired. If there are no errors then I will update the subscription to "enabled".

I cannot seem to find a class member or stored procedure that returns the resulting T-SQL for a single subscription.

Any help would be appreciated.

Thanks

Maybe, I didn't understand exactly what you are trying to accomplish, but as far as I can see, you do not need to retrieve T-SQL for a subscription creation. Instead, you can just do your validations in your SMI class. And you can maximize your GUI usage for validations.

For example, use as many drop-down menus or listboxes as possible instead of combo boxes, textboxes, etc. Let users choose the predefined values.

Also, say, for example, that there is a field in your subscription class which is a DateTime value. Don't let the user enter the date. Give him a Calendar or DateTimePicker controls.

If the user still has to enter some values manually (such as money values), do validations in GUI before calling a SMI method, such as

try {

double amount = Convert.ToDouble(textBox1.Text);

// invoke SMI method for creating or modifying the subscription

} catch (Exception ex) { // show error message to the user }

|||

Thank you for your response.

I am already performing these types of validations and using dropdowns/calendars where appropriate.

However, I also wanted to take it a step further in case something is missed in the validations. The bottom line is; you never know what a user is going to enter in a textbox.

I would like to retrieve the actual SQL that will be run for an individual subscription and test it.