combined merge + custom conflict resolution

I have what seems to be a unique case, but logically, would think that
someone out there is already doing this. I have the following configuration:
- merge replication
- column level tracking
- single pub./dist. server replicating with a single subscriber
- SQL Server 2000 Ent. on Windows 2000 Adv. Server
I have succesfully implemented replication, and data is merged properly in
both directions. However, when confilcts occurr, I want the ability to allow
those records that would merge accordingly (not in conflict), but for those
columns in conflict, I want the ability to run a stored procedure with my
proper business logic. Is there a way to do this? When using a stored proc
for conflict resolution, you are forced to return the "winning values" for
all the fields. This is where I lose the data that would have normally
replicated that was not in conflict. In my stored proc, I wish I had the
ability to see or read from a table the data values that would normally be
merged that were not in conflict, so I could supply them in my returned
recordset. I have the logic of reading both Subscriber and Publisher field
values for the current rowguid, but I don't know which of the two values (sub
or pub) has been edited so that it can merge.
Am I explaining this properly? I currently have an open instance with
Microsoft tech support ($250 !!), but they have yet to come back with a
solution. They have had the specs to my issue for a week now, and they have
not been able to resolve it. Maybe someone out there has done this. Thanks
in advance for all the help!
This type of logic is possible using merge replication, but you will have to
hack into the stored procedures that the merge agent uses to apply the
changes to the subscriber and publisher.
However, I think bi-directional transactional replication is a better choice
for implementing this form of custom business logic. When you use XCALL the
before and after images of the data will flow from the Publisher to the
Subscriber and you can incorporate logic to handle your conflicts this way.
So lets consider what a conflict is. A conflict is
1) a pk violation, trying to insert a PK value where a row with that PK
value already exists on the subscriber
2) trying to update a row on the Subscriber and instead of updating a single
row you update 0 or more than one rows.
3) trying to delete a row on the Subscriber and instead of deleting a single
row you delete 0 or more than one rows.
So, before your insert proc fires it will do an existence check. If the row
exists, it can instead do an update which will incorporate your custom
business logic. If the update ends up updating more than one row you have to
consider what is going on. This probably violates your database integrity,
but may not depending on how your database is set up or what you are trying
to accomplish. For instance consider you are replicating to audit table. A
row with a PK value of 1 may be inserted at the publisher and replicated to
the Subscriber. Then this row is deleted at the publisher, but this delete
is not replicated to the subscriber (remember the subscriber is an audit
table, so we need a record of that row). Then this row is readded with the
same PK (1) and replicate to the subscriber. If you haven't handled the
possibility of duplicate PK's you will have a problem.
Updates and deletes are simple as long as you can preserve the one to one
mapping of rows on the Publisher to the Subsriber. If you are doing a one to
many from the Publisher to the Subscriber it gets more complex, but it is
not impossible.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"deanc24" <deanc24athotmaildotcom> wrote in message
news:696CC504-19A5-4217-B12E-EEF9D181E842@.microsoft.com...
> I have what seems to be a unique case, but logically, would think that
> someone out there is already doing this. I have the following
configuration:
> - merge replication
> - column level tracking
> - single pub./dist. server replicating with a single subscriber
> - SQL Server 2000 Ent. on Windows 2000 Adv. Server
> I have succesfully implemented replication, and data is merged properly in
> both directions. However, when confilcts occurr, I want the ability to
allow
> those records that would merge accordingly (not in conflict), but for
those
> columns in conflict, I want the ability to run a stored procedure with my
> proper business logic. Is there a way to do this? When using a stored
proc
> for conflict resolution, you are forced to return the "winning values" for
> all the fields. This is where I lose the data that would have normally
> replicated that was not in conflict. In my stored proc, I wish I had the
> ability to see or read from a table the data values that would normally be
> merged that were not in conflict, so I could supply them in my returned
> recordset. I have the logic of reading both Subscriber and Publisher
field
> values for the current rowguid, but I don't know which of the two values
(sub
> or pub) has been edited so that it can merge.
> Am I explaining this properly? I currently have an open instance with
> Microsoft tech support ($250 !!), but they have yet to come back with a
> solution. They have had the specs to my issue for a week now, and they
have
> not been able to resolve it. Maybe someone out there has done this.
Thanks
> in advance for all the help!
>
|||being that I am only capable to connect at night, I was under the impression
that Merge Replication was my only option. Transaction Replication os for
constant connection, right? If I am wrong, please advise. As for the
remainder of your comments, I am not that familiar with Replication, to
understand everything you are referring to. If you know of any publications
or web sites that can help educate me, that would be great. So far, the web
has seemed to be kinda light on info that deals with the deep intricate
details regarding replication. It brushes lightly on the topic, but thats
it. Thanks so much for your assistance. I have been stuck with this for a
week now. Its frustrating.
"Hilary Cotter" wrote:

