customize template.ini to upgrade from msde to express edition?

I need to create the script to upgrade the current named instance of sql 2000 desktop engine to sql server 2005 express edition. I am having difficulty preparing the template.ini and would appreciate help.

[Options]
INSTANCENAME=MY_INSTANCE
SQLBROWSERACCOUNT=NT AUTHORITY\NETWORK SERVICE
SECURITYMODE=SQL
SAPWD=music
UPGRADE=SQL_Engine
SQLBROWSERAUTOSTART=1
RSCANINSTALLDEFAULT=0
RSCONFIGURATION=FilesOnly
RSSQLLOCAL=0

I get an error that this file is invalid..Any clues? I am trying to upgrade existing instance of msde 2k to sql express 2005 and uses sql authentication

http://www.microsoft.com/sql/editions/express/upgrade.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx

http://msdn2.microsoft.com/en-us/ms143491.aspx

|||

When a parameter includes space characters, you need quote with " or '. For example, the browser account should be specified as SQLBROWSERACCOUNT="NT AUTHORITY\NETWORK SERVICE " or SQLBROWSERACCOUNT='NT AUTHORITY\NETWORK SERVICE'

Note, this account is localized on some localized operating system. You need to use the localized version if required. In addition, MSDE already has its SP4. So please use MSDE SP4 directly.

The following are two command lines that may be useful to you.

1. Install MSDE SP4.

start /wait setup.exe /qb INSTANCENAME=msde4instance SAPWD="<password>" SECURITYMODE=SQL

2. Upgrade to SQL 2005 SP1.

start /wait setup.exe UPGRADE=SQL_Engine INSTANCENAME=msdesp4instance SAPWD=""<password>" SECURITYMODE=SQL

customizable report model ?

Hi friends
we created report model for our clients to create reports using report builder which works fine.
we have a situation now , as we've different clients for e.g.,individual doctors, hospitals etc.,
so we need to have smart report model which hides some entities/attributes if client is individual doctors or hospitals.
what i mean some entities and attributes are unique to a particular type of client. so we want to hide some entities/attributes based on client type .
is it possible.?
Thanks for ur help.
we are using sql server 2005 standard edition.

You can use model item security to control the visibility of various entities and attributes to different sets of users.

I have an article on my blog that goes into some depth on how to do this.

|||Thanks BOB.i'll have a read of this article :)

Customising report Manager

Hi guys
I just want user see only content tab in Report Manager and not the property
tab .
I create a new role and select 'VIEW ROLE' and 'VIEW REPORT' tasks,
descriptions says that user will be able to see the properties too. I dont
want let user see the property.
Niether should user see the subscription link.
How could I achieve that
Sincerely
AmeetI don't think it is possible to give a set of permissions where a user will
be able to view the report and not see the properties of the report. If you
don't 'Manage Individual Subscriptions' or 'Manage All Subscriptions'
permissions then they will not see the subscription tab.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ameet" <Ameet@.discussions.microsoft.com> wrote in message
news:2A20E4F6-1158-4845-8032-69DA31902816@.microsoft.com...
> Hi guys
> I just want user see only content tab in Report Manager and not the
property
> tab .
> I create a new role and select 'VIEW ROLE' and 'VIEW REPORT' tasks,
> descriptions says that user will be able to see the properties too. I dont
> want let user see the property.
> Niether should user see the subscription link.
> How could I achieve that
> Sincerely
> Ameetsql

custom(and dynamic) record fields query

