Report Delivery problem with Data Driven Subscription

I created a data driven subscription for a report that will email a copy of the report based on a parameter passed from a table. The report runs successfully as indicated in Report Manager but the email is not delivered. I checked the ReportServerService log file and a message states that the report was sent to the proper email address. There is another log, ReportServerWebApp, that shows the message "The extension Report Server Email does not have a LocalizedNameAttribute" .

I created a regular subscription and it emails the report successfully so it can't be the SMTP server.

Any ideas how I can fix this problem with the Data Driven Subscription? Thank you.

David

I did some further checking. If I send off a second job, it delivers the emailed reports. However, the problem persists with the second job.That is, Reporting Services indicates that the job ran successfully and delivered the reports, but some of the reports are getting stuck in a queue somewhere between the reporting server, the remote SMTP server and Microsoft Exchange.

Is there anyone else having problems with Reporting Services reports not getting fully delivered by a remote SMTP server? Would using a local SMTP server fix the problem?

David

|||Sorry for the delay with replying this. From the SSRS prospective your data driven subscription has been delivered to SMTP server successfully. Then SMTP server failed to deliver e-mal. Any chance that e-mail TO address is build off a data-driven subscription query results and is incorrect? Per every successfully sent e-mail SSRS will write into the service logfile ReportServerService__<Date>.log a line: Email successfully sent to {0}. Can you double check that TO addresses are correct for the cases that you thin are failing?|||

All of the reports are being sent to the same email address. Some get there successfully, others don't make it out of the queue.

David

|||

Then it really looks like a problem with SMTP server. It’s likely not related to SSRS.

-Igor

Report Delivery problem with Data Driven Subscription

I created a data driven subscription for a report that will email a copy of the report based on a parameter passed from a table. The report runs successfully as indicated in Report Manager but the email is not delivered. I checked the ReportServerService log file and a message states that the report was sent to the proper email address. There is another log, ReportServerWebApp, that shows the message "The extension Report Server Email does not have a LocalizedNameAttribute" .

I created a regular subscription and it emails the report successfully so it can't be the SMTP server.

Any ideas how I can fix this problem with the Data Driven Subscription? Thank you.

David

I did some further checking. If I send off a second job, it delivers the emailed reports. However, the problem persists with the second job.That is, Reporting Services indicates that the job ran successfully and delivered the reports, but some of the reports are getting stuck in a queue somewhere between the reporting server, the remote SMTP server and Microsoft Exchange.

Is there anyone else having problems with Reporting Services reports not getting fully delivered by a remote SMTP server? Would using a local SMTP server fix the problem?

David

|||Sorry for the delay with replying this. From the SSRS prospective your data driven subscription has been delivered to SMTP server successfully. Then SMTP server failed to deliver e-mal. Any chance that e-mail TO address is build off a data-driven subscription query results and is incorrect? Per every successfully sent e-mail SSRS will write into the service logfile ReportServerService__<Date>.log a line: Email successfully sent to {0}. Can you double check that TO addresses are correct for the cases that you thin are failing?|||

All of the reports are being sent to the same email address. Some get there successfully, others don't make it out of the queue.

David

|||

Then it really looks like a problem with SMTP server. It’s likely not related to SSRS.

-Igor

Report calling a Stored procedure taking too much time while

I have a CLR stored proceudure than runs under 2 or 3 mins when run in management studio. But when the report calls the same SP, it runs in around 30 mins and the CPU usage goes to 90 or 100% and the sqlserver service uses massive amount of memory. Previously the report worked fine, only after made a few data driven subscriptions and redeployed the reports with minor changes that it began to happen.

Please help--Iam stumped.

There are no infinite loops in the report. I checked.

I suggest you use the SQL Server Profiler to see what queries are sent to the database. Also, take a look at the ExecutionLog table in the ReportServer database to find how much time is spent in data retrieval.|||

All the time is spent in data retrievel while the same SP in management studio takes far less time ? I can't understand whats happening

|||

Also if i cancel the running jobs from Report Manager, they automatically restart after sometime? Any clues?

|||

Perhaps, debugging the stored procedure would help to find what's happening

|||

Sounds like you're running into issues with query plan; it is not uncommon to see this difference when running in Mgmt Studio vs. running in reports.

I recommend reading up on query performance:

