Email subscription Reported as Successful but not arriving in recipients inbox.

I have a report that is sent via email to a list of recipients. When I look at the subscription in 'My Subscriptions' the value in the Status column states that the message was 'sent successfully', however the email does not appear in the inboxes of any of the intended recipents. The report renders OK via the browser and only takes a few seconds to process. The subscription exports the report as an Excel file. The excel file is approx 8MB in size but this is still below the attachment size limit for internal emails within my organisation. The subscription is tied to a shared schedule. Other reports that are tied to the shared schedule are shown as successful in 'My Subscriptions' and also appear in the inboxes of recipients. All email address are correct.

I forgot to mention, the report was successfully received by all recipients until very recently and the report has not been modified since this time.

Has anyone come across this problem before.

Thanks

Mike

The Reporting Services e-mail delivery extension considers "sent successfully" to be "I handed it off to some SMTP forwarder". What happens to the e-mail after that is outside the scope of RS. You need to investigate within the e-mail system.

As a diagnostic, you might try delivering to an SMTP pickup folder temporarily to convince yourself that the Report Server is generating the e-mails. Find the rsReportServer.config file, change the "SendUsing" element to 1, specify some SMTPServerPickupDirectory, and try the delivery. If you get .EML files created in the specified folder, RS has done it's part of the delivery. Make sure you set SendUsing back to 2 or <blank> after you've done your test.

Here's a sample of the config file section you need to modify:

<RSEmailDPConfiguration>
<SMTPServer></SMTPServer>
<SMTPServerPort></SMTPServerPort>
<SMTPAccountName></SMTPAccountName>
<SMTPConnectionTimeout></SMTPConnectionTimeout>
<SMTPServerPickupDirectory>D:\Email</SMTPServerPickupDirectory>
<SMTPUseSSL></SMTPUseSSL>
<SendUsing>1</SendUsing>
<SMTPAuthenticate></SMTPAuthenticate>
<From>_awtest@.Microsoft.Com</From>
<EmbeddedRenderFormats>
<RenderingExtension>MHTML</RenderingExtension>
</EmbeddedRenderFormats>
<PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
<RenderingExtension>RGDI</RenderingExtension>
</ExcludedRenderFormats>
<SendEmailToUserAlias>True</SendEmailToUserAlias>
<DefaultHostName></DefaultHostName>
<PermittedHosts></PermittedHosts>
</RSEmailDPConfiguration>

Email Subscription not working - help please

