Having trouble getting connections

I have a custom Data Flow Destination Adapter that is looking for a particular type of ConnectionManager. I want to check that this connection exists in the Validate method. I first checked the ComponentMetedata.RuntimeConnections, but it was was an empty collection. I am guessing that it gets populated at runtime. Is there anything available for to check at design time?

Another way of asking this would be, Is there a way to programatically select which ConnectionManager gets assigned to a RuntimeConnection? Normally, this is done on the Connections tab of the Advanced Editor.

Thanks,

Graham

You can assign a connection manager at design time to your adapter and check that it is the correct type in the Validate() method. You will need to let your component know that it is expecting a connection manager.

Allan

|||I realize that I can do that by opening the advanced editor on my destination task and selecting the connection; but I dont want to do it that way. I want to Programmatically look for a connection of a specific type. Below is a code snippet of what I tried to do, but that doesnt seem to work. The connection is not set.

//this takes place in the onload handler for my DestinationAdapter form
//connections is passed in from the UI class that implements IDtsComponentUI
ConnectionManager connMan = null;
foreach (ConnectionManager cm in connections)
{
if (cm.InnerObject.GetType() == typeof(ProfileConnectionManager))
{
connMan = cm;
}
}
profConnMan = ProfileConnectionManager.FindProfileConnection(connections);
dtsComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(connMan);

|||Is there a reason you don't just get the value of ConnectionManagerType on the properties collection? Also, you're attempting to use a managed code idiom for a connection that may not be managed. Most of the connection managers are native. Is this a custom connection manager? I don't know about the ProfileConnectionManager.|||

You may also want to take a look at IDtsConnectionService.GetConnections and IDtsConnectionService.CreateConnection. Useful for writing UIs. They all work around the connection type that Kirk mentions.

|||ProfileConnectionManager is a custom connection that I have written. I want to allow a custom data flow destination adapter that I have written to look for a ProfileConnectionManager and if one is defined, add it to my CustomDestinationAdapter.ComponentMetaData.RuntimeConnections collection.

In normal situations, this is done at design time by openning the advanced editor and selecting the connection manager from a dropdown. In my situation, I am allowing only one ProfileConnectionManger to be created. So if it exists, I dont want the user to have to select it from the advanced editor; I want that to be done programmatically by me.

When you refer to properties collection, are you saying I should add a property to MyCustomDestinationAdapter.ComponentMetaData.CustomPropertyCollection and assign my custom connection as its value?

thanks,
Graham|||

I would use GetConnections, albeit in a UI, something like this-

foreach (ConnectionManager connectionManager in _dtsConnectionService.GetConnectionsOfType(connectionType))
{
comboBox.Items.Add(connectionManager.Name);
}

connectionType is the string that identifies the type of connection I want. For example if I want ADO.Net SQLClient connections I need to use-

"ADO.NET:System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089";

It is a bit annoying that I have to use the fully qualified name, but that's what works.

The ConnectionManager also has a CreationName property which I use often for validation, and Kirk suggests a ConnectionType property, so using this you could loop as you where above, and check such a property to see if it is your connection. What you should not do is try and get a .Net Type object for the connection manager, as not all of them are .Net objects.

sql

Having Custom Legend to Pie Charts

Hi All,

what I want is a custom Legend for the Pie Chart.

I have followed this article and have done what was written under

Custom Chart Color Palettes and Legends

http://msdn2.microsoft.com/en-us/library/aa964128.aspx#moressrscharts_topic4

But still the color of the legend is not uniform.

Basically I am using a stored procedure which returns two values : Circle, Total based on a parameter called Customer.

1. I have pasted the code asked in the Code Section of the report.

2. Have Disabled the 'Show Legend' box in Chart Properties.

3. Have included the following code in the Values->Appearance->Series Styel->Fill

=Code.GetColor(Fields!Circle.Value)

4. In the Category groups ->Group on-> this is my expression =Fields!Circle.Value (Basically I am not sure where

should I include Circle Field. I have tried this both by putting it in Category Groups and Series Groups.)

5. Dragged and dropped new table and have done the following

a.table1->Properties->Groups->Add->General->Group on Expression Fields!Circle.Value

b.Deleted Detail row.

