I am working with several tables and views. My goal is to create a view
with critical reporting data from these tables and views. I have
managed to get the majority of data but am having difficultly with the
final step.
For a record in the master dataview, add additional record information
by appending data from another view based on the first 2 letters of the
document number and the document number i.e
Document number
SC11111
DN22222
SI33333
For SC11111 look up data in Sales Credit table/view for doument SC11111
and append to that line
For DN22222 look up data in Delivery Note table/view for document
DN22222and append to that line
For SI33333 look up data in Sales Invoice table/view for document
SI33333 and append to that line
Fellow 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.
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
I've been using stored procedures to do queries including inner joins etc. I have always known about views but don't understand why they are used, what is there purpose, they seem the same as sql queries, is that true? Is it more effiicient to use views rather than stored procedures to do your queries?
Really I just want to know why I would ever want to use views instead of stored procedures to do my queries?
I would like to know if its possible to create a view of a table residing in a SQL server which is not the one in which we r currently working in Desc: 1) Say i have 2 SQL servers( different Boxes) A & B I am working in ' B ', can i have a view of a table residing in SQL server 'A' in my system( Server 'B') 2) Leaving out the network uncertainities, will such a setup give any performance issue when the view is for a table that captures daily transactions running into several thousands of records 3) Is ther any other Alternative to access tables in another SQL sever without going for Views?
I know that stored procedures(sql server) caches stored procedures in memory where it keeps the compiled execution plan in memory, how does it work with the views does sql server store /cache the views. Just wondering Thanks
Hi there,First thing off, I'd like to know what Views are useful for? I thing it can speeds select query containing multiple join, but I'm not even sure if that's true.Second, assuming that Views speeds thing up for SELECT query, can it be used by NHibernate when restoring object from their persistence store (in this case, SQL 2k5) ? If not, can they be (views) of any use if we use NHibernate?Third, would it be better to create our crystal report using Views to improve performance?Thanks for anyone who participates in this thread! :-)
Is it possible to make views which takes Data from Multiple Servers? NOTE: The view is very complex because it uses FOUR Diff. statements joined together with UNION clause.
One more Q. If my Query Analizer Displays following on execution of query. " Internal Query Processor Error: The query processor could not produce a query plan." What should be my approach to rectify the problem.
I have two databases on my Server - database1 and database2. Database1 has a view on tables on database2 : create view myview as select * from database2.dbo.tablename (because all tables in database2 are owned by dbo) and myview on database1 is owned by dbo. when a user on database1 - lets say user1 tries to select rows from myview, we get an error saying that user1 is not a valid user on database2. it only allows user1 to select rows from myview if i make user1 a user on database2 as well, which is not what i want. Can someone please point me in the right direction. i would like to be able to select rows from a view which is based on tables in another database. thanks simran
Be careful when implementing views (from SQL Server 97/2K). SQL Serverstores the metadata on the view at creation (or the last time it wassaved). This means if you have:SELECT * FROM table1it will put all the fields of table1 in the view's metadata. If youthen change table1 and add (for example) another field, this fieldwill not be visible in the view until you open it in design view andclick save (to update it).
hi all, i had a view in my project, i am inserting a record in to that view(View contains a identity key for a column), with in stored procedure i am inserting a record i am not passing the identity key value to the insert statement. The record is getting inserted,Based on the identity key(i am getting the identity key value with Scope_Identity()) and with that value i am inserting records into another two tables.In this scenario every thing is working fine. but now i am trying to place a trigger(instead of insert trigger) on the view, when i placed,it is not inserting record into first table,so i am not able to get the identity value of that record and process failed. how can this achived, let me know.
I have two servers that i want to create a SQL RS report on. On one server there is an HR database with our staff details, on the other server there is a database of assets.
In order to report on the assets assigned to each user i am thinking that i will have to : 1) link the servers 2) create a view in the HR database exposing the fields needed 3) create a view in the assets database exposing the assets information joined to the view from the other server 4) create my reports on the view on the assets server.
I created a linked server with Oracle Provider for OLE DB to Oracle Database from SQL Server 2000 SP 4. Afterwards I created Views from this linked server and granted SQL Server users "select" access to the Views. Unfortunately when they try to access the views the following error message was generated (see the attached image - error.gif).
If 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
Do not trust values returned by materialized views under SQL Serverwithout frequently checking underlying tables!!!I already posted this message under microsoft.public.sqlserver.serverand I'm amazed nobody from Microsoft answered about this problem. Byinserting lots of data into our two main tables for about 30 minutes,we can fail our materialized view that performs a count_big on thosetwo tables.Executing (after of course having stopped inserting rows in our twotables)[color=blue]> SELECT SUM(field1+field2+field3) FROM MatView option(expand views)[/color]DOES NOT RETURN the same value than:[color=blue]> SELECT SUM(field1+field2+field3) FROM MatView with (noexpand)[/color]The second call - using the materialized view - returns a smallernumber (as if counts were lost during our bulk insert)As our data has to be accurate, we cannot use Materialized viewsanymore. This problem does not occur when the amount of data insertedis smaller. Rebuilding the clustered index on the view fixes theproblem; do we have to constantly be rebuilding the index to keep theview synchronize !?!!?!Is there a way to tell that our view is not synchronized? Justcomparing values returned by our view does not work for us as data isconstantly been inserted.System: SQL server 2000 SP3 Enterprise EditionVincent LIDOU
I read that views can't be published with SQL Server 2005 replication. Is this planned for the future? If not what alternatives are there for this?
Simple collecting the data needed in a new table ain't a solution for us (memory consuming). And joining the data on the PPC ain't a good solution either (memory and time consuming). We only want to pull the data.
SQL 2005 SP2 in Managemrnt Studio... I have a linked server set-up pointing to an instance that has several databases. In one catalog I have no tables or views being listed for the catalog. So I select a view, add the permissions that should allow the view to show in the list but it does not. And I did refresh and I did even restart management studio.
BTW: I can exec the view using a 4 part name.
Any one have an idea why the view don't show in the list?
I have to come up with a SSIS package to tranform data from oracle database to our sql server database. This oracle db is managed by a third party , I have been given 10 views from oracle so I can extarct information I need. How should I design my SSIS package? Do I have to have 10 different data flows or there is an eaier way to run my 10 select statements from these views?
Is there any article that can give me some ideas on how to design SSIS packages to extract information from oracle?
Again i need help with a couple of issues in migrating from Oracle to SQL Server 2005. I need the equivalent sentence in SQL Server of the following sentence:
CREATE OR REPLACE VIEW IBA_MPDATOSGENERALES AS ( SELECT IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION, IBA_MPREQUERIMIENTOS.APLAZADOREQ, IBA_MPACCIONESXREQ.FECHAFINALAXR, IBA_USUARIOS.NOMBREUSU FROM IBA_MPPROCEDXLOC, IBA_MPREQUERIMIENTOS, IBA_MPACCIONESXREQ, IBA_USUARIOS WHERE IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION = IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO(+) AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME AND IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'S' AND IBA_MPACCIONESXREQ.CERRADAAXR = 'N' AND NOT IBA_MPACCIONESXREQ.FECHAFINALAXR IS NULL UNION SELECT IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION, IBA_MPREQUERIMIENTOS.APLAZADOREQ, IBA_MPACCIONESXREQ.FECHAFINALAXR, IBA_USUARIOS.NOMBREUSU FROM IBA_MPPROCEDXLOC, IBA_MPREQUERIMIENTOS, IBA_MPACCIONESXREQ, IBA_USUARIOS WHERE IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION = IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO(+) AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME AND IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'N' AND IBA_MPACCIONESXREQ.CERRADAAXR = 'N' AND NOT IBA_MPACCIONESXREQ.FECHAPROXEJECUCIONAXR IS NULL UNION SELECT IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION, IBA_MPREQUERIMIENTOS.APLAZADOREQ, IBA_MPACCIONESXREQ.FECHAFINALAXR, IBA_USUARIOS.NOMBREUSU FROM IBA_MPPROCEDXLOC, IBA_MPREQUERIMIENTOS, IBA_MPACCIONESXREQ, IBA_USUARIOS WHERE IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION = IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO(+) AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME AND (IBA_MPACCIONESXREQ.CERRADAAXR = 'S' OR (IBA_MPACCIONESXREQ.CERRADAAXR = 'N' AND ((IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'N' AND IBA_MPACCIONESXREQ.FECHAPROXEJECUCIONAXR IS NULL) OR (IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'S' AND IBA_MPACCIONESXREQ.FECHAFINALAXR IS NULL)))) )
I know that (+) is for outer joins (left or right). I also know that UNION may mean FULL OUTER JOIN. but this query? The real query is a lot more complex, well actually there are just more fields needed from more tables but with this exmple is enough. The bottom line is that i need data from various tables, most of them involved in a JOIN clause. Also check out that in SQL Server doesnt exists the REPLACE word or does it? Thank you in advance,
I am using the import/export wizard to import all the objects from one database into another.My problem is that any views that use User Defined Functions are failing because the view is being created before the UDF.I have searched for this error, and found the following microsoft article:http://support.microsoft.com/kb/300272/en-usThe article says that I should upgrade to the latest service pack. The problem with this is that I already have the latest service pack When I run the following:SELECT @@VersionI get:Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) Could anyone please help me solve this problem.Jag
I wonder if anyone can help me - I am trying to write a little application which recompiles any views dependent on a given view. I have a script which will identify any views containing a given text string, but I now need a way to force SQL Server to refresh its cache info (or recompile) the views. I have to admit that I am unclear on exactly how the caching works; i have tried using sp_recompile, but this does not seem to do the job. Does anyone have any ideas?
Hi, I'm getting some unexpected behaviour from my SSIS packages when targeting tables that are being referenced by Indexed Views. There's two separate issues:
1. When writing into a pair of tables with a SuperType / SubType relationship concurrently with a pair of SS destinations I'm getting deadlocks between the two. Removing the index on the view that references both of these fixes the problem.
2. Much odder, I'm getting some extremely long waits (10 times longer than the whole package should take to run!) from an SS Destination adapter even when there's no data in the flow for it to bulk insert. Again, removing the indexed views that reference the destination table fixes the problem.
The views aren't mine, and (apparently) are required by the reporting app (BO), so removing them isn't really an option. I realise that there's quite a lot of overhead to maintaining indexed views, but unfortunately, the project is on a very tight timeline, so I can't look into it in as much detail as I'd like. I was wondering if anyone's experienced any similar issues, or would have any ideas as to where to start investigating?
Previously in Sql Server 2000, we would be in enterprise manager, you'ddouble click on a view, and a nice little dialog box opened with the t-sqlstatetments, there was also a check sql syntax and apply and cancel buttons.Not exactly query anaylizer, just a quick lightweight dialog box. Is thisfeature still around? Seems like I have to go into the query anaylizer likemode to edit a view now. I am a total newbie to version 2005. Are there anyoptions I can set to make it behave the old way? All feedback isappreciated.TIA,~CK
I am using SQL Server Management Studio and Visual Studio 2005 and I am a newbie. Once connected to a remote server and database I am able to use SQL Server Management Studio to modify a table and save it. When I modify a stored procedure or create a new one and attempt to save it I get my local dialog box to save to my system with a new name €œSQLQuery6.sql.€? How do I cause the procedure to be saved to the remote database? If anyone can help this or where I can find it I will appreciate it. Thanks in advance.
