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.

View 1 Replies


ADVERTISEMENT

SQL Job Stopped But Underlying File Remains 'IN USE'

Jul 23, 2007

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.



thx very much.



seth j hersh

View 1 Replies View Related

Accessing The Header In The CSV File

Mar 20, 2008

Hi,

I am trying to load data from a csv file into a sql table. The csv file has 30 columns and 40-50 rows.

Here is an example of the csv file
Header: Painting, DryWall, Flooring
Row1: 100, 200, 300


Here is an example of the Department lookup table
DeptNo, DeptName
11, Painting
22, DryWall
33, Flooring

I have to load the sql table like this
DepNo, Cost
11, 100
22, 200
33, 300

I tried to read the csv with/without headers, couldn't find a way to get the header as I was reading through the rows to get to the DeptNo.

Any ideas. Any help is appreciated.

Thanks.

View 6 Replies View Related

Accessing A Database File

Sep 5, 2006

Hi,

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

View 3 Replies View Related

Issue Accessing File In Lan

Feb 11, 2008

Hi,

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?

Thanks
Saravanan Kanagaraj

View 9 Replies View Related

Problem Accessing SDF File Larger Than 128 MB

Feb 20, 2007

Hello.

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?

Thanks so far.

View 7 Replies View Related

Accessing Script File On Local Computer

Apr 13, 2004

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).


Thanks

View 2 Replies View Related

About Accessing SQL Server2005 Database File From A Remote Computer

Jun 5, 2007

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

View 2 Replies View Related

Accessing Environment Variable In Flat File Connection Manager

Apr 11, 2008

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.

Am I approaching this the right way?

View 9 Replies View Related

Accessing Data From VB6 - Query Builder's

Jan 7, 1999

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.

Thankyou whoever helps

Paul

View 1 Replies View Related

Programmatically Accessing An SQLDataSource With A SELECT COUNT(*) Query.

Jun 20, 2007

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?  

View 6 Replies View Related

Accessing Estimated Query Execution Plan (QEP) Statisitics

Jul 20, 2005

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.

View 2 Replies View Related

How Do I Write SQL Query Accessing Multiple Databases (DBFs)?

Aug 8, 2006

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.
 

View 5 Replies View Related

Accessing Query Execution Plan Results Programmatically

Mar 19, 2007

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.

Thanks -- MT

View 3 Replies View Related

Query Server Error When Accessing Visual FoxPro 9.0 Via ODBC

Nov 6, 2007



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..

View 1 Replies View Related

ODBC/OLEDB Failure Accessing MS Access Attachment Field In Query

Mar 12, 2008

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.

Thanks,

Joe

View 1 Replies View Related

Calculate The Values From A Underlying Table

Jan 17, 2007

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?

View 6 Replies View Related

Triggers On Tables Underlying A Partitioned View

Jul 23, 2005

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.

View 4 Replies View Related

The Underlying Connection Was Closed: The Remote Name Could Not Be Resolved

Jan 21, 2008

Our system administrators have renamed the server that our Reporting Service 2000 runs on. The database that RS2000 uses is on the same server.

Now when I navigate to the Report Manager, the following error appears:

The underlying connection was closed: The remote name could not be resolved


Obviously I need to tell Reporting Services 2000 the name of the new server (even though it's just the same server??). Where do I do that?

View 1 Replies View Related

Analysis :: How To See Underlying Tables And Joins Of A Cube

Nov 12, 2015

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).  

Is it possible I don't have permissions?

View 2 Replies View Related

View / Underlying Column Definition Metadata

Mar 18, 2008

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.

CREATE VIEW [dbo].[Branch]
AS
SELECT

divbra_id BranchID,
cmpny_id CompanyID,
divbra_cde BranchCode,
divbra_nme BranchName

FROM MyDatabase.dbo.divbranc

GO

is an existing view. I want to pull out the following metadata:

divbranc divbra_id Branch BranchID
divbranc cmpny_id Branch CompanyID
divbranc divbra_cde Branch BranchCode
divbranc divbra_nme Branch BranchName

Is there anyway to get this from SQL metadata without actually parsing the view SELECT statement in code?

View 7 Replies View Related

Tables And Underlying Views W/ Access Data Projects

Dec 3, 2001

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.

View 2 Replies View Related

Results From Cube And Underlying Datamart Are Not Matching - Urgent Please

Apr 14, 2004

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:

Please guide/help.

View 2 Replies View Related

Inconsistency Between Data Source Designer And Underlying Code

Mar 28, 2007

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?

View 4 Replies View Related

The Underlying Connection Was Closed: Could Not Establish Secure Channel For SSL/TLS.

Nov 20, 2007



Help.

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.

Thanks a ton,

deep

View 3 Replies View Related

Can A View Be Used To Modify More Than One Underlying Table With A Single Statement In MS SQL?

Mar 20, 2008

What's the answer for this and how ? If it is yes then How ?

View 4 Replies View Related

Analysis :: Calculated Translated Measures - Underlying Data

Oct 7, 2015

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.

View 4 Replies View Related

Error In Accessing Site Which Is Restored Using STSADM. Error-The File Exists. (Exception From HRESULT: 0x80070050)

Oct 8, 2007

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)

View 1 Replies View Related

Editing Underlying Boolean Data Without Making Cells Editable

Oct 5, 2007

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?

View 3 Replies View Related

Sql Server Reporting Services Error The Underlying Connection Was Closed

Sep 18, 2007



Hi,


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


Cheers


Daniel Worby

Database Administrator

AMP Capital Investors

(p) +61 2 9257 1746

(m)+61 434 327 150

(f) + 61 2 9257 1090

(e) daniel.worby@ampcapital.com

(w) www.ampcapital.com.au

View 1 Replies View Related

Permission On View. Is There Way To Avoid Granting Persmission On The Underlying Table?

Jul 13, 2007

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

Thanks in advance for your help

With regards
Ganesh

View 5 Replies View Related

The Underlying Connection Was Closed: Could Not Establish Trust Relationship With Remote Server.

Sep 21, 2007


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?


Thankyou

View 1 Replies View Related

Stored Procedure Doesn't Recompile After Replication Updates Underlying Tables

Mar 21, 2007

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?

View 3 Replies View Related







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