Nothing shows up under the "Last Run" column. No error msg or entry in
reporting services or SMTP log.
Looks like reporting server is completly ignoring the subscription entry.
I'm using "once" option and selecting a time just 10-15 minutes ahead.
Here are the entries in my RSReportServer.config file:
----
<Extensions>
<Delivery>
<Extension Name="Report Server FileShare"
Type="Microsoft.ReportingServices.FileShareDeliveryProvider.FileShareProvider,ReportingServicesFileShareDeliveryProvider">
<MaxRetries>3</MaxRetries>
<SecondsBeforeRetry>900</SecondsBeforeRetry>
<Configuration>
<FileShareConfiguration>
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
</ExcludedRenderFormats>
</FileShareConfiguration>
</Configuration>
</Extension>
<Extension Name="Report Server Email"
Type="Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider,ReportingServicesEmailDeliveryProvider">
<MaxRetries>3</MaxRetries>
<SecondsBeforeRetry>900</SecondsBeforeRetry>
<Configuration>
<RSEmailDPConfiguration>
<SMTPServer>150.8.111.82</SMTPServer>
<SMTPServerPort></SMTPServerPort>
<SMTPAccountName></SMTPAccountName>
<SMTPConnectionTimeout></SMTPConnectionTimeout>
<SMTPServerPickupDirectory></SMTPServerPickupDirectory>
<SMTPUseSSL></SMTPUseSSL>
<SendUsing></SendUsing>
<SMTPAuthenticate></SMTPAuthenticate>
<From>altbill@.discountcar.com</From>
<EmbeddedRenderFormats>
<RenderingExtension>MHTML</RenderingExtension>
</EmbeddedRenderFormats>
<PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
</ExcludedRenderFormats>
<SendEmailToUserAlias>True</SendEmailToUserAlias>
<DefaultHostName></DefaultHostName>
<PermittedHosts></PermittedHosts>
</RSEmailDPConfiguration>
</Configuration>
</Extension>
<Extension Name="NULL"
Type="Microsoft.ReportingServices.NullDeliveryProvider.NullProvider,ReportingServicesNullDeliveryProvider"/>
</Delivery>
----
What could be wrong... any ideas...
Thanks
AJHi, I have the exact same problem. I'm running win2003, and you?
"Amit Jain" wrote:
> Nothing shows up under the "Last Run" column. No error msg or entry in
> reporting services or SMTP log.
> Looks like reporting server is completly ignoring the subscription entry.
> I'm using "once" option and selecting a time just 10-15 minutes ahead.
> Here are the entries in my RSReportServer.config file:
> ----
> <Extensions>
> <Delivery>
> <Extension Name="Report Server FileShare"
> Type="Microsoft.ReportingServices.FileShareDeliveryProvider.FileShareProvider,ReportingServicesFileShareDeliveryProvider">
> <MaxRetries>3</MaxRetries>
> <SecondsBeforeRetry>900</SecondsBeforeRetry>
> <Configuration>
> <FileShareConfiguration>
> <ExcludedRenderFormats>
> <RenderingExtension>HTMLOWC</RenderingExtension>
> <RenderingExtension>NULL</RenderingExtension>
> </ExcludedRenderFormats>
> </FileShareConfiguration>
> </Configuration>
> </Extension>
> <Extension Name="Report Server Email"
> Type="Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider,ReportingServicesEmailDeliveryProvider">
> <MaxRetries>3</MaxRetries>
> <SecondsBeforeRetry>900</SecondsBeforeRetry>
> <Configuration>
> <RSEmailDPConfiguration>
> <SMTPServer>150.8.111.82</SMTPServer>
> <SMTPServerPort></SMTPServerPort>
> <SMTPAccountName></SMTPAccountName>
> <SMTPConnectionTimeout></SMTPConnectionTimeout>
> <SMTPServerPickupDirectory></SMTPServerPickupDirectory>
> <SMTPUseSSL></SMTPUseSSL>
> <SendUsing></SendUsing>
> <SMTPAuthenticate></SMTPAuthenticate>
> <From>altbill@.discountcar.com</From>
> <EmbeddedRenderFormats>
> <RenderingExtension>MHTML</RenderingExtension>
> </EmbeddedRenderFormats>
> <PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
> <ExcludedRenderFormats>
> <RenderingExtension>HTMLOWC</RenderingExtension>
> <RenderingExtension>NULL</RenderingExtension>
> </ExcludedRenderFormats>
> <SendEmailToUserAlias>True</SendEmailToUserAlias>
> <DefaultHostName></DefaultHostName>
> <PermittedHosts></PermittedHosts>
> </RSEmailDPConfiguration>
> </Configuration>
> </Extension>
> <Extension Name="NULL"
> Type="Microsoft.ReportingServices.NullDeliveryProvider.NullProvider,ReportingServicesNullDeliveryProvider"/>
> </Delivery>
> ----
> What could be wrong... any ideas...
> Thanks
> AJ
>
>|||I've tried it on Win2K server and Win2K professional, same issue on both
installations.
"Hennie" <Hennie@.discussions.microsoft.com> wrote in message
news:15DCD777-B85F-4D3D-893C-618D7B138F94@.microsoft.com...
> Hi, I have the exact same problem. I'm running win2003, and you?
> "Amit Jain" wrote:
>> Nothing shows up under the "Last Run" column. No error msg or entry in
>> reporting services or SMTP log.
>> Looks like reporting server is completly ignoring the subscription entry.
>> I'm using "once" option and selecting a time just 10-15 minutes ahead.
>> Here are the entries in my RSReportServer.config file:
>> ----
>> <Extensions>
>> <Delivery>
>> <Extension Name="Report Server FileShare"
>> Type="Microsoft.ReportingServices.FileShareDeliveryProvider.FileShareProvider,ReportingServicesFileShareDeliveryProvider">
>> <MaxRetries>3</MaxRetries>
>> <SecondsBeforeRetry>900</SecondsBeforeRetry>
>> <Configuration>
>> <FileShareConfiguration>
>> <ExcludedRenderFormats>
>> <RenderingExtension>HTMLOWC</RenderingExtension>
>> <RenderingExtension>NULL</RenderingExtension>
>> </ExcludedRenderFormats>
>> </FileShareConfiguration>
>> </Configuration>
>> </Extension>
>> <Extension Name="Report Server Email"
>> Type="Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider,ReportingServicesEmailDeliveryProvider">
>> <MaxRetries>3</MaxRetries>
>> <SecondsBeforeRetry>900</SecondsBeforeRetry>
>> <Configuration>
>> <RSEmailDPConfiguration>
>> <SMTPServer>150.8.111.82</SMTPServer>
>> <SMTPServerPort></SMTPServerPort>
>> <SMTPAccountName></SMTPAccountName>
>> <SMTPConnectionTimeout></SMTPConnectionTimeout>
>> <SMTPServerPickupDirectory></SMTPServerPickupDirectory>
>> <SMTPUseSSL></SMTPUseSSL>
>> <SendUsing></SendUsing>
>> <SMTPAuthenticate></SMTPAuthenticate>
>> <From>altbill@.discountcar.com</From>
>> <EmbeddedRenderFormats>
>> <RenderingExtension>MHTML</RenderingExtension>
>> </EmbeddedRenderFormats>
>> <PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
>> <ExcludedRenderFormats>
>> <RenderingExtension>HTMLOWC</RenderingExtension>
>> <RenderingExtension>NULL</RenderingExtension>
>> </ExcludedRenderFormats>
>> <SendEmailToUserAlias>True</SendEmailToUserAlias>
>> <DefaultHostName></DefaultHostName>
>> <PermittedHosts></PermittedHosts>
>> </RSEmailDPConfiguration>
>> </Configuration>
>> </Extension>
>> <Extension Name="NULL"
>> Type="Microsoft.ReportingServices.NullDeliveryProvider.NullProvider,ReportingServicesNullDeliveryProvider"/>
>> </Delivery>
>> ----
>> What could be wrong... any ideas...
>> Thanks
>> AJ
>>|||I fixed it by starting the ReportServer service. I should've have checked
it before posting.
"AJ" <amitjn_ca@.yahoo.ca> wrote in message
news:uIW6bH7AFHA.3120@.TK2MSFTNGP12.phx.gbl...
> I've tried it on Win2K server and Win2K professional, same issue on both
> installations.
> "Hennie" <Hennie@.discussions.microsoft.com> wrote in message
> news:15DCD777-B85F-4D3D-893C-618D7B138F94@.microsoft.com...
>> Hi, I have the exact same problem. I'm running win2003, and you?
>> "Amit Jain" wrote:
>> Nothing shows up under the "Last Run" column. No error msg or entry in
>> reporting services or SMTP log.
>> Looks like reporting server is completly ignoring the subscription
>> entry.
>> I'm using "once" option and selecting a time just 10-15 minutes ahead.
>> Here are the entries in my RSReportServer.config file:
>> ----
>> <Extensions>
>> <Delivery>
>> <Extension Name="Report Server FileShare"
>> Type="Microsoft.ReportingServices.FileShareDeliveryProvider.FileShareProvider,ReportingServicesFileShareDeliveryProvider">
>> <MaxRetries>3</MaxRetries>
>> <SecondsBeforeRetry>900</SecondsBeforeRetry>
>> <Configuration>
>> <FileShareConfiguration>
>> <ExcludedRenderFormats>
>> <RenderingExtension>HTMLOWC</RenderingExtension>
>> <RenderingExtension>NULL</RenderingExtension>
>> </ExcludedRenderFormats>
>> </FileShareConfiguration>
>> </Configuration>
>> </Extension>
>> <Extension Name="Report Server Email"
>> Type="Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider,ReportingServicesEmailDeliveryProvider">
>> <MaxRetries>3</MaxRetries>
>> <SecondsBeforeRetry>900</SecondsBeforeRetry>
>> <Configuration>
>> <RSEmailDPConfiguration>
>> <SMTPServer>150.8.111.82</SMTPServer>
>> <SMTPServerPort></SMTPServerPort>
>> <SMTPAccountName></SMTPAccountName>
>> <SMTPConnectionTimeout></SMTPConnectionTimeout>
>> <SMTPServerPickupDirectory></SMTPServerPickupDirectory>
>> <SMTPUseSSL></SMTPUseSSL>
>> <SendUsing></SendUsing>
>> <SMTPAuthenticate></SMTPAuthenticate>
>> <From>altbill@.discountcar.com</From>
>> <EmbeddedRenderFormats>
>> <RenderingExtension>MHTML</RenderingExtension>
>> </EmbeddedRenderFormats>
>> <PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
>> <ExcludedRenderFormats>
>> <RenderingExtension>HTMLOWC</RenderingExtension>
>> <RenderingExtension>NULL</RenderingExtension>
>> </ExcludedRenderFormats>
>> <SendEmailToUserAlias>True</SendEmailToUserAlias>
>> <DefaultHostName></DefaultHostName>
>> <PermittedHosts></PermittedHosts>
>> </RSEmailDPConfiguration>
>> </Configuration>
>> </Extension>
>> <Extension Name="NULL"
>> Type="Microsoft.ReportingServices.NullDeliveryProvider.NullProvider,ReportingServicesNullDeliveryProvider"/>
>> </Delivery>
>> ----
>> What could be wrong... any ideas...
>> Thanks
>> AJ
>>
>|||In general, when you encounter issues like this, check the reporting
services log files. You should see a event polling message and status for
any notifications that are entered. This will also tell you if there are
errors. If you do not see any service log files, you'll need to start the
service called reportserver
-Lukasz
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Amit Jain" <amitjn_ca@.yahoo.ca> wrote in message
news:eNlilGyAFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Nothing shows up under the "Last Run" column. No error msg or entry in
> reporting services or SMTP log.
> Looks like reporting server is completly ignoring the subscription entry.
> I'm using "once" option and selecting a time just 10-15 minutes ahead.
> Here are the entries in my RSReportServer.config file:
> ----
> <Extensions>
> <Delivery>
> <Extension Name="Report Server FileShare"
> Type="Microsoft.ReportingServices.FileShareDeliveryProvider.FileShareProvider,ReportingServicesFileShareDeliveryProvider">
> <MaxRetries>3</MaxRetries>
> <SecondsBeforeRetry>900</SecondsBeforeRetry>
> <Configuration>
> <FileShareConfiguration>
> <ExcludedRenderFormats>
> <RenderingExtension>HTMLOWC</RenderingExtension>
> <RenderingExtension>NULL</RenderingExtension>
> </ExcludedRenderFormats>
> </FileShareConfiguration>
> </Configuration>
> </Extension>
> <Extension Name="Report Server Email"
> Type="Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider,ReportingServicesEmailDeliveryProvider">
> <MaxRetries>3</MaxRetries>
> <SecondsBeforeRetry>900</SecondsBeforeRetry>
> <Configuration>
> <RSEmailDPConfiguration>
> <SMTPServer>150.8.111.82</SMTPServer>
> <SMTPServerPort></SMTPServerPort>
> <SMTPAccountName></SMTPAccountName>
> <SMTPConnectionTimeout></SMTPConnectionTimeout>
> <SMTPServerPickupDirectory></SMTPServerPickupDirectory>
> <SMTPUseSSL></SMTPUseSSL>
> <SendUsing></SendUsing>
> <SMTPAuthenticate></SMTPAuthenticate>
> <From>altbill@.discountcar.com</From>
> <EmbeddedRenderFormats>
> <RenderingExtension>MHTML</RenderingExtension>
> </EmbeddedRenderFormats>
> <PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
> <ExcludedRenderFormats>
> <RenderingExtension>HTMLOWC</RenderingExtension>
> <RenderingExtension>NULL</RenderingExtension>
> </ExcludedRenderFormats>
> <SendEmailToUserAlias>True</SendEmailToUserAlias>
> <DefaultHostName></DefaultHostName>
> <PermittedHosts></PermittedHosts>
> </RSEmailDPConfiguration>
> </Configuration>
> </Extension>
> <Extension Name="NULL"
> Type="Microsoft.ReportingServices.NullDeliveryProvider.NullProvider,ReportingServicesNullDeliveryProvider"/>
> </Delivery>
> ----
> What could be wrong... any ideas...
> Thanks
> AJ
>|||Like Lukasz says, check the report Server Log, if any error occured you will
find them there, in the first example of Amit i would suggest looking in the
logs of the SMTP Server (For example you could send the EMail temporary to
an virtual SMTP Server where you have full control of and relay it to the
productive server to check the logs) or just to check if the SMTP Server
accepted relayed EMails to conenct to the mail server via telnet.
--> telnet Mailserver 25
Rest can be found here:
http://www.yuki-onna.co.uk/email/smtp.html
HTH, Jens Süßmeyer.
--
http://www.sqlserver2005.de
--
"Koen" <Koen@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F6365949-8F76-41B7-B74A-182A7A5869C6@.microsoft.com...
> Is there an internal clock or so in reporting services ? Because i'm
> facing
> the same problem, but when i check my log files, i see that the time when
> these files were created is impossible (eg: 2 AM, when nobody's at the
> office
> and the server is shut down).
> Thanks in advance
> "Lukasz Pawlowski [MSFT]" wrote:
>> In general, when you encounter issues like this, check the reporting
>> services log files. You should see a event polling message and status
>> for
>> any notifications that are entered. This will also tell you if there are
>> errors. If you do not see any service log files, you'll need to start
>> the
>> service called reportserver
>> -Lukasz
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>>
>> "Amit Jain" <amitjn_ca@.yahoo.ca> wrote in message
>> news:eNlilGyAFHA.2540@.TK2MSFTNGP09.phx.gbl...
>> > Nothing shows up under the "Last Run" column. No error msg or entry in
>> > reporting services or SMTP log.
>> > Looks like reporting server is completly ignoring the subscription
>> > entry.
>> > I'm using "once" option and selecting a time just 10-15 minutes ahead.
>> > Here are the entries in my RSReportServer.config file:
>> > ----
>> > <Extensions>
>> > <Delivery>
>> > <Extension Name="Report Server FileShare"
>> > Type="Microsoft.ReportingServices.FileShareDeliveryProvider.FileShareProvider,ReportingServicesFileShareDeliveryProvider">
>> > <MaxRetries>3</MaxRetries>
>> > <SecondsBeforeRetry>900</SecondsBeforeRetry>
>> > <Configuration>
>> > <FileShareConfiguration>
>> > <ExcludedRenderFormats>
>> > <RenderingExtension>HTMLOWC</RenderingExtension>
>> > <RenderingExtension>NULL</RenderingExtension>
>> > </ExcludedRenderFormats>
>> > </FileShareConfiguration>
>> > </Configuration>
>> > </Extension>
>> > <Extension Name="Report Server Email"
>> > Type="Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider,ReportingServicesEmailDeliveryProvider">
>> > <MaxRetries>3</MaxRetries>
>> > <SecondsBeforeRetry>900</SecondsBeforeRetry>
>> > <Configuration>
>> > <RSEmailDPConfiguration>
>> > <SMTPServer>150.8.111.82</SMTPServer>
>> > <SMTPServerPort></SMTPServerPort>
>> > <SMTPAccountName></SMTPAccountName>
>> > <SMTPConnectionTimeout></SMTPConnectionTimeout>
>> > <SMTPServerPickupDirectory></SMTPServerPickupDirectory>
>> > <SMTPUseSSL></SMTPUseSSL>
>> > <SendUsing></SendUsing>
>> > <SMTPAuthenticate></SMTPAuthenticate>
>> > <From>altbill@.discountcar.com</From>
>> > <EmbeddedRenderFormats>
>> > <RenderingExtension>MHTML</RenderingExtension>
>> > </EmbeddedRenderFormats>
>> > <PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
>> > <ExcludedRenderFormats>
>> > <RenderingExtension>HTMLOWC</RenderingExtension>
>> > <RenderingExtension>NULL</RenderingExtension>
>> > </ExcludedRenderFormats>
>> > <SendEmailToUserAlias>True</SendEmailToUserAlias>
>> > <DefaultHostName></DefaultHostName>
>> > <PermittedHosts></PermittedHosts>
>> > </RSEmailDPConfiguration>
>> > </Configuration>
>> > </Extension>
>> > <Extension Name="NULL"
>> > Type="Microsoft.ReportingServices.NullDeliveryProvider.NullProvider,ReportingServicesNullDeliveryProvider"/>
>> > </Delivery>
>> > ----
>> > What could be wrong... any ideas...
>> > Thanks
>> > AJ
>> >
>>