c.Dragged and dropped rectangle in the column value and set the background value to =Code.GetColor(Fields!Circle.Value)

When I run the report for different customers the colors are not remaining constant. I have 17 values for Circles. So included three more colors in the "colorPalette " variable..

After doing all this still I am not able to get the same colors. Could someone tell me where I am going wrong?

Well, it looks like there was nothing wrong with the way I have been doing at all. My requierment was immaterial of whether certain value is present or not I wanted a color associated to that i.e. if for a given date(the input) my data returns the circles say Circle 1, Circle 2 with green and yellow colors associated with them respectively. So if those circles are not returned by the query then what was happening was green was getting applied to Circle 2 rather than sticking on to Circle 1. I changed my settings back at DB for this and now it is working fine.

For those of you whose results are static that is if you have data with all the circles say Circle 1 to Circle 5 all the time and you have to define your custom palette in it then this is the perfect method.Hope this helps some others.
Thank you
sql

Having Custom Legend to Pie Charts

Hi All,

what I want is a custom Legend for the Pie Chart.

I have followed this article and have done what was written under

Custom Chart Color Palettes and Legends

http://msdn2.microsoft.com/en-us/library/aa964128.aspx#moressrscharts_topic4

But still the color of the legend is not uniform.

Basically I am using a stored procedure which returns two values : Circle, Total based on a parameter called Customer.

1. I have pasted the code asked in the Code Section of the report.

2. Have Disabled the 'Show Legend' box in Chart Properties.

3. Have included the following code in the Values->Appearance->Series Styel->Fill

=Code.GetColor(Fields!Circle.Value)

4. In the Category groups ->Group on-> this is my expression =Fields!Circle.Value (Basically I am not sure where

should I include Circle Field. I have tried this both by putting it in Category Groups and Series Groups.)

5. Dragged and dropped new table and have done the following

a.table1->Properties->Groups->Add->General->Group on Expression Fields!Circle.Value

b.Deleted Detail row.

c.Dragged and dropped rectangle in the column value and set the background value to =Code.GetColor(Fields!Circle.Value)

When I run the report for different customers the colors are not remaining constant. I have 17 values for Circles. So included three more colors in the "colorPalette " variable..

After doing all this still I am not able to get the same colors. Could someone tell me where I am going wrong?

Well, it looks like there was nothing wrong with the way I have been doing at all. My requierment was immaterial of whether certain value is present or not I wanted a color associated to that i.e. if for a given date(the input) my data returns the circles say Circle 1, Circle 2 with green and yellow colors associated with them respectively. So if those circles are not returned by the query then what was happening was green was getting applied to Circle 2 rather than sticking on to Circle 1. I changed my settings back at DB for this and now it is working fine.

For those of you whose results are static that is if you have data with all the circles say Circle 1 to Circle 5 all the time and you have to define your custom palette in it then this is the perfect method.Hope this helps some others.
Thank you

Hanging Indent

This one is probably very simple, but I'm not sure. I am using SSRS 2005, and I already have a custom assembly being used in the report, but I'm hoping I won't have to use it for this.

In a table, a list of company names is being returned in a single column. Some of them are pretty long, so they wrap to the next line. If this happens I would to indent the next line. So, it should look like the following:

Company Name
Company A
Company B with a
really long name
Company C

Anybody know an easy way to do this?

The only I can think of to meet this requirement is handling this programmatically, e.g. by checking the field value length and breaking the text if exceeds the required width with vbCrLf concatenated with some padding and the remainder.

Handling Events in Report Builder

Is there any way that I can handle events using a custom library in the
Report Builder? For example, I have written my stored procs to specifically
handle the "All" case of multi-select parameters without the necessity of a
huge comma-separated list. I'd like to post-process the parameters before the
report is rendered.
Any suggestions?
--
Helen Warn, PhD
Agile Software Inc.
www.agile-soft.com"Event driven" is not possible in ReportBuilder. Will check from my side,
whether any API's can be used to handle events. But I dont think this is
possible.
Amarnath
"Helen Warn" wrote:
> Is there any way that I can handle events using a custom library in the
> Report Builder? For example, I have written my stored procs to specifically
> handle the "All" case of multi-select parameters without the necessity of a
> huge comma-separated list. I'd like to post-process the parameters before the
> report is rendered.
> Any suggestions?
> --
> Helen Warn, PhD
> Agile Software Inc.
> www.agile-soft.com

