How to simpify the Deployment process of a SQL Server Report

Dear all,
I have 5 SQL Servers and totally around 100 databases (1 database for 1
client).
Whenever I finish a Report development on my development server, I will need
to repeat the same task on these 100 databases 100 times ?
Although I can copy the Report Definition File from the development server
to these 100 databases' folders, I still need to do the following tasks :
(1)Create 100 new Visual Studio projects
(2)Enter "Start Item" and "Target Server URL" 100 times
(3)Create 100 new Shared Data Sources
(4)Build and Deploy Visual Studio projects 100 times.
Are there any better or more efficient ways to deploy a new Report in a
multiple servers or multiple databases environment ?
Please help me.
Thanks a lot."cpchan" wrote:
> Dear all,
>
> I have 5 SQL Servers and totally around 100 databases (1 database for 1
> client).
>
> Whenever I finish a Report development on my development server, I will need
> to repeat the same task on these 100 databases 100 times ?
> Although I can copy the Report Definition File from the development server
> to these 100 databases' folders, I still need to do the following tasks :
>
> (1)Create 100 new Visual Studio projects
> (2)Enter "Start Item" and "Target Server URL" 100 times
> (3)Create 100 new Shared Data Sources
> (4)Build and Deploy Visual Studio projects 100 times.
>
> Are there any better or more efficient ways to deploy a new Report in a
> multiple servers or multiple databases environment ?
>
> Please help me.
> Thanks a lot.
>
Hi
I am not a reporting services expert, you may find more help in the
reporting services newsgroup microsoft.public.sqlserver.reportingsvcs
You can probably do what you require using multiple configurations for a
single project that deploys onto the different servers:
http://msdn2.microsoft.com/en-us/library/aa179464.aspx
http://msdn2.microsoft.com/en-us/library/aa237242(SQL.80).aspx
You could probably use the command prompt utilities and write a
parameterised script that will deploy to a server
John|||Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:679E4454-427D-4734-B5BB-7817C3C11BCB@.microsoft.com...
> "cpchan" wrote:
> > Dear all,
> >
> >
> > I have 5 SQL Servers and totally around 100 databases (1 database for 1
> > client).
> >
> >
> >
> > Whenever I finish a Report development on my development server, I will
need
> > to repeat the same task on these 100 databases 100 times ?
> > Although I can copy the Report Definition File from the development
server
> > to these 100 databases' folders, I still need to do the following tasks
:
> >
> >
> > (1)Create 100 new Visual Studio projects
> > (2)Enter "Start Item" and "Target Server URL" 100 times
> > (3)Create 100 new Shared Data Sources
> > (4)Build and Deploy Visual Studio projects 100 times.
> >
> >
> >
> > Are there any better or more efficient ways to deploy a new Report in a
> > multiple servers or multiple databases environment ?
> >
> >
> >
> > Please help me.
> > Thanks a lot.
> >
> Hi
> I am not a reporting services expert, you may find more help in the
> reporting services newsgroup microsoft.public.sqlserver.reportingsvcs
> You can probably do what you require using multiple configurations for a
> single project that deploys onto the different servers:
> http://msdn2.microsoft.com/en-us/library/aa179464.aspx
> http://msdn2.microsoft.com/en-us/library/aa237242(SQL.80).aspx
> You could probably use the command prompt utilities and write a
> parameterised script that will deploy to a server
> John

How to set up reporting services to have up-to-date cube data whenever report opened

Hi, at work I have set up reporting services with analysis services
providing cube data. I have created a cube and deployed it to reporting
services. What I want to know is how do i guarantee that when an executive
opens up the report, they will get the latest data from the SQL database? I
have installed/created everything with defaults, including the reports and
their deployment via visual studio to reporting services.
Here I am guessing that when a report is opened that reporting services
connects to analysis services (which interacts with sql server whatever way)
to get the latest update of data within the report? I am just a little
concerned because I had a previous report set up and no matter how i tried
to get it to update data, it just couldnt. I had to redeploy the solution
from VS to reporting services.
Any help most appreciated!
cheers
murrayThe default deployment will always get you the latest data... However if you
make a change to the report onthe reporting server, that change will NOT be
overridden by redeploying...
Perhaps you set the cache time on the report in Report Manager... That means
that data will be re-used without re-querying for 60 minutes... Redeploying
the report will NOT change this and many other settings... You would have to
either delete and redeploy or change the setting in Report Manager..
However, for the standard default deploy of a new report, no caching is used..
--
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.
"j.murray" wrote:
> Hi, at work I have set up reporting services with analysis services
> providing cube data. I have created a cube and deployed it to reporting
> services. What I want to know is how do i guarantee that when an executive
> opens up the report, they will get the latest data from the SQL database? I
> have installed/created everything with defaults, including the reports and
> their deployment via visual studio to reporting services.
> Here I am guessing that when a report is opened that reporting services
> connects to analysis services (which interacts with sql server whatever way)
> to get the latest update of data within the report? I am just a little
> concerned because I had a previous report set up and no matter how i tried
> to get it to update data, it just couldnt. I had to redeploy the solution
> from VS to reporting services.
> Any help most appreciated!
> cheers
> murray
>
>sql

How to send mails from sql server?

Hello,

Problem statement ::

1) I have a birthday database which stores name s and date of birth of the persons.

2) Whenever sysdate and date of birth are equal then i want the sqls erver to send a mail/alert to my mail id saying that to day is birthday of so and so person.

3) I ma working on YUKON.

4) Please let me know how to accomplish this task?

Thanks&Regards,

Sreekanth Ammisetty

ask_sreekanth@.hotmail.com

Hi Sreekanth.