e-mail in SSRS reports with attachment

Hi All,

In my SSRS report there is a column with email field, when the user clicks on it -- it should open the outlook and in the attachment field there should be a screenshot of that report

i think anything i could do is only in the jump to url in the navigation tab......Please suggest me the solution or any other alternatives.

Thanks in advance for help

Warm Regards,

Chanduu.

I am using an alternate approach, but that is also not working

The approach I was using is writing a code in c# to export that report in PDF programmatically using c#, without opening SSRS so that I could attach it outlook/write a mail program, for this I got some help from this site http://www.programurl.com/pdf-reporting-services.htm, but this file is saving a PDG file without any data.

If you have any solution, please send me so that I could move further

Warm Regards,

Chanduu

e-mail in SSRS reports with attachment

Hi All,

In my SSRS report there is a column with email field, when the user clicks on it -- it should open the outlook and in the attachment field there should be a screenshot of that report

i think anything i could do is only in the jump to url in the navigation tab......Please suggest me the solution or any other alternatives.

Thanks in advance for help

Warm Regards,

Chanduu.

I am using an alternate approach, but that is also not working

The approach I was using is writing a code in c# to export that report in PDF programmatically using c#, without opening SSRS so that I could attach it outlook/write a mail program, for this I got some help from this site http://www.programurl.com/pdf-reporting-services.htm, but this file is saving a PDG file without any data.