> This type of logic is possible using merge replication, but you will have to
> hack into the stored procedures that the merge agent uses to apply the
> changes to the subscriber and publisher.
> However, I think bi-directional transactional replication is a better choice
> for implementing this form of custom business logic. When you use XCALL the
> before and after images of the data will flow from the Publisher to the
> Subscriber and you can incorporate logic to handle your conflicts this way.
> So lets consider what a conflict is. A conflict is
> 1) a pk violation, trying to insert a PK value where a row with that PK
> value already exists on the subscriber
> 2) trying to update a row on the Subscriber and instead of updating a single
> row you update 0 or more than one rows.
> 3) trying to delete a row on the Subscriber and instead of deleting a single
> row you delete 0 or more than one rows.
> So, before your insert proc fires it will do an existence check. If the row
> exists, it can instead do an update which will incorporate your custom
> business logic. If the update ends up updating more than one row you have to
> consider what is going on. This probably violates your database integrity,
> but may not depending on how your database is set up or what you are trying
> to accomplish. For instance consider you are replicating to audit table. A
> row with a PK value of 1 may be inserted at the publisher and replicated to
> the Subscriber. Then this row is deleted at the publisher, but this delete
> is not replicated to the subscriber (remember the subscriber is an audit
> table, so we need a record of that row). Then this row is readded with the
> same PK (1) and replicate to the subscriber. If you haven't handled the
> possibility of duplicate PK's you will have a problem.
> Updates and deletes are simple as long as you can preserve the one to one
> mapping of rows on the Publisher to the Subsriber. If you are doing a one to
> many from the Publisher to the Subscriber it gets more complex, but it is
> not impossible.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "deanc24" <deanc24athotmaildotcom> wrote in message
> news:696CC504-19A5-4217-B12E-EEF9D181E842@.microsoft.com...
> configuration:
> allow
> those
> proc
> field
> (sub
> have
> Thanks
>
>
|||While you can use bi-directional transactional replication in a disconnected
manner it is not really advisable to do so, as the conflict tracking and
resolving mechanisms are essentially non-existent.
If you get a conflict your distribution agent will fail and you will have to
resolve the conflict and restart the agent. If you have many conflicts this
is not acceptable, but if you have few conflicts or you can design your
replication solution to minimize conflicts this might be a solution for you.
Again, I don't know your data, data flow, or topology so I can't really
advise you on it. You know it more intimately than I so you should make the
decision on this.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"deanc24" <deanc24athotmaildotcom> wrote in message
news:C223CB8B-F458-4612-9531-398B4D42D8FA@.microsoft.com...
> being that I am only capable to connect at night, I was under the
impression
> that Merge Replication was my only option. Transaction Replication os for
> constant connection, right? If I am wrong, please advise. As for the
> remainder of your comments, I am not that familiar with Replication, to
> understand everything you are referring to. If you know of any
publications
> or web sites that can help educate me, that would be great. So far, the
web
> has seemed to be kinda light on info that deals with the deep intricate
> details regarding replication. It brushes lightly on the topic, but thats
> it. Thanks so much for your assistance. I have been stuck with this for
a[vbcol=seagreen]
> week now. Its frustrating.
> "Hilary Cotter" wrote:
have to[vbcol=seagreen]
choice[vbcol=seagreen]
the[vbcol=seagreen]
way.[vbcol=seagreen]
single[vbcol=seagreen]
single[vbcol=seagreen]
row[vbcol=seagreen]
have to[vbcol=seagreen]
integrity,[vbcol=seagreen]
trying[vbcol=seagreen]
A[vbcol=seagreen]
to[vbcol=seagreen]
delete[vbcol=seagreen]
the[vbcol=seagreen]
one[vbcol=seagreen]
one to[vbcol=seagreen]
is[vbcol=seagreen]
properly in[vbcol=seagreen]
to[vbcol=seagreen]
my[vbcol=seagreen]
stored[vbcol=seagreen]
for[vbcol=seagreen]
normally[vbcol=seagreen]
the[vbcol=seagreen]
normally be[vbcol=seagreen]
returned[vbcol=seagreen]
values[vbcol=seagreen]
a[vbcol=seagreen]
they[vbcol=seagreen]
|||I appreciate your feedback Hilary. I do think that bi-directional
transactional replication is not an option for me, due to the number of
conflicts anticipated. I hope that Microsoft's support people will help me
find the right answer. Its costing me $250, so they better! :-)
Thanks again. I enjoy your particiapation on this discussion board. Its a
great educational tool!
"Hilary Cotter" wrote:

> While you can use bi-directional transactional replication in a disconnected
> manner it is not really advisable to do so, as the conflict tracking and
> resolving mechanisms are essentially non-existent.
> If you get a conflict your distribution agent will fail and you will have to
> resolve the conflict and restart the agent. If you have many conflicts this
> is not acceptable, but if you have few conflicts or you can design your
> replication solution to minimize conflicts this might be a solution for you.
> Again, I don't know your data, data flow, or topology so I can't really
> advise you on it. You know it more intimately than I so you should make the
> decision on this.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "deanc24" <deanc24athotmaildotcom> wrote in message
> news:C223CB8B-F458-4612-9531-398B4D42D8FA@.microsoft.com...
> impression
> publications
> web
> a
> have to
> choice
> the
> way.
> single
> single
> row
> have to
> integrity,
> trying
> A
> to
> delete
> the
> one
> one to
> is
> properly in
> to
> my
> stored
> for
> normally
> the
> normally be
> returned
> values
> a
> they
>
>

