Concatenate Query Results into a String most effectively

Hi all,
I want to concatenate the results from a query in a string in the most
performance-friendly manner. E.g. executing
SELECT Code, Price
FROM Inventory
returns
ABC, 120
EFG, 200
HIJ, 245.50
...
Instead of returning the result in the format above, I want it to look like
this
ABC, 120/ EFG, 200/ HIJ, 245.50/ ...
returned as a string, or a column.
The normal way is to use cursor, and loop all the rows and concatenate the
values in each row into the string. BUT I read cursors are not
performance-friendly.
So what is the most performance-friendly SQL statement(s) that I can write
(preferably in one statement) to do the above?
Please kindly advise. TQ very much in advance.Hi
Why not doung such things on the client side'?
CREATE TABLE #Test
(
col CHAR(1)NOT NULL,
col1 DECIMAL(5,2) NOT NULL
)
INSERT INTO #Test VALUES('A',120)
INSERT INTO #Test VALUES('B',155)
INSERT INTO #Test VALUES('C',20.33)
DECLARE @.st VARCHAR(100)
SET @.st=''
SELECT @.st=@.st+col+','+CAST(col1 AS VARCHAR(10))+'/' FROM #Test
SELECT @.st
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:F18E50B2-F6D0-4FA8-BC2D-2242935F01FE@.microsoft.com...
> Hi all,
> I want to concatenate the results from a query in a string in the most
> performance-friendly manner. E.g. executing
> SELECT Code, Price
> FROM Inventory
> returns
> ABC, 120
> EFG, 200
> HIJ, 245.50
> ...
> Instead of returning the result in the format above, I want it to look
like
> this
> ABC, 120/ EFG, 200/ HIJ, 245.50/ ...
> returned as a string, or a column.
> The normal way is to use cursor, and loop all the rows and concatenate the
> values in each row into the string. BUT I read cursors are not
> performance-friendly.
> So what is the most performance-friendly SQL statement(s) that I can write
> (preferably in one statement) to do the above?
> Please kindly advise. TQ very much in advance.|||Hi,
Thanks. Cool solution. Never think abt that!
"Uri Dimant" wrote:

> Hi
> Why not doung such things on the client side'?
Well I am customizing an existing program... So try to keep my changes
minimum and yet still get good perforamance. ;)
Furthermore doing such things on client side would mean I have to write
additional VB codes and Crystal Report to reformat the whole thing. SQL
statements still so much more powerful.

>
> CREATE TABLE #Test
> (
> col CHAR(1)NOT NULL,
> col1 DECIMAL(5,2) NOT NULL
> )
> INSERT INTO #Test VALUES('A',120)
> INSERT INTO #Test VALUES('B',155)
> INSERT INTO #Test VALUES('C',20.33)
> DECLARE @.st VARCHAR(100)
> SET @.st=''
> SELECT @.st=@.st+col+','+CAST(col1 AS VARCHAR(10))+'/' FROM #Test
> SELECT @.st
>
>
> "HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
> news:F18E50B2-F6D0-4FA8-BC2D-2242935F01FE@.microsoft.com...
> like
>
>

Computed columns in temp tables

