Creating External Record Of All Subscriptions And Report Names

Jan 9, 2008

hi

i have been tasked with creating a record of all the reports ( 200+ ) within each folder ( bout 20 ) and all the subscriptions (including parameters) for our SSRS i was wondering what the best way of doing this was (SQL query?)

i have tried creating a site map with visio but im struggling to get the subscriptions details

anyone done this before (or have any ideas) i would be very grateful

thanks

Jake

View 5 Replies


ADVERTISEMENT

Losing Record After Synchronizing Subscriptions

Feb 18, 2007

Hi


We got a server running SQL server 2000 SP4 with a database that€™s being replicated
There are 17 subscribers running MSDE SP4 using merge replication. Replication is started manualy

Initially we tested this with two subscriptions an everything went well, but now, since 3 months, we are facing a weird problem while sync'ing. We have massive data loss on records that where inserted at the subscribers. Records seem to disappear, but only record that have a foreign key constraint. What I mean is that for example a record is inserted at the table that holds our client records with primary key €˜ClientID€™ and then a record is inserted in a table with actions for that client with a foreign key €˜ClientID€™ referring to the client table. After sync€™ing that client record is inserted correctly in database on the publisher but the records in the table with actions are gone.

As far as I know the tables are correctly formed with identity set not for replication and so on.
Shortly, I can€™t find any problem, a specially when it doesn€™t happen always.


If anyones has faced this and got a solution, please let me know.
Thanks.

Raf


View 3 Replies View Related

Creating Subscriptions In Reporting Services

Mar 26, 2008



Hi

I am trying to create new subscription on on of my reports. but some reason when I right click on the subscription the create new subscription is disabled. I have same permissions on the other reports also but it is anabled. Is there any thing do I need to do to create new subscription. The data source on that report is shared.Please some body let me know. Thank you!

View 4 Replies View Related

Monitor Record Changes After External Process Is Run

May 23, 2008

Greetings all,

I have a process that I run that modifies or adds records. I need to monitor changes to the original records without using triggers.

I have working code but was wondering if someone had an easier way.

My process is as follows.

1. Clone the original table and records:



Code Snippet

CREATE PROCEDURE usp_CreateOriginalTable
@TableName as varchar(50)
AS
--Clone the table
DECLARE @NewTableClone as varchar(60)
SET @NewTableClone = 'SELECT * INTO ' + @TableName + '_ORIGINAL ' + 'FROM ' + @TableName
EXEC(@NewTableClone)

GO






2. Now run my process.
3. Now compare the two tables (Table1 and Table1_ORIGINAL):




Code Snippet
CREATE PROCEDURE usp_GetModifiedorAddedRecords
@TableName varchar(50)
AS
/*The initial code create the base for the d-sql query*/
DECLARE @myQuery as varchar(8000)
SET @myQuery = 'SELECT t1.* FROM '+ @TableName + ' t1 ' +
'LEFT JOIN ' + @TableName + '_ORIGINAL t2 ON '

/*The cursor loops through all fields in the table*/
DECLARE myCursor CURSOR
FOR

SELECT sc.Name
FROM syscolumns sc
JOIN sysobjects so
ON so.ID = sc.ID
WHERE so.Name = @TableName

OPEN myCursor
DECLARE @fieldName varchar(50)
FETCH NEXT FROM myCursor INTO @fieldName
WHILE @@FETCH_STATUS = 0
BEGIN

/*In order to check modified records, all fields must be joined with clone*/
SET @myQuery = @myQuery + 't1.' + @fieldName + ' = t2.' + @fieldName + ' AND '

FETCH NEXT FROM myCursor INTO @fieldName
END
CLOSE myCursor
DEALLOCATE myCursor

SET @myQuery = @myQuery + 'WHERE t2.' + @fieldName + ' IS NULL'
SET @myQuery = REPLACE(@myQuery, 'AND WHERE', 'WHERE')

--SELECT @myQuery
EXEC(@myQuery)
GO




Yes I know it uses a cursor but I can't find an easier way. Does anyone know of a better way of doing this or is this fine?

View 5 Replies View Related

Creating Subscriptions To SSRS That Connects To SSAS

Oct 30, 2007

Hi,

I would like to ask how can I create a subscription for a report that connects to Analysis Services?

I was able to create a subscription but the report's datasource is SQL Server 2005 using the built-in "sa" credential. But when I am connected to SSAS the option to use the SQL login is greyed out.

Please help!

Thanks

View 1 Replies View Related

Creating A View Of An External Source

Nov 7, 2007



Hello,

I want to create a view in sql 2000 that will pull in data from a ms access database. What is the correct syntax for the select statement?
Thanks

View 4 Replies View Related