If you have any solution, please send me so that I could move further

Warm Regards,

Chanduu

Email Column

Can we provide an outlook based email column so that as the email is
clicked. directly outlook opens with to mail column. so tha we can directly
send the email as they click corresponding column.
Navin.DHi,
Why dont u have a link of "mailto:chathurangaw@.zone24x7.com " for that
column
on the column u need the link... right click and select properties,->
Advanced
then give an expression for the jump to URL of the navigation tab...
it's real simple...
Chathuranga
NAVIN.D wrote:
> Can we provide an outlook based email column so that as the email is
> clicked. directly outlook opens with to mail column. so tha we can directly
> send the email as they click corresponding column.
> Navin.D|||There are some 10,000 records filled in directly from dbase for that email
column, can you please provide the expression.
"chathurangakw@.gmail.com" wrote:
> Hi,
> Why dont u have a link of "mailto:chathurangaw@.zone24x7.com " for that
> column
> on the column u need the link... right click and select properties,->
> Advanced
> then give an expression for the jump to URL of the navigation tab...
> it's real simple...
> Chathuranga
>
> NAVIN.D wrote:
> > Can we provide an outlook based email column so that as the email is
> > clicked. directly outlook opens with to mail column. so tha we can directly
> > send the email as they click corresponding column.
> >
> > Navin.D
>|||Assuming the db field is email... The Action URL might be
="mailto:" & Fields!email.Value
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"NAVIN.D" wrote:
> There are some 10,000 records filled in directly from dbase for that email
> column, can you please provide the expression.
> "chathurangakw@.gmail.com" wrote:
> > Hi,
> >
> > Why dont u have a link of "mailto:chathurangaw@.zone24x7.com " for that
> > column
> > on the column u need the link... right click and select properties,->
> > Advanced
> > then give an expression for the jump to URL of the navigation tab...
> >
> > it's real simple...
> >
> > Chathuranga
> >
> >
> > NAVIN.D wrote:
> > > Can we provide an outlook based email column so that as the email is
> > > clicked. directly outlook opens with to mail column. so tha we can directly
> > > send the email as they click corresponding column.
> > >
> > > Navin.D
> >
> >|||the properties for the currently selected item are not valid,please correct
all errors before contuning. Is the error i am getting when i use it
"Wayne Snyder" wrote:
> Assuming the db field is email... The Action URL might be
> ="mailto:" & Fields!email.Value
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "NAVIN.D" wrote:
> > There are some 10,000 records filled in directly from dbase for that email
> > column, can you please provide the expression.
> >
> > "chathurangakw@.gmail.com" wrote:
> >
> > > Hi,
> > >
> > > Why dont u have a link of "mailto:chathurangaw@.zone24x7.com " for that
> > > column
> > > on the column u need the link... right click and select properties,->
> > > Advanced
> > > then give an expression for the jump to URL of the navigation tab...
> > >
> > > it's real simple...
> > >
> > > Chathuranga
> > >
> > >
> > > NAVIN.D wrote:
> > > > Can we provide an outlook based email column so that as the email is
> > > > clicked. directly outlook opens with to mail column. so tha we can directly
> > > > send the email as they click corresponding column.
> > > >
> > > > Navin.D
> > >
> > >|||the properties for the currently selected item are not valid,please correct
all errors before contuning. Is the error i am getting when i use it
Wayne Snyder" wrote:
> Assuming the db field is email... The Action URL might be
> ="mailto:" & Fields!email.Value
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "NAVIN.D" wrote:
> > There are some 10,000 records filled in directly from dbase for that email
> > column, can you please provide the expression.
> >
> > "chathurangakw@.gmail.com" wrote:
> >
> > > Hi,
> > >
> > > Why dont u have a link of "mailto:chathurangaw@.zone24x7.com " for that
> > > column
> > > on the column u need the link... right click and select properties,->
> > > Advanced
> > > then give an expression for the jump to URL of the navigation tab...
> > >
> > > it's real simple...
> > >
> > > Chathuranga
> > >
> > >
> > > NAVIN.D wrote:
> > > > Can we provide an outlook based email column so that as the email is
> > > > clicked. directly outlook opens with to mail column. so tha we can directly
> > > > send the email as they click corresponding column.
> > > >
> > > > Navin.D
> > >
> > >|||Wayne Snyder 's reply should be enough to get it working.... u might
be in a wrong place... I'm talking about the Navigation Tab of a
Properties Dialog-> advanced... for a TextBox|||or a TextBox inside a Table|||tried the same way but get the error as i mentioned
thank you
Navin.D
"chathurangakw@.gmail.com" wrote:
> or a TextBox inside a Table
>

