Extract hh AM/PM from getdate()

Hi,
I am looking for a query to extract hour and AM or PM value from a date on sql2000.

ex/-
Input : 2001-12-28 22:18:07.810 (from getdate())
Output : 10 PM

select convert(varchar, (datepart(hh, convert(varchar, getdate(), 8)) % 12)) + ' ' +
substring (convert(varchar, convert(datetime, getdate(),20), 100),
DATALENGTH(convert(varchar, convert(datetime, getdate(),20), 100)) - 1,
DATALENGTH(convert(varchar, convert(datetime, getdate(),20), 100 )))

The above works but is there a better way to do this?This is a little shorter:

SELECT CONVERT(VARCHAR,DATEPART(hh,GETDATE())%12) +
CASE WHEN (DATEPART(hh,GETDATE())%12) > 0 THEN ' PM' ELSE ' AM' END|||thanks for your reply.
but i figured that 12 AM or 12 PM was displayed as 0 AM and 0 PM.
Hence to reduce my troubles, i will stick with the good ol' substring.

SELECT (substring(CONVERT(VARCHAR,getdate(),22),10,2) + ' ' +
substring(CONVERT(VARCHAR,getdate(),22), 19,2))

extract distinct information and order the results

Hi,

MSSQL 2000 T-SQL

I have a problem in extracting information pertaing to a key value and matching that key value to another transaction but the order is based on another value in the same row.

I've attached a sample of DB data below.

tran_nr ret_ref_no msg_type description

5111 12345 420 reversal
5112 12345 200 auths
5113 15236 200 auths
5114 46587 200 auths
5115 46587 420 reversal

Requirement using the above data is to extract data where the ret_ref_no is the same for more than one row but also check that the msg_type 420 happens before the 200. Is there a way of retrieving the information in this way using the tran_nr coloumn values? The tran_nr values is basically the serial number when the transaction is wrriten away to the DB.

I've managed only to retrive the 1st half of my query whereby the same ret_ref_nr is being used by more then one transaction. Still need to figure out the 2nd part where the msg_type of 420 happens before the 200.

SELECT * FROM SAMPLE

WHERE ret_ref_no in
(
SELECT ret_ref_no FROM SAMPLE
GROUP BY ret_ref_no HAVING COUNT(*) > 1
)

Results of query

5111 12345 420 reversal
5112 12345 200 auths
5114 46587 200 auths
5115 46587 420 reversal

If someone could assist with only retreiving the above results in bold to the query analyser i will really appreciate it.

Regards

DeceptiveThe requirement "msg_type 420 happens before the 200" can be interpreted in more than one way. Did you mean that both 420 and 200 must exist and 420 must have a lower tran_nr. Or did you mean that if 200 exists for ret_ref_no , then it must be rpecede by 420?|||

Hi AKuz,

Your first thought was correct. Thats exactly what i require.

The requirement "msg_type 420 happens before the 200" can be interpreted in more than one way. Did you mean that

both 420 and 200 must exist and 420 must have a lower tran_nr.

Deceptive

|||

The possible query,

Assumption : The tran_nr are inserted in sequence. Suggestion, instead of using the id use the timestamp.

Code Snippet

Create Table #data (

[tran_nr] int ,

[ret_ref_no] int ,

[msg_type] int ,

[description] Varchar(100)

);

Insert Into #data Values('5111','12345','420','reversal');

Insert Into #data Values('5112','12345','200','auths');

Insert Into #data Values('5113','15236','200','auths');

Insert Into #data Values('5114','46587','200','auths');

Insert Into #data Values('5115','46587','420','reversal');

select * from #data main

where exists

(

select l.ret_ref_no from #data l

inner join #data r

on l.ret_ref_no = r.ret_ref_no

and l.tran_nr < r.tran_nr

where

l.msg_type='420'

and r.msg_type = '200'

and main.ret_ref_no = l.ret_ref_no

)

|||

Here are two more possible solutions. Thanks to Manivannan for the DDL and sample data.

Code Snippet

select

*

from

#data as a

where

exists (

select

*

from

#data as b

where

b.[ret_ref_no] = a.[ret_ref_no]

and b.[tran_nr] < a.[tran_nr]

and b.[msg_type] = 420 and a.[msg_type] = 200

)

or exists (

select

*

from

#data as b

where

b.[ret_ref_no] = a.[ret_ref_no]

and b.[tran_nr] > a.[tran_nr]

and b.[msg_type] = 200 and a.[msg_type] = 420

)

select

*

from

#data

where

[ret_ref_no] in (

select

a.[ret_ref_no]

from

#data as a

where

a.[msg_type] in (200, 420)

group by

a.[ret_ref_no]

having

min(case when a.[msg_type] = 200 then a.[tran_nr] end) > min(case when a.[msg_type] = 420 then a.[tran_nr] end)

)

AMB

|||Thanks Manivannan.D.Sekaran for your assistance.|||Thank you too hunchback |||

hi Manivannan

Looks like i have another issue that i need help with. I've i add a coloumn rsp_code to the database. This coloumn will contain two degit response codes consisting of values like 00, 25,91,01,05 etc. Each transaction will have its own rsp_code value.