Reporting Services :: Deploying Report With Subscriptions With New Report Having Same Name

Oct 30, 2015

Here is the scenario:

1. have report called report_a.rdl with subscriptions attached to it
2. developed a new report called report_b.rdl with the same parameter list as report_a.rdl
3. i would like to replace report_a.rdl with report_b.rdl without losing the subscriptions.

I think this is the workflow:

1. rename report_a.rdl to report_a_old.rdl in BIDS
2. rename report_b.rdl to report_a.rdl in BIDs
3. deploy report_a.rdl to reporting server

Will that work even though the ReportID GUID will change?

View 2 Replies View Related

Code For Creating A Table In SQL From External Source

Nov 18, 2005

I'm looking to create a table in SQL using data from the as/400. I need some code that extracts it from as/400 using odbc and I need it to loop and create that table every minute.
The file its retrieving is a live file but not very large.. I need to basically poll the data. Does anyone have code for something like this..
thanks

View 8 Replies View Related

Severe Error Occurring When Creating Assembly With External Access Permission

Jul 11, 2006

I had created a CLR function in my db and was able to execute it successfully a couple of months ago. But when I tried to execute it today it was throwing errors saying there was something wrong with the permissions on the assembly. So I decided to drop everything and recreate it except I can not longer create the assembly with EXTERNAL ACCESS permissions. Whenever I try to create the assembly I get the followng error:

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

I also tried to create the assembly with Unsafe permissions and got the same error. Does anyone know why this error would be occurring now? I tried creating the same assembly on a different SQL 2005 server and it creates successfully and can be executed successfully. Any help would be greatly appreciated!!



Thanks!

GN

View 3 Replies View Related

Report Subscriptions

Apr 4, 2007

Hello,



I have a dril lthrough report which takes a long time (5-7 minutes) to fetch the results. The reason is that the data it returns is huge as it fetches sales data till the lowest level.

when the users later on trz to export it to excel it takes again a lot of time and eventuallz the szstem freezes. I think the volume of export might be in the range of 14/15 MB.



Is there a way to reduce first the execution speed of the report and then secondly reduce the time to export the report?



Or a way where the report is executed once a day and everyone shares the already published report?



regards

Josh







View 3 Replies View Related

Report Subscriptions Problem

Feb 12, 2008

Hi,

I'm trying to setup a subscription on a SSRS report and get the following error message:

An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help EXECUTE permission denied on object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.

I've followed the advice given in this post but still get the same result.

The SQLAgent service is running under a domain account and the Reporting Services service running under a local account. Both these accounts are assigned to the RSExec Role in the master, msdb, ReportServer and ReportServerTempDB databases. This role has execute permissions set as per the above link.

I'm now at a loss as to what else the problem could be?

All ideas welcomed!

Steve.

View 1 Replies View Related

Re-running Subscriptions For Any Report

Mar 28, 2008



Hi,

I have created a subscription for a report and this subscription is scheduled to run at specific time everyday. My subscription failed because of some stored proc issue. Is there a way to re-run the subscription. I did not know how to do this so I created a temporary subscription just to run it again. Please help!

View 7 Replies View Related

Specifying Locale For Report Subscriptions

Nov 26, 2007


Is there a way to specify the locale for a report subscription?

I'm using the CreateSubsciption method through the web service and need to address the "User!Language" item within my reports. Currently, it's always en-US. If I manually access the report manager and set up a subscription in there, the locale is correctly taken from my browser settings. I would like to tap into this for subscriptions.

Thank You!!!

View 4 Replies View Related

Report Server Subscriptions - Job Owner

Jul 23, 2005

Hi,I am trying to setup a subscription but I keep running into problems.I setup the subscription fine, I see that it creates a job in SQLServer. It shows a specific domain account as the owner and I can'tfigure out why. The problem is that this account is in a differentdomain and it's having problems authenticating when it runs. I did usethis account for the services and whatnot when I originally set it up,but I've changed the account that SQL Server and SQL Server Agent rununder, but it still uses this account as the owner. The only otherthing I can think of is that the database owner ofSystemCenterReporting is still this account. Is this why it's listingthis account as the owner of the job? Where else could it be?Thanks,Bill

View 2 Replies View Related

Encrypt Report Attachments (through Subscriptions) -- Possible?

Aug 21, 2007

We currently email out reports as either PDF or Excel attachments using Data Driven subscriptions in SSRS 2005. I've been asked to be able to encrypt the attachments and am wondering if anyone has done this or can point me in the right direction.

There's been a couple of mentions about "encrypted email" -- but I don't know where to find further information. Is this possible using SSRS subscriptions?

Thanks in advance,

- Jesse Wolgamott

View 3 Replies View Related

How Do You Move A Report And Its Subscriptions To A Different RS2005 Instance?