I am having a problem with using UDF as part of a temp table computed
column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name = 'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.
I do not get this error if I use a regular table.
HELP!Hi,
As far as I know, You cannot use a UDF inside the table creation. It should
be
create table #x(i int,j int,k as i+j)
insert into #x(i,j) values(1,10)
select * from #x
output will be:-
i j k
-- -- --
1 10 11
(1 row(s) affected)
Thanks
Hari
MCDBA
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> I am having a problem with using UDF as part of a temp table computed
> column. Here's the sample code:
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name => 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> DECLARE @.z INT
> SET @.z = @.x + @.y
> RETURN @.z
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (dbo.fn_test(x,y))
> )
> I receive the following error:
> Server: Msg 208, Level 16, State 1, Line 2
> Invalid object name 'dbo.fn_test'.
> I do not get this error if I use a regular table.
> HELP!
>|||Looks like the UDF is being looked up in the tempdb database. If you created
the UDF in tempdb, your table creation will succeed. I am not sure if this
is the expected behavior. I'll post again, if I find out more.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
I am having a problem with using UDF as part of a temp table computed
column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name ='fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.
I do not get this error if I use a regular table.
HELP!|||Sorry for the wrong information.
Thanks Vyas. I have never tried this option.
--
Thanks
Hari
MCDBA
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OLTxVgqWEHA.716@.TK2MSFTNGP11.phx.gbl...
> Hi,
> As far as I know, You cannot use a UDF inside the table creation. It
should
> be
> create table #x(i int,j int,k as i+j)
> insert into #x(i,j) values(1,10)
> select * from #x
> output will be:-
> i j k
> -- -- --
> 1 10 11
> (1 row(s) affected)
>
> --
> Thanks
> Hari
> MCDBA
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> > I am having a problem with using UDF as part of a temp table computed
> > column. Here's the sample code:
> >
> > IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name
=> > 'fn_test')
> > DROP FUNCTION dbo.fn_test
> > GO
> >
> > CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> > RETURNS INT AS
> > BEGIN
> > DECLARE @.z INT
> > SET @.z = @.x + @.y
> > RETURN @.z
> > END
> > GO
> >
> > CREATE TABLE #X
> > (
> > x INT,
> > y INT,
> > z AS (dbo.fn_test(x,y))
> > )
> >
> > I receive the following error:
> >
> > Server: Msg 208, Level 16, State 1, Line 2
> > Invalid object name 'dbo.fn_test'.
> >
> > I do not get this error if I use a regular table.
> > HELP!
> >
> >
>|||I have thought that was the case but no luck! I modified the code to
explicitly call the function in the database where it was created and still
got the same error. Here's modified code:
use master
GO
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name ='fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
RETURN @.x + @.y
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (master.dbo.fn_test(x,y))
)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Looks like the UDF is being looked up in the tempdb database. If you
created
> the UDF in tempdb, your table creation will succeed. I am not sure if this
> is the expected behavior. I'll post again, if I find out more.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> I am having a problem with using UDF as part of a temp table computed
> column. Here's the sample code:
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name => 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> DECLARE @.z INT
> SET @.z = @.x + @.y
> RETURN @.z
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (dbo.fn_test(x,y))
> )
> I receive the following error:
> Server: Msg 208, Level 16, State 1, Line 2
> Invalid object name 'dbo.fn_test'.
> I do not get this error if I use a regular table.
> HELP!
>
>|||As Vyas pointed out, the issue is that you have to create the function in
tempdb. I think he is right in suggesting that you can't use UDFs that
reside in a different database in the definition of a table.
--
Jacco Schalkwijk
SQL Server MVP
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:%23siqQFrWEHA.3512@.TK2MSFTNGP12.phx.gbl...
> I have thought that was the case but no luck! I modified the code to
> explicitly call the function in the database where it was created and
still
> got the same error. Here's modified code:
> use master
> GO
> IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name => 'fn_test')
> DROP FUNCTION dbo.fn_test
> GO
> CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> RETURNS INT AS
> BEGIN
> RETURN @.x + @.y
> END
> GO
> CREATE TABLE #X
> (
> x INT,
> y INT,
> z AS (master.dbo.fn_test(x,y))
> )
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:O52L6fqWEHA.2544@.TK2MSFTNGP10.phx.gbl...
> > Looks like the UDF is being looked up in the tempdb database. If you
> created
> > the UDF in tempdb, your table creation will succeed. I am not sure if
this
> > is the expected behavior. I'll post again, if I find out more.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > http://vyaskn.tripod.com/
> > Is .NET important for a database professional?
> > http://vyaskn.tripod.com/poll.htm
> >
> >
> > "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> > news:eZChvYqWEHA.2804@.TK2MSFTNGP10.phx.gbl...
> > I am having a problem with using UDF as part of a temp table computed
> > column. Here's the sample code:
> >
> > IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name
=> > 'fn_test')
> > DROP FUNCTION dbo.fn_test
> > GO
> >
> > CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
> > RETURNS INT AS
> > BEGIN
> > DECLARE @.z INT
> > SET @.z = @.x + @.y
> > RETURN @.z
> > END
> > GO
> >
> > CREATE TABLE #X
> > (
> > x INT,
> > y INT,
> > z AS (dbo.fn_test(x,y))
> > )
> >
> > I receive the following error:
> >
> > Server: Msg 208, Level 16, State 1, Line 2
> > Invalid object name 'dbo.fn_test'.
> >
> > I do not get this error if I use a regular table.
> > HELP!
> >
> >
> >
>|||How can I create a function in tempdb while inside a stored procedure? I
don't think I can do that.
Lets assume I can use sp_executesql to create the function inside a SP. What
will be a lifespan of it? Will it get dropped once the session is closed?
will it be accessible from other sessions? Will there be a name conflict
when two sessions will try and execute the same SP?
The more I get into it, the more questions I get. Hopefully I'll come out
with good knowledge! :)
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> As Vyas pointed out, the issue is that you have to create the function in
> tempdb. I think he is right in suggesting that you can't use UDFs that
> reside in a different database in the definition of a table.
>|||Create a permanent function in tempdb, like
USE tempdb
GO
CREATE FUNCTION dbo.fnTest
(@.x int, @.y int)
RETURNS int
AS
BEGIN
RETURN (@.x+@.y)
END
GO
Now using another database u can call this function during temp table
creation
eg.
USE Pubs
CREATE TABLE #temp(x int,y int, z as dbo.fnTest(x,y))
INSERT INTO #temp VALUES(1,2)
INSERT INTO #temp VALUES(3,2)
SELECT * FROM #temp
Roji. P. Thomas
SQL Server Programmer
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:eLwmCwRXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> How can I create a function in tempdb while inside a stored procedure? I
> don't think I can do that.
> Lets assume I can use sp_executesql to create the function inside a SP.
What
> will be a lifespan of it? Will it get dropped once the session is closed?
> will it be accessible from other sessions? Will there be a name conflict
> when two sessions will try and execute the same SP?
> The more I get into it, the more questions I get. Hopefully I'll come out
> with good knowledge! :)
> Steve
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
wrote
> in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > As Vyas pointed out, the issue is that you have to create the function
in
> > tempdb. I think he is right in suggesting that you can't use UDFs that
> > reside in a different database in the definition of a table.
> >
>|||Won't that function get dropped/cleaned up and will require constant
checking for its existance?
Steve
"Roji. P. Thomas" <lazydragon@.nowhere.com> wrote in message
news:e5MzPCSXEHA.3016@.tk2msftngp13.phx.gbl...
> Create a permanent function in tempdb, like
> USE tempdb
> GO
>
> CREATE FUNCTION dbo.fnTest
> (@.x int, @.y int)
> RETURNS int
> AS
> BEGIN
> RETURN (@.x+@.y)
> END
> GO
> Now using another database u can call this function during temp table
> creation
> eg.
>
> USE Pubs
>
> CREATE TABLE #temp(x int,y int, z as dbo.fnTest(x,y))
>
> INSERT INTO #temp VALUES(1,2)
> INSERT INTO #temp VALUES(3,2)
> SELECT * FROM #temp
>
> --
> Roji. P. Thomas
> SQL Server Programmer
> "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> news:eLwmCwRXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> > How can I create a function in tempdb while inside a stored procedure? I
> > don't think I can do that.
> >
> > Lets assume I can use sp_executesql to create the function inside a SP.
> What
> > will be a lifespan of it? Will it get dropped once the session is
closed?
> > will it be accessible from other sessions? Will there be a name conflict
> > when two sessions will try and execute the same SP?
> >
> > The more I get into it, the more questions I get. Hopefully I'll come
out
> > with good knowledge! :)
> >
> > Steve
> >
> > "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote
> > in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > > As Vyas pointed out, the issue is that you have to create the function
> in
> > > tempdb. I think he is right in suggesting that you can't use UDFs that
> > > reside in a different database in the definition of a table.
> > >
> >
> >
>|||You can either add the function to the model database (on the basis of which
all databases are created) or you can create a start up stored procedure to
create the function in tempdb whenever SQL Server is started. The function
will not just disappear from tempdb once you have created it, only when SQL
Server is restarted, because tempdb gets recreated then.
--
Jacco Schalkwijk
SQL Server MVP
"Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
news:ufdhcNTXEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Won't that function get dropped/cleaned up and will require constant
> checking for its existance?
> Steve
> "Roji. P. Thomas" <lazydragon@.nowhere.com> wrote in message
> news:e5MzPCSXEHA.3016@.tk2msftngp13.phx.gbl...
> > Create a permanent function in tempdb, like
> >
> > USE tempdb
> > GO
> >
> >
> > CREATE FUNCTION dbo.fnTest
> > (@.x int, @.y int)
> > RETURNS int
> > AS
> > BEGIN
> > RETURN (@.x+@.y)
> > END
> > GO
> >
> > Now using another database u can call this function during temp table
> > creation
> >
> > eg.
> >
> >
> > USE Pubs
> >
> >
> > CREATE TABLE #temp(x int,y int, z as dbo.fnTest(x,y))
> >
> >
> > INSERT INTO #temp VALUES(1,2)
> > INSERT INTO #temp VALUES(3,2)
> >
> > SELECT * FROM #temp
> >
> >
> >
> > --
> > Roji. P. Thomas
> > SQL Server Programmer
> > "Steven Yampolsky" <syampolsky@.eagleinvsys.com> wrote in message
> > news:eLwmCwRXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> > > How can I create a function in tempdb while inside a stored procedure?
I
> > > don't think I can do that.
> > >
> > > Lets assume I can use sp_executesql to create the function inside a
SP.
> > What
> > > will be a lifespan of it? Will it get dropped once the session is
> closed?
> > > will it be accessible from other sessions? Will there be a name
conflict
> > > when two sessions will try and execute the same SP?
> > >
> > > The more I get into it, the more questions I get. Hopefully I'll come
> out
> > > with good knowledge! :)
> > >
> > > Steve
> > >
> > > "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> > wrote
> > > in message news:uCXtqSrWEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > > > As Vyas pointed out, the issue is that you have to create the
function
> > in
> > > > tempdb. I think he is right in suggesting that you can't use UDFs
that
> > > > reside in a different database in the definition of a table.
> > > >
> > >
> > >
> >
> >
>

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 Query question. Using Sum and CAST operation.

