Run Once Schedule

In SQL 2005 Reporting Services, on the page that you use to set a schedule
for a subscription, there is an option to run a report once. Is there a way
through the web service interface to invoke this specific schedule? Do you
use the NoRecurrence object?
(The options are: Hour, Day, Weekly, Monthly, Once)The quick answer is YES, everything that is done in the ReportManager
uses the web service interface. I would check the BOL for the specific
details.
"Steven Molen" <StevenMolen@.discussions.microsoft.com> wrote in message
news:7B87FBF2-5CE0-4298-8DD7-7FA9A586FD9D@.microsoft.com:
> In SQL 2005 Reporting Services, on the page that you use to set a schedule
> for a subscription, there is an option to run a report once. Is there a way
> through the web service interface to invoke this specific schedule? Do you
> use the NoRecurrence object?
> (The options are: Hour, Day, Weekly, Monthly, Once)
Bret Updegraff, MCAD,MCSD,MCDBA
Microsoft MVP - SQL Server
Crowe Chizek and Company LLC
President - Indianapolis Professional Association for SQL Server
Join our SQL Server Community http;//www.IndyPASS.org
317.208.2538 - FAX (317.706.2660) -BUpdegraff@.CroweChizek.com|||Excuse the ignorance. Whats the BOL? If you means sql books online then I
have already checked there and I couldn't find it...I wanted to look in the
source for the SubscriptionsProperties.aspx page but it is compiled and I
haven't bothered to run reflector against it.
"Bret Updegraff" wrote:
> The quick answer is YES, everything that is done in the ReportManager
> uses the web service interface. I would check the BOL for the specific
> details.
>
> "Steven Molen" <StevenMolen@.discussions.microsoft.com> wrote in message
> news:7B87FBF2-5CE0-4298-8DD7-7FA9A586FD9D@.microsoft.com:
> > In SQL 2005 Reporting Services, on the page that you use to set a schedule
> > for a subscription, there is an option to run a report once. Is there a way
> > through the web service interface to invoke this specific schedule? Do you
> > use the NoRecurrence object?
> >
> > (The options are: Hour, Day, Weekly, Monthly, Once)
>
> --
> Bret Updegraff, MCAD,MCSD,MCDBA
> Microsoft MVP - SQL Server
> Crowe Chizek and Company LLC
> President - Indianapolis Professional Association for SQL Server
> Join our SQL Server Community http;//www.IndyPASS.org
> 317.208.2538 - FAX (317.706.2660) -BUpdegraff@.CroweChizek.com
>|||OK. The answer wasn't in the BOL. Pfft. The SQL BOL is very light when it
comes to the web service and what it can and can't do.
I got "unlazy" and pulled out my reflector and deconstructed the
SubscriptionProperties page (All the report services pages are compiled
pages). Needless to say, my answer I was looking for wasn't on that page but
in a custom class that they were using to construct the schedule portion of
the subscription.
(Specifically Microsoft.ReportingServices.UI.ScheduleControl within the
ReportingServicesWebUserInterface.dll)
The method that most caught my interest was TaskToScheduleDefinition() since
I was using the scheduledefinition object inherently. The short answer is:
DO NOT set the Schedule.Item and to leave it null. Only set the
StartDateTime on the schedule object.
I tested this theory that I picked up from looking at the disassembled code
and my Subscriber object worked perfectly with the schedule object set as
described above.
Here is the code from the reporting services assembly if your interested.
This is from SQL 2005 of course and only has to do with setting the schedule
object and not the rest of the code needed to set up a full subscription:
internal static ScheduleDefinition TaskToScheduleDefinition(Task task)
{
ScheduleDefinition definition1 = new ScheduleDefinition();
definition1.StartDateTime = task.StartDateTime;
definition1.EndDate = task.EndDate;
definition1.EndDateSpecified = false;
if (task.EndDate != DateTime.MinValue)
{
definition1.EndDateSpecified = true;
}
switch (task.Trigger.RecurrenceType)
{
case RecurrenceType.Once:
{
return definition1;
}
case RecurrenceType.Minutes:
{
MinuteRecurrence recurrence1 = new MinuteRecurrence();
recurrence1.MinutesInterval = ((Minutes)
task.Trigger.TriggerData).MinutesInterval;
definition1.Item = recurrence1;
return definition1;
}
case RecurrenceType.Daily:
{
DailyRecurrence recurrence2 = new DailyRecurrence();
recurrence2.DaysInterval = (int) ((Daily)
task.Trigger.TriggerData).DaysInterval;
definition1.Item = recurrence2;
return definition1;
}
case RecurrenceType.Weekly:
{
WeeklyRecurrence recurrence3 = new WeeklyRecurrence();
Weekly weekly1 = (Weekly) task.Trigger.TriggerData;
recurrence3.DaysOfWeek =ScheduleControl.UintToDaysOfWeek(weekly1.DaysOfWeek);
recurrence3.WeeksInterval = (int) weekly1.WeeksInterval;
recurrence3.WeeksIntervalSpecified = true;
definition1.Item = recurrence3;
return definition1;
}
case RecurrenceType.MonthlyDate:
{
MonthlyRecurrence recurrence4 = new MonthlyRecurrence();
Monthly monthly1 = (Monthly) task.Trigger.TriggerData;
recurrence4.Days =Monthly.GetDayRange(monthly1.DaysOfMonth);
recurrence4.MonthsOfYear =ScheduleControl.UintToMonthsOfYear(monthly1.Months);
definition1.Item = recurrence4;
return definition1;
}
case RecurrenceType.MonthlyDOW:
{
MonthlyDOWRecurrence recurrence5 = new
MonthlyDOWRecurrence();
MonthlyDOW ydow1 = (MonthlyDOW) task.Trigger.TriggerData;
recurrence5.DaysOfWeek =ScheduleControl.UintToDaysOfWeek(ydow1.DaysOfWeek);
recurrence5.MonthsOfYear =ScheduleControl.UintToMonthsOfYear(ydow1.Months);
recurrence5.WhichWeek =ScheduleControl.UintToWhichWeek(ydow1.Week);
recurrence5.WhichWeekSpecified = true;
definition1.Item = recurrence5;
return definition1;
}
}
return definition1;
}
"Steven Molen" wrote:
> Excuse the ignorance. Whats the BOL? If you means sql books online then I
> have already checked there and I couldn't find it...I wanted to look in the
> source for the SubscriptionsProperties.aspx page but it is compiled and I
> haven't bothered to run reflector against it.
> "Bret Updegraff" wrote:
> > The quick answer is YES, everything that is done in the ReportManager
> > uses the web service interface. I would check the BOL for the specific
> > details.
> >
> >
> > "Steven Molen" <StevenMolen@.discussions.microsoft.com> wrote in message
> > news:7B87FBF2-5CE0-4298-8DD7-7FA9A586FD9D@.microsoft.com:
> >
> > > In SQL 2005 Reporting Services, on the page that you use to set a schedule
> > > for a subscription, there is an option to run a report once. Is there a way
> > > through the web service interface to invoke this specific schedule? Do you
> > > use the NoRecurrence object?
> > >
> > > (The options are: Hour, Day, Weekly, Monthly, Once)
> >
> >
> > --
> > Bret Updegraff, MCAD,MCSD,MCDBA
> > Microsoft MVP - SQL Server
> > Crowe Chizek and Company LLC
> > President - Indianapolis Professional Association for SQL Server
> > Join our SQL Server Community http;//www.IndyPASS.org
> > 317.208.2538 - FAX (317.706.2660) -BUpdegraff@.CroweChizek.com
> >
> >