Column Width

How can you widen or shorten a column's width when the report is run?
The width doesn't allow custom code or expressions.
Basically, based on a parameter I pass in, I want to widen or shrink a
column's width.Anyone?
"JSF" wrote:
> How can you widen or shorten a column's width when the report is run?
> The width doesn't allow custom code or expressions.
> Basically, based on a parameter I pass in, I want to widen or shrink a
> column's width.|||JSF,
It doesn't look like it's possible to dynamically change column width.
I just did another search through the group and found nothing new.

Column Mapping in a Custom Component

Does anyone know how to get destination coulmns to show up in the advanced editor for a custom component? I have a custom flat file destination component that builds the output based on a specific layout. It works as long as the upstream column names match my output names. What I want is to allow non-matching columns to be mapped by the user as they can in a stock flat file destination. The closest that I have been able to come is to get the "column mappings" tab to show up and populate the "Available Input Columns" by setting ExternalmetadataColumnCollection.IsUsed to true on the input. The problem is that the "Available destination columns" box is always empty. I have tried the IsUsed property on the output and pretty much every other property that I could find. On the Input and Output properties all of my columns show up under the output as both External and Output columns. Is there a separate collection for "destination" columns that I can't find? It's getting a little frustrating, is this something that can be done or do I have to write a custom UI to make it happen?

Thanks!
Harry

Have you added columns to the external metadata column collection for you destination? The available destination columns come from this collection so if you haven't put any columns there then there are none to be displayed.

Matt

|||

Matt,