Question:-

Together with the SQL already provided is it posible to only retrive the records where the rsp_code = '25' for the 420 and the rsp_code = '00' for the 200 transactions?

Thanks

Deceptive

Extract data from ACCESS database with pwd via ODBC

Hi,

My task is simple, I want to use the execute sql task editor to grab a value from a database in Access and put it in a variable. The connection is via ODBC and the access database is protected by a password.

I've done all the preliminary stuff such as running profiler to make sure that the package is getting the call to the database, setting up the ResultSet to be "single row" in the general tab, mapped the Result Set correctly, but nothing works. I get the same error every time.

This is my sql command:

selectcount(FingerPrintID) as FingerPrint

from Employee

Result Set is set up like this:

Result Name: FingerPrint ; Variable Name: User:: varDataset

Here is the error I get:

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "varDataset": "Value does not fall within the expected range.".

My variable is set up as a Int16.

Please help!!!!!

If you could provide step by step example's that would really make my day.

Thanks,

Lawrence

What connection type are you using? ADO.NET?|||

Have you tried a 32-bit integer? That would explain the error message.

-Doug

|||

Hi,

I create a connection via odbc, using Microsoft Access Driver.

And i'm using this connection to access the data in Access DataBase.

|||

Hi,

I already try all kind of variable types that exist in SSIS, but all them retrieve the same error.

Any more ideia? I don't knew what's to do.

Thanx very much

|||

Have you properly mapped the column value that you want out of the resultset by naming the resultset FingerPrint or 0 (zero), as described in the "Populating a Variable with a Result Set" section of the Execute SQL Task topic? http://msdn2.microsoft.com/en-us/library/ms141003.aspx

-Doug

|||

Hi,

First i created an odbc, in ODBC Data Source.

Then i have been trying to connect via odbc, using Execute SqlTask. But it doesn't work

Can you give me some help on this?

Thanx

|||

Hi,

Yes, i already did this. But also didn' work.

Any more ideias?

Thanx

Extract data from ACCESS database with pwd via ODBC

Hi,

My task is simple, I want to use the execute sql task editor to grab a value from a database in Access and put it in a variable. The connection is via ODBC and the access database is protected by a password.

I've done all the preliminary stuff such as running profiler to make sure that the package is getting the call to the database, setting up the ResultSet to be "single row" in the general tab, mapped the Result Set correctly, but nothing works. I get the same error every time.

This is my sql command:

selectcount(FingerPrintID) as FingerPrint

from Employee

Result Set is set up like this:

Result Name: FingerPrint ; Variable Name: User:: varDataset

Here is the error I get:

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "varDataset": "Value does not fall within the expected range.".

My variable is set up as a Int16.

Please help!!!!!

If you could provide step by step example's that would really make my day.

Thanks,

Lawrence

What connection type are you using? ADO.NET?|||

Have you tried a 32-bit integer? That would explain the error message.

-Doug

|||

Hi,

I create a connection via odbc, using Microsoft Access Driver.

And i'm using this connection to access the data in Access DataBase.

|||

Hi,

I already try all kind of variable types that exist in SSIS, but all them retrieve the same error.

Any more ideia? I don't knew what's to do.

Thanx very much

|||

Have you properly mapped the column value that you want out of the resultset by naming the resultset FingerPrint or 0 (zero), as described in the "Populating a Variable with a Result Set" section of the Execute SQL Task topic? http://msdn2.microsoft.com/en-us/library/ms141003.aspx

-Doug

|||

Hi,

First i created an odbc, in ODBC Data Source.

Then i have been trying to connect via odbc, using Execute SqlTask. But it doesn't work

Can you give me some help on this?

Thanx

|||

Hi,

Yes, i already did this. But also didn' work.

Any more ideias?

Thanx

Extra Trailing Whitespace Doesn't Violate Foreign Key Constrai

To check whether it was off, try this run this and see if the value is 1. If
so, then the trailing blank spaces are lost
select ColumnProperty(object_id('ProductTable')
, 'Product One',
'UsesAnsiTrim')On Tue, 30 May 2006 07:43:03 -0700, Omnibuzz wrote:

> To check whether it was off, try this run this and see if the value is 1.
If
> so, then the trailing blank spaces are lost
> select ColumnProperty(object_id('ProductTable')
, 'Product One',
> 'UsesAnsiTrim')
Yes, the value of this is 1, so ANSI_PADDING is on. As far as I can tell,
ANSI_PADDING is ON per the default and I have never done anything to
change this.
Thanks,
Josh|||It would have been set at the time of the table creation.
Its set at runtime.
"Josh T" wrote:

> On Tue, 30 May 2006 07:43:03 -0700, Omnibuzz wrote:
>
>
> Yes, the value of this is 1, so ANSI_PADDING is on. As far as I can tell,
> ANSI_PADDING is ON per the default and I have never done anything to
> change this.
> Thanks,
> Josh
>

extra space at bottom

