Stored Procedures Not Possible As Data Source View?
Apr 3, 2007only the tables and views are shown in the wizard (BIDS) thanks...
View 1 Replies
ADVERTISEMENT
Can You Use A Stored Procedure In A Data Source View?
Aug 2, 2007Is it possible to use a stored procedure in a Data Source View? When I use the wizard to create the Datasource View, all it lets me choose is tables and views in the database. Am I missing something, or trying to do something that is impossible?
View 8 Replies View RelatedSELECT Data: Stored Procedures Or View?
May 10, 2007Hi, I'm developing a fresh SQL DB which is result of a deep analysis of an old Access DB. THe thing is, this old one had very complex consultations to the Access tables, and some consultations were using another consultations as way to select some specific data. THe ideia in SQL is to avoid that too, however, there are some data that may serve exactly the same to some bigger stored procedures. This way, I have three options I guess: 1. Create every stored procedure making select queries directly to the table and it's done!2. Create auxiliary stored procedures which will select the redundant data, and when it is needed, another stored procedures call this one and use its returned data. (is this possible anyway?).3. I create a view to this redundant data, and the greater depth stored procedures access this data of the view when needed. I've heard, however, that a select to a view is slower than directly to the table, once the view adds an extra query to the process.. What is your guess on this issue of mine?I'm almost sure that option 1 is the best, however, I'd love to hear from you guys your opinion on this. The greater issue above this all is just one - performance. Thanks a lot!
View 3 Replies View RelatedLoading Data Using Ole Db Source With Input Source Being A View
Dec 13, 2007I was trying to load data using SSIS, Data Flow Task, OLE DB Source, source was a view to a OLE DB Destination (SQL Server). This view returns 420,591 rows from Query Analyzer in 21 seconds. Row length is 925. When I try to executed the Data Flow Task from SSIS, I had to stop the process after 30 minutes, because only 2,000 rows had been retrieved. I modified the view to retun top 440, 000 and reran. This time all 420, 591 rows were retrieved and written in 22 seconds. Next, I tried to use a TOP 100 Percent. Again, only 2,000 rows were return after 30 minutes. TempDB is on a separate SAN Raid group with 200 gig free, Databases on a separate drive with 200 gig free. Server has 13 gig of memory and no other processes were executing.
The only way I could populate the table was by using an Execute SQL Task and hard code an Insert into table selecting data from the view (35 seconds) from SSIS.
Have anyone else experience this or a similar issue? Anyone have a solutionexplanation?
Amo And Creating Data Source And Data Source View Code
Feb 2, 2008,
Hi
In this code how can I create a new data source and new data source view and model and structure that it run dynamic.
In this code I have a lot of errors, that they are about server and database don€™t have in current code,
In this code, first I should definition server or no?
Database dbNew = new Database (databaseName,
Utils.GetSyntacticallyValidID(databaseName, typeof(Database)));
srv.Databases.Add(dbNew);
dbNew.Update(true);
***********************************************************
How can I create data source and data source view and model and structure?
Please say code of that, and guide me.
databasename and srv is unknown.
Do I add other reference with analysis services?
Please explain about these codes:
************************************************************************
1)
RelationalDataSource dsNew = new RelationalDataSource(
datasourceName,
Utils.GetSyntacticallyValidID(
datasourceName,
typeof(RelationalDataSource)));
db.DataSources.Add(dsNew);
dsNew.ConnectionString = connectionString;
dsNew.Update();
2)
RelationalDataSourceView rdsv;
rdsv = db.DataSourceViews.Add(
datasourceviewName,
Utils.GetSyntacticallyValidID(
datasourceviewName,
typeof(RelationalDataSourceView)));
rdsv.DataSourceID = ds.ID
***************************************************************
3)
OleDbConnection cn = new OleDbConnection(ds.ConnectionString);
OleDbCommand cmd = new OleDbCommand(
"SELECT * FROM [" + tableName + "] WHERE 0=1", cn);
OleDbDataAdapter ad = new OleDbDataAdapter(cmd);
DataSet dss = new DataSet();
ad.FillSchema(dss, SchemaType.Source);
*************************************************************
4)
// Make sure we have the name we thought
dss.Tables[0].TableName = tableName;
// Clone here - the original DataTable already belongs to a DataSet
rdsv.Schema.Tables.Add(dss.Tables[tableName].Clone());
rdsv.Update();
5)
MiningStructure ms = db.MiningStructures.Add(miningstructureName, Utils.GetSyntacticallyValidID(miningstructureName,
typeof(MiningStructure)));
ms.Source = new DataSourceViewBinding(dsv.ID);
ms.CaseTableName = "Customer";
Add columns:
ScalarMiningStructureColumn smsc;
// From table "Customer" we will add a couple of columns
// CustomerID - key
smsc = new ScalarMiningStructureColumn("Customer ID",
Utils.GetSyntacticallyValidID("Customer ID", typeof(ScalarMiningStructureColumn)));
smsc.IsKey = true;
smsc.Content = "Key";
smsc.KeyColumns.Add("Customer", "customer_id", OleDbType.Integer);
ms.Columns.Add(smsc);
*******************************************
6)
MiningModel mm = ms.MiningModels.Add(miningmodelName,
Utils.GetSyntacticallyValidID(miningmodelName,
typeof(MiningModel)));
mm.Algorithm = "Microsoft_Decision_Trees";
mm.Parameters.Add("COMPLEXITY_PENALTY", 0.3);
MiningModelColumn mc = new MiningModelColumn("Customer ID",
Utils.GetSyntacticallyValidID("CustomerID",
typeof(MiningModelColumn)));
mc.SourceColumnID = ms.Columns["Customer ID"].ID;
mc.Usage = "Key";
mm.Columns.Add(mc);
mm.Update();
Please exactly say, whatever I want
Thanks a lot for your answer
Please don€™t move this question because I don€™t know where I should write this.
Create Data Source, Data Source View From XML?
May 8, 2007hi everybody,
i want to create data source and data source view for data mining, with using C Sharp.
i have create data source and data source view and export to XML file, but when i change to another computer, run those XML file, it return error, when i run statement to create and biuld mining model, what can i change on xml or how to run XML on another computer sucessfully,
and have i build data source and data source view, how to do it.?
thanks for you helps
Stored Procedures And Source Control
Oct 18, 2005We have a large team working on applications to support our internal sales and our public dotcom site. In the process, as you can imagine, we generate a lot of stored procedures and sql scripts going to our dba's for our staging,qa and production environments- but we don't have a solid way to manage it yet. Some people use Source Safe? Does anyone have any successes in this area or recomendations?Thanks!s!Stephen Rylander
View 2 Replies View RelatedWay To View All Stored Procedures
Jul 20, 2005I have MS SQL Server 7 and over the years have built quite a fewStored Procedures. It would be extremely convenient if I could print outor view ALL the text of ALL the stored proceudres at once. Is there a way ican do this? Is tehre a way I can make a quick print out?
View 4 Replies View RelatedView Or Stored Procedures
Jul 20, 2005I have a very complex view which we've been working with for sometime. As we have progressed with this view, we have reached the stagewhere the data is generated into a table which is then used in ourreporting application. This has improved reporting performanceconsiderably.There is a stored procedure which copies the data from the view into atable. Very simple select into statement.However, I was wondering if I actually held the query code in thestored procedure, would this improve performance ?The only user for this would be the server for getting to the data inthe view and generating into a table.I know it's a very general question, but would I see any performancedifference doing this as an SP instead of an SP calling a view ?Would appreciate any constructive comments. Not posting the view asit'll be too long, but FWIW, using SQL 7.
View 1 Replies View RelatedStored Procedures In Visual Source Safe
Nov 15, 2007
I have about 40 stored procedures in a Visual Source Safe stored procedures which we are using for change control.
All stored procedures require to be moved into production across at least 13 different databases each on a dedicated server.
The only way I know at the minute is to do this manually. Does anyone out there have any ideas of how this task can be automated?
View Stored Procedures With Transact SQL
May 25, 1999Hi
Does anyone the syntax or command to View Stored Procedures with Transact SQL statement. I have tried using the syscomments but there is some information missing.
I would appreciate any reply !
Thanks you for your time.
How To Grant Permission To View Stored Procedures?
May 21, 2007On our production SQL 2005 servers I want to give developers readonly access to each user database and also give them the ability to see stored procedures. Readonly is handled through db_datareader, but how do I give them the ability to see stored procedures without granting permission to execute them?
Thanks, Dave
Access 2007 Runtime: Stored Procedures As Record Source
Feb 15, 2008
Hello
In our company, after switching to Office 2007, Access 2007 runtime is installed on our general user machines.
Everything is just fine and beautiful, except that there is one issue with runtime:
Several forms in my Access Project use stored procedures in SQL Server 2000 as their record source. When i try to open the forms in Access 2007 Runtime on user machines, i receive an error that "The record source dbo.Myprocedure speified on this form or report does not exist ".
I use dbo.ProcName format and the forms open correctly in my full version Access.
What could be the problem?
Thanks!
Elman
Strange Behaviour Of Data Source View
May 29, 2008
I have a fact table which is a custim query in the data source view, I process and work with the data, after somewhile I make some changes in the data source view query but all my changes does not reflect in the cube after processing the same old data returned.
please help ASAP, I want to fix this issue
Report Models Data Source View
Apr 30, 2007How does the report model know what data source view to use? I could not find it defined anywhere in the .smdl file.
My problem is this. I have a Report Model project with two data sources, two data source views and multiple report models. When I try and bind a data source to an entity in the report model I do not get to choose which data source view to use to choose what table/view I want to bind the entity too and only the tables in one of my DSV's shows up. When I first created it, it worked fine. It automatically selected the correct view and table and was successfully created but now when I open the project, that correlation is lost.
Any suggestions or help is appreciated, thanks.
Data Source View Missing Fields
May 9, 2008Is there a limit on the number of fields that can be displayed in a table object in a data source view in SSAS 05?
One of the tables (the fact table) in my data source view is displaying only 50 fields. The table actually has many more than that. One of the fields that is not displaying is a foreign key that I need to link to a new dimension table. I have tried refreshing the view, but it doesn't bring in any additional fields.
Dynamic PIVOT Table As Data Source View
May 29, 2008I would like to use a dynamic pivot table in my data source view. It seems that a named query can be only one sql statement. So, I cannot use my multi-statement procedure that creates a dynamic pivot table output.
What is the best course of action here? I could hard-code my pivot table query. I could maintain a redundant table in the pivot format. Do I have any good options?
KenS
Ken
Report Model (Data Source View) Issue
Jul 6, 2007We've built an oltp that uses nothing but synonyms that reach out to various DB's in the organization thru linked servers.
The issue that we are having is Data Source Views can only see tables and views. Is it possible to use synonyms? If not what would be a work around.
Please advise. Thanks
Steve
Cross Database Join In A Data Source View
Jun 21, 2007Hi,
Is it possible to do a cross database join in a report services data source view? It doesn't look like it.
If not I was thinking of linking the table into the other database.
TIA,
Darren
Data Source View Of Mining Structure Cant Be Edited?
Nov 23, 2006Hi, all here,
Thank you very much for your kind attention.
I got a quite a strange problem with Mining structure for OLAP data source though. The problem is: I am not able to edit the mining structure in the mining structure editor. The whole data source view within the mining structure editor is greyed. Could please anyone here give me any advices for that?
Thank you very much in advance for any help for that.
With best regards,
Yours sincerely,
How Do I Add A Data Source View And Report Model To An Existing Project
Feb 22, 2007I have an existing project with the data source and report folder with working reports. I need to create a model for other department members to use Report Builder, not Business Intelligence Developer Studio, to develop their reports from. How do I add a data source view and report model folder to the project so I can then create a view and model?
Analysis :: Possible To Create Multiple Data Source View Having Same Datasource?
Jul 27, 20151. As per my current development SQL Sever Analysis Database consists of two Cube (Cube A and Cube B).  Cube A and Cube B share the same data source view (DSV1). The source for both these cubes has the same data source (DS1).
2. As per the requirement I need to create third Cube i.e. Cube C. Is it possible to create a second Data Source View (DSV2). The Source of second Data Source View (DSV2) will be the same data source(DS1).
I am thinking to create second Data Source View (DSV2) for Cube C because existing layout of the DSV1 has become complex. I wanted to know the pros and cons of creating a multiple data source view with same data source
Using Data Source View In Import Export Package Within Same Solution Is Not Possible ?
Dec 15, 2005hello ,
I want to download data from an AS/400 to a SQL 2005 Database.
Within Sql server 2000 it was possible to do more than 1 transformation with just 1 source connenction and 1 destination.
In SSIS I have created:
- new solution
- data source
- Data source viewer ( I made named queries to format the data as i want it to be in Sql)
Although it is possible to view the named query data it does not seem to be possible to import the data in the data viewer into my sql 2005 database. (For each dataflow I have to define each query again)
I have added the data source in the connection manager.
Also I am searching to import more than 1 file in 1 package !
I can use however a Dts-2000 package but why then use SQL server 2005
Hope somebody can help me
Regard,
Ronny
Create Date Source View And Data Model That Works Right
Jan 22, 2008I created a data model for report builder. And since it wont let me use views, i tried to put all the tables im using in one of my already made views, to create a data source view like my sql view. But when i connect everything the same, my report model still doesnt narrow my search. I only want to see Breed information, but since i have a Breeder table,and Customer table, its showing me all customers, and not limiting it to the ones that are breeder customers.
heres the tables:
Code Snippet
FROM dbo.Tbl_Customer INNER JOIN
dbo.Tbl_Customer_Breeder ON dbo.Tbl_Customer.Customer_Code = dbo.Tbl_Customer_Breeder.Customer_Code INNER JOIN
dbo.Tbl_Customer_Detail ON dbo.Tbl_Customer.Customer_Code = dbo.Tbl_Customer_Detail.Customer_Code INNER JOIN
dbo.Tbl_Customer_Category ON dbo.Tbl_Customer.Customer_Category_Id = dbo.Tbl_Customer_Category.Customer_Category_Id INNER JOIN
dbo.Tbl_Customer_Classification ON
dbo.Tbl_Customer.Customer_Classification_Id = dbo.Tbl_Customer_Classification.Customer_Classification_Id INNER JOIN
dbo.Tbl_Customer_In_Breed ON dbo.Tbl_Customer.Customer_Code = dbo.Tbl_Customer_In_Breed.Customer_Code INNER JOIN
dbo.Tbl_Breed ON dbo.Tbl_Customer_In_Breed.Breed_Id = dbo.Tbl_Breed.Breed_Id
What am i doing wrong, and are there any suggestions.
Report Model/Datasource View, Primary Data Source Problem
May 23, 2007I have a datasource view DSV1. It points to a datasource DS1 that is considered the "primary".
I have created a Report Model that uses DSV1 (and thus uses DS1)
I created a new datasource, DS2 that I would like to use instead of DS1. (I can't just modify DS1 because if I modify it, it will overwrite it when we go to our Production environment and break that datasource)
So, I can go into DSV1 and change all the references from DS1 to DS2.
But that's where the problem lies.
When I try to build, I get the following error:
"The Table property of the Entity "E1" refers to the Table "dbo_View", which is not in the primary data source."
Somehow, the entity is tied to the "primary" datasource. When I change it back to DS1, everything works fine. Any thoughts? What can I do?
How To Refresh A Data Source View And Autogenerate/Re-Deploy The Corresponding Report Model
Jan 9, 2007Hi everyone!,
Our team is new to SSRS and we are trying to figure out how to refresh the data source view, autogenerate the corressponding report model and Re-Deploy the Report Model (rewrite existing Report Model) on the server on a periodoc basis or trigger the whole action whenever there is a change in the database on some specific tables.
We prefer to do this action through a Agent job ( a diff approach is also welcome).
Are there any stored procs out there?
Appreciate your time.
-PN
SSIS OLEDB Source Data Access Mode (Table Name Or View Name Variable)
Apr 3, 2007Thanks for any one can give me a help.
I am try to transfer some tables data from one database server into another database server. I create a package in SSIS, and I use a variable to pass each table name. In Data flow, I use a OLEDB Source, but I cannot set the Data access mode to Table name or view name variable. Ever time, I will get this following error info "===================================
Error at Data Flow Task [OLE DB Source [31]]: A destination table name has not been provided.
(Microsoft Visual Studio)
===================================
Exception from HRESULT: 0xC0202042 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args)".
Some one can tell me what is the reason, or give me some examples.
Thanks in advance.
Reporting Services :: Set Default Values For Selection List Navigator For Data Source View
Jul 14, 2015How to set default values for selection list navigator without using parametrized queries for data source view.
View 2 Replies View RelatedData Access :: Transfer Data From One To Another Table On Another Server Using Stored Procedures
Jun 9, 2013I have two database(MYDB1 , MYDB2) on two different server's(SERVER1 , SERVER2) . I want to create an store procedure in MYDB1 on SERVER1 and get some data from a table of MYDB2 on SERVER2. How can i do this?
View 5 Replies View RelatedDatabase Stored Procedures Not Showing Up In Database View
Jan 31, 2008Hi I have an application that I have started to develop. I have successfully set the connection to open the SQL Server database that I created. When I first started on the program, I was able to create Table Adapters by dragging the tables or stored procedures onto the DataSet work surface and going through the configuration process. At one point, however, I stopped being able to see any of my stored procedures in the database view although they are there because when I go to create a new table adapter, I am able to right click and add the new adapter and find the stored procedure in the wizard.Is there any way I can reset this so that my stored procedures are visible again. I have tried refreshing to no avail - and I think this is creating problems in other parts of the application.Any help appreciate.Roger
View 1 Replies View RelatedCan Anyone Tell Me How To Access Data From Stored Procedures Using Data Adapters?
May 21, 2008Can anyone tell me how to access data from Stored Procedures using data adapters? My task is to select a row which is valid with data particular value. Suppose i had to get all values of particular user after validating username and password. Can anyone give me some hint regarding store procedure and retriving data from stored procedure using data adapters ? How can i bind data to dropdownbox of one field in the table using datasets and data adapters? How can i insert data in database using data adapters?Can any one solve this?
View 1 Replies View RelatedGetting Data Fields In Stored Procedures
Aug 18, 2004Hello,
How can I get the name of the fields along with datatypes of a stored procedures.
Thanks in advance,
Uday.
Stored Credentials On Shared Data Source
Jan 5, 2007I'm trying to use stored credentials to enable caching. I've created a special windows user account with minimum permissions for just this task, and once set up, it works great (almost).
I can update the shared data source using SQL Mgt Studio or directly via the Report Manager to use a set of -windows- stored credentials. But I don't seem to be able to do the same via VS.NET 2005. I can only store -SQL- credentials, which I have no need to enable and no desire to add to my surface area.
The problem lies that every time I deploy any report that using that data source (which is nearly all of them), the data source is re-published, which wipes out the stored credentials and caching immediately stops.
I've tried messing with the XML directly, with no luck, and changing assorted advanced settings, but nothing seems to stick.
Obviously I can manually update the credentials each time I deploy something, but surely it seems that there must be a better way. Is there a way to either set the DS to use stored windows credentials, or just plain prevent the deployment of the DS every time a dependent report is published?
Suggestions?
Geof
P.S. SQL 2005 Standard Ed SP1 32-bit