Incorrect syntax near .

I try to use stored procedure to do a search for Email column. But I always get a error "Incorrect syntax near '.'", when I input keyword like "ash11.tw." And if I put keyword without dot, it works fine. It seems like the dot caused the problem. But I dont know how to fix it. Can someone help me out? Thanks.

My where expression is following.

@.spWhereSearch = 'where cont.Email like "%' + @.Email + '%"'

Try this

@.spWhereSearch = Where cont.Email Like "'% + @.Email + %'"|||

Probably try this

@.spWhereSearch = 'where cont.Email like %' + @.Email + '%'

|||

Change the double quotes " in your where search to two single quotes '' and that should fix your issue.

|||

Thanks for answering.

I have tried above three but they still dont work.

The error is still the same.

Does anyone have other suggestions?

|||If you show us the code then it would be more helpful. I dont see any reason why this would fail

@.spWhereSearch = Where cont.Email Like '% + @.Email + %'|||That line needs to look *exactly* like this (which was Motley's suggestion and is the resolution to your prolem):

@.spWhereSearch = 'where cont.Email like ''%' + @.Email + '%'''

|||

Thanks for replying. it works!!!

tmorton and Motley's suggestion is correct.

Thanks again.

Incorrect Information in SQL Agent\Operator History

Apologies if this is in the wrong forum.

I've set up an email alert to an Operator who can only be alerted via email. When i view the history of this operator, it has a "most recent notification attempt" value against by net send and not against by e-mail. The value is the correct date of the latest email alert, but is just set against the wrong notification type.

Not a major bug for me, but does anyone else have this problem?

--

DDL statement for operator.

EXEC msdb.dbo.sp_add_operator @.name=N'Rich',

@.enabled=1,

@.weekday_pager_start_time=90000,

@.weekday_pager_end_time=180000,

@.saturday_pager_start_time=90000,

@.saturday_pager_end_time=180000,

@.sunday_pager_start_time=90000,

@.sunday_pager_end_time=180000,

@.pager_days=0,

@.email_address=N'rich@.rich.net,

@.category_name=N'[Uncategorized]'

Yup.

Same problem here.

Rob

|||

There is a hotfix for this.

I couldn't find the link, but I've been researching the hotfixes for some of the problems I've been having, and this was definately one of them.

|||

This issue still exists in SQL2005 Standard 9.00.3042.00. Its been raised as a bug (#124871) back in Feb 2006 but is still present.

Does anyone know if\when this is going to be fixed for good? I've not seen any hotfix information regarding it.

(Please move to the Tools forum)

Incorrect Information in SQL Agent\Operator History

Apologies if this is in the wrong forum.

I've set up an email alert to an Operator who can only be alerted via email. When i view the history of this operator, it has a "most recent notification attempt" value against by net send and not against by e-mail. The value is the correct date of the latest email alert, but is just set against the wrong notification type.

Not a major bug for me, but does anyone else have this problem?

--

DDL statement for operator.

EXEC msdb.dbo.sp_add_operator @.name=N'Rich',

@.enabled=1,

@.weekday_pager_start_time=90000,

@.weekday_pager_end_time=180000,

@.saturday_pager_start_time=90000,

@.saturday_pager_end_time=180000,

@.sunday_pager_start_time=90000,

@.sunday_pager_end_time=180000,

@.pager_days=0,

@.email_address=N'rich@.rich.net,

@.category_name=N'[Uncategorized]'

Yup.

Same problem here.

Rob

|||

There is a hotfix for this.

I couldn't find the link, but I've been researching the hotfixes for some of the problems I've been having, and this was definately one of them.

|||

This issue still exists in SQL2005 Standard 9.00.3042.00. Its been raised as a bug (#124871) back in Feb 2006 but is still present.

Does anyone know if\when this is going to be fixed for good? I've not seen any hotfix information regarding it.

(Please move to the Tools forum)

Incorrect Information in SQL Agent\Operator History

Apologies if this is in the wrong forum.

I've set up an email alert to an Operator who can only be alerted via email. When i view the history of this operator, it has a "most recent notification attempt" value against by net send and not against by e-mail. The value is the correct date of the latest email alert, but is just set against the wrong notification type.

Not a major bug for me, but does anyone else have this problem?

--

DDL statement for operator.

EXEC msdb.dbo.sp_add_operator @.name=N'Rich',

@.enabled=1,

@.weekday_pager_start_time=90000,

@.weekday_pager_end_time=180000,

@.saturday_pager_start_time=90000,

@.saturday_pager_end_time=180000,

@.sunday_pager_start_time=90000,

@.sunday_pager_end_time=180000,

@.pager_days=0,

@.email_address=N'rich@.rich.net,

@.category_name=N'[Uncategorized]'

Yup.

Same problem here.

Rob

|||

There is a hotfix for this.

I couldn't find the link, but I've been researching the hotfixes for some of the problems I've been having, and this was definately one of them.

|||

This issue still exists in SQL2005 Standard 9.00.3042.00. Its been raised as a bug (#124871) back in Feb 2006 but is still present.

Does anyone know if\when this is going to be fixed for good? I've not seen any hotfix information regarding it.

(Please move to the Tools forum)

include the report date

Best case, I include the report date in the report header. Worst case, in
the subject line of the subscription email. I'm using SQL/RS v2000, VS 2003,
anybody know how I can do this? nothing crazy, just like this:
ReportName xx/xx/xxxx
-- LynnI tried to add a text box into the header, just filling it w/this expression:
select LEFT(Dateadd(mm,-0,getdate()),11)
it fails indicating
'the value expression for the textbox 'textbox7' contains and error:
[BC30201] Expression expected.'
please advise
-- Lynn
"Lynn" wrote:
> Best case, I include the report date in the report header. Worst case, in
> the subject line of the subscription email. I'm using SQL/RS v2000, VS 2003,
> anybody know how I can do this? nothing crazy, just like this:
> ReportName xx/xx/xxxx
> -- Lynn|||Please disregard, I got it. I included a text box with this as the
expression, everything is fine now, thanks anyway:
=Format(today()," dd MMM yy" )
-- Lynn
"Lynn" wrote:
> I tried to add a text box into the header, just filling it w/this expression:
> select LEFT(Dateadd(mm,-0,getdate()),11)
> it fails indicating
> 'the value expression for the textbox 'textbox7' contains and error:
> [BC30201] Expression expected.'
> please advise
> -- Lynn
>
> "Lynn" wrote:
> > Best case, I include the report date in the report header. Worst case, in
> > the subject line of the subscription email. I'm using SQL/RS v2000, VS 2003,
> > anybody know how I can do this? nothing crazy, just like this:
> >
> > ReportName xx/xx/xxxx
> >
> > -- Lynn|||Just an FYI, you can use expression builder, look at the global variables.
One of those has what you want.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:DA80718F-E475-4891-93A5-EAEB1CB3D2D7@.microsoft.com...
> Best case, I include the report date in the report header. Worst case, in
> the subject line of the subscription email. I'm using SQL/RS v2000, VS
> 2003,
> anybody know how I can do this? nothing crazy, just like this:
> ReportName xx/xx/xxxx
> -- Lynn|||Will do, Bruce, thank you. I will check it out.
-- Lynn
oh, oh, just a slightly related question -- i've created my browser role and
i'm trying to send out this : http://webserver/reports
such that certain people can have direct access to a number of my reports
right now there's a 'Users Folders' in there along with my reports. I'm
unable to hide that. Do you know how to get rid of it?
"Bruce L-C [MVP]" wrote:
> Just an FYI, you can use expression builder, look at the global variables.
> One of those has what you want.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:DA80718F-E475-4891-93A5-EAEB1CB3D2D7@.microsoft.com...
> > Best case, I include the report date in the report header. Worst case, in
> > the subject line of the subscription email. I'm using SQL/RS v2000, VS
> > 2003,
> > anybody know how I can do this? nothing crazy, just like this:
> >
> > ReportName xx/xx/xxxx
> >
> > -- Lynn
>
>|||Yes, you can remove the browser role from that particular folder. Normally
the folder inherits from the parent so all folders initially have the same
roles assigned to them. Click on the folder, properties, security and remove
the browser role. Or, if it is just that you don't want them distracted by
it, you could hide in list view (properties, general).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:CDBC9A73-7FD8-4271-9BCE-73D03A0BD3D1@.microsoft.com...
> Will do, Bruce, thank you. I will check it out.
> -- Lynn
> oh, oh, just a slightly related question -- i've created my browser role
> and
> i'm trying to send out this : http://webserver/reports
> such that certain people can have direct access to a number of my reports
> right now there's a 'Users Folders' in there along with my reports. I'm
> unable to hide that. Do you know how to get rid of it?
> "Bruce L-C [MVP]" wrote:
>> Just an FYI, you can use expression builder, look at the global
>> variables.
>> One of those has what you want.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
>> news:DA80718F-E475-4891-93A5-EAEB1CB3D2D7@.microsoft.com...
>> > Best case, I include the report date in the report header. Worst case,
>> > in
>> > the subject line of the subscription email. I'm using SQL/RS v2000, VS
>> > 2003,
>> > anybody know how I can do this? nothing crazy, just like this:
>> >
>> > ReportName xx/xx/xxxx
>> >
>> > -- Lynn
>>