Hello,
I need to create a query that depends on custom fields (virtual).
First I'll try to outline the background.
I got Nodes table, that has NodeID field, and other (not really important)
fields like name, some boolean fields and so on.
Lets define node as an item for sale (just for the example purposes).
User of the application can create custom attribute values for each each
item (node).
To make the attribute names consistant through application I got another
table Attributes (AttID, AttributeName)
And the last table is association table between Node and Attributes
containing the value of the attribute as well.
I'd like to get following resultset:
NodeID, NodeName, CustomField1, CustomField2, CustomField3, ... and so on
(names of the fields would depend on Attribute name)
with values:
1,FirstNode, 'one of the values', 'another value', NULL, ...
2,SecondNode, NULL, NULL, NULL , ...
3,ThirdNode, NULL, NULL, 'some value', ...
SQL for creation such tables would look like (I ommited constrains and
indexes for siplicity):
CREATE TABLE Nodes (
NodeID INT IDENTITY (1, 1) NOT NULL,
NodeName NVARCHAR(255) NULL
)
CREATE TABLE Attributes (
AttID INT IDENTITY (1, 1) NOT NULL,
AttributeName NVARCHAR(50) NOT NULL
)
CREATE TABLE NodeAttr (
NodeID INT NOT NULL,
AttID INT NOT NULL,
Value NVARCHAR(255)
)
some data insertion:
INSERT INTO Nodes(NodeName) VALUES ('FirstNode')
INSERT INTO Nodes(NodeName) VALUES ('SecondNode')
INSERT INTO Nodes(NodeName) VALUES ('ThirdNode')
INSERT INTO Nodes(NodeName) VALUES ('FourthNode')
INSERT INTO Nodes(NodeName) VALUES ('FifthNode')
INSERT INTO Attributes (AttributeName) VALUES ('CustomField1')
INSERT INTO Attributes (AttributeName) VALUES ('CustomField2')
INSERT INTO Attributes (AttributeName) VALUES ('CustomField3')
INSERT INTO NodeAttr (NodeID,AttID,Value) VALUES (1,1,'My Value for Field1')
INSERT INTO NodeAttr (NodeID,AttID,Value) VALUES (1,2,'My Value for Field2')
INSERT INTO NodeAttr (NodeID,AttID,Value) VALUES (1,3,'My Value for Field3')
INSERT INTO NodeAttr (NodeID,AttID,Value) VALUES (3,3,'My Value for Field3
in third node')
Second I describe my approach to the problem.
I created function that retrieves data based on nodeID and Attribute name:
CREATE FUNCTION myfun(@.NodeID INT, @.AttributeName NVARCHAR(50))
RETURNS NVARCHAR(255)
AS
BEGIN
DECLARE @.Value NVARCHAR(255)
SET @.Value=NULL
SELECT @.Value=Value FROM NodeAttr NA
INNER JOIN Attributes A ON (NA.AttID=A.AttID)
WHERE NodeID=@.NodeID AND AttributeName=@.AttributeName
RETURN @.Value
END
Next, I create dynamic SQL for data retrieval:
declare @.query NVARCHAR(4000)
SELECT
@.query=isnull(@.query+',','SELECT NodeID,
')+'dbo.myfun(NodeID,'''+AttributeName+''') AS ['+AttributeName+']'
FROM Attributes
SET @.query = @.query + ' FROM Nodes'
and execute it:
EXECUTE sp_executesql @.query
Dynamicly generated SQL looks like this:
SELECT NodeID,
dbo.myfun(NodeID,'CustomField1') AS [CustomField1],
dbo.myfun(NodeID,'CustomField2') AS [CustomField2],
dbo.myfun(NodeID,'CustomField3') AS [CustomField3]
FROM Nodes
It is great when you deal with small number of nodes. When node number is
about 2000 the perfomarnce is really bad (executes over a minute). And I'd
like to have even more.
Anyone can point me to better solution? :)
regards,
Pawel Rogozinski
Software Engineer at Eracent Inc.
pawel.rogozinski@.eracent.com>> I need to create a query that depends on custom fields (virtual). <<
This flawed approach is called an EAV design and newbies re-invent it
about once a month here. The basic fallacy that leads to meltdown in
about a year is that you are mixing data and metadata. Also, columns
are not fields and rows are not records -- totally different concepts.
Then give the users a copy of SQL and get out of the way. If any
random user is a better database programmers and designer than you are,
you should not be programming.
It is not just exponently slow; since it has no data integrity, it is
error-prone, too!!|||>> I'd like to get following resultset:
It is better to use a client programming language or a report writer to
generate such cross tabulations. In t-SQL, most general approaches are
clumsy. For some methods refer to: http://tinyurl.com/ayjwa
Anith|||This sounds like an EAV (Entity-Attribute-Value) design. One of it's biggest
failings is that reporting is significantly more difficult and slow. Another
divanatage is that it requires dynamic SQL and all of its issues. Thus, y
ou
should be aware that the difficulties you are having in finding a solution a
re
directly related to the design.
That said and assuming you wanted to continue with this design, one solution
that takes the dynamic SQL out of the equation would be to make the equivale
nt
of a materialized view. In essence, create a permanent temp table where each
"attribute" in your design is a column in this temp table. You would then
populate and maintain this table with every entity's attribute values. To do
that you can use a massive case statement like so:
Select NA.NodeId
, Min(Case When NA.AttId = 1 Then NA.Value End) As Attribute1Name
, Min(Case When NA.AttId = 2 Then NA.Value End) As Attribute2Name
...
From NodeAttr As NA
Group By NA.NodeId
In order to maitain the contents of the table, you'll need to use triggers t
o
update the entity's appropriate attribute values in the temp table and so on
.
The solutions to the problems created by this type of design get uglier and
uglier and the performance gets worse and worse.
Thomas|||Celko,
thanks for your "constructive" comments.

> This flawed approach is called an EAV design
> and newbies re-invent it about once a month here.
Nice. I did not reinvent this, I'm just working on such flawed design,
as you called it.
Do you know better design, that could substitute EAV then?
[...cut...]

> It is not just exponently slow; since it has no data
> integrity, it is error-prone, too!!
Didn't I mention that I did not put integrity constraitns for simplicity
of the example? I believie I did.
I would be very gratefull if you could lead me to a better solution and
not criticizing my approach, as I am aware of the flaws that come with
my approach (and the design as well).
*** Sent via Developersdex http://www.examnotes.net ***|||I like the temporary table solution.
As the attributes are not added/deleted frequently it could be one of the
best approaches.
regards,
Pawel Rogozinski
Software Engineer at Eracent
pawel.rogozinski@.eracent.com|||>> Do you know better design, that could substitute EAV then? <<
The point everyone is trying to get across to you is that you need a
totally different approach; it is not a matter of finding a substutute.
Why not use an actual data model, based on the specifications from the
user. There is no "magical, one size fits all" answer that you s
with kludges like EAV. Each database is a thing in itself, but you can
look for patterns for reuse.
simplicity of the example? I believe I did. <<
Did I meniton that doing that is virtually impossible without a few
hundred lines of procedural code in triggers and procs if you have a
database of any size?
Let's say I have 50 attributes of type INTEGER in my EAV model. They
are crammed into one table. Each one has 1 or 2 CHECK() constraints
that are totally different. Now try to write the CASE expression with
50 WHEN clauses to validlate each one.
Let's say that I have a primary key among those 50 attributes. Can you
show me the uniquenss constraint to enforce that business rule?
Let's say that I have a a foreign key among those 50 attributes. Can
you show me the uniquness constraint to enforce that business rule?
And do the DRI Actions required to maintain this relationship?
Thomas Coleman had a recent posting where he worked out the equivalent
of a simple GROUP BY statement for an EAV. Google it.sql

custom xml data type

When I try to create a custom xml datatype I get the following error:
CREATE TYPE myOwnXMLType FROM xml;
Msg 15226, Level 16, State 1, Line 1
Cannot create user defined types from an XML datatype.
Is there anything I can do ? Other types work fine (int, nvarchar(max)
etc.). I use SQL Server 2005 Standard Edition.
Alex Voit wrote:
> When I try to create a custom xml datatype I get the following error:
> CREATE TYPE myOwnXMLType FROM xml;
> Msg 15226, Level 16, State 1, Line 1
> Cannot create user defined types from an XML datatype.
> Is there anything I can do ? Other types work fine (int, nvarchar(max)
> etc.). I use SQL Server 2005 Standard Edition.
The documentation lists the following allowed base types:
bigint
binary(n)
bit
char(n)
datetime
decimal
float
image
int
money
nchar(n)
ntext
numeric
nvarchar(n | max)
real
smalldatetime
smallint
smallmoney
sql_variant
text
tinyint
uniqueidentifier
varbinary(n | max)
varchar(n | max)
The xml data type is not listed as an allowed base type for CREATE TYPE
FROM.

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||We did not allow CREATE TYPE on the XML datatype. Please file a request at
http://connect.microsoft.com/sqlserver/feedback if this is a functionality
that you need.
Best regards
Michael
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:euWAnmrpHHA.4196@.TK2MSFTNGP06.phx.gbl...
> Alex Voit wrote:
> The documentation lists the following allowed base types:
> bigint
> binary(n)
> bit
> char(n)
> datetime
> decimal
> float
> image
> int
> money
> nchar(n)
> ntext
> numeric
> nvarchar(n | max)
> real
> smalldatetime
> smallint
> smallmoney
> sql_variant
> text
> tinyint
> uniqueidentifier
> varbinary(n | max)
> varchar(n | max)
>
> The xml data type is not listed as an allowed base type for CREATE TYPE
> FROM.
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/

custom xml data type

When I try to create a custom xml datatype I get the following error:
CREATE TYPE myOwnXMLType FROM xml;
Msg 15226, Level 16, State 1, Line 1
Cannot create user defined types from an XML datatype.
Is there anything I can do ? Other types work fine (int, nvarchar(max)
etc.). I use SQL Server 2005 Standard Edition.Alex Voit wrote:
> When I try to create a custom xml datatype I get the following error:
> CREATE TYPE myOwnXMLType FROM xml;
> Msg 15226, Level 16, State 1, Line 1
> Cannot create user defined types from an XML datatype.
> Is there anything I can do ? Other types work fine (int, nvarchar(max)
> etc.). I use SQL Server 2005 Standard Edition.
The documentation lists the following allowed base types:
bigint
binary(n)
bit
char(n)
datetime
decimal
float
image
int
money
nchar(n)
ntext
numeric
nvarchar(n | max)
real
smalldatetime
smallint
smallmoney
sql_variant
text
tinyint
uniqueidentifier
varbinary(n | max)
varchar(n | max)
The xml data type is not listed as an allowed base type for CREATE TYPE
FROM.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||We did not allow CREATE TYPE on the XML datatype. Please file a request at
http://connect.microsoft.com/sqlserver/feedback if this is a functionality
that you need.
Best regards
Michael
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:euWAnmrpHHA.4196@.TK2MSFTNGP06.phx.gbl...
> Alex Voit wrote:
> The documentation lists the following allowed base types:
> bigint
> binary(n)
> bit
> char(n)
> datetime
> decimal
> float
> image
> int
> money
> nchar(n)
> ntext
> numeric
> nvarchar(n | max)
> real
> smalldatetime
> smallint
> smallmoney
> sql_variant
> text
> tinyint
> uniqueidentifier
> varbinary(n | max)
> varchar(n | max)
>
> The xml data type is not listed as an allowed base type for CREATE TYPE
> FROM.
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/

custom wordbreaker

Is it possible to create a custom word breaker for SQL server? I've found
documentation for sharepoint and other technologies but SQL server appears
to be different and there are very few articles about customization (beyond
editing the junk word lists) for it.Can you provide some more information on what you are trying to do here? Are
you looking for some kind of a parsing routine that parses a character
string based on the occurrence of white space and punctuation?
Anith|||The problem I'm trying to address is that all the standard word breakers
don't handle chemical compound names correctly. For example AZ-113 might be
a valid term but even if i stick it in double quotes, the word breaker turns
it into two seperate terms 'AZ' and '113'. I need a way to have it keep
these as specific terms.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:e58Ee#EFFHA.628@.TK2MSFTNGP15.phx.gbl...
> Can you provide some more information on what you are trying to do here?
Are
> you looking for some kind of a parsing routine that parses a character
> string based on the occurrence of white space and punctuation?
> --
> Anith
>|||Clyde Seigle wrote:
> The problem I'm trying to address is that all the standard word
> breakers don't handle chemical compound names correctly. For example
> AZ-113 might be a valid term but even if i stick it in double quotes,
> the word breaker turns it into two seperate terms 'AZ' and '113'. I
> need a way to have it keep these as specific terms.
>
I'm not sure you've answered the question Anith asked. What is a
word-breaker as you are using it? Is it a function on the client, a
library you are using, etc.? What exactly do you need it to do and when
does it need to do this? For example, are you more interested in
breaking up the words in a text column for display in an application or
are you breaking up words at insert time in the database?
If you can do this on the application side, I'd recommend you look at a
Regular Expression library that Microsoft provides. There's a COM
version installed with Windows (in VBScript.dll) and there's a version
for .Net. Regular expressions can be a little daunting at first, but
provide a great way to search and validate all kinds of text.
David G.|||Sorry for not being clear and, in fact, I realize that this post is
misplaced (should be in ...fulltext). My question relates to the FullText
indexer that is part of SqlServer. It has a built-in word breaker that is
uses to do it's full text indexing. This is where I'm having the problems.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#fn3YRJFFHA.2756@.TK2MSFTNGP15.phx.gbl...
> Clyde Seigle wrote:
> I'm not sure you've answered the question Anith asked. What is a
> word-breaker as you are using it? Is it a function on the client, a
> library you are using, etc.? What exactly do you need it to do and when
> does it need to do this? For example, are you more interested in
> breaking up the words in a text column for display in an application or
> are you breaking up words at insert time in the database?
> If you can do this on the application side, I'd recommend you look at a
> Regular Expression library that Microsoft provides. There's a COM
> version installed with Windows (in VBScript.dll) and there's a version
> for .Net. Regular expressions can be a little daunting at first, but
> provide a great way to search and validate all kinds of text.
>
> --
> David G.
>|||Regrettably with both OS's Win2k and Win2003 the wordbreakers will break
AZ-113 as two separate words. Your best bet is to convert all such phrases
or tokens in your searches and content to AZ113.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Clyde Seigle" <clydeSeigle@.nospam.nospam> wrote in message
news:u8ayJlRFFHA.2156@.TK2MSFTNGP10.phx.gbl...
> Sorry for not being clear and, in fact, I realize that this post is
> misplaced (should be in ...fulltext). My question relates to the FullText
> indexer that is part of SqlServer. It has a built-in word breaker that is
> uses to do it's full text indexing. This is where I'm having the problems.
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:#fn3YRJFFHA.2756@.TK2MSFTNGP15.phx.gbl...
>|||Clyde,
Yes, it is best to discuss this subject (custom wordbreaker) in the fulltext
newsgroup... First a couple of very important questions on your environment
and the language of the text you are FT Indexing. Could you post the full
output of the following SQL code?
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
The above information is most important in helping troubleshoot the common
word breaking issues, I've seen in this newsgroup over many years. Note, for
SQL Server 2000, the word breakers you are using are specific to the OS
platform that your SQL Server is installed on. See
http://groups.google.com/groups?q=langwrbk+infosoft for a discussion on
Win2K's infosoft.dll vs. WinXP & Win2003's langwrkb.dll wordbreaker issues.
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Clyde Seigle" <clydeSeigle@.nospam.nospam> wrote in message
news:u8ayJlRFFHA.2156@.TK2MSFTNGP10.phx.gbl...
> Sorry for not being clear and, in fact, I realize that this post is
> misplaced (should be in ...fulltext). My question relates to the FullText
> indexer that is part of SqlServer. It has a built-in word breaker that is
> uses to do it's full text indexing. This is where I'm having the problems.
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:#fn3YRJFFHA.2756@.TK2MSFTNGP15.phx.gbl...
>

Custom unique ID.

Hello all,
I baddly need to create my own datatype, that will be able to generate
itself as unique id.
1. my user defined data type is based on char(14).
2. structure of this type is
YYYYMMDDSCIDCONT
YYYY = year of record creation
MM = month of record creation
DD = day of record creation
SCID = value of SCID column in the table
CONT = counter (like identity seed) next unique value in the table
I can to do it by my .NET application, or i can make unique keys with all
values in the table, but I would like to create my own SQL type, that will
be able to do what i described. Is it possible?
Thanks.Maybe I should better explain this or ask for some questions.
My task Im going to do:
1) create my own User Defined Data Type based on char(16) named for example
MyIDType
2) create rule that will check if the value is in right format.
3) create function, that will able to generate new id with parameters
datetime, scid. This fuction will parse the datetime, scid and newly created
counter into char(16) and into MyIDType.
Questions:
1) can I create rule that contain more complex check than one rule row ?
2) How to ask for source table in T-SQL function. I mean for the table which
initiated running of my function. Im going to put this function onto
defaultValue of the column that will be type of MyIDType. The function will
suppose, that in this table will exist columns CreationDateTime and SCID.
I know, how to do it in C#. The workflow of this function should be (it is
an example, dont check any syntax):
FUNCTION GetNewMyID (@.scid char(4))
DECLARE @.datetimenow char(8)
DECLARE @.lastid char(4)
DECLARE @.partOfMyID char(12)
@.datetimenow = FORMAT( GETDATE()) // in format YYYYMMDD
for(int i = 1; i < 10000; i++)
{
@.partOfMyID = @.datetimenow + @.scid + i.ToString("0000")
SELECT MyID FROM [executingTable?] WHERE MyID LIKE @.partOfMyID
if(there is no row)
return @.partOfMyID;
}
I hope, the good programer will understand this weird construction. :)))
Thanks.
"Mirek Endys" <MirekE@.community.nospam> wrote in message
news:%23$vH%23RZTGHA.196@.TK2MSFTNGP10.phx.gbl...
> Hello all,
> I baddly need to create my own datatype, that will be able to generate
> itself as unique id.
> 1. my user defined data type is based on char(16).
> 2. structure of this type is
> YYYYMMDDSCIDCONT
> YYYY = year of record creation
> MM = month of record creation
> DD = day of record creation
> SCID = value of SCID column in the table
> CONT = counter (like identity seed) next unique value in the table
> I can to do it by my .NET application, or i can make unique keys with all
> values in the table, but I would like to create my own SQL type, that will
> be able to do what i described. Is it possible?
> Thanks.
>|||hi Mirek,
Sure.
I understand that function but what does 'scid' mean? Let me know.
Current location: Alicante (ES)
"Mirek Endys" wrote:

