concat_ws() in mssql

hey folks, im busy trying to make my script compatible with different types of databases

so, for mysql i use the function 'concat_ws()' in my sql query
but.. this function is not valid if u use mssql

so my question is, does anyone now a function in mssql that does the same as concat_ws() in mysql?Originally posted by bertwasbeer
hey folks, im busy trying to make my script compatible with different types of databases

so, for mysql i use the function 'concat_ws()' in my sql query
but.. this function is not valid if u use mssql

so my question is, does anyone now a function in mssql that does the same as concat_ws() in mysql?

Good question ;)
My question for you: What does the concat_ws() in mysql? I've never saw mysql.|||CONCAT_WS(separator, str1, str2,...)

CONCAT_WS() stands for CONCAT With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator can be a string as well as the rest of the arguments. If the separator is NULL, the result will be NULL. The function will skip any NULL values after the separator argument. The separator will be added between the strings to be concatenated:

mysql> SELECT CONCAT_WS(",","First name","Second name","Last Name");
-> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(",","First name",NULL,"Last Name");
-> 'First name,Last Name'|||Originally posted by bertwasbeer
CONCAT_WS(separator, str1, str2,...)

CONCAT_WS() stands for CONCAT With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator can be a string as well as the rest of the arguments. If the separator is NULL, the result will be NULL. The function will skip any NULL values after the separator argument. The separator will be added between the strings to be concatenated:

mysql> SELECT CONCAT_WS(",","First name","Second name","Last Name");
-> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(",","First name",NULL,"Last Name");
-> 'First name,Last Name'

I've never heard about function like this in MSSQL, may somebody else has. Anyway you could create User-Defined Functions for sql2000.|||What do you want to do with it?
If you are concatenating a column from selected rows you can

declare @.s varchar(8000)

select @.s = coalesce(@.s + ',') + fld
from tbl
where col2 = 'test'

This will concatenate all the values into @.s separated by a comma.

Component Property Data Types - How to set?

I have developed a component, and within ProvideComponentProperties I have added a property (reflected code)-

IDTSCustomProperty90 property1 = base.get_ComponentMetaData().get_CustomPropertyCollection().New();
property1.set_Name("Seed");
property1.set_Description("The first row number.");
property1.set_Value(1);
property1.set_ExpressionType(1);
property1.set_TypeConverter(typeof(Int32Converter).AssemblyQualifiedName);

I want this to be an Int32, but where do you set this? I am having problems, because sometimes the component ends up with a Decimal type. This only becomes apparent when I try and set an expression. I get -

TITLE: Expression Builder

Cannot convert expression value to property type.
Cannot convert 'System.Int32' to 'System.Decimal'.

The expression I tried always a variable, of type Int32.

Looking at the package Xml I can see the property has been defined as Decimal, but why when I have never told it that-

<property id="18464" name="Seed" dataType="System.Decimal" state="default" isArray="false" description="The first row number." typeConverter="System.ComponentModel.Int32Converter, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" UITypeEditor="" containsID="false" expressionType="Notify">1</property>

It would appear that there is no way to set the typ of property, bit limiting, and that SSIS assumes a type, since it clearly has one in the XML. If it gets this wrong
you have to hack the package Xml to recover.

I have tried this several times, and some packages have worked OK, and others get stuck with Decimal. Any ideas why?

You are setting the datatype when you call set_Value. Now why 1 is becoming a decimal I am not certain but that is what is being passed to set_Value. Try creating an Int32 variable and using that to set the value and see if that solves your problem.

HTH,
Matt|||So idea goes that the literal 1 is coming out as decimal, but if I use an explicitly typed set parameter I should remove the ambiguity. I shall give it a go.

Thanks.|||

Matt,

I am having a similar problem again, this time I want the custom property type to be an enumeration. I have tried casting, using variables all sorts, but if I check the type of the variable after I have set the value, it is always Int32. I know Int32 and Enums are pretty much interchangeable, but the issue is I want to set the TypeConverter to EnumConverter. It does not work, and I assume this is because the value type is not an Enum. In the properties grid I just get an empty drop-down.

