Change in MHTML rendering in SP1?

Greetings.
I recently installed SP1, and am having some trouble with my Custom Delivery
Extension.
My extension renders to MHTML, converts the resulting stream to a
CDO.Message, then manipulates the resulting object. After installing SP1, I
get an exception during this process.
I also noticed that after installing SP1, my report snapshots have gone from
about 55K to 15K.
Has the MHTML rendering changed between RTM and SP1? Is there any
documentation for how it's changed?
Regards, Jonathan
http://kerblog.com/earlyedition/archive/2004/12/09/258.aspxHi Jonathan,
What's the exactly exception you encounter? Have you installed Reporting
Services Services Pack 1 successfully? Even the size the snapshot seems go
down, does the snapshot looks exactly as you expected?
Based on my knowledge, there is only one issue about rendering mhtml fixed
in SP1, see the following knowledge base articles below and no other
changes in rendering were made.
FIX: Bookmark links in a report that is rendered to the Web archive file
format (.mhtml) may not work correctly
http://support.microsoft.com/kb/842534
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Michael,
Thanks for your quick reply.
"Michael Cheng [MSFT]" wrote:
> What's the exactly exception you encounter?
After I Render the report, I convert it to a CDO.Message and then try to
open the body using CDO.IBodyPart.GetDecodedContentStream(). The exception
is basically a COMException.
> Have you installed Reporting
> Services Services Pack 1 successfully?
Yes.
> Even the size the snapshot seems go
> down, does the snapshot looks exactly as you expected?
Yes, it looks fine.
Reporting Services RTM used to render MHTML into a "text/html" type message
if there were no graphics in the report. It looks to me like SP1 now always
renders as "multipart/related." I'm able to traverse the "multipart/related"
message to find the "text/html" part.
No problem, but it would have been nice to document. I only found out after
the change broke my delivery extension.
Regards,
Jonathan
http://kerblog.com/earlyedition/archive/2004/12/09/260.aspx|||Hi Jonathan,
I will try to find somebody from development team to see whether he knows
the answer and there is any documents about this available. I will keep you
updated.
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||HI Jonathan,
It seems the snapshot size (in the report server catalog) has decreased,
because they are compressed in SP1 (unless snapshot compression is turned
off). However, I am not sure why SP1 always renders as "multipart/related"
as you said and no relative documents.
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Certificate not found

Hello I haw trouble getting the service broker to work I have 3 instances of SQL servers:

1 “Sender”SQL 2005 Server

2 “Receiver 1” SQLEXPRESS 2005

3 “Recevier 2” SQLEXPRESS 2005

What I wont is to be abele to do is to send a message from “Sender” to “Receiver 1” or “Recevier 2”.

I am abele to send a message from “Sender” to “Receiver 1” but if I send a message to “Receiver 2” I get a dialog security problem I think. If I use profiler I can se in “Receiver 2” the events:

Broker:Connection

Audit Broker Login

Broker:Message Classify

Audit Broker Conversation = Certificate not found

Broker:Message Undeliverable

And I cant find what′s wrong, this Is my scripts for etch instance.

“Sender”

USE master

CREATE CERTIFICATE Cert_ROBOTSRV

WITH SUBJECT = 'Cert_ROBOTSRV_auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_ROBOTSRV TO FILE = 'C:\Cert_ROBOTSRV'

GO

CREATE ENDPOINT SBEndpointServer STATE = STARTED

AS TCP (LISTENER_PORT = 5723)

FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE Cert_ROBOTSRV)

GO

CREATE USER andon

CREATE CERTIFICATE Cert_sevapc311_pub AUTHORIZATION andon

FROM FILE = 'C:\Cert_sevapc311'

--DROP CERTIFICATE Cert_Andonpc017_Trans

CREATE CERTIFICATE Cert_Andonpc017_Trans AUTHORIZATION andon

FROM FILE = 'C:\Cert_andonpc017_Trans'

