Build = Error: Value cannot be null.

Hello Everyone,
I am having a heck of a time with this report. I have five datasets,
all interacting with stored procedures in SQL Server 2005, all using
the same parameter. The datasets run fine individually. I can run the
report fine. When I try to build or deploy the report, I get this:
-- Build started: Project: JobApplication, Configuration: Debug
--
Error: Value cannot be null.
========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped
==========
Not very descriptive unfortunately :( I'm not even sure where to
look.
Any ideas? Any help would be greatly appreciated.
Have a great day,
RyanDid one of your datasets change 'mysteriously' to text, rather than Stored
Procedure? I think I have seen the error before, but I can't remember if it
is related to this problem or not.
"Ryan" wrote:
> Hello Everyone,
> I am having a heck of a time with this report. I have five datasets,
> all interacting with stored procedures in SQL Server 2005, all using
> the same parameter. The datasets run fine individually. I can run the
> report fine. When I try to build or deploy the report, I get this:
> -- Build started: Project: JobApplication, Configuration: Debug
> --
> Error: Value cannot be null.
> ========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped
> ==========> Not very descriptive unfortunately :( I'm not even sure where to
> look.
> Any ideas? Any help would be greatly appreciated.
> Have a great day,
> Ryan
>|||Just a guess, you have refered a value which is a PK and in the report
properties you have clicked "Allow Null"
Amarnath, MCTS
"Ryan" wrote:
> Hello Everyone,
> I am having a heck of a time with this report. I have five datasets,
> all interacting with stored procedures in SQL Server 2005, all using
> the same parameter. The datasets run fine individually. I can run the
> report fine. When I try to build or deploy the report, I get this:
> -- Build started: Project: JobApplication, Configuration: Debug
> --
> Error: Value cannot be null.
> ========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped
> ==========> Not very descriptive unfortunately :( I'm not even sure where to
> look.
> Any ideas? Any help would be greatly appreciated.
> Have a great day,
> Ryan
>|||Thank you both for answering . . . in case anybody is interested:
I tried both stored procedure and text type datasets . . . both with
the same result. I'll check to make sure when it is set as a stored
procedure, it stays that way :)
Hmmm, I assume the Allow Null is for a parameter? If so, there is one
the one parameter and the "Allow null value" is unchecked.
Again, thank you for the responses; I'll give them a whirl. In the
mean time, I found that uploading the report manually works fine.
Ryan
On Mar 23, 12:56 am, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> Just a guess, you have refered a value which is a PK and in the report
> properties you have clicked "Allow Null"
> Amarnath, MCTS
> "Ryan" wrote:
> > Hello Everyone,
> > I am having a heck of a time with this report. I have five datasets,
> > all interacting with stored procedures in SQL Server 2005, all using
> > the same parameter. The datasets run fine individually. I can run the
> > report fine. When I try to build or deploy the report, I get this:
> > -- Build started: Project: JobApplication, Configuration: Debug
> > --
> > Error: Value cannot be null.
> > ========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped
> > ==========> > Not very descriptive unfortunately :( I'm not even sure where to
> > look.
> > Any ideas? Any help would be greatly appreciated.
> > Have a great day,
> > Ryan

Bug? No WSDL information fo a WEBMETHOD that points to a table-value function

Hello all,

I'm trying to create a web service that merely exposes a table value function. I have a dummy function that looks like this (in a DB called ReportingDB):

USE [ReportingDB];
DROP function [dbo].[TestFN];
GO
CREATE FUNCTION [dbo].[TestFN] ()
RETURNS
@.Table_Var TABLE (c1 int, c2 int)
AS
BEGIN
RETURN
END;
GO

My web service definition looks like this:

DROP ENDPOINT sql;
go

CREATE ENDPOINT sql
STATE = STARTED
AS HTTP(
PATH = '/sql',
AUTHENTICATION = (INTEGRATED ),
PORTS = ( CLEAR ),
CLEAR_PORT = 90,
SITE = 'mypc'
)
FOR SOAP (
WEBMETHOD 'TestFN'
(name='ReportingDB.dbo.TestFN', SCHEMA=STANDARD),
WSDL = DEFAULT,
SCHEMA = STANDARD,
DATABASE = 'ReportingDB',
NAMESPACE = 'http://tempUri.org/'
);
go

Everything succeeds, however, the problem is that the TestFN function is not visible in the generated WSDL (http://mypc:90/sql?wsdl). If I do the same with a stored procedure instead of a function, then it works as expected, so it seems that I'm missing something or that this is a bug of SQL Server 2005.

Could somebody please shed some light on this issue?

Regards,

Ianier

Hi imj,

I think this comes from the type of function that you used. Your TestFN is a Table-valued function. I tried it with a Scalar-valued function, and it worked, the method is noted in the wsdl.

Here's my sample code:

DROP function [dbo].[TestFN];

GO

CREATE FUNCTION TestFN (@.DATE datetime)

RETURNS int

AS

BEGIN

DECLARE @.retVal int

SET @.retVal=1

RETURN(@.retVal)

END

GO

DROP ENDPOINT sql;

go

CREATE ENDPOINT sql

STATE = STARTED

AS HTTP(

PATH = '/sql/test',

AUTHENTICATION = (INTEGRATED ),

PORTS = ( CLEAR ),

CLEAR_PORT = 90,

SITE = 'mypc.domain.com'

)

FOR SOAP (

WEBMETHOD

'http://tempuri.org/'.'TestFN'

(NAME = 'ReportingDB.dbo.TestFN'),

BATCHES = ENABLED,

WSDL = DEFAULT

)

go

/*GRANT CONNECT ON ENDPOINT::sql TO [xxx\yyy]*/

Best regards,
Marcus

|||Thanks Marcus,

Then it may be a SQL Server bug, since the CREATE ENDPOINT command succeeds. Should I get no answer here, I will open a bug report about this.
The reason why I would like to use table-value functions instead of stored procedures is that, since the metadata for table-value functions results is known in advance, I was expecting to see a typed dataset (or array) in the WSDL. That would let me use my function from, say, InfoPath.
Regards,

Ianier|||Hi again,

FYI, I sent a bug report: http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?FeedbackId=9e1e9b79-079d-40eb-b3f8-441432a26cbc
Nevertheless, it's hard for me to believe that such a serious issue escaped Microsoft's testing, so I'm still hoping that I'm missing something obvious.
Regards,

Ianier|||SQL Server's SOAP does not support table valued functions unfortunately. This is why it's not working. I found this out by talking to one of the testers. I cannot find where this is mentioned in the docs yet.|||Hello,

Thanks for the info.
That would be a major disapointment. This limitation is not documented and CREATE ENDPOINT reports no error, so this must be either a bug or an "undocumented odd behavior", which is the same.
IMO, not allowing table-value functions in a SOAP endpoint makes the WS feature extremely limited, if useful at all.
I'll come back to this group as soon as I get some feedback on my bug report.
Regards,

Ianier|||

The SQL 2005 Books Online documentation does have a statement stating that Table Value Functions are not supported in Native XML Web Services. Under the "Guidelines and Limitations in Native XML Web Services" topic (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8907005f-7f9b-4f53-80b8-f42729caf86b.htm), there's a bullet point saying:

Table valued user-defined functions are not supported.|||Thank you Jimmy,

In that case CREATE ENDPOINT should not succeed. This is definitely a bug.
Regards,

Ianier|||

Hi Ianier,

Thanks for the feedback.
SQL 2005 actually allows the "CREATE ENDPOINT" query to be executed before any of the webmethods are created.
ie.
CREATE ENDPOINT sql
STATE = STARTED
AS HTTP(
PATH = '/sql',
AUTHENTICATION = (INTEGRATED ),
PORTS = ( CLEAR ),
CLEAR_PORT = 90,
SITE = 'mypc'
)
FOR SOAP (
WEBMETHOD 'TestFN'
(name='ReportingDB.dbo.TestFN', SCHEMA=STANDARD),
WSDL = DEFAULT,
SCHEMA = STANDARD,
DATABASE = 'ReportingDB',
NAMESPACE = 'http://tempUri.org/'
);
go

CREATE FUNCTION [dbo].[TestFN] ()
RETURNS
@.Table_Var TABLE (c1 int, c2 int)
AS
BEGIN
RETURN
END;
GO

In this scenario is would not be possible for the CREATE ENDPOINT query to verify what the webmethod is. You can argue that this should be a bug as well, but the current behavior provides a more flexible system. Suppose we were strict about requiring the webmethods to be created before the CREATE ENDPOINT query, what would happen if after the stored procedure or function was created, someone went and ALTER it or even DELETE it? Should the different endpoints to magically re-verify that the new version of the webmethod is still valid or even there?

The current mechanism allows SQL to verify at execution/run time that the methods are valid and the user has the proper permissions.

Hope that addresses some of your concerns.
Jimmy Wu [MSFT]

|||

Hi Jimmy,

Thanks for the information.

Common sense tells me that if the function exists and is not of a supported type (i.e. a table-value one) then CREATE ENDPOINT should fail. After all, CREATE ENDPOINT fails, for example, when trying to bind a WEBMETHOD to a view because views are not supported.

If someone ALTERs or DELETEs the function afterwards in a way that is not compatible with the endpoint, either of the following should happen:
Option 1: give an error explaining that the WEBMETHOD should be modified first (i.e. there's a dependency).
Option 2: delete the WEBMETHOD automatically and give a warning to the user.

Regards,

Ianier

sql

Bug? No WSDL information fo a WEBMETHOD that points to a table-value function

Hello all,

I'm trying to create a web service that merely exposes a table value function. I have a dummy function that looks like this (in a DB called ReportingDB):

USE [ReportingDB];
DROP function [dbo].[TestFN];
GO
CREATE FUNCTION [dbo].[TestFN] ()
RETURNS
@.Table_Var TABLE (c1 int, c2 int)
AS
BEGIN
RETURN
END;
GO

My web service definition looks like this:

DROP ENDPOINT sql;
go

CREATE ENDPOINT sql
STATE = STARTED
AS HTTP(
PATH = '/sql',
AUTHENTICATION = (INTEGRATED ),
PORTS = ( CLEAR ),
CLEAR_PORT = 90,
SITE = 'mypc'
)
FOR SOAP (
WEBMETHOD 'TestFN'
(name='ReportingDB.dbo.TestFN', SCHEMA=STANDARD),
WSDL = DEFAULT,
SCHEMA = STANDARD,
DATABASE = 'ReportingDB',
NAMESPACE = 'http://tempUri.org/'
);
go

Everything succeeds, however, the problem is that the TestFN function is not visible in the generated WSDL (http://mypc:90/sql?wsdl). If I do the same with a stored procedure instead of a function, then it works as expected, so it seems that I'm missing something or that this is a bug of SQL Server 2005.

Could somebody please shed some light on this issue?

Regards,

Ianier

Hi imj,

I think this comes from the type of function that you used. Your TestFN is a Table-valued function. I tried it with a Scalar-valued function, and it worked, the method is noted in the wsdl.

Here's my sample code:

DROP function [dbo].[TestFN];

GO

CREATE FUNCTION TestFN (@.DATE datetime)

RETURNS int

AS

BEGIN

DECLARE @.retVal int

SET @.retVal=1

RETURN(@.retVal)

END

GO

DROP ENDPOINT sql;

go

CREATE ENDPOINT sql

STATE = STARTED

AS HTTP(

PATH = '/sql/test',

AUTHENTICATION = (INTEGRATED ),

PORTS = ( CLEAR ),

CLEAR_PORT = 90,

SITE = 'mypc.domain.com'

)

FOR SOAP (

WEBMETHOD

'http://tempuri.org/'.'TestFN'

(NAME = 'ReportingDB.dbo.TestFN'),

BATCHES = ENABLED,

WSDL = DEFAULT

)

go

/*GRANT CONNECT ON ENDPOINT::sql TO [xxx\yyy]*/

Best regards,
Marcus

|||Thanks Marcus,

Then it may be a SQL Server bug, since the CREATE ENDPOINT command succeeds. Should I get no answer here, I will open a bug report about this.
The reason why I would like to use table-value functions instead of stored procedures is that, since the metadata for table-value functions results is known in advance, I was expecting to see a typed dataset (or array) in the WSDL. That would let me use my function from, say, InfoPath.
Regards,

Ianier
|||Hi again,

FYI, I sent a bug report: http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?FeedbackId=9e1e9b79-079d-40eb-b3f8-441432a26cbc
Nevertheless, it's hard for me to believe that such a serious issue escaped Microsoft's testing, so I'm still hoping that I'm missing something obvious.
Regards,

Ianier
|||SQL Server's SOAP does not support table valued functions unfortunately. This is why it's not working. I found this out by talking to one of the testers. I cannot find where this is mentioned in the docs yet.|||Hello,

Thanks for the info.
That would be a major disapointment. This limitation is not documented and CREATE ENDPOINT reports no error, so this must be either a bug or an "undocumented odd behavior", which is the same.
IMO, not allowing table-value functions in a SOAP endpoint makes the WS feature extremely limited, if useful at all.
I'll come back to this group as soon as I get some feedback on my bug report.
Regards,

Ianier
|||

The SQL 2005 Books Online documentation does have a statement stating that Table Value Functions are not supported in Native XML Web Services. Under the "Guidelines and Limitations in Native XML Web Services" topic (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8907005f-7f9b-4f53-80b8-f42729caf86b.htm), there's a bullet point saying:

Table valued user-defined functions are not supported.|||Thank you Jimmy,

In that case CREATE ENDPOINT should not succeed. This is definitely a bug.
Regards,

Ianier
|||

Hi Ianier,

Thanks for the feedback.
SQL 2005 actually allows the "CREATE ENDPOINT" query to be executed before any of the webmethods are created.
ie.
CREATE ENDPOINT sql
STATE = STARTED
AS HTTP(
PATH = '/sql',
AUTHENTICATION = (INTEGRATED ),
PORTS = ( CLEAR ),
CLEAR_PORT = 90,
SITE = 'mypc'
)
FOR SOAP (
WEBMETHOD 'TestFN'
(name='ReportingDB.dbo.TestFN', SCHEMA=STANDARD),
WSDL = DEFAULT,
SCHEMA = STANDARD,
DATABASE = 'ReportingDB',
NAMESPACE = 'http://tempUri.org/'
);
go

CREATE FUNCTION [dbo].[TestFN] ()
RETURNS
@.Table_Var TABLE (c1 int, c2 int)
AS
BEGIN
RETURN
END;
GO

In this scenario is would not be possible for the CREATE ENDPOINT query to verify what the webmethod is. You can argue that this should be a bug as well, but the current behavior provides a more flexible system. Suppose we were strict about requiring the webmethods to be created before the CREATE ENDPOINT query, what would happen if after the stored procedure or function was created, someone went and ALTER it or even DELETE it? Should the different endpoints to magically re-verify that the new version of the webmethod is still valid or even there?

The current mechanism allows SQL to verify at execution/run time that the methods are valid and the user has the proper permissions.

Hope that addresses some of your concerns.
Jimmy Wu [MSFT]

|||

Hi Jimmy,

Thanks for the information.

Common sense tells me that if the function exists and is not of a supported type (i.e. a table-value one) then CREATE ENDPOINT should fail. After all, CREATE ENDPOINT fails, for example, when trying to bind a WEBMETHOD to a view because views are not supported.

If someone ALTERs or DELETEs the function afterwards in a way that is not compatible with the endpoint, either of the following should happen:
Option 1: give an error explaining that the WEBMETHOD should be modified first (i.e. there's a dependency).
Option 2: delete the WEBMETHOD automatically and give a warning to the user.

Regards,

Ianier

Bug?

Is there a bug in SQL Server Reporting Services which produces an error like
The value expression for the query parameter â'@.homebaseâ' refers to a
non-existing report parameter â'homebase_1â'
I made some changes in the dataset (1 of them) and it starts giving me this
error for no reason although the same parameter @.homebase is referred in more
than 5 datasets...
Any help is appreciated!
ThanksI got this before and did this...
1) Copy query in dataset
2) Delete dataset
3) re-create dataset
4) run-query in data view and it will re-build columns.
This is what I always do when I do anything to the columns.
"Asim" <Asim@.discussions.microsoft.com> wrote in message
news:Asim@.discussions.microsoft.com:
> Is there a bug in SQL Server Reporting Services which produces an error
> like
> The value expression for the query parameter '@.homebase' refers to a
> non-existing report parameter 'homebase_1'
> I made some changes in the dataset (1 of them) and it starts giving me
> this
> error for no reason although the same parameter @.homebase is referred in
> more
> than 5 datasets...
> Any help is appreciated!
> Thanks|||Click on the ..., go the parameters tab and remap your query parameter to
the report parameter. Sometimes RS gets confused.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Asim" <Asim@.discussions.microsoft.com> wrote in message
news:1C6BF53F-97E6-4678-B76F-66D0BB655A3F@.microsoft.com...
> Is there a bug in SQL Server Reporting Services which produces an error
like
> The value expression for the query parameter '@.homebase' refers to a
> non-existing report parameter 'homebase_1'
> I made some changes in the dataset (1 of them) and it starts giving me
this
> error for no reason although the same parameter @.homebase is referred in
more
> than 5 datasets...
> Any help is appreciated!
> Thanks
>|||Yep - bug
Lost over 2 days development time re-creating reports
NICE ONE MICROSOFT !
From http://www.developmentnow.com/g/115_2005_1_0_0_454665/Bug.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||Another option could have been to hand edit the rdl file to correct the
problem (this is an XML file).
We have done this once to use an option that the UI didn't offered in the
first release of Reporting Services...
--
Patrice
"om" <om_optical@.hotmail.com> a écrit dans le message de news:
1df92cf4-df9e-4b38-905c-79df77ba241f@.developmentnow.com...
> Yep - bug
> Lost over 2 days development time re-creating reports
> NICE ONE MICROSOFT !
>
> From http://www.developmentnow.com/g/115_2005_1_0_0_454665/Bug.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com

Bug: default value showing as query based when it is not (report m

I have a report which uses a query to populate a parameter.
The default value is not queried, this is just set to "=1".
When the report is deployed, the parameter in report manager is showing as
'query based' which is wrong.
This is a pain in the ass as when I try linking to this report, I am not
able to set the value to something else.If you use a query to specify a range of parameters then it seems to show
default as 'query based' in the report manager.
What I have discovered is that if you change something to do with the
parameters on the report and redeploy, you can pick up the changes in the
linked report by repointing the linked report at the same report, this forces
some kind of refresh to occur and although very annoying, is easier than
recreating the linked report.
<despair>Thanks to MS for providing a UI of such high quality.</despair>
"adolf garlic" wrote:
> I have a report which uses a query to populate a parameter.
> The default value is not queried, this is just set to "=1".
> When the report is deployed, the parameter in report manager is showing as
> 'query based' which is wrong.
> This is a pain in the ass as when I try linking to this report, I am not
> able to set the value to something else.

bug: data file autogrowth set to 12800%

SQL2K5 SP1

the autogrowth setting in one of my database's primary filegroup data
file keeps having the value of 12800%, which was originally set to
100MB, everytime the service is restarted. the same occurs whenever i
restore a backup of this database in our development environment.

WTF? this issue does not happen to other filegroups. only on the
primary data file. whenever this happens the 4GB data file grows to
more than 70GB (even the math is incorrect) with about 95% of UNUSED
space.

has anyone else come accross this BS or anyone knows how to prevent
this from happening? is MS aware of this not-so-funny joke?

thankshi,
XNMB wrote:
> SQL2K5 SP1
> the autogrowth setting in one of my database's primary filegroup data
> file keeps having the value of 12800%, which was originally set to
> 100MB, everytime the service is restarted. the same occurs whenever i
> restore a backup of this database in our development environment.
> WTF? this issue does not happen to other filegroups. only on the
> primary data file. whenever this happens the 4GB data file grows to
> more than 70GB (even the math is incorrect) with about 95% of UNUSED
> space.
> has anyone else come accross this BS or anyone knows how to prevent
> this from happening? is MS aware of this not-so-funny joke?

unfortunately, it's a bug, already reported as you can see at
http://lab.msdn.microsoft.com/produ...62-729b178a7fa0
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--- remove DMO to reply|||XNMB (ChristianBautista@.gmail.com) writes:
> the autogrowth setting in one of my database's primary filegroup data
> file keeps having the value of 12800%, which was originally set to
> 100MB, everytime the service is restarted. the same occurs whenever i
> restore a backup of this database in our development environment.
> WTF? this issue does not happen to other filegroups. only on the
> primary data file. whenever this happens the 4GB data file grows to
> more than 70GB (even the math is incorrect) with about 95% of UNUSED
> space.
> has anyone else come accross this BS or anyone knows how to prevent
> this from happening? is MS aware of this not-so-funny joke?

Yes, as Andrea said, this is a known issue.

It appears that the problem appears with databases from SQL 2000, and it's
old bug that causes problems.

Look at http://lab.msdn.microsoft.com/Produ...ackId=FDBK49455
for a way to fix the database while its on SQL 2000.

If the database is on SQL 2005, it may help to set an explicit growth to
a fixed size.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Bug using FOR XML AUTO with columns with type char(1)

Hello,
Having some problems with generating xml with "FOR XML AUTO". Rows with
a column with a value char(0) seems to terminate the row!
Try to run the following:
SELECT
*
FROM (
SELECT
1 AS orderItemId,
1 AS orderId,
CHAR(0) AS orderItemStatus --CHAR(0) terminates row!
UNION
SELECT
2 AS orderItemId,
1 AS orderId,
CHAR(49) AS orderItemStatus
) AS orderItem
FOR XML AUTO
Generates:
<orderItem orderItemId="1" orderId="1" orderItemStatus="
Should be(?):
<orderItem orderItemId="1" orderId="1" orderItemStatus=" "/><orderItem
orderItemId="2" orderId="1" orderItemStatus="1"/>
Anyone seen this before?
Regards, Nils
CHAR(0) is not a valid XML character. In FOR XML, we do not detect invalid
characters for performance reasons, so you will pass the XML anyway.
Depending on what the client code uses to read the XML, it may see the
CHAR(0) and decide that it is the end of the string (for instance, if the
client code uses the standard C/C++ string types).
So the recommendation is: Do not generate XML containing invalid characters
such as CHAR(0) since they are not supported by compliant XML parsers and
may result in other unforeseen behaviour.
Best regards
Michael
<nilsflemstrom@.gmail.com> wrote in message
news:1125047051.277443.138730@.z14g2000cwz.googlegr oups.com...
> Hello,
> Having some problems with generating xml with "FOR XML AUTO". Rows with
> a column with a value char(0) seems to terminate the row!
> Try to run the following:
> SELECT
> *
> FROM (
> SELECT
> 1 AS orderItemId,
> 1 AS orderId,
> CHAR(0) AS orderItemStatus --CHAR(0) terminates row!
> UNION
> SELECT
> 2 AS orderItemId,
> 1 AS orderId,
> CHAR(49) AS orderItemStatus
> ) AS orderItem
> FOR XML AUTO
> Generates:
> <orderItem orderItemId="1" orderId="1" orderItemStatus="
> Should be(?):
> <orderItem orderItemId="1" orderId="1" orderItemStatus=" "/><orderItem
> orderItemId="2" orderId="1" orderItemStatus="1"/>
> Anyone seen this before?
> Regards, Nils
>

Bug using FOR XML AUTO with columns with type char(1)

Hello,
Having some problems with generating xml with "FOR XML AUTO". Rows with
a column with a value char(0) seems to terminate the row!
Try to run the following:
SELECT
*
FROM (
SELECT
1 AS orderItemId,
1 AS orderId,
CHAR(0) AS orderItemStatus --CHAR(0) terminates row!
UNION
SELECT
2 AS orderItemId,
1 AS orderId,
CHAR(49) AS orderItemStatus
) AS orderItem
FOR XML AUTO
Generates:
<orderItem orderItemId="1" orderId="1" orderItemStatus="
Should be(?):
<orderItem orderItemId="1" orderId="1" orderItemStatus=" "/><orderItem
orderItemId="2" orderId="1" orderItemStatus="1"/>
Anyone seen this before?
Regards, NilsCHAR(0) is not a valid XML character. In FOR XML, we do not detect invalid
characters for performance reasons, so you will pass the XML anyway.
Depending on what the client code uses to read the XML, it may see the
CHAR(0) and decide that it is the end of the string (for instance, if the
client code uses the standard C/C++ string types).
So the recommendation is: Do not generate XML containing invalid characters
such as CHAR(0) since they are not supported by compliant XML parsers and
may result in other unforeseen behaviour.
Best regards
Michael
<nilsflemstrom@.gmail.com> wrote in message
news:1125047051.277443.138730@.z14g2000cwz.googlegroups.com...
> Hello,
> Having some problems with generating xml with "FOR XML AUTO". Rows with
> a column with a value char(0) seems to terminate the row!
> Try to run the following:
> SELECT
> *
> FROM (
> SELECT
> 1 AS orderItemId,
> 1 AS orderId,
> CHAR(0) AS orderItemStatus --CHAR(0) terminates row!
> UNION
> SELECT
> 2 AS orderItemId,
> 1 AS orderId,
> CHAR(49) AS orderItemStatus
> ) AS orderItem
> FOR XML AUTO
> Generates:
> <orderItem orderItemId="1" orderId="1" orderItemStatus="
> Should be(?):
> <orderItem orderItemId="1" orderId="1" orderItemStatus=" "/><orderItem
> orderItemId="2" orderId="1" orderItemStatus="1"/>
> Anyone seen this before?
> Regards, Nils
>

BUG Multi-value field in SP2

Hi,
There seems to be a bug with the multi-valed fields when only one value is
selected and that value contains only spaces. I get the message 'Please
select a value for the parameter...'. It works when I test it in Visual
Studio but it doesn't work on the Web. I'm using SQL Server 2005 with SP2.
Any ideas ? Suggestions ?
Regards
AlainOn Apr 6, 12:05 pm, "Alain Magnan" <AMag...@.Hatch.caGARBAGE> wrote:
> Hi,
> There seems to be a bug with the multi-valed fields when only one value is
> selected and that value contains only spaces. I get the message 'Please
> select a value for the parameter...'. It works when I test it in Visual
> Studio but it doesn't work on the Web. I'm using SQL Server 2005 with SP2.
> Any ideas ? Suggestions ?
> Regards
> Alain
Are you using 2 columns in the dataset that sources the multi-select
parameter? If the value field is something like 0, it might not error
out. Something like this might work (in the dataset; where Name is the
displayed item and NameID is the parameter value):
Name NameID
" " 0
"Bob" 1
"John" 2
"Bill" 3
Hope this is helpful.
Regards,
Enrique Martinez
Sr. Software Consultant

BUG - ISNUMERIC('.') = 1

ISNUMERIC('.') returns a value of 1.
This is bad, right?
Howardhttp://www.aspfaq.com/2390
"Howard Swope" <swopehATgigamotoDOTcom> wrote in message
news:eockploLGHA.3100@.tk2msftngp13.phx.gbl...
> ISNUMERIC('.') returns a value of 1.
> This is bad, right?
> Howard
>|||Howard Swope wrote:
> ISNUMERIC('.') returns a value of 1.
> This is bad, right?
>
Try this to see why isnumeric returns 1 for this string:
select cast('.' as money)
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Howard Swope (swopehATgigamotoDOTcom) writes:
> ISNUMERIC('.') returns a value of 1.
> This is bad, right?
Yes isnumeric() is bad. It returns 1 if the value can be converted to
any numeric data type. Which is quite a useles piece of information.
It's not a bug though.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

buffer.SetString not writing values!

Hello all,

I'm writing a custom component similar to the "Derived Column" transformation, in which a value of some input column is "decoded" into an output column.

Here's the code:

Code Snippet

public override void ProcessInput(int inputID, PipelineBuffer buffer){

//TODO

setupTraceListener();

IDTSOutput90 outp = ComponentMetaData.OutputCollection["DecodeOutput"];

int outId = outp.ID;

int errorOut = -1;

IDTSInput90 inp = ComponentMetaData.InputCollection.GetObjectByID(inputID);

if( ComponentMetaData.OutputCollection.Count > 1){errorOut = ComponentMetaData.OutputCollection["DecodeErrors"].ID;

}

string sourceValue;

if(!buffer.EndOfRowset){

while(buffer.NextRow()){

if(!buffer.IsNull(inputColumnBufferIndex)){

switch(inp.InputColumnCollection[0].DataType){

case DataType.DT_WSTR:

sourceValue = buffer.GetString(this.inputColumnBufferIndex);

break;

case DataType.DT_I4:

sourceValue = buffer.GetInt32(this.inputColumnBufferIndex).ToString();

break;

default: throw new Exception("Invalid Data Type!");

}

sourceValue = sourceValue.Trim();

try{

object decodedValueObj = this.mappings[sourceValue];

if(decodedValueObj == null){

throw new Exception("No mapping!");

}

string decodedValue = decodedValueObj.ToString();

switch(outp.OutputColumnCollection[0].DataType){

case DataType.DT_WSTR:

buffer.SetString(this.outputColumnBufferIndex, decodedValue);

break;

case DataType.DT_I4:

buffer.SetInt32(this.outputColumnBufferIndex, int.Parse(decodedValue));

break;

default:

throw new IOException("Invalid Data Type!");

}

buffer.DirectRow(outId);

}catch(IOException fake){

throw fake;

}catch(Exception e){

redirectOrFail(inp, errorOut, outId, buffer, e);

}

}else{

redirectOrFail(inp,errorOut,outId,buffer,new Exception("Null values cannot be mapped"));

}

}

}

}

mappings is a hash map filled in PreExecute by accessing a database, outputColumnBufferIndex is calculated like this:

Code Snippet

IDTSInput90 input = ComponentMetaData.InputCollection[0];

IDTSOutput90 output = ComponentMetaData.OutputCollection["DecodeOutput"];

IDTSInputColumn90 inputColumn = input.InputColumnCollection[0];

IDTSOutputColumn90 outputColumn = output.OutputColumnCollection[0];

this.inputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, inputColumn.LineageID);

this.outputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, outputColumn.LineageID);

I've highlighted the parts that interest me, I've been testing the component thoroughly and it is getting the input data and mappings right, but it won't write the Decoded value to the given column and leaves it empty, even though the decodedValue variable is not null.

I'm guessing theres a problem when I call buffer.SetString() but I haven't the slightest idea of what that could be.

Any help would be greatly appreciated!

Have you debugged it? Do inputColumnBufferIndex and outputColumnBufferIndex values make sense?

Do you only have one input and one output column in your component?

Are you getting any errors from SetString?

Thanks,

Bob

|||

Thank you for your reply Bob,

Yes, I did debug it and the values made sense. Thats how I know that the decodedValue was being also properly initialized.

It's working now by using:

Code Snippet

buffer[this.outputColumnBufferIndex] = decodedValue;

Don't ask me why.

I'm having all sorts of problems that have very easy but completely undocumented solutions like having to override PerformUpgrade to update the UserComponentTypeName attribute and the Version, even though I'm not changing either one as the component is still in development and not in use in any "real" packages.

Maybe my installation is corrupted somehow?

|||

Hmm, you should not be required to implement PerformUpgrade unless you are really changing your component.

Could you check your ProvideComponentProperties implementation and if there is something that might be messing up the settings?

Thanks,

Bob

|||

I don't know what to look for, this is my 3rd week with C# and SSIS.

Here's the basic idea:

Code Snippet

public override void ProvideComponentProperties(){

//FIXME

base.ProvideComponentProperties();

(set the name, dispositions, etc...)

base.RemoveAllInputsOutputsAndCustomProperties();

(add synchronous inputs and outputs)

(add custom properties)

}

I suspect it could be I'm calling RemoveAllInpusOutputsAndCustomProperties(); after base.ProvideComponentProperties(), but I don't want to touch it just yet. What are your thoughts?

Thank you very much again.

|||

Yeah, that is it.

You can safely remove that line as ProvideComponentProperties is called only once in the SSIS designer.

Alternatively, you may move that line before base.ProvideComponentProperties().

Thanks,

Bob

|||Thank you very much for your help|||

Adrian, this is not related to your question, but there is a bug in how buffer.EndOfRowset is used in the ProcessInput method in the first code sample, please see my blog for details

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

|||

Thank you very much for that link! I'll be changing that right away.

buffer.SetString not writing values!

Hello all,

I'm writing a custom component similar to the "Derived Column" transformation, in which a value of some input column is "decoded" into an output column.

Here's the code:

Code Snippet

public override void ProcessInput(int inputID, PipelineBuffer buffer){

//TODO

setupTraceListener();

IDTSOutput90 outp = ComponentMetaData.OutputCollection["DecodeOutput"];

int outId = outp.ID;

int errorOut = -1;

IDTSInput90 inp = ComponentMetaData.InputCollection.GetObjectByID(inputID);

if( ComponentMetaData.OutputCollection.Count > 1){errorOut = ComponentMetaData.OutputCollection["DecodeErrors"].ID;

}

string sourceValue;

if(!buffer.EndOfRowset){

while(buffer.NextRow()){

if(!buffer.IsNull(inputColumnBufferIndex)){

switch(inp.InputColumnCollection[0].DataType){

case DataType.DT_WSTR:

sourceValue = buffer.GetString(this.inputColumnBufferIndex);

break;

case DataType.DT_I4:

sourceValue = buffer.GetInt32(this.inputColumnBufferIndex).ToString();

break;

default: throw new Exception("Invalid Data Type!");

}

sourceValue = sourceValue.Trim();

try{

object decodedValueObj = this.mappings[sourceValue];

if(decodedValueObj == null){

throw new Exception("No mapping!");

}

string decodedValue = decodedValueObj.ToString();

switch(outp.OutputColumnCollection[0].DataType){

case DataType.DT_WSTR:

buffer.SetString(this.outputColumnBufferIndex, decodedValue);

break;

case DataType.DT_I4:

buffer.SetInt32(this.outputColumnBufferIndex, int.Parse(decodedValue));

break;

default:

throw new IOException("Invalid Data Type!");

}

buffer.DirectRow(outId);

}catch(IOException fake){

throw fake;

}catch(Exception e){

redirectOrFail(inp, errorOut, outId, buffer, e);

}

}else{

redirectOrFail(inp,errorOut,outId,buffer,new Exception("Null values cannot be mapped"));

}

}

}

}

mappings is a hash map filled in PreExecute by accessing a database, outputColumnBufferIndex is calculated like this:

Code Snippet

IDTSInput90 input = ComponentMetaData.InputCollection[0];

IDTSOutput90 output = ComponentMetaData.OutputCollection["DecodeOutput"];

IDTSInputColumn90 inputColumn = input.InputColumnCollection[0];

IDTSOutputColumn90 outputColumn = output.OutputColumnCollection[0];

this.inputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, inputColumn.LineageID);

this.outputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, outputColumn.LineageID);