> Maybe I should better explain this or ask for some questions.
> My task Im going to do:
> 1) create my own User Defined Data Type based on char(16) named for exampl
e
> MyIDType
> 2) create rule that will check if the value is in right format.
> 3) create function, that will able to generate new id with parameters
> datetime, scid. This fuction will parse the datetime, scid and newly creat
ed
> counter into char(16) and into MyIDType.
> Questions:
> 1) can I create rule that contain more complex check than one rule row ?
> 2) How to ask for source table in T-SQL function. I mean for the table whi
ch
> initiated running of my function. Im going to put this function onto
> defaultValue of the column that will be type of MyIDType. The function wil
l
> suppose, that in this table will exist columns CreationDateTime and SCID.
> I know, how to do it in C#. The workflow of this function should be (it is
> an example, dont check any syntax):
> FUNCTION GetNewMyID (@.scid char(4))
> DECLARE @.datetimenow char(8)
> DECLARE @.lastid char(4)
> DECLARE @.partOfMyID char(12)
> @.datetimenow = FORMAT( GETDATE()) // in format YYYYMMDD
>
> for(int i = 1; i < 10000; i++)
> {
> @.partOfMyID = @.datetimenow + @.scid + i.ToString("0000")
> SELECT MyID FROM [executingTable?] WHERE MyID LIKE @.partOfMyID
> if(there is no row)
> return @.partOfMyID;
> }
> I hope, the good programer will understand this weird construction. :)))
> Thanks.
>
> "Mirek Endys" <MirekE@.community.nospam> wrote in message
> news:%23$vH%23RZTGHA.196@.TK2MSFTNGP10.phx.gbl...
>
>|||Hi Enric
the SCID is variable - value that is contained in inserted record... or
passed by function parameter.
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:6DE5E6D0-9B7A-421C-A6BF-14E33D967B06@.microsoft.com...
> hi Mirek,
> Sure.
> I understand that function but what does 'scid' mean? Let me know.
> Current location: Alicante (ES)
>
> "Mirek Endys" wrote:
>|||Hi Mirek,
As for the SQL 2005 clr UDT, it is still quiet limited, e.g, if the data
type used in the UDT is not in the following basic types:
bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double,
SqlByte, SqlInt16, SqlInt32, SqlInt64, SqlDateTime, SqlSingle, SqlDouble,
SqlMoney, SqlBoolean
we have to implenent our userdefined serialization. For type validation, we
can define the validation rules in a certain method and mark it with the
"ValidationMethodName" attribute.
And for the constructing a new UDT instance based on some parameters or
other info in the current database, this is hard to be done through the UDT
itself since UDT just provide a "parse" function which help use create UDT
instance from a string representation(also a "ToString" method help return
string representation from a UDT instance). For your scenario, maybe we
have to define an additional user defined funciton(CLR based) to the work.
#User-Defined Type Requirements
http://msdn2.microsoft.com/en-us/library/f33b06h1.aspx
Regards,
Steven Cheng
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.|||You can create static method(s) in your UDT to create. Kinda like a static
constructor. Something like:
public static MyID Create(SqlDateTime dt, SqlInt id, SqlInt counter)
{
// ...
return new MyID(dt, id, counter);
}
Use the sql types that make sense for your needs.
Then you can create an instance something like:
declare @.id MyID
set @.id = MyID.Create(getdate(), 1, 1)
William Stacey [MVP]
"Mirek Endys" <MirekE@.community.nospam> wrote in message
news:%23$vH%23RZTGHA.196@.TK2MSFTNGP10.phx.gbl...
| Hello all,
|
| I baddly need to create my own datatype, that will be able to generate
| itself as unique id.
|
| 1. my user defined data type is based on char(14).
| 2. structure of this type is
| YYYYMMDDSCIDCONT
|
| YYYY = year of record creation
| MM = month of record creation
| DD = day of record creation
| SCID = value of SCID column in the table
| CONT = counter (like identity seed) next unique value in the table
|
| I can to do it by my .NET application, or i can make unique keys with all
| values in the table, but I would like to create my own SQL type, that will
| be able to do what i described. Is it possible?
|
| Thanks.
|
||||Thank for Williams inputs.
This is a good suggestion that directly use a static method on the type.
Regards,
Steven Cheng
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.|||Thanks guys,
i thought so, that this will be solution.
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:wQfSEekTGHA.5536@.TK2MSFTNGXA03.phx.gbl...
> Thank for Williams inputs.
> This is a good suggestion that directly use a static method on the type.
> Regards,
> Steven Cheng
> 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.
>|||Just a note after looking at it again. You need the double "::" to refer to
the static method on a type from tsql. Such as:
set @.id = MyID::Create(getdate(), 1, 1)
William Stacey [MVP]
"Mirek Endys" <MirekE@.community.nospam> wrote in message
news:uYBv3FmTGHA.4792@.TK2MSFTNGP14.phx.gbl...
| Thanks guys,
|
| i thought so, that this will be solution.
|
|
| "Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
| news:wQfSEekTGHA.5536@.TK2MSFTNGXA03.phx.gbl...
| > Thank for Williams inputs.
| >
| > This is a good suggestion that directly use a static method on the type.
| >
| > Regards,
| >
| > Steven Cheng
| > 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.
| >
|
|

