extract non-unique records from a table

Can anyone pls help me with any SQL syntax / logic of extracting only the
non-unique records from an SQL table ?
Thanks
ShekharIf i understand correctly you're after a query that returns the duplicates.
If so give this a try :-
SELECT Col001, COUNT(*) FROM tablename
GROUP BY Col001
HAVING COUNT(*) > 1
This will return any Col001 that are duplicate
HTH. Ryan
"Shekhar Gupta" <ShekharGupta@.discussions.microsoft.com> wrote in message
news:38DC7E2D-051B-4573-9C9A-5B6164F99E78@.microsoft.com...
> Can anyone pls help me with any SQL syntax / logic of extracting only the
> non-unique records from an SQL table ?
> Thanks
> Shekhar|||gr8, Thanks Ryan, this worked
shekhar
"Ryan" wrote:

> If i understand correctly you're after a query that returns the duplicates
.
> If so give this a try :-
> SELECT Col001, COUNT(*) FROM tablename
> GROUP BY Col001
> HAVING COUNT(*) > 1
> This will return any Col001 that are duplicate
> --
> HTH. Ryan
>
> "Shekhar Gupta" <ShekharGupta@.discussions.microsoft.com> wrote in message
> news:38DC7E2D-051B-4573-9C9A-5B6164F99E78@.microsoft.com...
>
>

Expression Syntax Help