I've highlighted the parts that interest me, I've been testing the component thoroughly and it is getting the input data and mappings right, but it won't write the Decoded value to the given column and leaves it empty, even though the decodedValue variable is not null.

I'm guessing theres a problem when I call buffer.SetString() but I haven't the slightest idea of what that could be.

Any help would be greatly appreciated!

Have you debugged it? Do inputColumnBufferIndex and outputColumnBufferIndex values make sense?

Do you only have one input and one output column in your component?

Are you getting any errors from SetString?

Thanks,

Bob

|||

Thank you for your reply Bob,

Yes, I did debug it and the values made sense. Thats how I know that the decodedValue was being also properly initialized.

It's working now by using:

Code Snippet

buffer[this.outputColumnBufferIndex] = decodedValue;

Don't ask me why.

I'm having all sorts of problems that have very easy but completely undocumented solutions like having to override PerformUpgrade to update the UserComponentTypeName attribute and the Version, even though I'm not changing either one as the component is still in development and not in use in any "real" packages.

Maybe my installation is corrupted somehow?

|||

Hmm, you should not be required to implement PerformUpgrade unless you are really changing your component.

Could you check your ProvideComponentProperties implementation and if there is something that might be messing up the settings?

Thanks,

Bob

|||

I don't know what to look for, this is my 3rd week with C# and SSIS.