Custom time period - 26th of month to 25 of next month

How do I create a server time dimension which has custom defined month periods.

For example we use month that starts on 26 and ends on 25 next month.

So our January is actualy starting from 26.Dec.2006 until 25.Jan.2007,

and so on for Feb, Mar...

Any idea how I can do it?

Thank you,

Mitja

Hello! Create a new column in your time dimension called CustomMonth.

Update TimeDim

Set CustomMonth = 'Jan'

Where CalendarDate between '2006-12-26' and '2007-12-25'

--

Continue with the next month.

This is a very simple solution. It possible to make it generic but that will require more thinking.

HTH

Thomas Ivarsson

|||

Thank you Thomas!

I have tried the proposed solution,

but I get syntax error at "where" critieria.

I was looking for some pre-made solution in business intelligence wizards,

strangely enough to find there isn't one.

Any ideas?

|||

This is an example that works with the AdventureWorksDW sample database:

Alter Table DimTime

Add SpecialMonth Char(3)

Select * from DimTime

where FulldateAlternateKey Between '2002-12-26' and '2003-12-25' --365 Check the no of records that will be updated

-

Update DimTime

Set SpecialMonth = 'Jan'

where FulldateAlternateKey Between '2002-12-26' and '2003-12-25'

-

It is possible to build a full generic solution but it will take some time. You can use TSQL CASE for that.

This will help you to get started.

Regards

Thomas Ivarsson

custom system proc

Hello, I would like to create a stored procedure, which would manipulate dat
a
in different databases, where all my databases have the same structure.
I would expect , when this proc is called in DB1 to use tables from DB1,
when called in DB2 .. tables from DB2 .. and so on.
However, when I created my sample stored proc in master..
create proc sp_sample as select * from table1
and then I tried to call this proc in DB1.. even that DB1 had also table1 ,
stored procedure was selecting data from master database. I was expecting it
to use DB1..table1.
I hope I am clear on what I am going to achieve, I would like to have one
copy for each of my stored procedures, stored in master database instead of
10' copies stored across all different databases.
I would appreciate any tips on how this could be done.
LucjanThere's no supported or documented way to achieve what you want to do. Sugge
sted method is to have
the same proc in all databases and use some sw to manage versions. You *can*
achieve what you want
by marking the proc as a system proc using sp_MS_marksystemobject (Google fo
r usage), but again, it
is not supported or documented.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lucjan" <Lucjan@.discussions.microsoft.com> wrote in message
news:BB44926A-3624-4F56-B47C-299338E2F222@.microsoft.com...
> Hello, I would like to create a stored procedure, which would manipulate d
ata
> in different databases, where all my databases have the same structure.
> I would expect , when this proc is called in DB1 to use tables from DB1,
> when called in DB2 .. tables from DB2 .. and so on.
> However, when I created my sample stored proc in master..
> create proc sp_sample as select * from table1
> and then I tried to call this proc in DB1.. even that DB1 had also table1
,
> stored procedure was selecting data from master database. I was expecting
it
> to use DB1..table1.
> I hope I am clear on what I am going to achieve, I would like to have one
> copy for each of my stored procedures, stored in master database instead
of
> 10' copies stored across all different databases.
> I would appreciate any tips on how this could be done.
> Lucjan
>|||The supported behavior of special procedures (sp_ in master) is to resolve
only system tables in the current database, but user tables in master.
There's an undocumented "feature" that causes the proc to resolve user
tables in the current database as well. To achieve this, you run:
EXEC dbo.sp_MS_marksystemobject 'dbo.sp_procname'
Tough I'd be careful from relying on such undocumented behavior in
production systems. You never know when support for such a "feature" will be
dropped.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Lucjan" <Lucjan@.discussions.microsoft.com> wrote in message
news:BB44926A-3624-4F56-B47C-299338E2F222@.microsoft.com...
> Hello, I would like to create a stored procedure, which would manipulate
> data
> in different databases, where all my databases have the same structure.
> I would expect , when this proc is called in DB1 to use tables from DB1,
> when called in DB2 .. tables from DB2 .. and so on.
> However, when I created my sample stored proc in master..
> create proc sp_sample as select * from table1
> and then I tried to call this proc in DB1.. even that DB1 had also table1
> ,
> stored procedure was selecting data from master database. I was expecting
> it
> to use DB1..table1.
> I hope I am clear on what I am going to achieve, I would like to have one
> copy for each of my stored procedures, stored in master database instead
> of
> 10' copies stored across all different databases.
> I would appreciate any tips on how this could be done.
> Lucjan
>|||Lucjan (Lucjan@.discussions.microsoft.com) writes:
> Hello, I would like to create a stored procedure, which would manipulate
> data in different databases, where all my databases have the same
> structure.
> I would expect , when this proc is called in DB1 to use tables from DB1,
> when called in DB2 .. tables from DB2 .. and so on.
> However, when I created my sample stored proc in master..
> create proc sp_sample as select * from table1
> and then I tried to call this proc in DB1.. even that DB1 had also
> table1 , stored procedure was selecting data from master database. I was
> expecting it to use DB1..table1.
> I hope I am clear on what I am going to achieve, I would like to have
> one copy for each of my stored procedures, stored in master database
> instead of 10' copies stored across all different databases.
You can do this in SQL 2000, but it is not supported. And I don't think
you can do it at all in SQL 2005, since there is a radical change how
stored procedures, system tables etc are stored in SQL 2005.
Keep your code under version control and write a script that can update
one or more databases. The script can be Perl, VB, VBscript or even a
BAT file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