Oct 26, 2007

I am new to RS. The Report developers want a way to move a report and its subscriptions to a different RS2005 instance. They tell me they can move the report but have to manually add the subscriptions back. Is that true? Is there no friendly way to move the subscriptions?

Thanks,

Michael

View 1 Replies View Related

Report With Subreports And Data-Driven Subscriptions?

Feb 7, 2008

I 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,

View 3 Replies View Related

Updating Report Templates Without Losing Subscriptions

Feb 14, 2007

Hello,

We are using Reporting services 2005 and have been for some time. Recently we have started using the subscriptions as well. However we have come across a problem with this.

Currently we are updating our report templates by removing them and then creating them again. This worked fine until we started using subscriptions. Now, when the reports are removed, the subscriptions that are associated with the reports are also removed.

Is there a way to update a report template, without having to remove the report first? And can this be done programmatically? The code is currently publishing the reports by calling ReportingService2005.CreateReport(name, "/" + reportFolder, false, template, null);

Thanks,
Denise.

View 1 Replies View Related

Is It Possible To Create Report Subscriptions From A Windows Application

Nov 29, 2007

from with in an application using the report view can you puase a report that is taking a long time to complete and then at a latter date resume where you paused the report.

View 6 Replies View Related

How To Get Column Names Against Each Record In A Table

May 18, 2012

I have a table with almost 100 columns. Ex:

ID ColA ColB ColC
2 Null Word Excel
3 Access Paint Null

Expected result set is :

ID ColA ColB ColC ColumnNames IsEmpty

2 Null Word
Excel ColA
Yes

2 Null Word
Excel ColB
No

2 Null Word
Excel ColC
No
3 Access Paint Null ColA
No

3 Access Paint Null ColB
No
3 Access Paint Null ColC
Yes

I would like to list all the column names against each record of the table and and would like to keep track whether the column is empty or nonempty. How can I write SQL query for this?

View 3 Replies View Related

Accessing External Data Within A Report

Jan 2, 2008

I have created a report using Visual Studio that brings back the expected information from the database when either run from within VS or is deployed to the server and run through Internet Explorer. The problem that I have is that records on the database each has a field that contains the location of a photograph file which is located on a different server. When I run the report from within VS on my PC it correctly includes the photo but when I run it from the server the picture just shows the icon denoting a broken link - although all of the database information is correctly displayed.

I think this may be a security problem as my workstation user has Domain Administrator capabilities but I am not sure which user is being used by Reporting Services when it tries to access the photo files from the other server. I have made sure that the user configured in Windows Service Identity also temprarily has Domain Admin rights but this has not made any difference.

Can anyone shed any light on which user the server uses when it runs on the server and tries to access network based information.

Thanks

View 1 Replies View Related

Wants Help For Extracting Semicolon Seprated Names From A Record

Aug 30, 2007

Hello friends,
I am designing an ETL process in which I have a source column which contains names seprated by semicolon( i.e Rajat Kr Sharma;Mr Sammer;Mr Ravi;Mr Ankur Bhatnagar) in each row.
ETL process should create n records,one for each single name in destination table as n numbers of name apprear
in each row of source table.

The number of names can vary in each row of source table as per no. of delimited character semicolon.

So Can some one suggest me to lay out some plan and what controls of SSIS I should use?

Here I m giving the pictorial view of activity I'm trying to carryout.



Source Table Row
______________________________________________________________________
|Rajat Kr Sharma;Mr Sammer;Mr Ravi;Mr Ankur Bhatnagar |
|_____________________________________________________________________ |

Changes to Destination Table
______________________________________
|Rajat Kr Sharma |
|_____________________________________|
|Mr Sammer |
|_____________________________________|
|Mr Ravi |
|_____________________________________|
|Mr Mr Ankur Bhatnagar |
|_____________________________________|

View 3 Replies View Related

One Or More Subscriptions Have Been Marked Inactive. Drop And Re-create All Subscriptions For This Node...

May 16, 2007

Greetings,



We periodically get the following message in our three server peer-to-peer transactional replication environment:



"One or more subscriptions have been marked inactive. Drop and re-create all subscriptions for this node that are failing with this error."



What causes a subscription to be marked inactive? What properties or settings can be adjusted to minimize or eliminate this occurrence?



Everthing seems to be running OK and then we get the message. It afflicts the higest change volume publication on our slowest overseas server.



Thanks,

BCB

View 9 Replies View Related

How To Print External Documents With SSRS Report.

Jul 15, 2007

Hello,

We have a requirement that on some reports, like details on a specific inventory item, we need to print with the report and external attachment like a word document, and auto cad drawing, or a picture.

Some of the ideas we are thinking of are:

