Launch Subscription via VB Code

I have created a subscription via Report Manager & would like to launch it
via vb code or rs script on any given date or time. That is, even though the
subcription may have a set date/time, via CODe I would launch it & modified
its date & time as necessary.
--
EdgarNot particularly elegant, but you can do this in the following way:
1) create a shared schedule that fires once, in the past. Save the Schedule
ID you get back
2) create a subscription that is triggered by this shared schedule.
3) use some code to call the FireEvent method and submit the
TimedSubscription event as the EventType and the schedule ID from (1) as the
MatchData.
-Lukasz
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"mresanchez" <mresanchez@.donot-spam.com> wrote in message
news:045CCA4A-B712-489D-8609-068642154128@.microsoft.com...
>I have created a subscription via Report Manager & would like to launch it
> via vb code or rs script on any given date or time. That is, even though
> the
> subcription may have a set date/time, via CODe I would launch it &
> modified
> its date & time as necessary.
> --
> Edgar|||Lukasz,
I tried your solution & everything works fine except that I get an error
saying that the subscription cannot be found. Even though the subscription
ID is listed within SQL Server. Below is the output I get with the error. I
also Echo the subscription ID & the line so I can see the ID which later I
find in the subscriptions table in SQL Svr. Is there a particular way to
format the subscriptionID in order for RS to see it?
The Current Subscription is: 0d49d301-ecd8-43fc-8319-21ec19e82f77
==========================================System.Web.Services.Protocols.SoapException:
System.Web.Services.Protocols.SoapException: The subsc
ription cannot be found. The subscription identifier that is provided to an
operation cannot be located in the report server database. -->
Microsoft.ReportingServices.Diagnostics.Utilities.Subscrip
tionNotFoundException: The subscription cannot be found. The subscription
identifier that is provid
ed to an operation cannot be located in the report server database.
-- End of inner exception stack trace --
at
Microsoft.ReportingServices.WebServer.ReportingService.GetSubscriptionProperties(String Subsc
riptionID, String& Owner, ExtensionSettings& ExtensionSettings, String&
Description, ActiveState& A
ctive, String& Status, String& EventType, String& MatchData,
ParameterValue[]& Parameters)
at
System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message,
WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String
methodName, Object[] param
eters)
at
Microsoft.SqlServer.ReportingServices.ReportingService.GetSubscriptionProperties(String Subsc
riptionID, ExtensionSettings& ExtensionSettings, String& Description,
ActiveState& Active, String&
Status, String& EventType, String& MatchData, ParameterValue[]& Parameters)
at ____ScriptClass.Main()
The command completed successfullysql

Latin1_General_BIN vs Latin1_General_BIN2 in Script