Does anyone know of a good reference for Expression Syntax? I have
years of experience in SQL using all kinds of tools, but this thing
has got me really flustered. No If..Else, just endless nested Iif's
in Expressions? No Case..When? No Count Distinct? Can I fake a
Running Total?
This thing is killing me!
JodyPlease refer to the following resources for more information:
Expressions:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_6fhv.asp
Functions:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
In particular SQL Server 2000 Reporting Services does have a CountDistinct
and RunningValue functions.
SQL Server 2000 Reporting Services uses VB .NET expression syntax which
gives access to a large number of functions that can be used in reports.
Switch syntax is support in places of nested ifs.
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JodyT" <datagal@.msn.com> wrote in message
news:f9d864c3.0408120855.1266d521@.posting.google.com...
> Does anyone know of a good reference for Expression Syntax? I have
> years of experience in SQL using all kinds of tools, but this thing
> has got me really flustered. No If..Else, just endless nested Iif's
> in Expressions? No Case..When? No Count Distinct? Can I fake a
> Running Total?
> This thing is killing me!
> Jody|||BTW: a reference of available expression functions in the VB.NET runtime
library is available on MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:%23BTA8jJgEHA.3016@.tk2msftngp13.phx.gbl...
> Please refer to the following resources for more information:
> Expressions:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_6fhv.asp
> Functions:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> In particular SQL Server 2000 Reporting Services does have a CountDistinct
> and RunningValue functions.
>
> SQL Server 2000 Reporting Services uses VB .NET expression syntax which
> gives access to a large number of functions that can be used in reports.
> Switch syntax is support in places of nested ifs.
>
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408120855.1266d521@.posting.google.com...
> > Does anyone know of a good reference for Expression Syntax? I have
> > years of experience in SQL using all kinds of tools, but this thing
> > has got me really flustered. No If..Else, just endless nested Iif's
> > in Expressions? No Case..When? No Count Distinct? Can I fake a
> > Running Total?
> >
> > This thing is killing me!
> >
> > Jody
>|||Thanks Bruce...
That gets me some basic functions, but I have a ton of complex Crystal
formulas that need to be converted and I can't seem to get the 'iif'
syntax to act the way I want it to. I don't find any good
references/examples on MSDN.
Here's a Crystal example:
if {Orders.ShippedDate} = {?Report Period} then
if {Orders.RecordSource} = 'Siebel' then
if ({OrderItems.ProductTypeCode} = 'Delivery' and
Not({OrderItems.ProductCode} startswith 'HC2-D-RUSH')) or
{OrderItems.ProductCode} startswith 'HC2-D-QS' or
{OrderItems.ProductCode} = 'HC2-PR093-PC005' then
0
else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}-{@.MTD
UpCharges}
else if {Orders.RecordSource} = 'NFOS' then
if {OrderItems.ProductCode} = 'QS' or
{OrderItems.ProductCode} like '*LOGO' then
0
else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}-{@.MTD
UpCharges}
else if {Orders.RecordSource} = 'EPIC' then
if {OrderItems.ProductCode} = 'QS' or
{OrderItems.ProductCode} like '*LOGO' then
0
else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}
else 0
else 0
How would you handle something like this? (And no cracks about the
data inconsistancy, it was that way when I got here and ain't gonna
change any time soon enough for this project)
My first several tries were pretty pathetic. They would run, but no
data came back.
Thanks,
Jody
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message news:<#BTA8jJgEHA.3016@.tk2msftngp13.phx.gbl>...
> Please refer to the following resources for more information:
> Expressions:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_6fhv.asp
> Functions:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> In particular SQL Server 2000 Reporting Services does have a CountDistinct
> and RunningValue functions.
>
> SQL Server 2000 Reporting Services uses VB .NET expression syntax which
> gives access to a large number of functions that can be used in reports.
> Switch syntax is support in places of nested ifs.
>
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408120855.1266d521@.posting.google.com...
> > Does anyone know of a good reference for Expression Syntax? I have
> > years of experience in SQL using all kinds of tools, but this thing
> > has got me really flustered. No If..Else, just endless nested Iif's
> > in Expressions? No Case..When? No Count Distinct? Can I fake a
> > Running Total?
> >
> > This thing is killing me!
> >
> > Jody|||Three more options:
1. Transfer some of this logic to a stored procedure.
2. Consider using Switch():
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctswitch.asp?frame=true
3. Combination of the above two.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JodyT" <datagal@.msn.com> wrote in message
news:f9d864c3.0408121449.2109e4d0@.posting.google.com...
> Thanks Bruce...
> That gets me some basic functions, but I have a ton of complex Crystal
> formulas that need to be converted and I can't seem to get the 'iif'
> syntax to act the way I want it to. I don't find any good
> references/examples on MSDN.
> Here's a Crystal example:
> if {Orders.ShippedDate} = {?Report Period} then
> if {Orders.RecordSource} = 'Siebel' then
> if ({OrderItems.ProductTypeCode} = 'Delivery' and
> Not({OrderItems.ProductCode} startswith 'HC2-D-RUSH')) or
> {OrderItems.ProductCode} startswith 'HC2-D-QS' or
> {OrderItems.ProductCode} = 'HC2-PR093-PC005' then
> 0
> else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}-{@.MTD
> UpCharges}
> else if {Orders.RecordSource} = 'NFOS' then
> if {OrderItems.ProductCode} = 'QS' or
> {OrderItems.ProductCode} like '*LOGO' then
> 0
> else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}-{@.MTD
> UpCharges}
> else if {Orders.RecordSource} = 'EPIC' then
> if {OrderItems.ProductCode} = 'QS' or
> {OrderItems.ProductCode} like '*LOGO' then
> 0
> else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}
> else 0
> else 0
> How would you handle something like this? (And no cracks about the
> data inconsistancy, it was that way when I got here and ain't gonna
> change any time soon enough for this project)
> My first several tries were pretty pathetic. They would run, but no
> data came back.
> Thanks,
> Jody
> "Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:<#BTA8jJgEHA.3016@.tk2msftngp13.phx.gbl>...
> > Please refer to the following resources for more information:
> >
> > Expressions:
> >
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_6fhv.asp
> >
> > Functions:
> >
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> >
> > In particular SQL Server 2000 Reporting Services does have a
CountDistinct
> > and RunningValue functions.
> >
> >
> > SQL Server 2000 Reporting Services uses VB .NET expression syntax which
> > gives access to a large number of functions that can be used in reports.
> > Switch syntax is support in places of nested ifs.
> >
> >
> > --
> > Bruce Johnson [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "JodyT" <datagal@.msn.com> wrote in message
> > news:f9d864c3.0408120855.1266d521@.posting.google.com...
> > > Does anyone know of a good reference for Expression Syntax? I have
> > > years of experience in SQL using all kinds of tools, but this thing
> > > has got me really flustered. No If..Else, just endless nested Iif's
> > > in Expressions? No Case..When? No Count Distinct? Can I fake a
> > > Running Total?
> > >
> > > This thing is killing me!
> > >
> > > Jody|||Forgive me for being such a bonehead, but the .net stuff is all greek
to me. I've used many report tools in my day, but never seen anything
as unintuitive as this.
I created an SP for the code below, brought it in as a dataset, but I
can't see a way to combine the new SP dataset with my larger text type
query dataset in the same table object. So what good is it to me?
Are you saying I should create one huge SP for the whole dataset?
That sort of defeats my goal which was to use a standardized query and
report template, and vary the expressions by client (and a couple
other variables across the business) when necessary as I did in
Crystal. But the expression editor in RS doesn't seem to be robust
enough for the complexity of the original formulas.
Am I silly to try to make things this simple?
What are the books I have not teaching me? So much of the stuff in
the MSDN Library is so .net based, I find it very hard to decipher as
a newcomer.
Jody
"Ravi Mumulla \(Microsoft\)" <ravimu@.online.microsoft.com> wrote in message news:<ett6PeMgEHA.1276@.TK2MSFTNGP09.phx.gbl>...
> Three more options:
> 1. Transfer some of this logic to a stored procedure.
> 2. Consider using Switch():
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctswitch.asp?frame=true
> 3. Combination of the above two.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408121449.2109e4d0@.posting.google.com...
> > Thanks Bruce...
> >
> > That gets me some basic functions, but I have a ton of complex Crystal
> > formulas that need to be converted and I can't seem to get the 'iif'
> > syntax to act the way I want it to. I don't find any good
> > references/examples on MSDN.
> >
> > Here's a Crystal example:
> >
> > if {Orders.ShippedDate} = {?Report Period} then
> > if {Orders.RecordSource} = 'Siebel' then
> > if ({OrderItems.ProductTypeCode} = 'Delivery' and
> > Not({OrderItems.ProductCode} startswith 'HC2-D-RUSH')) or
> > {OrderItems.ProductCode} startswith 'HC2-D-QS' or
> > {OrderItems.ProductCode} = 'HC2-PR093-PC005' then
> > 0
> > else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}-{@.MTD
> > UpCharges}
> > else if {Orders.RecordSource} = 'NFOS' then
> > if {OrderItems.ProductCode} = 'QS' or
> > {OrderItems.ProductCode} like '*LOGO' then
> > 0
> > else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}-{@.MTD
> > UpCharges}
> > else if {Orders.RecordSource} = 'EPIC' then
> > if {OrderItems.ProductCode} = 'QS' or
> > {OrderItems.ProductCode} like '*LOGO' then
> > 0
> > else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}
> > else 0
> > else 0
> >
> > How would you handle something like this? (And no cracks about the
> > data inconsistancy, it was that way when I got here and ain't gonna
> > change any time soon enough for this project)
> >
> > My first several tries were pretty pathetic. They would run, but no
> > data came back.
> >
> > Thanks,
> > Jody
> >
[SNIP]
> > > >
> > > > This thing is killing me!
> > > >
> > > > Jody