Thanks for your response. Yes, I think that I have added the columns to the external metadata for the output. The output is built from a sql server table containing the layout. The layout (copybook) is supplied via a custom property. All of the columns show up correctly on the input and output page and there is an external and output list there. Here is my code that builds the output. I have also tried setting the SyncronousInputID and it doesn't make any difference.
(Please keep in mind that I am just learning C# )

Thanks!
Harry

IDTSOutput90 output = this.ComponentMetaData.OutputCollection.New();
output.Name = _copybook;
output.ExternalMetadataColumnCollection.IsUsed = true;
output.SynchronousInputID = 0;
IDTSOutputColumnCollection90 outCols = output.OutputColumnCollection;
IDTSExternalMetadataColumnCollection90 extCols = output.ExternalMetadataColumnCollection;
SqlConnection sqlConn = new SqlConnection();
UpdateConnectionString();
sqlConn.ConnectionString = _connectionString;
sqlConn.Open();
SqlCommand sqlCmd = sqlConn.CreateCommand();
sqlCmd.CommandText = "Select ColumnName, ColumnType, Length, Precision, Scale" +
" From " + _copybook +
" Order By ColumnSeq";
SqlDataReader sqlRdr = sqlCmd.ExecuteReader();
DataType dtColType;
while (sqlRdr.Read()) {
string ColumnName = sqlRdr.GetString(0).Trim();
string ColumnType = sqlRdr.GetString(1).Trim();
Int16 Len = sqlRdr.GetInt16(2);
Int16 Prec = sqlRdr.GetInt16(3);
Int16 Scale = sqlRdr.GetInt16(4);
IDTSOutputColumn90 outColumn = outCols.New();
outColumn.Name = ColumnName;
switch (ColumnType) {
case "Z":
dtColType = DataType.DT_NUMERIC;
Len = 0;
break;
case "P":
dtColType = DataType.DT_NUMERIC;
Len = 0;
break;
case "B":
if (Len > 4)
dtColType = DataType.DT_I8;
else if (Len > 2)
dtColType = DataType.DT_I4;
else
dtColType = DataType.DT_I2;
Len = 0;
break;
default:
dtColType = DataType.DT_WSTR;
break;
}
outColumn.SetDataTypeProperties(dtColType, Len, Prec, Scale, 0);
IDTSExternalMetadataColumn90 extColumn = extCols.New();
extColumn.Name = outColumn.Name;
extColumn.DataType = outColumn.DataType;
extColumn.Length = outColumn.Length;
extColumn.Precision = outColumn.Precision;
extColumn.Scale = outColumn.Scale;
extColumn.CodePage = outColumn.CodePage;
outColumn.ExternalMetadataColumnID = extColumn.ID;
}

sqlRdr.Close();
sqlCmd.Dispose();
sqlConn.Close();
sqlConn.Dispose();

|||

Well, I don't know if this is the only problem but destinations don't have outputs only inputs (genrally speaking anyhow). A destination usually just has an input (and many have an error output, but that is just for rows that fail to get written to the destination. You would then add external columns to the input's external columns collection and map the column to the associated input column.

Matt

|||

Matt,

Thanks, that was it. I didn't know that the mapping relation was between the input and the external metadata for the input. It makes sense, I was just stuck on mapping input to output. This is really supposed to be a tranformation so that's where the output came from. I just made it a destination to see if that was my issue. It probably did look a bit odd to have an output on a destination

Again, thank you for your help.

Harry

Column limit

This could belong in the CRM community but I thought I'd come here first.
I am trying to add for custom fields to the CRM Schema manager. It creates
the database portion. As I understand it, SQL can have 1024 columns per base
table. I just hit 256 and stopped cold. I find it odd that it stopped at the
8 bit mark. I have been using Picklists, Memo fields and boolean fields for
the most part.
Any insight is greatly appreciated.
~Graham
Graham,
The maximum row size for a data page is 8060 bytes. Perhaps this is the
limit you're bumping up against.
HTH
Jerry
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
> This could belong in the CRM community but I thought I'd come here first.
> I am trying to add for custom fields to the CRM Schema manager. It creates
> the database portion. As I understand it, SQL can have 1024 columns per
> base
> table. I just hit 256 and stopped cold. I find it odd that it stopped at
> the
> 8 bit mark. I have been using Picklists, Memo fields and boolean fields
> for
> the most part.
> Any insight is greatly appreciated.
> ~Graham
|||In addition to Jerry's post:
"stopped cold" doesn't give us much to go on. Try adding the column (ALTER TABLE ... ADD ...) or
creating the table with that many column (whichever applies to you) using Query Analyzer. If you get
an error, the problem is at the SQL Server side, and you can post the error message. If not, the
problem is outside SQL Server. Also, you can backup and restore your CRM database on another
machine, or another database name if you don't want to mess about with your production database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
> This could belong in the CRM community but I thought I'd come here first.
> I am trying to add for custom fields to the CRM Schema manager. It creates
> the database portion. As I understand it, SQL can have 1024 columns per base
> table. I just hit 256 and stopped cold. I find it odd that it stopped at the
> 8 bit mark. I have been using Picklists, Memo fields and boolean fields for
> the most part.
> Any insight is greatly appreciated.
> ~Graham
|||Its not the 8000 limit.. I learned that one the hard way already. The error
in the event viewer has no error code. Basically the errors say " didnt
create the column" and "can't create the column" no explaination or clues as
to why.
Im starting to think its more about how CRM controls SQL and that it's a CRM
limitation.
"Tibor Karaszi" wrote:

> In addition to Jerry's post:
> "stopped cold" doesn't give us much to go on. Try adding the column (ALTER TABLE ... ADD ...) or
> creating the table with that many column (whichever applies to you) using Query Analyzer. If you get
> an error, the problem is at the SQL Server side, and you can post the error message. If not, the
> problem is outside SQL Server. Also, you can backup and restore your CRM database on another
> machine, or another database name if you don't want to mess about with your production database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
> news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
>
|||> Im starting to think its more about how CRM controls SQL and that it's a CRM
> limitation.
As I said, try using Query Analyzer to do the change/create and see if you get an error from SQL
Server. Then you know. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:4182B61F-3F37-43D7-BB2E-A575A3C154E0@.microsoft.com...[vbcol=seagreen]
> Its not the 8000 limit.. I learned that one the hard way already. The error
> in the event viewer has no error code. Basically the errors say " didnt
> create the column" and "can't create the column" no explaination or clues as
> to why.
> Im starting to think its more about how CRM controls SQL and that it's a CRM
> limitation.
> "Tibor Karaszi" wrote:

Column limit

This could belong in the CRM community but I thought I'd come here first.
I am trying to add for custom fields to the CRM Schema manager. It creates
the database portion. As I understand it, SQL can have 1024 columns per base
table. I just hit 256 and stopped cold. I find it odd that it stopped at the
8 bit mark. I have been using Picklists, Memo fields and boolean fields for
the most part.
Any insight is greatly appreciated.
~GrahamGraham,
The maximum row size for a data page is 8060 bytes. Perhaps this is the
limit you're bumping up against.
HTH
Jerry
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
> This could belong in the CRM community but I thought I'd come here first.
> I am trying to add for custom fields to the CRM Schema manager. It creates
> the database portion. As I understand it, SQL can have 1024 columns per
> base
> table. I just hit 256 and stopped cold. I find it odd that it stopped at
> the
> 8 bit mark. I have been using Picklists, Memo fields and boolean fields
> for
> the most part.
> Any insight is greatly appreciated.
> ~Graham|||In addition to Jerry's post:
"stopped cold" doesn't give us much to go on. Try adding the column (ALTER T
ABLE ... ADD ...) or
creating the table with that many column (whichever applies to you) using Qu
ery Analyzer. If you get
an error, the problem is at the SQL Server side, and you can post the error
message. If not, the
problem is outside SQL Server. Also, you can backup and restore your CRM dat
abase on another
machine, or another database name if you don't want to mess about with your
production database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
> This could belong in the CRM community but I thought I'd come here first.
> I am trying to add for custom fields to the CRM Schema manager. It creates
> the database portion. As I understand it, SQL can have 1024 columns per ba
se
> table. I just hit 256 and stopped cold. I find it odd that it stopped at t
he
> 8 bit mark. I have been using Picklists, Memo fields and boolean fields fo
r
> the most part.
> Any insight is greatly appreciated.
> ~Graham|||Its not the 8000 limit.. I learned that one the hard way already. The error
in the event viewer has no error code. Basically the errors say " didnt
create the column" and "can't create the column" no explaination or clues as
to why.
Im starting to think its more about how CRM controls SQL and that it's a CRM
limitation.
"Tibor Karaszi" wrote:

> In addition to Jerry's post:
> "stopped cold" doesn't give us much to go on. Try adding the column (ALTER
TABLE ... ADD ...) or
> creating the table with that many column (whichever applies to you) using
Query Analyzer. If you get
> an error, the problem is at the SQL Server side, and you can post the erro
r message. If not, the
> problem is outside SQL Server. Also, you can backup and restore your CRM d
atabase on another
> machine, or another database name if you don't want to mess about with you
r production database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
> news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
>|||> Im starting to think its more about how CRM controls SQL and that it's a CRMn">
> limitation.
As I said, try using Query Analyzer to do the change/create and see if you g
et an error from SQL
Server. Then you know. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:4182B61F-3F37-43D7-BB2E-A575A3C154E0@.microsoft.com...[vbcol=seagreen]
> Its not the 8000 limit.. I learned that one the hard way already. The erro
r
> in the event viewer has no error code. Basically the errors say " didnt
> create the column" and "can't create the column" no explaination or clues
as
> to why.
> Im starting to think its more about how CRM controls SQL and that it's a C
RM
> limitation.
> "Tibor Karaszi" wrote:
>

