Data Driven Subscriptions - Triggers Multiple Times On Prod Servers?
Mar 7, 2008
Hey!
DDS triggers 3 - 4 times on Report Servers with 15 mints apart..any ideas?
ADVERTISEMENT
Data-Driven Subscriptions
Jan 8, 2007Hi
I migrated my system from SQL2000 to SQL2005, now my MDX reports don't want to work with the data-driven subscriptions. The SQL reports run fine.
Any help, comments, or even suggestions will be very welcome.
Kind Regards
Carel Greaves
Data Driven Subscriptions
Feb 22, 2007Is it possible to copy an existing Data Driven Subscriptions and make some minor changes?
Elias
Why Can't I Use Data-driven Subscriptions?
Jan 8, 2007I am using Reporting Services 2005 locally for testing purposes and also on a production server. Locally, I can create data-driven subscriptions, but not on the server. The only difference I can think of between my local test environment and the production server is the versions of SQL server database. Locally, I am using SQL server 2005 but on the server I am using SQL server 2000 (but with Reporting Services 2005). Can the version difference be the reason for not being able to use data-driven subscriptions?
Note: I have followed various guidelines and walkthroughs that describe how a data-driven subscription is created, i.e. stored credentials on the report server and such should not be the problem.
Operating systems: locally; Windows XP. Server; Windows Server 2003.
Thanks!
/Stefan
Don't See The Data Driven Subscriptions Button
May 22, 2007hi all,
i built my report with BIDS, configured my datasource and report. then i deployed it. on http://localhost/reports, I to go to the Report, but under "subscriptions", I don't see the "create data driven subscriptions" button.
however, under properties/datasources of my report, I clearly configured the
"Credentials stored securely in the report server"-setting.
Did I forgot something??????
Thx
Help - Standard Vs. Data-Driven Subscriptions
Nov 29, 2005Hello,
View 13 Replies View RelatedEnabling Data-Driven Subscriptions
Oct 1, 2007I am trying to go through the MS tutorials on creating Data-Driven Subscriptions.
http://technet.microsoft.com/en-us/library/ms169673.aspx
I have followed the steps multiple times but when I go to add the Data-Driven Subscription, the option is disabled. The report's datasource is setup to use stored credentials and the server Mail is setup. Is there another prerequisite that I'm missing?
Report With Subreports And Data-Driven Subscriptions?
Feb 7, 2008I have problem with using a data-driven subscription on a report with subreports. It works fine when I remove the subreport and generates error even when I add a subreport which only consists of one textbox.
I just was curious to know if using data-driven subscription on a report with subreports is supposed to work?
Thanks,
How To Migrate Data-driven Subscriptions To Different Server
Nov 30, 2007hi guys,
I have created data-driven subscriptions on top of the existing reports on my development server.
I have same reports on QA's report server but without the DD subscriptions.
Is there an easy way to migrate data-driven subscriptions to QA report server or the only way to do is create each subscription manually?
Thankskev
SQL 2012 :: Data Driven Subscriptions - Any Way To Send All Emails At Once?
Mar 18, 2015We have a rather large query that takes time to run between each data-driven instance. Is there a way to only send out the first email after the last subscription has finished processing? Any advanced input on data-driven subscriptions .
View 0 Replies View RelatedHow To Deal With User!UserID In Data-Driven Subscriptions
Jul 27, 2006First a bit of background.
We use the Windows-logon to authenticate en identify the user. With this logon we check within most of our reports what a user is allowed to see.
Depending his level or geographical location he is allowed to run reports on one to all sites. He can selects the sites from a Multiple Select box. This works fine.
Now, we want to use data-driven subscriptions, I understand why we can't use this parameter, but how do you people deal with this kind of configurations.
I would prefer not to 'double' the reports in a normal and a subsciption version .....
My Data-Driven Subscriptions(DDS) Don't Want To Work Although I Can View My Reports
Nov 28, 2006
Hi
I have been asked to move our entire system over from SQL2000 to SQL2005, i eventually got our database and Cubes over to 2005 and converted all my reports to 2005 and deployed them successfully on RS2005
All the subscriptions have been made and they seem to work when i click to view them. Although when i create a DDS for all my reports to run then they fail. Now i've got over 5000 reports to build and i'm definately not going to create each report manually. My SQL Reports huild successfully, it just seems to be the MDX Reports with a problem, i;ve checked my stored procs and they seem fine, i've even seen the [Clients].&[All.Clients] difference from SQL2005 ---->&<------ that is not in SQL2000.
If someone has, or has had the same problem before would you please help me. Any advice or suggestions would be more than welcome.
Kind Regards
Eagle
Reporting Services :: Table Data Types For Data Driven Subscriptions
Jun 11, 2015I am trying to find a reference for a client that lists the fields available to be substituted into a data driven subscription from the query, along with the expected data types.  For example, the field on whether or not to include a link to the report seems to be expecting a bit data type.I have searched and can't seem to find anything.  I guess I could walk through the interface and try different data types, but if  a list exists, that would be better.Â
View 4 Replies View RelatedReporting Services :: How To Read Data (email) From Report In Data Driven SSRS Subscriptions
Jul 28, 2015get the data from report to create data driven SSRS Subscriptions,
write query :
Running Report:
Data Driven Subscriptions - E-mail Distribution (SSRS 2005)
Jan 30, 2008Hello folks,
Does anybody have experience with e-mail distribution using Data Driven Subscriptions?
I want to build the following:
I have a table with names and e-mail addresses of people in my data warehouse. This table changes over time, more people will be added and data (for example e-mail addresses) can change.
I made 1 report that I want to mail to all the people in the table. This report contains 1 parameter; the e-mail address of a person.
So basically it€™s quite simple: I want to e-mail all the people in the table a personalized (by the parameter) report each month.
Can anybody tell me how I can achieve this with SSRS 2005(SSEE)? I know how to make a data driven subscription but how can I make one pending on the data in a table?
Any help would be appreciated. I will write a blog about this when I've done it so your input will not get lost.
Jorg
Reporting Services :: SSRS Data Driven Subscriptions In SharePoint
Sep 30, 2015I am looking for a good tutorial on how to set up Data Driven subscriptions through the SharePoint 2012 version of SSRS. I am needing to set up a Data Driven subscription that uses one report, but sends out the specific portion of the report to the appropriate recipients. I have used the information located at URL.... but it doesn't seem to provide all the steps needed to schedule the reports needed. It focuses around setting up the reports to go to shared folder locations. I need something around setting it up to go to email recipients.Â
View 5 Replies View RelatedAny Way To Control Execution Processing (parallel Or Serially) When Using Data Driven Subscriptions?
Apr 12, 2007
I've got an SSRS report that is set up using a data-driven subscription to supply input parameters to the stored procedure that is called to generate the report results.
I was wondering if there is any way to specify the execution processing method (running the reports in parallel or serially). The subscription that we have set up appears to be running all of the reports in parallel which is causing massive load on our servers.
Thanks.
Reporting Services :: Using Multi-Valued Parameters With Data-Driven Subscriptions
Jun 27, 2009I have researched this question and so far have found very different opinions as to how or if it is possible.I have created 2 Data-driven subscriptions in SSRS. 1 for our sales persons and another for our managers reports. For the salespersons report, the solution works brilliantly as expected and I can appreciated the increased simplicitly for having 1 subscription to drive reports to a sales staff of over 100 folks.One the other hand, I can't seem to grasp the significance of this type of solution for the Sales managers when they have multiple salespersons that report to them.
Apparently, SSRS 2008 has provided no practical solution for this scenario that would be easily implemented in a Data driven subscription.Since I can't find a way to pass in muliple integer values that represent each of the salesreps for a single manager, I am stuck with potentially sending multiple reports for each of the Sales Reps residing under a single manager. Not very elegant or useful as I'd hoped for using Data driven subscriptions. I have even changed the parameter datatype to string and used something like:
paramSalesPeople = substring((SELECT ( ', ' + CAST(territoryid AS VARCHAR(2000)))
FROM Reports..SalesForce t2
WHERE t1.SalesManagerID = t2.SalesManagerID
ORDER BY SalesManagerID
FOR XML PATH( '' )), 3, 1000 )
To create a comma-delimited list of values for the parameters in effort of generating 1 (ONE) record per Manager. But the Data Driven Subscription fails miserably.So the question, Is it or is it not possible to have multiple values passed as a single parameter to a data driven subscription to consolidate the number of required reports into one. If not, it would seem that it should be possible since it can be done from the reports parameters drop-down menu.I have read someones recommendation to script the multi-valued parameter which seems to defeat the intent of the term "Data Driven' Subscription if I have to hard-code this logic into a script.
DTS-data Driven Task, Repeat N Times If Failed
Aug 25, 2006Hi,
I have one data driven task set up in my DTS package. for some reason the connection to data source is not available when my package is scheduled to run,which fails the package.
is there a way, I can set the task to try to execute again after a couple of minutes or 5 mins, maybe a predefined number of times before it fails the package?
Thanks & Regards
Naseem
Enterprise Editon Of Sql Reporting Services Not Displaying Data-driven Subscriptions Option.
Mar 27, 2008I am running the following ENTERPRISE version of SQL Server, I am admin on the box and do not have the New data-driven Subscription showing up on the Subscription tab.
Does anyone have any ideas why?
Edition Product Level Product Version
-----------------------------------------------------------------------
Enterprise Edition SP4 8.00.2039
Reporting Services :: Identify Failed Subscriptions In Data Driven Subscription Batch
Apr 20, 2015I have a single data driven subscription that is a batch of 250 subscriptions running on SQL Server 2008 R2. All the parameter values for the report are sourced from a database table. During the execution of the data driven subscription 5 of those subscriptions failed due to timeout error.
Final Status as shown on Subscription screen "Done: 250 processed of 250 total; 5 errors."
The SSRS log file has the subscription GUID which is a single record in the reporting server database tables. How can i identify the individual failed subscriptions? Is there an easy way or a work around that i should implement to identify the failed subscriptions?
Creating Triggers Among Multiple Database Servers
Nov 14, 2000Hello,
I am trying to create a trigger to update a table on a different database server. (Both databases are SQL server 7.0) Does anyone know the syntax of how to implement this?
Any help is appreciated!!
Thanks,
Lisa
Data Driven Sub Calling Proc With Multiple Parameters
Aug 31, 2007I have a couple of questions that I hope someone might be able to provide some direction on.
1. Why can't you call a proc when the proc uses temp tables? I wrote a simple proc (select * from table) and the data subscription works perfect. I then take that same query and put it into a temp table and the data set can't be validated by the subscription, Why?
2. Why can't you pass a simple execute statement in a data subscription? (execute dbo.RptTakeTheLeadFLSDinnerCompetition @FiscalQuarter ='2007-Q3', @StoreRegionID = '9231', @StoreType = 'Full-Line Store') works perfect in SQL but once you try and validate in the data subscription is fails.
3. Another question, why if I dumb down the proc and the number of parameters does it work? Are there limitations on the number of parameters you can pass into a proc from a data driven subscription?
Basically, I want to declare a few variables dynamically and pass them into a execute statement that calls the proc. Which in turn runs the report.
Any ideas on where I seem to be going wrong?
SQL 2012 :: Data Driven Report Looping Through Parameter For Multiple Reports
Sep 15, 2015I'm trying to create a report that will be data-driven and produce multiple reports based on a parameter. For example, 5 reports go out today based on invoice numbers from yesterday. Each invoice can have multiple trasnsaction lines and each line contains the invoice number. What I have so far is only taking the first invoice number (let's say it has 10 transactions) and sending me the same report 10 times and stopping.
I get nothing for the remaining 4 invoices/reports. Here is what I have. To me this should enter the distinct invoice no's from yesterday into #temp, while a invoice no exiists begin query with one invoice selected from #temp then delete that invoice no and select another one and repeat till no more invoice no's. But it's only going through the one invoice no.
select distinct InvoiceNo
into #temp
from table
where Invoicedate between getdate()-1 and getdate()
declare @InvoiceNo VARCHAR(25)
[Code] ....
Binary Data On Multiple Servers
Feb 18, 2007I'm using EncryptByKey to encrypt data in my SS2005 database. Since our server is really slow to access from home to work on, I used the Database Publishing Wizard and installed the db to work on at home. Then I created the certificate and symmetric key in my home db.
When I pull info using the DecryptByKey on our database at work on Windows 2003 Server, no problem, the data is decrypted. However, the same data does not decrypt at home on my Windows XP computer. I'm using TripleDes on both machines for the symmetric key (AES won't work on XP).
--To create my cert and key:
USE My_DB;
CREATE CERTIFICATE MyCert
ENCRYPTION BY PASSWORD = 'some password'
WITH SUBJECT = My Data',
START_DATE = '01/01/2007',
EXPIRY_DATE = '01/01/2099';
GO
CREATE SYMMETRIC KEY MyKey WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE MyCert;
GO
To encrypt:
OPEN SYMMETRIC KEY MyKey
DECRYPTION BY CERTIFICATE MyCert
WITH PASSWORD = 'same password as above';
Insert my record, use scope_identity to return primary key into @CustomerID.
INSERT INTO [Customers] (EncryptByKey(Key_GUID('MyKey'), @DataToEncrypt, 1, CONVERT( varbinary, @CustomerID)))
CLOSE SYMMETRIC KEY MyKey
To decrypt:
SELECT CONVERT(varchar(3925), DecryptByKey(EncryptedField, 1, CONVERT( varbinary, @CustomerID))) as PlainTextData
FROM Customers
WHERE (CustomerID= @CustomerID)
Everything works fine when I run the decrypt query on the database on our work server. But I'm not getting decrypted data at home. Is the symmetric key or certificate machine specific? If so, that will cause a huge problem when we deploy to a production server.
Thanks in advance for your help!
Retrieving Data From Multiple SQL Servers In Parallel
Nov 9, 2001I have multiple SQL 2000 servers that hold data which I retrieve using a single SQL stored procedure. Unfortunately, I don't know how to retrieve this data asychronously (in parallel).
For example, I retrieve data from two linked servers using the following:
select * into #temp from openquery(SomeLinkedServer,'exec BigQueryHere')
select * into #temp2 from openquery(OtherLinkedServer,'exec BigQueryThere')
.
.
lots of manipulation of the temporary tables to get what I want, etc.
.
.
The problem I have with this is that there is no reason why the first query should have to finish before the second query begins (serially) because these are on separate servers. Is there a way to execute these so that they run at the same time?
Bill
Create View With Data From Multiple Servers
Jul 23, 2005Hi everyone,I have 5 servers, all with identical databases just different data. Ihave a rather lengthy SQL statement (in a View) to hit one database andpull-in certain data, but I'd like to somehow run this same SQLstatement within the view but hit all 5 servers so we don't have 5different versions of this data to mess with.I'm not opposed to creating an update query in a stored procedure tohit all 5 databases and update a table or even do this within a DTS,but I'd prefer to keep it as simple as possible and as dynamic so theusers can simply run the view and get live data anytime based on all 5tables.Is this possible ???Thanks,rlangly
View 2 Replies View RelatedMultiple Triggers On A Table Or Encapsulated Triggers
May 12, 2008This isn€™t an problem as such, it€™s more of a debate.
If a table needs a number of update triggers which do differing tasks, should these triggers be separated out or encapsulated into one all encompassing trigger. Speaking in terms of performance, it doesn€™t make much of an improvement doing either depending upon the tasks performed. I was wondering in terms of maintenance and best practice etc. My view is that if the triggers do totally differing tasks they should be a trigger each on their own.
www.handleysonline.com
Data Synchronization Accross Multiple Servers Running Replication
Apr 19, 2007Hello,
We have an environment with 7 servers that are running replication with one another and I'm wondering if there are any tools or experiences that any of you might have that may assist in the auditing of these servers. The data should be in synch accross the boards for all tables, but sometimes problems can arise such as replication not being set up properly, stored procedure's being out of synch, or data gliches etc.
In dealing with these issues we have an in-house written program which analises each table on each server and takes a snapshot and does column by column compare. We also have another program that will synch the data up (basically a delete/insert statement on the publisher). This process can take up to 3 weeks for our quarterly update of every table. I'm wondering if anybody has used any tools such as in SSIS or a third party tool and has done or is doing something similar to what we are doing now. If so, are there any tips you wouldn't mind sharing on how our process might be sped up?
Thanks,
Phil
Reporting Services :: Report Builder 3 Using Data Sources From Multiple Servers?
Sep 5, 2013I have a report designed in RB3 that uses a data source from a SQL database that is on the report server. I want to add data to the report from an access database an a network drive.
I can add the second data source and create a data set to add data to the report. The dataset query returns data from the Access database but when I run the report I get the following error.
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'Feasibility'. (rsError OpeningConnection)
Also when I test the connection to the Access data base I get error ERROR (IM002) (Microsoft)(ODBC Driver Manger) Data source name not found and no default driver specified.
I noticed that Report Builder is connecting to the report server. If I disconnect from the report server I can a can connect to the Access data base but not the SQL database.
How can I get the report to run against both data sources?
Triggers Across Servers
Aug 6, 2002Hi All,
Can I write a trigger on one server and if I reference the server.db.owner.table can it work on a different server?
Thanks in advance,
Brad
Querying Multiple Tables Multiple Times
May 31, 2007I am trying to query the Topics in my discussion forum...The Topic contains a "last_poster_id" and a "author_id" I need the username and userid for both "last_poster_id" and "author_id" in the table "aspnet_Users"How do I do this?I would guess I need to use sub select statements. Can someone help me?
View 12 Replies View RelatedTriggers And Linked Servers
Jun 17, 2004I've been working on this for a couple of days now, trying different scenarious.
Problem:
I created a 'FOR INSERT' trigger on server1.dbs1.owner.table1 to collect information and insert to a linked server server2.dbs2.owner.table2. When I run with the trigger active, SQL Analyzer just hangs in there "Executing Query Batch ..." indefinately. When I don't create the trigger, but run all parts manually from SQL Analyzer, it works fine.
What did I try:
1. create a 'FOR INSERT' trigger on server1.dbs1.owner.table1 to insert into server1.dbs1.owner.table2. This worked fine.
2. create a stored proc to execute within the trigger on server1.dbs1.owner.table1 to insert to server2.dbs2.owner.table2. This just hangs in there "Executing Query Batch ..." indefinately.
What now?
I have a suspicion that something is not working correctly with the triggers and the linked servers. Has anyone encountered a similar problem and what did you do to overcome this? I greatly appreciate all responses and suggestions. Thanks all.