Hi

Can onayone tell me what is wrong with the below code. Thank you,

SELECT P.FirstName, P.LastName, P.PortfolioID, P.PortfolioDescription, SC.Description, SC.SecurityID, SC.SecurityTypeID, SUM(T.Quantity) AS TQuantity,
CASE SC.SecurityTypeID WHEN 11 THEN CAST(SUM(T .Quantity)) WHEN 2 THEN CAST(SUM(T .Quantity *
(SELECT Price
FROM dbo.SecurityPrices AS SP WITH (Nolock)
WHERE T .SymbolID = SP.SecurityID AND PriceDate = '1/24/2007'))) / 100 ELSE CAST(SUM(T .Quantity *
(SELECT Price
FROM dbo.SecurityPrices AS SP WITH (Nolock)
WHERE T .SymbolID = SP.SecurityID AND PriceDate = '1/24/2007'))) END AS Amount, P.AccountNumber, P.AccountTypeDescription,
SC.CodeDescription, SC.CodeDescription2, SC.Symbol
FROM dbo.Transactions AS T WITH (Nolock) INNER JOIN
dbo.Portfolios AS P WITH (Nolock) ON P.PortfolioID = T.PortfolioID INNER JOIN
dbo.SFGSecuritiesAndCodes AS SC WITH (Nolock) ON T.SymbolID = SC.SecurityID INNER JOIN
dbo.Groups AS G WITH (Nolock) ON P.PortfolioID = G.PortfolioID
WHERE (T.StatusTypeID = 100) AND (G.OwningPortfolioID = 270) AND (T.TradeDate <= '1/24/2007')
GROUP BY T.SymbolID, P.FirstName, P.LastName, P.PortfolioID, SC.Description, SC.SecurityID, SC.SecurityTypeID, P.PortfolioDescription, P.AccountNumber,
P.AccountTypeDescription, SC.CodeDescription, SC.CodeDescription2, SC.Symbol