Handling conflicts with a Custom COM resolver

Hello,
I'm writing a custom COM conflict resolver to handle replication conflicts.
I'm having problems with handling a conflict caused by the violation of a foreign key constraint.
Situation:
Publisher deletes item X
Concurrently the subsriber creates item Y with a foreign key to item X
My goal of the replication is, that the deletion of item X on the publisher is rollbacked and that item Y is replicated to the publisher. So after replication, the publisher and subscriber contain both item X and item Y.
The replication procedure is:
In Upload phase, a conflict (REPOLEChange_UploadInsertFailed) occurs, because item Y cannot be created on the publisher (caused by foreign key violation). SQLServer adds item Y automatically to the MSMerge_tombstone table of the publisher.
In Download phase, the change event 'REPOLEChange_PublisherSystemDelete' happens, because the system tries to delete item Y at the subscriber.
My questions are:
How can I prevent item Y from being deleted on the subscriber?
How can I achieve that item Y is created at the publisher?
The problem is that item Y is put in the MSMerge_tombstone table of the publisher. I don't think it is nice to remove it manually from this system table.
Does someone has ideas ?
thanks in advance, Marco
What you are describing is a feature called "compensation". That is, if
we try and apply a row and an error occurs replication will compensate
for that error and delete the row in order to get both sides in sync
with each other.
This is not always the desired effect. There is a fix which will allow
you to control whether compensation occurs.
See:
http://support.microsoft.com/?kbid=828637
Please don't delete manually out of tombstone or contents
Hope this helps,
Reinout Hillmann
SQL Server Product Unit
This posting is provided "AS IS" with no warranties, and confers no rights.
Marco wrote:
> Hello,
> I'm writing a custom COM conflict resolver to handle replication conflicts.
> I'm having problems with handling a conflict caused by the violation of a foreign key constraint.
> Situation:
> Publisher deletes item X
> Concurrently the subsriber creates item Y with a foreign key to item X
> My goal of the replication is, that the deletion of item X on the publisher is rollbacked and that item Y is replicated to the publisher. So after replication, the publisher and subscriber contain both item X and item Y.
> The replication procedure is:
> In Upload phase, a conflict (REPOLEChange_UploadInsertFailed) occurs, because item Y cannot be created on the publisher (caused by foreign key violation). SQLServer adds item Y automatically to the MSMerge_tombstone table of the publisher.
> In Download phase, the change event 'REPOLEChange_PublisherSystemDelete' happens, because the system tries to delete item Y at the subscriber.
> My questions are:
> How can I prevent item Y from being deleted on the subscriber?
> How can I achieve that item Y is created at the publisher?
> The problem is that item Y is put in the MSMerge_tombstone table of the publisher. I don't think it is nice to remove it manually from this system table.
> Does someone has ideas ?
> thanks in advance, Marco
>
|||thanks!
I've tried the hotfix and it gives the desired result.
Marco

Handling a SQL Exceptions and Custom Error Messages

Hello guys,

I need some ideas on how to handle an exception or a user defined error message.

I have a procedure that creates a new user. Lets say if the e-mail address entered is already in use. What are some of the best practices for notifying the user that the e-mail address is already in use?

This is what I was thinking...

Solution #1
-----
My proc will raise an error with a message id that is great than 50000, then my DAL will recognize this is a user defined error and spit back to the user instead of trapping it.

Solution #2
-----
The proc should have an output param ( @.CreationStatus CHAR(1) ).
If the @.CreationStatus has a value for example "E", I will have lookup the value for "E" in my app and spit back that custom error message. I don't really like this option because it is too concrete.

What are some of the ways you deal with this situation?

Your suggestions are greatly appreciated.

Thank you!

You could return a @.status value with (0=success, 1= failure and an appropriate status message @.Statusmsg ( = 'Success' if @.status = 0, custom error message if @.status = 1)

From your application you could check the value in @.status and if its not 0, then display the message from @.statusmsg. You can handle this in a number of ways, It comes down to setting up one standard way of doing it across all procs and communicating with your team and documenting it so the same logic is followed across all procs.