Here's the basic idea:

Code Snippet

public override void ProvideComponentProperties(){

//FIXME

base.ProvideComponentProperties();

(set the name, dispositions, etc...)

base.RemoveAllInputsOutputsAndCustomProperties();

(add synchronous inputs and outputs)

(add custom properties)

}

I suspect it could be I'm calling RemoveAllInpusOutputsAndCustomProperties(); after base.ProvideComponentProperties(), but I don't want to touch it just yet. What are your thoughts?

Thank you very much again.

|||

Yeah, that is it.

You can safely remove that line as ProvideComponentProperties is called only once in the SSIS designer.

Alternatively, you may move that line before base.ProvideComponentProperties().

Thanks,

Bob

|||Thank you very much for your help|||

Adrian, this is not related to your question, but there is a bug in how buffer.EndOfRowset is used in the ProcessInput method in the first code sample, please see my blog for details

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

|||

Thank you very much for that link! I'll be changing that right away.

buffer.SetString not writing values!

Hello all,

I'm writing a custom component similar to the "Derived Column" transformation, in which a value of some input column is "decoded" into an output column.

Here's the code:

Code Snippet

public override void ProcessInput(int inputID, PipelineBuffer buffer){

//TODO

setupTraceListener();

IDTSOutput90 outp = ComponentMetaData.OutputCollection["DecodeOutput"];

int outId = outp.ID;

int errorOut = -1;

IDTSInput90 inp = ComponentMetaData.InputCollection.GetObjectByID(inputID);

if( ComponentMetaData.OutputCollection.Count > 1){errorOut = ComponentMetaData.OutputCollection["DecodeErrors"].ID;

}

string sourceValue;

if(!buffer.EndOfRowset){

while(buffer.NextRow()){

if(!buffer.IsNull(inputColumnBufferIndex)){

switch(inp.InputColumnCollection[0].DataType){

case DataType.DT_WSTR:

sourceValue = buffer.GetString(this.inputColumnBufferIndex);

break;

case DataType.DT_I4:

sourceValue = buffer.GetInt32(this.inputColumnBufferIndex).ToString();

break;

default: throw new Exception("Invalid Data Type!");

}

sourceValue = sourceValue.Trim();

try{

object decodedValueObj = this.mappings[sourceValue];

if(decodedValueObj == null){

throw new Exception("No mapping!");

}

string decodedValue = decodedValueObj.ToString();

switch(outp.OutputColumnCollection[0].DataType){

case DataType.DT_WSTR:

buffer.SetString(this.outputColumnBufferIndex, decodedValue);

break;

case DataType.DT_I4:

buffer.SetInt32(this.outputColumnBufferIndex, int.Parse(decodedValue));

break;

default:

throw new IOException("Invalid Data Type!");

}

buffer.DirectRow(outId);

}catch(IOException fake){

throw fake;

}catch(Exception e){

redirectOrFail(inp, errorOut, outId, buffer, e);

}

}else{

redirectOrFail(inp,errorOut,outId,buffer,new Exception("Null values cannot be mapped"));

}

}

}

}