--DROP LOGIN sbLogin

CREATE LOGIN sbLogin

FROM CERTIFICATE Cert_Andonpc017_Trans;

GO

GRANT CONNECT ON ENDPOINT::SBEndpointServer TO [public]

GRANT CONNECT ON ENDPOINT::SBEndpointServer TO andon

GO

-

USE AndonDB

CREATE ROUTE Grafik_sevapc311

WITH SERVICE_NAME = 'Grafik_Service_Recive_sevapc311',

BROKER_INSTANCE = '7C737F42-2DF6-46E7-A6B6-89D1A9608DE2',

ADDRESS = 'TCP://sevapc311:5723'

GO

--DROP ROUTE Grafik_andonpc017

CREATE ROUTE Grafik_Andonpc017

WITH SERVICE_NAME = 'Grafik_Service_Recive_Andonpc017',

BROKER_INSTANCE = 'AE2B294A-B02E-4709-A51E-CFBFD0E478C1',

ADDRESS = 'TCP://192.168.20.106:5723'

GO

CREATE CERTIFICATE Cert_ROBOTSRV_Dialog

WITH SUBJECT = 'Cert_ROBOTSRV_auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_ROBOTSRV_Dialog TO FILE = 'C:\Cert_ROBOTSRV_Dialog'

GO

CREATE CERTIFICATE Cert_sevapc311_pub_Dialog AUTHORIZATION andon

FROM FILE = 'C:\Cert_sevapc311_Dialog'

--DROP CERTIFICATE Cert_andonpc017_Dialog

CREATE CERTIFICATE Cert_Andonpc017_Dialog AUTHORIZATION andon

FROM FILE = 'C:\Cert_andonpc017_Dialog'

GRANT SEND ON SERVICE::[Grafik_Service_Send_ROBOTSRV]

TO andon

CREATE USER sbLogin

GRANT SEND ON SERVICE::[Grafik_Service_Send_ROBOTSRV]

TO sbLogin

GO

--GRANT SEND ON SERVICE::[Grafik_Service_Send_ROBOTSRV]

--TO [VADERSTAD\vrobot]

--GO

-- Grant RECEIVE permission on the queue.

GRANT RECEIVE ON [Grafik_Queue]

TO andon

GO

GRANT CONTROL ON SERVICE::[Grafik_Service_Send_ROBOTSRV]

TO andon

GO

--DROP REMOTE SERVICE BINDING Grafik_sevap

CREATE REMOTE SERVICE BINDING Grafik_sevap

TO SERVICE 'Grafik_Service_Recive_sevapc311'

WITH USER = andon

GO

--DROP REMOTE SERVICE BINDING Grafik_andonpc017

CREATE REMOTE SERVICE BINDING Grafik_andonpc017

TO SERVICE 'Grafik_Service_Recive_Andonpc017'

WITH USER = andon

“Receiver 1”

use master

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON'

CREATE CERTIFICATE Cert_sevapc311

WITH SUBJECT = 'Cert_sevapc311_Auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_sevapc311 To FILE = 'C:\Cert_sevapc311'

CREATE ENDPOINT SBEndpointklient STATE = STARTED

AS TCP (LISTENER_PORT = 5723)

FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE Cert_sevapc311)

CREATE CERTIFICATE Cert_ROBOTSRV_pub AUTHORIZATION andon

FROM FILE = 'C:\Cert_ROBOTSRV';

GRANT CONNECT ON ENDPOINT::SBEndpointklient to andon

-

use KlientDB

GRANT SEND ON SERVICE::[Grafik_Service_Recive_sevapc311]

TO andon

GO

GRANT CONTROL ON SERVICE::[Grafik_Service_Recive_sevapc311]

TO andon

GO

GRANT RECEIVE ON [Grafik_Queue]

TO andon

GO

CREATE Route Grafik_ROBOTSRV

WITH

SERVICE_NAME = 'Grafik_Service_Send_ROBOTSRV',

