Dynamically Change Initial Catalog In Shared Data Source In Reporting Services?
Oct 30, 2007
First off, let me just say that I'm a complete newbie to SQL Reporting Services, and .NET in general. We have a VB 6 application that is trying to launch an SRS 2005 report in a viewer window. This was accomplished by creating A VB.NET "wrapper" window that launches the report, and allows the report to be previewed, exported, etc. I did not write any of this.
The report is using a Shared Data Source, which points to a specific database. My problem is that the users can select which db they want when they launch the VB 6 Application, so I need to find a way to edit the connection string in the report to specify which database to use on the fly. I have the db name in the "wrapper" application, but I can't figure out how to pass it to the report.
SQL 2012 SSIS package.I have a package connection that has the initialcatalog set in the connection string/properties page. This package connection also has an expression defined to set the initial catalog at runtime according to a passed in parameter. It works fine.
I am trying to create a second package in this same manner, but the connection does not seem to want to keep both the hardcoded initialcatalog and the expression to set it dynamically.
I can hardcode the initcatalog just fine, when when I add the expression to set it dynamically later, it clears out the initialcatalog I added.
What am I missing, why was I able to do this in the other package? I compared as much as I can think of between the two packages, all seems similar.
I have an archive of an Analysis Services database that was created on a server that is not accessible to me. I also have a copy of the source SQL Server database that it uses as a data source. I have restored both of these to my server. I have figured out how to change the data source to point to my server for the fact tables referenced, but I can't figure out how change the data source for the shared dimensions. I would like to be able to do work on this version of the database, but I get errors when I try to browse the dimension data because it can't connect to the original data source. Any ideas?
At the Moment we use SQL Server 2008 R2 Std. with Reporting Services. I want to change the individual schedules (non-shared) for 170 subscriptions without using the web Interface.
I tried to change table entries for dbo.Schedule and dbo.Subscriptions but the reports did not run. I also know that here are Jobs in the SQL Server Agent for the schedules. Now I need to understand how the mechanism works that updates the job entries from database tables. Is there a stored procedure which can be used?
We find that if we deploy the OLAP database with a different name on the test server, then regardless of how we change the connection string provided to the SSIS package that processes the cube, then the package fails to connect to the database. To clarify:
In development the OLAP database is called MyOlapDB and the source database is called MySqlDB. Both are on the same machine. When the the application is built and released for test, the test team install the databases on a replica of the production environment (i.e. web app on one machine, OLAP DB on another and SQL database on yet another). They also, quite rightly, implement the new test databases so they incorporate the build version number. So, MyOlapDB123 and MySqlDB123 are both from build 123.
This is when the problems start. Regardless of how the connection string is specified in the job that processes the cube, the SSIS integration package fails with the error:
[Analysis Services Execute DDL Task] Error: Errors in the metadata manager. Either the database with the ID of 'MyOlapDB' does not exist in the server with the ID of 'OurTestServer', or the user does not have permissions to access the object.
We have tried config files and job properties, but neither work. Also, simply attempting to run the package using the DTEXECUI does not work either.
Looking inside the XML of the package, we clearly see the ConnectionManager object which has the original connection string, which is
Data Source=localhost;Initial Catalog=MyOlapDB;Provider=MSOLAP.3;Integrated Security=SSPI;Impersonation Level=Impersonate;
However, editing the initial catalog here still does not solve the problem. Searching the XML for the string MyOlapDB reveals the OLAP database name in two other places - both within the object data of the two Analysis Services Execute DDL tasks.
Anyone know how to solve this problem without having to hack the XML of the package?
I'm using a shared data source to connect an Oracle server in my packages. After changing the database user password in the shared data source, I noticed the package concerned would fail with the following description.
I want to set the default value of the parameter based on the query result, when the current_status parameter is set as "opened" I want a certain date value to be displayed in the next parameter (start date), if it is set as closed I want a different date value to be displayed in the next parameter (start date), so I created the dataset with the query given below and assigned the default value to the query fields start and end date, however my Report parameters are not changing as expected.
dataset query:
IF @current_status = 'Opened' SELECT DISTINCT cast(DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0) AS date) AS Start_Date1, cast(MAX(estcompletion_date) AS DATE) AS End_date FROM <Table Name> ELSE IF @current_status = 'Closed' SELECT distinct cast(DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0) AS date) AS Start_Date1, cast(getdate() AS DATE) AS End_date FROM <Table Name>
I am a relative newbie to SSIS. I have been tasked with writing packages to import data from our clients. We have about 100 clients. Each client has a few different file formats. None of the clients have the same format as each other. We load files from each client each day. Each day the file name changes. I have done all of my current development work with a constant file name in a text file connection manager.
Ultimately we will write a VB application for the computer operator to select the flat file to load and the SSIS package to load it with. I had been planning on accomplishing this thru the SSIS command line interface. Can I specify the flat file to load via a variable that is passed through the command line? Do I need to use a Script Component to take the variable and assign it to the connection manager?
Is there a better way to do this? I have seen glimpses of a VB interface to SSIS. Maybe that is a better way to kick off the packages from a VB app?
My requirement for the parameter is multivalue parameter with a text box. for example when user enters aa15 it need to include product aa15. when the user enters aa15, aa16, zz15 than it needs to include all the three products. the last case is when the user enters AA** than i need to inclued all the products start with AA. when i use default multivalue parameter with data source analytical services than i am getting a drop down box. I dont want that. I need a text box where user can enter the value. 1. In sql we have a like key word to query . for example select * from product where product like "AA%". what is equavalent mdx query to get such results ? 2.How to impliment the multivalue parameters without using dropdown box?
I have a table (source file) where all sourse file(its all text file) name and location stored. i have a dts packeage, through this package all the sourse file will upload. So I need to dynamically change the source file name in dts packages.
I'm not a sql server savvy, so I need assistance on the following two scenarios:
A customer runs a script like this (slightly larger, but I ripped away the meat) --------------------------------------- create database test_database go
USE [test_database] exec sp_changedbowner 'sa'
use master; go sp_grantlogin 'server01CUSTOM_ADMIN'; go
use test_database; go
-- lots of table creations, where one of the tables are TEST_TABLE
sp_addrole 'ADMIN_ROLE'; Go sp_grantdbaccess @loginame = 'server01CUSTOM_ADMIN', @name_in_db = 'USER_ADMIN'; go sp_addrolemember @rolename ='ADMIN_ROLE' , @membername = 'USER_ADMIN'; Go
GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[TEST_TABLE] TO [ADMIN_ROLE] GO ---------------------------
Now, if a person is added to the server01CUSTOM_ADMIN group, he/she should be able to do the following: (let's say it's a he)
- Create a test.udl-file (win xp). Set a provider to sql server. - On the connection-tab enter hostname of database server in the Data Source-field and use windows NT Integrated security. - When he now test the connection, it should work, since he has access to the database. - Also, using the dropdown "3. Enter the initial catalog to use:", he should see SOME datatables. Not ALL, not none. The ones that he has access to.
So, if TEST_DATABASE is the only access that server01CUSTOM_ADMIN has, that database and only that one should show, right?
In my customers scenario, some databases show (irrelevant ones), but not TEST_DATABASE. In my test, I still get ALL databases. Even after I rip the guy out of the Administrators-group and Users-group. He's only a member of the CUSTOM_ADMIN-group on server01. "Test connection" succeeds, and all the databases on the server shows.
What I hope for is following questions like "He's probably sysadmin, check it" etc, so that I can systematically (using your brains) filter out the reasons for these scenarios to happen.
I connect, but when I try to access any table from this database I get an error indicating that the object doesn't exist and if I use the fullname xx.table I get no errors. What may be happening?
I have 4 Tablix and 2 of the Tablix get data from Server 1 and other 2 get the data from Server 2.I have set NoRowsMessage "=Data Not Available for the Selected Values" for all the 4 Tablix.Now if data is not available from Server 1 then I must show "Data Not Available for the Selected Values" only once in the outputbut now its appearing twice in the output because of the 2 tablix that had no rows.Similarly if data not available from Server 2 then it should show "Data Not Available for the Selected Values" only once in my output.If Data not avilable from all the Tablix then also i t should show only once as "Data Not Available for the Selected Values" in the report output.
I am new to Reporting Services, and I would like to serve up SQL 2005 Reporting Services reports via an ASP.NET web app (possibly using Report Viewer controls in Server mode). The web app will be running on an IIS server outside the firewall. I would prefer to have the Reporting Services server be inaccessible outside the firewall, but it seems that the client browsers need direct URL access to the Reporting Services server. I guess this means either locating the Reporting Services server outside the firewall, or opening a hole in the firewall for access to the Reporting Services server. Is there a Microsoft best-practice methodology/white-paper that addresses this scenario (especially as regards security concerns)? Alternately, would it be possible, and would the same report presentation functionality be available (reports with expanding/collapsing sections, choice of export formats, etc), by calling the Reporting Services web service directly from the ASP.NET app, and manually populating the Response object? Thanks. Any help would be appreciated.
I 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?
I have set up a report on reporting services and deployed it on a server. (server A) The data comes from 2 databases - one on Server A and one on server B
I can view the reports when logged into Server A, but if I try to view them from a networked PC I get the following message:
An error has occurred during report processing.
Cannot create a connection to data source 'byvshs01'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors
Byvshs01 is Server B.
However, if I remove the data from server B from the report, I can view it ok. So it seems you can view data from a DB located on the server that hosts the reports, but not if the DB is located elsewhere.
An error has occurred during report processing. (rsProcessingAborted)...The execution failed for the shared data set 'DataSet1'. (rsDataSet ExecutionError )...Cannot create a connection to data source ' Data source for shared dataset'. (rsErrorOpeningConnection)...the above error message I get on the published report, but everything works fine when I do a preview in Visual Studio.How can I see exactely what is wrong?
When Datazen caches a data source, it sometimes says that the data was truncated. What factors are involved in the truncation and what I might be able to do to expand the size of cached elements to avoid truncation? My only current solution is to make the data source "Real Time" rather than cached.
I experimented a strange behavior using connections for data source inside packages in vss 2005.
Suppose we have a tab1 in a develpment source db and in a test source db:
tab1 dev db contains 100 records tab 2 test db contains 200 records
I start developing a package using an 'ole db data source' task inside a data flow. When I change the data source from dev to test, strange thinks happens.
The 'ole db source task' retrieve only 197 records ... but if I open the connection inside the connection manager pane and only after i click on the 'test connection' button tI get 200 records.
The source db is a sql 2000 db server and I use ole db provider for sql server.
I have a got a package with source as sql table which has got 50 columns. We are using only 10 columns out of this. Recently one column name has changed and thus throws error invalid mapping. When I open the source to do the changes noticed that all the colums are prselected now and also the datatypes got changed to default ( I had changed the datatypes as per my requirement while i developed). So now I had to select required columns from source and redo the datatype changes in advanced editor.Is there any option which doesnt disturb this settings and we just need to correct the mapping alone.
Hi there, I am in the process or deploying our existing .NET 1.1 application together with its SQL Server 2000 databases to 2 new hardware platforms (one for databases and one for application). I have transferred the databases and installed the application (using our installers). I have tested the database logins and checked the database user rights - everything is fine - or so it appears to be. However, when I try to open the application it receive the following exception [Exception: Data Source=172.16.7.1;Persist Security Info=False;UID=myAccount; password=myPassword;Connect Timeout=60;;Initial Catalog=http://xxx.xx.x.x/directory/myPage.aspxMYDATABASE;Pooling = 'false';Application Name=WEBAPP; System.Data.SqlClient.SqlException: Cannot open database requested in login 'http://xxx.xx.x.x/directory/myPage.aspxMYDATABASE'. Login fails. Login failed for user 'myAccount'. at System.Data.SqlClient.SqlInternalConnection.OpenAndLogin() at System.Data.SqlClient.SqlInternalConnection..ctor(SqlConnection connection, SqlConnectionString connectionOptions) at System.Data.SqlClient.SqlConnection.Open() at FrameWork.DataServices.DBConnect.GetInternalConnection(Int32& icnt)] FrameWork.DataServices.DBConnect.GetInternalConnection(Int32& icnt) +655 FrameWork.DataServices.DBConnect.GetInternalConnection() +16 FrameWork.Components.DBComponent.get_m_SqlConnection() +59 FrameWork.Components.DBComponent.ExecuteReader(SqlCommand cmd, Int32 iRetry, Boolean bIgnorecase) +53 FrameWork.Components.DBComponent.ExecuteReader(SqlCommand cmd, Boolean bIgnorecase) +337 FrameWork.Components.DBComponent.ExecuteReader(SqlCommand cmd) +7 It appears as if my ADO connection string is faulty. If a manually connect using a connection string in the format I expected it to be, everything works. Here are my manual connection string: Data Source=xxx.xx.x.x;Persist Security Info=False;UID=myAccount; password=myPassword;Connect Timeout=60;;Initial Catalog=MYDATABASE;Pooling = 'false' What is inserting the URL that I used to open the page ahead of my Initial Catalog parameter?Any assistance will be more than welcome.CJ
I have a question about ssrs security. In report manager I have set a list of users as browsers, and the builtin administrators are content manager in the parent folder where reports are. A user that is not an administrator and neither a user I added, has access to reports.
I'm thinking this person is having access because the data source of the reports use a administrator account to connect to the analysis services using the option Credentials stored securely in the report server with the 2 checkboxes marked (use as windows credentials when connecting to the data source and impersonate the authenticated user).
I simply marked this option because SSRS is in 1 server and SSAS in other and I think kerberos need to be configured and I haven't looked into it, but I think the report access is separated from the data source.
How can i get the shared data sources i created in the report manager to be available in VS.net to be able to assign to a report. I want to be able to create the shared data source first in Report manager and then be able to assign it a report in VS.Net
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 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:
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?
