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] + "
|||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() + "'"