BROKER_INSTANCE = '2BA192F8-0BA3-4237-A156-21AFF7C65481',

ADDRESS = 'TCP://ROBOTSRV:5723'

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON'

CREATE CERTIFICATE Cert_sevapc311_Dialog

WITH SUBJECT = 'Cert_sevapc311_Auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_sevapc311_Dialog To FILE = 'C:\Cert_sevapc311_Dialog'

CREATE CERTIFICATE Cert_ROBOTSRV_pub_Dialog AUTHORIZATION andon

FROM FILE = 'C:\Cert_ROBOTSRV_Dialog';

“Receiver 2”

use master

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON'

--DROP CERTIFICATE Cert_Andonpc017_Trans

CREATE CERTIFICATE Cert_Andonpc017_Trans

WITH SUBJECT = 'Cert_Andonpc017_Auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_Andonpc017_Trans To FILE = 'F:\Cert_Andonpc017_Trans'

CREATE ENDPOINT SBEndpointklient STATE = STARTED

AS TCP (LISTENER_PORT = 5723)

FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE Cert_Andonpc017_Trans)

--DROP USER andon

CREATE USER andon

--DROP CERTIFICATE Cert_ROBOTSRV

CREATE CERTIFICATE Cert_ROBOTSRV AUTHORIZATION andon

FROM FILE = 'C:\Cert_ROBOTSRV';

CREATE LOGIN sbLogin

FROM CERTIFICATE Cert_ROBOTSRV;

GO

GRANT CONNECT ON ENDPOINT::SBEndpointklient TO [public]

--Select * from sys.certificates

use KlientDB

create user andon

Grant SEND ON SERVICE::[Grafik_Service_Recive_Andonpc017] to [Public]

GRANT SEND ON SERVICE::[Grafik_Service_Recive_Andonpc017]

TO andon

GO

GRANT CONTROL ON SERVICE::[Grafik_Service_Recive_Andonpc017]

TO andon

GO

GRANT RECEIVE ON [Grafik_Queue]

TO andon

GO

--DROP Route Grafik_ROBOTSRV

CREATE Route Grafik_ROBOTSRV

WITH

SERVICE_NAME = 'Grafik_Service_Send_ROBOTSRV',

BROKER_INSTANCE = '2BA192F8-0BA3-4237-A156-21AFF7C65481',

ADDRESS = 'TCP://ROBOTSRV:5723'

--Dialog S?kerhet

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON'

--Drop CERTIFICATE Cert_Andonpc017_Dialog

CREATE CERTIFICATE Cert_Andonpc017_Dialog

WITH SUBJECT = 'Cert_Andonpc017_Auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_Andonpc017_Dialog To FILE = 'F:\Cert_Andonpc017_Dialog'

--Drop CERTIFICATE Cert_ROBOTSRV_Dialog

CREATE CERTIFICATE Cert_ROBOTSRV_Dialog AUTHORIZATION andon

FROM FILE = 'C:\Cert_ROBOTSRV_Dialog';

The Security Audit:Audit Broker Conversation event details will contain the Issuer Name and Serial Number of the certificate not found (I think they are the UserName and RemoteLogin columns). You can then look up sys.certificates on the sender side to make sure the sender is using the certificates you expect.

HTH,
~ Remus

|||

Ok thanks.

I will look if I can find what′s wrong.

I got it to work when I turned of the dialog security by deleting RSB.

Caught in a Replication Catch-22, SQLServer2005