expression syntax for variable

What would be the correct syntax if I wanted to add the following lines into a variable using an expression? The lines should be the first two rows before my XML.

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata">

+@.[User::xml_output]

Thanks,

Phil

Sigh... I hope there's an easier way and I'm just missing it myself...

"<?xml version=" + "\x0x22" + "1.0" + "\x0x22" + "encoding=" + "\x0x22" + "UTF-8" + "\x0x22" + "?>
<dataroot xmlns:od=" + "\x0x22" + "urn:schemas-microsoft-com:officedata" + "\x0x22" + ">" +

@.[User::xml_output]|||The reference guide: http://msdn2.microsoft.com/en-gb/library/ms141001.aspx|||

You can just escape the quotation marks:

"<?xml version=\"1.0\" encoding=\"UTF-8\"?>
<dataroot xmlns:od=\"urn:schemas-microsoft-com:officedata\">"

+@.[User::xml_output]

|||

notalreadytaken wrote:

You can just escape the quotation marks:

"<?xml version=\"1.0\" encoding=\"UTF-8\"?>
<dataroot xmlns:od=\"urn:schemas-microsoft-com:officedata\">"

+@.[User::xml_output]

Agreed. I must've had another syntax problem when I tried escaping them that way. So, Phil, now you have two options! The way I did it is great for injecting special characters...

expression syntax behavior

I have a variable that I'm storing in a database. The variable is a server name "servername\pub". When I watch this variable in the locals it becomes "servername\\pub". Is there something going on in the expression/variable syntax which is changing this?

Thanks,

Phil

tackett wrote:

I have a variable that I'm storing in a database.

Not sure what you mean by this. What database are you storing your variable in?

