how to get text (html) from "Render" method

currently i'm getting a stream back and am using [see code at end] to convert
it
what i get back on the screen is a bunch of crap like this
MIME-Version: 1.0 Content-Type: multipart/related;
boundary="--=_NextPart_01C35DB7.4B204430" X-MSSQLRS-ProducerVersion:
V8.00.878.00 This is a multi-part message in MIME format.
--=_NextPart_01C35DB7.4B204430 Content-ID: Content-Disposition: inline;
filename="DASH0014" Content-Type: text/html; name="DASH0014"; charset="utf-8"
Content-Transfer-Encoding: base64
PCFET0NUWVBFIEhUTUwgUFVCTElDICItLy9XM0MvL0RURCBIVE1MIDQuMDEgVHJhbnNpdGlvbmFs
Ly9FTiI+DQo8aHRtbD4NCjxoZWFkPg0KPHRpdGxlPg0KREFTSDAwMTQNCjwvdGl0bGU........................etc
instead of what i was expecting - a nicely formed html doc
is it because i am converting the stream?
is there a better way of handling the stream?
am i using the wrong encoding?
thanks
a.
--CODE START
Dim result As Byte() = Nothing
...
result = rs.Render(Report, Format, HistoryID, DeviceInfo,
Parameters, Credentials, _
ShowHideToggle, Encoding, MIMEType, ParametersUsed, Warnings,
StreamIDs)
Dim star As String
Dim ec As System.Text.Encoding = System.Text.Encoding.UTF8
star = ec.GetString(result)
Message.InnerHtml = star
--CODE ENDok so i set this and it works
Dim Format As String = "HTML4.0"
(it was "MHTML")
but now i have a new problem
"adolf garlic" wrote:
> currently i'm getting a stream back and am using [see code at end] to convert
> it
> what i get back on the screen is a bunch of crap like this
> MIME-Version: 1.0 Content-Type: multipart/related;
> boundary="--=_NextPart_01C35DB7.4B204430" X-MSSQLRS-ProducerVersion:
> V8.00.878.00 This is a multi-part message in MIME format.
> --=_NextPart_01C35DB7.4B204430 Content-ID: Content-Disposition: inline;
> filename="DASH0014" Content-Type: text/html; name="DASH0014"; charset="utf-8"
> Content-Transfer-Encoding: base64
> PCFET0NUWVBFIEhUTUwgUFVCTElDICItLy9XM0MvL0RURCBIVE1MIDQuMDEgVHJhbnNpdGlvbmFs
> Ly9FTiI+DQo8aHRtbD4NCjxoZWFkPg0KPHRpdGxlPg0KREFTSDAwMTQNCjwvdGl0bGU........................etc
> instead of what i was expecting - a nicely formed html doc
> is it because i am converting the stream?
> is there a better way of handling the stream?
> am i using the wrong encoding?
> thanks
> a.
>
> --CODE START
> Dim result As Byte() = Nothing
> ...
> result = rs.Render(Report, Format, HistoryID, DeviceInfo,
> Parameters, Credentials, _
> ShowHideToggle, Encoding, MIMEType, ParametersUsed, Warnings,
> StreamIDs)
> Dim star As String
> Dim ec As System.Text.Encoding = System.Text.Encoding.UTF8
> star = ec.GetString(result)
> Message.InnerHtml = star
> --CODE END
>sql

How to get stored procedure to ignore an @@error.