I am currently moving from SQL2K to 2005 and am having trouble incorporating
replication in 2005.
First off, I've found myself in a loop where I have two publications that
"don't exist" even though they show up in my Local Publishers list. I can't
delete tables that are articles in those publications because the tables are
being replicated. However, if I try to delete the publication, I get the
message that the publication doesn't exist. So the tables I need to drop are
bound to a publication that doesn't exist.
I've tried looking in the distribution and master databases to find the
missing links but I can't find anything. Without spending a lot of time
going through how I got in this mess, I'd just like to know if there are some
sp's that I can use to purge these publications once and for all.
Thanks in advance.
Roger.
You don't say which type of replication you are using. My experience
is only with merge replication, in this case I would suggest using
sp_removedbreplication in addition to normal sp_dropmergepublication
(or delete from the Gui)
Please see KB324401 for recommendations from MS. http://
support.microsoft.com/kb/324401
Tim Hill
On Feb 9, 3:33 pm, Roger Denison
<RogerDeni...@.discussions.microsoft.com> wrote:
> I am currently moving from SQL2K to 2005 and am having trouble incorporating
> replication in 2005.
> First off, I've found myself in a loop where I have two publications that
> "don't exist" even though they show up in my Local Publishers list. I can't
> delete tables that are articles in those publications because the tables are
> being replicated. However, if I try to delete the publication, I get the
> message that the publication doesn't exist. So the tables I need to drop are
> bound to a publication that doesn't exist.
> I've tried looking in the distribution and master databases to find the
> missing links but I can't find anything. Without spending a lot of time
> going through how I got in this mess, I'd just like to know if there are some
> sp's that I can use to purge these publications once and for all.
> Thanks in advance.
> --
> Roger.
|||I had a similar problem to this. The only way round it was to script
the publications & subscriptions, remove replication and then
re-enable.
On Fri, 9 Feb 2007 07:33:00 -0800, Roger Denison
<RogerDenison@.discussions.microsoft.com> wrote:

>I am currently moving from SQL2K to 2005 and am having trouble incorporating
>replication in 2005.
>First off, I've found myself in a loop where I have two publications that
>"don't exist" even though they show up in my Local Publishers list. I can't
>delete tables that are articles in those publications because the tables are
>being replicated. However, if I try to delete the publication, I get the
>message that the publication doesn't exist. So the tables I need to drop are
>bound to a publication that doesn't exist.
>I've tried looking in the distribution and master databases to find the
>missing links but I can't find anything. Without spending a lot of time
>going through how I got in this mess, I'd just like to know if there are some
>sp's that I can use to purge these publications once and for all.
>Thanks in advance.

Category and Subcategory Problem

Hi everyone, I am having trouble with a particular problem with SQL. I have a table that defines product categories like so:

Id (int)
Text varchar(50)
ParentId (int)

It holds all our categories, with subcategories having the appropriate ParentId relating to the above category. I am trying to write a stored procedure that takes in a single Id, and finds out all the related subcategories and subcategories all the way down the tree. I need to produce a resultset with a single column of Id's of all the subcategories etc

For example if the table had the following records:

8 - General - 1
9 - Academic - 1
10 - Science - 1
11 - History - 8
12 - Maths - 8
13 - English - 9
14 - Spanish - 9
15 - England - 13

So if I was to feed in Id 9 the resulting table that I want is like this

9
13
15

My problem is that there isn't a defined number of subcategory levels. General has only 1 subcategory level, but Academic has 2 subcategory levels.

The only part solution I have found was this:

CREATE TABLE #Categories (
CategoryId int)

insert #Categories (CategoryId)
select Id
from Category as c1
where c1.ParentId=8 or c1.Id=8

however it only brings back the first level of subcategories. I was intending to loop this over and over however because of the inconsistent number of levels I can't put in a predefined number of loops.

I have never really faced a problem like this before. I am pretty new to T-SQL and am not sure if there is an easy way to overcome this, but any help would be very much appreciated. I was pretty much ready to pull out my hair yesterday trying to solve this .

I am using SQL Server Express 2005 on Windows Server 2003

Thanks in advance for any help,

Dylan

Please take a look at the link below:

http://msdn2.microsoft.com/en-us/library/ms186243.aspx

You can use recursive CTEs in SQL Server 2005 to write the queries. You can encapsulate those in views or inline TVFs.

|||Thanks that worked great, exactly what I needed.