custom summary reports in mgmt studio

Is it possible to create custom summary reports and run them in management studio? I am assuming these reports are RS reports.

thanks,

Derek

Is it possible to create custom summary reports and run them in management studio?

Doubt if there is such feature.

I am assuming these reports are RS reports

SSMS installation doesn't require RS installation, so probably it's wrong assumption.

|||

This is not possible. Though this is something we're investigating for SP2. I suggest you vote on the following item in the Product Feedback Center: http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=fb83d75b-424a-4c72-a735-30692a077e57

Cheers,
Dan

custom SSIS with ATL dll

Hi All,Is it possible to create SSIS custom component with ATL COM dll with MFC support?I'm not familar with C# & VB# languages :-(Regards,Svilen Varbanov

Yes, it's possible but not supported. To get an idea what interfaces you need to implement, take a look at the DTS.DLL file in OLEVIEW. You need to implement IDTSTask90, IDTSComponentPersist90 for the basic implementation. If you want to do more advanced things like create custom events, custom log events, build breakpoints etc. there are other interfaces to support. Several of the stock tasks are written in native code including the dataflow, execute package and some parts of the MSMQ task.

Kirk Haselden Author "SQL Server Integration Services"|||

Thanks!

It seems C++ is obsolete prorgamming language.... :-( Now I have find out a way to convert DTS package written in C++ 6.0 into managed dll written in C#... I can't believe...

Regards,

Svilen Varbanov

Custom Series Color in SP1 Charts

I'm trying to figure out how to create a custom series color in a bar chart,
and am unable to find documentation on it.
I read a post here on trying the Choose() function in the fill color portion
of the series value, but my chart goes gray in the layout mode, and then in
the preview mode it just shows the default colors.
Any documentation on this I can read?Keep in mind that expressions cannot be evaluated in design mode.
From the SP1 Readme about chart enhancements:
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_chart_enhancements
"If you use an expression for fill color, the chart elements will be white
in Layout view, but will display properly when the report is run."
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:1AA68AB0-128E-44B2-8519-6357D7EBD465@.microsoft.com...
> I'm trying to figure out how to create a custom series color in a bar
chart,
> and am unable to find documentation on it.
> I read a post here on trying the Choose() function in the fill color
portion
> of the series value, but my chart goes gray in the layout mode, and then
in
> the preview mode it just shows the default colors.
> Any documentation on this I can read?|||That makes sense,
But still having an issue of what function to use in the fill color field.
I have
=IIf( Fields!descriptn.Value = "Self", "white","Transparent")
But it seems to ingore it.
"Robert Bruckner [MSFT]" wrote:
> Keep in mind that expressions cannot be evaluated in design mode.
> From the SP1 Readme about chart enhancements:
> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_chart_enhancements
> "If you use an expression for fill color, the chart elements will be white
> in Layout view, but will display properly when the report is run."
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:1AA68AB0-128E-44B2-8519-6357D7EBD465@.microsoft.com...
> > I'm trying to figure out how to create a custom series color in a bar
> chart,
> > and am unable to find documentation on it.
> >
> > I read a post here on trying the Choose() function in the fill color
> portion
> > of the series value, but my chart goes gray in the layout mode, and then
> in
> > the preview mode it just shows the default colors.
> >
> > Any documentation on this I can read?
>
>|||There might be multiple issues here:
* A chart is similar to a matrix. Therefore a "cell" (datapoint) in a chart
can actually contain multiple rows depending on the groupings applied. Using
Fields!description.Value will always return the first value if multiple rows
are present.
If series groupings are present in the chart, you have to use
=First(Fields!description.Value, "NameOfInnermostSeriesGrouping") to get the
correct datapoint value in your expression. You might also want to check
this thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=15f4e34b-345a-4876-8cbe-734b9e4dfa53&sloc=en-us
* Transparent is not a valid color. The only exceptions are currently the
chart area and the plot area fill colors (it's on the wishlist for
extensions in a future release).
Since transparent is not valid color, it gets ignored and the default color
(from the palette) applies. If you want to "hide" entire
datapoints/categories, you can filter them. If you have a solid plotarea
background color you could also assign the plotarea color to hide
datapoints.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:917930D0-5D0E-4231-A6BD-1B58602D4D9F@.microsoft.com...
> That makes sense,
> But still having an issue of what function to use in the fill color field.
> I have
> =IIf( Fields!descriptn.Value = "Self", "white","Transparent")
> But it seems to ingore it.
> "Robert Bruckner [MSFT]" wrote:
> > Keep in mind that expressions cannot be evaluated in design mode.
> > From the SP1 Readme about chart enhancements:
> >
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_chart_enhancements
> > "If you use an expression for fill color, the chart elements will be
white
> > in Layout view, but will display properly when the report is run."
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Matt" <Matt@.discussions.microsoft.com> wrote in message
> > news:1AA68AB0-128E-44B2-8519-6357D7EBD465@.microsoft.com...
> > > I'm trying to figure out how to create a custom series color in a bar
> > chart,
> > > and am unable to find documentation on it.
> > >
> > > I read a post here on trying the Choose() function in the fill color
> > portion
> > > of the series value, but my chart goes gray in the layout mode, and
then
> > in
> > > the preview mode it just shows the default colors.
> > >
> > > Any documentation on this I can read?
> >
> >
> >|||We create one particular line chart in which the number of lines will be
different based on what the user selects when printing the report. They graph
the performance of their portfolio to that of 1-5 different indices. So the
chart will have between 2 and 6 different lines on it.
In the data tab of the chart, we have one item listed under values, one
under category groups, and one under series groups.
Everything works great. Our problem is that when there are so many lines
that the chart uses a Green and LightGreen color for two of the lines, our
client can't tell those two colors apart and wants us to use a different
color than LightGreen.
We have tried to use the various suggestions seen in this and other blogs to
customize the color but it doesn't work.
Can anyone help us? I can provide more info if needed.
This is what we have tried under
Edit Value=>Appearance=>Series Style=>Fill=>Color=>Expression:
=Choose(Fields!uv_port_type.Value,"Red","Blue","Green","Yellow","Purple","Orange")
=CStr(Choose(Fields!uv_port_type.Value,"Red","Blue","Green","Yellow","Purple","Orange"))
= Choose(First(Fields!uv_port_type.Value,
"dsUnitValues"),"Red","Blue","Green","Yellow","Purple","Orange")
= CStr(Choose(First(Fields!uv_port_type.Value,
"dsUnitValues"),"Red","Blue","Green","Yellow","Purple","Orange"))
(We also tried "IIF" statements.)
Thank you!
Jen
"Robert Bruckner [MSFT]" wrote:
> There might be multiple issues here:
> * A chart is similar to a matrix. Therefore a "cell" (datapoint) in a chart
> can actually contain multiple rows depending on the groupings applied. Using
> Fields!description.Value will always return the first value if multiple rows
> are present.
> If series groupings are present in the chart, you have to use
> =First(Fields!description.Value, "NameOfInnermostSeriesGrouping") to get the
> correct datapoint value in your expression. You might also want to check
> this thread:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=15f4e34b-345a-4876-8cbe-734b9e4dfa53&sloc=en-us
> * Transparent is not a valid color. The only exceptions are currently the
> chart area and the plot area fill colors (it's on the wishlist for
> extensions in a future release).
> Since transparent is not valid color, it gets ignored and the default color
> (from the palette) applies. If you want to "hide" entire
> datapoints/categories, you can filter them. If you have a solid plotarea
> background color you could also assign the plotarea color to hide
> datapoints.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:917930D0-5D0E-4231-A6BD-1B58602D4D9F@.microsoft.com...
> > That makes sense,
> >
> > But still having an issue of what function to use in the fill color field.
> >
> > I have
> >
> > =IIf( Fields!descriptn.Value = "Self", "white","Transparent")
> >
> > But it seems to ingore it.
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > Keep in mind that expressions cannot be evaluated in design mode.
> > > From the SP1 Readme about chart enhancements:
> > >
> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_chart_enhancements
> > > "If you use an expression for fill color, the chart elements will be
> white
> > > in Layout view, but will display properly when the report is run."
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "Matt" <Matt@.discussions.microsoft.com> wrote in message
> > > news:1AA68AB0-128E-44B2-8519-6357D7EBD465@.microsoft.com...
> > > > I'm trying to figure out how to create a custom series color in a bar
> > > chart,
> > > > and am unable to find documentation on it.
> > > >
> > > > I read a post here on trying the Choose() function in the fill color
> > > portion
> > > > of the series value, but my chart goes gray in the layout mode, and
> then
> > > in
> > > > the preview mode it just shows the default colors.
> > > >
> > > > Any documentation on this I can read?
> > >
> > >
> > >
>
>|||I found the following on SQLJunkies.. Seems to work:
--
Need a custom palette for your charts? Embed the function below into your
report, and set the Data > Values > Appearance > Series Style > Fill to the
custom function =Code.SetColor. Replace the nnnn's below with the hex codes
you need. See http://www.321webmaster.com/colorconverter.php for an RGB to
hex converter online. And hug the Reporting Services developers for adding
custom color functionality into SP1.
Function SetColor As String
Static ColorCode as Integer
ColorCode = ColorCode + 1: If ColorCode > 5 then ColorCode = 1
Select Case ColorCode
Case 1:
SetColor = "#00nnnn" ' Bluish
Case 2:
SetColor = "#78nnnn" ' Greenish
Case 3:
SetColor = "#E2nnnn" ' Reddish
Case 4:
SetColor = "#00nnnn" ' Navyish
Case 4:
SetColor = "#CCnnnn" ' Yellowish
Case 5:
SetColor = "#00nnnn" ' Light Blueish
Case Else:
SetColor = "#000000" ' Black (placeholder)
End Select
End Function
--
I have a where I need to show an average value in bar graph, but how many
values went into that average is also important. I pull back a percentage in
the query and then created a function that based on a percentage, returns a
color. Added =Code.FunctionName(pct) to the series fill and voila!
George
"Jen Rubin" wrote:
> We create one particular line chart in which the number of lines will be
> different based on what the user selects when printing the report. They graph
> the performance of their portfolio to that of 1-5 different indices. So the
> chart will have between 2 and 6 different lines on it.
> In the data tab of the chart, we have one item listed under values, one
> under category groups, and one under series groups.
> Everything works great. Our problem is that when there are so many lines
> that the chart uses a Green and LightGreen color for two of the lines, our
> client can't tell those two colors apart and wants us to use a different
> color than LightGreen.
> We have tried to use the various suggestions seen in this and other blogs to
> customize the color but it doesn't work.
> Can anyone help us? I can provide more info if needed.
> This is what we have tried under
> Edit Value=>Appearance=>Series Style=>Fill=>Color=>Expression:
> => Choose(Fields!uv_port_type.Value,"Red","Blue","Green","Yellow","Purple","Orange")
> => CStr(Choose(Fields!uv_port_type.Value,"Red","Blue","Green","Yellow","Purple","Orange"))
> = Choose(First(Fields!uv_port_type.Value,
> "dsUnitValues"),"Red","Blue","Green","Yellow","Purple","Orange")
> = CStr(Choose(First(Fields!uv_port_type.Value,
> "dsUnitValues"),"Red","Blue","Green","Yellow","Purple","Orange"))
> (We also tried "IIF" statements.)
> Thank you!
> Jen
>
> "Robert Bruckner [MSFT]" wrote:
> > There might be multiple issues here:
> > * A chart is similar to a matrix. Therefore a "cell" (datapoint) in a chart
> > can actually contain multiple rows depending on the groupings applied. Using
> > Fields!description.Value will always return the first value if multiple rows
> > are present.
> > If series groupings are present in the chart, you have to use
> > =First(Fields!description.Value, "NameOfInnermostSeriesGrouping") to get the
> > correct datapoint value in your expression. You might also want to check
> > this thread:
> > http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=15f4e34b-345a-4876-8cbe-734b9e4dfa53&sloc=en-us
> >
> > * Transparent is not a valid color. The only exceptions are currently the
> > chart area and the plot area fill colors (it's on the wishlist for
> > extensions in a future release).
> > Since transparent is not valid color, it gets ignored and the default color
> > (from the palette) applies. If you want to "hide" entire
> > datapoints/categories, you can filter them. If you have a solid plotarea
> > background color you could also assign the plotarea color to hide
> > datapoints.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Matt" <Matt@.discussions.microsoft.com> wrote in message
> > news:917930D0-5D0E-4231-A6BD-1B58602D4D9F@.microsoft.com...
> > > That makes sense,
> > >
> > > But still having an issue of what function to use in the fill color field.
> > >
> > > I have
> > >
> > > =IIf( Fields!descriptn.Value = "Self", "white","Transparent")
> > >
> > > But it seems to ingore it.
> > >
> > > "Robert Bruckner [MSFT]" wrote:
> > >
> > > > Keep in mind that expressions cannot be evaluated in design mode.
> > > > From the SP1 Readme about chart enhancements:
> > > >
> > http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_chart_enhancements
> > > > "If you use an expression for fill color, the chart elements will be
> > white
> > > > in Layout view, but will display properly when the report is run."
> > > >
> > > > --
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > >
> > > >
> > > > "Matt" <Matt@.discussions.microsoft.com> wrote in message
> > > > news:1AA68AB0-128E-44B2-8519-6357D7EBD465@.microsoft.com...
> > > > > I'm trying to figure out how to create a custom series color in a bar
> > > > chart,
> > > > > and am unable to find documentation on it.
> > > > >
> > > > > I read a post here on trying the Choose() function in the fill color
> > > > portion
> > > > > of the series value, but my chart goes gray in the layout mode, and
> > then
> > > > in
> > > > > the preview mode it just shows the default colors.
> > > > >
> > > > > Any documentation on this I can read?
> > > >
> > > >
> > > >
> >
> >
> >

Custom Semi-Additive Member using Analysis Service 2005 Standard Edition

[Edited For Clarity]

I’m using Analysis Service 2005 the standard edition:

I’m trying to create a custom semi-additive measure through MDX. My time dimension has month granularity with years on top of that. Basically, I’m trying to create an average the months while summing the averages across non time dimensions.

My best solution was using the following code:

avg(descendants([time].[hierarchy].currentmember,[time].[hierarchy].[month], self), sum(measures.[measure_to_aggregate]))

the problem with the above code is that it does not include empty months of non empty years. That is, if an attribute exists for a month in 2004, I want to see all the months of 2004 as part of the denominator. However, if an attribute does not exist for any months in 2004 I want to see Null.

I also tried the following code:

avg(descendants([time].[hierarchy].currentmember,[time].[hierarchy].[month], self), sum(measures.[measure_to_aggregate]), includeempty)

but the statement could not be parsed due to too many arguments in the avg function. Can anyone clarify what the problem is here?

Another way of doing this is using the count member. Basically I would count all the periods in the year and use that as a denominator to find the average:

sum([measures].[measure_to_aggregate]) / count (descendants ([time].[hierarchy].currentmember,[time].[hierarchy].[month], self))

The problem with this statement is that I can’t differentiate between empty years and non-empty years, so the code computes the average for all years in the dimension.I also tred using the nonempty function around descendants, but it excludes both months of empty and non-empty years.

Any ideas?

DNA,

You are saying that statement below is OK, and one problem is that it does not include empty cells.

avg(descendants([all year].[hierarchy].currentmember,[all year].[hierarchy].[period], self), sum(measures.[adjusted headcount]))

Try to use CoalesceEmpty:

avg(descendants([all year].[hierarchy].currentmember,[all year].[hierarchy].[period], self), sum(CoalesceEmpty(measures.[adjusted headcount],0)))

This will replace NULLs with 0.

Vidas Matelis

Edited: Added ,0

|||

Thanks for replying Vidas Matelis,

I have edited my post to make the previous post much more clear.

The issue with using Coalesce is that I have more years in my dimension than the cube itself.

Therefore I would not want to replace the null values of the addtional years in the dimension with zeros.

|||

Please note that includeempty is an option for Count(), but not for Avg(). How about using Count() with Filter(), like:

[measures].[measure_to_aggregate] / count(Filter(descendants(

[time].[hierarchy].currentmember, [time].[hierarchy].[month]),

Not IsEmpty(([measures].[measure_to_aggregate], [time].[hierarchy].Parent))))

|||

DNA,

I would assume that for years where you have no data, you would get result as 0. So what if you use IIF statement and replace 0 with NULL? Would that work?

IIF(avg(descendants([all year].[hierarchy].currentmember,[all year].[hierarchy].[period], self), sum(CoalesceEmpty(measures.[adjusted headcount],0))) = 0

, NULL

,avg(descendants([all year].[hierarchy].currentmember,[all year].[hierarchy].[period], self), sum(CoalesceEmpty(measures.[adjusted headcount],0)))

)

Vidas Matelis

|||

Thanks for the replies and ideas. For clarification:

To Deepak Puri,

I tried using filter but it excluded all empty values, meaning the empty values for periods for the years within the cube were exclude as well. The way I wanted to approach this is by excluding all empty values for the years that are not in the cube while including the empty periods for the years that are in the cube.

To: Vidas Matelis,

And an iif statement works very similiar to case statements and coalesceempty. When I used the coalesceempty I had to make a seperate calculated measure to use it because there was an error saying too many arguments in the avg() function. I could certainly try using the code you have provided but I would think that this code would also exclude all empty values instead of selectively excluding values for certain years.

|||

Ultimately, the code will have to know somehow that some years are considered "empty" and some are not. You are the only one who can tell what the criteria is. It could be as simple as hardcoding 2004 as the first non-empty year, or, perhaps you will have more complex criteria, i.e. year in which all months are empty for Root() of everything else, or something else. Let's assume that you created named set with all the months from the years that you consider "non-empty", i.e. something like

CREATE SET NonEmptyMonths AS Exists([Time].[Month].[Month], [Time].[Year].[2004] : NULL )

I.e. here I hardcoded 2004 as the first "non-empty" year. After that you can take any solution proposed above and use

Intersect(NonEmptyMonths, Descendants([Time].[Hierarchy].CurrentMember, [Time].[Hierarchy].Month))

Instead of using Descendants directly. You won't have to filter empty cells anymore, of course, because Intersect will take care of it.

|||

Would there be a way without hardcoding it?

The reason being is that I want it to be automated and to account for changes in the time dimension (ie. additional years and periods being added).

|||Please see my previous message - uou have to tell us the criteria - which years are considered to be "populated". Is it a member property of the Year attribute ? Is it year which has at least one non-empty month at the Root ? Is it something else ? Once we know you business logic we can translate it into MDX, but without knowing it - not much we can do.|||Mosha, sorry for the late reply, the years are considered to be populated if there is at least one non-empty months.|||

OK, then the CREATE SET statement can look like something around the following theme:

CREATE SET NonEmptyMonths AS Descendants( NonEmpty( [Time].[Hierarchy].[Year].MEMBERS ), [Time].[Hierarchy].[Month] )

|||

Something along the following lines:

CREATE SET NonEmptyMonths AS Descendants( NonEmpty([Time].[Hierarchy].[Year]), [Time].[Hierarchy].[Month] )

|||

CREATE SET NonEmptyMonths AS Descendants( NonEmpty([Time].[Hierarchy].[Year]), [Time].[Hierarchy].[Month] )

Custom Security: How do you manage groups

Dear Anyone,

We are trying to create our own custom security extension for rs2005. We are wondering how will then security extension will manage the authenticated users and how will it be mapped to an existing RS2005 group or role?

Thanks,

Joseph

It completely depends on how you write the extension itself.

I'd recommend that you use the forms auth extension as a start: C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Extension Samples\FormsAuthentication Sample

Change the parts of it that handle authentication, and then leave the parts alone which do authorization for you.

If you go this route, you will continue to use all the same roles you did before: Browser, Content Manager, etc.

|||

How would this dovetail with using Custom roles within the custom authentication database?

I've already implemented a version of the custom authentication extension and have the basic SSRS Forms auth working.

For example, users of my application log in with a companycode and would be put into one or many custom application roles that would ostensibly control the visibility of various folders and reports (you can imagine the various scenarios). Would I have to intercept the ASP.NET authentication pipeline and insert a custom Principal/Identity into the HTTP context? Would SSRS use the "User.IsInRole" construct to determine if the user is in a role that can "see" the restricted SSRS objects?

Just in case I confused anyone...I want to be able to add application-domain roles to the SSRS built-in (and possibly custom) roles and use those to restrict visibility to the SSRS objects.

Thanks,

Matthew Belk

|||Did you ever find an answer to this question? We're wanting to do the same thing (use roles from our custom application DB).|||

You can do this by assigning security within RS using the role names (just like windows groups). To get this to work though you will need to:

1. make your implementation of IAuthenticationExtension.IsValidPrincipalName check against the roles in your custom database
2. Implement IAuthorisationExtension.CheckAccess and in there look up the roles the user belongs to and check the access based on the username and his roles.

A good sample of this is located at:

http://www.devx.com/dotnet/Article/26759 - Part 1
http://www.devx.com/dotnet/Article/27133 - Part 2

Incidentally, I would like to take this further by using the Membership and Role Management Application Services of ASP.NET 2.0 to manage my users and roles.

So far I've been able to get the Forms Authentication working and I've set up the Membership and Role Management features, but when I try to use the 2 together i.e. make my logon.aspx page call Membership.ValidateUser the report server stops working and returns:

An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help

Object reference not set to an instance of an object.

Custom Security: How do you manage groups

Dear Anyone,

We are trying to create our own custom security extension for rs2005. We are wondering how will then security extension will manage the authenticated users and how will it be mapped to an existing RS2005 group or role?

Thanks,

Joseph

It completely depends on how you write the extension itself.

I'd recommend that you use the forms auth extension as a start: C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Extension Samples\FormsAuthentication Sample

Change the parts of it that handle authentication, and then leave the parts alone which do authorization for you.

If you go this route, you will continue to use all the same roles you did before: Browser, Content Manager, etc.

|||

How would this dovetail with using Custom roles within the custom authentication database?

I've already implemented a version of the custom authentication extension and have the basic SSRS Forms auth working.

For example, users of my application log in with a companycode and would be put into one or many custom application roles that would ostensibly control the visibility of various folders and reports (you can imagine the various scenarios). Would I have to intercept the ASP.NET authentication pipeline and insert a custom Principal/Identity into the HTTP context? Would SSRS use the "User.IsInRole" construct to determine if the user is in a role that can "see" the restricted SSRS objects?

Just in case I confused anyone...I want to be able to add application-domain roles to the SSRS built-in (and possibly custom) roles and use those to restrict visibility to the SSRS objects.

Thanks,

Matthew Belk

|||Did you ever find an answer to this question? We're wanting to do the same thing (use roles from our custom application DB).|||

You can do this by assigning security within RS using the role names (just like windows groups). To get this to work though you will need to:

1. make your implementation of IAuthenticationExtension.IsValidPrincipalName check against the roles in your custom database
2. Implement IAuthorisationExtension.CheckAccess and in there look up the roles the user belongs to and check the access based on the username and his roles.

A good sample of this is located at:

http://www.devx.com/dotnet/Article/26759 - Part 1
http://www.devx.com/dotnet/Article/27133 - Part 2

Incidentally, I would like to take this further by using the Membership and Role Management Application Services of ASP.NET 2.0 to manage my users and roles.

So far I've been able to get the Forms Authentication working and I've set up the Membership and Role Management features, but when I try to use the 2 together i.e. make my logon.aspx page call Membership.ValidateUser the report server stops working and returns:

An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help

Object reference not set to an instance of an object.sql

Custom Security: How do you manage groups

Dear Anyone,

We are trying to create our own custom security extension for rs2005. We are wondering how will then security extension will manage the authenticated users and how will it be mapped to an existing RS2005 group or role?

Thanks,

Joseph

It completely depends on how you write the extension itself.

I'd recommend that you use the forms auth extension as a start: C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Extension Samples\FormsAuthentication Sample

Change the parts of it that handle authentication, and then leave the parts alone which do authorization for you.

If you go this route, you will continue to use all the same roles you did before: Browser, Content Manager, etc.

|||

How would this dovetail with using Custom roles within the custom authentication database?

I've already implemented a version of the custom authentication extension and have the basic SSRS Forms auth working.

For example, users of my application log in with a companycode and would be put into one or many custom application roles that would ostensibly control the visibility of various folders and reports (you can imagine the various scenarios). Would I have to intercept the ASP.NET authentication pipeline and insert a custom Principal/Identity into the HTTP context? Would SSRS use the "User.IsInRole" construct to determine if the user is in a role that can "see" the restricted SSRS objects?

Just in case I confused anyone...I want to be able to add application-domain roles to the SSRS built-in (and possibly custom) roles and use those to restrict visibility to the SSRS objects.

Thanks,

Matthew Belk

|||Did you ever find an answer to this question? We're wanting to do the same thing (use roles from our custom application DB).|||

You can do this by assigning security within RS using the role names (just like windows groups). To get this to work though you will need to:

1. make your implementation of IAuthenticationExtension.IsValidPrincipalName check against the roles in your custom database
2. Implement IAuthorisationExtension.CheckAccess and in there look up the roles the user belongs to and check the access based on the username and his roles.

A good sample of this is located at:

http://www.devx.com/dotnet/Article/26759 - Part 1
http://www.devx.com/dotnet/Article/27133 - Part 2

Incidentally, I would like to take this further by using the Membership and Role Management Application Services of ASP.NET 2.0 to manage my users and roles.

So far I've been able to get the Forms Authentication working and I've set up the Membership and Role Management features, but when I try to use the 2 together i.e. make my logon.aspx page call Membership.ValidateUser the report server stops working and returns:

An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help

Object reference not set to an instance of an object.

Custom Resolver for merge replication

Hi there!
I'm trying to create a custom resolver for merge replication exactly like in the MS example.
It seems to work, but only ONE time. If I change, insert or delete a record in a table the second time, the subscriber monitor comes with the following errors:

Error messages:
Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199411)
The Merge Agent encountered an error when executing code in the 'UpdateHandler' method implemented in the business logic handler 'D:\Program Files\Microsoft SQL Server\90\COM\MyResolver.dll'. Ensure that the overridden 'UpdateHandler' method has been properly implemented in the business logic handler. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199411)
This last error is of course dependant on my action (update, delete, insert).
My code is -exactly- like the example (I just stripped out the log message).
Does anyone know why I am "trying to read or write protected memory" ?
The thing is that I'm trying to create an application that detects if a table changes. Is this the right way to do this anyway or are there better solutions?
Any help is appreciated! Thanks!