You want to make use of Database Mail for this type of thing. See the following topic in SQL Server 2005 Books Online for more information, and repost any specific questions you may have after that:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/14cbf88f-d9d1-41a5-994e-532e2973ac9e.htm

|||

Hi Chad,

The link that you have provided is not working, however I ahve got solution for this to some extent. I am using "EXEC msdb.dbo.sp_send_dbmail ....." command to send the mails, But the problem I am getting is that SMTP service is not there in my desktop, I tried by installing the same, But still i could able to achieve the desired bahaviour, bcoz it is giving error related to SMTP.

The following is the error I am getting "A severe error occurred on the current command. The results, if any, should be discarded."

Please let me know the solution for this.

|||May try XPSMTP from http://www.sqldev.net/xp/xpsmtp.htm here.

how to send mail using trigger

Hello
I have created a update grigger. I want to send a mail whenever is trigger
is fired. I tried using xp_sendmail, but it required mapi account to be
configured in server. I want to avoid this situation. Can I use cdo code in
trigger?
regards
Ashish
"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
I believe so. But I wouldn't recommend it.
It'll really mess up performance.
You're better off having the trigger enter a row into a table and then
having a scheduled task go through once every X minutes sending emails.

> regards
> Ashish
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Well for SQL Server 2000 I agree, but as the Mail Service is now
enabled with SQL Server Service Broker in SQL Server 2005 you *could*
consider doing this within a trigger.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Try xp_smtp_sendmail.
TheSQLGuru
President
Indicium Resources, Inc.
"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
> regards
> Ashish
>
|||DOnt use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Hello All
I tried following solution, it is working for me. I like to know pros/cons
on this if any.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20649
Ashish
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegr oups.com...
DOnt use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||I already pointed out the cons, the same applies for this solution as
it might stale your application and the transaction with it. I
addition, the process might cancel the transaction if you execute it
within a trigger, depending on the error level coming back, The con
against a solution with a mail table and a scheduled delivery is that
it would not be send immediately, but you could implement something
like a retry whereas you cannot do this in the above procedure used
within a trigger. The best thing is to use the Service broker enabled
mail service if you are using SQL Server 2005. If you are using SQL
Server 2000 you should use a mail table which queues up mails and
sends them periodically.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Sorry, I didn't see the user state he was using SQL Server 2005. Oh, wait.
.. . he didn't! :-)
TheSQLGuru
President
Indicium Resources, Inc.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegr oups.com...
DOnt use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de

how to send mail using trigger

Hello
I have created a update grigger. I want to send a mail whenever is trigger
is fired. I tried using xp_sendmail, but it required mapi account to be
configured in server. I want to avoid this situation. Can I use cdo code in
trigger?
regards
Ashish"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
I believe so. But I wouldn't recommend it.
It'll really mess up performance.
You're better off having the trigger enter a row into a table and then
having a scheduled task go through once every X minutes sending emails.

> regards
> Ashish
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Well for SQL Server 2000 I agree, but as the Mail Service is now
enabled with SQL Server Service Broker in SQL Server 2005 you *could*
consider doing this within a trigger.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Try xp_smtp_sendmail.
TheSQLGuru
President
Indicium Resources, Inc.
"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
> regards
> Ashish
>|||DOn=B4t use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Hello All
I tried following solution, it is working for me. I like to know pros/cons
on this if any.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20649
Ashish
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegroups.com...
DOnt use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||I already pointed out the cons, the same applies for this solution as
it might stale your application and the transaction with it. I
addition, the process might cancel the transaction if you execute it
within a trigger, depending on the error level coming back, The con
against a solution with a mail table and a scheduled delivery is that
it would not be send immediately, but you could implement something
like a retry whereas you cannot do this in the above procedure used
within a trigger. The best thing is to use the Service broker enabled
mail service if you are using SQL Server 2005. If you are using SQL
Server 2000 you should use a mail table which queues up mails and
sends them periodically.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Sorry, I didn't see the user state he was using SQL Server 2005. Oh, wait.
. . he didn't! :-)
TheSQLGuru
President
Indicium Resources, Inc.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegroups.com...
DOnt use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--

how to send mail using trigger

Hello
I have created a update grigger. I want to send a mail whenever is trigger
is fired. I tried using xp_sendmail, but it required mapi account to be
configured in server. I want to avoid this situation. Can I use cdo code in
trigger?
regards
Ashish"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
I believe so. But I wouldn't recommend it.
It'll really mess up performance.
You're better off having the trigger enter a row into a table and then
having a scheduled task go through once every X minutes sending emails.
> regards
> Ashish
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Well for SQL Server 2000 I agree, but as the Mail Service is now
enabled with SQL Server Service Broker in SQL Server 2005 you *could*
consider doing this within a trigger.
Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Try xp_smtp_sendmail.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
> regards
> Ashish
>|||DOn=B4t use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Hello All
I tried following solution, it is working for me. I like to know pros/cons
on this if any.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20649
Ashish
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegroups.com...
DOn´t use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||I already pointed out the cons, the same applies for this solution as
it might stale your application and the transaction with it. I
addition, the process might cancel the transaction if you execute it
within a trigger, depending on the error level coming back, The con
against a solution with a mail table and a scheduled delivery is that
it would not be send immediately, but you could implement something
like a retry whereas you cannot do this in the above procedure used
within a trigger. The best thing is to use the Service broker enabled
mail service if you are using SQL Server 2005. If you are using SQL
Server 2000 you should use a mail table which queues up mails and
sends them periodically.
Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Sorry, I didn't see the user state he was using SQL Server 2005. Oh, wait.
. . he didn't! :-)
--
TheSQLGuru
President
Indicium Resources, Inc.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegroups.com...
DOn´t use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--