I'm trying to pass back to the calling program, the value of @.@.error from a
stored procedure. The stored procedure seems to work properly, but the
calling program diverts program flow to the "catch" portion of the try/catch
section since it knows an error occured. I'd like for it to just continue on
as if an error didn't occur, and if it wants to check for an error, it could
test the @.err output parameter.
I pushed the button twice to generate a duplicate key error on the 2nd push.
Here's the stored procedure I'm using:
----
-- Insert a single record into Categories3
----
CREATE PROC pr_Categories3_Insert
@.CategoryID int,
@.CategoryName nvarchar(30),
@.Description ntext = NULL,
@.Picture image = NULL,
@.err INT OUT
AS
SET NOCOUNT ON
SET @.err = 0
INSERT Categories2(CategoryID, CategoryName, Description, Picture)
VALUES (@.CategoryID, @.CategoryName, @.Description, @.Picture)
SELECT @.err = @.@.error
RETURN
GO
----
--
And here's the calling program that sets up the parameter list and calls it
using the data access blocks.
private void button3_Click(object sender, System.EventArgs e)
{
// SqlConnection that will be used to execute the sql commands
SqlConnection connection = null;
try
{
try
{
connection = GetConnection(txtConnectionString.Text);
}
catch
{
MessageBox.Show("The connection with the database can′t be established",
"Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
using (SqlTransaction trans = connection.BeginTransaction())
{
// Establish command parameters
SqlParameter param_CategoryID =
new SqlParameter("@.CategoryID", SqlDbType.Int);
param_CategoryID.Value = 1111;
SqlParameter param_CategoryName =
new SqlParameter("@.CategoryName", SqlDbType.Char, 10);
param_CategoryName.Value = "Test 1";
SqlParameter param_Description =
new SqlParameter("@.Description", SqlDbType.Char, 50);
param_Description.Value = "ABCD";
SqlParameter param_Picture =
new SqlParameter("@.Picture", SqlDbType.Image);
param_Picture.Value = null;
// @.err
SqlParameter param_err =
new SqlParameter("@.err", SqlDbType.Int);
param_err.Value = null;
param_err.Direction = ParameterDirection.Output;
try
{
// Call ExecuteNonQuery static method of SqlHelper class
// We pass in SqlTransaction object, command type, stored procedure
name, and a comma delimited list of SqlParameters
int @.RC = 0;
// Perform the address Insert operation
@.RC = SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure,
"prWS_Categories2_Insert",
param_CategoryID,
param_CategoryName,
param_Description,
param_Picture,
param_err);
int @.err = (int) param_err.Value;
if (@.err != 0)
{
trans.Rollback();
txtResults.Text = "Transfer Error, ID = " + param_CategoryID.Value + ";
@.RC=" + @.RC + "; paramErr = " + param_err.Value;
}
else
{
trans.Commit();
txtResults.Text = "Transfer Completed, ID = " + param_CategoryID.Value + ";
@.RC=" + @.RC + "; paramErr = " + param_err.Value;
}
}
catch (Exception ex)
{
// throw exception
trans.Rollback();
txtResults.Text = "Transfer Error";
throw ex;
}
}
}
catch(Exception ex)
{
string errMessage = "";
for( Exception tempException = ex; tempException != null ; tempException =
tempException.InnerException )
{
errMessage += tempException.Message + Environment.NewLine +
Environment.NewLine;
}
MessageBox.Show( string.Format( "There are some problems while trying to
use the Data Access Application block, please check the following error
messages: {0}"
+ Environment.NewLine + "This test requires some modifications to the
Northwind database. Please make sure the database has been initialized using
the SetUpDataBase.bat database script, or from the Install Quickstart optio
n
on the Start menu.", errMessage ),
"Application error", MessageBoxButtons.OK, MessageBoxIcon.Error );
}
finally
{
if(connection != null)
connection.Dispose();
}
}
}"Vern" <Vern@.discussions.microsoft.com> wrote in message
news:462AF1BE-1B3E-40D2-BCD8-1A4FA853772A@.microsoft.com...
> I'm trying to pass back to the calling program, the value of @.@.error from
a
> stored procedure. The stored procedure seems to work properly, but the
> calling program diverts program flow to the "catch" portion of the
try/catch
> section since it knows an error occured. I'd like for it to just continue
on
> as if an error didn't occur, and if it wants to check for an error, it
could
> test the @.err output parameter.
> I pushed the button twice to generate a duplicate key error on the 2nd
push.
> Here's the stored procedure I'm using:
> ----
--
> -- Insert a single record into Categories3
> ----
--
> CREATE PROC pr_Categories3_Insert
> @.CategoryID int,
> @.CategoryName nvarchar(30),
> @.Description ntext = NULL,
> @.Picture image = NULL,
> @.err INT OUT
> AS
> SET NOCOUNT ON
> SET @.err = 0
> INSERT Categories2(CategoryID, CategoryName, Description, Picture)
> VALUES (@.CategoryID, @.CategoryName, @.Description, @.Picture)
> SELECT @.err = @.@.error
> RETURN
> GO
> ----
--
> And here's the calling program that sets up the parameter list and calls
it
> using the data access blocks.
> private void button3_Click(object sender, System.EventArgs e)
> {
> // SqlConnection that will be used to execute the sql commands
> SqlConnection connection = null;
> try
> {
> try
> {
> connection = GetConnection(txtConnectionString.Text);
> }
> catch
> {
> MessageBox.Show("The connection with the database cant be established",
> "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
> return;
> }
> using (SqlTransaction trans = connection.BeginTransaction())
> {
> // Establish command parameters
> SqlParameter param_CategoryID =
> new SqlParameter("@.CategoryID", SqlDbType.Int);
> param_CategoryID.Value = 1111;
> SqlParameter param_CategoryName =
> new SqlParameter("@.CategoryName", SqlDbType.Char, 10);
> param_CategoryName.Value = "Test 1";
> SqlParameter param_Description =
> new SqlParameter("@.Description", SqlDbType.Char, 50);
> param_Description.Value = "ABCD";
> SqlParameter param_Picture =
> new SqlParameter("@.Picture", SqlDbType.Image);
> param_Picture.Value = null;
> // @.err
> SqlParameter param_err =
> new SqlParameter("@.err", SqlDbType.Int);
> param_err.Value = null;
> param_err.Direction = ParameterDirection.Output;
>
> try
> {
> // Call ExecuteNonQuery static method of SqlHelper class
> // We pass in SqlTransaction object, command type, stored procedure
> name, and a comma delimited list of SqlParameters
> int @.RC = 0;
> // Perform the address Insert operation
> @.RC = SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure,
> "prWS_Categories2_Insert",
> param_CategoryID,
> param_CategoryName,
> param_Description,
> param_Picture,
> param_err);
> int @.err = (int) param_err.Value;
> if (@.err != 0)
> {
> trans.Rollback();
> txtResults.Text = "Transfer Error, ID = " + param_CategoryID.Value + ";
> @.RC=" + @.RC + "; paramErr = " + param_err.Value;
> }
> else
> {
> trans.Commit();
> txtResults.Text = "Transfer Completed, ID = " + param_CategoryID.Value +
";
> @.RC=" + @.RC + "; paramErr = " + param_err.Value;
> }
> }
> catch (Exception ex)
> {
> // throw exception
> trans.Rollback();
> txtResults.Text = "Transfer Error";
> throw ex;
> }
> }
> }
> catch(Exception ex)
> {
> string errMessage = "";
> for( Exception tempException = ex; tempException != null ; tempException
=
> tempException.InnerException )
> {
> errMessage += tempException.Message + Environment.NewLine +
> Environment.NewLine;
> }
> MessageBox.Show( string.Format( "There are some problems while trying
to
> use the Data Access Application block, please check the following error
> messages: {0}"
> + Environment.NewLine + "This test requires some modifications to the
> Northwind database. Please make sure the database has been initialized
using
> the SetUpDataBase.bat database script, or from the Install Quickstart
option
> on the Start menu.", errMessage ),
> "Application error", MessageBoxButtons.OK, MessageBoxIcon.Error );
> }
> finally
> {
> if(connection != null)
> connection.Dispose();
> }
> }
> }
>
Hello -
If the error is "Violation of unique key...", I don't think you can stop SQL
Server from throwing it.
To sidestep the problem, you could test beforehand in the stored procedure
whether a conflicting row exists - the test and update should really be
wrapped in a transaction.
Another sidestep would be to create a new method in C# dedicated to calling
the stored procedure, and this method would have a try..catch that would
deal with the exception and prevent it reaching button3_Click().
I think this issue will be easier to deal with in SQL Server 2005 - it has
try..catch built in to T-SQL.
Regards,
Simon|||I did see where 2005 has the try/catch block, and was wishing it was
available now.
I was going to test to see if the key exists first, and write the new record
if it doesn't, but when 100's of people are entering data at the same time,
there is still a chance that two people could be inserting the same record a
t
the same time. The first persons attempt will get through, and the second
person will get an error exception.
I like the idea of creating a module that calls the stored procedure so it
can have it's own try/catch block.
Thanks,
Vern

> Hello -
> If the error is "Violation of unique key...", I don't think you can stop S
QL
> Server from throwing it.
> To sidestep the problem, you could test beforehand in the stored procedure
> whether a conflicting row exists - the test and update should really be
> wrapped in a transaction.
> Another sidestep would be to create a new method in C# dedicated to callin
g
> the stored procedure, and this method would have a try..catch that would
> deal with the exception and prevent it reaching button3_Click().
> I think this issue will be easier to deal with in SQL Server 2005 - it has
> try..catch built in to T-SQL.
> Regards,
> Simon
>
>

How to get results from a storedprocedure

To all,
I looked at the MS-SQL pubs sample database and execute the examplestored procedure reptq2 and I got 17 results set back. Where can I findan example using Visual Studio DataGrid or any means to get all theseresults from this SP.
Thanks,

Frank
Try this link for a two part tutorial in C# that can return 100 rows. Hope this helps.
http://www.dotnetjunkies.com/Tutorial/EA868776-D71E-448A-BC23-B64B871F967F.dcik|||I just remembered that Stored proc in Pubs used COMPUTE which is a none relational aggregate function in SQL Server so you have to make the code in the link work with the stored proc and Pubs. Hope this helps.|||Here's the same data from that stored procedure, in a single result set... Avoid COMPUTE BY.

use pubs
go

select
t.type,
t.pub_id,
t.title_id,
ta.au_ord,
Name = substring (a.au_lname, 1,15),
t.ytd_sales,
avg_pub.salessum as avg_pub,
avg_pub_type.salessum as avg_pub_type
from titles t
join titleauthor ta on t.title_id = ta.title_id
join authors a on a.au_id = ta.au_id
join
(
select
t.pub_id,
avg(t.ytd_sales)
from titles t
join titleauthor ta on t.title_id = ta.title_id
where
t.pub_id is NOT NULL
group by
t.pub_id
) avg_pub (pub_id, salessum) on avg_pub.pub_id = t.pub_id
join
(
select
t.pub_id,
t.type,
avg(t.ytd_sales)
from titles t
join titleauthor ta on t.title_id = ta.title_id
where
t.pub_id is NOT NULL
group by
t.pub_id,
t.type
) avg_pub_type (pub_id, type, salessum) on avg_pub_type.pub_id = t.pub_id and avg_pub_type.type = t.type
where
t.pub_id is NOT NULL
order by
t.type,
t.pub_id

How to get results back from a Stored procedure.

I have a stored procedure1 calling stored procedure 2. Stored
procedure 2 when it is finished returns a single row with 2 columns.
Is there a way to grab the return of column 1 from stored procedure 2
inside stored procedure 1?
Thank you in advance
http://www.sommarskog.se/share_data.html
"TheVillageCodingIdiot" <whosyodaddy1019@.hotmail.com> wrote in message
news:1651d138-de4e-49ac-928c-c2558468ef55@.s50g2000hsb.googlegroups.com...
>I have a stored procedure1 calling stored procedure 2. Stored
> procedure 2 when it is finished returns a single row with 2 columns.
> Is there a way to grab the return of column 1 from stored procedure 2
> inside stored procedure 1?
> Thank you in advance

How to get results back from a Stored procedure.

I have a stored procedure1 calling stored procedure 2. Stored
procedure 2 when it is finished returns a single row with 2 columns.
Is there a way to grab the return of column 1 from stored procedure 2
inside stored procedure 1?
Thank you in advancehttp://www.sommarskog.se/share_data.html
"TheVillageCodingIdiot" <whosyodaddy1019@.hotmail.com> wrote in message
news:1651d138-de4e-49ac-928c-c2558468ef55@.s50g2000hsb.googlegroups.com...
>I have a stored procedure1 calling stored procedure 2. Stored
> procedure 2 when it is finished returns a single row with 2 columns.
> Is there a way to grab the return of column 1 from stored procedure 2
> inside stored procedure 1?
> Thank you in advance

How to get record count only from FTS

Hi,
Is there a way to get the FTS system to return you the count of matches
only, without passing back all the key ids.
Basically if a user doesn't find what they are looking for with their
initial multi term search, i.e '10k resistor tomatoes'.
I want to display a list of the counts for each word. 10k = 500,
resistor = 46000, tomatoes = 5. This will help them refine their
search.
select count(*) from freetexttable([MyCatName],*,'resistor') seems
quite slow where there are a large number of matches as I assume it's
passing the data back for SQL to count. Is there a way to just get the
count it came up with.
Thanks,
No, there is no way to do this using SQL FTS, some of the Microsoft Search
engines return a hitcount value which is the raw number of hits for all
search tokens.
You could maintain a count should you shred all documents in an inverted
file index - this will require a lot of work however.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Martin" <bigmarts@.hotmail.com> wrote in message
news:1169052866.809003.281110@.a75g2000cwd.googlegr oups.com...
> Hi,
> Is there a way to get the FTS system to return you the count of matches
> only, without passing back all the key ids.
> Basically if a user doesn't find what they are looking for with their
> initial multi term search, i.e '10k resistor tomatoes'.
> I want to display a list of the counts for each word. 10k = 500,
> resistor = 46000, tomatoes = 5. This will help them refine their
> search.
> select count(*) from freetexttable([MyCatName],*,'resistor') seems
> quite slow where there are a large number of matches as I assume it's
> passing the data back for SQL to count. Is there a way to just get the
> count it came up with.
> Thanks,
>
|||Hello Martin,
Is this SQL 2000 or SQL 2005. The latter is orders of magintude better at
this.
We cache keyword counts to achieve something similar
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Hi,
> Is there a way to get the FTS system to return you the count of
> matches only, without passing back all the key ids.
> Basically if a user doesn't find what they are looking for with their
> initial multi term search, i.e '10k resistor tomatoes'.
> I want to display a list of the counts for each word. 10k = 500,
> resistor = 46000, tomatoes = 5. This will help them refine their
> search.
> select count(*) from freetexttable([MyCatName],*,'resistor') seems
> quite slow where there are a large number of matches as I assume it's
> passing the data back for SQL to count. Is there a way to just get
> the count it came up with.
> Thanks,
>
|||Hi, it's 2005.
It's not a major issue, current method of counting the results is
actually performing ok.
Shredding inverted file indexes sounds interesting. Any pointers of
where to look for info on what you were suggesting..?
I assume you
On 18 Jan, 20:11, Simon Sabin <SimonSa...@.noemail.noemail> wrote:
> Hello Martin,
> Is this SQL 2000 or SQL 2005. The latter is orders of magintude better at
> this.
> We cache keyword counts to achieve something similar

how to get one columns back ground color to red

Hi all
In attendance report i need to show the back ground color as red if the column is sundayRight click the field, choose format field, select the Border tab, press the 'x-2' button to the right of the background boxes, enter a formula like
if {table.field} = 'Sunday' then crRed else crNoColor

how to get IDENTITY_INSERT Incriment Primary Key ID roll back when the application fails.

My question is how to get IDENTITY_INSERT Incriment Primary Key ID roll back when the application fails.

Using TransactionScope with single connection in DataObject. I am trying to insert row in two dataTable using its own tableAdapter (two tableAdapter).

I have Product table with ProductID primary key with incriment identity. and that ProductID is used to insert row in ProductHistory Table. Lets say Product table has the last ProductID=8 (8 rows) and the next ProductID will be 9.

When I insert row in both table and if the second table insert fails both gets roll back (which is good). but when I insert again another time the Product ID=10 not 9. Is there any way to roll back the ProductID in Product table so when i insert next time it has incriment number instead of gap.

That is the nature of IDENTITY column. The number once assigned to a row is gone whether the transaction succeeds or not. Even if the transaction succeeds, if you delete the row, the number is not re-assigned. Hopefully your application is based only on the PK-FK relationship and not on the serial order of the data.

|||

Thats what I thought but thought there might be a way I guess not, My table relation is based on PK-FK . It looks odd when some one is viewing Product table and has a gap in Product ID in between product (further someone might have question or confuse). I guess I have to manually create Product ID Primary Key data.

Thank for your answer

|||

Just out of curiosity, why is the ProductId important? ProductId should just be a PK to identify the product, it should not matter if there are gaps.

|||

For Developer standpoint it shouldnt matter at all, For other users, they would like to see incriment by 1. The product page displays Product ID, Product Name, Product Insert Date. So if they will see gap in Product ID inbetween they will have question and trust me they dont want that way, I have dealt in similar situation before.

|||

So why is productId even shown? Do users care?

|||

Oh Yes , that is how they identify the product. Product ID is more important then Product Name. Product ID is the key when communicating with anyone and used all over the page. Product come and go. It is very hard to keep up with their name. They find easy with Product ID

|||

rumax96:

Oh Yes , that is how they identify the product. Product ID is more important then Product Name. Product ID is the key when communicating with anyone and used all over the page. Product come and go. It is very hard to keep up with their name. They find easy with Product ID

Then a gap won't matter - unless your users remember that the product they want appeared 4 products further down a page than product 26, and therefore assume they want product 30. I know users can be extremely odd, but that seems to me to be pushing it a bit.

|||

Thanks Mike, I understand , From a developing perspective it doenst make sense wether there is a gap or not in Primary key field. The maing thing is user dont like gap in their ProductID and If that is the way the users wants then We have no choice. Like I said I have deal before in similar situation.

How to get hold of data from web provider using SQL Server?

I am new to SQL server so apologies if this is a stupid question.
I have a website built and hosted by another company, with SQL server back end data. They don't want to analyse the data but I do. I am familiar with SQL and have SQL server express running on my laptop.
So I was thinking I could get them to send me the data from the server (it will be less than 1GB I imagine), and I could analyse it myself.
So my questions are:
will this work?
and what do I ask them to send me? Is it just a couple of files they have to copy and send me? Or is it more complicated than that?
Many thanks for any advice you can offer.
James

Have them back up the database and send the backup file to you. Restore it on your system and you're ready to go. This will work as long as the version of SQL Server they have (200 or 2005) is equal to or less than the one you have.

Buck Woody

How to get from JobID back to SubscriptionID

Hi!
I am hoping someone can explain how I can determine the subscription
associated with a currently running job (report). If a report is misbehaving,
we would like to determine the subscription that initiated the report
running. We can cancel the job to stop the report from running this time, but
we would like to be able to prevent it from running again.
I believe that this would mean tieing the jobid back to a subscriptionid,
but I have not been able to determine a way either programatically or via a
direct query of the RS tables to determine this.
Any help would be greatly appreciated.
Thanks in advance,
BobHello,
This is what I do:
--This will help you identify the OID of the report
SELECT ItemID, Path
FROM Catalog
--This will show all subscriptions for the report
SELECT *
FROM Subscriptions
WHERE Report_OID = '[ItemID from previous query]'
--This will tell you the job id
SELECT *
FROM ReportSchedule
WHERE SubscriptionID = '[SubscriptionID from the previous query]'
You can put all together, but I prefer to run them one by one because I have
a lot of reports and also several subscriptions per report.
Hope this helps.
Ricardo.
"bobhug" wrote:
> Hi!
> I am hoping someone can explain how I can determine the subscription
> associated with a currently running job (report). If a report is misbehaving,
> we would like to determine the subscription that initiated the report
> running. We can cancel the job to stop the report from running this time, but
> we would like to be able to prevent it from running again.
> I believe that this would mean tieing the jobid back to a subscriptionid,
> but I have not been able to determine a way either programatically or via a
> direct query of the RS tables to determine this.
> Any help would be greatly appreciated.
> Thanks in advance,
> Bob|||Ricardo,
Thanks for the response.
I do not see how this gets me to JobID however.
SELECT *
FROM ReportSchedule
WHERE SubscriptionID = '[SubscriptionID from the previous query]'
This would give me scheduleid and reportid, but I still do not know how to
get to jobid.
Perhaps I am just missing something.
Thanks,
Bob
"Ricardo Sampei" wrote:
> Hello,
> This is what I do:
> --This will help you identify the OID of the report
> SELECT ItemID, Path
> FROM Catalog
> --This will show all subscriptions for the report
> SELECT *
> FROM Subscriptions
> WHERE Report_OID = '[ItemID from previous query]'
> --This will tell you the job id
> SELECT *
> FROM ReportSchedule
> WHERE SubscriptionID = '[SubscriptionID from the previous query]'
> You can put all together, but I prefer to run them one by one because I have
> a lot of reports and also several subscriptions per report.
> Hope this helps.
> Ricardo.
> "bobhug" wrote:
> > Hi!
> > I am hoping someone can explain how I can determine the subscription
> > associated with a currently running job (report). If a report is misbehaving,
> > we would like to determine the subscription that initiated the report
> > running. We can cancel the job to stop the report from running this time, but
> > we would like to be able to prevent it from running again.
> > I believe that this would mean tieing the jobid back to a subscriptionid,
> > but I have not been able to determine a way either programatically or via a
> > direct query of the RS tables to determine this.
> > Any help would be greatly appreciated.
> >
> > Thanks in advance,
> > Bob|||Hello Bob,
Probably you have already figured out thi one, but anyway, here is the
answer: What you get in ScheduleID from ReportSchedule is the job name in SQL
Server Agent.
Ricardo.
"bobhug" wrote:
> Ricardo,
> Thanks for the response.
> I do not see how this gets me to JobID however.
> SELECT *
> FROM ReportSchedule
> WHERE SubscriptionID = '[SubscriptionID from the previous query]'
> This would give me scheduleid and reportid, but I still do not know how to
> get to jobid.
> Perhaps I am just missing something.
> Thanks,
> Bob
> "Ricardo Sampei" wrote:
> > Hello,
> >
> > This is what I do:
> > --This will help you identify the OID of the report
> > SELECT ItemID, Path
> > FROM Catalog
> >
> > --This will show all subscriptions for the report
> > SELECT *
> > FROM Subscriptions
> > WHERE Report_OID = '[ItemID from previous query]'
> >
> > --This will tell you the job id
> > SELECT *
> > FROM ReportSchedule
> > WHERE SubscriptionID = '[SubscriptionID from the previous query]'
> >
> > You can put all together, but I prefer to run them one by one because I have
> > a lot of reports and also several subscriptions per report.
> >
> > Hope this helps.
> >
> > Ricardo.
> >
> > "bobhug" wrote:
> >
> > > Hi!
> > > I am hoping someone can explain how I can determine the subscription
> > > associated with a currently running job (report). If a report is misbehaving,
> > > we would like to determine the subscription that initiated the report
> > > running. We can cancel the job to stop the report from running this time, but
> > > we would like to be able to prevent it from running again.
> > > I believe that this would mean tieing the jobid back to a subscriptionid,
> > > but I have not been able to determine a way either programatically or via a
> > > direct query of the RS tables to determine this.
> > > Any help would be greatly appreciated.
> > >
> > > Thanks in advance,
> > > Bob|||Ricardo,
Hi!
Now I understand the confusion.
The JobID I was referring to is the one as found in the RunningJobs table
and the Job class: http://tinyurl.com/9zyb2 , which is different than what
you are talking about, the name of the SQL Server agent job.
Thanks for responding,
Bob
"Ricardo Sampei" wrote:
> Hello Bob,
> Probably you have already figured out thi one, but anyway, here is the
> answer: What you get in ScheduleID from ReportSchedule is the job name in SQL
> Server Agent.
> Ricardo.
> "bobhug" wrote:
> > Ricardo,
> >
> > Thanks for the response.
> > I do not see how this gets me to JobID however.
> > SELECT *
> > FROM ReportSchedule
> > WHERE SubscriptionID = '[SubscriptionID from the previous query]'
> > This would give me scheduleid and reportid, but I still do not know how to
> > get to jobid.
> >
> > Perhaps I am just missing something.
> > Thanks,
> > Bob
> >
> > "Ricardo Sampei" wrote:
> >
> > > Hello,
> > >
> > > This is what I do:
> > > --This will help you identify the OID of the report
> > > SELECT ItemID, Path
> > > FROM Catalog
> > >
> > > --This will show all subscriptions for the report
> > > SELECT *
> > > FROM Subscriptions
> > > WHERE Report_OID = '[ItemID from previous query]'
> > >
> > > --This will tell you the job id
> > > SELECT *
> > > FROM ReportSchedule
> > > WHERE SubscriptionID = '[SubscriptionID from the previous query]'
> > >
> > > You can put all together, but I prefer to run them one by one because I have
> > > a lot of reports and also several subscriptions per report.
> > >
> > > Hope this helps.
> > >
> > > Ricardo.
> > >
> > > "bobhug" wrote:
> > >
> > > > Hi!
> > > > I am hoping someone can explain how I can determine the subscription
> > > > associated with a currently running job (report). If a report is misbehaving,
> > > > we would like to determine the subscription that initiated the report
> > > > running. We can cancel the job to stop the report from running this time, but
> > > > we would like to be able to prevent it from running again.
> > > > I believe that this would mean tieing the jobid back to a subscriptionid,
> > > > but I have not been able to determine a way either programatically or via a
> > > > direct query of the RS tables to determine this.
> > > > Any help would be greatly appreciated.
> > > >
> > > > Thanks in advance,
> > > > Bob