In this sample _Algorithm is a private class variable defined as an Enum type of mine.

IDTSCustomPropertyCollection90 propertyCollection = ComponentMetaData.CustomPropertyCollection;
IDTSCustomProperty90 customProperty = propertyCollection.New();
customProperty.Name = "DDD";
customProperty.Description = "hkjhgh";
customProperty.Value = _Algorithm;
customProperty.TypeConverter = typeof(System.ComponentModel.EnumConverter).AssemblyQualifiedName;

If I check customProperty.Value.GetType().Name I always get Int32.

Any ideas? I don't want to have to write my own TypeConverter coded to the specific enum, that is maddness.

Component Property Data Types - How to set?

I have developed a component, and within ProvideComponentProperties I have added a property (reflected code)-

IDTSCustomProperty90 property1 = base.get_ComponentMetaData().get_CustomPropertyCollection().New();
property1.set_Name("Seed");
property1.set_Description("The first row number.");
property1.set_Value(1);
property1.set_ExpressionType(1);
property1.set_TypeConverter(typeof(Int32Converter).AssemblyQualifiedName);

I want this to be an Int32, but where do you set this? I am having problems, because sometimes the component ends up with a Decimal type. This only becomes apparent when I try and set an expression. I get -

TITLE: Expression Builder

Cannot convert expression value to property type.
Cannot convert 'System.Int32' to 'System.Decimal'.

The expression I tried always a variable, of type Int32.

Looking at the package Xml I can see the property has been defined as Decimal, but why when I have never told it that-

<property id="18464" name="Seed" dataType="System.Decimal" state="default" isArray="false" description="The first row number." typeConverter="System.ComponentModel.Int32Converter, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" UITypeEditor="" containsID="false" expressionType="Notify">1</property>

It would appear that there is no way to set the typ of property, bit limiting, and that SSIS assumes a type, since it clearly has one in the XML. If it gets this wrong
you have to hack the package Xml to recover.

I have tried this several times, and some packages have worked OK, and others get stuck with Decimal. Any ideas why?

You are setting the datatype when you call set_Value. Now why 1 is becoming a decimal I am not certain but that is what is being passed to set_Value. Try creating an Int32 variable and using that to set the value and see if that solves your problem.

HTH,
Matt|||So idea goes that the literal 1 is coming out as decimal, but if I use an explicitly typed set parameter I should remove the ambiguity. I shall give it a go.

Thanks.|||

Matt,

I am having a similar problem again, this time I want the custom property type to be an enumeration. I have tried casting, using variables all sorts, but if I check the type of the variable after I have set the value, it is always Int32. I know Int32 and Enums are pretty much interchangeable, but the issue is I want to set the TypeConverter to EnumConverter. It does not work, and I assume this is because the value type is not an Enum. In the properties grid I just get an empty drop-down.

In this sample _Algorithm is a private class variable defined as an Enum type of mine.

IDTSCustomPropertyCollection90 propertyCollection = ComponentMetaData.CustomPropertyCollection;
IDTSCustomProperty90 customProperty = propertyCollection.New();
customProperty.Name = "DDD";
customProperty.Description = "hkjhgh";
customProperty.Value = _Algorithm;
customProperty.TypeConverter = typeof(System.ComponentModel.EnumConverter).AssemblyQualifiedName;

If I check customProperty.Value.GetType().Name I always get Int32.

Any ideas? I don't want to have to write my own TypeConverter coded to the specific enum, that is maddness.

complex insert problem

I am not really sure how to phrase this problem so I have a picture to help

Okay the tables above allow me to create types of data for pages that collect small amounts of info without me having to create hundreds of small tables

so one type of data I collect is Employee First Aid training - that type of data is made up of three attributes EmployeeID (ID 3 in the Attributes table), FirstAidTrainingDate (ID 4 in the Attributes table), LocationID (id 6 in the attributes table)