Hi there!

Any resolution to the problem described in the post? I'm getting the same problem with custom resolver.

Custom Resolver for merge replication

Hi there!

I'm trying to create a custom resolver for merge replication exactly like in the MS example.

It seems to work, but only ONE time. If I change, insert or delete a

record in a table the second time, the subscriber monitor comes with

the following errors:

Error messages:

Attempted to read or write protected memory. This is often an

indication that other memory is corrupt. (Source: MSSQL_REPL, Error

number: MSSQL_REPL-2147199411)

The Merge Agent encountered an error when executing code in the

'UpdateHandler' method implemented in the business logic handler

'D:\Program Files\Microsoft SQL Server\90\COM\MyResolver.dll'. Ensure

that the overridden 'UpdateHandler' method has been properly

implemented in the business logic handler. (Source: MSSQL_REPL,

Error number: MSSQL_REPL-2147199411)

This last error is of course dependant on my action (update, delete, insert).

My code is -exactly- like the example (I just stripped out the log message).

Does anyone know why I am "trying to read or write protected memory" ?

The thing is that I'm trying to create an application that detects if a

table changes. Is this the right way to do this anyway or are there

better solutions?

Any help is appreciated! Thanks!

Hi there!

Any resolution to the problem described in the post? I'm getting the same problem with custom resolver.