http://msdn2.microsoft.com/en-us/library/ms187032.aspx

There are a couple of common problems people have with queries in stored procs (especially). These can often be fixed using the WITH RECOMPILE directive. This is especially true if the size of data your return from a test query is significantly different than that in the production query.

Secondly, if you reference other database objects in your query, using fully qualified names (dbo.storedProcName), you can also get some benefits.

Hope this helps,

-Lukasz

Report calling a Stored procedure taking too much time while

I have a CLR stored proceudure than runs under 2 or 3 mins when run in management studio. But when the report calls the same SP, it runs in around 30 mins and the CPU usage goes to 90 or 100% and the sqlserver service uses massive amount of memory. Previously the report worked fine, only after made a few data driven subscriptions and redeployed the reports with minor changes that it began to happen.

Please help--Iam stumped.

There are no infinite loops in the report. I checked.

I suggest you use the SQL Server Profiler to see what queries are sent to the database. Also, take a look at the ExecutionLog table in the ReportServer database to find how much time is spent in data retrieval.|||

All the time is spent in data retrievel while the same SP in management studio takes far less time ? I can't understand whats happening

|||

Also if i cancel the running jobs from Report Manager, they automatically restart after sometime? Any clues?

|||

Perhaps, debugging the stored procedure would help to find what's happening

|||

Sounds like you're running into issues with query plan; it is not uncommon to see this difference when running in Mgmt Studio vs. running in reports.

I recommend reading up on query performance:

http://msdn2.microsoft.com/en-us/library/ms187032.aspx

There are a couple of common problems people have with queries in stored procs (especially). These can often be fixed using the WITH RECOMPILE directive. This is especially true if the size of data your return from a test query is significantly different than that in the production query.

Secondly, if you reference other database objects in your query, using fully qualified names (dbo.storedProcName), you can also get some benefits.

Hope this helps,

-Lukasz

Report calling a Stored procedure taking too much time while

I have a CLR stored proceudure than runs under 2 or 3 mins when run in management studio. But when the report calls the same SP, it runs in around 30 mins and the CPU usage goes to 90 or 100% and the sqlserver service uses massive amount of memory. Previously the report worked fine, only after made a few data driven subscriptions and redeployed the reports with minor changes that it began to happen.

Please help--Iam stumped.

There are no infinite loops in the report. I checked.

I suggest you use the SQL Server Profiler to see what queries are sent to the database. Also, take a look at the ExecutionLog table in the ReportServer database to find how much time is spent in data retrieval.|||

All the time is spent in data retrievel while the same SP in management studio takes far less time ? I can't understand whats happening

|||

Also if i cancel the running jobs from Report Manager, they automatically restart after sometime? Any clues?

|||

Perhaps, debugging the stored procedure would help to find what's happening

|||

Sounds like you're running into issues with query plan; it is not uncommon to see this difference when running in Mgmt Studio vs. running in reports.

I recommend reading up on query performance:

http://msdn2.microsoft.com/en-us/library/ms187032.aspx

There are a couple of common problems people have with queries in stored procs (especially). These can often be fixed using the WITH RECOMPILE directive. This is especially true if the size of data your return from a test query is significantly different than that in the production query.

Secondly, if you reference other database objects in your query, using fully qualified names (dbo.storedProcName), you can also get some benefits.

Hope this helps,

-Lukasz

sql

report based on day