EM: Jobs: Runnable column

Hi,
I havent been able to figure out when a job is marked as not runnable.
I have several jobs, some enabled, some disabled, but they are all marked
as runnable.
Someone who can tell?A job without any jobsteps isn't runnable.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns96FE6969F3064gurbaohotmailcom@.129.250.171.66...
> Hi,
> I havent been able to figure out when a job is marked as not runnable.
> I have several jobs, some enabled, some disabled, but they are all marked
> as runnable.
> Someone who can tell?

Eliminating the expensive remote join

I've got a table (dexIndex) with 9 million rows in it. It's got an
identity column (index_id) and a column that's full-text indexed
(insureds_name) and a column that I want to filter on (agency_number).
When I query like this:
select * from decIndex where agency_number = '0349323' and
contains(insureds_name,'"SMITH"')
The execution plan involves a remote scan (98%) and a clustered index
seek (0%) which lead into a hash match/inner join (2%). The problem is
that the remote scan is returning 51,000 rows even though only 1 comes
out of the hash match/inner join. Thus, the query takes over a minute
on a built-to-the-hilt server.
Is there a way to have the FT engine only look through or return a
subset of the FT index, like only those rows which correspond to
agency_number = '0349323'?
I'd like to avoid adding agency_number to the FT index.
Thanks!
Regretably there is no way of doing this. One of the strategies of getting
around this problem is to partition your table according to the other
elements of you where clause. Another option is to build full text indexes
on indexed views in SQL 2005. Again the view would be of a section of your
table which would match your where conditions.
"Ryan Walberg [MCSD]" <generalpf@.nospam.yahoo.reallynospam.com> wrote in
message news:%233kbwJZJFHA.588@.TK2MSFTNGP15.phx.gbl...
> I've got a table (dexIndex) with 9 million rows in it. It's got an
> identity column (index_id) and a column that's full-text indexed
> (insureds_name) and a column that I want to filter on (agency_number).
> When I query like this:
> select * from decIndex where agency_number = '0349323' and
> contains(insureds_name,'"SMITH"')
> The execution plan involves a remote scan (98%) and a clustered index seek
> (0%) which lead into a hash match/inner join (2%). The problem is that
> the remote scan is returning 51,000 rows even though only 1 comes out of
> the hash match/inner join. Thus, the query takes over a minute on a
> built-to-the-hilt server.
> Is there a way to have the FT engine only look through or return a subset
> of the FT index, like only those rows which correspond to agency_number =
> '0349323'?
> I'd like to avoid adding agency_number to the FT index.
> Thanks!
|||Hilary Cotter wrote:
> Regretably there is no way of doing this. One of the strategies of getting
> around this problem is to partition your table according to the other
> elements of you where clause. Another option is to build full text indexes
> on indexed views in SQL 2005. Again the view would be of a section of your
> table which would match your where conditions.
I ended up clustering the index that corresponded to the unique column
for the full-text index and that sped it up a great deal.
Thanks,
Ryan