The correct syntax for CAST is CAST( <expr> as <datatype>). So fix your query and try again. Also, it is good to post the actual error message that you are getting so that we don't have to guess what might be wrong.|||

The following query will work for you..

Note: You Cannot perform an aggregate function on an expression containing an aggregate or a subquery; So you have to use INNER JOIN Instead of Subquery. It is good practice to use join rather than the subquery.

SELECT
P.FirstName,
P.LastName,
P.PortfolioID,
P.PortfolioDescription,
SC.Description,
SC.SecurityID,
SC.SecurityTypeID,
SUM(T.Quantity) AS TQuantity,
CASE SC.SecurityTypeID
WHEN 11 THEN
CAST(SUM(T .Quantity) As Int)
WHEN 2 THEN
CAST(SUM(T .Quantity * SP.Price) / 100 as INT)
ELSE
CAST(SUM(T .Quantity * SP.Price) as INT)
END AS Amount,
P.AccountNumber,
P.AccountTypeDescription,
SC.CodeDescription,
SC.CodeDescription2,
SC.Symbol
FROM
dbo.Transactions AS T WITH (Nolock)
INNER JOIN dbo.Portfolios AS P WITH (Nolock) ON P.PortfolioID = T.PortfolioID
INNER JOIN dbo.SFGSecuritiesAndCodes AS SC WITH (Nolock) ON T.SymbolID = SC.SecurityID
INNER JOIN dbo.Groups AS G WITH (Nolock) ON P.PortfolioID = G.PortfolioID
INNER JOIN dbo.SecurityPrices As SP WITH (Nolock) ON T.SymbolID = SP.SecurityID AND SP.PriceDate = '1/24/2007'
WHERE
(T.StatusTypeID = 100)
AND (G.OwningPortfolioID = 270)
AND (T.TradeDate <= '1/24/2007')
GROUP BY
T.SymbolID,
P.FirstName,
P.LastName,
P.PortfolioID,
SC.Description,
SC.SecurityID,
SC.SecurityTypeID,
P.PortfolioDescription,
P.AccountNumber,
P.AccountTypeDescription,
SC.CodeDescription,
SC.CodeDescription2,
SC.Symbol