I have a report that runs daily.
I need to set the criteria for a date field to be today.
if i use the now() command then i get the value to the minute, whereas i wan
to cover the entire day.
today() doen't work in reporting services with visul studio.
what other date command can i use?This will give you the date with no time portion.
Now.ToShortDateString
Note that for a date/time field this really turns out to be today at 12 AM.
This would convert it back to a date instead of text.
CDate(Now.ToShortDateString)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"anon" <anon@.anon.com> wrote in message
news:%23Bz8zTC8FHA.2816@.tk2msftngp13.phx.gbl...
>I have a report that runs daily.
> I need to set the criteria for a date field to be today.
> if i use the now() command then i get the value to the minute, whereas i
> wan to cover the entire day.
> today() doen't work in reporting services with visul studio.
> what other date command can i use?
>
>|||How would i enter this into the criteria part of the query?
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23bxuGQD8FHA.1020@.TK2MSFTNGP15.phx.gbl...
> This will give you the date with no time portion.
> Now.ToShortDateString
> Note that for a date/time field this really turns out to be today at 12
> AM.
> This would convert it back to a date instead of text.
> CDate(Now.ToShortDateString)
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "anon" <anon@.anon.com> wrote in message
> news:%23Bz8zTC8FHA.2816@.tk2msftngp13.phx.gbl...
>>I have a report that runs daily.
>> I need to set the criteria for a date field to be today.
>> if i use the now() command then i get the value to the minute, whereas i
>> wan to cover the entire day.
>> today() doen't work in reporting services with visul studio.
>> what other date command can i use?
>>
>|||If this is the default for the report parameter then just use this for the
default.
If you haven't yet done any queries that are parameter based then backup and
do some simple reports that use query parameters like this:
select * from sometable where somevalue = @.MyParam
Note that if this is hard coded, the report should only do the current day
then use SQL syntax. You don't even need a query parameter. I am at home and
don't have my SQL reference but I am sure there is some normal SQL that can
be used (like DatePart, DateAdd etc).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"anon" <anon@.anon.com> wrote in message
news:%23Y0u5KP8FHA.3636@.TK2MSFTNGP09.phx.gbl...
> How would i enter this into the criteria part of the query?
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23bxuGQD8FHA.1020@.TK2MSFTNGP15.phx.gbl...
>> This will give you the date with no time portion.
>> Now.ToShortDateString
>> Note that for a date/time field this really turns out to be today at 12
>> AM.
>> This would convert it back to a date instead of text.
>> CDate(Now.ToShortDateString)
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "anon" <anon@.anon.com> wrote in message
>> news:%23Bz8zTC8FHA.2816@.tk2msftngp13.phx.gbl...
>>I have a report that runs daily.
>> I need to set the criteria for a date field to be today.
>> if i use the now() command then i get the value to the minute, whereas i
>> wan to cover the entire day.
>> today() doen't work in reporting services with visul studio.
>> what other date command can i use?
>>
>>
>

Report automatically runs with null params allowed. SSRS 2000

I see that other people have had a problem with this, but I never saw
a solution or a work around. Has anyone found a work around to this
issue? I want to allow nulls in all the parameters, but I don't want
the report to run by itself in Report Manager.
And does anyone know if this is fixed in SSRS 2005? We'll be
migrating to that later this year.
TIAOn Sep 17, 10:20 am, beener <rmp_c...@.hotmail.com> wrote:
> I see that other people have had a problem with this, but I never saw
> a solution or a work around. Has anyone found a work around to this
> issue? I want to allow nulls in all the parameters, but I don't want
> the report to run by itself in Report Manager.
> And does anyone know if this is fixed in SSRS 2005? We'll be
> migrating to that later this year.
> TIA
As far as I know, this is still the case in SSRS 2005. I've seen
several posts requesting this functionality. The only thing I can
think of is to have one of the parameters not have a default value.
Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Replication/Admin Help

We run a small business with a LAN of 2 pcs, a laptop pc, and a web server that runs Win2k Server with Exchange on it. My pc is used for most of the development and it also houses SQL Server (Developer) 2k. We have SQL Server (Developer) installed on the other network pc as well.

Here's the problem: We sometimes have to take my pc with us to use at different sites (with no internet connectivity at the time). When this happens, any data access on our web site is 'down' because our web data is in the sql server db on my machine. I want to replicate the db on the other pc so that we can take that machine to the different sites. I am having trouble getting this done.

Can someone give me some direction? Is there a good tutorial or a book or whatever somewhere? I am really in need of some sql server admin knowledgebase.

Thanks!!Have you considered merge replication? you could then publish to your network sites from either computer with transactional publications and have the two "master" databases have a merge publication between them. When the machine goes off site, the other will continue to provide data to the site and when the other machine returns, any update made to it can be merged with the local machine.

if this will do the trick, books online should have all you need as far as setting it up goes...|||Have you considered merge replication? you could then publish to your network sites from either computer with transactional publications and have the two "master" databases have a merge publication between them. When the machine goes off site, the other will continue to provide data to the site and when the other machine returns, any update made to it can be merged with the local machine.

if this will do the trick, books online should have all you need as far as setting it up goes...