Column limit

This could belong in the CRM community but I thought I'd come here first.
I am trying to add for custom fields to the CRM Schema manager. It creates
the database portion. As I understand it, SQL can have 1024 columns per base
table. I just hit 256 and stopped cold. I find it odd that it stopped at the
8 bit mark. I have been using Picklists, Memo fields and boolean fields for
the most part.
Any insight is greatly appreciated.
~GrahamGraham,
The maximum row size for a data page is 8060 bytes. Perhaps this is the
limit you're bumping up against.
HTH
Jerry
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
> This could belong in the CRM community but I thought I'd come here first.
> I am trying to add for custom fields to the CRM Schema manager. It creates
> the database portion. As I understand it, SQL can have 1024 columns per
> base
> table. I just hit 256 and stopped cold. I find it odd that it stopped at
> the
> 8 bit mark. I have been using Picklists, Memo fields and boolean fields
> for
> the most part.
> Any insight is greatly appreciated.
> ~Graham|||In addition to Jerry's post:
"stopped cold" doesn't give us much to go on. Try adding the column (ALTER TABLE ... ADD ...) or
creating the table with that many column (whichever applies to you) using Query Analyzer. If you get
an error, the problem is at the SQL Server side, and you can post the error message. If not, the
problem is outside SQL Server. Also, you can backup and restore your CRM database on another
machine, or another database name if you don't want to mess about with your production database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
> This could belong in the CRM community but I thought I'd come here first.
> I am trying to add for custom fields to the CRM Schema manager. It creates
> the database portion. As I understand it, SQL can have 1024 columns per base
> table. I just hit 256 and stopped cold. I find it odd that it stopped at the
> 8 bit mark. I have been using Picklists, Memo fields and boolean fields for
> the most part.
> Any insight is greatly appreciated.
> ~Graham|||Its not the 8000 limit.. I learned that one the hard way already. The error
in the event viewer has no error code. Basically the errors say " didnt
create the column" and "can't create the column" no explaination or clues as
to why.
Im starting to think its more about how CRM controls SQL and that it's a CRM
limitation.
"Tibor Karaszi" wrote:
> In addition to Jerry's post:
> "stopped cold" doesn't give us much to go on. Try adding the column (ALTER TABLE ... ADD ...) or
> creating the table with that many column (whichever applies to you) using Query Analyzer. If you get
> an error, the problem is at the SQL Server side, and you can post the error message. If not, the
> problem is outside SQL Server. Also, you can backup and restore your CRM database on another
> machine, or another database name if you don't want to mess about with your production database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
> news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
> > This could belong in the CRM community but I thought I'd come here first.
> >
> > I am trying to add for custom fields to the CRM Schema manager. It creates
> > the database portion. As I understand it, SQL can have 1024 columns per base
> > table. I just hit 256 and stopped cold. I find it odd that it stopped at the
> > 8 bit mark. I have been using Picklists, Memo fields and boolean fields for
> > the most part.
> >
> > Any insight is greatly appreciated.
> >
> > ~Graham
>|||> Im starting to think its more about how CRM controls SQL and that it's a CRM
> limitation.
As I said, try using Query Analyzer to do the change/create and see if you get an error from SQL
Server. Then you know. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
news:4182B61F-3F37-43D7-BB2E-A575A3C154E0@.microsoft.com...
> Its not the 8000 limit.. I learned that one the hard way already. The error
> in the event viewer has no error code. Basically the errors say " didnt
> create the column" and "can't create the column" no explaination or clues as
> to why.
> Im starting to think its more about how CRM controls SQL and that it's a CRM
> limitation.
> "Tibor Karaszi" wrote:
>> In addition to Jerry's post:
>> "stopped cold" doesn't give us much to go on. Try adding the column (ALTER TABLE ... ADD ...) or
>> creating the table with that many column (whichever applies to you) using Query Analyzer. If you
>> get
>> an error, the problem is at the SQL Server side, and you can post the error message. If not, the
>> problem is outside SQL Server. Also, you can backup and restore your CRM database on another
>> machine, or another database name if you don't want to mess about with your production database.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "GDaxon" <GDaxon@.discussions.microsoft.com> wrote in message
>> news:CCC35A02-7936-4538-AE89-338C4E47ED3A@.microsoft.com...
>> > This could belong in the CRM community but I thought I'd come here first.
>> >
>> > I am trying to add for custom fields to the CRM Schema manager. It creates
>> > the database portion. As I understand it, SQL can have 1024 columns per base
>> > table. I just hit 256 and stopped cold. I find it odd that it stopped at the
>> > 8 bit mark. I have been using Picklists, Memo fields and boolean fields for
>> > the most part.
>> >
>> > Any insight is greatly appreciated.
>> >
>> > ~Graham
>>

