how to trim a field value in rss

can any one explain how to trim a value on bothside in rss

for eg:

'asd '

' asd'

i wanna this output 'asd'............

can i give this command

=Format(Fields!company.Value.Trim)

You can use Trim function like this:

Trim(Fields!company.Value)

This will trim both leading and trailing spaces.

Pls mark this post as answer if your problem is solved.

Shyam

how to sum the HOURS in a group

Hi,

How to sum up the total of all the HOURS except 'ZZ' which should be summed up seperately.
hope u got me.
Example: (output should be as below).
nadeem 5
shyub 5
ayub 5
ZZirshad 5

Reported Production Hours: 15 (nadeem + shyub + ayub)
Reported NonProduction Hours: 5 (ZZirshad)

This report which i have asked now already exists..now i have to modify the report with the changes which i have explained to you above. In the existing report, the record selection formula that exists is,
{HR01.HR01015} like {?Department} and
{EPS_Timesheet_Validation.FNTransactDate} = {?DateRange} and
Switch ({?Non_Productive_Time}='Exclude Non Productive Time',Not ({EPS_Timesheet_Validation.FNProjectNo} like 'ZZ*'),
{?Non_Productive_Time}='Include Non Productive Time',{EPS_Timesheet_Validation.FNProjectNo} like '*',
{?Non_Productive_Time}='Only Non Productive Time',{EPS_Timesheet_Validation.FNProjectNo} like 'ZZ*')

Please help me.
Thanks.,edit the select, and remove the

Switch ({?Non_Productive_Time}='Exclude Non Productive Time',Not ({EPS_Timesheet_Validation.FNProjectNo} like 'ZZ*'),
{?Non_Productive_Time}='Include Non Productive Time',{EPS_Timesheet_Validation.FNProjectNo} like '*',
{?Non_Productive_Time}='Only Non Productive Time',{EPS_Timesheet_Validation.FNProjectNo} like 'ZZ*')

this should return all records, you can then group off the param
{?Non_Productive_Time} and sum it up that way, or use a running total

how to store output of exec stmt in another variable?

Hi

I am trying to store the output/result of exec statement in another variable like @.b.

Code Snippet

declare @.a varchar(10)

declare @.b int

set @.a='2 * 3';

EXEC ('select ' + @.a)

in the above sample I need to store 6 in @.b, how?

Please advice

Thanks

You could use sp_executesql like:

declare @.res int /*?*/;

declare @.sql nvarchar(max);

set @.sql = N'SELECT @.res = ' + @.a; -- Protect against SQL injection by using QUOTENAME as appropriate etc...

exec sp_executesql @.sql, N'@.res int OUTPUT', @.res = @.res OUTPUT;

But why do you want to do this? What are you trying to achieve? In SQL Server 2005, you could store the formula in a table and evaluate using CLR logic. This will actually perform better for complex calculations and will not suffer from SQL Injection issues. Otherwise, you should try to avoid dynamic SQL as far as possible. It is easy to code but hard to get it right in terms of security, performance and manageability. The risks are too much if you miss anything.

|||

Thanks a lot Chandar, for your nice reply

I got my answer from your query

The reason why I want this one is

I calculated the product of some values in a Table's column using COALESCE & EXEC Statements. But I am unable to store that result in a variable.

But now I got it using exec sp_executesql etc…..

Once again Thanks

|||You don't need to use dynamic SQL to get the product of values. You can do that with simple expressions using SUM and LOG. Search in this forum for my posts on this topic - I have some links that point to sample code that you can use.

How to store and use xqueries in sql2005?

Hello

I am trying to use the xml.query() method to output xml. Is there any way of storing the xqueries themselves in the database?

This works:

SELECT Col.query('
<Root>
<Header>
{
for $e in Report/PolicyBatchRef
return $e/PolicyBatchRef
}
</Header>
<NewElement>
{
for $e in Report/PolicyBatchRef/Locations/Location
return $e
}
</NewElement>
</Root>
')
FROM xmltest where id = 1

but this doesn't:

declare @.xquery nvarchar(max)
set @.xquery = '<Root>
<Header>
{
for $e in Report/PolicyBatchRef
return $e/PolicyBatchRef
}
</Header>
<NewElement>
{
for $e in Report/PolicyBatchRef/Locations/Location
return $e
}
</NewElement>
</Root>'
SELECT col.query(@.xquery) from xmltest where id = 1

I get the error

Msg 8172, Level 16, State 1, Line 4
The argument 1 of the xml data type method "query" must be a string literal.

Same thing happens when I store the xquery in the DB.

Any ideas?

Thanks very much

The string argument to the query function must be a string literal, so you cannot pass it in as a parameter. You have a few options:

1. Create UDF's that encapsulate the SELECT and the xquery, and invoke these.

2. Store strings that represent the SELECT and the xquery and invoke them at runtime using sp_executesql

3. store the strings that represent the xquery and combine them with the strings for the SELECT statement, and execute with sp_executesql. This has the most potential for SQL injection since you are constructing SQL dynamically with string concatenation. This method should be used only if the other two ways cannot be used.

|||

You can dynamically create XPath queries using sql variables.

I have successfully used something like

declare @.Date varchar(10)

set @.Date = replace(convert(varchar(10), getdate(), 121), '-', '')

WITH XMLNAMESPACES( 'https://www,somewhere.com/Bureau' AS "Bureau")

SELECT

AggDefaultAmount = convert(varchar(50), ResponseXML.query('sum(/BureauResponse/Bureau:ND07/Bureau:ND07/Bureau:Amount[../Bureau:InformationDate<sql:variable("@.Date")])'))

FROM

DB..testxml WITH (NOLOCK)

Adapt adopt and improve.

|||

Hi,

I need to a have a Store procedure that takes xml as input and it stores in my database tables. Using the nodes() method and value() method, I am able to solve this but only issue I have is these methods take arguments only as string literals. So, I have to hard code the xquery in the SP. I expect to read the xquery from a table and fetch it in a variable within the SP and pass it as the parameter to the Value() and nodes() method.

Please advice.

Sample code I have as below

declare @.xmldoc xml

SET @.xmldoc = '<customer><name>John</name><city>New York</city></customer>'

--This select works

SELECT

T.C.value('name[1]',varchar(50))

T.C.value('city[1]',varchar(50))

FROM @.xmldoc.nodes('/customer') AS T(C)

--But this does not work when I try to specify xquery using a variable as below

declare @.xquery_name varchar(100),@.xquery_city varchar(100), @.xquery_cust varchar(100)

SELECT @.xquery_name = 'name[1]', @.xquery_city = 'city[1]', @.xquery_cust = '/customer'

SELECT

T.C.value(@.xquery_name ,varchar(50))

T.C.value('@.xquery_city',varchar(50))

FROM @.xmldoc.nodes(@.xquery_cust) AS T(C)

Please help me out.

How to store and use xqueries in sql2005?

Hello

I am trying to use the xml.query() method to output xml. Is there any way of storing the xqueries themselves in the database?

This works:

SELECT Col.query('
<Root>
<Header>
{
for $e in Report/PolicyBatchRef
return $e/PolicyBatchRef
}
</Header>
<NewElement>
{
for $e in Report/PolicyBatchRef/Locations/Location
return $e
}
</NewElement>
</Root>
')
FROM xmltest where id = 1

but this doesn't:

declare @.xquery nvarchar(max)
set @.xquery = '<Root>
<Header>
{
for $e in Report/PolicyBatchRef
return $e/PolicyBatchRef
}
</Header>
<NewElement>
{
for $e in Report/PolicyBatchRef/Locations/Location
return $e
}
</NewElement>
</Root>'
SELECT col.query(@.xquery) from xmltest where id = 1

I get the error

Msg 8172, Level 16, State 1, Line 4
The argument 1 of the xml data type method "query" must be a string literal.

Same thing happens when I store the xquery in the DB.

Any ideas?

Thanks very much

The string argument to the query function must be a string literal, so you cannot pass it in as a parameter. You have a few options:

1. Create UDF's that encapsulate the SELECT and the xquery, and invoke these.

2. Store strings that represent the SELECT and the xquery and invoke them at runtime using sp_executesql

3. store the strings that represent the xquery and combine them with the strings for the SELECT statement, and execute with sp_executesql. This has the most potential for SQL injection since you are constructing SQL dynamically with string concatenation. This method should be used only if the other two ways cannot be used.

|||

You can dynamically create XPath queries using sql variables.

I have successfully used something like

declare @.Date varchar(10)

set @.Date = replace(convert(varchar(10), getdate(), 121), '-', '')

WITH XMLNAMESPACES( 'https://www,somewhere.com/Bureau' AS "Bureau")

SELECT

AggDefaultAmount = convert(varchar(50), ResponseXML.query('sum(/BureauResponse/Bureau:ND07/Bureau:ND07/Bureau:Amount[../Bureau:InformationDate<sql:variable("@.Date")])'))

FROM

DB..testxml WITH (NOLOCK)

Adapt adopt and improve.

|||

Hi,

I need to a have a Store procedure that takes xml as input and it stores in my database tables. Using the nodes() method and value() method, I am able to solve this but only issue I have is these methods take arguments only as string literals. So, I have to hard code the xquery in the SP. I expect to read the xquery from a table and fetch it in a variable within the SP and pass it as the parameter to the Value() and nodes() method.

Please advice.

Sample code I have as below

declare @.xmldoc xml

SET @.xmldoc = '<customer><name>John</name><city>New York</city></customer>'

--This select works

SELECT

T.C.value('name[1]',varchar(50))

T.C.value('city[1]',varchar(50))

FROM @.xmldoc.nodes('/customer') AS T(C)

--But this does not work when I try to specify xquery using a variable as below

declare @.xquery_name varchar(100),@.xquery_city varchar(100), @.xquery_cust varchar(100)

SELECT @.xquery_name = 'name[1]', @.xquery_city = 'city[1]', @.xquery_cust = '/customer'

SELECT

T.C.value(@.xquery_name ,varchar(50))

T.C.value('@.xquery_city',varchar(50))

FROM @.xmldoc.nodes(@.xquery_cust) AS T(C)

Please help me out.

How to stop a package

Hi,

In one of my packages, I have a script component to do a transformation and I am inserting the output to table. Within that script component, if the data does not match my requirement, I have to stop the job. For that, I am using 'componentmetadata.fireError' to raise an error. The problem is, now even when an error is raised, the job completes by loading the remaining records and then aborts. But I want to abort the job as soon it raises an error. How to do this?

Thanks.

Errors are not the way to do it.

Use OnQueryCancel. Books online should document it.

K

|||

HI, sorry to jump in. But I looked in books online and did not found a way to stop the package in the dataflow using the system cancel variable. Is there a way to raise an event in a script component?

Thank you,
Ccote

|||

Hi,

Anybody found a way to stop the package from script component?

Thanks.

|||

The only way I figured out to achieve this is to add a column on the output of the script component (e.g. bln_Continue, a boolean value). Before the error is triggered with ComponentMetadata.FireError, I assign this column to false. So when SSIS exits the script component, I use a conditional split transform that stop the pipeline if the value of the boolean column is false.

In my case, I use it for a logging purpose, so I will have only a fewlines in the pipeline. But in a case that there would many more lines, I would use a variable and initialize it in the post_execute. Then, using a combination of a derived column and conditional split transforms, I would still be able to stop the pipeline.

There may be a way to do it better but it is OK for me right now.

Ccote

|||Thx Ccote. That is a good option. I will try it.|||

Hi,

I am not sure how can I use the conditional split to stop my package. In my package I am loading data to a table and the source may have duplicate keys. So what I am doing, I am redirecting the error rows from destination to a script component and inside that, I am checking the error code. If it is corresponding to index key violation, I am just ignoring it. Otherwise I am raising an error. But how can I stop my package there. Now what is happening is, after processing an error record, it raises an error and reads the next record. If that also an error record, raises an error and it goes like that till the end of the input. But I want to stop the package as soon as the first error is raised. How can I do that?

Thanks.

|||

HI, for sure it will step to the other row as soon as the error is raised. The only thing that I could do for it is to prevent that other rows are processed. They will be read but not processed.

I added another output to the scriot component and called it ErrorOutput. I set its synchronous ID and exclusion group to their counterpart on the regular output (let's say output0). Then, in the code as soon as an error is trigerred, I set the script variable to true and redirect the row to ErrorOutput:

dim ErrorVar as Boolean = False

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If Error = <your error code> Then
ErrorVar = True
End If

If ErrorVar Then
Row.DirectRowToErrorOutput
Else
Row.DirectRowToOutput0
End If
End Sub

HTH,
Ccote