eliminate the Return charactor in a column

Hi,
We have a column defined as ntext for a column and we need to transfer this
column to oralce platform. Before transferring, we convert the data to
varchar. Some of data content "RETURN" charactor, and it caused issue for
providing report or printing.
Please suggest a way to eliminate the "RETURN" charactor in this column and
we can keep all the data in the nice order. Thanks.
Best Regards,
Lynnhttp://www.aspfaq.com/2445
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:3F6073C5-BF70-4DFA-BAE0-DD4EFA30A8AA@.microsoft.com...
> Hi,
> We have a column defined as ntext for a column and we need to transfer
> this
> column to oralce platform. Before transferring, we convert the data to
> varchar. Some of data content "RETURN" charactor, and it caused issue for
> providing report or printing.
> Please suggest a way to eliminate the "RETURN" charactor in this column
> and
> we can keep all the data in the nice order. Thanks.
> --
> Best Regards,
> Lynn

Eliminate duplicates from only one column

--
Randycan you expand your query...'
or try this...
select distinct(one_column) from table_with_one_column|||GROUP BY clause
"randy1200" <randy1200@.newsgroups.nospam> wrote in message
news:A063AE9C-6B3A-4FB8-B6D4-2BEFC7A5FF41@.microsoft.com...
> --
> Randy

