Accessing The Underlying Sql Query From The Rdl File
Apr 3, 2007
Our client uses the report builder to generate reports for collection of employees. We would like to use the employee records in this report to perform some additional processing (such as the list of employees gets assigned to a particular group).
Programmatically, I can retrieve a byte stream from rs.GetReportDefinition( "myClientEmpReport") and deserialize the stream into Report object (as define by ReportDefinition.xsd).
I can then manually drill down and retrieve the SematicQuery xml from the commandText field.
The problem is how to convert the SemanticQuery format into a T-Sql query that I can run against the view that the report model is based off of.
Is this possible?
Thanks,
Arjay
P.S. Running SQL 2005 Reporting Services, VC# 2005, ASP.Net 2.0.
I have an SQL Job whose sole step is to run an SSIS Package via the File System mode. The .dtsx file itself is located on the server where SQL resides.
The SSIS task looks inside a specific directory and if a qualifying file exists, archives the file, unzips the file, rename the contents and, eventually, processess the files.
Invariably, if i Stop the Job before it has completed successfully, i notice that the underlying file (in this case, a .zip file) remains 'in use'. If i try and move the file or rename the file, the OS says "no can do; the file is in use".
but no matter where i look on the network when I go to Manage (right-clicking on My Computer), i cannot fine any reference under Shared Folders | Open Files to "close" the file.
can anyone shed some light on this? ie, how to close, etc.
I'm a hobby C# programmer, and when at university there are a few machines that have SQL Server 2005 installed on them (i think it's standard ed).
From looking around at sql 2005 free edition and DNN (dot net nuke) it's apparently possible to have the database file in the execution directory of your application.
My question is this, if i have a C# win app, is in possible for me to store the database files, in the executable directory of that app, and not in the default sql database store of C:prog filessql server90 data etc...?
Ie, what i want to do, is for the C# win application on start up to "mount" the database, use it normally like any other sql database, and then "unmount" it at the end.
The reason for "mounting" and "unmounting" is that i don't want the database to be accessible or even visible to sql server when the c# app isnt running.
I hope I've explained myself clearly, and thanks a lot in advance. pro-logic
I have a SSIS package which reads XXX.rpt file from a LAN share. This is a simple text file from a mainframe job. The package is stored in file system and invoked by a third party scheduler. The scheduler uses a specific NT account to run the package and the NT account has read access to the share. The SSIS package is not updating the rpt file in the share. It reads the rpt file and updates sql database.
The issue is when the package is run, it returns the following error:
Error: 2008-02-07 14:52:33.18 Code: 0xC001401E Source: Mycustom Data Extract Connection manager "MyText File" Description: The file name "\myserverdev est.rpt" specified in the connection was not valid. End Error
I have verified that the NT account has read access to the LAN share. Does the account should also have write access?
I created SDF database files for "SQL compact" with a size larger than 128 MB (which is default for creation). Now when I try to open these files with VS2005 I get the error "The size of the databasfile exceeds the configured maximum...Required Max Databse size (in MB; 0 if unknown)" (translated from german). The real problem is that I can not change the connectionstring in VS, cause all field which show the connectionstring are readonly (greyed out). I know I have to set the option "Max Database Size = 512" or so in the connectionstring to get the things runnning, but don't know any way to do that in VS2005. My attempt to access the SDF files when copied to the device via active sync results in the same error message.
This seems for me to be a design flaw, cause I can not add the optional parameter to the connection string (even not in the details form, where only "DataSource" and "Password" fields are displayed. - Does anybody know a solution in VS2005? - Does anybody have a workaround for me? - Does anybody know where the connectionstrings of VS2005 are stored, so that I may "hack" the connection string?
I am trying to create a sproc creates several tables, views, and sprocs across several similar DBs (please...no lectures on structure...I didn't create this multi-DB setup) .
My sproc loops through the DBs and runs a script on each DB. I am running the Sproc from a client computer and cannot easily access the c drive on the SQL server.
The only way I have figured out how do do this is to run the following
xp_cmdshell 'isql d- %@mydb% i- c:script.sql'
inside a loop.
My problem is that xp_cmdshell refers to the server c drive instead of my local c drive, where my script file is.
Is there a way to refer to my local c drive? Also any thoughts on a better way to approach this (other than running the entire script inline in the sproc since I don't want to have to run quotes around each batch of the script).
hi every one. i am a new user of asp.net 2.0 using C# code and i am facing a problem in accessing a SQL Server2005 database file in the remote computer. i have connected two pc with peer to peer connection and trying to add a databse using the "Add connection" option from the visual studio 2005. in the add connection dialog box it is showing me the remote server and it was supposed to show all the database in that SQL Server when i select one. but when i am choosing the server name it was not showing me anything. by the way i have configuered the surface area for "both TCP/IP and named pipes" and both the pc's server browser is turned on. is it the right way to access a database file from a remote pc or not?? please send me a good solution to do this things and try to explain the codes with example. waiting for response....plz send me the solution.. as soon as possible
I'm doing a simple ETL that reads a database table and dumps the content to a text file. The text file will be named Employee.txt. This file name will remain the same across my environments, but I may want to vary the directory location to where I want this file dumped.
So, I defined an environment variable called "DataTargetDir" in all my environments. Now, I want to utilize this variable in the "File name:" box within the Flat File Connection Manager Editor. How do I do this? I'm thinking I can write something like "%DataTargetDir%Employee.txt" in the "File name:" box, but it's not working.
We have SQL Server 6.5 with an Access 8.0 front-end. We are about to re-write our front-end in VB6 using RDO. Obviously Access has a nice fast and easy to use query builder. My question therefore is - is there any such query builder that can be used within VB6 to simplify query writing ? Or do I have to write all query's and and stored procedures using straight SQL and/or stored procedures. I do not want to use Visdata as it doesn't appear up to the task.
I've found example code of accessing an SQLDataSource and even have it working in my own code - an example would be Dim datastuff As DataView = CType(srcSoftwareSelected.Select(DataSourceSelectArguments.Empty), DataView) Dim row As DataRow = datastuff.Table.Rows(0) Dim installtype As Integer = row("InstallMethod") Dim install As String = row("Install").ToString Dim notes As String = row("Notes").ToString The above only works on a single row, of course. If I needed more, I know I can loop it.The query in srcSoftwareSelected is something like "SELECT InstallMethod, Install, Notes FROM Software"My problem lies in trying to access the data in a simliar way when I'm using a SELECT COUNT query. Dim datastuff As DataView = CType(srcSoftwareUsage.Select(DataSourceSelectArguments.Empty), DataView) Dim row As DataRow = datastuff.Table.Rows(0) Dim count As Integer = row("rowcnt") The query here is "SELECT COUNT(*) as rowcnt FROM Software"The variable count is 1 every time I query this, no matter what the actual count is. I know I've got to be accessing the incorrect data member in the 2nd query because a gridview tied to srcSoftwareUsage (the SQLDataSource) always displays the correct value. Where am I going wrong here?
Hi,I have a question about estimated query execution plans that aregenerated in QA of MSSQL.If I point at an icon/physical operator in the estimated QEP, it showsmesome statistics about the operator.Is there a way to retrieve these statistics through a query, i.e., canthese statistics be available to the user?Also, is there a way to generate these statistics on my own?thanks in advance-TC.
I have several database files, all in the same directory. I want to write SQL statements that access two databases at once. For example, if only one database would be involved, I would write:
SELECT Test_Source_ID_column, Data_column INTO Test_Destination_table FROM Test_Sources_table The above query copies two columns (Test_Source_ID_column and Data_column) from Test_Sources_table into a brand new table called Test_Destination_table. Now the above query is fine for both the old and new tables being in the same database. But what if Test_Sources_table is in file Old.dbf and I want the new table Test_Destination_table to be in New.dbf? Both Old.dbf and New.dbf are in the same directory. Therefore, there is no security issue about using ......etcpasswords. I should therefore be able to use relative paths. So, I also want to know how I can do that select on two databases in the same directory without typing long, long paths. Is there a way to do queries that access multiple database files (all database files being in the exact same directory) AND use relative paths or no paths (and certainly not type a full path)? NOTE 1: I'm using SQLEXPRESS, therefore do not have the SQL Server 2005 tools. I have Visual Studio 2005 Developer Edition with its tools. NOTE 2: One table is accessed through ODBC. The other table is accessed through SQLEXPRESS.
Does anyone know if it is possible to access the execution plan results programmatically through a stored procedure or .NET assembly? I have the code sample
SET SHOWPLAN_XML ON; query... SET SHOWPLAN_XML OFF;
but it can only be run from the interface. I have tried a couple of solutions including dynamic sql to try to capture the results in a variable or file with no luck.
Does anyone know of a way to programmatically capture this information? We are doing some research with distributed query processing of dynamically generated queries using multiple processing nodes, and it would be helpful to know an estimate of how large the query is before sending it away to be processed.
I have looked at the dm_exec_query_stats view; but it can only be run on a query that has already been executed. I need to know the execution plan before the query is executed. If there is a way to get a query to show up in this view without being executed, then that would work as well.
I have a report that accessed a Visual FoxPro 6.0 database via ODBC. Since I upgraded to Visual FoxPro 9.0, now I am getting the error:
Query Server Error DMS-E-RBI_TABLE The table or view <table name> was not found in the dictionary
I've verified that the right path is setup and I've tried installing about every driver and none of them are working. I created a new database in 9.0 and the I can access it fine. Also, there are old tables that are accessible in the same path, just not the ones I need. Any ideas would be helpful..
We are experiencing failures when accessing a datatype="Attachments" field in a query in an MS Access 2007 database using ACE ODBC or OLEDB drivers.
We are using an MS Access 2007 database ACE ODBC/OLEDB drivers installed (i.e. Office 2007, which installs these drivers) DB contains a table with a field of type "Attachments" (which is new in MS Access 2007) DB contains a query that selects the fields of the above table
Using ACE ODBC or ACE OLEDB drivers to display the table works fine. The "Attachments" field is displayed as the file name of the attachment(s).
However, using either ACE ODBC or ACE OLEDB to display the query (i.e. NOT the table) results in either incorrect results or unexpected failures of the ODBC/OLEDB drivers.
Error using ODBC (using "ODBC Test"): ============================= select * from Query1 does not give an error, but displays a "1" for the Attachment field. select Attachments from Query1 gives the following error: stmt: szSqlState = "HY000", *pfNativeError = -3087, *pcbErrorMsg = 97, *ColumnNumber = -2, *RowNumber = -2 MessageText = "[Microsoft][ODBC Microsoft Access Driver] Reserved error (|); there is no message for this error."
Error using OLEDB (using "RowSetViewer"): ================================ select * from Query1 gives the following error: Interface: Unknown Result: 0x0004001 = E_NOTIMPL FormatMessage: €œNot implemented File: F:DepotSQLVaultmdac28sdkSamplesoledb owsetviewerSDKobji386CRowset.cpp Line: 616
select Attachments from Query1 gives the following error: Interface: IID_ICommand Result: 0x0004005 = E_FAIL IErrorInfo: [0x0000f3f1] €œUnspecified error€? File: F:DepotSQLVaultmdac28sdkSamplesoledb owsetviewerSDKobji386CCommand.cpp Line: 439
If it would help to analyze the problem, I have a folder containing all pertinent files (bare-bone database, tools, instructions) to reproduce this. I could attach it as a ZIP file, if requested.
I am building a customerlist within the customer sales of a period. I have a dataset with two tables: "customer" and "customer_ledger_entry".
In the report I will present the customer number, the customer name and the sales. The problem is the sales value is not available as a field, but I have to calculate this value from the "customer_ledger_entry" table. In this table are several entries (invoices, credit notes, etc.)
How to calculate the values from a underlying table?
We have a partitioned view with 4 underlying tables. The view and eachof the underlying tables are in seperate databases on the same server.Inserts and deletes on the view work fine. We then add insert anddelete triggers to each of the underlying tables. The triggers modifya different set of tables in the same database as the view (differentthan the underlying table). The problem is those triggers aren't firedwhen inserting or deleteing via the view. Inserting or deleteing theunderlying table directly causes the the triggers to fire, but not whenthe tables are accessed as a result of using the view.Am I missing something? The triggers are 'for insert' and 'fordelete'. No 'instead of' or 'after' triggers.
I have to write a report using SSRS using a cube somebody (no longer here) created. Â I know SSRS and SQL very well, but have never worked with cubes.Â
I want to make sure the cube was properly constructed (in terms of table/join relationships). Â I cannot find the tables/joins. Â I looked in SSMS under Data Source Views and everything else I could think of (or google). Â
Have many views based on legacy tables that have different table and column names. Want to create a table that shows view table / column and underlying table column, e.g.
I don't know if there is a fundamental problem with what I am trying to do, or am I just having problems setting it up correctly:
I have a SQL server multi-user database. I want my users to connect to this databsase via Access 2000 Data Project. No problem there. The database consists of one main table and several views (based on the office branch that the user works from). For example there is a Chicago view, an Atlanta view, etc. that all extract different records from the same underlying table. I need my users to have FULL ACCESS (select, update, delete) to their respective VIEWS, but they cannot have access to the underlying table. I've tried several configurations and I'm beginning to think that this may not be possible... is that the case?
If it is not possible to grant access to views but not the underlying table, then what are my other options? The objective is to have a multi-user table that each user "owns a piece of" without being able to see the tables or records belonging to their peers. Do I need to setup a table for every office, and somehow link those tables into one main table? How would I avoid duplicate records being entered into the separate tables? Any help would be GREATLY appreciated, as this problem has had me stumped for weeks.
When I check the totals for various dimensions in the database which is used for my cube, I get the correct results (I have tallied them with the results from my OLTP)...
But when I browse my cube (using the same parameters, or should i say dimension combination) i get different results as compared to what I mentioned earlier :confused:
I have 2 data sources that have recently been updated from SQL Express to full versions of 2005. The connection strings have been changed, and the changes appear in the code, but the data source designer still shows the SQLExpress portion of the connection string. This seems to be fouling up SSIS packages that are using these data sources. Has anyone else encountered this? If so, what can I do to fix this issue?
Today while trying to get the Windows NT security working i upgraded my reporting services IIS server to a domain controller and it screwed up all of my settings. First it changed password for ASPNET user and so I reinstalled .net framework.
Now i get the
The underlying connection was closed: Could not establish secure channel for SSL/TLS.
error message.
I do not want to use an SSL certificate for I do not have one right now. How can I configure my SQL Server 2000 Reporting services to not use SSL. I see they added something to SQL Reporting Services 2005 in the configuration tools but I cant find out how to modify the config files with SQL 2000 RS to eliminate the SSL.
Please help, my company is down waiting for me to get this back up.
I am trying to develop a cube with translated calculated measures in it. I have also translated the all underlying measures/dimensions being used in action for a header.
I can see my translated measures and dimension in excel but when I go to Underlying data, I see default member's names not the translated one. Please note that I have translated all underlying members.
i have restored site using stsadm, site get successfully restored. all user of the site able to access the site but the unable to access from same machine on which it is restored. i got the error The file exists. (Exception from HRESULT: 0x80070050)
I'm having a real hard time coming up with a solution to this problem. I created a custom gridview control from Dino Esposito's "Extending Gridview" article which autogenerates a checkbox column that allows for multiple record selection. Once a user checks a box, the entire row gets selected. I added a dropdown list on the top of the page that has only two options, "Yes" or "No". What I'm trying to do is update a boolean column called "contract" (I'm using the Pubs sample database) for all selected rows (via checkboxes checked) depending on whether the user selects "Yes" or "No" from the drop down menu. For example,1) the user selects "No" in the dropdown2) The user checks all rows in the checkbox column for which he wants the all the values"contract" field set to "No"3) The user then clicks on a button called "Submit" and all selected records get updated to "No" under the "contracts" column. The idea is to allow the user to change the boolean values from a field for multiple records. Hence, making individual cells editable is pointless. Anybody have an idea how to go about this?
We have a mature instance of SQL Server 2000 reporting services that has stopped working for no apparent reason. When browsing to http://reportserver/reports we receive the following error
Error
The underlying connection was closed: Unable to connect to the remote server.
Home
The installation is running on three servers - 1 sql cluster (sql 2000 sp4) and two IIS servers all running windows 2000 sp4.
The Reportserver website runs using identity impersonation using the .net machine.config file pointing to an encrypted registry key entry holding the userid and password. It has been moved from the default website to it's own website but has been working with no issues for years. No obvious patches have been applied or security changes made that could effect the application
When browsing to http://reportserver/reportserver we can view the loaded reports and even run them with no issues, only ther report manager isn't working.
I have tried changing the impersonation registry key ( using aspnet_setreg ) and the account the reportserver web service runs under ( using the rsconfig utiltity) to be a domain admin to no effect and I have checked and set specififc permission for the reportserver database on all registry keys to no effect.
None of the online solutions for this issue have worked for me. Any help or ideas would be greatly appreciated
I want to grant access on the below view for an end user so that he connect to our SQL server and retrieve data. The view looks like the below
CREATE VIEW DB1.[dbo].[View1] AS -- For brevity, I made it as simple statement. SELECT * From DB2.dbo.table2 GO
For the above view, it looks like I have to grant select and connect permission for the DB1. [dbo].[View1] as well as DB2.dbo.table2.
1. Is my understanding correct?
2. I want the user to access only DB1. [dbo].[View1] and not the underlying tables. Is there a way to grant access only on the view and execute the statement on a different security context so that the user can€™t access DB2.dbo.table2 directly?
3. When the user uses SQL Server Management Studio to connect to SQL server, he is able to connect and select DB2.dbo.table2 directly. Is there any way to restrict user from viewing and executing select statement on DB2 database from SQL Server Management Studio
I am trying to setup SQL Server 2000 Reporting services with an SSL connection. The SQL Server (Windows Server 2003 SP2 Standard) and Reporting Server (Windows Server 2003 R2 SP2 Standard) are on separate machines. I have tried setting up Certificates from both and Stand-Alone and Enterprise CA. I have tried several combinations of the Issue To name (server | server.company | server.company.local) I have modified the rsWebApplication.config and rsReportServer.config to match the certificate€™s Issue to exactly. I have tried installs with both Domain Accounts and NT AUTHORITYSYSTEM logins. I have managed to get https://server/ReportServer to work but I have had no luck with the report manager https://server/reports. How do I fix this error?
We have on demand snapshot replication set up between 2 servers. When the subscriber applies the snapshot, our stored procedures start executing very slowly. Updating statistics and rebuilding indexes does not resolve the problem, however; executing sp_recompile on the affected stored procedures does fix the problem. Is this a known issue with replication? Is there a better workaround than manually recompiling stored procedures after every snapshot?