mappings is a hash map filled in PreExecute by accessing a database, outputColumnBufferIndex is calculated like this:

Code Snippet

IDTSInput90 input = ComponentMetaData.InputCollection[0];

IDTSOutput90 output = ComponentMetaData.OutputCollection["DecodeOutput"];

IDTSInputColumn90 inputColumn = input.InputColumnCollection[0];

IDTSOutputColumn90 outputColumn = output.OutputColumnCollection[0];

this.inputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, inputColumn.LineageID);

this.outputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, outputColumn.LineageID);

I've highlighted the parts that interest me, I've been testing the component thoroughly and it is getting the input data and mappings right, but it won't write the Decoded value to the given column and leaves it empty, even though the decodedValue variable is not null.

I'm guessing theres a problem when I call buffer.SetString() but I haven't the slightest idea of what that could be.

Any help would be greatly appreciated!

Have you debugged it? Do inputColumnBufferIndex and outputColumnBufferIndex values make sense?

Do you only have one input and one output column in your component?

Are you getting any errors from SetString?

Thanks,

Bob

|||

Thank you for your reply Bob,

Yes, I did debug it and the values made sense. Thats how I know that the decodedValue was being also properly initialized.

It's working now by using:

Code Snippet

buffer[this.outputColumnBufferIndex] = decodedValue;

