Very Urgent : Bcp Or Dts Data To Partitioned Views
Jan 1, 2004someone please tell me if i can bcp or dts data in to partition views
it is failing with the error the bulk operation does not support this
ADVERTISEMENT
Help With Partitioned Views Or Updating Data From Multiple Tables
Mar 16, 2008Hi All,
My database's design is set out here. In summary, I'm trying to model a Stock Exchange for a Technical Analysis application written using Visual C++. In order to create the hierachy I'm using a Nested Set Model. I'm now trying to write code to add and delete equities (or, more generically, nodes) to the database using a form presented to the user in my application. I have example SQL code to create the necessary add and delete procedures that calculate the changes to the values in the lft and rgt columns, but these examples focus around a single table, where as my design aggregates rows from multiple tables using UNION ALL:
Code Snippet
CREATE VIEW vw_NSM_DBHierarchy -- Nested Set Model Database Hierarchy
AS
SELECT clmStockExchange, clmLeft, clmRight FROM tblStockExchange_
UNION ALL
SELECT clmMarkets, clmLeft, clmRight FROM tblMarkets_
UNION ALL
SELECT clmSectors, clmLeft, clmRight FROM tblSectors_
UNION ALL
SELECT clmEPIC, clmLeft, clmRight FROM tblEquities_
Essentially, I'm trying to create an updateable view but I receive the error "UNION ALL View is not updatable because a partitioning column was not found". I suspect that my design in wrong or lacks and this problem is highlighting the design flaws so any suggestions would be greatly appreciated.
Partitioned Views
Aug 23, 2006Hi,
I've starting to explore the Distributed Partitoned Views, in order to use it in the next project, and I've found the article:
"MS SQL Server Distributed Partitioned Views"
By Don Schlichting
I came across the following problem:
While running sample:
USE test
GO
CREATE VIEW AllAuthors
AS
SELECT *
FROM AuthorsAM,
TEST1.test.dbo.AuthorsNZ
GO
I got the error message:
Server: Msg 4506, Level 16, State 1, Procedure AllAuthors, Line 5
Column names in each view or function must be unique. Column name 'au_lname' in view or function 'AllAuthors' is specified more than once.
Could anyone please explain? Can't i use the same column names in both tables?
Regards,
Yifat
Partitioned Views
Aug 17, 2001I would like to break up a very large table into about ten smaller ones. With partitioning to be efficient the columns in the check constraint need to be used when accessing the view. The problem is the table has a composite primary key made up of LocationID/ProductID. With another composite index on ProductID/LocationID. This is accessed both ways from our applications.
I would like to partition the table by LocationID. But then when called by ProductID a scan of all tables in the view would have to be done.
In Oracle there is something called a global index that would solve this. Is there anything similar in SQL Server or does anybody have a work around?
Thanks,
Rob
Maintaining Partitioned Views
Jul 20, 2005Hello,I have a large set of data that I have set up as a partitioned view.The view is partitioned by a datetime column and the individual tableseach represent one month's worth of data. I need to keep at least twoyear's worth of data at all times, but after two years I can archivethe data. A sample of the code used is below. It is simplified forspace reasons.My question is, how do other people maintain the database in this typeof scenario? I could create all of the tables necessary for the nextyear and then go through that at the end of each year (archive tablesover two years, add new tables, and change the view), but I was alsothinking that I might be able to write a stored procedure that runsonce a month and does all three of those tasks automatically. It seemslike a lot of dynamic SQL code though for something like that.Alternatively, I could write VB code to handle it in a DTS package.So, my question again is, how are others doing it? Any suggestions?Thanks!-Tom.CREATE TABLE [dbo].[Station_Events_200401] ([event_time] [datetime] NOT NULL ,[another_column] [char] (8) NOT NULL )GOCREATE TABLE [dbo].[Station_Events_200402] ([event_time] [datetime] NOT NULL ,[another_column] [char] (8) NOT NULL )GOCREATE VIEW Station_EventsASSELECT event_time,another_columnFROM Station_Events_200401UNION ALLSELECT event_time,another_columnFROM Station_Events_200402GO
View 3 Replies View RelatedDistributed Partitioned Views
Jul 14, 2006Hi everyone,
I have some doubts about distributed partitioned views.
When we create a distributed partitioned view whcih include three server, do we have tocreate this same distributed partitioned view in that three server in order to make each server to see adn especially modify it ?
Thanks
Delete Statements In Partitioned Views
Dec 10, 2002Hi, I am using sql2000 ent edition. I have a partitioned view based on 8 tables. My selects and inserts are fine. But, when I run a delete on the view based on a query on the paritioned column, I get a "Transaction (Process ID 149) was deadlocked and has been chosen as a victim".
I looked at the query plan and it was showing a parallel query on all the underlying tables. So, I put the Option(maxdop 1), using only one processor and the delete worked fine.
Does anybody know why? is parallel query create deadlocks? is there any known problems with deletes on partitioned views?
same question for updates. I think I have the same problem for updates.
Any help will be useful.
thanks!!
Books On Lies - Partitioned Views
May 8, 2007From BOL...
CHECK constraints are not needed for the partitioned view to return the correct results. However, if the CHECK constraints have not been defined, the query optimizer must search all the tables instead of only those that cover the search condition on the partitioning column. Without the CHECK constraints, the view operates like any other view with UNION ALL. The query optimizer cannot make any assumptions about the values stored in different tables and it cannot skip searching the tables that participate in the view definition.
Then why am I getting index scans on my partitioning column on tables that fail the search value based on their check constraint?
Not looking for an answer because I know the query optimizer is a fickle b*tch and I did not post any code, but I needed to rant.
SQL SERVER 2000 Partitioned Views Bug
May 12, 2008Thanks in advance in reading this post ! I'm facing a situation in sql server 2000 sp4 with partitioned views.
I have a partition views that joins about 10 tables, in each table there is a check constraint.
For example, if a exec a select count(*) from VIEW where col1 = '20080101' , it goes for the table that has data for '20080101' .
If I exec a select col1,col2,col3,col4 from VIEW where col1='20080101', it goes to all tables and make an index seek.
I want the beaviour of query 1, beause it is just looking on 1 table and not one the 10.
Thanks in advance !
T-SQL (SS2K8) :: Local Partitioned Views
May 13, 2014SQL 2008R2, Standard on Windows 2008R2 Enterprise.I have implemented a set of local partitioned views to facilitate multi-threading in one of our applications.Testing with SQL and the insert, update, Delete all work fine. However the inserts from SSIS are Bulk and then fail.According to all I have read I should be able to add "instead of Trigger" for the insert and it should work fine. URL....
I have created instead of triggers for insert and delete and again they work fine when I test them with T-SQL but fail in the SSIS package. The error message is that the view is not updateable.
256 Table Limit For Partitioned Views
Aug 8, 2005I have a partitioned view sitting over several tables and I'm slowlyapproaching the 256 number. Can anybody confirm if there is such alimit for the maximum number of tables that a partitioned view canhold?If this is true, does anybody have any suggestions or ideas to workaround this max limit?TIA!
View 4 Replies View RelatedProblems With Partitioned Views And Pruning
Jul 20, 2005/*problem: Trying to get partitioned views to "prune" unneededpartitions fromselect statements against the partitioned view. There are 5partitionedtables. Each with a check constraint based on a range of formula_idcolumn.Test: Run this script to create the 5 partitioned tables and thepartitioned view. Thenrun the explain plans on the select statements at the end of thescript and see that wecan only prune if we give a seemingly superfluous is not nullcriteria in addition tothe formula_id.Ideal: We want to only have to use the formula_id in the selectstatement to prune.*//*note: you may get errors on the drops first time run*/drop table dbo.cs_working_e2goCREATE TABLE dbo.cs_working_e2 (formula_id int NOT NULLCONSTRAINT formula_id_e14CHECK (formula_id between 1and 1000),submission_id int NOT NULL,node_id int NOT NULL,reference_year smallint NOT NULL,observation_period datetime NOT NULL,authority_flag tinyint NOT NULLCONSTRAINT ONE_DEFAULT3436DEFAULT 1CONSTRAINT Binary_flag_rule667CHECK (authority_flag IN(0, 1)),interpolated_flag tinyint NOT NULLCONSTRAINT ZERO_DEFAULT6926DEFAULT 0CONSTRAINT Binary_flag_rule668CHECK (interpolated_flag IN(0, 1)),observation_value decimal_datatype NOT NULL,time_created smalldatetime NOT NULLCONSTRAINTCURRENT_DATE_DEFAULT1807DEFAULT getdate(),CONSTRAINT XPKcs_working_e2PRIMARY KEY NONCLUSTERED (formula_id, submission_id,node_id, reference_year, observation_period)--ON "INDEXES")--ON "WORKING"goCREATE UNIQUE CLUSTERED INDEX XAK1cs_working_e2 ON dbo.cs_working_e2(submission_id ASC,formula_id ASC,node_id ASC,authority_flag ASC,observation_period ASC,reference_year ASC,observation_value ASC)goCREATE INDEX XIE1cs_working_e2 ON dbo.cs_working_e2(node_id ASC,authority_flag ASC,formula_id ASC,observation_period ASC,reference_year ASC,observation_value ASC)--ON "INDEXES"godrop table dbo.cs_working_indexes2goCREATE TABLE dbo.cs_working_indexes2 (formula_id int NOT NULLCONSTRAINT formula_id_indexes14CHECK (formula_id between7001 and 9000),submission_id int NOT NULL,node_id int NOT NULL,reference_year smallint NOT NULL,observation_period datetime NOT NULL,authority_flag tinyint NOT NULLCONSTRAINT ONE_DEFAULT3437DEFAULT 1CONSTRAINT Binary_flag_rule669CHECK (authority_flag IN(0, 1)),observation_value decimal_datatype NOT NULL,interpolated_flag tinyint NOT NULLCONSTRAINT ZERO_DEFAULT6927DEFAULT 0CONSTRAINT Binary_flag_rule670CHECK (interpolated_flag IN(0, 1)),time_created smalldatetime NOT NULLCONSTRAINTCURRENT_DATE_DEFAULT1808DEFAULT getdate(),CONSTRAINT XPKcs_working_indexes2PRIMARY KEY NONCLUSTERED (formula_id, submission_id,node_id, reference_year, observation_period)--ON "INDEXES")--ON "WORKING"goCREATE UNIQUE CLUSTERED INDEX XAK1cs_working_indexes2 ONdbo.cs_working_indexes2(submission_id ASC,formula_id ASC,node_id ASC,authority_flag ASC,observation_period ASC,reference_year ASC,observation_value ASC)goCREATE INDEX XIE1cs_working_indexes2 ON dbo.cs_working_indexes2(node_id ASC,authority_flag ASC,formula_id ASC,observation_period ASC,reference_year ASC,observation_value ASC)--ON "INDEXES"godrop table dbo.cs_working_other2goCREATE TABLE dbo.cs_working_other2 (formula_id int NOT NULLCONSTRAINT formula_id_other14CHECK (formula_id >= 9001),submission_id int NOT NULL,node_id int NOT NULL,reference_year smallint NOT NULL,observation_period datetime NOT NULL,authority_flag tinyint NOT NULLCONSTRAINT ONE_DEFAULT3438DEFAULT 1CONSTRAINT Binary_flag_rule671CHECK (authority_flag IN(0, 1)),observation_value decimal_datatype NOT NULL,interpolated_flag tinyint NOT NULLCONSTRAINT ZERO_DEFAULT6928DEFAULT 0CONSTRAINT Binary_flag_rule672CHECK (interpolated_flag IN(0, 1)),time_created smalldatetime NOT NULLCONSTRAINTCURRENT_DATE_DEFAULT1809DEFAULT getdate(),CONSTRAINT XPKcs_working_other2PRIMARY KEY NONCLUSTERED (formula_id, submission_id,node_id, reference_year, observation_period)--ON "INDEXES")--ON "WORKING"goCREATE UNIQUE CLUSTERED INDEX XAK1cs_working_other2 ONdbo.cs_working_other2(submission_id ASC,formula_id ASC,node_id ASC,authority_flag ASC,observation_period ASC,reference_year ASC,observation_value ASC)goCREATE INDEX XIE1cs_working_other2 ON dbo.cs_working_other2(node_id ASC,authority_flag ASC,formula_id ASC,observation_period ASC,reference_year ASC,observation_value ASC)--ON "INDEXES"godrop table dbo.cs_working_p1q12goCREATE TABLE dbo.cs_working_p1q12 (formula_id int NOT NULLCONSTRAINT formula_id_p1q114CHECK (formula_id between3001 and 7000),submission_id int NOT NULL,node_id int NOT NULL,reference_year smallint NOT NULL,observation_period datetime NOT NULL,authority_flag tinyint NOT NULLCONSTRAINT ONE_DEFAULT3439DEFAULT 1CONSTRAINT Binary_flag_rule673CHECK (authority_flag IN(0, 1)),interpolated_flag tinyint NOT NULLCONSTRAINT ZERO_DEFAULT6929DEFAULT 0CONSTRAINT Binary_flag_rule674CHECK (interpolated_flag IN(0, 1)),observation_value decimal_datatype NOT NULL,time_created smalldatetime NOT NULLCONSTRAINTCURRENT_DATE_DEFAULT1810DEFAULT getdate(),CONSTRAINT XPKcs_working_p1q12PRIMARY KEY NONCLUSTERED (formula_id, submission_id,node_id, reference_year, observation_period)--ON "INDEXES")--ON "WORKING"goCREATE UNIQUE CLUSTERED INDEX XAK1cs_working_p1q12 ONdbo.cs_working_p1q12(submission_id ASC,formula_id ASC,node_id ASC,authority_flag ASC,observation_period ASC,reference_year ASC,observation_value ASC)goCREATE INDEX XIE1cs_working_p1q12 ON dbo.cs_working_p1q12(node_id ASC,authority_flag ASC,formula_id ASC,observation_period ASC,reference_year ASC,observation_value ASC)--ON "INDEXES"godrop table dbo.cs_working_pq2goCREATE TABLE dbo.cs_working_pq2 (formula_id int NOT NULLCONSTRAINT formula_id_pq14CHECK (formula_id between1001 and 3000),submission_id int NOT NULL,node_id int NOT NULL,reference_year smallint NOT NULL,observation_period datetime NOT NULL,authority_flag tinyint NOT NULLCONSTRAINT ONE_DEFAULT3440DEFAULT 1CONSTRAINT Binary_flag_rule675CHECK (authority_flag IN(0, 1)),interpolated_flag tinyint NOT NULLCONSTRAINT ZERO_DEFAULT6930DEFAULT 0CONSTRAINT Binary_flag_rule676CHECK (interpolated_flag IN(0, 1)),observation_value decimal_datatype NOT NULL,time_created smalldatetime NOT NULLCONSTRAINTCURRENT_DATE_DEFAULT1811DEFAULT getdate(),CONSTRAINT XPKcs_working_pq2PRIMARY KEY NONCLUSTERED (formula_id, submission_id,node_id, reference_year, observation_period)--ON "INDEXES")--ON "WORKING"goCREATE UNIQUE CLUSTERED INDEX XAK1cs_working_pq2 ONdbo.cs_working_pq2(submission_id ASC,formula_id ASC,node_id ASC,authority_flag ASC,observation_period ASC,reference_year ASC,observation_value ASC)goCREATE INDEX XIE1cs_working_pq2 ON dbo.cs_working_pq2(node_id ASC,authority_flag ASC,formula_id ASC,observation_period ASC,reference_year ASC,observation_value ASC)--ON "INDEXES"go----- create view ---------drop view cs_working2goCREATE VIEW cs_working2 (submission_id, node_id, reference_year,observation_period, formula_id, observation_value, interpolated_flag,time_created, authority_flag) ASSELECT we.submission_id, we.node_id, we.reference_year,we.observation_period, we.formula_id, we.observation_value,we.interpolated_flag, we.time_created, we.authority_flagFROM cs_working_e2 weunion allSELECT wo.submission_id, wo.node_id, wo.reference_year,wo.observation_period, wo.formula_id, wo.observation_value,wo.interpolated_flag, wo.time_created, wo.authority_flagFROM cs_working_other2 wounion allSELECT wpq.submission_id, wpq.node_id, wpq.reference_year,wpq.observation_period, wpq.formula_id, wpq.observation_value,wpq.interpolated_flag, wpq.time_created, wpq.authority_flagFROM cs_working_pq2 wpqunion allSELECT wp1q1.submission_id, wp1q1.node_id, wp1q1.reference_year,wp1q1.observation_period, wp1q1.formula_id, wp1q1.observation_value,wp1q1.interpolated_flag, wp1q1.time_created, wp1q1.authority_flagFROM cs_working_p1q12 wp1q1union allSELECT wi.submission_id, wi.node_id, wi.reference_year,wi.observation_period, wi.formula_id, wi.observation_value,wi.interpolated_flag, wi.time_created, wi.authority_flagFROM cs_working_indexes2 wigo--- sample selects against partitioned view -----/*--run explain plan here and see all 5 partitions being pulledselect * from cs_working--run explain plan here and see just the 1 partitionselect * from cs_working_e2--run explain plan and see this is not pruning to the needed partitionselect * from cs_workingwhere formula_id = 1--run explain plan and see it is now pruning to the needed partitionselect * from cs_workingwhere formula_id = 1and submission_id is not null--run explain plan and see it is now pruning to the needed partition,tooselect * from cs_workingwhere formula_id = 1and observation_value is not null*/
View 1 Replies View RelatedDistributed Partitioned Views/Federated Servers Anyone?
Apr 21, 2003I'm trying to do some researh on the use of SQL's DPV. I'm looking for feedback from people who've actually done this production to know more about the design challenges and level of added administration required. Any information will be much appreciated. Thanks.
aK
DB Engine :: Partitioned Views In Standard Edition?
Apr 24, 2015We have are SQL 2014 Standard edition, I have a situation where-in I plan to partition a table now since table partition is not supported in standard version I thought about Partitioned views however now I am stuck where I cant make the view writable because of the identity column in the base table.
Do I have any other option in this case?
SQL Server 2014 :: Query Plan For Partitioned Views Not Running As They Should
Mar 29, 2015I've been using partitioned views in the past and used the check constraint in the source tables to make sure the only the table with the condition in the where clause on the view was used. In SQL Server 2012 this was working just fine (I had to do some tricks to suppress parameter sniffing, but it was working correct after doing that). Now I've been installing SQL Server 2014 Developer and used exactly the same logic and in the actual query plan it is still using the other tables. I've tried the following things to avoid this:
- OPTION (RECOMPILE)
- Using dynamic SQL to pass the parameter value as a static string to avoid sniffing.
To explain wat I'm doing is this:
1. I have 3 servers with the same source tables, the only difference in the tables is one column with the server name.
2. I've created a CHECK CONSTRAINT on the server name column on each server.
3. On one of the three server (in my case server 3) I've setup linked server connections to Server 1 and 2.
4. On Server 3 I've created a partioned view that is build up like this:
SELECT * FROM [server1].[database].[dbo].[table]
UNION ALL SELECT * FROM [server2].[database].[dbo].[table]
UNION ALL SELECT * FROM [server3].[database].[dbo].[table]5. To query the partioned view I use a query like this:
SELECT *
FROM [database].[dbo].[partioned_view_name]
WHERE [server_name] = 'Server2'
Now when I look at the execution plan on the 2014 environment it is still using all the servers instead of just Server2 like it should be. The strange thing is that SQL 2008 and 2012 are working just fine but 2014 seems not to use the correct plan.
SQL Server 2008 :: Partitioned Views Table Elimination Not Working
Jul 7, 2015I have some Partitioned Views and on all queries using a table for the in clause, table elimination isn't happening.
Check Constraint is on the oid column
This works as expected, only goes to 2 tables;
SELECT *
FROM view_oap_all
WHERE oid IN ( '05231416529481', '06201479586431' )
This works as expected, only goes to 2 tables;
SELECT *
FROM view_oap_all
WHERE oid IN ( SELECT oid
FROM owners
WHERE oid IN ( '05231416529481', '06201479586431' ) )
This is checking all tables (headingnames are unique), ive tried this for the last 3 hours on many different tables containing the oid column.
Unless I write the oid as in the above queries it just doesn't work.
SELECT *
FROM view_oap_all
WHERE oid IN ( SELECT oid
FROM owners
WHERE headingname = 'TestSystem' )
SQL 2012 :: Using Partitioned Views In Order To Manage Table Sizes
Oct 13, 2015I have a few databases that are using Partitioned Views in order to manage the table sizes and they all work well for our purposes. Recently I noticed a table that had grown to 400+ million rows and want to partition it as well, so I went about creating new base tables based on the initial table's structure, just adding a column to both table and primary key to be able to build a Partitioned View on them.The first time around, on a test system, everything worked flawlessly but when I put the same structure in place on the production system I get the dreaded "UNION ALL view 'DBName.dbo.RptReportData' is not updatable because the primary key of table '[DBName].[dbo].[RptReportData_201405]' is not included in the union result. [SQLSTATE 42000] (Error 4444)" error.
I have searched high and low and everything I see points to a few directives in order for a UNION ALL view to be updatable:
- Need a partitioning column that is part of the primary key
- Need a CHECK constraint that make the base tables exclusive, i.e. data cannot belong to more than one table
- Cannot have IDENTITY or calculated columns in the base tables
- The INSERT statement needs to specify all columns with actual values, i.e. not DEFAULT
Well, according to me, my structure fulfills these conditions but the INSERT fails anyway. CREATE scripts below scripted from SQL Server. I only modified them to be on a single row - it is easier to verify that they are identical in a text editor that way.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
[code]....
Query Server To Find All Partitioned Tables, Partition Name, Column Used, Partitioned By
Dec 17, 2007I want to find a way to get partition info for all the tables in all the databases for a server. Showing database name, table name, schema name, partition by (maybe; year, month, day, number, alpha), column used in partition, current active partition, last partition (for date partitions I want to know if the partition goes untill 2007, so I can add 2008)
all I've come up with so far is:
Code Block
SELECT distinct o.name From sys.partitions p
inner join sys.objects o on (o.object_id = p.object_id)
where o.type_desc = 'USER_TABLE'
and p.partition_number > 1
Can Not Create Views, Tables Etc * URGENT*
Jul 20, 2005Hi,I am not getting option as 'new view' and 'new table' when I rightclick onviews and tables option in VisualStudio.net IDE server explorersqlservers database to create new objects.Looks like some setup issue in my database.Thanks for your help in advance.RgdsCV
View 1 Replies View RelatedDB Design :: Cannot Delete Data From A Partitioned Table
Sep 30, 2015I have a very large table that I am trying to partition and use to reduce maintenance overhead as well as improve performance. The table contains about 12 years worth of data but only the most recent years is inserted/updated/deleted from thru the app. I created partitions on a computed(persisted) column which holds the "year" value derived from a date column. I have created the partitions with all the default set options, and the stored procedure which performs the delete against this table also was created with no special set options(basically database/session default). Yet, every time I try to run the proc to delete data thru the app, I get this error:
Msg 1934, Level 16, State 1, Procedure xxxx, Line 118
DELETE failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
I've tried setting ANSI_WARNINGS on and off when creating the proc, inside the proc etc.., its always the same error whatever I set the option to.
Best Practice - Data Sources And Data Views Vs OLE DB Source
Feb 26, 2008Hi, i'm wondering which is the best way to search data in a SQL Server.
I reach data using Data Sources and Data Views and also with OLE DB Source with a Data access mode: Named query.
I have to write the data into a Flat File. So, does any one knows which is the best practice for this? Or any one of the two are good choices?
Thanks for your help.
Beli
Are Embedded Views (Views Within Views...) Evil And If So Why?
Apr 3, 2006Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
View 15 Replies View RelatedPartition Data/Views?
Jul 17, 2001Hi folks,
Recently i've been working on a new project that would partition a large table 2 smaller tables. I then create a view to union the 2 smaller tables(table A, B). I've been getting a strange error when i try to update, insert, delete a record through the view. "View needs partitioning column"....i find this strange. Both of my table have a cluster primary key consisting of 3 columns, and one of the 3 columns(date field) consist of a check constraint. The constraint is used to determine what record goes into which table. Am i missing anything else? The really strange part is sometime it works, and sometimes i get the error message.
Any thoughts?
Joe R.
Adding Data Through Views ?
Dec 8, 2004Hi!
Is it possible to add/delete/modidy data through views?
What is the major difference between SQL Stored procedure & SQL function?One diff i know is using SP we can execute transact-sql statements.Is there any other difference?
Thanks in advance
Editing Data Using Views
Feb 29, 2008Please direct me towards good documentation about updating, inserting and deleting data using views involving multiple tables.
Thanks
Retriving Data From 2 Views By The Value In A Textbox
Aug 9, 2007i have a textbox which a user enters a numeric value
i want it to use SqlDataSource and check if the value exists in any of the tables.
in my text box the users would enter starting from '100000' or '200000'
i want it to check the view that starts the # with '100000' and 2ed view starts '200000'
With this i can check in one of the tables and make the selection.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:imacstestConnectionString %>"
SelectCommand="SELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @ReportNumber)">
<SelectParameters>
<asp:ControlParameter ControlID="txtReport" Name="ReportNumber" PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
How can i make this possible ?
i was thinking putting a second sqldatasource and have that check the second view but how can i make the textbox goto the correct selectcommand ?
Reading Data From Table And Views
Feb 2, 2006Hi
I need to read a very big table more than 60,000 record but it is giving the following problem: But if it is small table there is no problem. Same problem also views.
Server Error in '/POBuilds' Application.
Runtime Error
Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine. Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".
<!-- Web.Config Configuration File -->
<configuration>
<system.web>
<customErrors mode="Off"/>
</system.web>
</configuration>Notes: The current error page you are seeing can be replaced by a custom error page by modifying the "defaultRedirect" attribute of the application's <customErrors> configuration tag to point to a custom error page URL.
<!-- Web.Config Configuration File -->
<configuration>
<system.web>
<customErrors mode="RemoteOnly" defaultRedirect="mycustompage.htm"/>
</system.web>
</configuration>
Comparing Data In Tables/views.
May 13, 2008I am looking for an efficient mechanism to compare data between 2 tables/views.
Rationale:
I use a proprietary tool to data transfer between 2 databases. The tool itself uses Microsoft SSIS (integration service) to transfer data. I want to make sure that the data is transfered properly. Both the source and target database are not live database. The source and target database are in seperate servers.
Querying Data From Multiple Views
Jul 23, 2005Hello,I am relatively new to doing non-trivial SQL queries.I have to get data out of 8 diff views based on a parameter Name.There is a view having name-ssn pairs. All other views have SSN field.For a person there MAY NOT be data in all the views.I have to populate data into diff tables in a Report from differentviews.I would like to know what is the best way to approach it.So far I was trying an Inner join from the Name-ssn vies to all otherviews based on the SSN and test for the name field with the inputparameter.I am thinking there will be problem of Cross join if I dont have datain all views about a person.Or the best way is to write query for each view and have all of them ina stored procedure ?Any help will be appreciatedThanksBofo
View 1 Replies View RelatedData Parsing In SQL Server Views
Jul 23, 2005If column1 in SQL Server column is text: 19980701What is the syntax in the select statement to convert it to a datelike: 07/01/1998Thanks for any helpRbollinger
View 1 Replies View RelatedUsing Views/Stored Procs In One Db, Data In Another
Jan 24, 2007I am moving an app from Access 2003 to C#/SQL Server 2005. The Access app has one front end exe and two back end databases. One back end db is for UK users, one for US users. The tables and structures are identical, but the data is different. UK users link to the UK back end, US users to the US backend. (The queries are in the front end)
In SQL Server, the view and stored procs will be in one database (KCom), the US data tables in another database (KUS), and the UK data tables in another (KUK). All databases are on the same server.
My question is how to let the views and stored procs in KCom know whether to pull the data from KUK or KUS.
In the front end app, I use ADO.Net to deal with the SQL Server databases.
I have not been able to find a model for this, but it must be somewhat common.
I willl have a lost of nested views and stored procs, but as a simple example, say I have a view in KCom which is just "Select * From Invoice Where InvDate > '01/01/2007'. The ADO request would come from the front end app which would know whether it wanted the data from KUK or KUS. How would I get the view to get the data from one as opposed to the other (KUK vs KUS) ?
Is there some other strategy for this situation, say use of connection strings? If anyone has an idea, or knows of an explanation somewhere on the net I'd greatly appreciate it.
Many thanks
Mike Thomas
View That Would Output Data From Two Views
Aug 8, 2007OK,
I have two data tables where depending on the time line item it is, it needs a different join.
So, it's like this:
Table Credit Card
Table Memo
If Credit Card is VISA, join on column A
If Credit Card is MA, join on column B
So, I created two views. One view has all the info joined on column A. The other view has all the info joined on column B.
How do I create a third view that will output all the data from View A and View B without making it into a cartesian product?
I can't change the table structure due to legacy application/data issues.
Thanks!
SQL 2012 :: Should Use Views Or Create A Data Mart
Oct 9, 2015our group is debating whether we should use views or data marts. WE have some huge queries that can have many joins up to 20 in some cases. One part of our group wants to use views to pull this data and another thinks we should create a data mart off an SSIS package and run it early every morning and then have the users access the data directly from there.
I am not really sure how a view would speed things up. If I have 20 users and they all call a view the view is created 20 times is that not correct? Since a view is basically just a stored sql statement (not a stored proc) I am not seeing how this is any more efficient.