tackett wrote:

The variable is a server name "servername\pub". When I watch this variable in the locals it becomes "servername\\pub". Is there something going on in the expression/variable syntax which is changing this?

Backslash is an escape character. It allows you to refer to values that aren't "normal" (e.g. tab, carriage return, new line etc...). In order that you can use backslashes themselves you need an escape character for that as well - which is what double backslash is. Tht's why it shows up in your Watch window as double slash,.

If I were you, i wouldn't worry about it. Using backslash for escape characters is very common in many development technologies.

-Jamie

|||

I'm retrieving a value from a database and storing that in a variable.. to clarify.

The strange thing about this behavior is that I have two servers that I'm testing this package on. It's the same package on both servers, I thought that perhaps because our dev SSIS server was running SP2 and our production one was running SP1 that this may be causing the "\\" behavior, but it does it on both servers. However, on the dev server the package succeeds and on prod it fails when it's trying to connect to a server with a "\" in the name ie server\pub. I can't verify for sure if the error is because of the service pack difference's but I suspect it may be.

Thanks,

Phil

|||

Well if it were me, job number one would be to get the same SP on all your servers. Its extemely bad practice not to do that.

Not sure sure why its failing though I'm afraid.

-Jamie

|||

I'll see if I can convince the DBA's to do that. I'll keep you posted if that fixes this issue.

Thanks,

Phil

|||

tackett wrote:

I'm retrieving a value from a database and storing that in a variable.. to clarify.

The strange thing about this behavior is that I have two servers that I'm testing this package on. It's the same package on both servers, I thought that perhaps because our dev SSIS server was running SP2 and our production one was running SP1 that this may be causing the "\\" behavior, but it does it on both servers. However, on the dev server the package succeeds and on prod it fails when it's trying to connect to a server with a "\" in the name ie server\pub. I can't verify for sure if the error is because of the service pack difference's but I suspect it may be.

Thanks,

Phil

The double backslash is normal and is happening to escape the backslash which would otherwise indicate a special character sequence such as \t for a tab character. If you want proof, use a script task with a messagebox instead of the local window. This way it will not be escaped.

Windows.Forms.MessageBox.Show(Dts.Variables("myVar").Value.ToString())

Your connection problem on prod is most likely permissions, name resolution, or connectivity.
|||

Does this error mean anything to you guys?

<ROOT><?MSSQLError HResult=\"0x80040e37\" Source=\"Microsoft SQL Native Client\" Description=\"Invalid object name 'exp_a_client_vw'.\"?></ROOT>\r\n

I'm able to get the task to work if it's running an SP, unlike before when I was connecting to a file that had SQL in it and then running that SQL on the variable destination (yes the file location was shared). Well now, it's getting passed the task but my "XML" is garbage. Any help would be greatly appreciated.

Thanks,

phil

|||I seem to recall this being answered in another thread - but if not - your SP or SQL is calling something that depends on "exp_a_client_vw" - but it doesn't exist in the database.

expression syntax behavior

I have a variable that I'm storing in a database. The variable is a server name "servername\pub". When I watch this variable in the locals it becomes "servername\\pub". Is there something going on in the expression/variable syntax which is changing this?

Thanks,

Phil

tackett wrote:

I have a variable that I'm storing in a database.

Not sure what you mean by this. What database are you storing your variable in?

tackett wrote:

The variable is a server name "servername\pub". When I watch this variable in the locals it becomes "servername\\pub". Is there something going on in the expression/variable syntax which is changing this?

Backslash is an escape character. It allows you to refer to values that aren't "normal" (e.g. tab, carriage return, new line etc...). In order that you can use backslashes themselves you need an escape character for that as well - which is what double backslash is. Tht's why it shows up in your Watch window as double slash,.

If I were you, i wouldn't worry about it. Using backslash for escape characters is very common in many development technologies.

-Jamie

|||

I'm retrieving a value from a database and storing that in a variable.. to clarify.

The strange thing about this behavior is that I have two servers that I'm testing this package on. It's the same package on both servers, I thought that perhaps because our dev SSIS server was running SP2 and our production one was running SP1 that this may be causing the "\\" behavior, but it does it on both servers. However, on the dev server the package succeeds and on prod it fails when it's trying to connect to a server with a "\" in the name ie server\pub. I can't verify for sure if the error is because of the service pack difference's but I suspect it may be.