Don't ask me why.

I'm having all sorts of problems that have very easy but completely undocumented solutions like having to override PerformUpgrade to update the UserComponentTypeName attribute and the Version, even though I'm not changing either one as the component is still in development and not in use in any "real" packages.

Maybe my installation is corrupted somehow?

|||

Hmm, you should not be required to implement PerformUpgrade unless you are really changing your component.

Could you check your ProvideComponentProperties implementation and if there is something that might be messing up the settings?

Thanks,

Bob

|||

I don't know what to look for, this is my 3rd week with C# and SSIS.

Here's the basic idea:

Code Snippet

public override void ProvideComponentProperties(){

//FIXME

base.ProvideComponentProperties();

(set the name, dispositions, etc...)

base.RemoveAllInputsOutputsAndCustomProperties();

(add synchronous inputs and outputs)

(add custom properties)

}

I suspect it could be I'm calling RemoveAllInpusOutputsAndCustomProperties(); after base.ProvideComponentProperties(), but I don't want to touch it just yet. What are your thoughts?

Thank you very much again.

|||

Yeah, that is it.

You can safely remove that line as ProvideComponentProperties is called only once in the SSIS designer.

Alternatively, you may move that line before base.ProvideComponentProperties().

Thanks,

Bob

|||Thank you very much for your help|||