Hi John
No, with Latin1_General_CI_AS I not have problems, but I dont test with
others Collations and the problem is in db not in the tables.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Does the same thing happen with Latin1_General_CI_AS? Does this happen with
> a subset of the tables etc?
> John
> "sqlextreme" wrote:
Hi
How are you generating this script?
John
"sqlextreme" wrote:
[vbcol=seagreen]
> Hi John
> No, with Latin1_General_CI_AS I not have problems, but I dont test with
> others Collations and the problem is in db not in the tables.
> "John Bell" wrote:
|||In Management Studio-> Select de Name db -> Tasks -> generate scripts -> db
and etc. for all objects
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> How are you generating this script?
> John
> "sqlextreme" wrote:
|||Hi
I have no problems generating a script for the following database:
CREATE DATABASE BIN_DB
COLLATE LATIN1_GENERAL_BIN2
USE BIN_DB
GO
CREATE TABLE tbl1 ( id int not null identity, col1 varchar(10) )
GO
CREATE TABLE [dbo].[tbl2](
[id] [int] IDENTITY(1,1) NOT NULL,
[col1] [varchar](10) COLLATE LATIN1_GENERAL_BIN2 NULL
)
GO
USE TEMPDB
GO
John
"sqlextreme" wrote:
[vbcol=seagreen]
> In Management Studio-> Select de Name db -> Tasks -> generate scripts -> db
> and etc. for all objects
> "John Bell" wrote:
|||Is strange, if you make script for all objects this is the result:
Generate Script Progress
- Determining objects in database 'BIN_DB' that will be scripted. (Error)
Messages
* Exception has been thrown by the target of an invocation. (mscorlib)
ADDITIONAL INFORMATION:
There is already an open DataReader associated with this Command which must
be closed first. (System.Data)
but, if you make script by next and next for all objects this is the result:
Generate Script Progress
- Determining objects in database 'BIN_DB' that will be scripted. (Success)
- dbo.tbl1 (Success)
- dbo.tbl2 (Success)
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I have no problems generating a script for the following database:
> CREATE DATABASE BIN_DB
> COLLATE LATIN1_GENERAL_BIN2
> USE BIN_DB
> GO
> CREATE TABLE tbl1 ( id int not null identity, col1 varchar(10) )
> GO
> CREATE TABLE [dbo].[tbl2](
> [id] [int] IDENTITY(1,1) NOT NULL,
> [col1] [varchar](10) COLLATE LATIN1_GENERAL_BIN2 NULL
> )
> GO
> USE TEMPDB
> GO
> John
> "sqlextreme" wrote:
|||Hi
It does seem like this is a bug, selecting "Script all objects in the
selected database" will also produce the error on my system. There seems to
be a few similar errors logged on connect such as
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127175
although that is closed and supposed to be fixed before the version I am
using (9.00.2047.00).
John
"sqlextreme" wrote:
[vbcol=seagreen]
> Is strange, if you make script for all objects this is the result:
> Generate Script Progress
> - Determining objects in database 'BIN_DB' that will be scripted. (Error)
> Messages
> * Exception has been thrown by the target of an invocation. (mscorlib)
> --
> ADDITIONAL INFORMATION:
> There is already an open DataReader associated with this Command which must
> be closed first. (System.Data)
> but, if you make script by next and next for all objects this is the result:
> Generate Script Progress
> - Determining objects in database 'BIN_DB' that will be scripted. (Success)
> - dbo.tbl1 (Success)
> - dbo.tbl2 (Success)
>
>
>
>
> "John Bell" wrote:
|||..I installed a new instance of SQL Server 2005 with Latin1_General_BIN2 and
later I created a db with Latin1_General_BIN2 and I could not create script.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> It does seem like this is a bug, selecting "Script all objects in the
> selected database" will also produce the error on my system. There seems to
> be a few similar errors logged on connect such as
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127175
> although that is closed and supposed to be fixed before the version I am
> using (9.00.2047.00).
> John
> "sqlextreme" wrote:
|||Hi
I have logged this at
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=248724 where you can vote for the issue.
John
"sqlextreme" wrote:
[vbcol=seagreen]
> .I installed a new instance of SQL Server 2005 with Latin1_General_BIN2 and
> later I created a db with Latin1_General_BIN2 and I could not create script.
> "John Bell" wrote:
|||Hi,
Ok, thank for your help.
Cristián
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I have logged this at
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=248724 where you can vote for the issue.
> John
> "sqlextreme" wrote:
sql

latest order date

Hi,
I have an order table which contains the following fielde: 1). orderid (this
is the order number 2). clientid 3). orderdate.
I need to script so that I can find out those clientids which do not have
place an order for at least 90 days.
Can you tell me how to program it?Read the documentation. You want to select clientids which are NOT IN a
subquery that selects all clients that have ordered in the last 90 days. You
could also use a join where max order date is less than 90 days ago.
RR
"qjlee" <qjlee@.discussions.microsoft.com> wrote in message
news:B84E46A2-9291-4D17-A625-3DBADD9746B1@.microsoft.com...
> Hi,
> I have an order table which contains the following fielde: 1). orderid
(this
> is the order number 2). clientid 3). orderdate.
> I need to script so that I can find out those clientids which do not have
> place an order for at least 90 days.
> Can you tell me how to program it?
>
>|||Something like this should give you the client and their last order date.
declare @.DaysSinceOrder as numeric
set @.DaysSinceOrder = 90
select
clientid ,
max(orderdate)
from "YourTableHere"
group by clientid
having max(orderdate) < getdate()-@.DaysSinceOrder
"qjlee" wrote:

> Hi,
> I have an order table which contains the following fielde: 1). orderid (th
is
> is the order number 2). clientid 3). orderdate.
> I need to script so that I can find out those clientids which do not have
> place an order for at least 90 days.
> Can you tell me how to program it?
>
>|||Please post DDL, sample data and expected results
(http://www.aspfaq.com/etiquette.asp?id=5006 )
Since you have a ClientID column in your Orders table, I am guessing that
you have a Clients table somewhere. Here is a complete guess:
SELECT ClientID, ClientName
FROM Clients
WHERE ClientID NOT IN (SELECT ClientID FROM Orders WHERE DATEDIFF(d,
OrderDate, CURRENT_TIMESTAMP) <= 90)
"qjlee" <qjlee@.discussions.microsoft.com> wrote in message
news:B84E46A2-9291-4D17-A625-3DBADD9746B1@.microsoft.com...
> Hi,
> I have an order table which contains the following fielde: 1). orderid
> (this
> is the order number 2). clientid 3). orderdate.
> I need to script so that I can find out those clientids which do not have
> place an order for at least 90 days.
> Can you tell me how to program it?
>
>

latest entry per distinct ID

This summary is not available. Please click here to view the post.

latest entry per distinct ID

This summary is not available. Please click here to view the post.

latest entry per distinct ID

This summary is not available. Please click here to view the post.