effect of more column in where clause

what are the effects of having number of columns in WHERE clause, ie: if we use more columns in where clause, what will be its impact on performance.

this is important for me to design queries

having a lot of unneccessary where clauses will surely slowdown the query if there are.

the best thing that you can do is to streamline the logic into its simplest form

|||Perhaps I misunderstand the question; my normal experience is that if the additional columns contribute to filtering out unwanted records that the additional columns in the where clause usually enhance performance.|||

yeah thats right but a poorly written where clause will definetely slowdown performance.

The trick is to use the best suited functions and keywords in the where clause

here some useful link:

http://www.sql-server-performance.com/transact_sql.asp

regards,

joey

|||

Hi joeydj,

what do you mean by "the trick is to use the best suited where clause by making use of the powerful sql server functions"?

AMB

|||

oh sorry. got a grammar problem

here i have it corrected

"The trick is to use the best suited functions and keywords in the where clause"

hmmm... thats better..

example:

select ... from

where x=1 and x=2 and x=7 and x=9

maybe written as

where x in (1,2,7,9)

|||

Joey is normally better than that; please cut him some slack. I think what he means is

select ... from

where x=1 OR x=2 OR x=7 OR x=9

maybe written as

where x in (1,2,7,9)

|||

hahaha. not been here for sometime.

hmmm thanks kent.

|||