adding the links to the external documents in a report parameter and then, when the report is about to be printed, get the values and send the documents to the print queue just after we send the report to the print queue.
Creating a custom control on the report to somehow stream the file to the print queue when the report is printed.
Render the report to a pdf and programmatically send the pdf and other attachments to the report queue.

Does anyone have any thoughts on this or, even better, some code that meets the requirements.

Thank you,

Paul



View 2 Replies View Related

Creating /accessing Tables With Dynamic Names

May 17, 2004

Hi
I have developed an application in ASP/SQL server 7.
Ths system is single user. One of the tables is updated by usr actions( say Table A).
To make it multi user. I want to create table such as A_Username.
How can query be written for this. Also there are many stored procedures which will access this table. In these stored procedures i will send username as an input. Then the query in the stored procedure shd access the table as A_Username .
Such dynamic table name refrencing , how can it be done.. Is creating a string for the query and then executing it using sp_exec the only option?
pls suggest

View 6 Replies View Related

Dynamically Creating Temp Table Names

Jul 20, 2005

Hello,I am interested in dynamically creating temp tables using avariable in MS SQL Server 2000.For example:DECLARE @l_personsUID intselect @l_personsUID = 9842create table ##Test1table /*then the @l_personsUID */(resultset1 int)The key to the problem is that I want to use the variable@l_personsUID to name then temp table. The name of the temp tableshould be ##Test1table9842 not ##Test1table.Thanks for you help.Billy

View 5 Replies View Related

Switch Record Background Color With Each Record In Report

Jan 14, 2008

Hi Everyone-

i have a matrix report
and i want to switch the record background color with each record in the value column in that matrix report
e.g 1st record background color is gray and next record background color is white
and then the next record background color is gray ... and so on

can anyone help?

thanx
Maylo

View 8 Replies View Related

Email Subscriptions: Changing From Address For Different Subscriptions On Same Server

Jan 18, 2007

I doubt this is possible, but can someone think of a way to change the email address used for sending report subscriptions based on the report or subscription?

It's a need that I've heard from a number of different clients. Scenario: a company has one reporting services server with reports running from numerous departments. Report subscriptions are sent to internal and external email addresses and there's a business need to use different "from" addresses based on the report (or audience).

View 1 Replies View Related

T-SQL (SS2K8) :: Creating Database Where Each Record Is Required To Have Twin Record In Database

May 12, 2014

,I am creating a database where each record is required to have a twin record in the database.These is a type a value and a type b value and both must be present for the record to be valid.

Customer_ID, Order_Type, Product_Code
54, a, 00345
54, b, 00356

Is this something that would have to be done programmatically, or is it possible to create a constraint of some sort to ensure this?

View 8 Replies View Related

Reporting Services :: Report Subscriptions Not Working With A Particular Domain Service Account?

Aug 13, 2015

I have an instance of SSRS that will not run my report subscriptions if it is using a dedicated domain account I made for the express purpose of using it to run this service.

If I have SSRS use my personal domain account as the service account, my subscriptions run correctly. If I have SSRS use this other domain account, the subscriptions do not run.

What else do I have to configure to make this run correctly not on my personal account?

Error message below.

"ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: AuthzInitializeContextFromSid: Win32 error: 5; possible reason - service account doesn't have rights to check domain user SIDs., Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;"

View 2 Replies View Related

SQL Server 2012 :: How To Set Some Restriction On Table Names While Creating

Nov 26, 2013

I need to ensure some naming standards to users while creating tables.

All table name should be in same pattern

(ie., ) TeamName_EmpId_tablename

I need a way to restrict user to follow the naming standard. If any user tries to create table without the above naming standard system should not allow.

View 5 Replies View Related

SQL Sever 2005 - Creating Alias Names Programatically

Aug 14, 2006

Is there a way we can programatically create a SQL server Alias name (SQL 2005 - SQL Native client configuration) using either T-SQL, C# etc. - Manmeet

View 4 Replies View Related

Transact SQL :: Creating Dummy Headers (column Names) With Union All

May 19, 2015

I want to create a raw SQL resultset for outputting to Excel with some artificial headers transposed over the top of the 2nd part of the Union's column names. The first part of the Union will be the Headers. Like this, the space to the left of the topmost columns is preferably empty ....

COL 1     COL2      COL3       COL4 etc.                         BEH                             BIG       BPL etc.

*************************************   INTAKT       DEFEKT       INTAKT DEFEKT          INTAKT

*************************************       B                E                 B         E                    B

I just want the text above as a 3 line header and there won't be any values obviously. Then the 2nd query will be joined to the above with a Union all. The 2nd query has all the same column names as what will be given in the first set above. What is the SQL Syntax for doing so? Do I have to use a from clause?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved