Hi All,
Not sure if this is the right group so feel free to point me in a better
direction.
Most of our customers have a personal price for every product but Im having
a nightmare trying to produce a price matrix on the fly. I currently have a
solution in MS Access 2K which uses a series of append and update queries to
create a temp table, this takes a little over 2 minutes to generate and is
becoming problematic. The idea is to have the sales office be able to give a
customer a price over the phone - a 2-3 minute wait isnt going to work.
I have 3 sources of prices
global : This contains a default price for every stock item it is the base
of all prices
price set(s): these contain a group of stock items from global at a
discounted price
personal product prices : A customer specific price for a specific stock
item
All prices can and do change and frequently and there are are also quantity
discount breaks for the same stock Item.
We carry approximately 3500 products and have about 4000 customers.
Im currently in the process or moving things from MS Access 2K over to MS
SQL Server 2K
I realise that its difficult to visualise without the database but if
someone could offer some pointers,
Im put off buliding 1 big flat table due to the size and the frequent
updates - am I right to avoid doing this?
Is it correct to construct the temp table and then running update/inserts -
Im having problems with the lack of a unique ID as Stock code is duplicated
for different quantity.
I can quickly generate a temp table containing all the information from each
source adding a field value for the process number (1,2,3 ) what Id like is
the record with the highest process number, but if I use a group by and max
as soon as I add price in the view I get all the records again.
I cant help but think I have something fundamentally wrong as its causing
such a problem. Can anyone point me at some info or share some insight.
TIA."Annon" <Ieatlotsofspam@.yum.yum> wrote in message
news:rNomf.5843$iz3.8@.text.news.blueyonder.co.uk...
> Hi All,
> Not sure if this is the right group so feel free to point me in a better
> direction.
> Most of our customers have a personal price for every product but Im
> having
> a nightmare trying to produce a price matrix on the fly. I currently have
> a
> solution in MS Access 2K which uses a series of append and update queries
> to
> create a temp table, this takes a little over 2 minutes to generate and is
> becoming problematic. The idea is to have the sales office be able to give
> a
> customer a price over the phone - a 2-3 minute wait isnt going to work.
> I have 3 sources of prices
> global : This contains a default price for every stock item it is the base
> of all prices
> price set(s): these contain a group of stock items from global at a
> discounted price
> personal product prices : A customer specific price for a specific stock
> item
> All prices can and do change and frequently and there are are also
> quantity
> discount breaks for the same stock Item.
> We carry approximately 3500 products and have about 4000 customers.
> Im currently in the process or moving things from MS Access 2K over to MS
> SQL Server 2K
> I realise that its difficult to visualise without the database but if
> someone could offer some pointers,
> Im put off buliding 1 big flat table due to the size and the frequent
> updates - am I right to avoid doing this?
> Is it correct to construct the temp table and then running
> update/inserts -
> Im having problems with the lack of a unique ID as Stock code is
> duplicated
> for different quantity.
> I can quickly generate a temp table containing all the information from
> each
> source adding a field value for the process number (1,2,3 ) what Id like
> is
> the record with the highest process number, but if I use a group by and
> max
> as soon as I add price in the view I get all the records again.
> I cant help but think I have something fundamentally wrong as its causing
> such a problem. Can anyone point me at some info or share some insight.
>
You'll need to post your table DDL, sample data and desired output for any
real information.
David|||which takes precedence? (i assume customer -> price set -> global)
how does a price set relate to the customer? (if it does)
if it doesn't, how do you know which to use? (assuming a stockno can be
in more than one price set)
what do you mean by a "process number"?
it would probably be best to avoid a big flat table in this case, but if
you make the source for this matrix a data warehouse, then that's
probably what you'll have.
DDL will certainly help get better answers...
Annon wrote:
> Hi All,
> Not sure if this is the right group so feel free to point me in a better
> direction.
> Most of our customers have a personal price for every product but Im havin
g
> a nightmare trying to produce a price matrix on the fly. I currently have
a
> solution in MS Access 2K which uses a series of append and update queries
to
> create a temp table, this takes a little over 2 minutes to generate and is
> becoming problematic. The idea is to have the sales office be able to give
a
> customer a price over the phone - a 2-3 minute wait isnt going to work.
> I have 3 sources of prices
> global : This contains a default price for every stock item it is the base
> of all prices
> price set(s): these contain a group of stock items from global at a
> discounted price
> personal product prices : A customer specific price for a specific stock
> item
> All prices can and do change and frequently and there are are also quantit
y
> discount breaks for the same stock Item.
> We carry approximately 3500 products and have about 4000 customers.
> Im currently in the process or moving things from MS Access 2K over to MS
> SQL Server 2K
> I realise that its difficult to visualise without the database but if
> someone could offer some pointers,
> Im put off buliding 1 big flat table due to the size and the frequent
> updates - am I right to avoid doing this?
> Is it correct to construct the temp table and then running update/inserts
-
> Im having problems with the lack of a unique ID as Stock code is duplicate
d
> for different quantity.
> I can quickly generate a temp table containing all the information from ea
ch
> source adding a field value for the process number (1,2,3 ) what Id like
is
> the record with the highest process number, but if I use a group by and ma
x
> as soon as I add price in the view I get all the records again.
> I cant help but think I have something fundamentally wrong as its causing
> such a problem. Can anyone point me at some info or share some insight.
> TIA.
>
>|||If you can't post the DDL, explain the rules.
ML
http://milambda.blogspot.com/|||"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:ODmZj5R$FHA.436@.TK2MSFTNGP10.phx.gbl...
> which takes precedence? (i assume customer -> price set -> global)
> how does a price set relate to the customer? (if it does)
> if it doesn't, how do you know which to use? (assuming a stockno can be in
> more than one price set)
> what do you mean by a "process number"?
> it would probably be best to avoid a big flat table in this case, but if
> you make the source for this matrix a data warehouse, then that's probably
> what you'll have.
> DDL will certainly help get better answers...
Hi Tery,
Your precedence is right, customer - price set then global, 1 to 3.
What im looking for,
Item Qty Price
Item1 1 2.25
Item1 10 1.5
Item2 10 1.9
Item3 100 4.99
Item4 1 2.2
The best I've come up with so far is to copy each stage (global, price set
then customers price) into a temp table with triggers to check if the
stock_Code and Qty match then update rather than append.
Any help suggestions welcome.
(SQL server is all really rather new to me still)
DDL hopefully below.
CREATE TABLE [dbo].[tbl_Customers] (
[ID] [numeric](18, 0) NOT NULL ,
[Company] [char] (10) NULL ,
[ID_Price_set_Global] [numeric](18, 0) NULL ,
[ID_Price_set_Main] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_Matrix_Base] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Price_Set_ID] [numeric](18, 0) NULL ,
[Company_ID] [numeric](18, 0) NULL ,
[Stock_Code] [varchar] (10) NOT NULL ,
[Quantity] [numeric](18, 0) NOT NULL ,
[Price] [float] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_Price_Sets] (
[ID] [numeric](18, 0) NOT NULL ,
[Price_set_ID] [numeric](18, 0) NOT NULL ,
[Description] [varchar] (50) NULL
) ON [PRIMARY]
GO
insert into tbl_customers
([ID],Company,ID_price_set_global,ID_Pri
ce_set_main) values (1,'Test1',1,2)
Go
insert into tbl_price_sets ([ID],Price_set_ID,[Description]) values
(1,1,'Price set 1 Global')
insert into tbl_price_sets ([ID],Price_set_ID,[Description]) values
(2,2,'Price set 2 Band A')
Go
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
(1,null,'Item1',1,2.25)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
(1,null,'Item1',10,2)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
(1,null,'Item2',10,3)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
(1,null,'Item3',100,4.99)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
(1,null,'Item4',1,2.2)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
(1,null,'Item2',10,1.9)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
(null,1,'Item1',10,1.5)
Go|||Annon (Ieatlotsofspam@.yum.yum) writes:
> Your precedence is right, customer - price set then global, 1 to 3.
> What im looking for,
> Item Qty Price
> Item1 1 2.25
> Item1 10 1.5
> Item2 10 1.9
> Item3 100 4.99
> Item4 1 2.2
> The best I've come up with so far is to copy each stage (global, price set
> then customers price) into a temp table with triggers to check if the
> stock_Code and Qty match then update rather than append.
Is that table structure you posted carved in stone? Or can you change it?
I started to compose a query from your repro, but I found it darn
difficult as tbl_Matrix_base is so scattered. And there were also
some thing I did not understand. What does those two ID_price_set in
Customers mean? And your sample data had:
> (Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
> (1,null,'Item2',10,3)
> (Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
> (1,null,'Item2',10,1.9)
How do I know which is which?
First of all, you need to keep track of which is which. To start with
you need a table to define sellable items:
CREATE TABLE sellable_items (stockcode varchar(10) NOT NULL,
qty smallint NOT NULL,
stdprice float NOT NULL,
CONSTRAINT pk_selleable_items PRIMARY KEY (stockcode, qty))
This table could just as well hold the standard price.
A price-set table would then look like this
CREATE TABLE priceset (pricesetid integer NOT NULL,
pricesetname varchar(30) NOT NULL,
CONSTRAINT pk_priceset PRIMARY KEY (pricesetid))
The actual prices in a price set would be in:
CREATE TABLE pricesetprices (pricesetid integer NOT NULL,
stockcode varchar(10) NOT NULL,
qty smallint NOT NULL,
price float NOT NULL,
CONSTRAINT pk_pricesetprices PRIMARY KEY
(pricesetid, stockcode, qty),
CONSTRAINT fk_prp_priceset FOREIGN KEY(pricesetid)
REFERENCES pricesets (pricesetid),
CONSTRAINT fk_prp_sellable FOREIGN KEY (stockcode, qty),
REFERENCES sellable_items (stockcode, qty)
)
Then then customer-specific prices:
CREATE TABLE customerprices (customerid integer NOT NULL,
stockcode varchar(10) NOT NULL,
qty smallint NOT NULL,
price float NOT NULL,
CONSTRAINT pk_customerprices PRIMARY KEY
(customerid, stockcode, qty),
CONSTRAINT fk_cup_customer FOREIGN KEY(customerid)
REFERENCES tbl_Customers (ID),
CONSTRAINT fk_cup_sellable FOREIGN KEY (stockcode, qty),
REFERENCES sellable_items (stockcode, qty)
)
Now to get all prices for a customer you can do:
SELECT s.stock_code, s.qty, price = coalesce(cup.price, prp.price,
s.price)
FROM sellable_items s
CROSS JOIN (SELECT * FROM tbl_Customers WHERE ID = @.custid) c
LEFT JOIN pricesetprices prp ON c.pricesetid = prp.pricesetid
AND s.stock_code = prp.stock_code
AND s.qty = prp.qty
LEFT JOIN customerprices cup ON c.customerid = cup.customerid
AND s.stock_code = cup.stock_code
AND s.qty = cup.qty
A very important thing to note here is the use of foreign keys, and
multi-column primary keys. A common mistake is to add an ID column to
each table and think you have a primary key. For basic concepts like
customers, this can be a good thing, but for composed entities like the
price plan is not.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns972985B77D074Yazorman@.127.0.0.1...
> Annon (Ieatlotsofspam@.yum.yum) writes:
<SNIP elegant solution>
> Now to get all prices for a customer you can do:
> SELECT s.stock_code, s.qty, price = coalesce(cup.price, prp.price,
> s.price)
> FROM sellable_items s
> CROSS JOIN (SELECT * FROM tbl_Customers WHERE ID = @.custid) c
> LEFT JOIN pricesetprices prp ON c.pricesetid = prp.pricesetid
> AND s.stock_code = prp.stock_code
> AND s.qty = prp.qty
> LEFT JOIN customerprices cup ON c.customerid = cup.customerid
> AND s.stock_code = cup.stock_code
> AND s.qty = cup.qty
> A very important thing to note here is the use of foreign keys, and
> multi-column primary keys. A common mistake is to add an ID column to
> each table and think you have a primary key. For basic concepts like
> customers, this can be a good thing, but for composed entities like the
> price plan is not.
>
Thanks for the solution Erland, unfortunatly as is always the way - Im stuck
with backward compatibility I cant rip out the tables and replace them with
the correct design. As you point out the problem is not know what is what
and from where it came. All the data is held in 1 table the tbl_Matrix_base,
customer, price set and global all lumped together. As time was pressing I
kept the temp table created a compound key field from Company & Stock_Code &
Qty , created a unqiue index on it but with ingnore_dup_key on. and ran the
3 insert queries. It adds new records but fails to add duplicates - as long
as I keep the insert order correct it gives me the results I need.
Its far from perfect - but its bought me more time, I'll take apart your
example and see if I can work it back into the live system.
Thanks again for the help.
Showing posts with label point. Show all posts
Showing posts with label point. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Custom URL to Report Manager
Hopefully someone will know the answer to this one.
We are trying to setup a url (reports.company.com) to point to Report
Manager so that end users will not have to enter in a server name.
This works initially, but the web app changes the url back to
http://ServerName/Reports/Pages/Folder.aspx
Is there anyway to have the url not change so that it would show
http://reports.company.com/Reports/Pages/Folder.aspx?
We are wanting to customize report manager a bit before we offer to the end
user communnity.
Thanks in advance,
spindlejDuh, I must have been on crack yesterdy. After thinking about it somemore
today, I realized that the default web site in IIS configuration was set to
foward to http://ServerName/Reports/
All I needed to do was change it to forward to
http://reports.company.com/Reports/ and now it works like a charm.
Thanks to me for figuring it out :)
"spindlej" wrote:
> Hopefully someone will know the answer to this one.
> We are trying to setup a url (reports.company.com) to point to Report
> Manager so that end users will not have to enter in a server name.
> This works initially, but the web app changes the url back to
> http://ServerName/Reports/Pages/Folder.aspx
> Is there anyway to have the url not change so that it would show
> http://reports.company.com/Reports/Pages/Folder.aspx?
> We are wanting to customize report manager a bit before we offer to the end
> user communnity.
> Thanks in advance,
> spindlej
We are trying to setup a url (reports.company.com) to point to Report
Manager so that end users will not have to enter in a server name.
This works initially, but the web app changes the url back to
http://ServerName/Reports/Pages/Folder.aspx
Is there anyway to have the url not change so that it would show
http://reports.company.com/Reports/Pages/Folder.aspx?
We are wanting to customize report manager a bit before we offer to the end
user communnity.
Thanks in advance,
spindlejDuh, I must have been on crack yesterdy. After thinking about it somemore
today, I realized that the default web site in IIS configuration was set to
foward to http://ServerName/Reports/
All I needed to do was change it to forward to
http://reports.company.com/Reports/ and now it works like a charm.
Thanks to me for figuring it out :)
"spindlej" wrote:
> Hopefully someone will know the answer to this one.
> We are trying to setup a url (reports.company.com) to point to Report
> Manager so that end users will not have to enter in a server name.
> This works initially, but the web app changes the url back to
> http://ServerName/Reports/Pages/Folder.aspx
> Is there anyway to have the url not change so that it would show
> http://reports.company.com/Reports/Pages/Folder.aspx?
> We are wanting to customize report manager a bit before we offer to the end
> user communnity.
> Thanks in advance,
> spindlej
Custom Sync Objects
sql2k sp3
Can someone point out to me the steps to take to use
Custom Sync Objects? My Publisher and Subscriber have
different schemas which is the reason for they're use.
Obviously I need to take into account the Insert, and
Update Procs, as well as the Subscriber schema. I could
see this taking quite a while until I get into the groove
of how this all works.
TIA, ChrisR
Hi chris,
I have a slightly similar issue where I wanted to change the ins stored
procs for a number of the tables on the subscriber. I ended up putting all
my alter proc commands in a PostSnapshot script and specifying that in the
sp_addpublication procedure. This runs after the snapshot so all the data
should be in the tables and you could run alter table.
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:380f01c47ef7$7cce1900$a501280a@.phx.gbl...
> sql2k sp3
> Can someone point out to me the steps to take to use
> Custom Sync Objects? My Publisher and Subscriber have
> different schemas which is the reason for they're use.
> Obviously I need to take into account the Insert, and
> Update Procs, as well as the Subscriber schema. I could
> see this taking quite a while until I get into the groove
> of how this all works.
> TIA, ChrisR
|||Thanks Mary!!!
>--Original Message--
>Hi chris,
>I have a slightly similar issue where I wanted to change
the ins stored
>procs for a number of the tables on the subscriber. I
ended up putting all
>my alter proc commands in a PostSnapshot script and
specifying that in the
>sp_addpublication procedure. This runs after the snapshot
so all the data
>should be in the tables and you could run alter table.
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:380f01c47ef7$7cce1900$a501280a@.phx.gbl...
groove
>
>.
>
|||You use custom sync objects when you want to bcp data for the snapshot which
will be sent to the Subscriber.
The custom sync object can send different data than what is in the
underlying object, or different schema, or a combination of the two.
To get this to work you need to
1) create the custom sync object - a view
2) tell SQL Server to use this custom sync object with the @.sync_object
parameter of sp_addarticle.
If you are replicating to a table with a different schema where the object
on the subscriber has more columns than the object on the publisher you have
to ask yourself this question. How am I going to fill in these columns as
the log reader will only fill in values for the columns which are present in
the underlying object on the Publisher?
If you are putting defaults on the additional columns on this object on the
Subscriber, its not a problem, but if you aren't you have to trick the log
reader into reading these additional columns. Most often you have to
replicate from a different source object, or replicate tables containing
these additional values, and then in your custom stored procedure do a join
to fill them in.
Here is an example:
CREATE VIEW Authors_View
AS
SELECT authors.au_id, titles.title_id, au_lname,
au_fname, phone, address, state, zip, contract, city,
title
FROM authors, titles, titleauthor
WHERE authors.au_id = titleauthor.au_id
AND titles.title_id = titleauthor.title_id
GO
sp_addarticle @.publication = 'test',
@.article = 'AuthorTitle',
@.source_object = 'AuthorTitle',
@.destination_table = 'AuthorTitle',
-Specifying that we are using a custom sync object
@.type = 'logbased manualview',
-Our sync object
@.sync_object='Authors_View',
-Our custom script
@.creation_script = 'c:\temp\AuthorTitle.sql',
@.pre_creation_cmd = 'delete',
-Specifying we want to auto generate our stored
procedures
@.schema_option = 0x02,
@.status = 8,
@.ins_cmd = 'CALL sp_MSins_AuthorTitle',
@.del_cmd = 'CALL sp_MSdel_AuthorTitle ',
@.upd_cmd = 'MCALL sp_MSupd_AuthorTitle '
GO
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:380f01c47ef7$7cce1900$a501280a@.phx.gbl...
> sql2k sp3
> Can someone point out to me the steps to take to use
> Custom Sync Objects? My Publisher and Subscriber have
> different schemas which is the reason for they're use.
> Obviously I need to take into account the Insert, and
> Update Procs, as well as the Subscriber schema. I could
> see this taking quite a while until I get into the groove
> of how this all works.
> TIA, ChrisR
|||That example wasn't the best - try this one.
create database CustomSyncObject
GO
create database CustomSyncObjectSub
GO
use CustomSyncObject
go
sp_dboption 'CustomSyncObject','published', 'true'
go
create table test(pk int not null primary key identity(1,1),
charcol1 char(20),
charcol2 char(20),
charcol3 char(20))
go
declare @.counter int
set @.counter=0
while @.counter<100
begin
insert into test (charcol1, charcol2, charcol3) values(getdate(),
System_user,@.counter)
select @.counter=@.counter+1
end
create View TestView
as
select charcol1, charcol2, charcol4=convert(char(20),'') From test
GO
sp_addpublication 'Custom', @.status='active'
GO
sp_addpublication_snapshot 'custom'
GO
sp_addarticle @.publication = 'Custom',
@.article = 'Custom',
@.source_object = 'test',
@.destination_table = 'TestWithDifferentSchema',
@.type = 'logbased manualview',
@.sync_object='TestView',
@.creation_script = 'c:\temp\TestWithDifferentSchema.sql',
@.pre_creation_cmd = 'delete',
@.schema_option = 0x0,
@.status = 8,
@.ins_cmd = 'CALL sp_MSins_CustomProc',
@.del_cmd = 'CALL sp_MSdel_CustomProc',
@.upd_cmd = 'MCALL sp_MSupd_CustomProc'
GO
sp_addsubscription 'Custom', 'Custom', @.@.Servername, 'CustomSyncObjectSub'
GO
Use CustomSyncObjectSub
GO
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSins_CustomProc') drop proc [sp_MSins_CustomProc]
go
create procedure [sp_MSins_CustomProc] @.c1 int,@.c2 char(20),@.c3 char(20),@.c4
char(20)
AS
BEGIN
insert into [TestWithDifferentSchema](
[pk], [charcol1], [charcol2]
)
values (
@.c1, @.c2, @.c3
)
END
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSdel_CustomProc') drop proc [sp_MSdel_CustomProc]
go
create procedure [sp_MSdel_CustomProc] @.pkc1 int
as
delete [TestWithDifferentSchema]
where [pk] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
GO
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSupd_CustomProc') drop proc [sp_MSupd_CustomProc]
go
create procedure [sp_MSupd_CustomProc]
@.c1 int,@.c2 char(20),@.c3 char(20),@.c4 char(20),@.pkc1 int
as
if @.c1 = @.pkc1
begin
update [TestWithDifferentSchema] set [charcol1] = @.c2,[charcol2] = @.c3
where [pk] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update [test] set [pk] = @.c1,[charcol1] = @.c2,[charcol2] = @.c3
where [pk] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
GO
Use CustomSyncObject
sp_repladdcolumn 'test','chrisRSpecial','char(20)'
insert into test (charcol1, charcol2,charcol3) values('test','test','test')
--before reinitializing we have to
--update custom sync object for newly added columns
--update replication stored procedurs
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uOzCC55fEHA.636@.TK2MSFTNGP12.phx.gbl...
> You use custom sync objects when you want to bcp data for the snapshot
which
> will be sent to the Subscriber.
> The custom sync object can send different data than what is in the
> underlying object, or different schema, or a combination of the two.
> To get this to work you need to
> 1) create the custom sync object - a view
> 2) tell SQL Server to use this custom sync object with the @.sync_object
> parameter of sp_addarticle.
> If you are replicating to a table with a different schema where the object
> on the subscriber has more columns than the object on the publisher you
have
> to ask yourself this question. How am I going to fill in these columns as
> the log reader will only fill in values for the columns which are present
in
> the underlying object on the Publisher?
> If you are putting defaults on the additional columns on this object on
the
> Subscriber, its not a problem, but if you aren't you have to trick the log
> reader into reading these additional columns. Most often you have to
> replicate from a different source object, or replicate tables containing
> these additional values, and then in your custom stored procedure do a
join
> to fill them in.
> Here is an example:
> CREATE VIEW Authors_View
> AS
> SELECT authors.au_id, titles.title_id, au_lname,
> au_fname, phone, address, state, zip, contract, city,
> title
> FROM authors, titles, titleauthor
> WHERE authors.au_id = titleauthor.au_id
> AND titles.title_id = titleauthor.title_id
> GO
> sp_addarticle @.publication = 'test',
> @.article = 'AuthorTitle',
> @.source_object = 'AuthorTitle',
> @.destination_table = 'AuthorTitle',
> -Specifying that we are using a custom sync object
> @.type = 'logbased manualview',
> -Our sync object
> @.sync_object='Authors_View',
> -Our custom script
> @.creation_script = 'c:\temp\AuthorTitle.sql',
> @.pre_creation_cmd = 'delete',
> -Specifying we want to auto generate our stored
> procedures
> @.schema_option = 0x02,
> @.status = 8,
> @.ins_cmd = 'CALL sp_MSins_AuthorTitle',
> @.del_cmd = 'CALL sp_MSdel_AuthorTitle ',
> @.upd_cmd = 'MCALL sp_MSupd_AuthorTitle '
> GO
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:380f01c47ef7$7cce1900$a501280a@.phx.gbl...
>
|||Thank you so much. As it turns out Ive been putting out a
day long fire and havent even tried any of it. Theres
always tomorrow. Thanks again.
>--Original Message--
>That example wasn't the best - try this one.
>create database CustomSyncObject
>GO
>create database CustomSyncObjectSub
>GO
>use CustomSyncObject
>go
>sp_dboption 'CustomSyncObject','published', 'true'
>go
>create table test(pk int not null primary key identity
(1,1),
>charcol1 char(20),
>charcol2 char(20),
>charcol3 char(20))
>go
>declare @.counter int
>set @.counter=0
>while @.counter<100
>begin
>insert into test (charcol1, charcol2, charcol3) values
(getdate(),
>System_user,@.counter)
>select @.counter=@.counter+1
>end
>create View TestView
>as
>select charcol1, charcol2, charcol4=convert(char(20),'')
From test
>GO
>sp_addpublication 'Custom', @.status='active'
>GO
>sp_addpublication_snapshot 'custom'
>GO
>sp_addarticle @.publication = 'Custom',
>@.article = 'Custom',
>@.source_object = 'test',
>@.destination_table = 'TestWithDifferentSchema',
>@.type = 'logbased manualview',
>@.sync_object='TestView',
>@.creation_script = 'c:\temp\TestWithDifferentSchema.sql',
>@.pre_creation_cmd = 'delete',
>@.schema_option = 0x0,
>@.status = 8,
>@.ins_cmd = 'CALL sp_MSins_CustomProc',
>@.del_cmd = 'CALL sp_MSdel_CustomProc',
>@.upd_cmd = 'MCALL sp_MSupd_CustomProc'
>GO
>sp_addsubscription 'Custom', 'Custom',
@.@.Servername, 'CustomSyncObjectSub'
>GO
>Use CustomSyncObjectSub
>GO
>if exists (select * from sysobjects where type = 'P' and
name =
>'sp_MSins_CustomProc') drop proc [sp_MSins_CustomProc]
>go
>create procedure [sp_MSins_CustomProc] @.c1 int,@.c2 char
(20),@.c3 char(20),@.c4
>char(20)
>AS
>BEGIN
>insert into [TestWithDifferentSchema](
>[pk], [charcol1], [charcol2]
> )
>values (
>@.c1, @.c2, @.c3
> )
>END
>go
>if exists (select * from sysobjects where type = 'P' and
name =
>'sp_MSdel_CustomProc') drop proc [sp_MSdel_CustomProc]
>go
>create procedure [sp_MSdel_CustomProc] @.pkc1 int
>as
>delete [TestWithDifferentSchema]
>where [pk] = @.pkc1
>if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
>GO
>if exists (select * from sysobjects where type = 'P' and
name =
>'sp_MSupd_CustomProc') drop proc [sp_MSupd_CustomProc]
>go
>create procedure [sp_MSupd_CustomProc]
> @.c1 int,@.c2 char(20),@.c3 char(20),@.c4 char(20),@.pkc1 int
>as
>if @.c1 = @.pkc1
>begin
>update [TestWithDifferentSchema] set [charcol1] = @.c2,
[charcol2] = @.c3
>where [pk] = @.pkc1
>if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
>end
>else
>begin
>update [test] set [pk] = @.c1,[charcol1] = @.c2,[charcol2]
= @.c3
>where [pk] = @.pkc1
>if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
>end
>GO
>Use CustomSyncObject
>sp_repladdcolumn 'test','chrisRSpecial','char(20)'
>insert into test (charcol1, charcol2,charcol3) values
('test','test','test')[vbcol=seagreen]
>--before reinitializing we have to
>--update custom sync object for newly added columns
>--update replication stored procedurs
>
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Hilary Cotter" <hilaryk@.att.net> wrote in message
>news:uOzCC55fEHA.636@.TK2MSFTNGP12.phx.gbl...
for the snapshot[vbcol=seagreen]
>which
what is in the[vbcol=seagreen]
combination of the two.[vbcol=seagreen]
the @.sync_object[vbcol=seagreen]
schema where the object[vbcol=seagreen]
the publisher you[vbcol=seagreen]
>have
in these columns as[vbcol=seagreen]
which are present[vbcol=seagreen]
>in
on this object on[vbcol=seagreen]
>the
have to trick the log[vbcol=seagreen]
often you have to[vbcol=seagreen]
tables containing[vbcol=seagreen]
procedure do a[vbcol=seagreen]
>join
message[vbcol=seagreen]
could[vbcol=seagreen]
groove
>
>.
>
Can someone point out to me the steps to take to use
Custom Sync Objects? My Publisher and Subscriber have
different schemas which is the reason for they're use.
Obviously I need to take into account the Insert, and
Update Procs, as well as the Subscriber schema. I could
see this taking quite a while until I get into the groove
of how this all works.
TIA, ChrisR
Hi chris,
I have a slightly similar issue where I wanted to change the ins stored
procs for a number of the tables on the subscriber. I ended up putting all
my alter proc commands in a PostSnapshot script and specifying that in the
sp_addpublication procedure. This runs after the snapshot so all the data
should be in the tables and you could run alter table.
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:380f01c47ef7$7cce1900$a501280a@.phx.gbl...
> sql2k sp3
> Can someone point out to me the steps to take to use
> Custom Sync Objects? My Publisher and Subscriber have
> different schemas which is the reason for they're use.
> Obviously I need to take into account the Insert, and
> Update Procs, as well as the Subscriber schema. I could
> see this taking quite a while until I get into the groove
> of how this all works.
> TIA, ChrisR
|||Thanks Mary!!!
>--Original Message--
>Hi chris,
>I have a slightly similar issue where I wanted to change
the ins stored
>procs for a number of the tables on the subscriber. I
ended up putting all
>my alter proc commands in a PostSnapshot script and
specifying that in the
>sp_addpublication procedure. This runs after the snapshot
so all the data
>should be in the tables and you could run alter table.
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:380f01c47ef7$7cce1900$a501280a@.phx.gbl...
groove
>
>.
>
|||You use custom sync objects when you want to bcp data for the snapshot which
will be sent to the Subscriber.
The custom sync object can send different data than what is in the
underlying object, or different schema, or a combination of the two.
To get this to work you need to
1) create the custom sync object - a view
2) tell SQL Server to use this custom sync object with the @.sync_object
parameter of sp_addarticle.
If you are replicating to a table with a different schema where the object
on the subscriber has more columns than the object on the publisher you have
to ask yourself this question. How am I going to fill in these columns as
the log reader will only fill in values for the columns which are present in
the underlying object on the Publisher?
If you are putting defaults on the additional columns on this object on the
Subscriber, its not a problem, but if you aren't you have to trick the log
reader into reading these additional columns. Most often you have to
replicate from a different source object, or replicate tables containing
these additional values, and then in your custom stored procedure do a join
to fill them in.
Here is an example:
CREATE VIEW Authors_View
AS
SELECT authors.au_id, titles.title_id, au_lname,
au_fname, phone, address, state, zip, contract, city,
title
FROM authors, titles, titleauthor
WHERE authors.au_id = titleauthor.au_id
AND titles.title_id = titleauthor.title_id
GO
sp_addarticle @.publication = 'test',
@.article = 'AuthorTitle',
@.source_object = 'AuthorTitle',
@.destination_table = 'AuthorTitle',
-Specifying that we are using a custom sync object
@.type = 'logbased manualview',
-Our sync object
@.sync_object='Authors_View',
-Our custom script
@.creation_script = 'c:\temp\AuthorTitle.sql',
@.pre_creation_cmd = 'delete',
-Specifying we want to auto generate our stored
procedures
@.schema_option = 0x02,
@.status = 8,
@.ins_cmd = 'CALL sp_MSins_AuthorTitle',
@.del_cmd = 'CALL sp_MSdel_AuthorTitle ',
@.upd_cmd = 'MCALL sp_MSupd_AuthorTitle '
GO
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:380f01c47ef7$7cce1900$a501280a@.phx.gbl...
> sql2k sp3
> Can someone point out to me the steps to take to use
> Custom Sync Objects? My Publisher and Subscriber have
> different schemas which is the reason for they're use.
> Obviously I need to take into account the Insert, and
> Update Procs, as well as the Subscriber schema. I could
> see this taking quite a while until I get into the groove
> of how this all works.
> TIA, ChrisR
|||That example wasn't the best - try this one.
create database CustomSyncObject
GO
create database CustomSyncObjectSub
GO
use CustomSyncObject
go
sp_dboption 'CustomSyncObject','published', 'true'
go
create table test(pk int not null primary key identity(1,1),
charcol1 char(20),
charcol2 char(20),
charcol3 char(20))
go
declare @.counter int
set @.counter=0
while @.counter<100
begin
insert into test (charcol1, charcol2, charcol3) values(getdate(),
System_user,@.counter)
select @.counter=@.counter+1
end
create View TestView
as
select charcol1, charcol2, charcol4=convert(char(20),'') From test
GO
sp_addpublication 'Custom', @.status='active'
GO
sp_addpublication_snapshot 'custom'
GO
sp_addarticle @.publication = 'Custom',
@.article = 'Custom',
@.source_object = 'test',
@.destination_table = 'TestWithDifferentSchema',
@.type = 'logbased manualview',
@.sync_object='TestView',
@.creation_script = 'c:\temp\TestWithDifferentSchema.sql',
@.pre_creation_cmd = 'delete',
@.schema_option = 0x0,
@.status = 8,
@.ins_cmd = 'CALL sp_MSins_CustomProc',
@.del_cmd = 'CALL sp_MSdel_CustomProc',
@.upd_cmd = 'MCALL sp_MSupd_CustomProc'
GO
sp_addsubscription 'Custom', 'Custom', @.@.Servername, 'CustomSyncObjectSub'
GO
Use CustomSyncObjectSub
GO
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSins_CustomProc') drop proc [sp_MSins_CustomProc]
go
create procedure [sp_MSins_CustomProc] @.c1 int,@.c2 char(20),@.c3 char(20),@.c4
char(20)
AS
BEGIN
insert into [TestWithDifferentSchema](
[pk], [charcol1], [charcol2]
)
values (
@.c1, @.c2, @.c3
)
END
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSdel_CustomProc') drop proc [sp_MSdel_CustomProc]
go
create procedure [sp_MSdel_CustomProc] @.pkc1 int
as
delete [TestWithDifferentSchema]
where [pk] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
GO
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSupd_CustomProc') drop proc [sp_MSupd_CustomProc]
go
create procedure [sp_MSupd_CustomProc]
@.c1 int,@.c2 char(20),@.c3 char(20),@.c4 char(20),@.pkc1 int
as
if @.c1 = @.pkc1
begin
update [TestWithDifferentSchema] set [charcol1] = @.c2,[charcol2] = @.c3
where [pk] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update [test] set [pk] = @.c1,[charcol1] = @.c2,[charcol2] = @.c3
where [pk] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
GO
Use CustomSyncObject
sp_repladdcolumn 'test','chrisRSpecial','char(20)'
insert into test (charcol1, charcol2,charcol3) values('test','test','test')
--before reinitializing we have to
--update custom sync object for newly added columns
--update replication stored procedurs
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uOzCC55fEHA.636@.TK2MSFTNGP12.phx.gbl...
> You use custom sync objects when you want to bcp data for the snapshot
which
> will be sent to the Subscriber.
> The custom sync object can send different data than what is in the
> underlying object, or different schema, or a combination of the two.
> To get this to work you need to
> 1) create the custom sync object - a view
> 2) tell SQL Server to use this custom sync object with the @.sync_object
> parameter of sp_addarticle.
> If you are replicating to a table with a different schema where the object
> on the subscriber has more columns than the object on the publisher you
have
> to ask yourself this question. How am I going to fill in these columns as
> the log reader will only fill in values for the columns which are present
in
> the underlying object on the Publisher?
> If you are putting defaults on the additional columns on this object on
the
> Subscriber, its not a problem, but if you aren't you have to trick the log
> reader into reading these additional columns. Most often you have to
> replicate from a different source object, or replicate tables containing
> these additional values, and then in your custom stored procedure do a
join
> to fill them in.
> Here is an example:
> CREATE VIEW Authors_View
> AS
> SELECT authors.au_id, titles.title_id, au_lname,
> au_fname, phone, address, state, zip, contract, city,
> title
> FROM authors, titles, titleauthor
> WHERE authors.au_id = titleauthor.au_id
> AND titles.title_id = titleauthor.title_id
> GO
> sp_addarticle @.publication = 'test',
> @.article = 'AuthorTitle',
> @.source_object = 'AuthorTitle',
> @.destination_table = 'AuthorTitle',
> -Specifying that we are using a custom sync object
> @.type = 'logbased manualview',
> -Our sync object
> @.sync_object='Authors_View',
> -Our custom script
> @.creation_script = 'c:\temp\AuthorTitle.sql',
> @.pre_creation_cmd = 'delete',
> -Specifying we want to auto generate our stored
> procedures
> @.schema_option = 0x02,
> @.status = 8,
> @.ins_cmd = 'CALL sp_MSins_AuthorTitle',
> @.del_cmd = 'CALL sp_MSdel_AuthorTitle ',
> @.upd_cmd = 'MCALL sp_MSupd_AuthorTitle '
> GO
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:380f01c47ef7$7cce1900$a501280a@.phx.gbl...
>
|||Thank you so much. As it turns out Ive been putting out a
day long fire and havent even tried any of it. Theres
always tomorrow. Thanks again.
>--Original Message--
>That example wasn't the best - try this one.
>create database CustomSyncObject
>GO
>create database CustomSyncObjectSub
>GO
>use CustomSyncObject
>go
>sp_dboption 'CustomSyncObject','published', 'true'
>go
>create table test(pk int not null primary key identity
(1,1),
>charcol1 char(20),
>charcol2 char(20),
>charcol3 char(20))
>go
>declare @.counter int
>set @.counter=0
>while @.counter<100
>begin
>insert into test (charcol1, charcol2, charcol3) values
(getdate(),
>System_user,@.counter)
>select @.counter=@.counter+1
>end
>create View TestView
>as
>select charcol1, charcol2, charcol4=convert(char(20),'')
From test
>GO
>sp_addpublication 'Custom', @.status='active'
>GO
>sp_addpublication_snapshot 'custom'
>GO
>sp_addarticle @.publication = 'Custom',
>@.article = 'Custom',
>@.source_object = 'test',
>@.destination_table = 'TestWithDifferentSchema',
>@.type = 'logbased manualview',
>@.sync_object='TestView',
>@.creation_script = 'c:\temp\TestWithDifferentSchema.sql',
>@.pre_creation_cmd = 'delete',
>@.schema_option = 0x0,
>@.status = 8,
>@.ins_cmd = 'CALL sp_MSins_CustomProc',
>@.del_cmd = 'CALL sp_MSdel_CustomProc',
>@.upd_cmd = 'MCALL sp_MSupd_CustomProc'
>GO
>sp_addsubscription 'Custom', 'Custom',
@.@.Servername, 'CustomSyncObjectSub'
>GO
>Use CustomSyncObjectSub
>GO
>if exists (select * from sysobjects where type = 'P' and
name =
>'sp_MSins_CustomProc') drop proc [sp_MSins_CustomProc]
>go
>create procedure [sp_MSins_CustomProc] @.c1 int,@.c2 char
(20),@.c3 char(20),@.c4
>char(20)
>AS
>BEGIN
>insert into [TestWithDifferentSchema](
>[pk], [charcol1], [charcol2]
> )
>values (
>@.c1, @.c2, @.c3
> )
>END
>go
>if exists (select * from sysobjects where type = 'P' and
name =
>'sp_MSdel_CustomProc') drop proc [sp_MSdel_CustomProc]
>go
>create procedure [sp_MSdel_CustomProc] @.pkc1 int
>as
>delete [TestWithDifferentSchema]
>where [pk] = @.pkc1
>if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
>GO
>if exists (select * from sysobjects where type = 'P' and
name =
>'sp_MSupd_CustomProc') drop proc [sp_MSupd_CustomProc]
>go
>create procedure [sp_MSupd_CustomProc]
> @.c1 int,@.c2 char(20),@.c3 char(20),@.c4 char(20),@.pkc1 int
>as
>if @.c1 = @.pkc1
>begin
>update [TestWithDifferentSchema] set [charcol1] = @.c2,
[charcol2] = @.c3
>where [pk] = @.pkc1
>if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
>end
>else
>begin
>update [test] set [pk] = @.c1,[charcol1] = @.c2,[charcol2]
= @.c3
>where [pk] = @.pkc1
>if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
>end
>GO
>Use CustomSyncObject
>sp_repladdcolumn 'test','chrisRSpecial','char(20)'
>insert into test (charcol1, charcol2,charcol3) values
('test','test','test')[vbcol=seagreen]
>--before reinitializing we have to
>--update custom sync object for newly added columns
>--update replication stored procedurs
>
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Hilary Cotter" <hilaryk@.att.net> wrote in message
>news:uOzCC55fEHA.636@.TK2MSFTNGP12.phx.gbl...
for the snapshot[vbcol=seagreen]
>which
what is in the[vbcol=seagreen]
combination of the two.[vbcol=seagreen]
the @.sync_object[vbcol=seagreen]
schema where the object[vbcol=seagreen]
the publisher you[vbcol=seagreen]
>have
in these columns as[vbcol=seagreen]
which are present[vbcol=seagreen]
>in
on this object on[vbcol=seagreen]
>the
have to trick the log[vbcol=seagreen]
often you have to[vbcol=seagreen]
tables containing[vbcol=seagreen]
procedure do a[vbcol=seagreen]
>join
message[vbcol=seagreen]
could[vbcol=seagreen]
groove
>
>.
>
Monday, March 19, 2012
Custom Report Item
Hello all,
Could any one point me to some documents on building custom report items.
Thanks!!
SqlNew
A good place to start would be the BOL (Books Online).Here is the starting point for Custom Report Items: http://msdn2.microsoft.com/en-us/library/ms345231.aspx
Custom Report Item
Hello.
I need to write a CRI which will update a attribute in the RDL file.
Can anyone point me in the right direction.
This is my view I
will have a textbox and a button on the CRI.
While building a report through BI , the report author adds CRI.
enter a value in the text box and click ok.
Then the text in the textbox needs to be added to the RDL file.
Thanks!!
RadhikaHello,
Does any one have ideas.
Thanks!!
"Radhika" wrote:
> Hello.
> I need to write a CRI which will update a attribute in the RDL file.
> Can anyone point me in the right direction.
> This is my view I
> will have a textbox and a button on the CRI.
> While building a report through BI , the report author adds CRI.
> enter a value in the text box and click ok.
> Then the text in the textbox needs to be added to the RDL file.
>
> Thanks!!
> Radhika
I need to write a CRI which will update a attribute in the RDL file.
Can anyone point me in the right direction.
This is my view I
will have a textbox and a button on the CRI.
While building a report through BI , the report author adds CRI.
enter a value in the text box and click ok.
Then the text in the textbox needs to be added to the RDL file.
Thanks!!
RadhikaHello,
Does any one have ideas.
Thanks!!
"Radhika" wrote:
> Hello.
> I need to write a CRI which will update a attribute in the RDL file.
> Can anyone point me in the right direction.
> This is my view I
> will have a textbox and a button on the CRI.
> While building a report through BI , the report author adds CRI.
> enter a value in the text box and click ok.
> Then the text in the textbox needs to be added to the RDL file.
>
> Thanks!!
> Radhika
Sunday, March 11, 2012
Custom Properties and PDF Properties
We need to generate PDF files as our end point. These files must contain
'custom properties' (such as Author, Title, 'What Have You') which will be
used to index the documents for searches. We will generate our reports
using Reporting Services and then 'export' them to PDF format. We are not
sure that
Reporting Services "CustomProperties" will work to achive this goal. We
also have seen no working code examples of "CustomProperties". Do anyone
have anything to contribute to these questions? Thanks in advance. dlrHello Dennis,
Based on my scope, "CustomProperties" of pdf is not supported for pdf
rendering extention. Customproperties settings in SRS are used when
processing custom report item control. You may want to click "send
feedback" toolbar in SQL 2005 BOL to send your feedback on this to product
team.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: "D Redfield" <dennis_redfield@.newsgroup.nospam>
>Subject: Custom Properties and PDF Properties
>Date: Tue, 7 Feb 2006 16:13:25 -0500
>Lines: 10
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
>Message-ID: <#LAdWtCLGHA.3496@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: rrcs-24-39-49-178.nys.biz.rr.com 24.39.49.178
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:68257
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>We need to generate PDF files as our end point. These files must contain
>'custom properties' (such as Author, Title, 'What Have You') which will be
>used to index the documents for searches. We will generate our reports
>using Reporting Services and then 'export' them to PDF format. We are not
>sure that
>Reporting Services "CustomProperties" will work to achive this goal. We
>also have seen no working code examples of "CustomProperties". Do anyone
>have anything to contribute to these questions? Thanks in advance. dlr
>
>
'custom properties' (such as Author, Title, 'What Have You') which will be
used to index the documents for searches. We will generate our reports
using Reporting Services and then 'export' them to PDF format. We are not
sure that
Reporting Services "CustomProperties" will work to achive this goal. We
also have seen no working code examples of "CustomProperties". Do anyone
have anything to contribute to these questions? Thanks in advance. dlrHello Dennis,
Based on my scope, "CustomProperties" of pdf is not supported for pdf
rendering extention. Customproperties settings in SRS are used when
processing custom report item control. You may want to click "send
feedback" toolbar in SQL 2005 BOL to send your feedback on this to product
team.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: "D Redfield" <dennis_redfield@.newsgroup.nospam>
>Subject: Custom Properties and PDF Properties
>Date: Tue, 7 Feb 2006 16:13:25 -0500
>Lines: 10
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
>Message-ID: <#LAdWtCLGHA.3496@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: rrcs-24-39-49-178.nys.biz.rr.com 24.39.49.178
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:68257
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>We need to generate PDF files as our end point. These files must contain
>'custom properties' (such as Author, Title, 'What Have You') which will be
>used to index the documents for searches. We will generate our reports
>using Reporting Services and then 'export' them to PDF format. We are not
>sure that
>Reporting Services "CustomProperties" will work to achive this goal. We
>also have seen no working code examples of "CustomProperties". Do anyone
>have anything to contribute to these questions? Thanks in advance. dlr
>
>
Thursday, March 8, 2012
Custom Grouping
Can someone please point me in the direction of how to create a custom grouping for an attribute? I have an 'age' attribute of an item that tracks how old it is. Let's assume this age is always between 1 and 30 days. Regardless of the distribution of the ages, I'd like to have specific ranges defined according to our business' tracking rules: 1 - 3 days, 4 - 10 days, 11 - 20 days and 21 - 30 days.
The DiscretizationMethods available will not produce these types of grouping ranges for me. What am I missing?
Thanks!
Kirun
You might have to create a two level hierarchy in the age dimension with your custom groups as the first level.Friday, February 24, 2012
Custom Code - Reference Report Objects
Hi,
Can anyone point me in the right direction on how to reference report objects in a custom sub or function. For example if I want to assign a value to a report parameter or a textbox
public Sub SetDepth()
Parameters!test.Value = 2
ReportItems!Textbox23.value = "test"
end sub
I've tried really simle stuff like below but i get the following error [BC30469] Reference to a non-shared member requires an object reference.
Cheers
KevinYou can pass in the Parameters or ReportItems collection as an argument to
your function. Note you can't change the values of parameters/textboxes
though - they are read-only.
Fang Wang (MSFT)
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
news:E136C0ED-9890-4315-8455-14687D66FB24@.microsoft.com...
> Hi,
> Can anyone point me in the right direction on how to reference report
objects in a custom sub or function. For example if I want to assign a value
to a report parameter or a textbox
> public Sub SetDepth()
> Parameters!test.Value = 2
> ReportItems!Textbox23.value = "test"
> end sub
> I've tried really simle stuff like below but i get the following error
[BC30469] Reference to a non-shared member requires an object reference.
> Cheers
> Kevin|||How do you do this?
"Fang Wang (MSFT)" wrote:
> You can pass in the Parameters or ReportItems collection as an argument to
> your function. Note you can't change the values of parameters/textboxes
> though - they are read-only.
> Fang Wang (MSFT)
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
> news:E136C0ED-9890-4315-8455-14687D66FB24@.microsoft.com...
> > Hi,
> > Can anyone point me in the right direction on how to reference report
> objects in a custom sub or function. For example if I want to assign a value
> to a report parameter or a textbox
> >
> > public Sub SetDepth()
> > Parameters!test.Value = 2
> > ReportItems!Textbox23.value = "test"
> > end sub
> >
> > I've tried really simle stuff like below but i get the following error
> [BC30469] Reference to a non-shared member requires an object reference.
> >
> > Cheers
> > Kevin
>
>|||For example if you want to calculate average sales. You have two fields
Number of Units and Sales in dollars.
You can write custom code as below:
Public Function GetAvg(ByVal NoOfUnits As Integer, ByVal TotalSales As
Decimal) As Decimal
Dim ReturnValue As Decimal
ReturnValue = 0
If NoOfUnits <> 0 Then
ReturnValue = TotalSales/NoOfUnits
End If
Return ReturnValue
End
In your table cell you can write expression as:
Code.GetAvg(Fields!Units.Value, Fields!Sales.Value)
When you preview the report, you will see the calculated average value in
that cell.
I hope this helps.
"Tanya" <Tanya@.discussions.microsoft.com> wrote in message
news:68055C09-D38E-4CDA-990C-CD4641594E33@.microsoft.com...
> How do you do this?
> "Fang Wang (MSFT)" wrote:
>> You can pass in the Parameters or ReportItems collection as an argument
>> to
>> your function. Note you can't change the values of parameters/textboxes
>> though - they are read-only.
>> Fang Wang (MSFT)
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
>> news:E136C0ED-9890-4315-8455-14687D66FB24@.microsoft.com...
>> > Hi,
>> > Can anyone point me in the right direction on how to reference report
>> objects in a custom sub or function. For example if I want to assign a
>> value
>> to a report parameter or a textbox
>> >
>> > public Sub SetDepth()
>> > Parameters!test.Value = 2
>> > ReportItems!Textbox23.value = "test"
>> > end sub
>> >
>> > I've tried really simle stuff like below but i get the following error
>> [BC30469] Reference to a non-shared member requires an object reference.
>> >
>> > Cheers
>> > Kevin
>>
Can anyone point me in the right direction on how to reference report objects in a custom sub or function. For example if I want to assign a value to a report parameter or a textbox
public Sub SetDepth()
Parameters!test.Value = 2
ReportItems!Textbox23.value = "test"
end sub
I've tried really simle stuff like below but i get the following error [BC30469] Reference to a non-shared member requires an object reference.
Cheers
KevinYou can pass in the Parameters or ReportItems collection as an argument to
your function. Note you can't change the values of parameters/textboxes
though - they are read-only.
Fang Wang (MSFT)
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
news:E136C0ED-9890-4315-8455-14687D66FB24@.microsoft.com...
> Hi,
> Can anyone point me in the right direction on how to reference report
objects in a custom sub or function. For example if I want to assign a value
to a report parameter or a textbox
> public Sub SetDepth()
> Parameters!test.Value = 2
> ReportItems!Textbox23.value = "test"
> end sub
> I've tried really simle stuff like below but i get the following error
[BC30469] Reference to a non-shared member requires an object reference.
> Cheers
> Kevin|||How do you do this?
"Fang Wang (MSFT)" wrote:
> You can pass in the Parameters or ReportItems collection as an argument to
> your function. Note you can't change the values of parameters/textboxes
> though - they are read-only.
> Fang Wang (MSFT)
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
> news:E136C0ED-9890-4315-8455-14687D66FB24@.microsoft.com...
> > Hi,
> > Can anyone point me in the right direction on how to reference report
> objects in a custom sub or function. For example if I want to assign a value
> to a report parameter or a textbox
> >
> > public Sub SetDepth()
> > Parameters!test.Value = 2
> > ReportItems!Textbox23.value = "test"
> > end sub
> >
> > I've tried really simle stuff like below but i get the following error
> [BC30469] Reference to a non-shared member requires an object reference.
> >
> > Cheers
> > Kevin
>
>|||For example if you want to calculate average sales. You have two fields
Number of Units and Sales in dollars.
You can write custom code as below:
Public Function GetAvg(ByVal NoOfUnits As Integer, ByVal TotalSales As
Decimal) As Decimal
Dim ReturnValue As Decimal
ReturnValue = 0
If NoOfUnits <> 0 Then
ReturnValue = TotalSales/NoOfUnits
End If
Return ReturnValue
End
In your table cell you can write expression as:
Code.GetAvg(Fields!Units.Value, Fields!Sales.Value)
When you preview the report, you will see the calculated average value in
that cell.
I hope this helps.
"Tanya" <Tanya@.discussions.microsoft.com> wrote in message
news:68055C09-D38E-4CDA-990C-CD4641594E33@.microsoft.com...
> How do you do this?
> "Fang Wang (MSFT)" wrote:
>> You can pass in the Parameters or ReportItems collection as an argument
>> to
>> your function. Note you can't change the values of parameters/textboxes
>> though - they are read-only.
>> Fang Wang (MSFT)
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
>> news:E136C0ED-9890-4315-8455-14687D66FB24@.microsoft.com...
>> > Hi,
>> > Can anyone point me in the right direction on how to reference report
>> objects in a custom sub or function. For example if I want to assign a
>> value
>> to a report parameter or a textbox
>> >
>> > public Sub SetDepth()
>> > Parameters!test.Value = 2
>> > ReportItems!Textbox23.value = "test"
>> > end sub
>> >
>> > I've tried really simle stuff like below but i get the following error
>> [BC30469] Reference to a non-shared member requires an object reference.
>> >
>> > Cheers
>> > Kevin
>>
Sunday, February 19, 2012
Custom Assembly Reading from Registry
Can anyone point me to an example of a MS SRS 2005 custom assembly reading
from the registry? We have had this issue on/off for several months, and we
are at the breaking point. The assembly has to access our database and we
want to store the connection information in the registry for shared use with
other components.
The network admins assure us that all users have read access to the
registry. It appears that we have granted our assembly fulltrust via
entries in the rs srs policy file. For a while were were using MS SRS 2000
and a temp work-around was to disable Code Access Security (CAS). We have
since upgraded to MS SRS 2005 and that work-around does not appear to work
anymore as our admin cannot seem to turn-off CAS (not that that is the
solution we want). Our code appears to fail when we assert read permission
on our registry key.
We're no longer in a trial-and-error approach to solving this. We've spent
way too long on something that, in our opinion, should be fairly simple. A
working example of a custom assembly, including policy file entiries, that
reads string values from the registry would be very welcome.
Any help would be appreciated.
Thanks,
ChrisHi Chris,
Thank you for your posting!
My understanding of this issue is: You want use custom assembly to access
the Registry in Reporting Services 2005. If I misunderstood your concern,
please feel free to let me know.
Based on my scope, you could turn-off CAS temporarily and try to run your
assembly.
Remember, in .NET 2005, you could only turn off CAS by using the Caspol.exe
temporarily. If you press <Enter> then you will restore the setting and
turn on the CAS.
If you could access the registry when you turn-off the CAS, then please
let me know what change have you done on your policy config file and post
the changes here.
If you still can not access the registry when you turn-off the CAS, we need
to start the Regmon to monitor the registry. You could download this tool
from the following site:
http://www.sysinternals.com/Utilities/Regmon.html.
Please let me know the result and so that I can provide further assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Chris ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||I apologize for the long delay. Other matters have taken precedence, but I
hope to return to this next week. I will try disabling CAS again to see if
my policy entry is the culprit and I'll let you know the results.
Thanks,
Chris
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:tcrT04ApGHA.4612@.TK2MSFTNGXA01.phx.gbl...
> Hi Chris ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Chris,
Thanks for your followup. Since Wei is OOF due to some urgent business,
I'll continue to help you on this issue. And sure, please feel free to
manage your first priority work and just let me know when you get any
progress or need any further assistance. We'll hold this thread and closely
monitoring it.
Looking forward to your update!
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||This is a multi-part message in MIME format.
--=_NextPart_000_000C_01C6AA84.480797E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I executed my report that has a custom assemby that reads from the =registery. As previously described, it failed with the following error:
Request for the permission of type ='System.Security.Permissions.RegistryPermission, mscorlib, =Version=3D2.0.0.0, Culture=3Dneutral, PublicKeyToken=3Db77a5c561934e089' =failed
As directed I disabled code access security (caspol -s off). Prior to =pressing <enter>, I re-executed the report and still received the same =error. I both refreshed the report, and also closed the browser and =re-logged in to execute the report. No change.
My rssrvpolicy.config entry for my custom assembly is below.
<CodeGroup class=3D"UnionCodeGroup"
version=3D"1"
PermissionSetName=3D"FullTrust"
Name=3D"cgBBSDataFormatter"
Description=3D"">
<IMembershipCondition class=3D"UrlMembershipCondition"
version=3D"1"
Url=3D"file://D:/Program Files/Microsoft SQL =Server/MSSQL.3/Reporting =Services/ReportServer/bin/BBSDataFormatter.dll"/>
</CodeGroup>
He is nested within the following codegroup entry:
<CodeGroup class=3D"FirstMatchCodeGroup" version=3D"1" PermissionSetName=3D"Execution"
Description=3D"This code group grants MyComputer code Execution =permission. ">
<IMembershipCondition class=3D"ZoneMembershipCondition"
version=3D"1"
Zone=3D"MyComputer" />
Any help would be greatly appreciated.
- Chris
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message =news:eedfRYYqGHA.4188@.TK2MSFTNGXA01.phx.gbl...
> Hi Chris,
> > Thanks for your followup. Since Wei is OOF due to some urgent =business, > I'll continue to help you on this issue. And sure, please feel free to =
> manage your first priority work and just let me know when you get any > progress or need any further assistance. We'll hold this thread and =closely > monitoring it.
> > Looking forward to your update!
> > Sincerely,
> > Steven Cheng
> > Microsoft MSDN Online Support Lead
> > ==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > This posting is provided "AS IS" with no warranties, and confers no =rights.
>
--=_NextPart_000_000C_01C6AA84.480797E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I executed my report that has a custom =assemby that reads from the registery. As previously described, it failed with =the following error:
Request for the permission of =type 'System.Security.Permissions.RegistryPermission, mscorlib, =Version=3D2.0.0.0, Culture=3Dneutral, PublicKeyToken=3Db77a5c561934e089' failed
As directed I disabled code access =security (caspol -s off). Prior to pressing , I re-executed the report =and still received the same error. I both refreshed the report, and =also closed the browser and re-logged in to execute the report. No change.
My rssrvpolicy.config entry for my =custom assembly is below.
He is nested within the following =codegroup entry:
Any help would be greatly =appreciated.
- Chris
"Steven Cheng[MSFT]" wrote in message news:eedfRYYqGHA.4188@.TK2MSFTNGXA01.phx.gbl...> =Hi Chris,> > Thanks for your followup. Since Wei is OOF due to some urgent =business, > I'll continue to help you on this issue. And sure, please feel =free to > manage your first priority work and just let me know when you =get any > progress or need any further assistance. We'll hold this thread =and closely > monitoring it.> > Looking forward to your =update!> > Sincerely,> > Steven =Cheng> > Microsoft MSDN Online Support Lead> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D> => This posting is provided "AS IS" with no warranties, and confers no rights.>
--=_NextPart_000_000C_01C6AA84.480797E0--|||Hi Chris,
Thanks a lot for your prompt response and the further detailed information.
After reading the security policy file you posted, I found that you use the
"UnionCodeGroup" for your custom codegroup type, I think it would be
better to use the "FirstMatchCodeGroup" which will exclude any other
matched CodeGroup and you need to put it as before other groups as possible
so that it will be evaluated and matched first in the policy file. The
custom codegroup element's content is ok from my view.
In addition, I've performed some tests on my local environment and noticed
some other problems here:
In our custom assembly's code there are two things we need to take care:
1. Make sure that our assembly is marked as
"AllowPartiallyTrustedCallersAttribute", such as:
[assembly:AllowPartiallyTrustedCallers]
2. Before we calling the registry code(or access any restricted resource),
we need to construct the certain Permission instance and Assert it. e.g:
public class Class1
{
public static string Test()
{
RegistryPermission perm = new
RegistryPermission(RegistryPermissionAccess.Read,
@."HKEY_LOCAL_MACHINE\SOFTWARE");
perm.Assert();
RegistryKey regkey =Registry.LocalMachine.OpenSubKey("Software", false);
int i = regkey.SubKeyCount;
return "LocalMachine_Software_SubKeyCount: " + i;
}
}
Here is a web article which has demonstrate a complete sample(it is
performing file access in custom assembly) and it has mentioned many things
we need to take care when developing and deploying custom assemby.
http://www.c-sharpcorner.com/Code/2005/June/CustomAssemblyinRS.asp
BTW, here is my custom codegroup section in the policy file:
===============<CodeGroup
class="FirstMatchCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="MyCustomAssemblyCodeGroup"
Description="A special code group for my custom assembly.">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\bin\CustomAssembly.dll"/>
</CodeGroup>
===================
Further more, if you still meet problem on this and if you feel convenient,
you can provide me a copy or code of your simplified custom assembly(which
can reproduce the problem behavior) so that I can do some further test on
my side.
Hope the above info helps.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Chris,
Have you got any further process on this issue, or does the information in
my last reply also helps on this issue?
Curerntly we're still closely monitoring this issue, if you still have any
questions or if there is anything we can help, please feel free to post
here.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Steven,
Modifying the policy file as suggested did the trick. We had previously
implemented the other two suggestions when we first ran into this problem.
Thank you for your help.
Regards,
Chris
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:6UHfrWNrGHA.4272@.TK2MSFTNGXA01.phx.gbl...
> Hello Chris,
> Have you got any further process on this issue, or does the information in
> my last reply also helps on this issue?
> Curerntly we're still closely monitoring this issue, if you still have any
> questions or if there is anything we can help, please feel free to post
> here.
> Sincerely,
> Steven Cheng
> Microsoft MSDN Online Support Lead
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Thanks for your followup Chris,
Glad to hear the good news.
Have a good day!
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
from the registry? We have had this issue on/off for several months, and we
are at the breaking point. The assembly has to access our database and we
want to store the connection information in the registry for shared use with
other components.
The network admins assure us that all users have read access to the
registry. It appears that we have granted our assembly fulltrust via
entries in the rs srs policy file. For a while were were using MS SRS 2000
and a temp work-around was to disable Code Access Security (CAS). We have
since upgraded to MS SRS 2005 and that work-around does not appear to work
anymore as our admin cannot seem to turn-off CAS (not that that is the
solution we want). Our code appears to fail when we assert read permission
on our registry key.
We're no longer in a trial-and-error approach to solving this. We've spent
way too long on something that, in our opinion, should be fairly simple. A
working example of a custom assembly, including policy file entiries, that
reads string values from the registry would be very welcome.
Any help would be appreciated.
Thanks,
ChrisHi Chris,
Thank you for your posting!
My understanding of this issue is: You want use custom assembly to access
the Registry in Reporting Services 2005. If I misunderstood your concern,
please feel free to let me know.
Based on my scope, you could turn-off CAS temporarily and try to run your
assembly.
Remember, in .NET 2005, you could only turn off CAS by using the Caspol.exe
temporarily. If you press <Enter> then you will restore the setting and
turn on the CAS.
If you could access the registry when you turn-off the CAS, then please
let me know what change have you done on your policy config file and post
the changes here.
If you still can not access the registry when you turn-off the CAS, we need
to start the Regmon to monitor the registry. You could download this tool
from the following site:
http://www.sysinternals.com/Utilities/Regmon.html.
Please let me know the result and so that I can provide further assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Chris ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||I apologize for the long delay. Other matters have taken precedence, but I
hope to return to this next week. I will try disabling CAS again to see if
my policy entry is the culprit and I'll let you know the results.
Thanks,
Chris
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:tcrT04ApGHA.4612@.TK2MSFTNGXA01.phx.gbl...
> Hi Chris ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Chris,
Thanks for your followup. Since Wei is OOF due to some urgent business,
I'll continue to help you on this issue. And sure, please feel free to
manage your first priority work and just let me know when you get any
progress or need any further assistance. We'll hold this thread and closely
monitoring it.
Looking forward to your update!
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||This is a multi-part message in MIME format.
--=_NextPart_000_000C_01C6AA84.480797E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I executed my report that has a custom assemby that reads from the =registery. As previously described, it failed with the following error:
Request for the permission of type ='System.Security.Permissions.RegistryPermission, mscorlib, =Version=3D2.0.0.0, Culture=3Dneutral, PublicKeyToken=3Db77a5c561934e089' =failed
As directed I disabled code access security (caspol -s off). Prior to =pressing <enter>, I re-executed the report and still received the same =error. I both refreshed the report, and also closed the browser and =re-logged in to execute the report. No change.
My rssrvpolicy.config entry for my custom assembly is below.
<CodeGroup class=3D"UnionCodeGroup"
version=3D"1"
PermissionSetName=3D"FullTrust"
Name=3D"cgBBSDataFormatter"
Description=3D"">
<IMembershipCondition class=3D"UrlMembershipCondition"
version=3D"1"
Url=3D"file://D:/Program Files/Microsoft SQL =Server/MSSQL.3/Reporting =Services/ReportServer/bin/BBSDataFormatter.dll"/>
</CodeGroup>
He is nested within the following codegroup entry:
<CodeGroup class=3D"FirstMatchCodeGroup" version=3D"1" PermissionSetName=3D"Execution"
Description=3D"This code group grants MyComputer code Execution =permission. ">
<IMembershipCondition class=3D"ZoneMembershipCondition"
version=3D"1"
Zone=3D"MyComputer" />
Any help would be greatly appreciated.
- Chris
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message =news:eedfRYYqGHA.4188@.TK2MSFTNGXA01.phx.gbl...
> Hi Chris,
> > Thanks for your followup. Since Wei is OOF due to some urgent =business, > I'll continue to help you on this issue. And sure, please feel free to =
> manage your first priority work and just let me know when you get any > progress or need any further assistance. We'll hold this thread and =closely > monitoring it.
> > Looking forward to your update!
> > Sincerely,
> > Steven Cheng
> > Microsoft MSDN Online Support Lead
> > ==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > This posting is provided "AS IS" with no warranties, and confers no =rights.
>
--=_NextPart_000_000C_01C6AA84.480797E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I executed my report that has a custom =assemby that reads from the registery. As previously described, it failed with =the following error:
Request for the permission of =type 'System.Security.Permissions.RegistryPermission, mscorlib, =Version=3D2.0.0.0, Culture=3Dneutral, PublicKeyToken=3Db77a5c561934e089' failed
As directed I disabled code access =security (caspol -s off). Prior to pressing , I re-executed the report =and still received the same error. I both refreshed the report, and =also closed the browser and re-logged in to execute the report. No change.
My rssrvpolicy.config entry for my =custom assembly is below.
He is nested within the following =codegroup entry:
Any help would be greatly =appreciated.
- Chris
"Steven Cheng[MSFT]"
--=_NextPart_000_000C_01C6AA84.480797E0--|||Hi Chris,
Thanks a lot for your prompt response and the further detailed information.
After reading the security policy file you posted, I found that you use the
"UnionCodeGroup" for your custom codegroup type, I think it would be
better to use the "FirstMatchCodeGroup" which will exclude any other
matched CodeGroup and you need to put it as before other groups as possible
so that it will be evaluated and matched first in the policy file. The
custom codegroup element's content is ok from my view.
In addition, I've performed some tests on my local environment and noticed
some other problems here:
In our custom assembly's code there are two things we need to take care:
1. Make sure that our assembly is marked as
"AllowPartiallyTrustedCallersAttribute", such as:
[assembly:AllowPartiallyTrustedCallers]
2. Before we calling the registry code(or access any restricted resource),
we need to construct the certain Permission instance and Assert it. e.g:
public class Class1
{
public static string Test()
{
RegistryPermission perm = new
RegistryPermission(RegistryPermissionAccess.Read,
@."HKEY_LOCAL_MACHINE\SOFTWARE");
perm.Assert();
RegistryKey regkey =Registry.LocalMachine.OpenSubKey("Software", false);
int i = regkey.SubKeyCount;
return "LocalMachine_Software_SubKeyCount: " + i;
}
}
Here is a web article which has demonstrate a complete sample(it is
performing file access in custom assembly) and it has mentioned many things
we need to take care when developing and deploying custom assemby.
http://www.c-sharpcorner.com/Code/2005/June/CustomAssemblyinRS.asp
BTW, here is my custom codegroup section in the policy file:
===============<CodeGroup
class="FirstMatchCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="MyCustomAssemblyCodeGroup"
Description="A special code group for my custom assembly.">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\bin\CustomAssembly.dll"/>
</CodeGroup>
===================
Further more, if you still meet problem on this and if you feel convenient,
you can provide me a copy or code of your simplified custom assembly(which
can reproduce the problem behavior) so that I can do some further test on
my side.
Hope the above info helps.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Chris,
Have you got any further process on this issue, or does the information in
my last reply also helps on this issue?
Curerntly we're still closely monitoring this issue, if you still have any
questions or if there is anything we can help, please feel free to post
here.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Steven,
Modifying the policy file as suggested did the trick. We had previously
implemented the other two suggestions when we first ran into this problem.
Thank you for your help.
Regards,
Chris
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:6UHfrWNrGHA.4272@.TK2MSFTNGXA01.phx.gbl...
> Hello Chris,
> Have you got any further process on this issue, or does the information in
> my last reply also helps on this issue?
> Curerntly we're still closely monitoring this issue, if you still have any
> questions or if there is anything we can help, please feel free to post
> here.
> Sincerely,
> Steven Cheng
> Microsoft MSDN Online Support Lead
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Thanks for your followup Chris,
Glad to hear the good news.
Have a good day!
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to:
Posts (Atom)