expression syntax

How would I do a select from a container using the previous container's starttime as a condition in the variable?

select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] >= " + @.[System::ContainerStartTime] + "

Thanks,

Phil

Tackett,

I dont know the requirements of your project, but try to run the SQL statment inside a OLEDB command and define @.[System::ContainerStartTime] as parameter.

For example, you can create your sql statment as stored procedure in database and inside OLEDB Command write in SQL command :

EXEC SP_NAMESTOREDPROCEDURE ?

And in the second tab link the parameter to your system variable.

If you want i can show you an example.

Regards,

Pedro

|||

Isn't there a way to refer to the containerstarttime in a execute sql task via expression syntax? That's what I'm trying to do.

select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] >= ' + (DT_STR,50)@.[System::ContainerStartTime] + '

|||

Sorry Tacket, I was "sleeping"...

Try this post to help your problem...

Tomorrow morning I will think better about this.

Regards,

Pedro

|||

NP. Actually I need to do a BETWEEN where time between 'previous container' and 'current container'. I'm sure that involves system variables and namespaces .

Thanks,

Phil

|||

Check this:

http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

|||

Ok, so I need to create a variable and then evaluate it as an expression and put the code in there, correct? NP, accept, I can't get it to evaluate in the "evaluate as expression" part. Here's what I have so far in the expression builder and it's not working..

"select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] >= " + (DT_STR,50)@.[System::ContainerStartTime] + "

Any help?

Thanks,

Phil

|||

One thing I noticed is that your DT_STR type conversion is missing the thrid parameter (Code Page)

(DT_STR,50) should be (DT_STR,50,1252) -- Assuming you are using the standard 1252 code page.

|||

TITLE: Expression Builder

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

ADDITIONAL INFORMATION:

Attempt to parse the expression ""select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] >= " + (DT_STR,50,1252)@.[System::ContainerStartTime] + "" failed. The token """ at line number "5", character number "68" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.

(Microsoft.DataTransformationServices.Controls)

BUTTONS:

OK

Here is my code in the variable's expression builder:

"select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] >= " + (DT_STR,50,1252)@.[System::ContainerStartTime] + "

|||Well I took off the + " and now it's working |||

Working syntax for those who care:

"select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] between '" + (DT_STR,50,1252)@.[System::ContainerStartTime] + "' and '" + (DT_STR,50,1252)GETDATE() + "'"