Adrian, this is not related to your question, but there is a bug in how buffer.EndOfRowset is used in the ProcessInput method in the first code sample, please see my blog for details

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

|||

Thank you very much for that link! I'll be changing that right away.

buffer.SetString not writing values!

Hello all,

I'm writing a custom component similar to the "Derived Column" transformation, in which a value of some input column is "decoded" into an output column.

Here's the code:

Code Snippet

public override void ProcessInput(int inputID, PipelineBuffer buffer){

//TODO

setupTraceListener();

IDTSOutput90 outp = ComponentMetaData.OutputCollection["DecodeOutput"];

int outId = outp.ID;

int errorOut = -1;

IDTSInput90 inp = ComponentMetaData.InputCollection.GetObjectByID(inputID);

if( ComponentMetaData.OutputCollection.Count > 1){errorOut = ComponentMetaData.OutputCollection["DecodeErrors"].ID;

}

string sourceValue;

if(!buffer.EndOfRowset){

while(buffer.NextRow()){

if(!buffer.IsNull(inputColumnBufferIndex)){

switch(inp.InputColumnCollection[0].DataType){

case DataType.DT_WSTR:

sourceValue = buffer.GetString(this.inputColumnBufferIndex);

break;

case DataType.DT_I4:

sourceValue = buffer.GetInt32(this.inputColumnBufferIndex).ToString();

break;

default: throw new Exception("Invalid Data Type!");

}

sourceValue = sourceValue.Trim();

try{

object decodedValueObj = this.mappings[sourceValue];

if(decodedValueObj == null){

throw new Exception("No mapping!");

}

string decodedValue = decodedValueObj.ToString();

switch(outp.OutputColumnCollection[0].DataType){

case DataType.DT_WSTR:

buffer.SetString(this.outputColumnBufferIndex, decodedValue);

break;

case DataType.DT_I4:

buffer.SetInt32(this.outputColumnBufferIndex, int.Parse(decodedValue));

break;

default:

throw new IOException("Invalid Data Type!");

}

buffer.DirectRow(outId);

}catch(IOException fake){

throw fake;

}catch(Exception e){

redirectOrFail(inp, errorOut, outId, buffer, e);

}

}else{

redirectOrFail(inp,errorOut,outId,buffer,new Exception("Null values cannot be mapped"));

}

}

}

}