completed successfully, but not sending and no error.

hi, i have a message queue system using sql 2005 service broker.

the code and setup is the same on both dev and live database. but

soon after i restored a live backup to dev. the queue stopped

working on dev, live is ok thou. after some trouble shooting, i

found that the server is not sending the message at all, but it says

"Command(s) completed successfully" without any error messages.

setup:

--

create message type TestQueryMessage validation = none

create contract TestQueryContract (TestQueryMessage sent by initiator)

create queue TestSenderQueue

create service TestSenderService on queue TestSenderQueue

create queue TestQueueReceiver

create service TestServiceReceiver on queue TestQueueReceiver (TestQueryContract)

send message:

-

declare @.conversationhandle uniqueidentifier;

begin dialog @.conversationhandle

from service [TestSenderService]

to service 'TestServiceReceiver'

on contract [TestQueryContract]

with encryption = off;

send on conversation @.conversationhandle

message type [TestQueryMessage] ('blah blah blah');

result:

-

Command(s) completed successfully.

but when i do "select * from TestQueueReceiver", there's nothing. and i sure nothing else had picked up the messages.

please advise. thanks a lot.

Look in sys.transmission_queue on the sender's database. The transmission_status column should indicate the problem. Most likely the broker was not enabled back after the restore operation.

HTH,
~ Remus

compiler is not recognizing my using statement for SglConnection statement

I am using ASP.NET 2.0, and am attempting to write some code to connect to the database and query a data table. The compiler is not recognizing my SqlConnection statement. It does recognize other commands. And just to make sure, I created other sql objects such as ObjectDataSource and SqlDataSource. The compiler does not find a problem with that code.

Basically the compiler is telling me that I am missing a "using" directive. The compiler is wrong though, because I am including the statement "usingSystemData" Can someone please take a look at my code below and to see if you notice what the problem might be? Note that I numbered the lines of code below. Note that I also tried putting lines 3 trhough 6 before line 2(The page directive) but that did not fix the problem The compiler still gives me the same compiler message.

Compilation Error

Description: An error occurred during the compilation of a resource required to service this request.Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)Source Error:

Line 21: SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;");

1 <asp:sqldatasource runat="server"></asp:sqldatasource>

2 <%@. Page Language="C#"%>

3 using System;

4 using System.Data;

5 using System.Collections;

6 using System.Data.SqlClient;

7

8 <script runat=server>

9

10 protected void Page_Load(object o, EventArgs e)

