How to Schedule Reports and How to use File Share Suscription

I tried to schedule reports using File Share Subscription as specified in MSDN, But was not able to schedule the report.Can you give me detail of the nature of your issue?

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.

How to retrieve identity in stored procedure for updatable subscription??

We have transactional replication setup between SQL Server 2000 on Win2003
and MSDE on Win2003 (both SP3a). SQL Server is the publisher and is pushing
an updatable subscription (queued updating) to the MSDE instance.
We are trying to insert master and detail rows into a pair of tables via a
stored proc. After inserting the master row, doing @.@.IDENTITY to retrieve
the ID for the child row's foreign key returns NULL, causing the child row
insert to fail. Looking at the table after running the proc, the parent row
does have a valid identity value.
Any suggestions on how to make this work?
Thanks,
Steve Klein
Steve,
I'd be interested in what DBCC CHECKIDENT (tablename) returns (on the
publisher and the subscriber).
Also, presumably this insert was done on the subscriber?
Do you have just queued or immediate updating subscribers with queued
failover?
Are there any other triggers on the table (apart from the replication ones
if we are talking about the subscriber)?
Regards,
Paul Ibison
|||I have tried to repro this and it does work. Can you post the problem proc
for us?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Steve Klein" <sklein@.singular.com> wrote in message
news:10hbbbf92amjt6a@.corp.supernews.com...
> We have transactional replication setup between SQL Server 2000 on Win2003
> and MSDE on Win2003 (both SP3a). SQL Server is the publisher and is
pushing
> an updatable subscription (queued updating) to the MSDE instance.
> We are trying to insert master and detail rows into a pair of tables via a
> stored proc. After inserting the master row, doing @.@.IDENTITY to retrieve
> the ID for the child row's foreign key returns NULL, causing the child row
> insert to fail. Looking at the table after running the proc, the parent
row
> does have a valid identity value.
> Any suggestions on how to make this work?
> Thanks,
> Steve Klein
>
|||Paul--
My apologies for not sending the following reply to the group. That was my
goal...
Paul--
Thanks for the response. DBCC CHECKIDENT shows no problems (publisher
and subscriber).
We don't have triggers (other than those for replication) on the tables and
there are not any other transactions affecting the transaction in our
distributed database. The test insert was done via a stored procedure on
the subscriber. This
stored procedure does the following:
1) Insert into table A
2) set @.x = @.@.IDENTITY
3) Insert into table B (using @.x as foreign key back to table A)
We have replication configured for queued updating.
Further info -- if we use IDENT_CURRENT('table_name') instead of
@.@.IDENTITY,
we do get an ID back, but IDENT_CURRENT has (claimed) global scope.
--Steve
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uTduWcTfEHA.2848@.TK2MSFTNGP10.phx.gbl...
> Steve,
> I'd be interested in what DBCC CHECKIDENT (tablename) returns (on the
> publisher and the subscriber).
> Also, presumably this insert was done on the subscriber?
> Do you have just queued or immediate updating subscribers with queued
> failover?
> Are there any other triggers on the table (apart from the replication ones
> if we are talking about the subscriber)?
> Regards,
> Paul Ibison
>
|||Hilary--
I'll be able to post the procedure later today. Thanks.
--Steve
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23hrM6ggfEHA.2028@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> I have tried to repro this and it does work. Can you post the problem proc
> for us?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Steve Klein" <sklein@.singular.com> wrote in message
> news:10hbbbf92amjt6a@.corp.supernews.com...
Win2003[vbcol=seagreen]
> pushing
a[vbcol=seagreen]
retrieve[vbcol=seagreen]
row
> row
>
|||Steve,
there's something strange here, but I'd check to see if Scope_Identity()
also returns NULL.
Finally, I'll keep an eye on your thread with Hilary where you mentioned
posting up the script, because I'd also like to try to reproduce this.
Regards,
Paul Ibison
|||Paul--
Thanks very much for your interest. I (sheepishly) need to report that the
problem was a form of user error. The table causing the problem had a text
column. The way that the error management was implemented was evidently
causing the @.@.IDENTITY to return NULL.
Why Ident_Current() worked is odd, but since removing the text column caused
the @.@.IDENTITY to work, we are not too inclined to look much further
By the way, we have changed to Scope_Identity() rather than @.@.IDENTITY.
Thanks again.
--Steve
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uGr2g3qfEHA.3148@.TK2MSFTNGP10.phx.gbl...
> Steve,
> there's something strange here, but I'd check to see if Scope_Identity()
> also returns NULL.
> Finally, I'll keep an eye on your thread with Hilary where you mentioned
> posting up the script, because I'd also like to try to reproduce this.
> Regards,
> Paul Ibison
>
|||Hilary--
Thanks for your help. As I indicated in the response to Paul's last post,
this was a stored procedure error, which I would label "user error"... Check
there for more details.
--Steve
"Steve Klein" <sklein@.singular.com> wrote in message
news:10hg0lla3jcmu8f@.corp.supernews.com...[vbcol=seagreen]
> Hilary--
> I'll be able to post the procedure later today. Thanks.
> --Steve
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:%23hrM6ggfEHA.2028@.tk2msftngp13.phx.gbl...
proc[vbcol=seagreen]
> Win2003
via[vbcol=seagreen]
> a
> retrieve
> row
parent
>
|||Steve,
many thanks for the update.
Cheers,
Paul