mappings is a hash map filled in PreExecute by accessing a database, outputColumnBufferIndex is calculated like this:

Code Snippet

IDTSInput90 input = ComponentMetaData.InputCollection[0];

IDTSOutput90 output = ComponentMetaData.OutputCollection["DecodeOutput"];

IDTSInputColumn90 inputColumn = input.InputColumnCollection[0];

IDTSOutputColumn90 outputColumn = output.OutputColumnCollection[0];

this.inputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, inputColumn.LineageID);

this.outputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, outputColumn.LineageID);

I've highlighted the parts that interest me, I've been testing the component thoroughly and it is getting the input data and mappings right, but it won't write the Decoded value to the given column and leaves it empty, even though the decodedValue variable is not null.

I'm guessing theres a problem when I call buffer.SetString() but I haven't the slightest idea of what that could be.

Any help would be greatly appreciated!

Have you debugged it? Do inputColumnBufferIndex and outputColumnBufferIndex values make sense?

Do you only have one input and one output column in your component?

Are you getting any errors from SetString?

Thanks,

Bob

|||

Thank you for your reply Bob,

Yes, I did debug it and the values made sense. Thats how I know that the decodedValue was being also properly initialized.

It's working now by using:

Code Snippet

buffer[this.outputColumnBufferIndex] = decodedValue;

Don't ask me why.

I'm having all sorts of problems that have very easy but completely undocumented solutions like having to override PerformUpgrade to update the UserComponentTypeName attribute and the Version, even though I'm not changing either one as the component is still in development and not in use in any "real" packages.

Maybe my installation is corrupted somehow?

|||

Hmm, you should not be required to implement PerformUpgrade unless you are really changing your component.

Could you check your ProvideComponentProperties implementation and if there is something that might be messing up the settings?

Thanks,

Bob

|||

I don't know what to look for, this is my 3rd week with C# and SSIS.

Here's the basic idea:

Code Snippet

public override void ProvideComponentProperties(){

//FIXME

base.ProvideComponentProperties();

(set the name, dispositions, etc...)

base.RemoveAllInputsOutputsAndCustomProperties();

(add synchronous inputs and outputs)

(add custom properties)

}

I suspect it could be I'm calling RemoveAllInpusOutputsAndCustomProperties(); after base.ProvideComponentProperties(), but I don't want to touch it just yet. What are your thoughts?

Thank you very much again.

|||

Yeah, that is it.

You can safely remove that line as ProvideComponentProperties is called only once in the SSIS designer.

Alternatively, you may move that line before base.ProvideComponentProperties().

Thanks,

Bob

|||Thank you very much for your help|||

Adrian, this is not related to your question, but there is a bug in how buffer.EndOfRowset is used in the ProcessInput method in the first code sample, please see my blog for details

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

|||

Thank you very much for that link! I'll be changing that right away.

buffer.SetString not writing values!

Hello all,

I'm writing a custom component similar to the "Derived Column" transformation, in which a value of some input column is "decoded" into an output column.

Here's the code:

Code Snippet

public override void ProcessInput(int inputID, PipelineBuffer buffer){

//TODO

setupTraceListener();

IDTSOutput90 outp = ComponentMetaData.OutputCollection["DecodeOutput"];

int outId = outp.ID;

int errorOut = -1;

IDTSInput90 inp = ComponentMetaData.InputCollection.GetObjectByID(inputID);

if( ComponentMetaData.OutputCollection.Count > 1){errorOut = ComponentMetaData.OutputCollection["DecodeErrors"].ID;

}

string sourceValue;

if(!buffer.EndOfRowset){

while(buffer.NextRow()){

if(!buffer.IsNull(inputColumnBufferIndex)){

switch(inp.InputColumnCollection[0].DataType){

case DataType.DT_WSTR:

sourceValue = buffer.GetString(this.inputColumnBufferIndex);

break;

case DataType.DT_I4:

sourceValue = buffer.GetInt32(this.inputColumnBufferIndex).ToString();

break;

default: throw new Exception("Invalid Data Type!");

}

sourceValue = sourceValue.Trim();

try{

object decodedValueObj = this.mappings[sourceValue];

if(decodedValueObj == null){

throw new Exception("No mapping!");

}

string decodedValue = decodedValueObj.ToString();

switch(outp.OutputColumnCollection[0].DataType){

case DataType.DT_WSTR:

buffer.SetString(this.outputColumnBufferIndex, decodedValue);

break;

case DataType.DT_I4:

buffer.SetInt32(this.outputColumnBufferIndex, int.Parse(decodedValue));

break;

default:

throw new IOException("Invalid Data Type!");

}

buffer.DirectRow(outId);

}catch(IOException fake){

throw fake;

}catch(Exception e){

redirectOrFail(inp, errorOut, outId, buffer, e);

}

}else{

redirectOrFail(inp,errorOut,outId,buffer,new Exception("Null values cannot be mapped"));

}

}

}

}

mappings is a hash map filled in PreExecute by accessing a database, outputColumnBufferIndex is calculated like this:

Code Snippet

IDTSInput90 input = ComponentMetaData.InputCollection[0];

IDTSOutput90 output = ComponentMetaData.OutputCollection["DecodeOutput"];

IDTSInputColumn90 inputColumn = input.InputColumnCollection[0];

IDTSOutputColumn90 outputColumn = output.OutputColumnCollection[0];

this.inputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, inputColumn.LineageID);

this.outputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, outputColumn.LineageID);

I've highlighted the parts that interest me, I've been testing the component thoroughly and it is getting the input data and mappings right, but it won't write the Decoded value to the given column and leaves it empty, even though the decodedValue variable is not null.