Not to mention that there is no performance benefit in choosing one of these as opposed to the other, anyway. (There is a readability benefit, and the two forms will behave differently if you carelessly tack on AND <another condition> to the query.

Steve Kass

Drew University

http://www.stevekass.com

|||

Can you give an example of the choices you have to make? Usually if you add "more columns in where clause", you change the meaning of the query (but not always), and the first goal of designing a query is for it to ask the right question...

Steve Kass

Drew University

http://www.stevekass.com

|||

One thing that people sometimes forget is that a full table scan can sometimes be better than an indexed scan. If you are ultimately going to read every block of data from disk via an index scan then you might want to forgo the index. Modern databases with good statistics can usually establish an execution plan that is "good enough" but it can still pay to understand your data.

Here is a quick example.

Let's say I have a database of people and for some reason their geneder in overwhelmingly biased in one direction. An optimizer might look at the number of unique values for gender and assume a 50/50 split in the data. It might make good sence to always use an index on gender to access the rows of the table from the optimizer's best guess. However, that might not be the case in practice.

Let's say the table is 100k records with :

90% F

10% M

To find the men an indexed lookup on gender is probably a good thing. On the other hand an indexed lookup to find women will result in more disk IO and slower performance than a full table scan (not counting your network).

Knowing this distribution ahead of time might lead someone looking for all women to do something like

Select * from employees where gender + '' = 'F'

|||

thanks for all your views.

let me give u a specific example.

my database has "branch name" field in all the tables. and we have seperate copy of database for each branch. so a particular branch user will connect to his branch, which has records only for that branch.

in this situation, there is no need to filter the records again with "branch name", but if there is no performance issue , i wish to include it in the WHERE clause to be 100% sure that all the records that the query output does have the same branch name.

|||

In your situation. I think you could add "branch name" column without perfomance issue. But you need to create index or statictics for this column. As a result query optimizer understands that "branch name" same for all records and doesn't use them for plan.

But it any case your could check query plans and only after this decide

|||

This is a second good example of what I am talking about.

If your database is physically segragated by branch already then you want to make sure you know what the optimizer is doing when you add a "failsafe"

Where BranchId = 10

or alternatively

Where BranchName = 'Downtown'

to your queries.

If adding that clause causes the optimizer to include an index on BranchId as part of the execution plan then you will hurt your performance as a result of extra disk i/o and memory use. This might or might not be a concern for your infrastructure. Though it sounds like if you are physically partitioning your database by branch then you might be concerned about database performance.

Although it might seem counter intuative, it might be a good idea to experiment with:

Where BranchId + 0 = 10

or alternatively

Where BranchName + '' = 'Downtown'

This would ensure than no index on BranchId could be used in the execution plan.

|||

Dear AMERMSAMER,

The bottom line is that having more columns in the WHERE clause does not necessarily slow down performance and may, in fact, actually improve performance. It all depends on the indexes on the table and the "selectivity" of the columns in the WHERE. If there is a unique key and all of the columns in the key are "covered" by the WHERE clause, no more may be needed. SQL will try to optimize the query by using these columns where available.

effect of more column in where clause

what are the effects of having number of columns in WHERE clause, ie: if we use more columns in where clause, what will be its impact on performance.

this is important for me to design queries

having a lot of unneccessary where clauses will surely slowdown the query if there are.

the best thing that you can do is to streamline the logic into its simplest form

|||Perhaps I misunderstand the question; my normal experience is that if the additional columns contribute to filtering out unwanted records that the additional columns in the where clause usually enhance performance.|||

yeah thats right but a poorly written where clause will definetely slowdown performance.

The trick is to use the best suited functions and keywords in the where clause

here some useful link:

http://www.sql-server-performance.com/transact_sql.asp

regards,

joey

|||

Hi joeydj,

what do you mean by "the trick is to use the best suited where clause by making use of the powerful sql server functions"?

AMB

|||

oh sorry. got a grammar problem

here i have it corrected

"The trick is to use the best suited functions and keywords in the where clause"

hmmm... thats better..

example:

select ... from

where x=1 and x=2 and x=7 and x=9

maybe written as

where x in (1,2,7,9)

|||

Joey is normally better than that; please cut him some slack. I think what he means is

select ... from

where x=1 OR x=2 OR x=7 OR x=9

maybe written as

where x in (1,2,7,9)

|||

hahaha. not been here for sometime.

hmmm thanks kent.

|||

Not to mention that there is no performance benefit in choosing one of these as opposed to the other, anyway. (There is a readability benefit, and the two forms will behave differently if you carelessly tack on AND <another condition> to the query.

Steve Kass

Drew University

http://www.stevekass.com

|||

Can you give an example of the choices you have to make? Usually if you add "more columns in where clause", you change the meaning of the query (but not always), and the first goal of designing a query is for it to ask the right question...

Steve Kass

Drew University

http://www.stevekass.com

|||

One thing that people sometimes forget is that a full table scan can sometimes be better than an indexed scan. If you are ultimately going to read every block of data from disk via an index scan then you might want to forgo the index. Modern databases with good statistics can usually establish an execution plan that is "good enough" but it can still pay to understand your data.

Here is a quick example.

Let's say I have a database of people and for some reason their geneder in overwhelmingly biased in one direction. An optimizer might look at the number of unique values for gender and assume a 50/50 split in the data. It might make good sence to always use an index on gender to access the rows of the table from the optimizer's best guess. However, that might not be the case in practice.

Let's say the table is 100k records with :

90% F

10% M

To find the men an indexed lookup on gender is probably a good thing. On the other hand an indexed lookup to find women will result in more disk IO and slower performance than a full table scan (not counting your network).

Knowing this distribution ahead of time might lead someone looking for all women to do something like

Select * from employees where gender + '' = 'F'

|||

thanks for all your views.

let me give u a specific example.

my database has "branch name" field in all the tables. and we have seperate copy of database for each branch. so a particular branch user will connect to his branch, which has records only for that branch.

in this situation, there is no need to filter the records again with "branch name", but if there is no performance issue , i wish to include it in the WHERE clause to be 100% sure that all the records that the query output does have the same branch name.

|||

In your situation. I think you could add "branch name" column without perfomance issue. But you need to create index or statictics for this column. As a result query optimizer understands that "branch name" same for all records and doesn't use them for plan.

But it any case your could check query plans and only after this decide

|||

This is a second good example of what I am talking about.

If your database is physically segragated by branch already then you want to make sure you know what the optimizer is doing when you add a "failsafe"

Where BranchId = 10

or alternatively

Where BranchName = 'Downtown'

to your queries.

If adding that clause causes the optimizer to include an index on BranchId as part of the execution plan then you will hurt your performance as a result of extra disk i/o and memory use. This might or might not be a concern for your infrastructure. Though it sounds like if you are physically partitioning your database by branch then you might be concerned about database performance.

Although it might seem counter intuative, it might be a good idea to experiment with:

Where BranchId + 0 = 10

or alternatively

Where BranchName + '' = 'Downtown'

This would ensure than no index on BranchId could be used in the execution plan.

|||

Dear AMERMSAMER,

The bottom line is that having more columns in the WHERE clause does not necessarily slow down performance and may, in fact, actually improve performance. It all depends on the indexes on the table and the "selectivity" of the columns in the WHERE. If there is a unique key and all of the columns in the key are "covered" by the WHERE clause, no more may be needed. SQL will try to optimize the query by using these columns where available.