Custom reports from SQL 2000

What would I need to create custom reports from SQL 2000 reporting services?
I was told I would need Visual Studio to create the reports. It would be
nice to do some ad hoc reporting without having to go to our developer each
time we want to create a new report.RS 2005 has an end user reporting solution as well as the report designer
which exists in RS 2000.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Rockn" <Rockn@.newsgroups.nospam> wrote in message
news:ebCEtnANHHA.4244@.TK2MSFTNGP04.phx.gbl...
> What would I need to create custom reports from SQL 2000 reporting
> services? I was told I would need Visual Studio to create the reports. It
> would be nice to do some ad hoc reporting without having to go to our
> developer each time we want to create a new report.
>|||I believe this would only let you set the parameters for the report not the
content or type of report.
I would like to create completely custom reports. If VS2003 is the only way
to do it without upgrading to SQL 2005 we may have to go that route.
We have the media for SQL 2005 and isn't there some client side report
writer you could use in conjunction with the SQL 2000 data base we are
currently using for production?
"Paul.G." <PaulG@.discussions.microsoft.com> wrote in message
news:3E8832DC-C2F9-402B-B64D-3AD4B0EF748F@.microsoft.com...
> reports in Reporting services 2000 are created only in visual studio
> 2003.
> But as the reports are plain XML document, then your developper team can
> create a module for you that interacts with your database and let you
> create
> reports on the FLY. That means an application created by your team that
> let
> you create reports that you need.
> BUT this approche takes a gr8 deal of developpemnt, time, and resources.
> What I suggess is to define your needs and go to your developper team.
> "Rockn" wrote:
>> What would I need to create custom reports from SQL 2000 reporting
>> services?
>> I was told I would need Visual Studio to create the reports. It would be
>> nice to do some ad hoc reporting without having to go to our developer
>> each
>> time we want to create a new report.
>>|||Hello Rockn,
My understanding is that: You want konw is it possible that SQL 2005
Reporting service use the SQL 2000 database as the datasource.
My answer is yes. You could use the SQL 2000 database as the datasource of
the SQL 2005 reporting services.
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,
Just want to say Hi, and I was wondering how everything is going. If
anything is unclear, please let me know. It is my pleasure to be of
assistance.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================