Hi;
i want to add extra separation between lines when a field has certain
value... right now, i tried bottom padding and Lineheight but none seems to
work...
this is the expression i had on my table detail's line bottom pading :
=iif(fields!UTIL.value="T",10,2)
Any advice?found the problem...
i has to be: =iif(fields!UTIL.value="T","10pt","2pt")
"Willo" <willoberto@.yahoo.com.mx> wrote in message
news:%235Wc$9FgHHA.668@.TK2MSFTNGP05.phx.gbl...
> Hi;
> i want to add extra separation between lines when a field has certain
> value... right now, i tried bottom padding and Lineheight but none seems
> to work...
> this is the expression i had on my table detail's line bottom pading :
> =iif(fields!UTIL.value="T",10,2)
> Any advice?
>
>

external image not rendering when generated from aspx page

I'm having problems displaying images generated by aspx pages from a report. It works fine if I point the external value to a static image on the web. I can paste the url to the page into IE and it generates the image with no issues. What am I doing wrong? I used a textbox to verify that the correct url is being generated (= Parameters!MYPROJECT_URL.Value & Parameters!MYPROJECT_NO.Value).

Are there any issues with generating external images with reporting services?

I have installed SP1 for MS 2005...shouldn't I see some sort of visual confirmation in Management studio when I click the Help Menu->about? saying the version is MS SQL Server 2005? how do I verify that the upgrade was successful?

You can check if the upgrade is successful from the error logs, they contain the build number on the top.

|||Check if ASPX pase generate the image have anonymouse accesssql

External Image not displaying

Hi,
I installed SP1 and have specified an image as External & am using
"https"//abc.com/Images"+Fields!LOGO.Value as the exprseeion for getting the
image but the iamge is not displaying. The image in the database field LOGO
is stored simply as xyz.gif . How acn i display such an image? Am i missing
something here?
--
pmudDid you read the following section for the SP1 readme about external images:
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images
If the UnattendedExecutionAccount is not configured, the report server will
try to access the image with no credentials (anonymous). Also, did you check
if the expression really generates a valid image path? If you run the report
in report designer preview (with SP1 installed), you should see a processing
warning message with details if the web server responds e.g. with a 401
error on the image request.
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:ACFAA192-6175-4AC4-8CA5-26641626CAFB@.microsoft.com...
> Hi,
> I installed SP1 and have specified an image as External & am using
> "https"//abc.com/Images"+Fields!LOGO.Value as the exprseeion for getting
> the
> image but the iamge is not displaying. The image in the database field
> LOGO
> is stored simply as xyz.gif . How acn i display such an image? Am i
> missing
> something here?
> --
> pmud|||Hi Robert,
I validated that the image can be accessed through IE. Also, my report uses
a shared data source which uses credentials. So i dont think there will be a
need to configure an account for unattended execution. what do you think?
Also,when i use the expression ="https://abc.com/Images/"&FieldsLOGO.Value,
I cant see the image when the report is previewed. actually whn i create the
expression & try to insert &Fields!LOGO.VAlue , when i expand the Fields
section, it says report item not linked to dataset. So I tried to use the
expression ="https://triotekdealer.com/Images/"&(Fields!LOGO_IMAGEPATH.Value,
"DataSet1")
But i got the error : The value expression for the image â'image1â' contains
an error: [BC30198] ')' expected.
How can I link the dataset to the image so that it can use the fields in the
dataset, if thats's what the problem is?
--
pmud
"Robert Bruckner [MSFT]" wrote:
> Did you read the following section for the SP1 readme about external images:
> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images
> If the UnattendedExecutionAccount is not configured, the report server will
> try to access the image with no credentials (anonymous). Also, did you check
> if the expression really generates a valid image path? If you run the report
> in report designer preview (with SP1 installed), you should see a processing
> warning message with details if the web server responds e.g. with a 401
> error on the image request.
>
> --
> Robert M. Bruckner
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:ACFAA192-6175-4AC4-8CA5-26641626CAFB@.microsoft.com...
> > Hi,
> >
> > I installed SP1 and have specified an image as External & am using
> > "https"//abc.com/Images"+Fields!LOGO.Value as the exprseeion for getting
> > the
> > image but the iamge is not displaying. The image in the database field
> > LOGO
> > is stored simply as xyz.gif . How acn i display such an image? Am i
> > missing
> > something here?
> > --
> > pmud
>
>|||You could move the image inside a data region (e.g. a list). Then you will
get one image per row in the dataset. In that case the expression has to
look like:
="https://triotekdealer.com/Images/" & Fields!LOGO_IMAGEPATH.Value
Otherwise, you have to use an aggregate function (because the dataset has
multiple rows):
="https://triotekdealer.com/Images/" & First(Fields!LOGO_IMAGEPATH.Value,
"DataSet1")
This posting is provided "AS IS" with no warranties, and confers no rights.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:83471FE2-3457-4C66-85A0-8F9CE798901C@.microsoft.com...
> Hi Robert,
> I validated that the image can be accessed through IE. Also, my report
> uses
> a shared data source which uses credentials. So i dont think there will be
> a
> need to configure an account for unattended execution. what do you think?
> Also,when i use the expression
> ="https://abc.com/Images/"&FieldsLOGO.Value,
> I cant see the image when the report is previewed. actually whn i create
> the
> expression & try to insert &Fields!LOGO.VAlue , when i expand the Fields
> section, it says report item not linked to dataset. So I tried to use the
> expression
> ="https://triotekdealer.com/Images/"&(Fields!LOGO_IMAGEPATH.Value,
> "DataSet1")
> But i got the error : The value expression for the image 'image1' contains
> an error: [BC30198] ')' expected.
> How can I link the dataset to the image so that it can use the fields in
> the
> dataset, if thats's what the problem is?
> --
> pmud
>
> "Robert Bruckner [MSFT]" wrote:
>> Did you read the following section for the SP1 readme about external
>> images:
>> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images
>> If the UnattendedExecutionAccount is not configured, the report server
>> will
>> try to access the image with no credentials (anonymous). Also, did you
>> check
>> if the expression really generates a valid image path? If you run the
>> report
>> in report designer preview (with SP1 installed), you should see a
>> processing
>> warning message with details if the web server responds e.g. with a 401
>> error on the image request.
>>
>> --
>> Robert M. Bruckner
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "pmud" <pmud@.discussions.microsoft.com> wrote in message
>> news:ACFAA192-6175-4AC4-8CA5-26641626CAFB@.microsoft.com...
>> > Hi,
>> >
>> > I installed SP1 and have specified an image as External & am using
>> > "https"//abc.com/Images"+Fields!LOGO.Value as the exprseeion for
>> > getting
>> > the
>> > image but the iamge is not displaying. The image in the database field
>> > LOGO
>> > is stored simply as xyz.gif . How acn i display such an image? Am i
>> > missing
>> > something here?
>> > --
>> > pmud
>>