I'm guessing theres a problem when I call buffer.SetString() but I haven't the slightest idea of what that could be.

Any help would be greatly appreciated!

Have you debugged it? Do inputColumnBufferIndex and outputColumnBufferIndex values make sense?

Do you only have one input and one output column in your component?

Are you getting any errors from SetString?

Thanks,

Bob

|||

Thank you for your reply Bob,

Yes, I did debug it and the values made sense. Thats how I know that the decodedValue was being also properly initialized.

It's working now by using:

Code Snippet

buffer[this.outputColumnBufferIndex] = decodedValue;

Don't ask me why.

I'm having all sorts of problems that have very easy but completely undocumented solutions like having to override PerformUpgrade to update the UserComponentTypeName attribute and the Version, even though I'm not changing either one as the component is still in development and not in use in any "real" packages.

Maybe my installation is corrupted somehow?

|||

Hmm, you should not be required to implement PerformUpgrade unless you are really changing your component.

Could you check your ProvideComponentProperties implementation and if there is something that might be messing up the settings?

Thanks,

Bob

|||

I don't know what to look for, this is my 3rd week with C# and SSIS.

Here's the basic idea:

Code Snippet

public override void ProvideComponentProperties(){

//FIXME

base.ProvideComponentProperties();

(set the name, dispositions, etc...)

base.RemoveAllInputsOutputsAndCustomProperties();

(add synchronous inputs and outputs)

(add custom properties)

}

I suspect it could be I'm calling RemoveAllInpusOutputsAndCustomProperties(); after base.ProvideComponentProperties(), but I don't want to touch it just yet. What are your thoughts?

Thank you very much again.

|||

Yeah, that is it.

You can safely remove that line as ProvideComponentProperties is called only once in the SSIS designer.

Alternatively, you may move that line before base.ProvideComponentProperties().

Thanks,

Bob

|||Thank you very much for your help|||

Adrian, this is not related to your question, but there is a bug in how buffer.EndOfRowset is used in the ProcessInput method in the first code sample, please see my blog for details

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

|||

Thank you very much for that link! I'll be changing that right away.

buckets or quartile

Hi all,

Dreaded moment has come:)

I need to produce a report against MSAS 2005 with quartiles buckets, meaning that besides actual value of the measure, it needs to display following:

Value 1st 2nd 3rd 4th
X Min - Max Min - Max Min - Max Min - Max
-
80 85 - 95 75 - 85 55 - 75 12 - 55

where 1st is range of values of top 25% performing entities and so forth. It's supposed to be sliceable by any hierarchy in the cube.

I tried to use TopPercent 25%, but never could get it work in calculated member.

Any idea, comment, reference highly appreciated.
Thanks

You might consider using the Excel.Quartile function that comes with the ExcelMDX library in SSAS.|||

An alternative is to create a "band dimension" and set the bandID in the fact table prior to loading. This will give you greater control if you are planning to use this type of banding in many queries.

eg. Band dimension structure

BandId StartBand EndBand Description

1 -99999 11 Less than 12
2 12 55 12 to 55
...
n 85 95 85 to 95
n+1 96 9999999 greater than 95

|||

Can you use the Descritization properties on the attribute? Set it for 4 buckets, and change the label properties to have it display what you need.

-Kory

|||

Let's clarify the problem a bit.

Suppose Value is Sales, which aggregates as Sum. Suppose for tuple (Geography.USA.Redmond, Time.Year.2005) the value of Sales is 80. How the value of 1st would be calculated? It is stated as "top 25% performing entities". What is "entity" and how the "performance" is calculated?

Suppose Geography dimension has the key attribute - Employee, which is the granularity attribute. Are employees entities?

Suppose in 2005 the employees of Redmond showed sales as: Emp1=10, Emp2=20, Emp3=30, Emp4=20. Total is 80.

How do we calculate 1st value?

|||

Extendong your example with employees in particular geography top 25% performing employees (Emp1=10, Emp2=20, Emp3=30, Emp4=26, Emp5=15, Emp6 = 22, Emp7=35, Emp8=14) will be Emp3 & Emp7. The report will look like this:

Redmond 30 37

22 26

15 20

10 14

Employees are entities and performance is highest value of the sale in particular geography.

Thank you.

|||

is it some kind of Add-on for AS? How it can be installed and used?

Thank you

|||

It is kind of strange that with your last example the numbers for the employees you provided did not sum to 80, like you showed with your very first example. Along with the 1stValue - 85-95 being more than Value - 80 in the first example. I am thinking about the additional rules not yet explained.

Let's move up in the Geography hierarchy a bit. Consider tuple (Geography.USA, Time.Year.2005) (let's not have states, since in my first example i showed Geography.USA.Redmond). Will you need calculation of those 4 values for this tuple also? Will it be something like scanning aggregated sales in cities, bucketizing them and reporting?

What i am trying to get clarified is - do you need just one static report (is "report" an MDX query or report for MS Reporting Services?), where you have predefined 2 levels of the same hierarchy, one measure and for each member of the first hierarchy we calculate buckets from the values of the measure when the member is drilled down to the second level?

|||

the measure is not additive.

The report will calculate quartiles for the cities only.

For some reason, TopPercent did not work properly, so I could not create calculated members or named set. So far I have calculated quartile values for each city and store them in a cube as member properties.

We are using ReportPortal on SPS, because there are many other hierarchies, which users might want filter or multi-select, so dynamic reporting is preferred.

I would like to thank you all for help and suggestions. Strange, but TopPercent still does not work. May be MSFT folks can shed some light on its usage.

Thank you very much again

|||You can use it in SSAS if you have Excel installed on the server.

bound table

I am wondering if anyone knows a solution to this question.

I have an sp that may return two different value fields depending on the parameters that are passed in. One way, the sp can return 5 values and another way it can return 10 values. I need to bind the returned values to a table. However, I only want to view the values that are intended to be shown. For example, if the input is SSN and show all is false then the 5 values that need to be outputted would be first name, last name, address, zip code, and contact number. However, if the input is SSN and the show all is true, then the 10 values would be the name plus, other info........

How can I go about outputting all of this info? should I use one bound table?

Or should I have two tables one on top of another and then differentiate on what to show based on what parameters have been inputted? how would i do that and is it possible?

Column headers should reflect all the data that is displayed. Therefore, i was thinking making two tables with hard coded column headers.

Any help would be grateful!

thanks

yadgor2000

I found the answer to my own question, I hope it might help someone else in future: http://www.codeproject.com/dotnet/DynamicReport.asp?df=100&forumid=205933&exp=0&select=1195355

YADGOR2000

Borderline

Hi,
Does anybody know how i can add a borderline (at the top), only when there
is a value in that cell? (when the cell is empty then no borderline).
Expression in the borderstyle-property?
Gr.
MikeExactly. You can use expressions to drive the properties.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Mike Klaarhamer" <MikeKlaarhamer@.discussions.microsoft.com> wrote in
message news:70EEF793-A452-44C7-BA74-A62067EE979C@.microsoft.com...
> Hi,
> Does anybody know how i can add a borderline (at the top), only when there
> is a value in that cell? (when the cell is empty then no borderline).
> Expression in the borderstyle-property?
> Gr.
> Mike