Is there a command that can be used to set the account and password of an SSRS data source? I want to use a datasource that uses the option "(windows) credentials stored securely in the report server". The password for the account to be used can not be known by any humans (or we have to kill them after) so we want to set it programmatically. Is this possible via a command?
View 4 RepliesI am running Microsoft SQL Server 2005 Express Edition, and I am having difficulties setting up an ODBC User Data Source to connect to the SQL server.
I have tried going through "Control Panel -> Administrative Tools -> Data Sources (ODBC)" to add a user data source. For the SQL Server to connect to, I have tried specifying "localhostSQLEXPRESS", "SQLEXPRESS", "(local)", etc. -- but nothing seems to work. When the DSN configuration wizard tries to connect to the SQL Server to obtain default settings, it fails with the error "SQL Server does not exist or access denied". (Same error occurs if I try connecting to this ODBC data source later.)
But I know that the SQL Server exists and I have been using it through the SQL Server data provider in another application.
I am new to this, so any help or pointers would be greatly appreciated.
Thanks,
Kumar
I'm trying to set up a user DSN for a MS Access database in Vista. In XP it's usually just a case of going to Admin Tools > Data Sources but I only get the option of SQL Server and SQL Native Client with Vista.
I'm pretty sure I found a way of doing it a while ago by opening another version of Data Sources from the command prompt but can't for the life of me remember what it was.
Anyone know how to do this?
Hello,
I've been working on an application that uploads an RDL to Reporting Services (through the SOAP webservice method CreateReport) programmatically. I'm having difficulty setting up the data source properties for my uploaded report. In particular the Data Source Credentials property.
The datasource for my report doesn't require credentials. By default after I upload the report to Reporting Services, the Data Source Credentials property is set to "Credentials supplied by the user running the report". How do I go about setting the Data Source Credentials property to "Credentials are not required" programmatically through the webservice?
Thanks in advance
Was wondering if there was a best practice minimum permissions for creating a SQL login to use when setting up a new shared Data source for SSRS report manager?
Something along the lines of them being a data read for the DB and permissions to update tempdb?
Would have thought it not advisable to have the login be able to update the main db...
I have a SSRS report that uses a custom library. The custom library returns a string values, and I have tested this with a windows application.In the SSRS report. I have set the expression value for a text box as  =CodeReportingLibrary.CodeReportingFunctions.GetImage(1232)
However, when i preview the report the text box value shows as #ERROR. I checked the error list and I get a warning message : Warning 1 [rsRuntime ErrorIn Expression] The Value expression for the textrun ‘Textbox5.Paragraphs[0].TextRuns[0]’ contains an error: Attempt by security transparent method 'CodeReportingLibrary.CodeReportingFunctions.GetReferenceImage(Int32)' to access security critical method 'Microsoft. TeamFoundation. Client. TfsTeamProjectCollection..ctor(System.Uri)' failed.  Assembly 'CodeReportingLibrary, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' is partially trusted, which causes the CLR to make it entirely security transparent regardless of any transparency annotations in the assembly itself.  In order to access security critical code, this assembly must be fully trusted.
I have updated the rssrvpolicy.config in ReportServer folder, to include my custom dll.
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="CoDeMagSample"
Description="CoDe Magazine Sample. ">
[code]....
This is a question regarding the use of an XML data source within an SSRS sub-report. This is a two server configuration consisting of SSRS 2K5 and a separate file server exposing xml files over http, IIS 5.
Is it possible, and if yes how, to configure the xml data source to use the application pool identity instead of the current caller identity? I can not turn off impersonation within SSRS. I need to revert to use the W3WC, app pool, process identity within the xml data source. I do not want to configure Kerberos delegation and the file server€™s IIS virtual directory must be set for windows security.
I have a vision of a beautifully interlocked Business Intelligence system in which I load dimensional fact tables through SSIS, build aggregations in SSAS cubes, and quickly and easily generate SSRS reports using the cubes as data marts.
I am now a month into trying to implement this vision and Reality is stomping all over it. Primarily, I am running into issues with the "cube as a data mart" idea. Each cube is taking up an additional gig or two of space, report queries against them seem to take longer than a straight SQL query against my fact tables, and I am running into serious problems trying to pass time-related calculations such as Year-To-Date to my reports.
Has anyone else tried setting up a BI system that uses cubes as the primary drivers for their reports? Have you seen any benefits to doing it that way? Or should I generate most of my reports through the SQL tables?
I'm nearing the point-of-no-return in choosing my final methodology, and any feedback would be greatly appreciated.
Thanks!
-- Steve
I have a table that I'm loading as part of a control flow that in turn is copied to a target table by using a data flow task. I am doing this because a different set of fields may be used from the source entry to create the target entry based on a field in the source table. That same field may indicate that multiple entries need to be created in the target table from one source table entry for which I use a multi-cast transformation.
The problem I'm having is that no matter how many entries there are in the source table, the OLE DB Source during execution only shows 7,532 entries being taken from the source table. If there are less than 7,532 entries in the source table, everything processes fine. More than 7,532 and the data flow task only takes 7,532 and then seems to hang. It also seems as though only one path of the multi-cast transformation is taken when the conditional split directs a source entry down that path.
Seems like a strange problem I know, but any insight anyone could provide is appreciated. Thanks.
Hi,
I'm new to SSRS but have a problem connecting to Oracle. I have placed my reports upon a reporting server but the shared data connection isn't working and I'm confused as to why. I have specified the name, connection type as "Oracle", set the correct user id and password for the credentials and the connection string as for example 'Data Source=oracleExample;Unicode="True"'. I also have set the correct entry in the tnsnames.ora file for this datasource, example:
"oracleExample =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 12.23.45.23)(PORT = 3425))
)
(CONNECT_DATA =
(SID = oracle)
)
)"
Unfortunately I receive the following error when trying to connect.
"
An error has occurred during report processing.
Cannot create a connection to data source 'oracleDataSource'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors
"
I'm mystified as to what the issue is. Any ideas? Would restarting the report server pick up the tnsnames.ora file? I have this working locally using report designer.
Any help, much appreciated.
I'm attempting to use SSRS (2005) with my Oracle RDB database (old DEC RDB), using an ODBC driver. I am able to create a report, and view data, but I need to add some report parameters to filter the data. When I attempt to use parameters in the query, I either get a syntax error, or a message that my odbc connection cannot use "named" parameters, and that I should use "unnamed" parameters.
Here is a snippet of my sql query:
SELECT Application_Notes.Lease_Application, Application_Notes.Followup_Code,
<...>
FROM
Application_Notes Join
<...>
WHERE
(Application_Notes.Tickler_date >= {ts '2008-02-08 00:00:00'} AND
Application_Notes.Tickler_Date < {ts '2008-02-09 00:00:00'} )
The "Tickler_Date" field is a binary date field. The above syntax is pulled from a Crystal Report, which I am considering moving over to SSRS. Ideally, I would like the user to enter a single RunDate parameter, and have the query do the following:
Where (Application_Notes.Tickler_Date >= @RunDate and
Application_Notes.Tickler_Date < (@RunDate + 1 day) )
If the user has to enter two dates, that will be fine, but I can't seem to get past the errors.
Thanks,
Paul
I'm new to SSRS 2005, and having a very hard time getting something I thought would be trivial to work. It would appear that if you want to report from XML data, there are a variety of ways to do so.
I would just like to use a simple document that was created by serializing to disk. I don't even have a fancy query. Can someone help me with an start-to-end from which I can build upon and add complexity? Thanks.
C:XmlDataCustomers.xml contains a single Customers table; 3 Customer rows with an ID, FirstName, LastName. (I'll try pasting it as a follow-up).
My DataSource type is 'XML' and the ConnectionString is the simplest of URLs "C:XmlDataCustomers.xml". Credentials are set to 'none'. My suspision is that I am not even succeding at creating a valid DataSource. I don't know if it has to do with the XML, the location of the file, the syntax of the ConnectionString or what.
Assuming I get this right, I want all rows and all columns, so I assumed an empty query on the DataSet would be appropriate.
I've found a bunch of examples - but none seam to be this trivial - they all involve using a web service, embedded data in the query, or creating a Customer Data Extension (CDE) to provide the document. I've tried converting these samples to use my DataSource, but so far no luck.
Your assistance is appreciated.
See above...
Apologies for the duplicate post!
I have inherited a SSRS setup and am trying to do some clean up. Problem is I cannot see the Data Sources Folder. I am setup as SA and SU on both the RS Server and SQL Server. When I go check the catalog in SQL db it does truly exist as well as a number of data sources in the folder that reports are currently using. How can I see this folder?
View 7 Replies View RelatedI want to run SSRS report directly by connecting Excel as a data source. The report runs properly from SSDT, but when it is run from Report manager, some users cannot access the Excel connection and the report execution fails. I am using SSRS 2012 and Excel 2010.I have created the ODBC data connection in SSRS and when I use 'Windows Integrated Security' option for credentials, the connection gets successful. But the problem is that all users does not have permissions to access the Excel inline, but they should get the report working.How can I store the credentials to access the Excel in SSRS with a specific username and password?
View 4 Replies View RelatedI cannot get this error resolve either for myself or any users, and I'm actually part of the Admin group! Furthermore, a user with whom I'm tring allow to view this report keeps getting prompted for their windows username and password. I thought that since this datasource was set to Windows Authentification that it would just pass it through?
I'm pulling my hair out at this point:
Screen Shots:
http://www.webfound.net/datasource_connection.jpg
http://www.webfound.net/datasource_connection2.jpg
http://www.webfound.net/datasource_connection3.jpg
Also As far as I know, I've given sufficient permissions to the right logins and right users on my SQL Databases and related stored procs that the datasets (not datasource) run
An error has occurred during report processing.
Cannot create a connection to data source 'datasourcename'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors
Hi All,
It's been a while since I post here. Anyway, I'm trying to call a SSIS package as my data source for SQL reporting service but I keep getting the error "Cannot create a connection to data source 'DsSSIS'.
My DsSSIS consists of the following in the connection string.
="/file c:FarmBillTesting.dtsx /Set Package.Variables[User::FilterValue].Properties[Value];" & Parameters!FilterValue.Value.ToString()
I have tried everything that I know to make this work but I have been able to do so. Any help from the group would be great.
Thanks
Ham
Hi,
I'm am writing a report in SSRS 2005 on a local PC that uses cubes in AS 2000 on another server as a datasource. I am using the Microsoft SQL Server Analysis Services Extension (OLAP9) to connect to the cubes using windows authentication. I can save and preview the report in visual studio and publish the reports on IIS on my local PC with no problems but when I go to view the report in IE, I get the following error:
An error has occurred during report processing.
Query execution failed for data set 'dataset'.
Unable to recognize the requested property ID 'ReturnCellProperties'.
Have read various articles on the net with no conclusive explanation.
I have seen another post on this forum that suggests using OLEDB for Analysis Services instead but if I do this it doesn't allow me to use the MDX visual query designer. Creating the report using OLAP9 then changing to OLEDB and pasting in the query code doesn't work so well either.
I went and read http://technet.microsoft.com/en-us/library/ms159219.aspx about data sources supported by SSRS 2005 and it should be ok.
I am using sql server 2005 with the latest service pack 2.
Any ideas?
Hi,
We are in the process of upgrading a SQL Server 2000 database to 2005. For the mean time, we're trying it out on SQL Server Express 2005. My question is, can reports in SSRS 2005 pull data from SQL Server 2000 in case both are installed side by side in the same machine and the 2000 version is the default instance while the 2005 express edition is the named instance?
Thanks a lot!
I am building a webapp that calls an SSRS instance to display a report based on another servers web service.I can make a call from the ssrs server using this in the RDL:
<Query>
<Method Namespace="http://machine/webservices" Name="HelloUser">
               <Parameters>
                               <Parameter Name="user">
                               <DefaultValue>Craig</DefaultValue>
                               </Parameter>
               </Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">*</ElementPath>
</Query>
This works no problem. but.. I want to have the user parameter "Craig" be a parameter passed in from the web app. If it were a regular SQL data source you would put @user in the query. How do you do it with XML text queries?
I am looking command (cmd/powershell/c#) for setting/updating credential for SSRS data source.
View 2 Replies View RelatedI have published SSRS reports to sharepoint and used the Reporting Services Report viewer to present the reports on a Page.  Everything works fine for the day, however in the morning, when I go to view the page, I get an error messageÂ
The report server cannot process the report. The data source connection information has been deleted. (rsInvalidDataSourceReference)
how to trigger report's refresh following its underlying SSAS cubes process?
I want to keep reports data updated at all times, especially when this happens while user is already browsing the report..
I don't want to set an auto refresh for the report every 5 minutes as my cube is processed only one time during the day...
We are running Reporting Services 2008 R2 on a Windows Server 2008 Standard 64-Bit server. I have a user that has full access to Reporting Services at all folder levels but IS NOT a local administrator on the 2008 server.
This user can create data source connections but when he tries to test the connection by clicking on the 'Test Connection' button, he gets the following error "The permissions granted to user <username> are insufficient for performing this operation.A user that has administrator priveleges on the server can test the connection fine.Â
I don't want to make this user an administrator on the server.Â
This is an extract from the log file:
ibrary!ReportServer_0-24!3478!08/16/2011-13:45:37:: Call to TestConnectForDataSourceDefinitionAction().
library!ReportServer_0-24!3478!08/16/2011-13:45:37:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: , Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user <username>
are insufficient for performing this operation.;
[code]....
hi ,
i am trying for a drill through report (rdlc)
ihave written the following code in drill through event of reportviewer, whenever i click on the first report iam getting the error like
An error has occurred during report processing.
A data source instance has no
t been supplied for the data source "DetailDS_get_orderdetail".
the code is
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
//using Microsoft.ApplicationBlocks.Data;
using Microsoft.Reporting.WebForms;
using DAC;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
ReportViewer1.Visible = false;
}
protected void Button1_Click(object sender, EventArgs e)
{
DAC.clsReportsWoman obj = new clsReportsWoman();
DataSet ds = new DataSet();
ds = obj.get_order();
ReportViewer1.LocalReport.DataSources.Clear();
ReportDataSource reds = new ReportDataSource("DataSet1_get_order", ds.Tables[0]);
ReportViewer1.LocalReport.DataSources.Add(reds);
ReportViewer1.LocalReport.ReportPath = "C:/Documents and Settings/km63096/My Documents/Visual Studio 2005/WebSites/drillthrurep/Report.rdlc";
ReportViewer1.LocalReport.Refresh();
ReportViewer1.Visible = true;
}
protected void ReportViewer1_Drillthrough(object sender, DrillthroughEventArgs e)
{
DAC.clsReportsWoman obj = new clsReportsWoman();
ReportParameterInfoCollection DrillThroughValues =
e.Report.GetParameters();
foreach (ReportParameterInfo d in DrillThroughValues)
{
Label1.Text = d.Values[0].ToString().Trim();
}
LocalReport localreport = (LocalReport)e.Report;
string order_id = Label1.Text;
DataSet ds = new DataSet();
ds = obj.get_orderdetail(order_id);
ReportViewer1.LocalReport.DataSources.Clear();
ReportDataSource reds = new ReportDataSource("DetailDS_get_orderdetail", ds.Tables[0]);
ReportViewer1.LocalReport.DataSources.Add(reds);
ReportViewer1.LocalReport.ReportPath = Server.MapPath(@"Reportlevel1.rdlc");
ReportViewer1.LocalReport.Refresh();
}
}
the code in method get_orderdetail(order_id) is
public DataSet get_orderdetail(string order_id)
{
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
cmd.Parameters.Add("@order_id", SqlDbType.VarChar, 50);
cmd.Parameters["@order_id"].Value = order_id;
ds = SQLHelper.ExecuteAdapter(cmd, CommandType.StoredProcedure, "dbo.get_orderdetail");
return (ds);
}pls help me.
Hi,
Is there a way to set the XSD programatically? I don't want to hard code an XSD filename/location.
Alternatively, is there a way to set the location in a relative path name (i.e. relative to the path of the package)? I did see a similar question here but that wasn't answered. I tried but I wasn't getting anywhere.
As far as I can see there is no way to set this with an Expression either.
Another alternative I thought of would be to set this with using the SSIS object model but I have no experience with this and I don't know if it can be done at run time. If it can be done I would appreciate a pointer to some further information on doing this.
Thanks,
Mark
I'm looking for a way to set the id (on an asp page) when a selected row from a SSRS report in clicked. The id set would then become a session parameter on said asp page.
View 1 Replies View RelatedMy manager has asked me to install and configure Source Safe so that our team can check out stored procedures, (and other objects), and check them back in when we are finished working on them.
I'm not familiar with Visual Source Safe. Does SQL Server 2005 work with Visual Source Safe in such a way that I can check out/in SQL Server objects such as procedures?
If someone knows the answer, or perhaps an existing thread that covers this, please let me know.
Thank you for your time!
Frank
I'm running EM on my local box and sql server on a remote internetaccessed box.How do I specify a file path for a DTS package to access files on theremote box?For example, to run a local dts package the filepath isc:filepathfile.txt.How would I change the file path/name to allow the dts package toaccess files on the same remote machine?-Dave
View 1 Replies View RelatedI would like to set up a subscription that has two date parameters, I would like the end_date to be today and the start_date to be (today - 1 Month). The interface does not seem to support expressions?
I saw some documentation that said to use defaults in the report but that does not help because I may want multiple subscriptions with different params like 1 person may want (today - 2 months) as the start date ...
Any help would be appreciated
I want to create some links in Sharepoint that will take me directly to a report, display it within the SharePoint full page report viewer and set some parameter values along the way.
I've tried:
http://<sharepoint server>/<portal name>/<report library>/<report name>.rdl?Param=value
No luck.
Anyone have suggestions? Workarounds?
Has anyone seen the error:
[rsInvalidColor] The value of the BackgroundColor property for the textbox €˜textbox14€™ is €œTransparent€?, which is not a valid BackgroundColor.
This is appearing when I set the BackgroundColor property in a SSRS report using an expression. What I'm trying to do is have the colour of the text and background returned by the stored procedure that returns the data for use in Matrix and Table. But the problem seems to relate to all controls in SSRS 2005.
Steps to reproduce are create a new report, add a TextBox.
Go to properties of the textbox and select the Backgroundcolor and select Expression...
Then add the expression:
="Transparent"
Transparent doesn't seem to be a constant, but does appear to be a value you are allowed to selected (and is the default) from the drop down in the background colour.
The work-around is to use White, which works if you don't use a background image or change the background colour of the page.
However if you do us the "Transaparent" colour, then it does return the correct effect, but generates an error in the Output window within the development environment. I'm just ignoring the error for the time being. but has anyone else seen this error.
Andy
Hoping someone may be able to help with a problem I'm having with SSRS parameters....
My report has 2 parameters - the User Id used to login to the application and the Department(s) within the organisation. Based upon the User's role, the user may have access to data for one or many Departments.
Thus, the first parameter needs to be set in code based upon the User's login, however, the range of the second parameter (i.e. the range of Departments that the user can access) is controlled by the value of the first parameter.
The second parameter is to appear as a drop-down of Departments to which the User has access.
The report is to be produced for the selected Department.
Are you able to advise how to restrict the range of values for the second parameter based upon the value of the first parameter?
Any help is much appreciated.