Run DTS from ASP.NET (VB)

I have a DTS package which imports data from a legacy app to SQLServer 2000. Right now I have a page in classic ASP which executes a SP which in turn calls the DTS package. After the user hits the "Update" button on the ASP page they are redirected to a page that says "This process takes several minutes, check back later." The current setup is causing problems because Joe may hit the "Update" button to refresh the data and get an error because the DTS package is already running from when Mary just ran the same process.

I want to rewrite this in .NET (2.0) to accomplish the following:

Eliminate the SPCheck the status of the DTS package and alert the user if it is already running.Show a "Please wait while we process your request" message to the person who started the update while the DTS is running.Automatically show the report when the DTS is done.

I think I can find code in the forum to execute the DTS, but how do check the current status first? I've read about SQLDMO but the Web server and SQLServer are on different machines. Would this still work?

Thanks in advance.

I'm having trouble getting the DTS execute to run asynchronously. All the examples I've found show executing queries asynchronously, but nothing on DTS (there all in C# as well). Here's what I have in my class.

Imports System.IO
Imports System.NetImports System.Net.Mail
Imports System.ThreadingImports System.Runtime.Remoting.Messaging
Imports DTS
Imports DTS.DTSSQLServerStorageFlags

PartialClass Reporting_AmSherReports_rptColStats
Inherits System.Web.UI.Page
'----------------------------
'Remember, if you change the package, these numbers will need to be modified too.
'----------------------------

Private sPkgIDAs String ="{4CFD14F0-12D8-445C-AD98-E0DD784B166A}"
Private sPkgVersionAs String ="{F0CAFA3B-9A52-4780-B0C6-5B86C38BCFC5}"
Protected sMessageAs String =""

Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.Load
If Page.IsPostBackThen

'Show processing message...

Processing.Visible =True
Report.Visible =False
Refresh.Visible =False

Dim stateAs New Object()
Dim beginHandlerAs New BeginEventHandler(AddressOf ExecuteDTS)
Dim endHandlerAs New EndEventHandler(AddressOf DTSComplete)
Dim timeoutHandlerAs New EndEventHandler(AddressOf DTSTimeout)
Dim taskAs New PageAsyncTask(beginHandler, endHandler, timeoutHandler, state)
RegisterAsyncTask(task)
End If
End Sub

Private Function ExecuteDTS(ByVal senderAs Object,ByVal eAs EventArgs,ByVal cbAs AsyncCallback,ByVal stateAs Object)As IAsyncResult
Try
Dim sSourceAs String =""
Dim sDescAs String =""
Dim lErrAs Long
Dim oPKGAs DTS.Package =New DTS.Package
Dim oStepAs DTS.Step

'Load Package

oPKG.LoadFromSQLServer("server","sa","", DTSSQLStgFlag_UseTrustedConnection, , sPkgID, sPkgVersion,"Refresh Stats")

'Execute

oPKG.Execute()

'Get Status and Error Message

For Each oStepIn oPKG.Steps
If oStep.ExecutionResult = DTSStepExecResult.DTSStepExecResult_FailureThen
oStep.GetExecutionErrorInfo(lErr, sSource, sDesc)
sMessage = sMessage &"Step """ & oStep.Name &""" Failed" & vbCrLf & _
vbTab &"Error: " & lErr & vbCrLf & _
vbTab &"Source: " & sSource & vbCrLf & _
vbTab &"Description: " & sDesc & vbCrLf & vbCrLf
End If
Next
Catch exAs Exception
Finally
Processing.Visible =False
Report.Visible =True
Refresh.Visible =False
End Try
End Function

Private Sub DTSComplete(ByVal arAs IAsyncResult)
Processing.Visible =False
Report.Visible =True
Refresh.Visible =True
End Sub

Private Sub DTSTimeout(ByVal arAs IAsyncResult)
'No implementation -- needed for PageAsyncTask

End Sub
End Class

Run a SQL script file from an ASP.NET page

Hi there,

I would like to know how can I run a SQL Server 2000 script file (*.sql) from an ASP.NET page. I don't wanna run it from the SQL Query Analyzer, but run it the page itself, I mean dynamically.

Is there any object, method, or whatever to do it?

Thanks in advanceHmpf.

1: Load file into stroing variable.
2: open connection to server.
3: create SqlCommand object.

and now carefull - the HUGH trick:

4: assign string from file to CommandText property of the command.

and...

5: execute.

That simple, really.|||Hi again,

It works!

Thank you very much!|||you mean MyCommand.Execute("myFile.sql") ?
or you must do a readfile (readAll) first ?
thank you
|||it works for CREATE TABLE but for view or procedure I get

ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]'CREATE VIEW' must be the first statement in a query batch.

do you know why ?
my script >>
CREATE TABLEdbo.Label
(
[id] [int] IDENTITY (1, 1) NOT NULL ,
[value] [varchar] (255) COLLATE French_CI_AS NULL ,
)
ON [PRIMARY]

CREATE VIEW dbo.Label
AS
SELECT value, id
FROM dbo.Label
WHERE (value = '100')

thank you for helping

|||it seems that nobody knows how to run VREATE VIEW or PROCEDURE from sql file
|||You need to put a GO statement after each command.
|||no it doesn't work !
I can run a .sql file with create TABLE but not with create VIEW or PROCEDURE
|||Are you certain of your current error message? In the script youhave provided you are calling both objects you are attempting to create"Label", which is not possible. Also, you will receive an errorif the object "Label" already exists in your database when you run thescript.
|||now it works !!
thank you
but how can I insert with a procedure from queryAnalyser ?
If I run
CREATE PROCEDURE [dbo].Insert_all AS
INSERT INTO Config (nom, valeur, valide) VALUES ('langDef','0',1)
INSERT INTO Config (nom, valeur, valide) VALUES ('Login','1',1)
INSERT INTO Config (nom, valeur, valide) VALUES ('Open','1',1)
GO
nothing is inserted in my database

|||You have to EXECUTE the stored procedure in order for the commands it contains to be performed.

EXECUTE dbo.Insert_all

|||it works fine !!!:)))
thanks a ton
|||I can run it from QueryAnalyser but from ASP NET ...first iof all : I must remove the GO
then it works for CREATE TABLE
but if i add this statement >>
CREATE PROCEDURE [dbo].dt_Component
AS
DELETE FROM Component
it doesn't work ... even the tables are not created
for any pROCEDURE or VIEW it doesn't work at all


|||Quentin, it is impossible to help you when you only tell us "it doesn'twork". Certainly there must be associated error messages. Withoutthose error messages and your vague descriptions it is extremelyfrustrating to try to help you. And at this point you havesupplied us with so many varying pieces of code and scenarios that Ihave no idea what you are asking.
Could you please try again, stating exactly what code you are running,from where, and what the behavior you are seeing is, along with anyerror messages?
|||I am trying a very simple .sql file
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Config]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Config]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_Config]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_Config]
CREATE TABLE [dbo].[Config] (
[id_Config] [int] IDENTITY (1, 1) NOT NULL ,
[nom] [varchar] (50) COLLATE French_CI_AS NULL ,
[valeur] [varchar] (255) COLLATE French_CI_AS NULL ,
[valide] [bit] NULL
) ON [PRIMARY]
but if I add >>
CREATE PROCEDURE [dbo].dt_Config
AS
DELETE FROM Config

it does not work ... it is just impossible to create view or procedure from ASP NET
and if I add the GO between each command it bugs at the first GO