I recently installed the Teradata .NET data provider and I am trying to use named parameters and multi-value parameters in my SQL. I am finding information on this topic hard to come by.
In order to get parameters to work do I need to write my own Teradata data extension from scratch, expand an existing data extension to work with Teradata, or can I simple edit my .config files to point to a generic extension wrapper already existing?
I'm trying to convert old DTS packages over to SSIS and also create new SSIS packages that use teradata as a warehouse source for many different departments.
So far i've tried what I can find here on the forums:
using special syntax and wrapping the sql command in an alias/variable name
Methods i've tried: using an ODBC connection string that works just fine in VB for pulling data. - test connection succeeds but when I select data reader source I get:
Error at Data Flow Task [DataReader Source[185]]: Cannot acquire a managed connection from the run-time connection manager
Using an OLE DB connection where I get the standard error many have gotten and I tried the listed use special syntax and wrapping the sql command in an alias/variable name from : MSDN Error at Data Flow Task [Source - Testing]: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "OLE DB Provider for Teradata" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Currently i'm testing/trying this on a very simple view that produces 2 columns and 173 rows of string data. I have not tried the .net provider and am reluctant to accept that as the only solution if it works as it would involve permissions issues and red tape here at the bank and is a less user-friendly solution for my dev team.
Any ideas?
Driver version on my machine and likely the other 5 that would need this to work: 3.03.00.01 12/17/2003
I am extracting data from an SQLServer database to load into Teradata using DTS. The performance is abysmal. The same data in a text file loads quickly via multiload. I can move the data to other DBMSs via DTS quickly as well. Is there some way for me to improve the elapsed time/performance while using DTS? If not, what is the best way to move data from SQLServer into Teradata?
Does anyone have any experience setting up paramters when using a Teradata data source? I am specifically looking for how to set up dates and multi-select drop down boxes as parameters. Using a fixed query against Teradata using SSRS is straight forward and is working well. Parameters, not so much.
Recently I installed the .NET data provider for Teradata. In a regular C# application I can add the namespace, "using Teradata.Client.Provider;" to connect to this data provider directly without going though ODBC or OLD DB so I know it works.
However, when I open Reporting Services (new project -> Business Intelligence Projects) I do not see how to add the Teradata .NET provider. I only see the same standard data types as before. I have searched and I do not see how to add the Teradata .NET provider to the available "Type" list when you click €œAdd New Data Source€? to your report. Thanks!
I am fetching large amount of data from teradata to sql server using linked server. I am facing below query:
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)
I am fetching large amount of data from teradata to sql server using linked server. I am facing below query:
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)
My query should be pretty simple to answer by the gurus. I have SQL Server Express Advance series installed at my system along with VS 2005. I tried the reporting services (remote mode) to display a report in the report viewer on my aspx page. The report simply uses an SQL query to get the result. Everything works fine and dandy!
However if i want to get data from my own data set, i used a webservice and ussed the XML data source option in the report to traverse through the XML. In the preview mode of the RDL everything works fine however when i deploy or rather run it using report viewer on my webpage, it gives an error that 'XML' has not been registered as an extension etc.
I looked in the rsserver config file, it IS configured. The only answer i have is that the EXPRESS version of SQL only supports the regular SQL data sources. I do not understand if that was the case, why does the preview mode work fine??
If i am correct then for the next release the ms developers should disable that or rather give a more meaning full error. I spent an entire day fideling around just to find out that it isnt supported with debates all over the place as to why in the world does it work in the preview mode?
Anyhoo, to get around that issue, i am wondering if the Custom Data Extension would work on the SQL Express or not? I am assuming it wouldnt. Please clearify this. Ideally all i want is to access my own data set in the report, no SQL queries or whatever, CDE seems the way forward but does SQL Express allow it?
Many thanks guyz.
Regards
Zeeshan (btw, its only been two days since i started working with reporting services, so be gentle :-D )
I have written a Data Processing extension for my application and can deploy it on my development machine no problems. My question is: what is the "correct" way of deploying an extension to an end user's machine? Do I have to write a special program to find and modify the Reporting Server config files and copy the extension over. Surely many developers have the same need so there must be a generic solution to this problem, however, I haven't managed to find one.
I first came across this issue in SQL2000 and I thought/hoped it would be rectified with SQL2005 but it appears not to be (unless I'm missing something).
I'm writing a Data Processing Extension for SSRS 2005 that allows you to use a WSS 3.0 List as a dataset. This works for one list per dataset. Now I am trying to extend the extension to allow multiple lists in a dataset because I want to join lists on a common field (INNER JOIN). The problem is that I can only return one DataTable to the Report Server with the DataReader. Does anyone know if it is possible to return multiple DataTables (without the join)?
I'm a student, working on this project. I know Visual Basic.net but only started last month with Data.
In SQL 2k5 every db object say table , table fields etc have extension properties . I plan to populate these extension properties with meta data related information like field description , string length , case conversion , field alignment , enum options etc .
I wish to know if anybody has done, anything like this . Can this properties be easily avaialble in .Net development , reporting services , BIDS etc . Can there be significant advantages wrt this .
I have been getting to grips with writing a custom data processing extension for using a supplied dataset. I have got it working OK so far, but I don't see how to make use of relational data. There's plenty of code for walking through the fields, but nothing to walk through the tables. Since this clearly has to be possible can someone tell me where I'm going wrong?
I build a CDE on the base of the FSi-Example. Deploying worked fine
here is the problem: I build a new shared data source, but can't edit the connection string. the button disables itself. why? Therefore I copied the string from an existing datasource. I supplied the auth-data and all seemed to be fine. when i want to build a dataset with this datasource, I get something like this: "no connection to database possible check connection string and test connection. "
But this is not possible because of the disabled edit button.
Can anyone help please? What's wrong?
dev-pc and server are not the same machine.
I made the following changes to the config-files:
rspreviewpolicy.config
Code Snippet
<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="CDECodeGroup" Description="Code group for my Custom data processing extension"> <IMembershipCondition class="UrlMembershipCondition" version="1" Url="C:ProgrammeMicrosoft SQL Server80ToolsBinMicrosoft.Samples.ReportingServices.CustomDataExtension.dll" /> </CodeGroup>
RSReportDesigner.config
Code Snippet
<Extension Name="CDE Test Ext" Type="Microsoft.Samples.ReportingServices.CustomDataExtension.CustomConnection,Microsoft.Samples.ReportingServices.CustomDataExtension"/>
<Extension Name="CDE Test Ext" Type="Microsoft.ReportDesigner.Design.GenericQueryDesigner,Microsoft.ReportingServices.Designer"/> rssrvpolicy.config
Code Snippet
<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="CDECodeGroup" Description="Code group for my Custom data processing extension"> <IMembershipCondition class="UrlMembershipCondition" version="1" Url="C:Program FilesMicrosoft SQL ServerMSSQLReporting ServicesReportServerinMicrosoft.Samples.ReportingServices.CustomDataExtension.dll" /> </CodeGroup> RSReportServer.config
Code Snippet
<Extension Name="CDE Test Ext" Type="Microsoft.Samples.ReportingServices.CustomDataExtension.CustomConnection,Microsoft.Samples.ReportingServices.CustomDataExtension"/>
I have following all the steps but I am getting this error:
--------------------------- Microsoft Visual Studio --------------------------- The designer extension DATASET could not be loaded. Check the configuration file RSReportDesigner.config. --------------------------- OK ---------------------------
I have doubled checked the edits I made to that config file. Rebooted, relaunched, etc. No matter what, I cannot get past that error message.
I am using a Win XP machine with SQL Server 2005 Express Edition with Advanced Services SP2. Just curious, but does express version support CDEs?
i have sql server 2000 db with two data file... primary data file has extension mdf and secondary file has extension ndf (as per microsoft recommendation)..
when i try to backup the db and restore thru the enterprise manager .. in the restore -> options window ... i see both the files has the same extension mdf.. and when the restore completed, the new database still has extension mdf for both the file..
why this behaviour?
* i even try to create a new test db with two files, still its the same behaviour.
I try to add an data extension who take the information to connect to the database in a config file. But It's could not retreive the values corresponding to the key.
I'm using the class ConfigurationManager from the namespace System.Configuration.
ConfigurationManager.AppSettings["QueryModel"]
Without the config file, the extension is working well.... I got the impression that reporting service and Visual Studio BI don't find the config file.
If someone can help me or have some advices. I will be pleased
I am running SQL Express Advanced addition on an XP pro box. The install went cleanly, and I have no problems creating and publishing reports that use a SQL server data source, however when I try and publish a report that uses an oracle data source I get the following error.
An attempt has been made to use a data extension 'ORACLE' that is not registered for this report server.
The report works fine withing the preview pain of Visual Studio, and it publishes fine within SQL 2005 standard edition.
Can someone please tell me how to register the data extension for ORACLE in SQL Express.
I am using an OLEDB data source connecting to an Access database. Using SQL Server 2005 SP2 Workgroup Edition.
I can build the report fine and view it in Visual Studio. When I deploy the report and try to run it on the Report Manager site I get this error
An error has occurred during report processing. (rsProcessingAborted) An attempt has been made to use a data extension 'OLEDB' that is not registered for this report server. (rsDataExtensionNotFound).
When I go to the Shared Data Source, this message appears beside the Connection type field
The data processing extension used for this report is not available. It has either been uninstalled, or it is not configured correctly. What am I doing wrong??
Can anyone tell me what the heck this is trying to tell me. I have 3 datasets I'm working with so there are errors for each here:
[rsMissingFieldInDataSet] The data set €˜Revenue_By_Client€™ contains a definition for the Field €˜Branch€™. This field is missing from the returned result set from the data source.
[rsErrorReadingDataSetField] The data set €˜Revenue_By_Client€™ contains a definition for the Field €˜Branch€™. The data extension returned an error during reading the field.
[rsMissingFieldInDataSet] The data set €˜Main_Dataset_AZ€™ contains a definition for the Field €˜PostedAmount_InHouse€™. This field is missing from the returned result set from the data source.
[rsErrorReadingDataSetField] The data set €˜Main_Dataset_AZ€™ contains a definition for the Field €˜PostedAmount_InHouse€™. The data extension returned an error during reading the field.
I need help about this , What i've done so far is a data processing extension that gets its data from a WS , I use it as a datasource for a report , That report will be displayed withing an ASPX page containing a reportviewer control that displays that report , The problem is catching errors that occurs withing the data processing extension .. ,
When i debugged the extension i saw that after throwing an exception the code ends up inside the "cancel()" event inside the IDBcommand implementation...
In all examples for a data processing extension i could find online they always have a comment saying the cancel is not implemented and throw a NewUnsupportedExtension()..
What happens is i get some uninformative , non-userfriendly error from the reporting services , something like "an error occurred during the report processing(rssomehing)"
How should i go about handling those exception that happened inside the data processing extension?
I've tried catching the error inside the event of the reportviewercontrol - reporterror but it seems that event don't fire when those errors are generated..
Any help on how to approach this would be highly welcome
I am using this custom data extension from BI Studio.
Que1: I want to create report parameters from BI studio and capture them in my Data Extension.
Que2: Every report parameter has so many properties, like hidden, internal, default value etc, how do capture that information in my data extension, the parameters only give me name and value.
Que3: I know I can capture command Text. So My command can be Select * From tablename where fieldname= @myFieldname. Once I get this commandText in my data extension, How do extract parameters of the command?
Que4: If I have any custom attributes in my RDL file, Can I capture them in my Data Extension ?
I've created a custom Data Processing Extension and I've implemented the IDBCommandAnalysis interface so that my reports can enter parameters and pass them to my Data Processing Extension.
My question is, how do I extract the value from the Parameters coming from the report? Where do the parameters get passed off from the report? I can query the Parameters collection and my report gets prompted in Preview mode to enter something for the parameter but I can't find the spot where it gets passed for processing.
I have written CDE to retrieve and process data before generating the report. It works very well inside VS.Net. However, when deployed to the report server I get the following error
An error has occurred during report processing. Cannot create a connection to data source 'CallsTaken'. Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
I have added appropriate CAS entry in rssrvpolicy.config file as following
<CodeGroup
class="UnionCodeGroup"
version="1"
Name="CustomDataExtensionCodeGroup"
Description="Code group for the Custom Data Extension"
I am attempting to deploy a sample report (SQL 2005 Workgroup w/SP2) and I am receiving the subject error message when I attempt to view the report on the web server. We are using SQL Server 2005 Workgroup Edition connecting to an Oracle database.
Is this a limitiation of this version? In report designer, I am able to select Oracle as a connection type. If I try to create a data source at the server, "Microsoft SQL Server" is the only option available. If I look at the ReportServer config file, I see many other data connection types available. Did I miss a step, or is this a Workgroup Edition limitation (it's not documented as a limitation that I am able to find)?
Using SSRS 2008R2 is it possible to change the file extension of a CSV data driven subscription? Â I'm outputting a text file with a .csv extension, but users have access to these files and I don;t want them opened with Excel and then saved back in an incorrect format.Â
These are the options I have from the report "manage" option. Â
I am using Custom Data Processing Extension to call a stored procedure. Iam getting following error when creating a dataset in report designer using the extension. I wrote the code in c#.
could not update a list of fields for the query. verify that you can connect to the data source and that your query syntax is correct.(Details-Object reference not set to an instance of an object.)
Here is my code
using System; using System.Collections.Generic; using System.Text; using System.IO; using System.Data;
I am having a question as stated in the subject title, yes, when we want to deploy scorecards to reporting services, as the prerequisite, how can we install and register the scorecard custom data processing extension with the Microsoft reporting services server?
I am looking forward to hearing from you shortly and thank you again.
I am trying to query Teradata tables from SQL Server 2005 using SSIS that will populate a table in SS 2005. I have been able to test the connection successfully. I choose the Query option b.c the tables in Teradata are over 100 million records and I don't want to copy those tables locally.
I am able to enter my query and parse it. I can even preview the query and see the results. A very simple query:
select distinct acc_yr from d4574fdp.clm_dim
But when I try and execute the query, I get the following error message:
quote: TITLE: SQL Server Import and Export Wizard ------------------------------
Could not connect source component.
Error 0xc0202009: Source - Query [1]: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "OLE DB Provider for Teradata" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