LastNonEmpty vs MDX Script solve order

Hi,

I'm using the Account Intelligence and some account members use the aggregation function LastNonEmpty.

Witch evaluates before the LastNonEmpty function or my MDX Script that I put in the calculation inside the cube?

If my MDX Script change some values only in the the leaves of the time dimension in one account (taht uses LastNonEmpty function). What will be evaluate first? My MDX Script and after the LastNonEmpty function or the oposite?

Handerson

LastNonEmpty is applied to the members above the leaves of Time dimension by aggregation the leaves through LastNonEmpty. Therefore if you will apply some calculation to the leaves of Time - it will be done before the LastNonEmpty aggregation.sql

LastNonEmpty + Time PrevMember don't aggregate correctly

I'm using the Account Intelligence and when I have a balance account modified with a mdx script in cube calculation using the Time prevmember it don't aggregates correctly. I think that it's a solver order problem.

I have a account dimension like that:

Balance (balance account)

Asset (balance account)

Computers (balance account)

Result (flow account)

Computers Investments (flow account)

In the cube calculation I have these formula:

Computers = (Computers, Time.Currentmember.Prevmember) + Computer Investments;

When I query these data I receive these:

2007 Jan Fev Mar

Balance 0 100 50 0

Asset 0 100 50 0

Computers 150 100 150 150

Result 150 100 50 0

Computers Investments 150 100 50 0

It's not aggregatin the (Computers, Time.Currentmember.Prevmember) in account hierarchy, any Idea why is that?

I'm using SQL Server 2005 Enterprise SP1.

I was using the follow Time Hierarchy:

Year

Last Row Got Stuck

Hello,

I have a small table to manage orders in my company. When a new order is entered, the script makes use of the last row of the table to find out the last order, increments it and creates the new order number. The problem is, a few days ago the last row got stuck. New insertions to the table all got the same order number and are placed above the last row. Anybody has any idea what's going on?hey
do you have multiple concurrent applciations accesing the table?
any reason you are doing the incremting 'manually' instead of using an auto-inc int?
des|||Hello Des,

Below is the protion of the ASP code that assigns the ordernumber. It is in the form "TPD-mmm-nnn" where mmm is the mont (like 002 for Feb) and nnn is the order number (like 001,002,etc). The if clause tries to reset the order number back to 001 if the month starts anew.

...
set rs = Server.CreateObject("ADODB.recordset")
rs.CursorType = 3
selectstr="SELECT * FROM konuttekliflist"
rs.open selectstr, conn
rs.movelast
tekno=rs.fields("teklifno")
tekay=mid(tekno,5,3)
teklifindex=right("00" & right(tekno,4)+1,3)
monthnow=right("00" & month(now()),3)
if tekay=monthnow then
teklifno1="TPD-" & monthnow & "-" & teklifindex
else
teklifno1="TPD-" & monthnow & "-001"
end if
sql="INSERT INTO konuttekliflist ("
...

And yes, the application is used concurrently by 4 people.

Thanks to your interest and help.|||So ID is generated with datenow...are the four apps runnning on sep machines to db server & eachother? are all their dates/times
in perfect sync? otherwise this could cause problems.

If two orders come in at the same time, they could potentially conflict?

i see you select all into a recordset - this could cause locks that conflict with your other apps. why not select(max) substring(mmm)+subsrting(nnn) into local variable, instead of iterating through recordset?
How does it cross over the year?

how about generating the ID in sql with a computed column, using its own date and increment, then at least you dont have four different things competing for the next value? ie.gen id's centrally

des

Large report.

My SQL script behind a report produces, say, 10,000 records or more. I
need to show,
at first, only 1st page and - this is important - to pull data from
server only for this 1st page.
In order to avoid performance bottleneck I need to avoid pulling all
data from server! When user clicks next page on client, the client
should extract data from server.
Is there a standard solution for this in SQL Sertver 2000 Reporting
Services?
It looks like a common situation, but I did not find a positive answer
and HOWTO in this group.
StanYou do not have control over this. There is a good reason why. How is
reporting services supposed to apply groups without the data?
I suggest you look at the concept of drill through using jump to report (or
jump to URL).
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/e746e8e2-8af9-4597-8476-18562a92015e.htm
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<smalevanny@.hotmail.com> wrote in message
news:1142338498.985075.219970@.u72g2000cwu.googlegroups.com...
> My SQL script behind a report produces, say, 10,000 records or more. I
> need to show,
> at first, only 1st page and - this is important - to pull data from
> server only for this 1st page.
> In order to avoid performance bottleneck I need to avoid pulling all
> data from server! When user clicks next page on client, the client
> should extract data from server.
> Is there a standard solution for this in SQL Sertver 2000 Reporting
> Services?
> It looks like a common situation, but I did not find a positive answer
> and HOWTO in this group.
> Stan
>