Ext. SPs

I need to be able to create my own MSSQ UDF to return the current system
date, offset by a configurable number of minutes, depending on a value in a
table. The reason I am trying to do this is, I want to be able to, for
testing purposes, fake the system into thinking that time has elapsed.
Changing the time on the computer is not an option.

I would like to call the UDF MyGetDate and to replace all code occurrence of
getdate() in the database with this call. This includes column default value
constraints and stored procedures.

The problem is that MSSQL does not allow the function GETDATE with a UDF. I
thought to try an fake it out by having the UDF call a SP, which in turn
called GETDATE. When I did this, I got the error 'Only functions and
extended stored procedures can be executed from within a function.'

I guess I can go down the road to try and learn how to write an extended
stored procedure to return the current time, but I imagine that there is a
learning curve here.

I realize that all COLUMN default CONSTRAINT with GETDATE could be handled
by create ADD AND UPDATE TRIGGERS that populate thisIt's a kludge but you can create a UDF that reads a value from a table.
Then schedule a Sql Agent Job to run every minute, updating that
table's value.|||Chad (chad.dokmanovich@.unisys.com) writes:
> I need to be able to create my own MSSQ UDF to return the current system
> date, offset by a configurable number of minutes, depending on a value
> in a table. The reason I am trying to do this is, I want to be able to,
> for testing purposes, fake the system into thinking that time has
> elapsed. Changing the time on the computer is not an option.
> I would like to call the UDF MyGetDate and to replace all code
> occurrence of getdate() in the database with this call. This includes
> column default value constraints and stored procedures.
> The problem is that MSSQL does not allow the function GETDATE with a
> UDF. I thought to try an fake it out by having the UDF call a SP, which
> in turn called GETDATE. When I did this, I got the error 'Only functions
> and extended stored procedures can be executed from within a function.'
> I guess I can go down the road to try and learn how to write an extended
> stored procedure to return the current time, but I imagine that there is a
> learning curve here.

Using a UDF in all sorts of constraints, could have performance issues,
and if that UDF calls an extended procedure that does not make things
better.

You could save the show with:

CREATE FUNCTION kalle(@.d datetime) RETURNS datetime AS
BEGIN
RETURN dateadd(DAY, 12, @.d)
END
go
select dbo.kalle(getdate())

Yes, that will be somewhat bulkier, but it should get the job one.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Expressions not working if Matrix cell is not populated by dataset