Colors in legend does not match bar graph

Hi

I have a bar graph with custom colors applied to it.
The custom colors displays nicely in the bar graph, but not in the legend.

The legend displays a mix of some of the "correct" custom colors, and some of the "wrong" standard colors. The colors in the legend vary, depending on the parameters passed to the report. Sometimes legend colors are all right, other times all wrong but must times a mix of right and wrongs.

The value for the custom color of the graph is retrieved from a field in the dataset. However I doubt that this is the cause of the error because, the bar graph colors are correct at all times, and I have validated thet correct color names are actually in the dataset.

Hope someone can help me out with this one...

/Henrik

The legend color always depends on the color of the first datapoint in a chart series.

Also, is a series grouping present in the chart? In that case you have to make sure that e.g. instead of using =Code.PickColor( Fields!ResultSort.Value), you should use this expression that uses the First() aggregate: =Code.PickColor(First(Fields!ResultSort.Value, "chart1_SeriesGroup1")

The important part is the aggregate scope which has to be identical to the chart series grouping name. Just using the First aggregate without the scope will give you incorrect results, because the aggregate will be just scoped for every chart datapoint (and therefore null if you don't have any datapoints for a particular series group / category group combination).

Another option you may consider is using a "custom legend" which is not drawn inside the chart but created by a table besides the chart. My sample and instructions for this can be found in the following blog article:
http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx

-- Robert

|||

Thanks a lot Robert.
It was the aggregate scope I was missing. It's all working fine now.

/Henrik

|||

Does this apply for Gray Scale? I am having trouble when I set a chart to use Gray Scale the legend shows the first set of patterns but the remaining patterns are all random. I tried adding the scope to the Data section of the chart but it doesn't change the behavior with Gray Scale. Also, if it's a bug, is there a way that I can specify fill patterns in case I write a function to pick the pattern based on the series? Thanks!

Colors in legend does not match bar graph

Hi

I have a bar graph with custom colors applied to it.
The custom colors displays nicely in the bar graph, but not in the legend.

The legend displays a mix of some of the "correct" custom colors, and some of the "wrong" standard colors. The colors in the legend vary, depending on the parameters passed to the report. Sometimes legend colors are all right, other times all wrong but must times a mix of right and wrongs.

The value for the custom color of the graph is retrieved from a field in the dataset. However I doubt that this is the cause of the error because, the bar graph colors are correct at all times, and I have validated thet correct color names are actually in the dataset.

Hope someone can help me out with this one...

/Henrik

The legend color always depends on the color of the first datapoint in a chart series.

Also, is a series grouping present in the chart? In that case you have to make sure that e.g. instead of using =Code.PickColor( Fields!ResultSort.Value), you should use this expression that uses the First() aggregate: =Code.PickColor(First(Fields!ResultSort.Value, "chart1_SeriesGroup1")

The important part is the aggregate scope which has to be identical to the chart series grouping name. Just using the First aggregate without the scope will give you incorrect results, because the aggregate will be just scoped for every chart datapoint (and therefore null if you don't have any datapoints for a particular series group / category group combination).

Another option you may consider is using a "custom legend" which is not drawn inside the chart but created by a table besides the chart. My sample and instructions for this can be found in the following blog article:
http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx

-- Robert

|||

Thanks a lot Robert.
It was the aggregate scope I was missing. It's all working fine now.

/Henrik

|||

Does this apply for Gray Scale? I am having trouble when I set a chart to use Gray Scale the legend shows the first set of patterns but the remaining patterns are all random. I tried adding the scope to the Data section of the chart but it doesn't change the behavior with Gray Scale. Also, if it's a bug, is there a way that I can specify fill patterns in case I write a function to pick the pattern based on the series? Thanks!

colors

Does anyone know if you can update background colors in RS 2005 using hex
values? It looks like you can only assign named colors. When I go to
custom colors and use the palette and assign a custom color, RS puts a hex
value in the expressio box, but it returns an error that it is an
un-recognized color.
Thanks
BillBill,
In theory you can use an expression for the background colour.
I have tried expressions like =rgb(255,0,255) which the Expression
Editor accepts as a syntactically correct expression. Stylistically
it's a bit overpowering! :)
However, although the expression shows in the textbox for the
BackgroundColor property in the Properties pane it is ignored when
previewing.
I assume it is a bug in September CTP.
Andrew Watt
MVP - InfoPath
On Mon, 17 Oct 2005 22:54:18 -0700, "wsaHarem" <spammers@.screwYou.com>
wrote:
>Does anyone know if you can update background colors in RS 2005 using hex
>values? It looks like you can only assign named colors. When I go to
>custom colors and use the palette and assign a custom color, RS puts a hex
>value in the expressio box, but it returns an error that it is an
>un-recognized color.
>Thanks
>Bill|||A color value in RDL is a either a color name or a hex HTML color string of
the form #HHHHHH. See also:
http://msdn.microsoft.com/library/default.asp?url=/workshop/author/dhtml/reference/colors/colors.asp
If you want to use the RGB function, you would need to convert the resulting
integer into a string. I.e.
=String.Format("#{0:x6}",RGB(255, 0, 255))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew Watt [MVP - InfoPath]" <SVGDeveloper@.aol.com> wrote in message
news:m8i9l1l10dtp2c27q35bpdmmujkleb97mf@.4ax.com...
> Bill,
> In theory you can use an expression for the background colour.
> I have tried expressions like =rgb(255,0,255) which the Expression
> Editor accepts as a syntactically correct expression. Stylistically
> it's a bit overpowering! :)
> However, although the expression shows in the textbox for the
> BackgroundColor property in the Properties pane it is ignored when
> previewing.
> I assume it is a bug in September CTP.
> Andrew Watt
> MVP - InfoPath
>
> On Mon, 17 Oct 2005 22:54:18 -0700, "wsaHarem" <spammers@.screwYou.com>
> wrote:
>>Does anyone know if you can update background colors in RS 2005 using hex
>>values? It looks like you can only assign named colors. When I go to
>>custom colors and use the palette and assign a custom color, RS puts a hex
>>value in the expressio box, but it returns an error that it is an
>>un-recognized color.
>>Thanks
>>Bill
>|||Thanks, Robert.
Unfortunately the Expression Editor doesn't like your code (or my
attempts to correct it). Versions which the Expression Editor likes
seem to be ignored in display.
Nor have I found a way to express a custom colour using the Hex
notation that actually displays.
Any further suggestions?
Thanks
Andrew Watt
MVP - InfoPath
On Tue, 18 Oct 2005 19:54:20 -0700, "Robert Bruckner [MSFT]"
<robruc@.online.microsoft.com> wrote:
>A color value in RDL is a either a color name or a hex HTML color string of
>the form #HHHHHH. See also:
>http://msdn.microsoft.com/library/default.asp?url=/workshop/author/dhtml/reference/colors/colors.asp
>If you want to use the RGB function, you would need to convert the resulting
>integer into a string. I.e.
>=String.Format("#{0:x6}",RGB(255, 0, 255))
>
>-- Robert
>This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>"Andrew Watt [MVP - InfoPath]" <SVGDeveloper@.aol.com> wrote in message
>news:m8i9l1l10dtp2c27q35bpdmmujkleb97mf@.4ax.com...
>> Bill,
>> In theory you can use an expression for the background colour.
>> I have tried expressions like =rgb(255,0,255) which the Expression
>> Editor accepts as a syntactically correct expression. Stylistically
>> it's a bit overpowering! :)
>> However, although the expression shows in the textbox for the
>> BackgroundColor property in the Properties pane it is ignored when
>> previewing.
>> I assume it is a bug in September CTP.
>> Andrew Watt
>> MVP - InfoPath
>>
>> On Mon, 17 Oct 2005 22:54:18 -0700, "wsaHarem" <spammers@.screwYou.com>
>> wrote:
>>Does anyone know if you can update background colors in RS 2005 using hex
>>values? It looks like you can only assign named colors. When I go to
>>custom colors and use the palette and assign a custom color, RS puts a hex
>>value in the expressio box, but it returns an error that it is an
>>un-recognized color.
>>Thanks
>>Bill
>|||The expression is valid - just try to run the report. The expression editor
marking the expression as invalid at design time is a bug.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew Watt [MVP - InfoPath]" <SVGDeveloper@.aol.com> wrote in message
news:vifcl1t2g9o34g6utcdsphe3u1rq6vkcqn@.4ax.com...
> Thanks, Robert.
> Unfortunately the Expression Editor doesn't like your code (or my
> attempts to correct it). Versions which the Expression Editor likes
> seem to be ignored in display.
> Nor have I found a way to express a custom colour using the Hex
> notation that actually displays.
> Any further suggestions?
> Thanks
> Andrew Watt
> MVP - InfoPath
> On Tue, 18 Oct 2005 19:54:20 -0700, "Robert Bruckner [MSFT]"
> <robruc@.online.microsoft.com> wrote:
>>A color value in RDL is a either a color name or a hex HTML color string
>>of
>>the form #HHHHHH. See also:
>>http://msdn.microsoft.com/library/default.asp?url=/workshop/author/dhtml/reference/colors/colors.asp
>>If you want to use the RGB function, you would need to convert the
>>resulting
>>integer into a string. I.e.
>>=String.Format("#{0:x6}",RGB(255, 0, 255))
>>
>>-- Robert
>>This posting is provided "AS IS" with no warranties, and confers no
>>rights.
>>
>>
>>"Andrew Watt [MVP - InfoPath]" <SVGDeveloper@.aol.com> wrote in message
>>news:m8i9l1l10dtp2c27q35bpdmmujkleb97mf@.4ax.com...
>> Bill,
>> In theory you can use an expression for the background colour.
>> I have tried expressions like =rgb(255,0,255) which the Expression
>> Editor accepts as a syntactically correct expression. Stylistically
>> it's a bit overpowering! :)
>> However, although the expression shows in the textbox for the
>> BackgroundColor property in the Properties pane it is ignored when
>> previewing.
>> I assume it is a bug in September CTP.
>> Andrew Watt
>> MVP - InfoPath
>>
>> On Mon, 17 Oct 2005 22:54:18 -0700, "wsaHarem" <spammers@.screwYou.com>
>> wrote:
>>Does anyone know if you can update background colors in RS 2005 using
>>hex
>>values? It looks like you can only assign named colors. When I go
>>to
>>custom colors and use the palette and assign a custom color, RS puts a
>>hex
>>value in the expressio box, but it returns an error that it is an
>>un-recognized color.
>>Thanks
>>Bill
>>
>|||Thanks, Robert.
There also seem to be bugs in the Layout tab and in the Preview tab
relating to this.
I will wait and see what the situation is in RTM then, if necessary,
post some bugs to Product Feedback Center then.
Andrew Watt
MVP - InfoPath
On Wed, 19 Oct 2005 09:29:49 -0700, "Robert Bruckner [MSFT]"
<robruc@.online.microsoft.com> wrote:
>The expression is valid - just try to run the report. The expression editor
>marking the expression as invalid at design time is a bug.
>-- Robert
>This posting is provided "AS IS" with no warranties, and confers no rights.
>
>"Andrew Watt [MVP - InfoPath]" <SVGDeveloper@.aol.com> wrote in message
>news:vifcl1t2g9o34g6utcdsphe3u1rq6vkcqn@.4ax.com...
>> Thanks, Robert.
>> Unfortunately the Expression Editor doesn't like your code (or my
>> attempts to correct it). Versions which the Expression Editor likes
>> seem to be ignored in display.
>> Nor have I found a way to express a custom colour using the Hex
>> notation that actually displays.
>> Any further suggestions?
>> Thanks
>> Andrew Watt
>> MVP - InfoPath
>> On Tue, 18 Oct 2005 19:54:20 -0700, "Robert Bruckner [MSFT]"
>> <robruc@.online.microsoft.com> wrote:
>>A color value in RDL is a either a color name or a hex HTML color string
>>of
>>the form #HHHHHH. See also:
>>http://msdn.microsoft.com/library/default.asp?url=/workshop/author/dhtml/reference/colors/colors.asp
>>If you want to use the RGB function, you would need to convert the
>>resulting
>>integer into a string. I.e.
>>=String.Format("#{0:x6}",RGB(255, 0, 255))
>>
>>-- Robert
>>This posting is provided "AS IS" with no warranties, and confers no
>>rights.
>>
>>
>>"Andrew Watt [MVP - InfoPath]" <SVGDeveloper@.aol.com> wrote in message
>>news:m8i9l1l10dtp2c27q35bpdmmujkleb97mf@.4ax.com...
>> Bill,
>> In theory you can use an expression for the background colour.
>> I have tried expressions like =rgb(255,0,255) which the Expression
>> Editor accepts as a syntactically correct expression. Stylistically
>> it's a bit overpowering! :)
>> However, although the expression shows in the textbox for the
>> BackgroundColor property in the Properties pane it is ignored when
>> previewing.
>> I assume it is a bug in September CTP.
>> Andrew Watt
>> MVP - InfoPath
>>
>> On Mon, 17 Oct 2005 22:54:18 -0700, "wsaHarem" <spammers@.screwYou.com>
>> wrote:
>>Does anyone know if you can update background colors in RS 2005 using
>>hex
>>values? It looks like you can only assign named colors. When I go
>>to
>>custom colors and use the palette and assign a custom color, RS puts a
>>hex
>>value in the expressio box, but it returns an error that it is an
>>un-recognized color.
>>Thanks
>>Bill
>>
>