This data is to be saved in the Data Table as rows

so the data table looks like this assuming my DataThing is number 10 in the Objects table and its Type 1 in the Types table the data table will record

10,3,83

10,4,10/25/2007

10,6,35

My question is this without having to do three insert statements is there a way to pass in a pipedelimited string and have some dynamic sql do all the inserts for me?

If this makes no sense please tell me what a biter I am :)

From where do you get the data to insert?

|||

Yes, it is possible to write a stored procedure that would accept a delimited string and use dynamic sql to insert / update / delete the data.

If course, that stored procedure will have to issue three insert statements or three delete statements. If you sent old/new values and checked for differences, you could cut down on the number of update statements that will be issued, otherwise you will be issuing 3 dynamically created update statements. :)

Now, there is a totally different approach you could take.

Are you familiar with instead of triggers on views?

The information that you have in your tables is sufficient
(with the possible addition of a column or three - I didn't spend the time to check)

to generated the sql to create one view and instead of trigger per "row" of data. I refer to this type of object as "compile-time dynamically generated", versus "run-time dynamically generated". If the definitions of your FARTS does not change all that often, you get the advantage of generic code without the full run-time penalty for having it.

This would allow you to dynamically issue insert/update/delete statements to the view, And it would be pretty nifty for reporting also.

The key to making the query in the view work is to create a function that returns the correct FartData Data value for a given row, by passing in the row id and the attribute id.

Your select query would look like this:

select
id
,dbo.GetFartData(id, 3) as EmployeeID
,dbo.GetFartData(id, 4) as FirstAidTrainingDate
,dbo.GetFartData(id, 6) as LocationId
from ...

Let me know if you need more info on these ideas.

Comparison of different replication types

Greetings,
I'm looking for a document that does a really good comparison of merge
replication, peer-to-peer transactional replication, and transactional
replication with updating subscribers. Does anyone know of a site or
document with such a comparison? I would especially be interested in
scalability/performance information regarding the different types of
replication.
Thanks,
Sam Bendayan
DB Architect
Ultimate Software
sam.bendayan@.gmail.com
*** Sent via Developersdex http://www.codecomments.com ***
Also, I found in one of the blog posts a statement saying that
Peer-To-Peer replication does not scale well beyond 10 nodes. Just to
be perfectly clear, does this mean that if you have more than 10 servers
in the replication topology then you will start to see performance
problems and that there is no good solution to this?
It also states that merge replication is better for a large number of
nodes. Is it possible to have these larger number of nodes synchronized
frequently (every few minutes), or will that not scale well either?
Thanks,
SB
Sam Bendayan
DB Architect
Ultimate Software
sam.bendayan@.gmail.com
*** Sent via Developersdex http://www.codecomments.com ***
|||In a PASS talk on SQL Server 2005 Replication, Philip Vaughn the Microsoft
program manager for Replication said that (IIRC) between 10-15 nodes the
network links become saturated and he did not recommend scaling it beyond
this number.
Regarding scaling merge replication, you have to look at hierarchies.
Depending on the amount of data you sync with each subscriber each time you
might have to limit the number of concurrent merge processes.
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
"Sam Bendayan" <sam.bendayan@.gmail.com> wrote in message
news:u4DP5YFvHHA.3724@.TK2MSFTNGP03.phx.gbl...
> Also, I found in one of the blog posts a statement saying that
> Peer-To-Peer replication does not scale well beyond 10 nodes. Just to
> be perfectly clear, does this mean that if you have more than 10 servers
> in the replication topology then you will start to see performance
> problems and that there is no good solution to this?
> It also states that merge replication is better for a large number of
> nodes. Is it possible to have these larger number of nodes synchronized
> frequently (every few minutes), or will that not scale well either?
> Thanks,
> SB
> Sam Bendayan
> DB Architect
> Ultimate Software
> sam.bendayan@.gmail.com
> *** Sent via Developersdex http://www.codecomments.com ***