11 {

12 ObjectDataSource dsa; // This works no problems from the compiler here

13 SqlDataSource ds; // This works no problems from the compiler

14

15 if (IsPostBack)

16 {

17 if (AuthenticateUser(txtUsername.Text,txtPassword.Text))

18 {

19 instructions.Text = "Congratulations, your authenticated!";

20 instructions.ForeColor = System.Drawing.Color.Red;

21 SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;");

22 String sqlStmt = "Select UserName from LogIn where UserName='" + txtUsername.Text + "' and password='" + sHashedPassword + "'";

23 }

24 else

25 {

26 instructions.Text = "Please try again!";

27 instructions.ForeColor = System.Drawing.Color.Red;

28 }

29 }

30

31 }

32

33 bool AuthenticateUser(string username, string password)

34 {

35 // Authentication code goes here

36

37 }

When using the in-line server code instead of code-behind, you have to use the following syntax instead:

<%@. Import Namespace="System.Data.SqlClient" %>

Compiled Code as a data source

Does anyone have any examples of a c# program and that returns a record set
from sql server that could be compiled and referenced as a data source in an
RDL file? If so I would sure like an example of the C# and the RDL file that
references it. Thanks.no.. but MS Access can do this for sure
On Apr 19, 7:10 am, Greg Larsen <gregalar...@.removeit.msn.com> wrote:
> Does anyone have any examples of a c# program and that returns a record set
> from sql server that could be compiled and referenced as a data source in an
> RDL file? If so I would sure like an example of the C# and the RDL file that
> references it. Thanks.|||Instead use web services using c# to directly access the datasource and
reports itself. A good sample is given in the BOL.
Amarnath
"Greg Larsen" wrote:
> Does anyone have any examples of a c# program and that returns a record set
> from sql server that could be compiled and referenced as a data source in an
> RDL file? If so I would sure like an example of the C# and the RDL file that
> references it. Thanks.|||yeah.. or you could write your own compiler and install linux
I mean wtf
fuck web services
fuck C#
can I put a form in a BI project and loop through all of the reports
in my PROJECT?
until I can do this; SSRS can fuck themselves
On Apr 19, 11:28 pm, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> Instead use web services using c# to directly access the datasource and
> reports itself. A good sample is given in the BOL.
> Amarnath
> "Greg Larsen" wrote:
> > Does anyone have any examples of a c# program and that returns a record set
> > from sql server that could be compiled and referenced as a data source in an
> > RDL file? If so I would sure like an example of the C# and the RDL file that
> > references it. Thanks.

Compatibility between SQL2000 and SQL2005

Will my old applications with standard T-SQL code work againt the new
SQL2005 without modifications ?
KayHi
It depends. Mostly yes, but if you are using any syntax that Microsoft has
been warning will be depricated, or any undocumented functions, no.
You will have to test.
Regards
Mike
"Kay-Christian Hansen" wrote:

> Will my old applications with standard T-SQL code work againt the new
> SQL2005 without modifications ?
> Kay
>
>|||If there is any breaking bits, MS will issue a document before release. Thus
far, I have seen nothing that is not backward compatible in T-SQL. If you
follow good standards, you should not have any problems. There are new
additions to T-SQL in SQL 2005, however, that you might want to move to for
perf, etc.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
****************************************
*******
Think outside the box!
****************************************
*******
"Kay-Christian Hansen" <Kay@.Tempus.no> wrote in message
news:OhbU4zbOFHA.2252@.TK2MSFTNGP15.phx.gbl...
> Will my old applications with standard T-SQL code work againt the new
> SQL2005 without modifications ?
> Kay
>|||There are a few changes, for example with the new schema getup,
INFORMATION_SCHEMA.SCHEMATA doesn't work like it currently does in 2000.
There are also some very forceful changes to non-ansi outer joins (*=, =*).
For the most part, backward compatibility is pristine, but there are several
other things going forward that, while you don't have to deal with it right
away, you should definitely visit before the next version after 2005.
You really should be asking this kind of question in the SQL Server 2005
newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Kay-Christian Hansen" <Kay@.Tempus.no> wrote in message
news:OhbU4zbOFHA.2252@.TK2MSFTNGP15.phx.gbl...
> Will my old applications with standard T-SQL code work againt the new
> SQL2005 without modifications ?
> Kay
>