Hi
I'm using the following expression for the BackGroundColor in a matrix
detail cell:
=IIF(Fields!ID_Quarter.Value = "Cumulative", "Gainsboro","Transparent")
This is checking the value of another cell before setting the
BackGroundColor value.
I'm also using an expression for the bottom border style:
=IIF(Fields!ID_Quarter.Value = "Cumulative", "Solid","Dotted")
This is checking the value of the same cell that the BackGroundColor
expression checks.
These expression ONLY work if the cell has been populated from the dataset
(in my matrix some cells don't have a value - this is expected).
My Cell has three expressions all up, the remaining expression checks it's
own value for nothing and then put's a "0" in the cell if true.
=IIF(Fields!Deep_SSI.Value = Nothing,"0",Sum(Fields!Deep_SSI.Value))
This expression always works.
Is there a problem with using more than one expression on a cell' I think
not because they all work fine if the cell is populated.
Any thoughts?
SimonBHello...
RE: Expressions not working if Matrix cell is not populated by dataset!
Has anyone got any clues on this problem?
Simon
"simonb" wrote:
> Hi
> I'm using the following expression for the BackGroundColor in a matrix
> detail cell:
> =IIF(Fields!ID_Quarter.Value = "Cumulative", "Gainsboro","Transparent")
> This is checking the value of another cell before setting the
> BackGroundColor value.
> I'm also using an expression for the bottom border style:
> =IIF(Fields!ID_Quarter.Value = "Cumulative", "Solid","Dotted")
> This is checking the value of the same cell that the BackGroundColor
> expression checks.
> These expression ONLY work if the cell has been populated from the dataset
> (in my matrix some cells don't have a value - this is expected).
> My Cell has three expressions all up, the remaining expression checks it's
> own value for nothing and then put's a "0" in the cell if true.
> =IIF(Fields!Deep_SSI.Value = Nothing,"0",Sum(Fields!Deep_SSI.Value))
> This expression always works.
> Is there a problem with using more than one expression on a cell' I think
> not because they all work fine if the cell is populated.
> Any thoughts?
> SimonB

Expressions not always evaluated in correct order

Hello!

I have an SSIS package, run by the DTExec utility, each night. When I run my package, I assign a value to a variable (package scope) (/SET "\Package.Variables[User:: psRunNo]";0154). (Note: all the variables here are strings).

Value for variable psRunNo = 0154

In the package, I have another package variable called S_SOURCE_FILE_NAME, which is an expression and that uses the variable psRunNo.

Expression for S_SOURCE_FILE_NAME = @.[User:: psRunNo] + "_{TABLENAME}.txt"

So, at the beginning, the value of variable S_SOURCE_FILE_NAME is 0154_{TABLENAME}.txt with that example.

Then, I use the variable S_SOURCE_FILE_NAME in the expression used to assign the ConnectionString property of a Connection (a source file), like this:

REPLACE( @.[User:: S_REJECTED_ROWS_FILE_NAME] ,"{TABLENAME}", "STADDRES")

So here for example, the final value for ConnectionString would be 0154_STADDRES.txt

Everything works nice, most of the time. Sometimes (intermittent problem), the value of the ConnectionString for one of the many Connections I have in the package is not assigned with the right value of psRunNo. The ConnectionString gets the value of psRunNo which is saved into the package (when it was deployed) instead of the value of psRunNo passed with the DTExec.

It is like if the ConnectionString value was computed before the variable psRunNo (and S_SOURCE_FILE_NAME expression) was assigned to the new value, but only for one of the connections (all my connections use the same kind of expression for their ConnectionString property).

Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ?

Thanks!

Isabelle_ wrote:

Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ?

Thanks!

There isn't really a precedence. If VariableC depends on VariableB which depends on VariableA then all will get evaluated when VariableC gets used somewhere. The expressions are not pre-evaluated or anything like that.

This doesn't help explain the problem you are seeing. I confess I can't undersand why it might be happening. Can you post a repro?

-Jamie

|||

I could not post a reproduction as is, because even if I would make up a small package with my case, this case is intermittent, so almost impossible to get it right... I was thinking maybe on making a script at the beginning of my package and "manually" assign the variable values, instead of using expressions.. Normally, the outcome would be the same, but maybe that would ensure that those variables always get their right values before being use... ?

|||

Isabelle_ wrote:

I could not post a reproduction as is, because even if I would make up a small package with my case, this case is intermittent, so almost impossible to get it right... I was thinking maybe on making a script at the beginning of my package and "manually" assign the variable values, instead of using expressions.. Normally, the outcome would be the same, but maybe that would ensure that those variables always get their right values before being use... ?

You could try that but this is just papering over the cracks. Something else is wrong here. Could it be that you haven't got EvaluateAsExpression=TRUE?

-Jamie

|||

I've check, and I got the EvaluateAsExpression=TRUE. I guess also that if I wouldn't have it to true, it would never work. But it works, 90% of the time ...

Ok, thanks anyway. I've changed my thing so I just have one level of expression. I will let it run for few days/weeks and see if this problem still happen.

Thanks everybody for your input!

Expressions not always evaluated in correct order

Hello!

I have an SSIS package, run by the DTExec utility, each night. When I run my package, I assign a value to a variable (package scope) (/SET "\Package.Variables[User:: psRunNo]";0154). (Note: all the variables here are strings).

Value for variable psRunNo = 0154

In the package, I have another package variable called S_SOURCE_FILE_NAME, which is an expression and that uses the variable psRunNo.

Expression for S_SOURCE_FILE_NAME = @.[User:: psRunNo] + "_{TABLENAME}.txt"

So, at the beginning, the value of variable S_SOURCE_FILE_NAME is 0154_{TABLENAME}.txt with that example.

Then, I use the variable S_SOURCE_FILE_NAME in the expression used to assign the ConnectionString property of a Connection (a source file), like this:

REPLACE( @.[User:: S_REJECTED_ROWS_FILE_NAME] ,"{TABLENAME}", "STADDRES")

So here for example, the final value for ConnectionString would be 0154_STADDRES.txt

Everything works nice, most of the time. Sometimes (intermittent problem), the value of the ConnectionString for one of the many Connections I have in the package is not assigned with the right value of psRunNo. The ConnectionString gets the value of psRunNo which is saved into the package (when it was deployed) instead of the value of psRunNo passed with the DTExec.

It is like if the ConnectionString value was computed before the variable psRunNo (and S_SOURCE_FILE_NAME expression) was assigned to the new value, but only for one of the connections (all my connections use the same kind of expression for their ConnectionString property).

Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ?

Thanks!

Isabelle_ wrote:

Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ?

Thanks!

There isn't really a precedence. If VariableC depends on VariableB which depends on VariableA then all will get evaluated when VariableC gets used somewhere. The expressions are not pre-evaluated or anything like that.

This doesn't help explain the problem you are seeing. I confess I can't undersand why it might be happening. Can you post a repro?

-Jamie

|||

I could not post a reproduction as is, because even if I would make up a small package with my case, this case is intermittent, so almost impossible to get it right... I was thinking maybe on making a script at the beginning of my package and "manually" assign the variable values, instead of using expressions.. Normally, the outcome would be the same, but maybe that would ensure that those variables always get their right values before being use... ?

|||

Isabelle_ wrote:

I could not post a reproduction as is, because even if I would make up a small package with my case, this case is intermittent, so almost impossible to get it right... I was thinking maybe on making a script at the beginning of my package and "manually" assign the variable values, instead of using expressions.. Normally, the outcome would be the same, but maybe that would ensure that those variables always get their right values before being use... ?

You could try that but this is just papering over the cracks. Something else is wrong here. Could it be that you haven't got EvaluateAsExpression=TRUE?

-Jamie

|||

I've check, and I got the EvaluateAsExpression=TRUE. I guess also that if I wouldn't have it to true, it would never work. But it works, 90% of the time ...

Ok, thanks anyway. I've changed my thing so I just have one level of expression. I will let it run for few days/weeks and see if this problem still happen.

Thanks everybody for your input!

Expressions not always evaluated in correct order

Hello!

I have an SSIS package, run by the DTExec utility, each night. When I run my package, I assign a value to a variable (package scope) (/SET "\Package.Variables[User:: psRunNo]";0154). (Note: all the variables here are strings).

Value for variable psRunNo = 0154

In the package, I have another package variable called S_SOURCE_FILE_NAME, which is an expression and that uses the variable psRunNo.

Expression for S_SOURCE_FILE_NAME = @.[User:: psRunNo] + "_{TABLENAME}.txt"

So, at the beginning, the value of variable S_SOURCE_FILE_NAME is 0154_{TABLENAME}.txt with that example.

Then, I use the variable S_SOURCE_FILE_NAME in the expression used to assign the ConnectionString property of a Connection (a source file), like this:

REPLACE( @.[User:: S_REJECTED_ROWS_FILE_NAME] ,"{TABLENAME}", "STADDRES")

So here for example, the final value for ConnectionString would be 0154_STADDRES.txt

Everything works nice, most of the time. Sometimes (intermittent problem), the value of the ConnectionString for one of the many Connections I have in the package is not assigned with the right value of psRunNo. The ConnectionString gets the value of psRunNo which is saved into the package (when it was deployed) instead of the value of psRunNo passed with the DTExec.

It is like if the ConnectionString value was computed before the variable psRunNo (and S_SOURCE_FILE_NAME expression) was assigned to the new value, but only for one of the connections (all my connections use the same kind of expression for their ConnectionString property).

Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ?

Thanks!

Isabelle_ wrote:

Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ?

Thanks!

There isn't really a precedence. If VariableC depends on VariableB which depends on VariableA then all will get evaluated when VariableC gets used somewhere. The expressions are not pre-evaluated or anything like that.

This doesn't help explain the problem you are seeing. I confess I can't undersand why it might be happening. Can you post a repro?

-Jamie

|||

I could not post a reproduction as is, because even if I would make up a small package with my case, this case is intermittent, so almost impossible to get it right... I was thinking maybe on making a script at the beginning of my package and "manually" assign the variable values, instead of using expressions.. Normally, the outcome would be the same, but maybe that would ensure that those variables always get their right values before being use... ?

|||

Isabelle_ wrote:

I could not post a reproduction as is, because even if I would make up a small package with my case, this case is intermittent, so almost impossible to get it right... I was thinking maybe on making a script at the beginning of my package and "manually" assign the variable values, instead of using expressions.. Normally, the outcome would be the same, but maybe that would ensure that those variables always get their right values before being use... ?

You could try that but this is just papering over the cracks. Something else is wrong here. Could it be that you haven't got EvaluateAsExpression=TRUE?

-Jamie

|||

I've check, and I got the EvaluateAsExpression=TRUE. I guess also that if I wouldn't have it to true, it would never work. But it works, 90% of the time ...

Ok, thanks anyway. I've changed my thing so I just have one level of expression. I will let it run for few days/weeks and see if this problem still happen.

Thanks everybody for your input!

Expressions in RS

Hi,
Im using the following expression to calculate the percentage
=iif(Fields!BudgetedRevenue.Value = 0,0,(Fields!BudgetedRevenue.Value-
Fields!BudgetedCostStan.Value- Fields!BudgetedCostOT.Value-
Fields!BudgetedCostExp.Value)/ Fields!BudgetedRevenue.Value)
if the Fields!BudgetedRevenue.Value = 0 it is returning the #error,instead
of 0.
Fields!BudgetedRevenue.Value is of datatype money.
Thanks in advance.Try using this:
=iif(isnothing(Fields!BudgetedRevenue.Value),0,(Fields!BudgetedRevenue.Value-
Fields!BudgetedCostStan.Value- Fields!BudgetedCostOT.Value-
Fields!BudgetedCostExp.Value)/ Fields!BudgetedRevenue.Value)
I belive the isnothing() should take the place of the = 0 and might help
your formula calculate correctly.
Let me know if that didn't work.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"CCP" wrote:
> Hi,
> Im using the following expression to calculate the percentage
> =iif(Fields!BudgetedRevenue.Value = 0,0,(Fields!BudgetedRevenue.Value-
> Fields!BudgetedCostStan.Value- Fields!BudgetedCostOT.Value-
> Fields!BudgetedCostExp.Value)/ Fields!BudgetedRevenue.Value)
> if the Fields!BudgetedRevenue.Value = 0 it is returning the #error,instead
> of 0.
> Fields!BudgetedRevenue.Value is of datatype money.
> Thanks in advance.

Expressions as a field value in a table

Is there a way to store an expression inside a table, and have the expression
being pulled from the table to execute during run time? I want to dynamically
create an expression based on the value of a column of the table. Using the
IIF statement will work but it's going to be a very long nested IIF.... and
that brings to the second question -> is there a limit for the length of an
expression?
Thanks in advance.There is no limit on the length of an expression (other than limits imposed
by the VB.NET compiler).
In your case, you may want to look at alternatives to the IIF function:
* =Choose(...)
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctchoose.asp
* =Switch(...)
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctswitch.asp
In addition, you may want to consider writing a function in custom code or
as custom assembly that contains your logic. You can then reuse the function
from RDL expressions.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"g3kamdbs" <g3kamdbs@.discussions.microsoft.com> wrote in message
news:2A1DC075-220C-4494-AF82-157760B3EBD5@.microsoft.com...
> Is there a way to store an expression inside a table, and have the
> expression
> being pulled from the table to execute during run time? I want to
> dynamically
> create an expression based on the value of a column of the table. Using
> the
> IIF statement will work but it's going to be a very long nested IIF....
> and
> that brings to the second question -> is there a limit for the length of
> an
> expression?
> Thanks in advance.|||Or a User Defined Function that the Stored Procedure calls.
GeoSynch
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:uO3ZYBwvFHA.252@.TK2MSFTNGP09.phx.gbl...
> There is no limit on the length of an expression (other than limits imposed by
> the VB.NET compiler).
> In your case, you may want to look at alternatives to the IIF function:
> * =Choose(...)
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctchoose.asp
> * =Switch(...)
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctswitch.asp
> In addition, you may want to consider writing a function in custom code or as
> custom assembly that contains your logic. You can then reuse the function from
> RDL expressions.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "g3kamdbs" <g3kamdbs@.discussions.microsoft.com> wrote in message
> news:2A1DC075-220C-4494-AF82-157760B3EBD5@.microsoft.com...
>> Is there a way to store an expression inside a table, and have the expression
>> being pulled from the table to execute during run time? I want to dynamically
>> create an expression based on the value of a column of the table. Using the
>> IIF statement will work but it's going to be a very long nested IIF.... and
>> that brings to the second question -> is there a limit for the length of an
>> expression?
>> Thanks in advance.
>

expressions

Maybe I just have my eyes closed as I search for how to do this, but can you refer to a textbox value in an expression?

something like;

Value of textbox1

= textbox2.value

Use this syntax:

=ReportItems!TextboxName.Value

However, keep in mind that for instance you cannot refer to a textbox that is contained within a data region (list, table, etc.) from somewhere outside that data region. Additional restrictions apply.

-- Robert

Expression to alter the font size

I'm trying to use an expressions like these to alter the font size...
iif(Len(Fields!MyField.Value) > 12, "7pt","8pt")
iif(Len(Fields!MyField.Value) > 12, 7pt,8pt")
iif(Len(Fields!MyField.Value) > 12, 7,8)
Regardless of which expression I use, I get this error:
"<my expression> cannot be parsed as a unit because it does not contain
numeric values. Examples of valid unit strings are "1pt" and ".5in".
Any suggestions on how i can check the length of a string and alter the
font size in the table?
Thanks,
James
--This worked for me, =iif(Len(Fields!MyField.Value)>12,"7pt","8pt"). Make
sure that you are putting the expression in the FontSize property with the =.
daw
"James" wrote:
> I'm trying to use an expressions like these to alter the font size...
> iif(Len(Fields!MyField.Value) > 12, "7pt","8pt")
> iif(Len(Fields!MyField.Value) > 12, 7pt,8pt")
> iif(Len(Fields!MyField.Value) > 12, 7,8)
> Regardless of which expression I use, I get this error:
> "<my expression> cannot be parsed as a unit because it does not contain
> numeric values. Examples of valid unit strings are "1pt" and ".5in".
> Any suggestions on how i can check the length of a string and alter the
> font size in the table?
> Thanks,
> James
> --
>

expression sum and iif help?

Hi All,

SUM(IIF(Fields!NO.Value >= 1, Fields!tsHours.Value,0))

i write this expression inside the group footer row, it give a #error when generating report.

please help

Cheers

Nick

Hi Nick,

The possible reason for this error is that the Sum function can only add fields of the same data type. It is quite possible in this case that the data types of the fields in the TRUE and FALSe part of the If statement is different. Try making the data types same of both the fields by usinf the Conversion functions available.

Hope this helps.

Regards,

Pradeep

|||

Hi,

I think ure iif() is returning varying datatypes. 0 being a integer and Fields!tsHours.Value returning some other datatype.

Use a conversion function such as CDbl() on both fields.For eg: CDbl(Fields!tsHours.Value) and CDbl(0).This makes either of the values returned as Double.

eg:

Sum(IIf(Fields!NO.value>=1,CDbl(Fields!tsHours.Value),Cdbl(0)))

Somiya

|||

Thanks Chaubey and Somiya,

You guys are right, after i explicitly convert to same type , then it works.

Cheers

Nick

expression problem

Hi There
I am trying to sum the amount value in the budgets dataset based on a sub
catagory within the budgets table called personal loans.
my expression is
=Sum iif(Fields!Sub_Catagory.Value, "budgets" = "Personal Loans",
Fields!Amount.Value, "budgets", Nothing))
this sits within an actuals table that uses a different dataset
could i please have some assistance as to where i am going wrong. error
message is The value expression for the textbox â'total_budgetâ' refers to the
field â'Sub_Catagoryâ'. Report item expressions can only refer to fields
within the current data set scope or, if inside an aggregate, the specified
data set scope.
ThankyouIf I understood you right: You want to sum the Amount (within one dataset)
if the sub_category is "Personal Loans" (within another dataset). try this:
= Sum(iif(Fields!Sub_Category.Value = "Personal Loans", Fields!Amount.Value,
0))
syntax:
iif( expression, what to do if the expression is true, what to do if the
expression is false)
I wasn't able to test the expression, because i have no report server
running at the moment.
"Tango" <Tango@.discussions.microsoft.com> schrieb im Newsbeitrag
news:EE09B408-6D43-4774-BC61-8D5A999EDF4B@.microsoft.com...
> Hi There
> I am trying to sum the amount value in the budgets dataset based on a sub
> catagory within the budgets table called personal loans.
> my expression is
> =Sum iif(Fields!Sub_Catagory.Value, "budgets" = "Personal Loans",
> Fields!Amount.Value, "budgets", Nothing))
> this sits within an actuals table that uses a different dataset
> could i please have some assistance as to where i am going wrong. error
> message is The value expression for the textbox 'total_budget' refers to
> the
> field 'Sub_Catagory'. Report item expressions can only refer to fields
> within the current data set scope or, if inside an aggregate, the
> specified
> data set scope.
> Thankyou
>
>|||The following link discusses conditional expressions:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_3983.asp
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jens Konerow" <keineangabe@.web.de> wrote in message
news:%23RfrfWqwFHA.3556@.TK2MSFTNGP15.phx.gbl...
> If I understood you right: You want to sum the Amount (within one dataset)
> if the sub_category is "Personal Loans" (within another dataset). try
> this:
> = Sum(iif(Fields!Sub_Category.Value = "Personal Loans",
> Fields!Amount.Value, 0))
> syntax:
> iif( expression, what to do if the expression is true, what to do if the
> expression is false)
> I wasn't able to test the expression, because i have no report server
> running at the moment.
> "Tango" <Tango@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:EE09B408-6D43-4774-BC61-8D5A999EDF4B@.microsoft.com...
>> Hi There
>> I am trying to sum the amount value in the budgets dataset based on a sub
>> catagory within the budgets table called personal loans.
>> my expression is
>> =Sum iif(Fields!Sub_Catagory.Value, "budgets" = "Personal Loans",
>> Fields!Amount.Value, "budgets", Nothing))
>> this sits within an actuals table that uses a different dataset
>> could i please have some assistance as to where i am going wrong. error
>> message is The value expression for the textbox 'total_budget' refers to
>> the
>> field 'Sub_Catagory'. Report item expressions can only refer to fields
>> within the current data set scope or, if inside an aggregate, the
>> specified
>> data set scope.
>> Thankyou
>>
>

Expression on hidden property

I have param named Direction i would like to put

on the hidden property of image this expression

iif(Parameters!Direction.Value = "ascending" ,False ,True)

But i've got error message

.....my expression is not avalid value.

why?

Hi, didn't you miss the "=" as in :

=iif(Parameters!Direction.Value = "ascending" ,False ,True)

|||

Maybe?

Can you please write me the correct form you think it should be?

|||

It′s like a said before...

=iif(Parameters!Direction.Value = "ascending" ,False ,True)

if everything's ok that should hide your image when the Parameters!Direction.Value is "ascending" and show it otherwise.

|||Thanks, work grate :)