Thanks,

Phil

|||

Well if it were me, job number one would be to get the same SP on all your servers. Its extemely bad practice not to do that.

Not sure sure why its failing though I'm afraid.

-Jamie

|||

I'll see if I can convince the DBA's to do that. I'll keep you posted if that fixes this issue.

Thanks,

Phil

|||

tackett wrote:

I'm retrieving a value from a database and storing that in a variable.. to clarify.

The strange thing about this behavior is that I have two servers that I'm testing this package on. It's the same package on both servers, I thought that perhaps because our dev SSIS server was running SP2 and our production one was running SP1 that this may be causing the "\\" behavior, but it does it on both servers. However, on the dev server the package succeeds and on prod it fails when it's trying to connect to a server with a "\" in the name ie server\pub. I can't verify for sure if the error is because of the service pack difference's but I suspect it may be.

Thanks,

Phil

The double backslash is normal and is happening to escape the backslash which would otherwise indicate a special character sequence such as \t for a tab character. If you want proof, use a script task with a messagebox instead of the local window. This way it will not be escaped.

Windows.Forms.MessageBox.Show(Dts.Variables("myVar").Value.ToString())

Your connection problem on prod is most likely permissions, name resolution, or connectivity.
|||

Does this error mean anything to you guys?

<ROOT><?MSSQLError HResult=\"0x80040e37\" Source=\"Microsoft SQL Native Client\" Description=\"Invalid object name 'exp_a_client_vw'.\"?></ROOT>\r\n

I'm able to get the task to work if it's running an SP, unlike before when I was connecting to a file that had SQL in it and then running that SQL on the variable destination (yes the file location was shared). Well now, it's getting passed the task but my "XML" is garbage. Any help would be greatly appreciated.

Thanks,

phil

|||I seem to recall this being answered in another thread - but if not - your SP or SQL is calling something that depends on "exp_a_client_vw" - but it doesn't exist in the database.

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

Expression syntax

what is the syntax for IN in Expression?

I am tryin to do @.ID IN (1,2,3,4) then return 1 else 0...

FINDSTRING("1,2,3,4",@.[User::ID],1) > 0|||

Thank you so much

expression syntax

Hi,

I'm having some trouble with this syntax:

@.[User::xml_output] != "<ROOT></ROOT>"

I have a variable that I KNOW is = '<ROOT></ROOT>' and I've set up a precendence constraint to only go to the next step if the above not true, correct?

Do I need to convert the right side of the equation to a string or something first? Does anybody know that syntax off hand?

Thanks,

Phil

I don't know what the problem is but maybe try this:

@.[User::xml_output] != (DT_STR, <length>, 1252)"<ROOT></ROOT>"

I'd be surprised if this worked tho!

-jamie

|||Looks good to me. CAsE will matter and so will any spaces, of course.

Make sure you don't have another variable of the same name with a different scope.

And how do you KNOW the variable is populated with what you think it is? Have you debugged with a breakpoint set using the on PostExecute() event of the task before the "next step" and then looked at the locals window?|||

Jamie,

Is there a way to remove trailing leading spaces from a variable in expression syntax? I think that may be the issue.

Thanks,

Phil

|||

tackett wrote:

Jamie,

Is there a way to remove trailing leading spaces from a variable in expression syntax? I think that may be the issue.

Thanks,

Phil

trim(@.[User::Variable]) != "string"

This will do leading and trailing|||

Good stuff guys. That really helped. Turns out there were a few extra characters in there. Thanks Phil and Jamie.

expression question

Hi
Dose "Expression" can write any syntax such as CASE... WHEN... Structure?
I have a serial code such as B1, B2, B3.
When B1, the "jump to url" to http://b1.aspx. B2 the http://b2.aspx
I wirte this use IIF now but feel this way dosen't make sense.
So is any other way can instead of IIF?
Thanks for help! (I use RS 2005)
Anginot sure if you can use the case statement, in theory I don't see why
not. I also was getting irritated writing lengthy or having the giant
iif statements, so instead I now create methods in the Code tab and
simply pass the value into the method and return the result.
seems neater this way|||Try using SWITCH instead of IIF.
The syntax is
SWITCH(test1,option1,test2,option2,...)
compared to
IIF(test1,option1,IIF(test2,option2,IIF(...)))
It reads the comma separated sequence in pairs and executes the first
one that has a test = true.